Simple TSQL and C# Entity Generator for S#arp Architecture

Recently, we started a big project which uses S#arp Architecture and for those who do not know what it is, you can go to the site and have a read, but in a gist, it is an open source architectural foundation that uses ASP.NET MVC framework with NHibernate.

The following principles of this architecture are Domain Driven Design Focus, Loosely coupled, Preconfigured Infrastructure and Open Ended Presentation that means having those principles in mind, the developer can concentrate more on Domain and User Experience part of the application that is being developed.

Having said that, we used this open-source architecture to leverage on its principles and now we are on the first phase of the project where we are building the Domain layer where we define all the entities needed. If you have a big database, this will be a tedious task mapping tables and columns to classes so I created a TSQL script to generate those entities so that I’ll just copy and paste the output to a cs file and we're ready to go.

DECLARE @TableName varchar(200)
DECLARE @ProjectName varchar(200) = '<<<YourApplicationName>>>.Domain'
DECLARE @DataType varchar(200)
DECLARE @ColumnName varchar(200)
DECLARE @IsNullable varchar(200)
DECLARE @AppDataType varchar(200) = 'String'
DECLARE @ConstraintType varchar(200) 
DECLARE @ReferringTable varchar(200) 

DECLARE ClassGenerator CURSOR

--Get all Tables
FOR SELECT TABLE_NAME from _
<<<YourDatabaseName>>>.INFORMATION_SCHEMA.TABLES _
where TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN ClassGenerator
FETCH NEXT FROM ClassGenerator
INTO @TableName

PRINT 'namespace ' + @ProjectName
PRINT '{'
PRINT ' using System;'
PRINT ' using SharpArch.Domain.DomainModel;'

