German Wear Discount Shop - Click Here Write for Dotnet-friends and earn for your each submission [Dot]Net-Friends
Skip Navigation Links
Home
Latest
Fast Code
Articles
Tutorials
Online Resources
Forums
Login   | Hi, Guest


Creating a new table in SQL Server from ADO.NET DataTable
Written By Dotnet Friends On 07/11/2007

You created a DataTable from some Data Source and now you will like to save it to your Database.

Views: 355
Rating: 5
Login to Rate
dotnet-friends
Tagged Under: ADO .NET, Database, SQL Server 2005

Explanation:

I was searching for a solution so I can save my dynamically created DataTable to my Database. I could not find any direct method in .NET. Then I came to this solution. I guess it will be very helpfull for many guys.

 I have built a class that can create an SQL Server table from either a DataTable, or the Schema of a IDataReader (r.GetSchemaTable()). It is somewhat optimised to my purposes, but it is easily modified to your own.

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 += column["ColumnName"].ToString() + " " + SQLGetType(column) + ",\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";
            }
 
            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) + ")";
 
                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 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
    }
Delicious Digg reddit reddit Technorati
About the Author:

@@ Omer Kamal is a Software Developer at Eidanza GmbH Germany. He is Founder of FriendsPoint.de and Dotnet-Friends.com. He is currently Involved with SharePoint 2007, SharePoint Services 3.0 and BI Portal Solutions (Microsoft Dynamics Customization).
Check Dotnet Friends Profile

Related Useful Links:
Visitors/Readers Comments
(for questions please use The Forum)



visitman
This is very useful code. It saved a lote of my time. Thanks!

13/11/2007 05:12:00 UTC




Add your Comments

Name:  
Message:
Note: For faster response please use Forums >> for your questions instead of the comments area! (Admin)