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.

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!

c# UK postcode splitting

To maintain consistency with user inputted data, i wrote this small method in order to normalise UK post codes into the standard format.

The logic is as follows:

  1. Convert all the letters to their uppercase variant.
  2. Strip out any character that isn’t letter or a number using regular expressions and remove all spaces from the string.
  3. Split the post code using the substring method. We call this method twice, so we can get the first and last segments of the post code and insert a space before the last 3 characters.

The code:

public string fixPostCode(string postCode)
    {
        postCode = postCode.ToUpper(); //Convert all letters to upper case
        Regex r = new Regex("(?:[^a-z0-9 ]|(?&lt;=['\"])s)", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Compiled);
        postCode = r.Replace(postCode, String.Empty).Replace(" ", "");

        return  postCode.Substring(0, postCode.Length - 3) + 
			" " + postCode.Substring(postCode.Length - 3);
    }

You will need to include the following namespace into your class:

System.Text.RegularExpressions;

Go ahead and test it. Try an input string of something like: “M£$£^%£$£££66£$%££££&A)£$££^£%$(^P” and see what happens ;). N.B: You should get, “M6 6AP” as output.

Just some career thoughts…

Just a short entry of some thoughts…

During my placement I’ve definitely grown a passion for Windows based web development. I’ve been uncertain up until now as to which avenue of web development i’d like to follow, since there are numerous different languages that I could specialise in.

Up until recently, I had no idea how powerful ASP.NET really was, and the vast array of benefits that stem from using it. Whilst there are a lot of developers out there that prefer to use PHP/MySQL based languages, ASP.NET gives you the flexibility to adapt between software engineering and web development. I realised this whilst being tasked with the various projects I’ve been working on lately. C# can be used in a wide range of applications, and so I feel that being able to use this language will potentially give me more career opportunities later in life. Of course, there is that famous saying; “Don’t put all your eggs in one basket”. Whilst it’s true that it may be somewhat of a gamble, I feel that becoming a specialist in something will be more rewarding than dabbling with many (different) languages. Not only that, but I’ve grown rather fond of C#, and find myself enjoying using it! Bonus!

I’ve had clearance from my manager to blog about the code that I’ve been writing for the company. This is awesome as it provides me a unique opportunity to create a solid platform in which to demonstrate my skills. Thanks Salim! 🙂

For those that are interested, I shall soon be blogging about what I’ve been up to recently. However I’m currently in the middle of a few different things at the moment, and time is precious, so I’ll see how I get on!