根据数据自动生成SQL代码(基于企业库)

 

-- =============================================
-- Author:  shipeng.wang
-- Create date: 2010-12-31
-- Description: 根据表名，自动生成增、删、改参数化存储过程和调用代码(用于生成企业库的调用代码)
-- =============================================
create proc [dbo].[sp_SendCode_qyk]
@tablename varchar(20),  --表名
@type int=1,    --类型（1：存储过程，2：调用代码）
@opertype int=1,         --操作类型（1：查，2：增，3：改，4：删）
@fields varchar(1000)='*',--要操作的列（查、增、改时有效，默认操作所有列，多个列用英文逗号隔开）
@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)+
   'db.AddInParameter(command, "@'+a.name+'",DbType.'+
   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 UPPER(left(b.name,1))+RIGHT(b.name,len(b.name)-1) end+',model.'+a.name+');'+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
   --db.AddInParameter(command, "@typename", DbType.String, model.TypeName);
   --声明参数
   select @sqlparameters=@sqlparameters+CHAR(9)+
   'db.AddInParameter(command, "@'+a.name+'",DbType.'+
   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 UPPER(left(b.name,1))+RIGHT(b.name,len(b.name)-1) end+','+
   case when @opertype=3 then 'model.' else '' end +a.name+');'+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(@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)+'DbCommand command = db.GetStoredProcCommand("sp_'+@tablename+'_select");'+CHAR(13)
        +@sqlparameters+CHAR(13)+CHAR(9)+CHAR(13)
        +CHAR(9)+'return db.ExecuteDataSet(command);'+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)+'DbCommand command = db.GetStoredProcCommand("sp_'+@tablename+'_select_one");'+CHAR(13)
        +@sqlparameters+CHAR(13)+CHAR(9)+CHAR(13)
        set @code=@code+CHAR(9)+'IDataReader read= db.ExecuteReader(command);'+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)
        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(CommandType.Text, sql);'+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)+'DbCommand command = db.GetStoredProcCommand("sp_'+@tablename+'_'+case @opertype when 2 then 'insert' else 'update' end+'");'+CHAR(13)
      +@sqlparameters+CHAR(13)
   set @code=@code+CHAR(9)+'return db.ExecuteNonQuery(command) > 0 ? true : false;'+CHAR(13)
   set @code=@code+'}'
  end
  else if(@opertype=4)--删除
  begin
    set @sqlparameters=char(9)+'db.AddInParameter(command, "@'+@keyfield+'",DbType.'+
    case when @partype in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'String'
    when @partype in('tinyint','smallint','int') then 'Int32'
    when @partype='bigint' then 'Int64'
    when @partype in('datetime','smalldatetime') then 'DateTime'
    when @partype in('float','decimal','numeric','money','real','smallmoney') then 'Decimal'
    when @partype ='bit' then 'Boolean'
    else UPPER(left(@partype,1))+RIGHT(@partype,len(@partype)-1) end+','+@keyfield+');'
    
    --生成参数化
    set @code='/// <summary>'+CHAR(13)
       +'/// 对表'+@tablename+'中数据进行删除'+CHAR(13)
       +'/// </summary>'+CHAR(13)
       +'/// 以下代码由shipeng.wang代码生成器自动生成///'+CHAR(13)
       +'/// <param name="'+@keyfield+'">要删除的主键</param>'+CHAR(13)
       +'public bool delete('+case when @partype in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'String'
      when @partype in('tinyint','smallint','int') then 'int'
      when @partype='bigint' then 'long'
      when @partype in('datetime','smalldatetime') then 'DateTime'
      when @partype in('float','decimal','numeric','money','real','smallmoney') then 'Decimal'
      when @partype ='bit' then 'bool'
      else UPPER(left(@partype,1))+RIGHT(@partype,len(@partype)-1) end +' '+@keyfield+')'+CHAR(13)
      +'{'+CHAR(13)
      +char(9)+'DbCommand command = db.GetStoredProcCommand("sp_'+@tablename+'_delete");'+CHAR(13)
      +@sqlparameters+CHAR(13)+CHAR(9)+CHAR(13)
      set @code=@code+CHAR(9)+'return db.ExecuteNonQuery(command) > 0 ? true : false;'+CHAR(13)
      +'}'
  end
 end
 print @code