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
