Generating C# Classes from SQL Tables

I’ve recently started a new project that is very database heavy, and I couldn’t help but find myself writing what can only be described as a ridiculous amount of boilerplate code to model entities. You might say, erm, Dan? Use an ORM? Entity Framework will take care of it for you… Now before I get into the details of this post, I want to state that this is not a post comparing Entity Framework to ADO.NET. I simply prefer to use ADO.NET and keep the flexibility that comes with it.

So, using ADO.NET, how can I generate C# Classes using my preexisting SQL Tables? Well, I found this utter GEM of an answer to this very question on our beloved StackOverFlow. (Check out the question here). We can use the following Query in SQL Server to return a C# class structure for our table:


declare @TableName sysname = '<TABLENAME>'
declare @Result varchar(max) = '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 'char'
            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 ('bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', '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  + '
}'

print @Result

Lets give it a try…

I’m going to test this code using a fairly basic table as an example. This table is called “OrderItems”, and is responsible for holding data about items relating to a customers order. Consider a table that has the following fields:

ID    int
OrderItemID    nvarchar(50)
SKU    nvarchar(50)
Quantity    int
ItemPrice    money
ShippingPrice    money
OrderID    int
ClientID    int

Pretty simple! So lets run the query against this table, and we get the following output:


public class OrderItems
{
    public int ID { get; set; }

    public string OrderItemID { get; set; }

    public string SKU { get; set; }

    public int Quantity { get; set; }

    public decimal ItemPrice { get; set; }

    public decimal ShippingPrice { get; set; }

    public int OrderID { get; set; }

    public int ClientID { get; set; }

}

Just what we wanted!

Don’t forgot to change the <TABLENAME> with the name of your table, eg:

declare @TableName sysname = 'OrderItems'

 

I hope that by sharing this, it will help as many of you as possible – in the same way that it has helped me.

Credit: Alex Aza on StackOverFlow.com.

Microsoft Open-Sources the .NET Framework

Microsoft .NET Logo

 

Developers Rejoice!

Microsoft announce that they’re making .NET open source and cross platform. News broke on wednesday via an msdn blog post from S. Somasegar, the corporate vice president of the Developer division at Microsoft.

Somasegar goes on to summarise the key points of the announcement:

  • Over the coming months, we will be open sourcing the full server-side .NET Core stack, from ASP.NET 5 down to the Core Runtime and Framework, and the open source .NET will be expanded to run on Linux and Mac OS X in addition to Windows.
  • Visual Studio Community 2013 is a new, free and fully featured edition of Visual Studio, available today, with access to the full Visual Studio extensibility ecosystem and support for targeting any platform, from devices and desktop to web and cloud services.
  • A preview of the next generation of our tools is available today with Visual Studio 2015 Preview and .NET 2015 Preview.  Together, these bring industry-leading cross-platform mobile development tools, deep support for cloud development, and great productivity improvements across the breadth of the developer experience.
  • Visual Studio 2013 Update 4 is available now for every Visual Studio 2013 user, including dozens of improvements across the product plus several great new features.
  • Visual Studio Online is expanding its DevOps portfolio with the new Visual Studio Online Release Management service and Visual Studio Cloud Deployment Projects.

Microsoft have also created a new hub for their open source projects on github: http://microsoft.github.io/

On the whole, the news was very well received within the developer community. And rightly so. Good move Microsoft.

Some Twitter reaction:

  (My personal favorite)

 

 

 

 

 

Calculating the time until a specific time of day

In order to calculate the time remaining before a specific time of day, .NET has a nice class called TimeSpan. With TimeSpan you can perform addition or subtraction on DateTime objects.

I ran into a situation where I needed to calculate the time remaining before it was 14:00. If it was 14:00 or greater, then I needed to calculate it for the following day.

The solution was pretty simple:


            DateTime now = DateTime.Now;
            DateTime target =
                new DateTime( DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 14, 00, 00 );

            //Add 1 day to the target time if we're already at 14:00+ today.
            if ( now.Hour >= 14 )
                target = target.AddDays( 1 );

            TimeSpan difference = target - now;

            var timeRemain = difference.ToString( @"hh\:mm\:ss" );

The result is a nice string that looks similar to this:

02:47:22

Hope this helps someone looking for a similar solution!

Simple C# Wrapper Class for SQL Server

Whilst database technology and the way we create and interact with database has changed significantly with the use of Object-Relational Mapping, however prefer to create the Tables and Queries myself within the database as it is my opinion that this offers greater flexibility and easier maintainability.

Upon finding myself writing the same boilerplate code over and over to interact with a database (A problem that ORMS effectively eliminate), I decided to create my own C# Wrapper Class for SQL Server. In doing so, I have significantly reduced the amount of code needed to run a query – simple or complex!

It is worth noting that this wrapper class is being actively developed, with new features added as and when I find that I need them.

The Class

Since the class is several hundred lines long (I like human readable code) I’ve hosted it externally: View Class on PasteBin

Usage

Once included in your project, usage for this class is fairly simple. An example might be:


    //Usage example for the database wrapper class     
     
     private bool validateOrder( )
        {
            //Create a new Database object
            Database database = new Database();
     
            //Add optional parameters to the query
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "OrderID", Value = txtOrderID.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "EmailAddress", Value = txtEmail.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "PostCode", Value = txtPostCode.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
           
            //Execute the query - give it the name of the stored procedure and set "hasParams" flag to true (or false if none).
            database.runSelect( "RETURNS_GET_ValidateOrder", true);
     
            //The results table is stored within the object and can be accessed as follows:
            if ( database.resultsTable.Rows.Count == 1 )
                return true;
            else
                return false;
        }

Known Caveats

  • This class doesn’t deal with datasets, only datatables.
  • Could have better Error handling – EG: Store SQLExceptions within the object

Please do let me know what you think, and what features you would like to see added.

Disclaimer:
Use of this code is entirely Open-Source. You are free to use it however you want; Commercially or Non-Commercially, but note: I provide absolutely no warranties for it’s use. This material has been posted as educational material, that may not be suitable for a production environment.