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