How to create a table before using sqlbulkcopy

Question

I have a DBF file which I am trying to import and then write that to a SQL table. The issue I am running into is if I use SqlBulkCopy, it requires me to create the table in advance but that is not possible in my scenario as the dbf file changes constantly.

Here is my code so far:

public void saveDBF()
        {

            //define the connections to the .dbf file
            OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+ Path.GetDirectoryName(tbFile.Text)+";Extended Properties=dBase III");

            OleDbCommand command = new OleDbCommand("select * from " + Path.GetFileName(tbFile.Text), oConn);

            //open the connection and read in all the airport data from .dbf file into a datatable

            oConn.Open();
            DataTable dt = new DataTable();

            dt.Load(command.ExecuteReader());

            oConn.Close();  //close connection to the .dbf file

            //create a reader for the datatable
            DataTableReader reader = dt.CreateDataReader();

            myConnection = new SqlConnection(cString);
            myConnection.Open();   ///this is my connection to the sql server
            SqlBulkCopy sqlcpy = new SqlBulkCopy(myConnection);

            sqlcpy.DestinationTableName = "TestDBF";  //copy the datatable to the sql table

            sqlcpy.WriteToServer(dt);

            myConnection.Close();

            reader.Close();

        }

It keeps failing at sqlcpy.WriteToServer(dt); stating it cannot access the destination table.

Is there an option in C# to create the table on the fly before writing to that table?

Answer 1

This method can help you:

static void AutoSqlBulkCopy(DataSet dataSet)
{
    var sqlConnection = new SqlConnection("Data Source=sqlServer;Initial Catalog=mydatabase;user id=myuser;password=mypass;App=App");
    sqlConnection.Open();
    foreach (DataTable dataTable in dataSet.Tables)
    {
        // checking whether the table selected from the dataset exists in the database or not
        var checkTableIfExistsCommand = new SqlCommand("IF EXISTS (SELECT 1 FROM sysobjects WHERE name =  '" + dataTable.TableName + "') SELECT 1 ELSE SELECT 0", sqlConnection);
        var exists = checkTableIfExistsCommand.ExecuteScalar().ToString().Equals("1");

        // if does not exist
        if (!exists)
        {
            var createTableBuilder = new StringBuilder("CREATE TABLE [" + dataTable.TableName + "]");
            createTableBuilder.AppendLine("(");

            // selecting each column of the datatable to create a table in the database
            foreach (DataColumn dc in dataTable.Columns)
            {
                createTableBuilder.AppendLine("  ["+ dc.ColumnName + "] VARCHAR(MAX),");
            }

            createTableBuilder.Remove(createTableBuilder.Length - 1, 1);
            createTableBuilder.AppendLine(")");

            var createTableCommand = new SqlCommand(createTableBuilder.ToString(), sqlConnection);
            createTableCommand.ExecuteNonQuery();
        }

        // if table exists, just copy the data to the destination table in the database
        // copying the data from datatable to database table
        using (var bulkCopy = new SqlBulkCopy(sqlConnection))
        {
            bulkCopy.DestinationTableName = dataTable.TableName;
            bulkCopy.WriteToServer(dataTable);
        }
    }
}

And you can use it like this:

var ds = new DataSet("MyDataSet");
var dt = new DataTable("MyDataTable");
dt.Columns.Add(new DataColumn("name", typeof(string)));
dt.Columns.Add(new DataColumn("email", typeof(string)));
dt.Columns.Add(new DataColumn("phone", typeof(string)));

dt.Rows.Add("John","[email protected]","56765765");
dt.Rows.Add("Tom","[email protected]","8978987987");
ds.Tables.Add(dt);
AutoSqlBulkCopy(ds);