none
Creating a new table in SQL Server from ADO.NET DataTable

    Question

  • I have some C# code which is dynamically creating a DataTable, using ADO.NET. I would like to be able to create a new table in an SQL Server database, matching the schema of my DataTable object.

    Is there any ADO.NET functionality that I can leverage to do this? Or should I just go to plan B - construct and execute a CREATE TABLE command, using the properties of my DataTable to build the command - seems a bit clunky, with all the flash capability of ADO.NET.

    Thanks!

    Nick Tompson

    Friday, April 28, 2006 9:38 AM

Answers

All replies

  • You can use SQL Server Management Objects to create tables in SQL Server using SMO. Here is an example I wrote particular to creating a table:

     

    Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation

     

    There is a SQL Server SMO Forum here:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

     

    Hope this helps,

    Dave

     

    Friday, April 28, 2006 11:48 AM
  • Dave,

    That's a great help - I wasn't aware of SMO before. Certainly much easier than having to construct a CREATE TABLE query.

    Is there any integration between the System.Data.DataTable object and the SMO Table object? ie Is it possible to use a DataTable as the basis for an SMO Table object - either by coercion, or by using a DataTable object as a reference?

    Cheers,

    Nick

    Monday, May 01, 2006 4:12 AM
  • That would certainly be a cool idea, but to the best of my knowledge there is no way to hand off a DataTable to SMO and have it create a physical table in SQL Server based on the DataTable's metadata. That would be an interesting class to write.

    Regards,

    Dave

    Monday, May 01, 2006 1:47 PM
  • .. and it would be very useful to me. For reasons too complicated to mention I want to create tables in an SQL database based on DBase files. The ADO.NET DataTable is an in-memory representation of a database table. So I thought I'd be able to use it as a translator between DBFs and SQL databases. I suppose the problem lies in data type differences. If I have to specify every column there'll be no advantage in doing it programmatically. Seems to me there are assumptions one could make about data types in DBase and SQL (or Excel and SQL etc) that would hold good nearly all of the time. Surely someone's got a class out there that'll do this? Please!!
    Tuesday, May 16, 2006 12:01 AM
  • Further to this post, 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.

    See what you think! Constructive critisism gratefully received.

    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++) {

                    primaryKeysIdea = 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

        }

     

    • Proposed as answer by sanka060707 Wednesday, September 09, 2009 11:04 PM
    Sunday, December 10, 2006 1:08 AM
  • Yummy!  Thanks!

     

    BTW, if you combine that with the SQLBulkCopy class, you've got soome coolness going on, build an fill a SQL Server table from any ADO.NET DataTable:

     

    private void BulkLoad(DataTable tbl)

    {

    string conn = System.Configuration.ConfigurationManager.ConnectionString["myConnectionString"].ConnectionString;

     

    System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn);

     

    bulkCopy.DestinationTableName = tbl.TableName;

     

    try

    {

    bulkCopy.WriteToServer(tbl);

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }

    finally

    {

    bulkCopy.Close();

    }

    }

    Tuesday, December 02, 2008 4:30 PM
  • Thank You!!!  that is exactly what i have searched ...

    In my case it was required to add 2 Data Types and add a ")" to the SQL-String:

    Now it works great :-)

    ----

    SQLGetType ...

    ...

    Case "System.Boolean"
                        Return "BIT"

    Case "System.Byte"
                        Return "TINYINT"

    ...

    GetCreateFromDataTableSQL ...

    ...

     '// if not ends with ")"
    If table.PrimaryKey.Length = 0 And Not sql.EndsWith(")") Then sql += ")"

    ...

    ----

    Greetings Ralph

     

    Friday, March 26, 2010 10:32 AM
  • Thanks for the useful code, I have improved it slightly.

    Check to see if the field can be 'NOT NULL', also put table fields and columns within '[', ']' to prevent it crashing when the colmn name contains a space or reserved name.

    The constraint also needed an extra comma to work.

        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;
        }
    
    Saturday, June 12, 2010 7:16 PM
  • Thank U very much Nick and bot121, this code is very useful...

     

    Just one thing, in the method 'GetCreateFromDataTableSQL' is neccesary to put an 'else' statement, because if the table does not have a PK it sends an error about sql-sintaxis.

    else {
                    sql += ")";
                }

     

    Greetings.

    Wednesday, July 07, 2010 5:10 PM
  • Good job. Thanks.

     

    Case "System.Guid"
                        Return "UNIQUEIDENTIFIER"

     

    Ruprecht Dröge

    • Proposed as answer by Sergey Chanaev Tuesday, November 30, 2010 9:36 AM
    Friday, October 08, 2010 8:12 AM
  • Hi, I am a vb.net guy but want this class. However, there appears to be an emoticon with the alt text "idea" where there should be some code. Its in the for loop within the

    public object Create(DataTable schema, int numKeys) {

                int[] primaryKeys = new int[numKeys];

                for (int i = 0; i < numKeys; i++) {

                    primaryKeysIdea = i;

                }

                return Create(schema, primaryKeys);

            }

    It causes an error obivously, but what is supposed to go there? I am going to try [i], but I think thats wrong. Assistance appreciated, and thanks in advance!

    Wednesday, November 03, 2010 2:45 AM
  • [i] is correct, I think

    Monday, March 21, 2011 12:46 AM
  • I compiled the complete code listing and put it here:

    http://wp.me/p14j9T-8p

     

     

     

    Monday, June 27, 2011 10:23 PM
  • Should this class implement IDisposable, since it owns IDisposable resources?
    eric bennett
    Saturday, October 15, 2011 3:17 PM
  • I am new to C#. This is what i am trying to accomplish with this code. I want to load a csv file to a new sql table as a staging table and then update my tables using the data in the staging table. I am using sqlbulkcopy to load csv data into a data table. Now i want to use this class to create a sql table for the csv data table.

     

    Thanks,

    Anu

    Friday, December 30, 2011 3:44 PM
  • I made a few tweaks to make it more compatible, I changed the last 3 methods. I changed some of the data types and I changed the parsing of SQLGetType(DataRow schemaRow) as sometimes NumbreicPrecision and NumericScale can be DBNull

            // Return T-SQL data type definition, based on schema definition for a column
            // Based off of http://msdn.microsoft.com/en-us/library/ms131092.aspx
            public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
            {
                switch (type.ToString())
                {
                    case "System.Byte[]":
                        return "VARBINARY(MAX)";
    
                    case "System.Boolean":
                        return "BIT";
                        
                    case "System.DateTime":
                        return "DATETIME";
    
                    case "System.DateTimeOffset":
                        return "DATETIMEOFFSET";
    
                    case "System.Decimal":
                        if (numericPrecision != -1 && numericScale != -1)
                            return "DECIMAL(" + numericPrecision + "," + numericScale + ")";
                        else
                            return "DECIMAL";
    
                    case "System.Double":
                        return "FLOAT";
    
                    case "System.Single":
                        return "REAL";
    
                    case "System.Int64":
                        return "BIGINT";
    
                    case "System.Int32":
                        return "INT";
    
                    case "System.Int16":
                        return "SMALLINT";
    
                    case "System.String":
                        return "NVARCHAR(" + ((columnSize == -1 || columnSize > 8000) ? "MAX" : columnSize.ToString()) + ")";
    
                    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)
            {
                int numericPrecision;
                int numericScale;
    
                if (!int.TryParse(schemaRow["NumericPrecision"].ToString(), out numericPrecision))
                {
                    numericPrecision = -1;
                }
                if (!int.TryParse(schemaRow["NumericScale"].ToString(), out numericScale))
                {
                    numericScale = -1;
                }
    
                return SQLGetType(schemaRow["DataType"],
                                    int.Parse(schemaRow["ColumnSize"].ToString()),
                                    numericPrecision,
                                    numericScale);
            }
            // Overload based on DataColumn from DataTable type
            public static string SQLGetType(DataColumn column)
            {
                return SQLGetType(column.DataType, column.MaxLength, -1, -1);
            }
    Friday, April 27, 2012 3:31 PM
  • Very nice.  This is very good work and it is pretty much what I was looking for. 

    One suggestion is to implement this as a base class where SQLGetType is virtual.  In such a case you could inherit the class and override the SQLGetType  to implement that same functionality across a wide variety of databases.  The overridden method would only return values for altered types and defer back to the base class in all other cases.

    I came here looking to implement this functionality in SQLite and not SQLServer so I will probably implement these changes myself.  I'll post them back here if anyone is interested in having it.

    • Proposed as answer by Habip OĞUZ Friday, October 12, 2012 10:25 PM
    • Unproposed as answer by Habip OĞUZ Friday, October 12, 2012 10:25 PM
    Friday, May 18, 2012 4:27 PM
  • Firstly, i am beginner in C#. So i don't know how to use SqlTableCreator class. I created an SQL Server Compact database like "data.sdf". I am trying to get tables' schemas from MSSQL Server and transfer them into data.sdf. Up to now, i did not manage. :( Could you help me?

    EDIT: Hello everyone. I did not manage to get connection. Please help me. How can I get the connection?



    • Edited by Habip OĞUZ Saturday, October 13, 2012 11:59 AM
    Friday, October 12, 2012 10:28 PM
  • I copied the code from the new location and I'm getting the same error as I did with the original listing (posted here) which is:

    "Incorrect syntax near ')'.

    I'm trying to create a new table called Employee2 and the sql command string within the SQLCreateTable function is:

    "CREATE TABLE [Employee2] (\n)"

    is that proper SQL syntax? (I'm brand new to SQL).


    Dan

    shouldn't the foreach loop be "foreach(DataColumn column in schema.Column) ?

    I'm adding the columns to the DataTable that gets passed in but there are no rows since its a

    new table I'm creating.

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

           Oh well, one way to find out...

    Nevermind, it works. I was calling Create() instead of CreateFromDataTable()


    • Edited by zliminator Tuesday, December 18, 2012 4:12 PM
    Tuesday, December 18, 2012 2:39 PM