Using a Sybase with S#arp Architecture and nHibernate

This article will discuss on how I achieved using a Sybase database alongside SQL Server with S#arp Architecture / nHibernate.   In this article I would assume that you use S#arp and have already a running project where you want to include Sybase in your scenario.  So lets start.

1. Create your Sybase Connection String

Go to your web config and add a connection string like such

  <connectionStrings>
    <add name="SybaseConnectionString" connectionString="Data Source='{YOURSYBASEDATABASE}';Port=5000;UID='{xxxxxx}';Password='{xxxxxx}';Database='{SomeDatabase}'"/>
  </connectionStrings>

2. Create a Domain for your Sybase Tables

For this demo we will reference a table from a Sybase database called Activity.

First we create a class library project to separate you existing Domain to your Sybase Domain.  We will call it Sybase.Domain

In that project we will create a class to represent the “Activity” table

namespace Sybase.Domain
{
    public class Activity
    {
        public virtual string Code { get; set; }

        public virtual string Name { get; set; }

        public virtual string Type { get; set; }

        public virtual string SubType { get; set; }
    }
}

3. Create a class to grab that Connection String

For a cleaner implementation we create an Infrastructure Class Library Project, under the Infrastructure Solution Folder and call it Sybase.Infrastructure. This project will be a container for infrastructure related items such as repositories.  We will also create an Interface for it so we don’t reference Infrastructure in our main Web project and use the already exposed Domain.  Having said that we will be creating that Interface in the Sybase.Domain project.

At this stage import the highlighted references on the image below

Lets create your RepositoryConfiguration Class which will expose your connection string

using System.Configuration;
using Sybase.Domain.Contracts.Configuration;

namespace Sybase.Infrastructure
{
    public class RepositoryConfiguration : IRepositoryConfiguration
    {
        public string ConnectionString
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["SybaseConnectionString"].ConnectionString;

                return connectionString;
            }
        }
    }
}

Then the interface in your Sybase.Domain project

namespace Sybase.Domain.Contracts.Configuration
{
    public interface IRepositoryConfiguration
    {
        string ConnectionString { get; }
    }
}

4. Create a mapping Extension

Now you have your domain we need to create a reusable Mapping mechanism so Sybase column names map properly to the Sybase Domain Classes.  At this stage we will be creating it in another class library project which we call Framework, this will contain Framework Related Items like Referenced Assemblies, Extensions as well as Helpers.  Let’s call it Demo.Framework.

Inside the project we create and Extensions folder and create our AutoMappingExtension class

using System;
using System.Collections.Generic;
using System.Data;

namespace Demo.Framework.Extensions
{
    public static class AutoMappingExtension
    {
        public static IList<T> Map<T>(this IDataReader dataReader) where T : new()
        {
            IList<T> list = new List<T>();

            if (dataReader != null)
            {
                while (dataReader.Read())
                {
                    var destination = new T();
                    Type destinationType = destination.GetType();

                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        string sourceFieldName = dataReader.GetName(i);
                        string destinationPropertyName = sourceFieldName.InflectTo().Pascalized;

                        var destinationProperty = destinationType.GetProperty(destinationPropertyName);

                        if (destinationProperty != null && destinationProperty.CanWrite)
                        {
                            object destinationValue = FormatDestinationValue(dataReader[i], destinationProperty.PropertyType.GetUnderlyingType());
                            destinationProperty.SetValue(destination, destinationValue, null);
                        }
                    }

                    list.Add(destination);
                }

                dataReader.Close();
            }

            return list;
        }

        public static Type GetUnderlyingType(this Type source)
        {
            if (source.IsGenericType && (source.GetGenericTypeDefinition() == typeof(Nullable<>)))
            {
                // source is a Nullable type so return its underlying type
                return Nullable.GetUnderlyingType(source);
            }

            // source isn't a Nullable type so just return the original type
            return source;
        }

        private static object FormatDestinationValue(object value, Type destinationType)
        {
            if (value == null || value == DBNull.Value)
            {
                return null;
            }

            TypeCode destinationTypeCode = Type.GetTypeCode(destinationType);

            switch (destinationTypeCode)
            {
                case TypeCode.String:
                    value = value.ToString().TrimEnd();
                    break;

                case TypeCode.DateTime:
                    value = Convert.ToDateTime(value);
                    break;
            }

            return value;
        }
    }
}

