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; }
        replace(, ' ', '_') ColumnName,
        column_id ColumnId,
            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_' +
        end ColumnType,
            when col.is_nullable = 1 and 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

SQL Server – Return a list of parameters from a Stored Procedure

In SQL Server, you can query the database to return the parameters that a Stored Procedure contains. This can be achieved like so:

SELECT AS Parameter, AS [Type]
FROM <DATABASE NAME>.sys.procedures sp
JOIN <DATABASE NAME>.sys.parameters p
    ON sp.object_id = p.object_id
JOIN sys.types t
    ON p.system_type_id = t.system_type_id

You can change the SELECT to a COUNT(*) if you just want the number of parameters.


Credit: JodyT

How to find expensive stored procedures in SQL Server

There may be a time when you find your database server(s) is/are utilising a high percentage of CPU resources. If this is the case, the first step in diagnosing the issue is to find out exactly what SQL Server is doing. One way you can do this, is to run the following query in SQL Server Management Studio:

SELECT TOP (25) AS [SP Name], 
  qs.total_worker_time AS [TotalWorkerTime], 
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) 
    AS [Calls/Second],
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

The output is ordered by TotalWorkerTime, however you may wish to change this to avg_elapsed_time, to see which SP’s are taking the longest to execute. Don’t forget to take into account execution_count, as you may be able to find a way to reduce this within your applications for better performance.

SQL Server Stored Procedure Naming Convention

Microsoft SQL Server

Microsoft SQL Server

One thing SQL Server seems to be lacking is the ability to categorise stored procedures (SPROCS), or even just organise them into separate “folders” within the database. This might not be much of an issue when you only have a few procedures, but can become a real issue for developers using the SQL Server Management Studio software to develop.

How I do it

Whilst developing my final year project at University, I decided to address this issue. I wanted a method of being able to quickly scroll through potentially hundreds of SPROCS, in order to find the specific procedure that I was looking for. Not only this, but I wanted to group procedures together into the type of queries that they ultimately run eg SELECT, INSERT etc…







In this example I have chosen to demonstrate how naming SPROCS for ASP.NET Routing could be done.

Firstly we start with the functionality that the SPROC is for. In this case “ROUTING”. This could also have been other things such as “ORDERS”, or “COMMENTS”. Since SQL Management Studio orders everything alphanumerically, we have managed to ensure that all SPROCS relating to “ROUTING” are now grouped together.

Similarly with the type of query we want to run. All query types will be grouped together.. GET.. INSERT etc. This makes finding what you are looking for so much quicker and easier. Not only this, but the names of your queries are also now “Self Documenting”, and precitable.

Finally, finish the name with some useful information. In the example, I also showed how I was going to run the query. “ROUTING_DELETE_RouteByID”. Just by looking at the name, you can take a pretty well educated guess as to what is needed in terms of parameters for the query. In this case, we’re deleteing a specific record by it’s “ID” and so some kind of “ID” will need to be passed as a parameter.

It’s also worth noting that I opted to use capital letters for the first and second parts of the name. In my opinion this just makes the names stand out that much more in the SPROC list.


How do you name your stored procedures?

I hope you found this useful, and helpful.


Happy Coding!