瀏覽分類:

MS SQL

MS SQL 快速產生 C# 類別

https://habilisbest.com/creating-c-model-class-from-sql-query

 

DECLARE @cnt INT; 

SET @cnt = 1; 

DECLARE @tableCount INT; 

SET @tableCount = (SELECT Count(*) 
                   FROM   sys.tables 
                   WHERE  type = 'U') 

WHILE @cnt <= @tableCount 
BEGIN
	--逐一取得資料表的名稱
	declare @tbName nvarchar(200);
	set @tbName = (
	SELECT name FROM
	(SELECT  ROW_NUMBER() OVER(ORDER BY name) AS ROWID , name
	FROM sys.Tables
	WHERE type = 'U') mytable
	WHERE ROWID = @cnt)

--原作者的 code 產生 csharp 類別
--這段不要 format 產出來才好看
declare @TableName sysname = @tbName
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
	public ' + (CASE WHEN ColumnName = 'RowVersion' THEN 'byte[]' ELSE ColumnType END) + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
	select 
		replace(col.name, ' ', '_') ColumnName,
		column_id ColumnId,
		case typ.name 
			when 'bigint' then 'long'
			when 'binary' then 'byte[]'
			when 'bit' then 'bool'
			when 'char' then 'string'
			when 'date' then 'DateTime'
			when 'datetime' then 'DateTime'
			when 'datetime2' then 'DateTime'
			when 'datetimeoffset' then 'DateTimeOffset'
			when 'decimal' then 'decimal'
			when 'float' then 'float'
			when 'image' then 'byte[]'
			when 'int' then 'int'
			when 'money' then 'decimal'
			when 'nchar' then 'string'
			when 'ntext' then 'string'
			when 'numeric' then 'decimal'
			when 'nvarchar' then 'string'
			when 'real' then 'double'
			when 'smalldatetime' then 'DateTime'
			when 'smallint' then 'short'
			when 'smallmoney' then 'decimal'
			when 'text' then 'string'
			when 'time' then 'TimeSpan'
			when 'timestamp' then 'timestamp'
			when 'rowversion' then 'byte[]'
			when 'tinyint' then 'byte'
			when 'uniqueidentifier' then 'Guid'
			when 'varbinary' then 'byte[]'
			when 'varchar' then 'string'
			else 'UNKNOWN_' + typ.name
		end ColumnType,
		case 
			when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
			then '?' 
			else '' 
		end NullableSign
	from sys.columns col
		join sys.types typ on
			col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
	where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

	print @Result

	--遞增
	set @cnt += 1
END