☆常用SQL脚本☆
/*CreateEntity*/
USE EasySales --数据库:EasySales
GO
Declare @MyEntity varchar(100)
Set @MyEntity = 'myAddrListEntity' --命名实体
Declare @MyDataTable varchar(100) --数据源[DataTable]
Set @MyDataTable = 'ds.Tables(0)'
Declare @MyTable varchar(100) --数据库表:AL_AddrListCataLog
Set @MyTable = 'AL_AddrListCataLog'
SELECT (case when a.colorder=1 then 'Public Const Table_' + d.name + ' As String = "' + d.name + '"' else '' end) 表映射类名,
'Public Const ' + UPPER(a.name) + ' AS String="' + (a.name) + '"' AS 字段映射常量,
case when b.name='int' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.Int64))'
when b.name='datetime' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.DateTime))'
else '.Add(Me.' + UPPER(a.name) + ' , GetType(System.String))' end
+ case a.isnullable when 0 then '' else '.AllowDBNull = True' end,
'.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ') = ' + @MyDataTable + '.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ')',
b.name 字段类型, a.isnullable
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
---------------------------------------
/*联结字段*/
DECLARE @AAA VARCHAR(8000)
SET @AAA=''
select @AAA=@AAA+
CASE a.isnullable WHEN 1 THEN
'Isnull([' + a.name + '],' +
Case b.name When 'int' Then '0' When 'bit' Then '0' When 'tinyint' Then '0' When 'money' Then '0.00' When 'datetime' Then '''1900-1-1''' When 'uniqueidentifier' Then '''00000000-0000-0000-0000-000000000000''' Else '''''' End
+ ') AS ['+a.name+ '], '
ELSE
'['+ a.name + '], '
END
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name=@MyTable --数据库表[AL_AddressList]
order by a.id
PRINT @AAA
USE EasySales --数据库:EasySales
GO
Declare @MyEntity varchar(100)
Set @MyEntity = 'myAddrListEntity' --命名实体
Declare @MyDataTable varchar(100) --数据源[DataTable]
Set @MyDataTable = 'ds.Tables(0)'
Declare @MyTable varchar(100) --数据库表:AL_AddrListCataLog
Set @MyTable = 'AL_AddrListCataLog'
SELECT (case when a.colorder=1 then 'Public Const Table_' + d.name + ' As String = "' + d.name + '"' else '' end) 表映射类名,
'Public Const ' + UPPER(a.name) + ' AS String="' + (a.name) + '"' AS 字段映射常量,
case when b.name='int' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.Int64))'
when b.name='datetime' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.DateTime))'
else '.Add(Me.' + UPPER(a.name) + ' , GetType(System.String))' end
+ case a.isnullable when 0 then '' else '.AllowDBNull = True' end,
'.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ') = ' + @MyDataTable + '.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ')',
b.name 字段类型, a.isnullable
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
---------------------------------------
/*联结字段*/
DECLARE @AAA VARCHAR(8000)
SET @AAA=''
select @AAA=@AAA+
CASE a.isnullable WHEN 1 THEN
'Isnull([' + a.name + '],' +
Case b.name When 'int' Then '0' When 'bit' Then '0' When 'tinyint' Then '0' When 'money' Then '0.00' When 'datetime' Then '''1900-1-1''' When 'uniqueidentifier' Then '''00000000-0000-0000-0000-000000000000''' Else '''''' End
+ ') AS ['+a.name+ '], '
ELSE
'['+ a.name + '], '
END
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name=@MyTable --数据库表[AL_AddressList]
order by a.id
PRINT @AAA
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)