Scripting SQL Server database with ScriptTableData.cst

Recently, I worked on SQL scripts for database deployment. I use VS.NET 2003's Database Project to maintain the Create Script and Change Script in the SourceSafe. Those scripts will be run automatically by the build tool (NAnt) to create the database from scratch at any time and update the existing database as required.

The database project in VS.NET is an excellent tool to script database objects like tables, views, UDFs and stored procedures. You just need to drag and drop those objects from the Server pane to the Solution pane and automatically all the required SQL scripts will be generated and added to the solution. However, it fells short when I use the database project to script the data. The data is stored as binary file, so I lose control of it.

Surprisingly, Codesmith 2.6 (the free edition) comes with a template called ScriptTableData.cst that will do exactly what I want: to script the data as SQL statements. It produces INSERT INTO... SELECT statements that can be run on the target database to populate the data.

Unfortunately, the ScriptTableData template contains some annoying bugs. For example, the bit data type is scripted as "true" or "false" instead of "1" or "0", causing errors when you execute the produced SQL script. Moreover, it does not handle datetime and binary datatype very well.

I decided to modify the script to help my work. The following is the modified version of ScriptTableData.cst. All good credit goes to the maker of this excellent code generator, Eric J. Smith. I only fix some bugs and add few more features.

Bugs fixed:

• Boolean data type is handled properly
• SET IDENTITY_INSERT appears only for the table with identity column

Improvement:

• More precise representation for DateTime data type
• Support binary data type
• Support two style of scripting: Compact and Verbose. The verbose style will produce SQL script similar to the commercial tools.

The template:

<%@ CodeTemplate Language="C#" Debug="True" TargetLanguage="T-SQL" Description="Generates a script based on the data from a table." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="\tContext" Description="Table to get the data from." %>
<%@ Property Name="ScriptType" Type="ScriptTypeEnum" Category="Option" Default="Compact" Description="How the script is rendered" %>

<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Collections" %>

<%
bool hasIdentity = HasIdentityColumn();
string tableName = string.Format("{0}.[{1}]", GetTableOwner(), SourceTable.Name);
string columnList = GetColumnList();
%>
USE <%= SourceTable.Database.Name %>
GO

DELETE <%=tableName%>
<% if (hasIdentity) {%>
DBCC CHECKIDENT ('<%=tableName%>', RESEED, 1)
SET IDENTITY_INSERT <%=tableName%> ON
<%}%>

<% if (ScriptType==ScriptTypeEnum.Compact) { %>
INSERT INTO <%=tableName%> (<%=columnList%>)
 <% for (int i = 0; i < SourceTableData.Rows.Count; i++) { %>
 SELECT <%= GetTableRowValues(SourceTableData.Rows[i]) %><% if (i < SourceTableData.Rows.Count - 1) { %> UNION<% } %>
 <% } %>
<%} else { for (int i = 0; i < SourceTableData.Rows.Count; i++) { %>
INSERT INTO <%=tableName%> (<%=columnList%>) VALUES (<%=GetTableRowValues(SourceTableData.Rows[i])%>)
<%}}%>

<% if (hasIdentity) {%>
SET IDENTITY_INSERT <%=tableName%> OFF
<%}%>
<script runat="template">

public enum ScriptTypeEnum
{
  Compact,
  Verbose
}

private DataTable _sourceTableData;

private DataTable SourceTableData
{
 get
 {
  if (_sourceTableData == null)
  {
   _sourceTableData = SourceTable.GetTableData();
  }
  
  return _sourceTableData;
 }
}

public string GetColumnList()
{
 ArrayList columnList = new ArrayList(SourceTable.Columns.Count);
 foreach(ColumnSchema column in SourceTable.Columns)
 {
  columnList.Add(string.Format("[{0}]", column.Name));
 }
 return string.Join(", ", (string[]) columnList.ToArray(typeof(string)));
}

public string GetTableRowValues(DataRow row)
{
 StringBuilder rowBuilder = new StringBuilder();
 
 int columnCount = SourceTable.Columns.Count;
 ArrayList valueList = new ArrayList(columnCount);
 for (int i=0; i<columnCount; i++)
 {
  ColumnSchema column = SourceTable.Columns[i];
  if (row[i] == DBNull.Value)
  {
   valueList.Add("NULL");
  }
  else
  {
   switch (column.NativeType.ToLower())
   {
    case "bigint":
    case "decimal":
    case "float":
    case "int":
    case "money":
    case "numeric":
    case "real":
    case "smallint":
    case "smallmoney":
    case "tinyint":
     // numeric type
     valueList.Add(row[i].ToString());
     break;
     
    case "bit":
     // boolean type
     string val = ((bool) row[i]) ? "1" : "0";
     valueList.Add(val);
     break;

    case "varbinary":     
    case "binary":
     // binary type
     valueList.Add(GetHexStringFromBytes((byte[])row[i]));
     break;
     
    case "datetime":
    case "smalldatetime":
     // datetime type
     DateTime dt = (DateTime) row[i];
     valueList.Add(string.Format("'{0:yyyy-MM-dd HH:mm:ss.fff}'", dt));
     break;

    default:
     // other type
     valueList.Add(string.Format("'{0}'", PrepareValue(row[i].ToString())));
     break;
   }
  }
 }
 return string.Join(", ", (string[]) valueList.ToArray(typeof(string)));
}

public string PrepareValue(string value)
{
 return value.Replace("'", "''").Replace("\r\n", "' + CHAR(13) + CHAR(10) + '").Replace("\n", "' + CHAR(10) + '");
}

public string GetTableOwner()
{
  string owner = SourceTable.Owner;
    if (!owner.Equals(string.Empty))
    {
        return string.Format("[{0}]", owner);
    }
    return string.Empty;
}

public string GetHexStringFromBytes(byte[] bytes)
{
  if (bytes == null)
  {
   return string.Empty;
  }
 
  int byteCount = bytes.Length;
 StringBuilder sb = new StringBuilder(byteCount * 2 + 2);
 sb.Append("0x");
  for (int i = 0; i < byteCount; i++)
  {
    sb.Append(bytes[i].ToString("X2"));
  }
  return sb.ToString();
}

public bool HasIdentityColumn()
{
 foreach(ColumnSchema column in SourceTable.Columns)
 {
  if (column.ExtendedProperties["CS_IsIdentity"].Value.ToString()=="True")
  {
   return true;
  }
 }
 return false;
}

</script>