"Dream as if you'll live forever, live as if you'll die today (James Dean)"
(ASP).NET - C# - System Architecture - and more...

Creating a temp table in ADO.NET with an existing table schema

Posted under ADO.NET |

I was recently in the situation where I had to perform a BulkCopy into a temp table, followed by executing a Merge on the SQL Server between this temp table and a permanent table.

The temp table needed to have the same schema as the existing table, however that schema could change. So I needed to construct a Create Table script, based on an existing schema.

Here is my code. I hope the comments are self explaining.

using (var connection = new SqlConnection(connectionString))
{
    var command = connection.CreateCommand();
    // This will return the table schema information
    command.CommandText = "select * from information_schema.columns where table_name = @tableName";
    command.Parameters.Add("@tableName", SqlDbType.VarChar).Value = "MyTable";
    command.CommandType = CommandType.Text;

    connection.Open();
    var columnList = new List<ColumnInfo>();
    // Loop over the results and create a ColumnInfo object for each Column in the schema.
    using (IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
    {
        while (reader.Read())
        {
            columnList.Add(new ColumnInfo().ReadFromReader(reader));
        }
    }

    string createTempCommand = "create table {0} ({1})";
    StringBuilder sb = new StringBuilder();
    // Loop over each column info object and construct the string needed for the SQL script.
    foreach (var column in columnList)
    {
        sb.Append(column.ToString());
    }

    // create temp table
    command.CommandText = string.Format(createTempCommand, "#TempTable", 
                          string.Join(",", columnList.Select(c => c.ToString()).ToArray()));
    command.ExecuteNonQuery();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName("#TempTable");
        bulkCopy.WriteToServer(readerWithData);
    }
}

I created a ColumnInfo object that represents the information for a column needed to create the sql statement.

public class ColumnInfo
{
    public string Name { get; set; }
    public string DataType { get; set; }
    public int OrdinalPosition { get; set; }
    public bool IsNullable { get; set; }
    public string MaxLength { get; set; }

    protected string MaxLengthFormatted
    {
        // note that columns with a max length return –1.
        get { return MaxLength.Equals("-1") ? "max" : MaxLength; }
    }

    public ColumnInfo ReadFromReader(IDataReader reader)
    {
        // get the necessary information from the datareader.
        // run the SQL on your database to see all the other information available.
        this.Name = reader["COLUMN_NAME"].ToString();
        this.DataType = reader["DATA_TYPE"].ToString();
        this.OrdinalPosition = (int)reader["ORDINAL_POSITION"];
        this.Nullable = ((string)reader["IS_NULLABLE"]) == "YES";
        this.MaxLength = reader["CHARACTER_MAXIMUM_LENGTH"].ToString();
        return this;
    }

    public override string ToString()
    {
        return string.Format("[{0}] {1}{2} {3}NULL", Name, DataType, 
            MaxLength == string.Empty ? "" : "(" + MaxLengthFormatted + ")", 
            IsNullable ? "" : "NOT ");
    }
}

I’ll be happy to hear any thoughts or improvements for doing the same task. Maybe by using an ORM such as Entity Framework or NHibernate?

Comments (0)

Sorry, due to immense comment spam on my site, comments are completely close for everyone until I find a way to fight them.
Disclaimer: The opinions expressed herin are my own personal opinions and do not represent my employer's view in any way.
© 2012 Ronald Rosier.     Creative Commons License
Title
content stuff to be said.