Generate Stored Procedures with data from SQL Server System Tables

Here’s an experiment I wrote over the weekend to create an Insert SP for a table using SQL Server System Tables.

This code generates code for a specific code pattern and won’t necessarily work for any table that is thrown at it. Supporting every possible situation can make the code really complex. If I am faced with a table that this can’t handle, I am face with the choice of expanding the program to handle the new situation or write the code by hand.

These SPs are the same except for:

• The SP name – Based on Table Name
• The Table Name
• The Fields
• Which Field is the Primary Key
• Which Fields are Nullable

From the System Tables I use:

• sysObjects.name: the name of the table
• sysColumns.name: the column name
• sysTypes.name: the type name
• sysColumns.length: the length of the field
• sysColumns.isnullable: is the field nullable?
• sysColumns.status: used to determine if this is the primary key/auto-number column (look for 0x80)

The Code

using System;
using System.Data.SqlClient;
using System.Text;

namespace GenerateSP
{
    class Program
    {
        /// <summary>
        /// The Connection String
        /// </summary>
        private const string CONNECTION_STRING = @"Data Source=localhost\SQLEXPRESS;" + 
             "Initial Catalog=Northwind;Integrated Security=True";
        /// <summary>
        /// Number of list items per line in the generated procedure
        /// </summary>
        private const int ITEMS_PER_LINE = 5;
        /// <summary>
        /// Shows the Code Generation in action
        /// </summary>
        static void Main(string[] args)
        {
            Console.WriteLine(CreateInsertSp("Employees"));
            Console.ReadKey();
        }
        /// <summary>
        /// Creates an INSERT Stored Procedure for a given table as a string
        /// </summary>
        public static string CreateInsertSp(string tableName)
        {
            string identityField = string.Empty;
            // There are 3 places in the SP that refer to the field names
            // so I am using 3 string builders to generate those parts
            // of the SP;
            StringBuilder parametersSb = new StringBuilder();
            StringBuilder InsertValueListSb = new StringBuilder();
            StringBuilder InsertFieldListSb = new StringBuilder();
            using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
            {
                int lineNumber = 0; // Used to 
                SqlDataReader reader = GetTableDefReader(tableName, conn);
                while (reader.Read())
                {
                    string colName = reader.GetString(0);
                    string typeName = reader.GetString(1);
                    int length = reader.GetInt32(2);
                    int isNullable = reader.GetInt32(3);
                    int status = reader.GetInt32(4);

                    AddToParameterSb(parametersSb, colName, typeName, length, 

                        isNullable, status);
                    if (status == 0x80)
                        identityField = colName;
                    else
                    {
                        lineNumber++;
                        AddInsertFieldListToSb(InsertFieldListSb, colName, lineNumber);
                        AddInsertValueListToSb(InsertValueListSb, colName, lineNumber);
                    }
                }
            }
            // Put everything together
            return
                string.Format("\nCREATE PROCEDURE [dbo].[Insert{0}]\n", tableName) +
                parametersSb.ToString() +
                string.Format("\nAS\n\nINSERT INTO [{0}] (", tableName) +
                InsertFieldListSb.ToString() +
                string.Format(")\nVALUES (") +
                InsertValueListSb.ToString() +
                string.Format(")\n\nSET @{0} = @@IDENTITY\n\nGO\n", identityField);
        }
        /// <summary>
        /// Gets a DataReader containing selected column information for all of the 
        /// columns in the indecated table from SQL Server System Tables 
        /// </summary>

        private static SqlDataReader GetTableDefReader(string tableName, 

            SqlConnection conn)
        {
            SqlDataReader reader;
            string getDataForTable =
                "SELECT c.name AS col_name, " +
                "   t.name AS type_name, " +
                "   CAST(c.length AS INT) AS length, " +
                "   c.isnullable, " +
                "   CAST(c.status AS INT) AS status " +
                "FROM " +
                "  sysObjects o " +
                "  JOIN sysColumns c ON c.id = o.id " +
                "  JOIN systypes t on c.xtype = t.xtype AND t.status = 0 " +
                "WHERE o.name = @tableName " +
                "ORDER BY c.colid ";
            using (SqlCommand cmd = new SqlCommand(getDataForTable, conn))
            {
                cmd.Parameters.AddWithValue("@tableName", tableName);
                conn.Open();
                reader = cmd.ExecuteReader();
            }
            return reader;
        }
        /// <summary>
        /// Adds the current field to the 
        /// </summary>
        private static void AddToParameterSb(StringBuilder sb, string colName, 
             string typeName, int length, int isNullable, int status)
        {
            if (sb.Length > 0)
                sb.Append(",\n");
            sb.AppendFormat("\t@{0} ", colName);
            switch (typeName.ToLower())
            {
                case "varchar":
                case "nvarchar":
                    string lenString = length.ToString();
                    if (length == -1)
                        lenString = "MAX";
                    sb.AppendFormat("{0}({1})", typeName, lenString);
                    break;
                default:
                    sb.Append(typeName);
                    break;
            }
            if (status == 0x80)
                sb.Append(" OUT");
            else if (isNullable != 0)
                sb.Append(" = NULL");
        }
        /// <summary>
        /// Adds the current field to the VALUES list for an INSERT query
        /// </summary>
        private static void AddInsertValueListToSb(StringBuilder sb, string colName, 
            int lineNumber)
        {
            AddListSeperatorToSb(sb, lineNumber);
            sb.AppendFormat("@{0}", colName);
        }
        /// <summary>
        /// Adds the current field to the Field list for an INSERT query
        /// </summary>

        private static void AddInsertFieldListToSb(StringBuilder sb, string colName, 
            int lineNumber)
        {
            AddListSeperatorToSb(sb, lineNumber);
            sb.AppendFormat("[{0}]", colName);
        }
        /// <summary>
        /// Adds the comma and or new line to the list StringBuilder
        /// </summary>
        /// <remarks>
        /// Shouldn't have a comma before the first item in the list.<br />
        /// Should only insert new ine after each ITEMS_PER_LINE items
        /// </remarks>
        private static void AddListSeperatorToSb(StringBuilder sb, int lineNumber)
        {
            if (sb.Length > 0)
                sb.Append(", ");
            if ((lineNumber % ITEMS_PER_LINE) == 0)
                sb.Append("\n\t");
        }
    }
}

I adapted this code for T4 Templates here