在使用sqlbulkcopy之前如何创建表 [英] How to create a table before using sqlbulkcopy
本文介绍了在使用sqlbulkcopy之前如何创建表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个要导入的DBF文件,然后将其写入SQL表。我遇到的问题是如果我使用SqlBulkCopy,它需要我提前创建表,但是在我的情况下这是不可能的,因为dbf文件不断变化。
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();
}
在 sqlcpy上一直失败。 WriteToServer(dt);
指出它无法访问目标表。
It keeps failing at sqlcpy.WriteToServer(dt);
stating it cannot access the destination table.
C#中是否有一个选项可以在写入之前快速创建表
Is there an option in C# to create the table on the fly before writing to that table?
推荐答案
此方法可以帮助您:
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);
}
}
}
您可以像这样使用它
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","john@company.com","56765765");
dt.Rows.Add("Tom","tom@company.com","8978987987");
ds.Tables.Add(dt);
AutoSqlBulkCopy(ds);
这篇关于在使用sqlbulkcopy之前如何创建表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文