存储过程自动生成增删改proc
create proc [dbo].[sp_SendCode]
@tablename varchar(20), --表名
@type int=1, --类型（1：存储过程，2：调用代码）
@opertype int=1, --操作类型（1：查，2：增，3：改，4：删）
@fields varchar(2000)='*',--要操作的列（查、增、改时有效，默认操作所有列，多个列用英文逗号隔开）
@where varchar(500)='' --要作为条件的列（查、改、删时有效，默认为空，多个列名用英文逗号隔开，如：字段1,字段2，其中主键列可以省略）
as
--不返回受影响的行数，提高性能
set nocount on
--分别定义主键列,主键列是否自增列,主键列的类型,最后要生成的代码
declare @keyfield varchar(20),@iden int,@partype varchar(20),@code varchar(4000)
select @keyfield=c.name,@iden=c.is_identity,@partype=d.name from sys.indexes a,sys.index_columns b,sys.columns c,systypes d
where a.object_id=b.object_id and a.index_id=b.index_id and a.object_id=c.object_id and c.user_type_id=d.xtype
and b.column_id=c.column_id and a.is_primary_key=1 and d.status=0
and a.object_id=OBJECT_ID(@tablename)
--查询时，是否只返回实体对象(1:是，0:不是)
declare @isflag bit
set @isflag=0
if(@where=@keyfield)
set @isflag=1
--进行非空处理
if(not exists(select 1 from sysobjects where id=OBJECT_ID(@tablename)))
begin
print '请输入正确的表名！'
return
end
--修改的列集合
declare @updatefields varchar(1000)
set @updatefields=''
--如果指定了要操作的列，进行校验。防止出现错误列
if(@fields!='' and @fields!='*')
begin
declare @oldfields varchar(200)
set @fields=REPLACE(@fields,'，',',')
if(right(@fields,1)!=',')
set @fields=@fields+','
set @oldfields=@fields
set @fields=''
--遍历，筛选出@fields中的有效列
while(CHARINDEX(',',@oldfields)>0)
begin
--获取到字段名
declare @tempf varchar(50)
set @tempf=SUBSTRING(@oldfields,1,charindex(',',@oldfields)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@tempf)
begin
set @fields=@fields+@tempf+','
set @updatefields=@updatefields+@tempf+'=@'+@tempf+','
end
set @oldfields=SUBSTRING(@oldfields,charindex(',',@oldfields)+1,LEN(@oldfields))
end
end
--如果没有有效字段，或是操作所有字段
if(@fields='*' or @fields='')
begin
set @fields=''
select @fields=@fields+name+',',@updatefields=@updatefields+case when name!=@keyfield then name+'=@'+name+',' else '' end
from syscolumns where id=OBJECT_ID(@tablename)
end
if(@updatefields!='')
set @updatefields=STUFF(@updatefields,LEN(@updatefields),1,'')
--保存条件
declare @tempwhere varchar(200)
set @tempwhere=''
--如果有条件时，也对条件进行相同处理
if(@where!='')
begin
declare @oldwhere varchar(200),@tempfield varchar(50)
set @where=REPLACE(@where,'，',',')
if(right(@where,1)!=',')
set @where=@where+','
set @oldwhere=@where
set @where=''
--遍历，筛选出@fields中的有效列
while(CHARINDEX(',',@oldwhere)>0)
begin
set @tempfield=SUBSTRING(@oldwhere,1,charindex(',',@oldwhere)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@tempfield)
begin
set @tempwhere=@tempwhere+@tempfield+','
set @where=@where+' and '+@tempfield+'=@'+@tempfield
end
set @oldwhere=SUBSTRING(@oldwhere,charindex(',',@oldwhere)+1,LEN(@oldwhere))
end
end
if(@where='' and (@opertype=3 or @opertype=4))
begin
--如果没有有效条件，则将主键作为条件
set @tempwhere=@keyfield+','
set @where=' and '+@keyfield+'=@'+@keyfield
end
--生成存储过程
if(@type=1)
begin
--增加时，如果操作列中 存在主键列，并且主键列为自增列时，排除该列
if(@opertype=2 and CHARINDEX(@keyfield,@fields)>0 and @iden=1)
begin
set @fields=stuff(@fields,charindex(@keyfield+',',@fields),LEN(@keyfield+','),'')
end
set @fields=stuff(@fields,LEN(@fields),1,'')
--生成存储过程的参数声明
declare @paras varchar(1000)
--生成存储过程
if(@opertype=1) --查询
begin
--根据条件来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and CHARINDEX(','+a.name+',',','+@tempwhere+',')>0
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行查询'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_select'+case @isflag when 0 then '' else '_one' end +']'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'select '+@fields+' from ['+@tablename +'] where 1=1'+ @where
+CHAR(13)
end
else if(@opertype=2)--增加
begin
--根据添加的字段来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and CHARINDEX(','+a.name+',',','+@fields+',')>0
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行添加'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_insert]'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'insert into ['+@tablename +'] ('+@fields+') values(@'+REPLACE(@fields,',',',@')+')'
+CHAR(13)
end
else if(@opertype=3) -- 修改
begin
--如果没有有效条件，则将主键作为条件
if(@where='')
begin
set @tempwhere=@keyfield
set @where=' and '+@keyfield+'=@'+@keyfield
end
--根据添加的字段和条件来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and (CHARINDEX(','+a.name+',',','+@fields+',')>0 or CHARINDEX(','+a.name+',',','+@tempwhere+',')>0)
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行修改'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_update]'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'update ['+@tablename +'] set '+@updatefields+' where 1=1'+@where
+CHAR(13)
end
else if(@opertype=4)
begin
--根据条件来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and CHARINDEX(','+a.name+',',','+@tempwhere+',')>0
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行删除'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_delete]'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'delete ['+@tablename +'] where 1=1'+@where
+CHAR(13)
end
end
--生成C#方法
else if(@type=2)
begin
--增加、修改时都需要对操作列和条件列声明为参数化
declare @sqlparameters varchar(3000),@newfield varchar(400)
set @sqlparameters=''
if(@opertype=2 or @opertype=3)
begin
--添加操作时，如果主键列是自增的，不需要指定主键列
if(CHARINDEX(@keyfield,@fields)>0 and @iden=1)
begin
set @fields=stuff(@fields,charindex(@keyfield+',',@fields),LEN(@keyfield+','),'')
end
--声明参数
select @sqlparameters=@sqlparameters+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+
'new SqlParameter("@'+a.name+'",SqlDbType.'+
case b.name when 'varchar' then 'VarChar' when 'bigint' then 'BigInt'
when 'datetime' then 'DateTime' when 'nvarchar' then 'NVarChar' when 'numeric' then 'Decimal'
when 'tinyint' then 'TinyInt'
else UPPER(left(b.name,1))+RIGHT(b.name,len(b.name)-1) end
+','+ltrim(a.length)+'),'+CHAR(13)
from syscolumns a,systypes b where a.id=OBJECT_ID(@tablename) and charindex(','+a.name+',',','+@fields)>0 and a.xtype=b.xtype
and b.status=0
end
if(@opertype=1 or @opertype=4 or @opertype=3)--查询、修改、删除时需要添加条件参数
begin
--声明参数
select @sqlparameters=@sqlparameters+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+
'new SqlParameter("@'+a.name+'",SqlDbType.'+
case b.name when 'varchar' then 'VarChar' when 'bigint' then 'BigInt'
when 'datetime' then 'DateTime' when 'nvarchar' then 'NVarChar' when 'numeric' then 'Decimal'
when 'tinyint' then 'TinyInt'
else UPPER(left(b.name,1))+RIGHT(b.name,len(b.name)-1) end +','+ltrim(a.length)+'),'+CHAR(13)
from syscolumns a,systypes b where a.id=OBJECT_ID(@tablename) and
charindex(','+a.name+',',','+@tempwhere)>0 and a.xtype=b.xtype and b.status=0
end
if(@sqlparameters!='')
set @sqlparameters=STUFF(@sqlparameters,len(@sqlparameters)-1,1,'')
if(@opertype=1)
begin
if(@sqlparameters!='')
begin
--参数声明
declare @parselect varchar(200)
select @parselect=ISNULL(@parselect+',','')+
case when b.name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
when b.name in('tinyint','smallint','int') then 'int'
when b.name='bigint' then 'long'
when b.name in('datetime','smalldatetime') then 'DateTime'
when b.name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
when b.name ='bit' then 'bool'
else b.name end+' '+a.name
from syscolumns a,systypes b where a.xtype=b.xtype and a.id=OBJECT_ID(@tablename) and CHARINDEX(','+a.name+',',','+@tempwhere)>0
and b.status=0
--如果返回多记录
if(@isflag=0)
begin
--生成参数化
set @code='/// <summary>'+CHAR(13)
+'/// 对表'+@tablename+'中数据进行查询 '+CHAR(13)
+'/// </summary>'+CHAR(13)+
+'/// 以下代码有shipeng.wang存储过程自动生成 ///'+CHAR(13)
+'public DataSet GetList('+@parselect+')'+CHAR(13)
+'{'+CHAR(13)
+char(9)+'SqlParameter[] paras=new SqlParameter[]{'+CHAR(13)+@sqlparameters+CHAR(9)+'};'+CHAR(13)
declare @j int
set @j=0
--给参数赋值
while(CHARINDEX(',',@tempwhere)>0)
begin
declare @p1 varchar(30)
set @p1=substring(@tempwhere,1,CHARINDEX(',',@tempwhere)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@p1)
begin
select @p1=name from syscolumns where id=OBJECT_ID(@tablename) and name=@p1
set @code=@code+char(9)+'paras['+LTRIM(@j)+'].Value='+@p1+';'+CHAR(13)
set @j=@j+1
end
set @tempwhere=SUBSTRING(@tempwhere,CHARINDEX(',',@tempwhere)+1,LEN(@tempwhere))
end
set @code=@code+CHAR(9)+'return db.ExecuteDataSet("sp_'+@tablename+'_select'+'",CommandType.StoredProcedure,paras);'+CHAR(13)
+'}'
end
else --返回实体
begin
--生成参数化
set @code='/// <summary>'+CHAR(13)
+'/// 根据主键获取'+@tablename+'中实体对象'+CHAR(13)
+'/// </summary>'+CHAR(13)
+'/// 以下代码有shipeng.wang存储过程自动生成 ///'+CHAR(13)
+'public '+@tablename+' GetModel('+@parselect+')'+CHAR(13)
+'{'+CHAR(13)
+char(9)+'SqlParameter[] paras=new SqlParameter[]{'+CHAR(13)+@sqlparameters+CHAR(13)+CHAR(9)+'};'+CHAR(13)
set @code=@code+char(9)+'paras[0].Value='+@keyfield+';'+CHAR(13)
set @code=@code+CHAR(9)+'SqlDataReader read= db.ExecuteDataReader("sp_'+@tablename+'_select_one'+'",CommandType.StoredProcedure,paras);'+CHAR(13)
set @code=@code+CHAR(9)+'if(read!=null && read.Read())'+CHAR(13)
set @code=@code+CHAR(9)+'{'+CHAR(13)
set @code=@code+CHAR(9)+CHAR(9)+@tablename+' model = new '+@tablename+' ;'+CHAR(13)
declare @k int
set @k=0
declare @t varchar(50)
set @fields=@fields+','
while(CHARINDEX(',',@fields)>0)
begin
set @t=substring(@fields,1,CHARINDEX(',',@fields)-1)
select @code=@code+CHAR(9)+CHAR(9)+'model.'+@t+' = read.Get'+
case when b.name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'String'
when b.name in('tinyint','smallint','int') then 'Int32'
when b.name='bigint' then 'Int64'
when b.name in('datetime','smalldatetime') then 'DateTime'
when b.name in('float','decimal','numeric','money','real','smallmoney') then 'Decimal'
when b.name ='bit' then 'Boolean'
else b.name end+'('+ltrim(@k)+')'+';'+CHAR(13)
from syscolumns a,systypes b where a.id=object_id(@tablename) and a.name=@t and a.xtype=b.xtype and b.status=0
set @k=@k+1
set @fields=STUFF(@fields,1,CHARINDEX(',',@fields),'')
end
set @code=@code+CHAR(9)+CHAR(9)+'return model;'+CHAR(13)
set @code=@code+CHAR(9)+'}'+CHAR(13)
set @code=@code+CHAR(9)+'else'+CHAR(13)
set @code=@code+CHAR(9)+CHAR(9)+'return null;'+CHAR(13)
+'}'
end
end
else
--生成参数化
set @code='/// <summary>'+CHAR(13)
+'/// 对表'+@tablename+'中数据进行查询 '+CHAR(13)
+'/// </summary>'+CHAR(13)
+'/// 以下代码有shipeng.wang存储过程自动生成 ///'+CHAR(13)
+'public DataSet GetList()'+CHAR(13)
+'{'+CHAR(13)
+char(9)+'string sql="select * from '+@tablename+'";'+CHAR(13)
+CHAR(9)+'return db.ExecuteDataSet(sql,CommandType.Text);'+CHAR(13)
+'}'
end
else if(@opertype=2 or @opertype=3)--增加或者修改
begin
--生成参数化
set @code='/// <summary>'+CHAR(13)
+'/// 对表'+@tablename+'中数据进行'+case @opertype when 2 then '添加' else '修改' end +CHAR(13)
+'/// </summary>'+CHAR(13)
+'/// 以下代码有shipeng.wang存储过程自动生成 ///'+CHAR(13)
+'/// <param name="model">要'+case @opertype when 2 then '添加' else '修改' end +'的实体对象</param>'+CHAR(13)
+'public bool '+case @opertype when 2 then 'insert' else 'update' end +'('+@tablename+' model)'+CHAR(13)
+'{'+CHAR(13)
+char(9)+'SqlParameter[] paras=new SqlParameter[]{'+CHAR(13)+@sqlparameters+CHAR(13)+CHAR(9)+'};'+CHAR(13)
declare @i int
set @i=0
--给参数赋值
while(CHARINDEX(',',@fields)>0)
begin
declare @p varchar(30)
set @p=substring(@fields,1,CHARINDEX(',',@fields)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@p)
begin
set @code=@code+char(9)+'paras['+LTRIM(@i)+'].Value=model.'+@p+';'+CHAR(13)
set @i=@i+1
end
set @fields=SUBSTRING(@fields,CHARINDEX(',',@fields)+1,LEN(@fields))
end
if(@opertype=3)--修改时，需要加上条件参数
begin
--给参数赋值
while(CHARINDEX(',',@tempwhere)>0)
begin
set @p=substring(@tempwhere,1,CHARINDEX(',',@tempwhere)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@p)
begin
set @code=@code+char(9)+'paras['+LTRIM(@i)+'].Value=model.'+@p+';'+CHAR(13)
set @i=@i+1
end
set @tempwhere=SUBSTRING(@tempwhere,CHARINDEX(',',@tempwhere)+1,LEN(@tempwhere))
end
end
set @code=@code+CHAR(9)+'return db.ExecuteNonQuery("sp_'+@tablename+'_'+case @opertype when 2 then 'insert' else 'update' end
+'",CommandType.StoredProcedure,paras);'+CHAR(13)
set @code=@code+'}'
end
else if(@opertype=4)--删除
begin
declare @parselect1 varchar(200)
select @parselect1=ISNULL(@parselect1+',','')+
case when b.name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
when b.name in('tinyint','smallint','int') then 'int'
when b.name='bigint' then 'long'
when b.name in('datetime','smalldatetime') then 'DateTime'
when b.name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
when b.name ='bit' then 'bool'
else b.name end+' '+a.name
from syscolumns a,systypes b where a.xtype=b.xtype and a.id=OBJECT_ID(@tablename) and CHARINDEX(','+a.name+',',','+@tempwhere)>0
and b.status=0
--生成参数化
set @code='/// <summary>'+CHAR(13)
+'/// 对表'+@tablename+'中数据进行删除'+CHAR(13)
+'/// </summary>'+CHAR(13)
+'/// 以下代码有shipeng.wang存储过程自动生成 ///'+CHAR(13)
+'public bool delete('+@parselect1+')'+CHAR(13)
+'{'+CHAR(13)
+char(9)+'SqlParameter[] paras=new SqlParameter[]{'+CHAR(13)+@sqlparameters+CHAR(13)+CHAR(9)+'};'+CHAR(13)
declare @h int
set @h=0
--给参数赋值
while(CHARINDEX(',',@tempwhere)>0)
begin
set @p=substring(@tempwhere,1,CHARINDEX(',',@tempwhere)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@p)
begin
set @code=@code+char(9)+'paras['+LTRIM(@h)+'].Value='+@p+';'+CHAR(13)
set @h=@h+1
end
set @tempwhere=SUBSTRING(@tempwhere,CHARINDEX(',',@tempwhere)+1,LEN(@tempwhere))
end
--set @code=@code+char(9)+'paras[0].Value='+@keyfield+';'+CHAR(13)
set @code=@code+CHAR(9)+'return db.ExecuteNonQuery("sp_'+@tablename+'_delete",CommandType.StoredProcedure,paras);'+CHAR(13)
set @code=@code+'}'
end
end
print @code
*通过存储过程自动生成对Employee表进行增、删、改、查操作的存储过程，示例如下：*/
/*在数据库中创建测试表*/
CREATE TABLE Employee(
[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[name] varchar(50) NULL,
[sex] [varchar](2) NULL,
[phone] [varchar](20) NULL,
[city] [varchar](20) NULL,
[address] [varchar](100) NULL
)
go
测试部分：
--1、增加操作
--A、增加时添加所有字段（除主键外）
--自动生成添加存储过程
exec sp_SendCode 'Employee',@opertype=2
go
--自动生成对应的c#调用代码
exec sp_SendCode 'Employee',@type=2,@opertype=2
go
--B、如果增加操作时只添加部分字段，可以给@fields参数进行赋值,如只添加"姓名"，“性别”，“电话”字段（如果在@fields参数中，存在无效字段，则会自动过滤掉）
--自动生成添加存储过程
exec sp_SendCode 'Employee',@opertype=2,@fields='name,sex,phone'
go
--自动生成对应的c#调用代码
exec sp_SendCode 'Employee',@opertype=2,@type=2,@fields='name,sex,phone'
go
--2、删除操作
--A、根据表中的主键进行删除
--自动生成删除存储过程
exec sp_SendCode 'Employee',@opertype=4
go
--自动生成对应的c#调用代码
exec sp_SendCode 'Employee',@opertype=4,@type=2
go
--B、根据表中指定字段作为条件删除，则指定@where参数的值即可,如根据"name"和"sex"字段删除，多个字段用逗号隔开
--自动生成删除存储过程
exec sp_SendCode 'Employee',@opertype=4,@where='name,sex'
go
--自动生成对应的c#调用代码
exec sp_SendCode 'Employee',@opertype=4,@type=2,@where='name,sex'
go
--3、查询示例：为了减少代码，由存储过程自动生成的代码我就不贴了
--A、查询所有数据
--自动生成查询存储过程
exec sp_SendCode 'Employee',@opertype=1
go
--生成对应的c#调用代码
exec sp_SendCode 'Employee',@opertype=1,@type=2
go
--B、根据条件查询部分数据，条件字段用逗号隔开
--自动生成查询存储过程
exec sp_SendCode 'Employee',@opertype=1,@where='name'
--生成对应的c#调用代码
exec sp_SendCode 'Employee',@opertype=1,@type=2,@where='name'
go
--C、根据主键字段查询单条数据
--自动生成查询存储过程
exec sp_SendCode 'Employee',@opertype=1,@where='id'
--生成对应的c#调用代码
exec sp_SendCode 'Employee',@opertype=1,@type=2,@where='id'
--4、修改(和增加类似，大家自己去试吧。写累了)
exec sp_SendCode 'Employee',@opertype=3
