【SQL】常用SQL

declare @table varchar(1000)

declare @t_num int

set @table = 'Wx_AssistACT'

set @t_num=1

--生成model （实体类）

select 'public ' +
case when c.name in ('nvarchar', 'varchar') then 'string'
when c.name='datetime' then 'DateTime'
else c.name
end + ' ' + b.name +
' {get;set;}' neirong1
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id

--字段逗号隔开
select (
select b.name + ', '
--select '@' + b.name + ', '
--select b.name + '=@' + b.name + ', '
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id
for xml path('')
)

 

select (
--select b.name + ', '
select '@' + b.name + ', '
--select b.name + '=@' + b.name + ', '
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id
for xml path('')
)

select (
--select b.name + ', '
--select '@' + b.name + ', '
select b.name + '=@' + b.name + ', '
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id
for xml path('')
)

--new SqlParameter的写法

select 'new SqlParameter("@' + b.name + '", SqlDbType.' +
case when c.name in ('nvarchar', 'varchar') then 'NVarChar,'+''+Convert(varchar(200),b.max_length/2)+''
when c.name='datetime' then 'DateTime'
when c.name='Decimal' then 'Decimal,18'
else 'Int,4'
end + '),' neirong1
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id

 

--给Params赋值
select 'parameters['+
cast(cast(ROW_NUMBER() OVER(order by b.column_id asc) as int)-@t_num as varchar) +
'].Value = model.' + b.name + ';' as neirong1
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id

--生成一个实体时赋值写法
select 'model.'+b.name+' =(ds.Tables[0].Rows[0]["' + b.name + '"] != DBNull.Value) ?' +
case when c.name = 'int' then ' Convert.ToInt32(ds.Tables[0].Rows[0]["' + b.name + '"]) : -1;'
when c.name = 'nvarchar' then ' ds.Tables[0].Rows[0]["' + b.name + '"].ToString() : "";'
when c.name = 'datetime' then ' Convert.ToDateTime(ds.Tables[0].Rows[0]["' + b.name + '"]) : DateTime.Parse("1900-01-01");'
when c.name = 'bit' then ' Convert.ToBoolean(ds.Tables[0].Rows[0]["' + b.name + '"]):false;'
when c.name = 'decimal' then ' Convert.ToDecimal(ds.Tables[0].Rows[0]["' + b.name + '"]):-1;'
end
neirong1
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id

 

 

 

 

/*旧版*/

select 'private ' +
case when c.name in ('nvarchar', 'varchar') then 'string'
when c.name='datetime' then 'DateTime'
else c.name
end + ' _' + b.name + ';' neirong1
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id
and b.system_type_id=c.system_type_id
and a.name=@table
and c.name!='sysname'
order by b.column_id