Please note that we will use Inflector Extension (http://brendanjerwin.com/blog/2010/02/25/inflector-extension/) to provides a convenient Inflect() extension method on string and int data types

5. Create your first Sybase Repository

Now you have everything you need lets create your first Repository which will be in your Sybase.Infrastructure project, Lets call it ActivitiesRepository.  You will also need to create your Interface in Sybase.Domain project.

Lets do a simple Get By, so here is your method

using System;
using Sybase.Domain.Contracts.Configuration;
using System.Collections.Generic;
using Sybase.Domain;
using Sybase.Data.AseClient;
using Sybase.Domain.Repositories;
using System.Data;
using Demo.Framework.Extensions;
namespace Sybase.Infrastructure
{
    public class ActivitiesRepository : IActivitiesRepository
    {
        private readonly IRepositoryConfiguration repositoryConfiguration;
        public ActivitiesRepository(IRepositoryConfiguration repositoryConfiguration)
        {
            this.repositoryConfiguration = repositoryConfiguration;
        }
        public IList<Activity> GetBy(string activityCode)
        {
            try
            {
                IList<Activity> activities;

                using (var connection = new AseConnection(repositoryConfiguration.ConnectionString))
                {
                    var command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;

                    command.CommandText = @"SELECT
                                            activity_code as Code,
                                            activity_name as Name,
                                            activity_type as Type,
                                            activiy_subtype as SubType
                                            FROM dbo.activity 
                                            WHERE activity_code = '" + activityCode + "'";

                    connection.Open();

                    using (var dataReader = command.ExecuteReader())
                    {
                        activities = dataReader.Map<Activity>();
                    }
                }

                return activities;
            }
            catch (Exception ex)
            {
                // TODO: Log exception
                return null;
            }
        }
    }
}

Then your Interface in your Sybase.Domain project

using System.Collections.Generic;

namespace Sybase.Domain.Repositories
{
    public interface IActivitiesRepository
    {
        IList<Activity> GetBy(string activityCode);
    }
}

6. Register the projects you just created in CastleWindsor’s Component Registrar

Go to your Presentation Layer, in this case its Demo.Web.Mvc.  In the CastleWindsor Folder there is a Class called ComponentRegistrar.  Add the following codes to under the AddQueryObjectsTo object

container.Register(
    AllTypes.FromAssemblyNamed("Sybase.Infrastructure")
        .Pick()
        .WithService.FirstNonGenericCoreInterface("Sybase.Domain"));

You also need to reference your Sybase Related Projects here

7. Create your query object and test your code

In this instance we create a method in your controllers queries and call it GetActivities.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SharpArch.NHibernate;

namespace Demo.Web.Mvc.Controllers.Queries
{
    public class HomeQuery : NHibernateQuery
    {
        private readonly Sybase.Domain.Contracts.Repositories.IActivitiesRepository activitiesRepository;
        public HomeQuery(
              Sybase.Domain.Contracts.Repositories.IActivitiesRepository activitiesRepository
            )
        {
            this.activitiesRepository = activitiesRepository;
        }

        public void GetActivities(string activityCode)
        {
            activitiesRepository.GetBy(activityCode);
        }

    }
}

Now run and test your code.

Upgrading S#arp to MVC4

First of all I can assure you that S#arp will work on MVC4 and I am using Sharp Architecture 2.0.4.  We just recently migrated and tested 2 large S#arp projects and it is fully working and is running in production.

Now lets go to the details, I guess you came across tis article either because you want to use MVC4 with your S#arp project or you got this “Entry point was not found” error because you recently installed MVC4 in your machine

If its the second reason yes, it is MVC4 doing it and not S#arp your project just auto referenced the latest MVC version.  Now to fix that issue is that you need to upgrade your S#arp project to use MVC4 this can also be used in any .Net web projects that is using MVC3 so this guide is not specific to S#arp projects.  Lets start!

1. Go to all of your web projects in this case if its S#arp its in the presentation layer.  You need to replace all instances of

System.Web.Mvc, Version="3.0.0.0"
System.Web.WebPages="", Version="1.0.0.0"
System.Web.Helpers="", Version="1.0.0.0"
System.Web.WebPages.Razor="", Version="1.0.0.0"

to

System.Web.Mvc="", Version="4.0.0.0"
System.Web.WebPages="", Version="2.0.0.0"
System.Web.Helpers="", Version="2.0.0.0"
System.Web.WebPages.Razor="", Version="2.0.0.0"

You can see this in 3 different locations

First is in the configSections

which means your old web.config lines which looks like this

will become like this

Next is in system.web.webPages.razor section under host and in system.web under assemblies

which means your old web.config lines which looks like this

will become like this

Finally check also under the views folder, there is one web.config file in there.

Old one will be like this

change it so it looks like this

In the root Web.config file update or add the following is not yet there

<appSettings>
  <add key="webpages:Version" value="2.0.0.0" />
  <add key="PreserveLoginUrl" value="true" />
</appSettings>

3. In Solution Explorer, right-click the project name (if using s#arp its the {ProjectName}.Web.Mvc) and then select Unload Project this will unload you project and you can edit what’s in the code behind.  Once unloaded right-click the name again and select Edit {ProjectName}.csproj.

Find the ProjectTypeGuids element and replace {E53F8FEA-EAE0-44A6-8774-FFD645390401} with {E3E379DF-F4C6-4180-9B81-6769533ABE47}.

so from this

will become like this

Save and close file you just edited, right-click the project, and then select Reload Project.  This will load the project with the new settings.

4. If the project references any third-party libraries that are compiled using older versions of MVC (definitely S#arp is) then you have to add the following
three bindingRedirect elements under the configuration section in your web.config

here is what you need to add:

<dependentAssembly>
  <assemblyIdentity name="System.Web.Helpers"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.Mvc"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="4.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.WebPages"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>

so it should look like this

Foreign Key Drop Downs on Telerik MVC Grid

If you are using MVC on your projects and you are using a Grid View control most probably it will be the Telerik Grid.  While it is one of the best ones around it still have its cons but whats good with it is that the product is regularly updated even for the open source license.  One thing I noticed with the older version before 2011.3.1115.0 is that foreign keys are not natively supported, which means if you have a data structure similar to below then it will be task to perform operations on those in the grid as you will go by the template method.

Logically you will put a drop down on the grid so when you edit or add an item then you will be presented with the contact type options.  Well with that old version it would not be straightforward as you have to make your own template to display that drop down.  For those who are interested well here is how made it.

First is to create your view model, for this sample lets call it AccountContactViewModel

public class AccountContactViewModel
{
    public int Id { get; set; }

    public string LastName { get; set; }

    public string FirstName { get; set; }

    public string TelephoneNumber { get; set; }

    public string FaxNumber { get; set; }

    public string MobileNumber { get; set; }

    public string EmailAddress { get; set; }

    private NumericKeyValuePair contactType;

    [UIHint("AccountContactType"), Required]
    public NumericKeyValuePair ContactType
    {
        get
        {
            if (this.contactType == null)
            {
                NumericKeyValuePair o = new NumericKeyValuePair();
                o.Key = 0;
                o.Value = string.Empty;

                return o;
            }

            return this.contactType;
        }

        set
        {
            this.contactType = value;
        }
    }
}

If you noticed I used a NumericKeyValuePair which is just a class to define Key and value parings to be used for dropdowns

public class NumericKeyValuePair
{
    public int Key { get; set; }

    public string Value { get; set; }
}

Also you will notice the UIHint property, that’s the property to set the name of the field template to use to display the data field which is used for rendering data fields in a data model.  In simple words this is what you need to name your Editor Template to show your drop down so it automatically maps the data field in the model and render it in the control.

Next is you need an Editor template to show that drop down values and like I said above we will name it AccountContactType

The code behind is easy as it is just using a DropDownListFor and it is feeding data from a ContactType Model, you can also manually add items like such

@Html.DropDownList(ViewData.TemplateInfo.GetFullHtmlFieldName(string.Empty), new List<SelectListItem>
{
    new SelectListItem{ Value = "0", Text = string.Empty },
    new SelectListItem{ Value="1", Text = "Primary Debtor" }, 
    new SelectListItem{ Value="2", Text = "Other Debtor" },
    new SelectListItem{ Value="3", Text = "Primary Internal" },  
    new SelectListItem{ Value="4", Text = "Other Internal" }
})

Then on your View where your MVC Grid is, one column should be defined as a Client Template which calls the Client Template above and assigns the ContactTypeId to be linked to the ContactTypes, this is important for the grid to know which item to choose when the row is bound.

@(Html.Telerik().Grid<AccountContactViewModel>()
.Name("grdAccountContact")
.Columns(columns =>
{
    columns.Command(a =>
    {
        a.Delete().ButtonType(GridButtonType.Image);
    }).Width(80);
    columns.Bound(a => a.FirstName);
    columns.Bound(a => a.LastName);
    columns.Bound(a => a.TelephoneNumber);
    columns.Bound(a => a.MobileNumber);
    columns.Bound(a => a.FaxNumber);
    columns.Bound(a => a.EmailAddress);
    columns.Bound(a => a.ContactType).ClientTemplate("<#= ContactType.Value #>").Title("Contact Type");
    columns.Bound(a => a.Id).Hidden();
})
.ToolBar(commands => commands.Insert())
.DataBinding(d => d.Ajax()
    .OperationMode(GridOperationMode.Server)
    .Select("SelectAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Update("UpdateAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Delete("DeleteAccountContacts", "Setup", new { accountId = Model.AccountId })
.Editable(e => e.Mode(GridEditMode.InCell))
.DataKeys(k => k.Add(a => a.Id))
.ToolBar(b => b.SubmitChanges())
.ClientEvents(e => e
    .OnDataBound("GridHelper.onDataBound")
    .OnEdit("GridHelper.onEdit")
    .OnSave("GridHelper.onSave"))
)

Now for your Controller you will have something simple as this

[GridAction]
public ActionResult SelectAccountContacts(int? accountId)
{
    var viewModel = accountSetupQuery.GetAccountContacts(accountId.Value);

    return View(new GridModel<AccountContactViewModel>(viewModel));
}

which calls this query

public IList<AccountContactViewModel> GetAccountContacts(int accountId)
{
    var accountContacts = 
    (from a in Session.Query<AccountContact>()
    where a.Account.Id == accountId
    select new AccountContactViewModel
    {
        Id = a.Account.Id,
        LastName = a.LastName,
        FirstName = a.FirstName,
        TelephoneNumber = a.TelephoneNumber,
        FaxNumber = a.FaxNumber,
        MobileNumber = a.MobileNumber,
        EmailAddress = a.EmailAddress,
        ContactType = new NumericKeyValuePair() { Key = a.ContactType.Id, Value = a.ContactType.Name }
    }).ToList();

    return accountContacts;
}

Take note of that Session.Query<AccountContact> as I am using S#arp Architecture, my LINQ is executing its query to a nHibernateQuery Session of the Account Contact Entity defined in my domain, you can use any queryable collection here.

Now thanks to the new version you don’t have to use the Client Template and all you have to do is to define the column as a Foreign Key.  Here is how it should be done now for those interested.  (BTW the codes above can still be used as a reference for using Client Template)

First is we modify our view model and we remove all instances of the NumericKeyValuePair and replace it with the Foreign Key Id and we will name it ContactTypeId.

public class AccountContactViewModel
{
    public int Id { get; set; }

    public string LastName { get; set; }

    public string FirstName { get; set; }

    public string TelephoneNumber { get; set; }

    public string FaxNumber { get; set; }

    public string MobileNumber { get; set; }

    public string EmailAddress { get; set; }

    public int ContactTypeId { get; set; }  
}

Next is we get rid of the client template then your Grid should be defined like this

@(Html.Telerik().Grid<AccountContactViewModel>()
.Name("grdAccountContact")
.Columns(columns =>
{
    columns.Command(a =>
    {
        a.Delete().ButtonType(GridButtonType.Image);
    }).Width(80);
    columns.Bound(a => a.FirstName);
    columns.Bound(a => a.LastName);
    columns.Bound(a => a.TelephoneNumber);
    columns.Bound(a => a.MobileNumber);
    columns.Bound(a => a.FaxNumber);
    columns.Bound(a => a.EmailAddress);
    columns.ForeignKey(a => a.ContactTypeId, Model.ContactTypes, "Id", "Name");
    columns.Bound(a => a.Id).Hidden();
})
.ToolBar(commands => commands.Insert())
.DataBinding(d => d.Ajax()
    .OperationMode(GridOperationMode.Server)
    .Select("SelectAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Update("UpdateAccountContacts", "Setup", new { accountId = Model.AccountId }))
.Editable(e => e.Mode(GridEditMode.InCell))
.DataKeys(k => k.Add(a => a.Id))
.ToolBar(b => b.SubmitChanges())
.ClientEvents(e => e.OnDataBound("GridHelper.onDataBound")
    .OnEdit("GridHelper.onEdit")
    .OnSave("GridHelper.onSave"))
)

You notice that the old ClientTemplate is now replaced to ForeignKey and it is populated by from the Model.ContactTypes so make sure before the MVC Grid is populated the Model.ContactTypes already have a value.

Now you can still use the same controller but you will need to change your query to something like this

public IList<AccountContactViewModel> GetAccountContacts(int accountId)
{
    var accountContacts = (from a in Session.Query<AccountContact>()
    where a.Account.Id == accountId
    select new AccountContactViewModel
    {
        Id = a.Account.Id,
        LastName = a.LastName,
        FirstName = a.FirstName,
        TelephoneNumber = a.TelephoneNumber,
        FaxNumber = a.FaxNumber,
        MobileNumber = a.MobileNumber,
        EmailAddress = a.EmailAddress,
        ContactTypeId = a.ContactType.Id
    }).ToList();

    return accountContacts;
}

You will notice that you don’t have that NumericKeyValuePair and it was replaced with the Foreign Key Id in your AccountContactViewModel.

That’s it, its simpler than before, now you can put a of foreign keys on that table without creating lots of Client Template.

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 does not know what it is you can go to the site (http://www.sharparchitecture.net/) 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 Experince 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 a 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 were 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 lets 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

http://anyrest.wordpress.com/2011/09/13/singularize-function-in-tsql/.

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 on what other items can be extracted from it have a read here

http://msdn.microsoft.com/en-us/library/ms186778.aspx

Once we have the data we need, we start to process it and it’s not that straightforward but its 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 codes.  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 is it related to as we don’t declare Id’s 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 someones life easier.