Поскольку объекты POCO часто представляют собой таблицы, у меня есть решение для создания классов на основе таблицы SQL в MS SQL Management Studio.
Сначала создайте этот хранимый процесс:
CREATE PROC [dbo].[CreateClass](
@Schema VARCHAR(100),
@TableName VARCHAR(100),
@Type VARCHAR(2) = NULL
)
AS
IF @Type IS NULL
SET @Type = 'T'
SET NOCOUNT ON
DECLARE @TableInfo TABLE (
ColumnName VARCHAR(100),
ColumnPosition INT,
ColumnTypeCS VARCHAR(100),
ColumnTypeTS VARCHAR(100),
NullableSign CHAR(1)
)
INSERT INTO @TableInfo
SELECT
replace(COLUMN_NAME, ' ', '_') ,
ORDINAL_POSITION AS ColumnPosition,
CASE DATA_TYPE
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 'double'
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 'DateTime'
WHEN 'tinyint' THEN 'byte'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'varbinary' THEN 'byte[]'
WHEN 'varchar' THEN 'string'
ELSE 'UNKNOWN_' + DATA_TYPE
END AS ColumnTypeCS,
CASE DATA_TYPE
WHEN 'bigint' THEN 'number'
WHEN 'binary' THEN 'any'
WHEN 'bit' THEN 'boolean'
WHEN 'char' THEN 'string'
WHEN 'date' THEN 'Date'
WHEN 'datetime' THEN 'Date'
WHEN 'datetime2' THEN 'Date'
WHEN 'datetimeoffset' THEN 'Date'
WHEN 'decimal' THEN 'number'
WHEN 'float' THEN 'number'
WHEN 'image' THEN 'any'
WHEN 'int' THEN 'number'
WHEN 'money' THEN 'number'
WHEN 'nchar' THEN 'string'
WHEN 'ntext' THEN 'string'
WHEN 'numeric' THEN 'number'
WHEN 'nvarchar' THEN 'string'
WHEN 'real' THEN 'number'
WHEN 'smalldatetime' THEN 'Date'
WHEN 'smallint' THEN 'number'
WHEN 'smallmoney' THEN 'number'
WHEN 'text' THEN 'string'
WHEN 'time' THEN 'number'
WHEN 'timestamp' THEN 'number'
WHEN 'tinyint' THEN 'number'
WHEN 'uniqueidentifier' THEN 'string'
WHEN 'varbinary' THEN 'any'
WHEN 'varchar' THEN 'string'
ELSE 'UNKNOWN_' + DATA_TYPE
END ColumnTypeTS,
CASE
WHEN IS_NULLABLE = 'YES' and DATA_TYPE in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
THEN '?'
ELSE ''
END NullableSign
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @Schema
DECLARE @Result VARCHAR(MAX) = 'public class ' + @TableName + '
{'
SELECT @Result = @Result + '
public ' + ColumnTypeCS + NullableSign + ' ' + ColumnName + ' { get; set; }
'
FROM @TableInfo
ORDER BY ColumnPosition
SET @Result = @Result + '
}'
IF CHARINDEX('C', @Type) > 0
PRINT @Result
UPDATE @TableInfo
SET ColumnName = LOWER(SUBSTRING(ColumnName, 1, 1)) + SUBSTRING(ColumnName, 2, LEN(ColumnName))
SET @Result = '
export interface I' + @TableName + '
{'
SELECT @Result = @Result + '
'+ColumnName + '?: ' + ColumnTypeTS + ';'
FROM @TableInfo
ORDER BY ColumnPosition
SET @Result = @Result + '
}
'
IF CHARINDEX('T', @Type) > 0
PRINT @Result
SET @Result = '
export class ' + @TableName + ' implements I'+@TableName+'
{'
SELECT @Result = @Result + '
'+ColumnName + '?: ' + ColumnTypeTS + ';'
FROM @TableInfo
ORDER BY ColumnPosition
SET @Result = @Result + '
constructor(recoverFrom: '+@TableName+' | I'+@TableName+') {
super(recoverFrom);
}
}
'
PRINT @Result
GO
использовать, просто выполните это
EXEC dbo.CreateClass @Schema = 'dbo', @TableName = 'MyTable', @Type = 'T'
or
EXEC dbo.CreateClass @Schema = 'dbo', @TableName = 'MyTable', @Type = 'C'
or
EXEC dbo.CreateClass @Schema = 'dbo', @TableName = 'MyTable'
и увидеть результат в C#, Typescript или обоих
05.12.2019
