I am working on a spike that requires fetching metadata views from Sql Server's sys and INFORMATION_SCHEMA schemas. Initially, coding a few DTO by hand was no problem but as new views are required it obviously became tedious and typo prone.
I really did not want to break out ANY ORM, I just needed to read the data and a simple reflective filler would do just fine. In any case, I found myself in a query window writing a SQL based DTO generator. It ultimately ended up including a lot of clever functionality and I will present it here but first lets start with the simplest implementation that results in classes similar to the following snippet...
public partial class Categories
{
#region Properties
private int _CategoryID;
public virtual int CategoryID
{
get { return _CategoryID; }
set { _CategoryID = value; }
}
private string _CategoryName;
public virtual string CategoryName
{
get { return _CategoryName; }
set { _CategoryName = value; }
}
private string _Description;
public virtual string Description
{
get { return _Description; }
set { _Description = value; }
}
private byte[] _Picture;
public virtual byte[] Picture
{
get { return _Picture; }
set { _Picture = value; }
}
#endregion
}
DeadSimpleDTO.sql generates a simple property bag DTO for each Table and View in the current database. It does NOT account for identifiers that are C# reserved words and replaces embedded spaces with underscores. If the data you wish to model demonstrates either of these characteristics you may wish to wait for the next post.
The plan is very simple and consists of 3 parts: Run a cursor over a list of Tables and Views; Run a nested cursor over the Columns of the current object; Translate the sql data type to the equivalent CLR type.
The queries:
Get Tables and Views:
-- get all tables and views in currrent database
SELECT o.[object_id],
s.[name] AS 'schema',
o.[name],
o.[type]
FROM [sys].[objects] o
INNER JOIN [sys].[schemas] AS s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ( 'U', 'V' )
ORDER BY s.[name], o.[type]
Output:
object_id schema name type
----------- -------- ----------------------------------- ----
21575115 dbo Categories U
53575229 dbo Customers U
85575343 dbo Shippers U
117575457 dbo Suppliers U
149575571 dbo Orders U
245575913 dbo Products U
453576654 dbo Order Details U
981578535 dbo CustomerCustomerDemo U
997578592 dbo CustomerDemographics U
1013578649 dbo Region U
1029578706 dbo Territories U
1045578763 dbo EmployeeTerritories U
2105058535 dbo Employees U
613577224 dbo Customer and Suppliers by City V
629577281 dbo Alphabetical list of products V
645577338 dbo Current Product List V
661577395 dbo Orders Qry V
677577452 dbo Products Above Average Price V
693577509 dbo Products by Category V
709577566 dbo Quarterly Orders V
725577623 dbo Invoices V
741577680 dbo Order Details Extended V
757577737 dbo Order Subtotals V
773577794 dbo Product Sales for 1997 V
789577851 dbo Category Sales for 1997 V
805577908 dbo Sales by Category V
821577965 dbo Sales Totals by Amount V
837578022 dbo Summary of Sales by Quarter V
853578079 dbo Summary of Sales by Year V
Get columns for current object:
-- get relevant column data for a table
SELECT c.[column_id],
c.[name],
t.[name] AS 'type',
c.[is_nullable]
FROM [sys].[all_columns] c
LEFT JOIN [sys].[types] t ON c.[system_type_id] = t.[system_type_id]
WHERE c.[object_id] = 21575115 -- <-- replace with valid object_id from your database
AND NOT t.[name] = 'sysname' -- <-- system objects dupe sysname with nvarchar
ORDER BY c.[column_id]
Output:
column_id name type is_nullable
----------- --------------- ----------- -----------
1 CategoryID int 0
2 CategoryName nvarchar 0
3 Description ntext 1
4 Picture image 1
The conversion code:
-- translate SQL type to CLR type
SET @property_type = CASE @column_type
WHEN 'image' THEN 'byte[]'
WHEN 'text' THEN 'string'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'date' THEN 'DateTime'
WHEN 'time' THEN 'TimeSpan'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'datetimeoffset'
THEN 'DateTimeOffset'
WHEN 'tinyint' THEN 'byte'
WHEN 'smallint' THEN 'short'
WHEN 'int' THEN 'int'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'real' THEN 'Single'
WHEN 'money' THEN 'decimal'
WHEN 'datetime' THEN 'DateTime'
WHEN 'float' THEN 'double'
WHEN 'sql_variant' THEN 'object' -- if you know the underlying type, use that
WHEN 'ntext' THEN 'string'
WHEN 'bit' THEN 'bool'
WHEN 'decimal' THEN 'decimal'
WHEN 'numeric' THEN 'decimal'
WHEN 'smallmoney' THEN 'decimal'
WHEN 'bigint' THEN 'long'
WHEN 'hierarchyid'
THEN 'Microsoft.SqlServer.Types.SqlHierarchyId'
WHEN 'geometry' THEN 'Microsoft.SqlServer.Types.SqlGeometry'
WHEN 'geography' THEN 'Microsoft.SqlServer.Types.SqlGeography'
WHEN 'varbinary' THEN 'byte[]'
WHEN 'varchar' THEN 'string'
WHEN 'binary' THEN 'byte[]'
WHEN 'char' THEN 'string'
WHEN 'timestamp' THEN 'byte[]'
WHEN 'nvarchar' THEN 'string'
WHEN 'nchar' THEN 'string'
WHEN 'xml' THEN 'string' -- TODO: figure how to handle.. string?
WHEN 'sysname' THEN 'string'
ELSE 'object'
END
-- TODO: handle FILESTREAM attribute varbinary(max) Byte[]
-- if the CLR type is a reference type, ignore nullable bit on source field
IF ( @property_type IN ( 'object', 'string', 'Guid', 'byte[]' ) )
SET @column_nullable = 0 ; -- is ref type
-- if field type effectively is nullable, indicate with '?'
SET @property_type = @property_type + CASE @column_nullable
WHEN 1 THEN '?'
ELSE ''
END
Very simple. Here is the complete script
DeadSimpleDTO.sql:
/*
File: DeadSimpleDTO.sql
Author: Sky Sanders <sky.sanders@gmail.com>
Date: 01-23-10
Summary:
This script will generate simple C# data transfer objects (DTO) for each
Table and View in the current database.
The generated code is output via PRINT statements. If this script is run
interactively in a query tool, the generated code may be copied from the
output window. If run via ADO simply handle the SqlConnection.InfoMessage
event.
Be sure to execute within the scope of the desired database.
Known Issues:
This script does not account for objects or columns which use C#
reserved words as identifiers.
This script handles spaces embedded in identifiers by replacing with
an underscore.
The next version will illustrate the use of meta data via Custom Attributes
to retain original DDL when necessary.
*/
-- NOTE: Be sure to execute within the scope of the desired database.
-- You may wish to use a 'USE' statement if this script is called
-- interactively from a SQL tool
-- USE Northwind
SET NOCOUNT ON
PRINT 'using System;'
-- add other required namespaces
-- set the root namespace
DECLARE @namespace NVARCHAR(128)
SELECT TOP 1 @namespace = REPLACE([CATALOG_NAME],' ','_') FROM [INFORMATION_SCHEMA].[SCHEMATA]
PRINT 'namespace ' + @namespace + 'TransferObjects'
PRINT '{'
DECLARE @object_id INT,
@object_schema NVARCHAR(128),
@object_name NVARCHAR(128),
@object_type NVARCHAR(2)
-- foreach Table and View
DECLARE cObject CURSOR READ_ONLY
FOR SELECT o.[object_id],
s.[name] AS 'schema',
REPLACE(o.[name],' ','_'),
o.[type]
FROM [sys].[objects] o
INNER JOIN [sys].[schemas] AS s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ( 'U', 'V' )
ORDER BY s.[name],
o.[type]
OPEN cObject
FETCH NEXT FROM cObject INTO @object_id, @object_schema, @object_name, @object_type
WHILE @@FETCH_STATUS = 0
BEGIN
-- scope the class in the schema of the object
-- to prevent collisions. e.g. dbo.Customers and testing.Customers
PRINT ' namespace ' + @object_schema
PRINT ' {'
-- render class
PRINT ' public partial class ' + @object_name
PRINT ' {'
PRINT ''
PRINT ' #region Properties'
PRINT ''
DECLARE @column_id INT,
@column_name VARCHAR(128),
@column_type VARCHAR(30),
@column_nullable BIT,
@property_type VARCHAR(30)
-- foreach Column in object
DECLARE cColumn CURSOR READ_ONLY
FOR SELECT c.[column_id],
REPLACE(c.[name],' ','_'),
t.[name] AS 'type',
c.[is_nullable]
FROM [sys].[all_columns] c
LEFT JOIN [sys].[types] t ON c.[system_type_id] = t.[system_type_id]
WHERE c.[object_id] = @object_id
AND NOT t.[name] = 'sysname' -- system objects dupe sysname with nvarchar
ORDER BY c.[column_id]
OPEN cColumn
FETCH NEXT FROM cColumn INTO @column_id, @column_name, @column_type, @column_nullable
WHILE @@FETCH_STATUS = 0
BEGIN
-- translate SQL type to CLR type
SET @property_type = CASE @column_type
WHEN 'image' THEN 'byte[]'
WHEN 'text' THEN 'string'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'date' THEN 'DateTime'
WHEN 'time' THEN 'TimeSpan'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'datetimeoffset'
THEN 'DateTimeOffset'
WHEN 'tinyint' THEN 'byte'
WHEN 'smallint' THEN 'short'
WHEN 'int' THEN 'int'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'real' THEN 'Single'
WHEN 'money' THEN 'decimal'
WHEN 'datetime' THEN 'DateTime'
WHEN 'float' THEN 'double'
WHEN 'sql_variant' THEN 'object' -- if you know the underlying type, use that
WHEN 'ntext' THEN 'string'
WHEN 'bit' THEN 'bool'
WHEN 'decimal' THEN 'decimal'
WHEN 'numeric' THEN 'decimal'
WHEN 'smallmoney' THEN 'decimal'
WHEN 'bigint' THEN 'long'
WHEN 'hierarchyid'
THEN 'Microsoft.SqlServer.Types.SqlHierarchyId'
WHEN 'geometry' THEN 'Microsoft.SqlServer.Types.SqlGeometry'
WHEN 'geography' THEN 'Microsoft.SqlServer.Types.SqlGeography'
WHEN 'varbinary' THEN 'byte[]'
WHEN 'varchar' THEN 'string'
WHEN 'binary' THEN 'byte[]'
WHEN 'char' THEN 'string'
WHEN 'timestamp' THEN 'byte[]'
WHEN 'nvarchar' THEN 'string'
WHEN 'nchar' THEN 'string'
WHEN 'xml' THEN 'string' -- TODO: figure how to handle.. string?
WHEN 'sysname' THEN 'string'
ELSE 'object'
END
-- TODO: handle FILESTREAM attribute varbinary(max) Byte[]
-- if the CLR type is a reference type, ignore nullable bit on source field
IF ( @property_type IN ( 'object', 'string', 'Guid', 'byte[]' ) )
SET @column_nullable = 0 ; -- is ref type
-- if field type effectively is nullable, indicate with '?'
SET @property_type = @property_type + CASE @column_nullable
WHEN 1 THEN '?'
ELSE ''
END
-- render property
PRINT ''
PRINT ' private ' + @property_type + ' _' + @column_name + ';'
PRINT ''
PRINT ' public virtual ' + @property_type + ' ' + @column_name
PRINT ' {'
PRINT ' get { return _' + @column_name + '; }'
PRINT ' set { _' + @column_name + ' = value; }'
PRINT ' }'
FETCH NEXT FROM cColumn INTO @column_id, @column_name, @column_type, @column_nullable
END -- column loop
CLOSE cColumn
DEALLOCATE cColumn
-- render end of class
PRINT ''
PRINT ' #endregion'
PRINT ''
PRINT ' }'
PRINT ''
-- render end of schema namespace
PRINT ' }'
FETCH NEXT FROM cObject INTO @object_id, @object_schema, @object_name,
@object_type
END
-- object loop
CLOSE cObject
DEALLOCATE cObject
-- render end of root namespace
PRINT '}'
This script can be run as-is from a Sql query tool and the generated code can be copied from the output window.
You can also use a T4 template to generate the code directly into your project. Any time the shape of the database changes, just right-click on the template and select "Run Custom Tool". Bingo, up-to-date DTO.
Here is a simple T4 template that you can use to execute the script and generate your DTO from within your project. There is no Visual Studio template for , lolz, T4 templates so just add a new text file and rename it to DeadSimpleDTO.tt or whatever.tt and add the code from listing 2.
DeadSimpleDTO.tt:
<#@ template inherits="Microsoft.VisualStudio.TextTemplating.VSHost.ModelingTextTransformation" language="C#v3.5" debug="true" hostSpecific="true" #>
<#@ output extension=".cs" #>
<#@ Assembly Name="System.dll" #>
<#@ Assembly Name="System.Data.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data.SqlClient" #>
<#
// File: DeadSimpleDTO.tt
// this template will be executed upon each save or from Solution Explorer context menu item 'Run Custom Tool'
// and will generate a nested file containing the generated code.
const string connectionString = @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";
string output = string.Empty;
using (SqlConnection conn = new SqlConnection(connectionString))
{
// inline anonymous delegate to catch the output
conn.InfoMessage += ((ignored, messageArgs) =>
{
output = messageArgs.Message;
});
conn.Open();
// execute the script
using (var cmd = conn.CreateCommand())
{
string sql = System.IO.File.ReadAllText( System.IO.Path.GetDirectoryName(this.Host.TemplateFile) + "\\DeadSimpleDTO.sql");
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
#>
<#= output #>
Technorati tags:
codeproject
Technorati tags:
SQL,
C#,
CodeGen