從網路上看到的文章,免安裝任何套件可以在SQL TABLE建立後透過純SQL指令產生出C#的ORM(Object Relational Mapping)或稱DTO(Data Transfer Object),基於該文章去除掉一些自己不會用到的冗餘部分並且把欄位註解也標註上去,拾人牙慧將資訊摘錄至此。
基本上把下面這一段貼到SSMS上面,設定好前兩行:CatalogName、TableName,按下執行後就可以把既有的SQL資料表轉換成C#類別了。
USE YourSQLCatalogName DECLARE @Table NVARCHAR(MAX) = 'YourSQLTableName' DECLARE @Result NVARCHAR(MAX) = '' SET @Result = 'public class ' + @Table + CHAR(13) + '{' + CHAR(13) SELECT @Result = @Result + ' //' + cDescription + CHAR(13) + ' public ' + cDataType + ' ' + cName + ' { get; set; }' + CHAR(13) FROM ( SELECT c.COLUMN_NAME AS cName, CASE c.DATA_TYPE WHEN 'bigint' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'long?' ELSE 'long' END WHEN 'binary' THEN 'Byte[]' WHEN 'bit' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'bool?' ELSE 'bool' END WHEN 'char' THEN 'string' WHEN 'date' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END WHEN 'datetime' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END WHEN 'datetime2' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END WHEN 'datetimeoffset' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END WHEN 'decimal' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END WHEN 'float' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'double?' ELSE 'double' END WHEN 'image' THEN 'Byte[]' WHEN 'int' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'int?' ELSE 'int' END WHEN 'money' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END WHEN 'nchar' THEN 'string' WHEN 'ntext' THEN 'string' WHEN 'numeric' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END WHEN 'nvarchar' THEN 'string' WHEN 'real' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'double?' ELSE 'double' END WHEN 'smalldatetime' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END WHEN 'smallint' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'short?' ELSE 'short' END WHEN 'smallmoney' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END WHEN 'text' THEN 'string' WHEN 'time' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END WHEN 'timestamp' THEN 'Byte[]' WHEN 'tinyint' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END WHEN 'uniqueidentifier' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'Guid?' ELSE 'Guid' END WHEN 'varbinary' THEN 'Byte[]' WHEN 'varchar' THEN 'string' ELSE 'Object' END AS cDataType, CONVERT(NVARCHAR(MAX), e.value) AS cDescription FROM INFORMATION_SCHEMA.COLUMNS c LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES e ON e.major_id = OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME) AND e.minor_id = c.ORDINAL_POSITION AND e.name = 'MS_Description' WHERE c.TABLE_NAME = @Table ) t SET @Result = @Result + '}' PRINT @Result
以下是轉換出來的效果範例,還不錯:
public class YourSQLTableName { //自動編號 public int iAutoIndex { get; set; } //身分證號 public string cID { get; set; } //張貼日期 public DateTime? dPost { get; set; } }MSSQL SqlToCSharpClass SqlToC#Class SqlToDTO SqlToORM SqlToEntityModel