首先通过excel获取需要的数据,我这里用的两个测试字段
//获取连接字符串
public SqlConnection con()
{
return new SqlConnection("server=localhost;uid=sa;pwd=rui110;database=AIS20230816104641");
//这里的uid=test中的test必须是System Administrtor, 否则会出错
}
pubilc void TestSqlBulkCopy()
{
string StrConnMsg = @"server=localhost;uid=sa;pwd=rui110;database=AIS20230816104641";
string sqls = "select * from T_DataTemp";
DataTable dts = new DataTable();
// 定义 Excel 连接字符串
string connectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\\Users\\Lenovo\\Desktop\\ERP总重统计.xlsx;Extended Properties=\"Excel 12.0\";";
// 创建 OleDbConnection 对象
OleDbConnection conn = new OleDbConnection(connectionString);
// 打开连接
conn.Open();
// 查询数据
string sql = "SELECT 编码,重量 FROM [型号$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
DataTable dt = new DataTable();
adapter.Fill(dt);
// 关闭连接
conn.Close();
//获取数据
using (SqlConnection conns = new SqlConnection(StrConnMsg))
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(new SqlCommand(sqls, conns));
dts = new DataTable();
sqlDataAdapter.Fill(dts);
}
//批量进行插入
using (SqlConnection conns = new SqlConnection(StrConnMsg))
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(conns);
bulkCopy.DestinationTableName = "T_DataTemp";
bulkCopy.BatchSize = dts.Rows.Count;
conns.Open();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dts.NewRow();
dr[0] = dt.Rows[i]["编码"];
dr[1] = dt.Rows[i]["重量"];
dts.Rows.Add(dr);
}
if (dts != null && dts.Rows.Count != 0)
{
bulkCopy.WriteToServer(dts);
}
}
}