如何在使用sqlbulkcopy之前创建表

本文关键字：创建 sqlbulkcopy | 更新日期: 2023-09-27 18:06:39

我有一个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#中是否有一个选项可以在写入表之前动态创建表?

如何在使用sqlbulkcopy之前创建表

这个方法可以帮助您:

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);
相关文章：