Stored Procedure POCO Generator Utility for SQL Server

I've been doing a lot of work with Dapper - the micro ORM - and had been using a modified T4 template that originally came with PetaPoco to generate my POCO classes from all the tables in a database, but what I really wanted was something that would generate classes from a stored procedure. With Dapper, you have the MultiMapper query that can process SQL statements which return multiple resultsets, and usually I shape such mapping queries via a stored proc.

But search as I might, I could not find any examples of T4 templates that take this approach. However, I did find a fledgling utilty that used a StringBuilder to loop over a DataTable and build a POCO class from it. This gave me enough ideas to build what I needed.

What my utility does is to take a connection string and a stored procedure name that you supply. It then uses the SqlCommandBuilder DeriveParameters method to dynamically create a series of labels and textboxes containing the SQLParameter names and datatypes. You enter the parameter values you want, press the button, and off we go.

If your sproc returns 5 resultsets, you'll get 5 POCO classes written to the C:\Temp folder.

At that point, all you need to do is modify the stock namespace and class names, and you're "good to go".  

Let's have a look at the code I put together and see how this works:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.Reflection;
using System.Reflection.Emit;
using System.IO;
using System.Runtime.CompilerServices;
using System.Data.SqlClient;

namespace database.properties
{
    public partial class Form1 : Form
    {
         public string filePath = @"C:\Temp";
        private static string sprocName = "";
      
         public Form1()
        {
             InitializeComponent();
        }

         private void Form1_Load(object sender, EventArgs e)
        {
          
        }

         public void DoWork()
        {
            string fileText = null;
            DataSet ds = GetDataSet();
            foreach (DataTable DTable in ds.Tables)
            {
                string classFilePath = Path.Combine(filePath, string.Format("{0}{1}", DTable.TableName, ".cs"));
                fileText = GetTableType(DTable).ToString();
                 using (StreamWriter outfile =
                     new StreamWriter(classFilePath))
                 {
                      outfile.Write(fileText.ToString());
                }
            }
            label3.Text = "OPERATION COMPLETED.";
        }



        public static StringBuilder GetTableType(DataTable DTable)
        {
            StringBuilder sb  = new StringBuilder();
             sb.Append("namespace MyNameSpace");
            sb.Append("\n {");
            sb.Append("\n");
            sb.Append(string.Format("public class {0} ", DTable.TableName));
             sb.Append("\n {");
            foreach (DataColumn col in DTable.Columns)
            {
                var PropertyName = col.ColumnName;
                string propertyVariable = string.Format("_{0}", PropertyName).ToLower();
                string PropertyType = col.DataType.FullName.ToString();
                PropertyType = ConvertSqlDatatype(PropertyType.Remove(0, 7));
                 sb.Append("\n");
                 sb.Append(string.Format("private {0} {1};", PropertyType, propertyVariable));
            }
            foreach (DataColumn col in DTable.Columns)
            {
                var PropertyName = col.ColumnName;
                string propertyVariable = string.Format("_{0}", PropertyName).ToLower();
                string PropertyType = col.DataType.FullName.ToString();
                PropertyType = ConvertSqlDatatype(PropertyType.Remove(0, 7));
                 sb.Append("\n");
                 sb.Append(string.Format("public {0} {1}", PropertyType, PropertyName.ToString()));
                 sb.Append("\n {");
                 sb.Append(string.Format(" get {0} return {1}; {2}", "{", propertyVariable, "}"));
                 sb.Append("\n");
                 sb.Append(string.Format("set {0} {1} = value; {2}", "{", propertyVariable, "}"));
                 sb.Append("\n }");
            }
            sb.Append("\n }");
            sb.Append("\n }");
            return sb;
        }

