none
Create table if not exists RRS feed

  • Question

  • Hi Experts, 

    Is it possible to create a table out of the DataTable in case it doesn't exist in the destination before performing the SQLBulkCopy.

     public static void DataToSQL(DataTable table, String tableName)
            {
                using (SqlConnection cnn = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("COnnetionDetails")))
                {
                    cnn.Open();
    
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn))
                    {
    
                        bulkCopy.DestinationTableName = $"dbo.{tableName}";
                        DataTableReader reader = table.CreateDataReader();
                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(reader);
                        }
    					
    					Catch (Exception E)
    					{
    					Console.WriteLine (E);
    					}
    	   }

    Thanks in advance

    Priya

    Monday, December 9, 2019 8:29 AM

All replies

  • Hello,

    The following page shows two methods (actually more but stick with the top two)

    https://stackoverflow.com/questions/5952006/check-if-table-exists-and-if-it-doesnt-exist-create-it-in-sql-server-2008

    Here is a working code sample were mHasException, mLastException are from my NuGet package.

    using System;
    using System.Data.SqlClient;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace CreateTableIfDoesNotExists
    {
        public class Operations : SqlServerConnection
        {
            public Operations()
            {
                DatabaseServer = ".\\SQLEXPRESS";
                DefaultCatalog = "KarenDatabase";
            }
            public void CreateTable(string tableName)
            {
                var statement = $@"
                IF  NOT EXISTS (SELECT * FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'[dbo].[{tableName}]') AND type in (N'U'))
    
                BEGIN
                CREATE TABLE [dbo].[{tableName}](
    	            [Id] [int] IDENTITY(1,1) NOT NULL,
    	            [Ip] [nvarchar](max) NULL,
    	            [User] [nvarchar](max) NULL,
    	            [Actions] [nvarchar](max) NULL,
    	            [Created] [datetime2](7) NULL,
                 CONSTRAINT [PK_{tableName}1] PRIMARY KEY CLUSTERED 
                (
    	            [Id] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
                END";
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand {Connection = cn})
                    {
                        cmd.CommandText = statement;
                        try
                        {
                            cn.Open();
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            mHasException = true;
                            mLastException = ex;
                        }
                    }
                }
            }
    
        }
    }
    

    Calling the above

    using System;
    using System.Windows.Forms;
    
    namespace CreateTableIfDoesNotExists
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var operations = new Operations();
                operations.CreateTable("Customers");
                if (operations.IsSuccessFul)
                {
                    MessageBox.Show("Table exists");
                }
                else
                {
                    MessageBox.Show($"Error: {operations.LastExceptionMessage}");
                }
    
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, December 9, 2019 10:48 AM
    Moderator
  • Hello,

    The following page shows two methods (actually more but stick with the top two)

    https://stackoverflow.com/questions/5952006/check-if-table-exists-and-if-it-doesnt-exist-create-it-in-sql-server-2008

    Here is a working code sample were mHasException, mLastException are from my NuGet package.

    using System;
    using System.Data.SqlClient;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace CreateTableIfDoesNotExists
    {
        public class Operations : SqlServerConnection
        {
            public Operations()
            {
                DatabaseServer = ".\\SQLEXPRESS";
                DefaultCatalog = "KarenDatabase";
            }
            public void CreateTable(string tableName)
            {
                var statement = $@"
                IF  NOT EXISTS (SELECT * FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'[dbo].[{tableName}]') AND type in (N'U'))
    
                BEGIN
                CREATE TABLE [dbo].[{tableName}](
    	            [Id] [int] IDENTITY(1,1) NOT NULL,
    	            [Ip] [nvarchar](max) NULL,
    	            [User] [nvarchar](max) NULL,
    	            [Actions] [nvarchar](max) NULL,
    	            [Created] [datetime2](7) NULL,
                 CONSTRAINT [PK_{tableName}1] PRIMARY KEY CLUSTERED 
                (
    	            [Id] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
                END";
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand {Connection = cn})
                    {
                        cmd.CommandText = statement;
                        try
                        {
                            cn.Open();
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            mHasException = true;
                            mLastException = ex;
                        }
                    }
                }
            }
    
        }
    }

    Calling the above

    using System;
    using System.Windows.Forms;
    
    namespace CreateTableIfDoesNotExists
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var operations = new Operations();
                operations.CreateTable("Customers");
                if (operations.IsSuccessFul)
                {
                    MessageBox.Show("Table exists");
                }
                else
                {
                    MessageBox.Show($"Error: {operations.LastExceptionMessage}");
                }
    
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Dear Ma'am, 

    Thanks a lot for the support & shared solution. Am basically reading data from a SQL Server storing it inside a data table and before loading it into another SQL Server ,  I need to ensure that the table exists if not create the table using the DataTable ... Could you please share if possible how we can create another persistent table inside SQL Server using DataTable.

    Thanks again

    Priya

    Monday, December 9, 2019 3:10 PM
  • Hi Priya Bange,
    You can build a “Create Table” statement based on the DataTable and send that to Database.
    Here is a code example you can refer to.

    private void button1_Click(object sender, EventArgs e)
    {
        DataTable table = new DataTable("Users");
        table.Columns.Add(new DataColumn()
        {
            ColumnName = "UserId",
            DataType = System.Type.GetType("System.Int32"),
            AutoIncrement = true,
            AllowDBNull = false,
            AutoIncrementSeed = 1,
            AutoIncrementStep = 1
        });
        table.Columns.Add(new DataColumn()
        {
            ColumnName = "UserName",
            DataType = System.Type.GetType("System.String"),
            AllowDBNull = true,
            DefaultValue = String.Empty,
            MaxLength = 50
        });
    
        table.Columns.Add(new DataColumn()
        {
            ColumnName = "LastUpdate",
            DataType = System.Type.GetType("System.DateTime"),
            AllowDBNull = false,
            DefaultValue = DateTime.Now,
            Caption = "<defaultValue>GETDATE()</defaultValue>"
        });
    
        table.PrimaryKey = new DataColumn[] { table.Columns[0] };
        string sql = GetCreateTableSql(table);
        Console.WriteLine(sql);
    
    }
    public static string GetCreateTableSql(DataTable table)
    {
        StringBuilder sql = new StringBuilder();
        StringBuilder alterSql = new StringBuilder();
    
        sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);
        for (int i = 0; i < table.Columns.Count; i++)
        {
            bool isNumeric = false;
            bool usesColumnDefault = true;
            sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);
            switch (table.Columns[i].DataType.ToString().ToUpper())
            {
                case "SYSTEM.INT16":
                    sql.Append(" smallint");
                    isNumeric = true;
                    break;
                case "SYSTEM.INT32":
                    sql.Append(" int");
                    isNumeric = true;
                    break;
                case "SYSTEM.INT64":
                    sql.Append(" bigint");
                    isNumeric = true;
                    break;
                case "SYSTEM.DATETIME":
                    sql.Append(" datetime");
                    usesColumnDefault = false;
                    break;
                case "SYSTEM.STRING":
                    sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
                    break;
                case "SYSTEM.SINGLE":
                    sql.Append(" single");
                    isNumeric = true;
                    break;
                case "SYSTEM.DOUBLE":
                    sql.Append(" double");
                    isNumeric = true;
                    break;
                case "SYSTEM.DECIMAL":
                    sql.AppendFormat(" decimal(18, 6)");
                    isNumeric = true;
                    break;
                default:
                    sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
                    break;
            }
    
            if (table.Columns[i].AutoIncrement)
            {
                sql.AppendFormat(" IDENTITY({0},{1})",
                    table.Columns[i].AutoIncrementSeed,
                    table.Columns[i].AutoIncrementStep);
            }
            else
            {
                if (table.Columns[i].DefaultValue != null)
                {
                    if (usesColumnDefault)
                    {
                        if (isNumeric)
                        {
                            alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ({2}) FOR [{1}];",
                                table.TableName,
                                table.Columns[i].ColumnName,
                                table.Columns[i].DefaultValue);
                        }
                        else
                        {
                            alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ('{2}') FOR [{1}];",
                                table.TableName,
                                table.Columns[i].ColumnName,
                                table.Columns[i].DefaultValue);
                        }
                    }
                    else
                    {
                        try
                        {
                            System.Xml.XmlDocument xml = new System.Xml.XmlDocument();
    
                            xml.LoadXml(table.Columns[i].Caption);
    
                            alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ({2}) FOR [{1}];",
                               table.TableName,
                                table.Columns[i].ColumnName,
                                xml.GetElementsByTagName("defaultValue")[0].InnerText);
                        }
                        catch
                        {
                       }
                    }
                }
            }
            if (!table.Columns[i].AllowDBNull)
            {
                sql.Append(" NOT NULL");
            }
            sql.Append(",");
        }
    
        if (table.PrimaryKey.Length > 0)
        {
            StringBuilder primaryKeySql = new StringBuilder();
    
            primaryKeySql.AppendFormat("\n\tCONSTRAINT PK_{0} PRIMARY KEY (", table.TableName);
            for (int i = 0; i < table.PrimaryKey.Length; i++)
            {
                primaryKeySql.AppendFormat("{0},", table.PrimaryKey[i].ColumnName);
            }
    
            primaryKeySql.Remove(primaryKeySql.Length - 1, 1);
            primaryKeySql.Append(")");
            sql.Append(primaryKeySql);
        }
        else
        {
            sql.Remove(sql.Length - 1, 1);
        }
        sql.AppendFormat("\n);\n{0}", alterSql.ToString());
        return sql.ToString();
    }

    Besides, you can use SQL Server Managment objects as well.
    Here are some useful links you can refer to.
    [Creating a new table in SQL Server from ADO.NET DataTable]
    [Creating a SQL Server table from a C# datatable]
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 25, 2019 9:09 AM