WHILE @@FETCH_STATUS = 0
BEGIN 

		PRINT ' public class ' + dbo.Singularize(@TableName) + ' : Entity'
		PRINT ' {'

		DECLARE EntityGenerator CURSOR
		FOR 

		--Get all columns and their key types from all the tables
		SELECT 
		COL.COLUMN_NAME, 
		COL.IS_NULLABLE, 
		COL.DATA_TYPE, 
		CST.CONSTRAINT_TYPE,
		KCU.TABLE_NAME AS REFERENTIAL_TABLE_SOURCE
		FROM 
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
		INNER JOIN 
		INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RCN
		ON KCU.CONSTRAINT_NAME = RCN.UNIQUE_CONSTRAINT_NAME 
		RIGHT OUTER JOIN 
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
		INNER JOIN 
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS CST
		ON KCU2.CONSTRAINT_NAME = CST.CONSTRAINT_NAME 
		AND KCU2.TABLE_NAME = CST.TABLE_NAME 
		ON RCN.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
		RIGHT OUTER JOIN 
		INFORMATION_SCHEMA.COLUMNS COL
		ON KCU2.TABLE_NAME = COL.TABLE_NAME 
		AND KCU2.COLUMN_NAME = COL.COLUMN_NAME
		WHERE 
		COL.TABLE_NAME = @TableName
		ORDER BY 
		COL.TABLE_NAME

		OPEN EntityGenerator
		FETCH NEXT FROM EntityGenerator
		INTO @ColumnName, @IsNullable, @DataType, @ConstraintType, @ReferringTable

		WHILE @@FETCH_STATUS = 0
		BEGIN 

			IF @ConstraintType <> 'PRIMARY KEY' OR @ConstraintType IS NULL
			BEGIN
				IF @ConstraintType = 'FOREIGN KEY'
				BEGIN
					PRINT ' public virtual ' + _
					dbo.Singularize(@ReferringTable) + ' '  + _
					dbo.Singularize(@ReferringTable) + ' { get; set; }' 
				END
				ELSE
				BEGIN

					--SQL to .NET Data Type Mapping
					IF @DataType = 'bigint' BEGIN SET @AppDataType = 'Int64' END
					IF @DataType = 'binary' BEGIN SET @AppDataType = 'Byte[]' END
					IF @DataType = 'bit' BEGIN SET @AppDataType = 'Boolean' END
					IF @DataType = 'char' BEGIN SET @AppDataType = 'String' END
					IF @DataType = 'date' BEGIN SET @AppDataType = 'DateTime' END
					IF @DataType = 'datetime' BEGIN SET @AppDataType = 'DateTime' END
					IF @DataType = 'datetimeoffset' BEGIN SET @AppDataType = 'DateTimeOffset' END
					IF @DataType = 'decimal' BEGIN SET @AppDataType = 'decimal' END
					IF @DataType = 'float' BEGIN SET @AppDataType = 'Double' END
					IF @DataType = 'image' BEGIN SET @AppDataType = 'Byte[]' END
					IF @DataType = 'int' BEGIN SET @AppDataType = 'Int32' END
					IF @DataType = 'money' BEGIN SET @AppDataType = 'Decimal' END
					IF @DataType = 'nchar' BEGIN SET @AppDataType = 'String' END
					IF @DataType = 'ntext' BEGIN SET @AppDataType = 'String' END
					IF @DataType = 'numeric' BEGIN SET @AppDataType = 'Decimal' END
					IF @DataType = 'nvarchar' BEGIN SET @AppDataType = 'String' END
					IF @DataType = 'real' BEGIN SET @AppDataType = 'Single' END
					IF @DataType = 'rowversion' BEGIN SET @AppDataType = 'Byte[]' END
					IF @DataType = 'smalldatetime' BEGIN SET @AppDataType = 'DateTime' END
					IF @DataType = 'smallint' BEGIN SET @AppDataType = 'Int16' END
					IF @DataType = 'smallmoney' BEGIN SET @AppDataType = 'Decimal' END
					IF @DataType = 'sql_variant' BEGIN SET @AppDataType = 'Object' END
					IF @DataType = 'text' BEGIN SET @AppDataType = 'String' END
					IF @DataType = 'time' BEGIN SET @AppDataType = 'TimeSpan' END
					IF @DataType = 'timestamp' BEGIN SET @AppDataType = 'Byte[]' END
					IF @DataType = 'tinyint' BEGIN SET @AppDataType = 'Byte' END
					IF @DataType = 'uniqueidentifier' BEGIN SET @AppDataType = 'Guid' END
					IF @DataType = 'varbinary' BEGIN SET @AppDataType = 'Byte[]' END
					IF @DataType = 'varchar' BEGIN SET @AppDataType = 'String' END
					IF @DataType = 'xml' BEGIN SET @AppDataType = 'Xml' END

					IF @IsNullable = 'YES' AND @AppDataType _
					<> 'Byte[]' AND @AppDataType <> 'String'
					BEGIN 
						PRINT ' public virtual ' + @AppDataType + _
						'? ' + @ColumnName + ' { get; set; }'
					END
					ELSE
					BEGIN
						PRINT ' public virtual ' + @AppDataType + _
						' ' + @ColumnName + ' { get; set; }'
					END
				END
			END
			FETCH NEXT FROM EntityGenerator
			INTO @ColumnName, @IsNullable, @DataType, @ConstraintType, @ReferringTable

		END
		CLOSE EntityGenerator
		DEALLOCATE EntityGenerator

	PRINT ' }'

	FETCH NEXT FROM ClassGenerator

INTO @TableName
END
PRINT '}'
CLOSE ClassGenerator
DEALLOCATE ClassGenerator

