Converting a DataTable to an CREATE TABLE + INSERT script for SQL in C#

First, I would use this object to build your CREATE TABLE command.

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;namespace Toolkit{    public class SqlTableCreator    {        #region Instance Variables        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; }        }        #endregion        #region Constructor        public SqlTableCreator() { }        public SqlTableCreator(SqlConnection connection) : this(connection, null) { }        public SqlTableCreator(SqlConnection connection, SqlTransaction transaction)        {            _connection = connection;            _transaction = transaction;        }        #endregion        #region Instance Methods        public object Create(DataTable schema)        {            return Create(schema, null);        }        public object Create(DataTable schema, int numKeys)        {            int[] primaryKeys = new int[numKeys];            for (int i = 0; i < numKeys; i++)            {                primaryKeys[i] = i;            }            return Create(schema, primaryKeys);        }        public object Create(DataTable schema, int[] primaryKeys)        {            string sql = GetCreateSQL(_tableName, schema, primaryKeys);            SqlCommand cmd;            if (_transaction != null && _transaction.Connection != null)                cmd = new SqlCommand(sql, _connection, _transaction);            else                cmd = new SqlCommand(sql, _connection);            return cmd.ExecuteNonQuery();        }        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();        }        #endregion        #region Static Methods        public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys)        {            string sql = "CREATE TABLE [" + tableName + "] (\n";            // columns            foreach (DataRow column in schema.Rows)            {                if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))                {                    sql += "\t[" + column["ColumnName"].ToString() + "] " + SQLGetType(column);                    if (schema.Columns.Contains("AllowDBNull") && (bool)column["AllowDBNull"] == false)                        sql += " NOT NULL";                    sql += ",\n";                }            }            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";            // primary keys            string pk = ", CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";            bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);            if (hasKeys)            {                // user defined keys                foreach (int key in primaryKeys)                {                    pk += schema.Rows[key]["ColumnName"].ToString() + ", ";                }            }            else            {                // check schema for keys                string keys = string.Join(", ", GetPrimaryKeys(schema));                pk += keys;                hasKeys = keys.Length > 0;            }            pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";            if (hasKeys) sql += pk;            sql += ")";            return sql;        }        public static string GetCreateFromDataTableSQL(string tableName, DataTable table)        {            string sql = "CREATE TABLE [" + tableName + "] (\n";            // columns            foreach (DataColumn column in table.Columns)            {                sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";            }            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";            // primary keys            if (table.PrimaryKey.Length > 0)            {                sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";                foreach (DataColumn column in table.PrimaryKey)                {                    sql += "[" + column.ColumnName + "],";                }                sql = sql.TrimEnd(new char[] { ',' }) + "))\n";            }            //if not ends with ")"            if ((table.PrimaryKey.Length == 0) && (!sql.EndsWith(")")))            {                sql += ")";            }            return sql;        }        public static string[] GetPrimaryKeys(DataTable schema)        {            List<string> keys = new List<string>();            foreach (DataRow column in schema.Rows)            {                if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])                    keys.Add(column["ColumnName"].ToString());            }            return keys.ToArray();        }        // 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 "VARCHAR(" + ((columnSize == -1) ? "255" : (columnSize > 8000) ? "MAX" : columnSize.ToString() ) + ")";                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";                case "System.Boolean":                    return "BIT";                case "System.Byte":                    return "TINYINT";                case "System.Guid":                    return "UNIQUEIDENTIFIER";                default:                    throw new Exception(type.ToString() + " not implemented.");            }        }        // Overload based on row from schema table        public static string SQLGetType(DataRow schemaRow)        {            return SQLGetType(schemaRow["DataType"],                                int.Parse(schemaRow["ColumnSize"].ToString()),                                int.Parse(schemaRow["NumericPrecision"].ToString()),                                int.Parse(schemaRow["NumericScale"].ToString()));        }        // Overload based on DataColumn from DataTable type        public static string SQLGetType(DataColumn column)        {            return SQLGetType(column.DataType, column.MaxLength, 10, 2);        }        #endregion    }}

Then I would use SQL BulkCopy rather than separate INSERT statements.

public static void BulkInsertDataTable(string connectionString, string tableName, DataTable table)        {            using (SqlConnection connection = new SqlConnection(connectionString))            {                SqlBulkCopy bulkCopy =                    new SqlBulkCopy                    (                    connection,                    SqlBulkCopyOptions.TableLock |                    SqlBulkCopyOptions.FireTriggers |                    SqlBulkCopyOptions.UseInternalTransaction,                    null                    );                bulkCopy.DestinationTableName = tableName;                connection.Open();                bulkCopy.WriteToServer(table);                connection.Close();            }        }

public void createsqltable(DataTable dt,string tablename)        {            string strconnection = "";            string table = "";            table += "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tablename + "]') AND type in (N'U'))";            table += "BEGIN ";            table += "create table " + tablename + "";            table += "(";            for (int i = 0; i < dt.Columns.Count; i++)            {                if (i != dt.Columns.Count-1)                    table += dt.Columns[i].ColumnName + " " + "varchar(max)" + ",";                else                    table += dt.Columns[i].ColumnName + " " + "varchar(max)";            }            table += ") ";            table += "END";            InsertQuery(table,strconnection);            CopyData(strconnection, dt, tablename);        }        public void InsertQuery(string qry,string connection)        {            SqlConnection _connection = new SqlConnection(connection);            SqlCommand cmd = new SqlCommand();            cmd.CommandType = CommandType.Text;            cmd.CommandText = qry;            cmd.Connection = _connection;            _connection.Open();            cmd.ExecuteNonQuery();            _connection.Close();        }        public static void CopyData(string connStr, DataTable dt, string tablename)        {            using (SqlBulkCopy bulkCopy =            new SqlBulkCopy(connStr, SqlBulkCopyOptions.TableLock))            {                bulkCopy.DestinationTableName = tablename;                bulkCopy.WriteToServer(dt);            }        }

Take a look at: Generate SQL INSERT commands programmatically

Essentially, the code is looping through the DataTable.Columns collection to generate the column list of the INSERT statement. Then it loops through the values of the DataTable to create the parameters with values.