Save DataTable to SQL Server

A couple of times now, I've needed to save in-memory ADO.NET DataTables straight to SQL Server as new tables. Its handy sometimes, if you're importing data from other sources. Nick Tompson had posted some code on the social.msdn forum to do this, and here I've stripped out the features I didn't need and adapted it to use StringBuilders:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


    // Save a DataTable to a SQLServer Table
    // Based on Nick Tompsons code from 
    // http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/4929a0a8-0137-45f6-86e8-d11e220048c3/
    // Removed 'make from Schema' options to simplify, and used StringBuilders.
    public class SqlTableCreator
    {
        
        private SqlConnection _connection;
        public SqlConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }

        private SqlTransaction _transaction;
        public SqlTransaction Transaction
        {
            get { return _transaction; }
            set { _transaction = value; }
        }

        private string _tableName;
        public string DestinationTableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        

        

        public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
        public SqlTableCreator(SqlConnection connection, SqlTransaction transaction)
        {
            _connection = connection;
            _transaction = transaction;
        }
        


        public object CreateFromDataTable(DataTable table)
        {
            string sql = GetCreateFromDataTableSQL(_tableName, table);

            SqlCommand cmd;
            if (_transaction != null && _transaction.Connection != null)
                cmd = new SqlCommand(sql, _connection, _transaction);
            else
                cmd = new SqlCommand(sql, _connection);

            return cmd.ExecuteNonQuery();
        }


        public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE TABLE [{0}] (", tableName);
            
            // columns
            bool first = true;
            foreach (DataColumn column in table.Columns)
            {
                if (first)
                    first = false;
                else
                    sb.Append(",");

                sb.AppendFormat("\n[{0}] {1}", column.ColumnName, SQLGetType(column));
            }
            
            // primary keys
            if (table.PrimaryKey.Length > 0)
            {
                sb.AppendFormat(" CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED (", tableName);
                bool first2 = true;
                foreach (DataColumn column in table.PrimaryKey)
                {
                    if (first2)
                        first2 = false;
                    else
                        sb.Append(",");

                    sb.AppendFormat("[{0}]", column.ColumnName);
                }
                sb.Append(")\n");
            }
            sb.Append(")\n");

            return sb.ToString();
        }


        // Return T-SQL data type definition, based on schema definition for a column
        public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return String.Format("VARCHAR({0})", ((columnSize == -1) ? 255 : columnSize));

                case "System.Decimal":
                    if (numericScale > 0)
                        return "REAL";
                    else if (numericPrecision > 10)
                        return "BIGINT";
                    else
                        return "INT";

                case "System.Double":
                case "System.Single":
                    return "REAL";

                case "System.Int64":
                    return "BIGINT";

                case "System.Int16":
                case "System.Int32":
                    return "INT";

                case "System.DateTime":
                    return "DATETIME";

                default:
                    throw new ApplicationException(String.Format("{0} not implemented.",type.ToString()));
            }
        }

        // Overload based on DataColumn from DataTable type
        public static string SQLGetType(DataColumn column)
        {
            return SQLGetType(column.DataType, column.MaxLength, 10, 2);
        }

        

    }