从MS Access批量导入并插入到Sql Server中

本文关键字：插入 Sql Server 导入 MS Access | 更新日期: 2023-09-27 17:53:32

我想从MS Access数据库中读取记录并插入到Sql server数据库中，该过程应该是批量插入。我使用asp.net/vb.net

从MS Access批量导入并插入到Sql Server中

首先从Excel表格中读取数据

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/temp/") + "FileName.xlsx; Extended Properties=Excel 12.0;";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        DbDataAdapter adapter = factory.CreateDataAdapter();
        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT ColumnNames FROM [Sheet1$]";
        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;
        selectCommand.Connection = connection;
        adapter.SelectCommand = selectCommand;
        DataTable dtbl = new DataTable();
        adapter.Fill(dtbl);
 // Then use SQL Bulk query to insert those data
        if (dtbl.Rows.Count > 0)
{
 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection))
        {
            bulkCopy.ColumnMappings.Add("ColumnName", "ColumnName");
            bulkCopy.ColumnMappings.Add("ColumnName", "ColumnName");
        bulkCopy.DestinationTableName = "DBTableName";
        bulkCopy.WriteToServer(dtblNew);
    }
}

private void Synchronize()
{           
    SqlConnection con = new SqlConnection("Database=DesktopNotifier;Server=192.168.1.100''sql2008;User=common;Password=k25#ap;");
    con.Open();
    SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM CustomerData", con);
    DataSet ds = new DataSet();
    adap.Fill(ds, "CustomerData");
    DataTable dt = new DataTable();
    dt = ds.Tables["CustomerData"];
    foreach (DataRow dr in dt.Rows)
    {                
        string File = dr["CustomerFile"].ToString();
        string desc = dr["Description"].ToString();
        string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=D:''DesktopNotifier''DesktopNotifier.accdb";
        OleDbConnection conn = new OleDbConnection(conString);
        conn.Open();
        string dbcommand = "insert into CustomerData (CustomerFile, Description) VALUES ('" + File + "', '" + desc + "')";
        OleDbCommand mscmd = new OleDbCommand(dbcommand, conn);
        mscmd.ExecuteNonQuery();                 
    }
}
private void Configuration_Load(object sender, EventArgs e)
{
    LoadGridData();
    LoadSettings();                     
}

只是我的两分……

使用如下代码:

DataSet ds = new DataSet();
adap.Fill(ds, "CustomerData");

您应该知道数据适配器填充方法将读取所有数据到内存中。所以如果你有无数行。三思而后行。

相关文章：