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?

have you thought about doing a bulk copy so to speak via XML — MethodMan
– MethodMan, Commented Jun 4, 2014 at 20:20
You can use this function before write to server the data table: stackoverflow.com/a/11360186/2327332 — TotPeRo
– TotPeRo, Commented Jun 4, 2014 at 20:21

TotPeRo · Accepted Answer · 2019-03-04 09:00:39Z

23

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

edited Mar 4, 2019 at 9:00

answered Jun 4, 2014 at 20:23

TotPeRo

6,8314 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Si8 Over a year ago

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

TotPeRo Over a year ago

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

Si8 Over a year ago

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

TotPeRo Over a year ago

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

Iftikhar Over a year ago

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

|

Collectives™ on Stack Overflow

How to create a table before using sqlbulkcopy

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related