根据表名，自动生成增、删、改参数化存储过程和调用代码

-- Author:  shipeng.wang
-- Create date: 2010-12-31
-- Description: 根据表名，自动生成增、删、改参数化存储过程和调用代码
-- =============================================
create proc [dbo].[sp_SendCode]
@tablename varchar(20),  --表名
@type int=1,    --类型（1：存储过程，2：调用代码）
@opertype int=1,         --操作类型（1：查，2：增，3：改，4：删）
@fields varchar(200)='*',--要操作的列（查、增、改时有效，默认操作所有列，多个列用英文逗号隔开）
@where varchar(100)=''   --要作为条件的列（查、改、删时有效，默认为空，多个列名用英文逗号隔开，如：字段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)
 
 
 --进行非空处理
 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+name+'=@'+name+',' 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(@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_select_'+@tablename+case @where when '' 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_insert_'+@tablename+']'+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_update_'+@tablename+']'+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
   --如果没有有效条件，则将主键作为条件
   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+',',','+@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_delete_'+@tablename+']'+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(1000),@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
    --生成参数化
    set @code='/// <summary>'+CHAR(13)
       +'/// 对表'+@tablename+'中数据进行查询 '+CHAR(13)
       +'/// </summary>'+CHAR(13)
       +'public DataSet GetList('+@parselect+')'+CHAR(13)
       +'{'+CHAR(13)
       +char(9)+'SqlParameter[] paras=new SqlParameter[]{'+CHAR(13)+@sqlparameters+CHAR(13)+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
        set @code=@code+char(9)+'paras['+LTRIM(@j)+'].Value=model.'+@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_select_'+@tablename+'_one'+'",CommandType.StoredProcedure,paras);'+CHAR(13)
       +'}'
   end
   else
    --生成参数化
    set @code='/// <summary>'+CHAR(13)
       +'/// 对表'+@tablename+'中数据进行查询 '+CHAR(13)
       +'/// </summary>'+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)
      +'/// <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_'+case @opertype when 2 then 'insert' else 'update' end +'_'
   +@tablename+'",CommandType.StoredProcedure,paras);'+CHAR(13)
   set @code=@code+'}'
  end
  else if(@opertype=4)--删除
  begin
    set @sqlparameters=char(9)+char(9)+char(9)+char(9)+char(9)+'new SqlParameter("@'+@keyfield+'",'
    +'SqlDbType.'+case @partype when 'varchar' then 'VarChar' when 'bigint' then 'BigInt'
    when 'datetime' then 'DateTime' when 'nvarchar' then 'NVarChar' when 'numeric' then 'Decimal'
    when 'tinyint' then 'TinyInt'
    when 'bigint' then 'long'
    else UPPER(left(@partype,1))+RIGHT(@partype,len(@partype)-1) end+');'
    
    if(@sqlparameters!='')
     set @sqlparameters=STUFF(@sqlparameters,len(@sqlparameters),1,'')
    
    --生成参数化
    set @code='/// <summary>'+CHAR(13)
       +'/// 对表'+@tablename+'中数据进行删除'+CHAR(13)
       +'/// </summary>'+CHAR(13)
       +'/// <param name="'+@keyfield+'">要删除的主键</param>'+CHAR(13)
       +'public bool delete('+case @partype when 'varchar' then 'VarChar' when 'bigint' then 'long'
    when 'datetime' then 'DateTime' when 'nvarchar' then 'NVarChar' when 'numeric' then 'Decimal'
    when 'tinyint' then 'TinyInt'
    else UPPER(left(@partype,1))+RIGHT(@partype,len(@partype)-1) end +' '+@keyfield+')'+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)+'return db.ExecuteNonQuery("sp_delete_'+@tablename+'",CommandType.StoredProcedure,paras);'+CHAR(13)
    set @code=@code+'}'
  end
 end
 print @code