-- 生成数据视图类
alter procedure cv
@tablename varchar(50) = ''
as
if object_id(@tablename) is null
begin
select 'table not exists' as error
return
end
declare @tmptb table(code varchar(2048))
declare @maxcols int
declare @classname varchar(50)
declare @word varchar(50)
declare @pos int
declare @func varchar(2048)
select @maxcols = max(colid) from syscolumns where id = object_id(@tablename)
set @classname = @tablename
if( @classname <> '' and left(@classname,2) = 't_')
begin
set @classname = substring(@classname,3,len(@classname) - 2)
end
set @classname = upper(left(@classname,1)) + substring(@classname,2,len(@classname) - 1) + 'Info'
while( charindex('_',@classname) > 0 )
begin
set @pos = charindex('_',@classname)
set @word = substring(@classname,@pos + 1, 1)
set @classname = replace(@classname,'_' + @word,upper(@word) )
end
select @classname + '.cs' as 数据视图类文件名
set @func = 'public ' + @classname + '('
select @func = @func +
case type_name(xusertype)
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'decimal' then 'decimal'
when 'tinyint' then 'int'
when 'smallint' then 'int'
when 'datetime' then 'DateTime'
when 'smalldatetime' then 'DateTime'
when 'char' then 'string'
when 'nchar' then 'string'
when 'bigint' then 'long'
when 'text' then 'string'
when 'ntext' then 'string'
else type_name(xusertype)
end
+ ' '+ name +
case when colid < @maxcols then ', ' else '' end
from syscolumns where id = object_id(@tablename) order by colid
select @func = @func + ')'
insert into @tmptb select @func
insert into @tmptb select '{'
insert into @tmptb select 'this._' + name + ' = ' + name + ';' from syscolumns where id = object_id(@tablename) order by colid
insert into @tmptb select '}'
insert into @tmptb select ''
declare @parmType varchar(50)
declare @parmName varchar(50)
declare @colid int
declare @cur cursor
set @cur = cursor read_only forward_only for
select b.name,a.name,a.colid from syscolumns a inner join systypes b on a.xtype = b.xtype where a.id = object_id(@tablename)
open @cur
fetch next from @cur into @parmType,@parmName,@colid
while(@@fetch_status = 0)
begin
insert into @tmptb
select
'private ' + case @parmType
when 'decimal' then 'decimal'
when 'int' then 'int'
when 'smallint' then 'int'
when 'tinyint' then 'int'
when 'bigint' then 'long'
when 'bit' then 'int'
when 'char' then 'string'
when 'nchar' then 'string'
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'smalldatetime' then 'DateTime'
when 'datetime' then 'DateTime'
else @parmType end + ' _' + @parmName + ';'
if exists(select 1 from sysproperties where id = object_id(@tablename) and smallid = @colid)
insert into @tmptb
select '/// <summary>' union all
select '/// ' + convert(varchar,value) from sysproperties where id = object_id(@tablename) and smallid = @colid union all
select '/// </summary>'
else
insert into @tmptb
select '/// <summary>' union all
select '/// 无' union all
select '/// </summary>'
insert into @tmptb
select
'public ' + case @parmType
when 'decimal' then 'decimal'
when 'int' then 'int'
when 'smallint' then 'int'
when 'tinyint' then 'int'
when 'bigint' then 'long'
when 'bit' then 'int'
when 'char' then 'string'
when 'nchar' then 'string'
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'smalldatetime' then 'DateTime'
when 'datetime' then 'DateTime'
else @parmType end
+ ' ' + upper(left(@parmName,1)) + right(@parmName,len(@parmName) - 1)
+ ' {'
+ ' get { return _' + @parmName + '; }'
+ ' set { _' + @parmName + ' = value ; }'
+ ' }'
insert into @tmptb select ''
fetch next from @cur into @parmType,@parmName,@colid
end
select code as 类字段和属性 from @tmptb
GO
/*
调用示例:
cp t_goods
生成常用存储过程
*/
ALTER procedure cp
@tablename varchar(50) = '' -- 表名称
as
declare @tableid int
set @tableid = object_id(@tablename)
if @tableid is null
begin
select 'table not exists' as error
return
end
declare @tmptb table(code varchar(4000))
declare @fieldsName varchar(4000)
declare @parmName varchar(4000)
declare @keyname varchar(50)
declare @proctablename varchar(50)
declare @ident_seed int
select @ident_seed = ident_seed(@tablename)
set @proctablename = @tablename
if( left(@proctablename,2) ='t_')
set @proctablename = substring(@proctablename,3,len(@proctablename) - 2)
print @proctablename
select top 1 @keyname= [name] from syscolumns where id = @tableid order by colid
set @fieldsname = ''
select @fieldsname = @fieldsname + a.[name] + ','
from syscolumns a inner join systypes b on a.xtype = b.xtype
where id = @tableid and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid
if( right(@fieldsname,1) = ',' )
set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1)
set @parmname = ''
select @parmname = @parmname + '@' + a.[name] + ','
from syscolumns a inner join systypes b on a.xtype = b.xtype
where id = @tableid and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid
if( right(@parmname,1) = ',' )
set @parmname = substring(@parmname,1,len(@parmname) - 1)
-- proc_pagelist1
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_list'
insert into @tmptb
select '@page int = 1, --当前页' union all
select '@pagesize int = 10, --每页显示' union all
select '@total int = 0 output, --总记录数' union all
select '@typeid int = 0 ''这里需要修改''' union all
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
insert into @tmptb
select 'if( @total = 0 )' union all
select ' select @total = count(1) from ' + @tablename + ' where typeid = @typeid ''这里需要修改''' union all
select '' union all
select 'if( @page = 1 )' union all
select 'begin' union all
select ' set rowcount @pagesize' union all
select ' select * from ' + @tablename + ' where typeid = @typeid ''这里需要修改'' order by ' + @keyname + ' desc ' union all
select ' set rowcount 0' union all
select ' return' union all
select 'end' union all
select 'else' union all
select 'begin' union all
select ' declare @tb table(rownum int identity(1,1),keyid int)' union all
select ' insert into @tb(keyid) select ' + @keyname + ' from ' + @tablename union all
select ' where typeid = @typeid ''这里需要修改'' order by ' + @keyname + ' desc' union all
select ' select a.* from ' + @tablename + ' a inner join @tb b on a.'+ @keyname + ' = b.keyid ' union all
select ' where b.rownum > @pagesize * (@page - 1) and b.rownum <= @pagesize * @page' union all
select 'end'
insert into @tmptb select '' union all select 'go'
select code as proc_pagelist1 from @tmptb
-- proc_pagelist2
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_list'
insert into @tmptb
select '@page int = 1, --当前页' union all
select '@pagesize int = 10, --每页显示' union all
select '@total int = 0 output, --总记录数' union all
select '@typeid int = 0 ''这里需要修改''' union all
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
insert into @tmptb
select 'declare @tablename varchar(1024)' union all
select 'declare @orderby varchar(1024)' union all
select 'declare @orderby2 varchar(1024)' union all
select 'declare @orderbyclause varchar(1024)' union all
select 'declare @keyname varchar(50)' union all
select 'declare @keyinorder tinyint' union all
select 'declare @whereclause varchar(1024)' union all
select 'declare @showclause varchar(1024)' union all
select '' union all
select '-- 表名称' union all
select 'set @tablename = ''' + @tablename + ''' ' union all
select '--排序字段,字段前面加b.,字段后加 desc或asc,格式如 b.rid desc,b.rname asc' union all
select 'set @orderby = ''b.' + @keyname + ' desc'' ' union all
select '--排序字段,字段前面加b.,字段后面和上面的orderby正好相反,格式如 b.rid asc,b.ranme desc' union all
select 'set @orderby2 = ''b.' + @keyname + ' asc'' ' union all
select '--排序字段，这里不需要加 asc 和 desc，格式如 b.rid,b.rname' union all
select 'set @orderbyclause = ''b.' + @keyname + '''' union all
select '--主键是否在排序字段里，1 是 0 不是，一般是1' union all
select 'set @keyinorder = 1' union all
select '--这个表的主键' union all
select 'set @keyname = ''' + @keyname + '''' union all
select '--查询条件' union all
select 'set @whereclause = '' where typeid = '' + convert(varchar,@typeid) ' + '''这里需要修改''' union all
select 'set @showclause = ''a.*''' union all
select '' union all
select '--调用分页存储过程' union all
select 'exec P_SplitPageOneSql_v2 @tablename,@pagesize,@page,@orderby,@orderby2,' union all
select '@orderbyclause,@keyname,@keyinorder,@whereclause,@showclause,@total output'
insert into @tmptb select '' union all select 'go'
select code as proc_pagelist2 from @tmptb
-- proc_insert
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_insert'
insert into @tmptb
select
'@' + a.name + ' ' +
case b.name
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''','
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''','
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''','
when 'datetime' then 'datetime,'
when 'smalldatetime' then 'smalldatetime,'
else b.name + ' = 0,' end
from syscolumns a inner join systypes b
on a.xtype = b.xtype
where a.id = object_id(@tablename)
and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid
insert into @tmptb
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
insert into @tmptb
select 'insert into ' + @tablename + '(' + @fieldsname + ')' union all
select ' values(' + @parmname + ')'
insert into @tmptb select '' union all select 'go'
select code as proc_insert from @tmptb
-- proc_update
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_update'
insert into @tmptb
select
'@' + a.name + ' ' +
case b.name
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''','
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''','
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''','
when 'datetime' then 'datetime,'
when 'smalldatetime' then 'smalldatetime,'
else b.name + ' = 0,' end
from syscolumns a inner join systypes b
on a.xtype = b.xtype
where a.id = object_id(@tablename)
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid
insert into @tmptb
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
set @fieldsname = ''
select @fieldsname = @fieldsname + a.[name] + '=' + '@' + a.[name] + ','
from syscolumns a inner join systypes b on a.xtype = b.xtype
where id = @tableid and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid
if( right(@fieldsname,1) = ',' )
set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1)
insert into @tmptb
select 'update ' + @tablename union all
select ' set ' + @fieldsname union all
select ' where ' + @keyname + '=@' + @keyname
insert into @tmptb select '' union all select 'go'
select code as proc_update from @tmptb
-- proc_delete
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_delete'
insert into @tmptb
select top 1
'@' + a.name + ' ' +
case b.name
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''''
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''''
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''''
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''''
else b.name + ' = 0' end
from syscolumns a inner join systypes b
on a.xtype = b.xtype
where a.id = object_id(@tablename)
order by a.colid
insert into @tmptb
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
insert into @tmptb select 'delete from ' + @tablename + ' where ' + @keyname + '=@' + @keyname
insert into @tmptb select '' union all select 'go'
select code as proc_delete from @tmptb
-- proc_detail
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_detail'
insert into @tmptb
select top 1
'@' + a.name + ' ' +
case b.name
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''''
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''''
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''''
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''''
else b.name + ' = 0' end
from syscolumns a inner join systypes b
on a.xtype = b.xtype
where a.id = object_id(@tablename)
order by a.colid
insert into @tmptb
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
insert into @tmptb select 'select * from ' + @tablename + ' where ' + @keyname + '=@' + @keyname
insert into @tmptb select '' union all select 'go'
select code as proc_detail from @tmptb
-- proc_listall
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_listall'
insert into @tmptb
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''
insert into @tmptb
select 'select * from ' + @tablename
insert into @tmptb select '' union all select 'go'
select code as proc_listall from @tmptb
GO
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
