C#-Models für alle Tabellen einer SQL-Server-Datenbank generieren

net
sqlserver
csharp
t-sql
Tags: #<Tag:0x00007fa1e9bca1a0> #<Tag:0x00007fa1e9bca060> #<Tag:0x00007fa1e9bc9f20> #<Tag:0x00007fa1e9bc9de0>

#1

Manchmal ist es sinnvoll, Models/POCOs aus einer bestehenden Datenbank zu generieren, z. B. um sie mit Dapper zu verwenden.

Hier ist ein T-SQL-Fragment, um das zu erledigen:

-- Generates POCOs/models for all tables in the current database.
-- Useful for later processing e. g. with Dapper.
-- 
-- See https://stackoverflow.com/a/5873231/107625 for the original idea
-- See http://midnightprogrammer.net/post/use-sql-query-to-writecreate-a-file for the SP to write to file.

declare @TableName sysname 
declare @Result nvarchar(MAX) = ''


DECLARE table_cursor CURSOR FOR 
SELECT TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]

OPEN table_cursor

FETCH NEXT FROM table_cursor 
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN


-- https://stackoverflow.com/a/5873231/107625

select @Result = @Result + '[Table(@"' + @TableName + '")]
public class ' + @TableName + '
{'

	select @Result = @Result + '
    public ' + ColumnType + 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 'DateTime'
						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  + '
}
			
'


    FETCH NEXT FROM table_cursor 
    INTO @tableName
END
CLOSE table_cursor
DEALLOCATE table_cursor

-- http://midnightprogrammer.net/post/use-sql-query-to-writecreate-a-file
EXEC USP_SaveFile @Result, 'C:\Ablage\POCOS.cs'

-- PRINT is truncated, therefore see the above saved file for the full POCOs.
print @Result

Das ganze ist inspiriert von:


Zum Konfigurieren der Datei-Speichern-Funktion ist als Ergänzung zum oben verlinkten Blog-Artikel Nachfolgendes vollständiger:

Use master
GO
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1; 
GO 
RECONFIGURE; 
GO