Save DataTable to SQL Server

Published on 15 September 2010

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);  
        }  
  
          
  
    }