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?

Mozellamozelle

have you thought about doing a bulk copy so to speak via XML – Ers

You can use this function before write to server the data table: https://mcmap.net/q/352496/-creating-a-sql-server-table-from-a-c-datatable – Hoey

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);

Hoey

So add that before my sqlcopy.WriteToServer(dt); line? – Mozellamozelle

@SiKni8 yes but adapt the code from your.... for example replace the name of the connection sqlcon->myConnection ...etc. – Hoey

Thanks. For this line SqlCommand cmd = new SqlCommand("SELECT * FROM " + tbTableName.Text + " where name = '" + dt.TableName + "'", myConnection); is it correct? I am allowing the user to enter the TABLE name they would like to save the data to. Or should it be SqlCommand cmd = new SqlCommand("SELECT * FROM " + tbTableName.Text + " where name = '" + tbTableName.Text + "'", myConnection); – Mozellamozelle

After looking through the code, it seems I have to replace all the dt.TableName to tbTableName.Text to allow the user to enter a table name? Correct? – Mozellamozelle

@SiKni8 yes if the you don't want to use the Data Table name and you want to add custom table name. – Hoey

Thank you. Do I still have to do ColumnMappings? – Mozellamozelle

No because the names from the data table is the same from the database table – Hoey

I am just a bit confused as to what should be the DataTable dt in ds.Tables for my code? – Mozellamozelle

I Upvoted because it is something that will most likely end up being my solution after I fix up the nooks and crannies of the code :) – Mozellamozelle

comment or remove this foreach ... this help you if you have two or more table into dataset – Hoey

Awesome it worked!!!! :) I wonder how can I get the forloop working so in case I do have multiple in the future I can use the existing code. – Mozellamozelle

you can send some parameters to saveDBF() function and you can use this function in future – Hoey

No need for the if/else, you are writing the same code twice. DRY. Simply end the if and write the Bulkcopy code after it, its the same code no matter what. – Bauble

What about using dc.DataType as column datatype instead of VARCHAR(MAX)? – Hellen