          DataSet GetDataSet()
          {
              string cnString = this.txtConn.Text;
            SqlCommand cmd = new SqlCommand(sprocName , new SqlConnection(cnString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach ( Control c in panel1.Controls)
             {
                 if (c is TextBox)
                {
                    var k = c.Tag.ToString().ToLower();
                     if(k=="int")
                    {
                        int val = int.Parse(c.Text);
                       cmd.Parameters.AddWithValue(c.Name, val);
                      }
                     if(k=="varchar" || k=="nvarchar")
                        cmd.Parameters.AddWithValue(c.Name, c.Text);
                     if (k == "float")
                        cmd.Parameters.AddWithValue(c.Name, float.Parse(c.Text));
                     if (k == "datetime")
                        cmd.Parameters.AddWithValue(c.Name, DateTime.Parse(c.Text));
                     if (k == "uniqueidentifier")
                        cmd.Parameters.AddWithValue(c.Name, Guid.Parse(c.Text));
                     if (k == "bit")
                         cmd.Parameters.AddWithValue(c.Name, Convert.ToBoolean(c.Text));
                       // Note - I've only done some of the most common parameter types - fill in more as you need them, the same way.
                }
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
             da.Fill(ds);
            return ds;
        }

         public static string ConvertSqlDatatype(string dataType)
        {
            switch (dataType.ToLower())
            {
                case "string": return "string";
                case "bigint": return "long";
                case "binary": return "byte[]";
                case "bit": return "bool";
                case "char": return "char";
                case "date": return "DateTime";
                case "datetime": return "DateTime";
                case "datetime2": return "DateTime";
                case "datetimeoffset": return "DateTimeOffset";
                case "decimal": return "decimal";
                case "float": return "float";
                case "image": return "byte[]";
                case "int": return "int";
                case "money": return "decimal";
                case "nchar": return "char";
                case "ntext": return "string";
                case "numeric": return "decimal";
                case "nvarchar": return "string";
                case "real": return "double";
                case "smalldatetime": return "DateTime";
                case "smallint": return "short";
                case "smallmoney": return "decimal";
                case "text": return "string";
                case "time": return "TimeSpan";
                case "timestamp": return "DateTime";
                case "tinyint": return "byte";
                case "uniqueidentifier": return "Guid";
                case "varbinary": return "byte[]";
                case "varchar": return "string";
                 default:
                     return "string";
            }

        }

         private void button2_Click(object sender, EventArgs e)
         {
             // create classes
            DoWork();
        }

         private void button1_Click(object sender, EventArgs e)
        {
            string cnString = txtConn.Text;
            string sproc = txtSproc.Text;
            SqlCommand cmd = new SqlCommand( sproc, new SqlConnection(cnString));
             cmd.Connection.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            SqlCommandBuilder.DeriveParameters(cmd);
            int loc1 = 0;
            int loc2 = 0;
            foreach (SqlParameter parameter in cmd.Parameters)
             {
                 if (parameter.ParameterName != "@RETURN_VALUE")
                {
                    TextBox tb = new TextBox();
                    tb.Name =  parameter.ParameterName;
                    tb.Width = 150;
                    tb.Height = 25;
                    tb.Tag = parameter.SqlDbType;
                    tb.Location = new Point(loc1 + 150, loc2);
                    System.Windows.Forms.Label lbl = new System.Windows.Forms.Label();
                    lbl.Name = "lbl" + parameter.ParameterName;
                    lbl.Text = parameter.ParameterName;
                    lbl.Width = 150;
                    lbl.Height = 25;
                    lbl.Location = new Point(loc1, loc2);
                     panel1.Controls.Add(lbl);
                     panel1.Controls.Add(tb);
                    loc2 += 25;
                }
            }
            panel1.Visible = true;
            Application.DoEvents();
            cmd.Connection.Close();
             sprocName = txtSproc.Text;
             this.button2.Enabled = true;
        }
    }
}

The "button1_Click" handler is where everything starts. We create a SqlCommand, open our connection, and pass the command into the SqlCommandBuilder's DeriveParameters method. The command now has populated parameters. Then we simply iterate over the SqlParameters collection, creating a TextBox and a Label for each one, and adding these to the Panel in the lower half of the Form. The user gets to see a label and a textbox for each SqlParameter, and they can then fill in the desired values. At this point, I enable the final button.



When you click the "Create" button, we call the DoWork() method which uses the GetTableType method to build a class. The SqlDatatypes are converted to .NET types via the ConvertSqlDataType lookup method.  The classes, named Table.cs, Table1.cs, etc. are all written to the C:\temp folder.

NOTE: As pointed out by Robbe in the comments below, there are some SQL datatypes that cannot be easily represented by textbox values. Timestamp (now "rowversion") is one of them, being a .NET type of Byte[]. This is a self-incrementing column value that would rarely be used as a stored procedure input parameter. Other types would be varbinary, etc. These are minor limitations of the POCO Generation concept, but developers should still be aware of them.

I have included a sample stored proc "FindEmployees" for the popular Northwind database that you can use to test this out.

You can download the Visual Studio 2010 solution here.

By Peter Bromberg   Popularity  (4230 Views)