Now I noticed, wouldn’t it be cleaner to create each entity in different class files? So I created a console application to do that which separates each entity to a file. And here is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        string sConnString = 
        "Data Source=<<<YourDatabaseServer>>>;
        Initial Catalog=<<<YourDatabase>>;Integrated Security=SSPI";
        SqlConnection oSQLConn1 = new SqlConnection(sConnString);
        SqlDataReader oReader = null;
        string sProjectName = "<<<YourProjectName>>>.Domain";
        string sGeneratedCodeLocation = @"C:\<<<YourProjectFolder>>>";

        oSQLConn1.Open();

        SqlCommand oCommand = new SqlCommand
        ("SELECT TABLE_NAME, dbo.Singularize(TABLE_NAME) 
        as SINGULARIZED_TABLE from INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME", oSQLConn1);

        oReader = oCommand.ExecuteReader();

        while (oReader.Read())
        {
            string sTableName = oReader[0].ToString();
            string sSingularizedTableName = oReader[1].ToString();
            SqlConnection oSQLConn2 = new SqlConnection(sConnString);
            SqlDataReader oItemReader = null;

            oSQLConn2.Open();

            SqlCommand oItemCommand =
            new SqlCommand(@"SELECT 
     COL.COLUMN_NAME, 
     COL.IS_NULLABLE, 
     COL.DATA_TYPE, 
     CST.CONSTRAINT_TYPE,
     dbo.Singularize(KCU.TABLE_NAME) AS REFERENTIAL_TABLE_SOURCE
     FROM 
     INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
     INNER JOIN 
     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RCN
     ON KCU.CONSTRAINT_NAME = RCN.UNIQUE_CONSTRAINT_NAME 
     RIGHT OUTER JOIN 
     INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
     INNER JOIN 
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS CST
     ON KCU2.CONSTRAINT_NAME = CST.CONSTRAINT_NAME 
     AND KCU2.TABLE_NAME = CST.TABLE_NAME 
     ON RCN.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
     RIGHT OUTER JOIN 
     INFORMATION_SCHEMA.COLUMNS COL
     ON KCU2.TABLE_NAME = COL.TABLE_NAME 
     AND KCU2.COLUMN_NAME = COL.COLUMN_NAME
     WHERE 
     COL.TABLE_NAME = '" + sTableName +
            @"' ORDER BY 
     COL.TABLE_NAME", oSQLConn2);

            oItemReader = oItemCommand.ExecuteReader();
            TextWriter oCSFile = new StreamWriter
            (sGeneratedCodeLocation + sSingularizedTableName + ".cs");

            oCSFile.WriteLine("namespace " + sProjectName);
            oCSFile.WriteLine("{");
            oCSFile.WriteLine("\tusing System;");
            oCSFile.WriteLine("\tusing SharpArch.Domain.DomainModel;");

            oCSFile.WriteLine("\tpublic class " + 
            sSingularizedTableName + " : Entity");
            oCSFile.WriteLine("\t{");

            while (oItemReader.Read())
            {
                string sAppDataType = "String";

                string sColumnName = oItemReader[0].ToString();
                string sIsNullable = oItemReader[1].ToString();
                string sDataType = oItemReader[2].ToString();
                string sConstraintType = oItemReader[3].ToString();
                string sReferringTable = oItemReader[4].ToString();

                if (sConstraintType != "PRIMARY KEY" || sConstraintType == null)
                {
                    if (sConstraintType == "FOREIGN KEY")
                    {
                        oCSFile.WriteLine("\t\tpublic virtual " + 
                        sReferringTable + " " + 
                        sReferringTable + " { get; set; }");
                    }
                    else
                    {
                        //SQL to .NET Data Type Mapping
                        if (sDataType == "bigint") sAppDataType = "Int64";
                        if (sDataType == "binary") sAppDataType = "Byte[]";
                        if (sDataType == "bit") sAppDataType = "Boolean";
                        if (sDataType == "char") sAppDataType = "String";
                        if (sDataType == "date") sAppDataType = "DateTime";
                        if (sDataType == "datetime") sAppDataType = "DateTime";
                        if (sDataType == "datetimeoffset") sAppDataType = "DateTimeOffset";
                        if (sDataType == "decimal") sAppDataType = "decimal";
                        if (sDataType == "float") sAppDataType = "Double";
                        if (sDataType == "image") sAppDataType = "Byte[]";
                        if (sDataType == "int") sAppDataType = "Int32";
                        if (sDataType == "money") sAppDataType = "Decimal";
                        if (sDataType == "nchar") sAppDataType = "String";
                        if (sDataType == "ntext") sAppDataType = "String";
                        if (sDataType == "numeric") sAppDataType = "Decimal";
                        if (sDataType == "nvarchar") sAppDataType = "String";
                        if (sDataType == "real") sAppDataType = "Single";
                        if (sDataType == "rowversion") sAppDataType = "Byte[]";
                        if (sDataType == "smalldatetime") sAppDataType = "DateTime";
                        if (sDataType == "smallint") sAppDataType = "Int16";
                        if (sDataType == "smallmoney") sAppDataType = "Decimal";
                        if (sDataType == "sql_variant") sAppDataType = "Object";
                        if (sDataType == "text") sAppDataType = "String";
                        if (sDataType == "time") sAppDataType = "TimeSpan";
                        if (sDataType == "timestamp") sAppDataType = "Byte[]";
                        if (sDataType == "tinyint") sAppDataType = "Byte";
                        if (sDataType == "uniqueidentifier") sAppDataType = "Guid";
                        if (sDataType == "varbinary") sAppDataType = "Byte[]";
                        if (sDataType == "varchar") sAppDataType = "String";
                        if (sDataType == "xml") sAppDataType = "Xml";

                        if (sIsNullable == "YES" && 
                        sAppDataType != "Byte[]" && sAppDataType != "String")
                        {
                            oCSFile.WriteLine("\t\tpublic virtual " + 
                            sAppDataType + "? " + sColumnName + " { get; set; }");
                        }
                        else
                        {
                            oCSFile.WriteLine("\t\tpublic virtual " + 
                            sAppDataType + " " + sColumnName + " { get; set; }");
                        }
                    }
                }
            }
            oCSFile.WriteLine("\t}");
            if (oItemReader != null) oItemReader.Close();
            if (oSQLConn2 != null) oSQLConn2.Close();

            oCSFile.WriteLine("}");
            oCSFile.Close();
        }
        Console.ReadLine();

        if (oReader != null) oReader.Close();
        if (oSQLConn1 != null) oSQLConn1.Close();
    }
}

