如何在使用sqlbulkcopy之前创建一张表

Question

如何在使用sqlbulkcopy之前创建一张表

9

我有一个DBF文件，正在尝试将其导入并将其写入SQL表中。我遇到的问题是，如果使用SqlBulkCopy，它要求我提前创建表，但在我的情况下不可能，因为dbf文件会不断变化。

这是我到目前为止的代码：

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);时它一直失败，并声称无法访问目标表。

C#中是否有选项可以在写入该表之前动态创建该表？

- Si8

你有没有考虑通过XML进行批量复制？ - MethodMan

在将数据表写入服务器之前，您可以使用此函数：https://dev59.com/y3M_5IYBdhLWcg3wgzZl#11360186 - TotPeRo

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- TotPeRo · Accepted Answer

这个方法能够帮助您：

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