Now let's dissect the codes, if you noticed, I used a Singularize Function to Singularize Table Names I have posted a code for that which you can find here.

Now let’s go to the queries, you will notice that there is a complex join in how we get our data regarding our data structure which can be easily extracted from the Information Schema, if you are interested in what other items can be extracted from it, have a read here.

Once we have the data we need, we start to process it and it’s not that straightforward but it's easy. All you need to do is to gather information whether a column or field is nullable so that we can declare it as nullabe in the code. We also need a mapping to SQL DataTypes to .NET Data Types, hence the big if conditions.

Also, since we are using the S#arp Architecture, there is a convention for PrimaryKeys so we don’t need to declare it as it is handled by the PrimaryKeyConvention class under NHibernateMaps\Convention, so if you named your PK right prefixing them with Id, then this will be easy but you can always override it if you wanted to. Below is the code that handles it:

namespace CI5.Infrastructure.NHibernateMaps.Conventions
{
    #region Using Directives

    using FluentNHibernate.Conventions;

    #endregion

    public class PrimaryKeyConvention : IIdConvention
    {
        public void Apply(FluentNHibernate.Conventions.Instances.IIdentityInstance instance)
        {
            instance.Column(instance.EntityType.Name + "Id");
        }
    }
}

Finally, we need to take note of Foreign Keys and what table it is related to as we don’t declare Ids in the Entities, but the class it is related to, hence we have this line.

if (sConstraintType == "FOREIGN KEY")
{
    oCSFile.WriteLine("\t\tpublic virtual " + 
    sReferringTable + " " + sReferringTable + " { get; set; }");
}

Other than that, everything should be straightforward.

The above code is made to generate only basic stuff so if you have added validation and other lines in your entities, do not use it, otherwise I hope this would make someone's life easier.