数据库批量插入

最新推荐文章于 2023-03-22 15:51:32 发布

人工博客

最新推荐文章于 2023-03-22 15:51:32 发布

阅读量518

点赞数

分类专栏：数据库文章标签：批量插入

关注人工博客 https://www.gzcx.net/，了解一线java技术专家的日常问题排查

本文链接：https://blog.csdn.net/cx308679291/article/details/41447575

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

/// <param name="sqlStr"></param>
/// <param name="param"></param>
public static void InsertBach(string tableName,string sqlStr,params OleDbParameter[] param)
{
string sql = string.Format("insert into {0} {1}", tableName, sqlStr);
try
{
OleDbDB.ExeSql(sql, param);
}
catch
{

}
}

string sqlStr = "";
List<OleDbParameter> paramList = new List<OleDbParameter>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (sqlStr.Length > 3000)
{
xs_czxt.ashx.Common.InsertBach(admin.master.Main.JG_Table2("xspjmdb", "1"), sqlStr, paramList.ToArray());
sqlStr = "";
paramList.Clear();
}
sqlStr += (sqlStr == "" ? "" : " union all ") + " select ? bjmc,? xykh,'*' pjlx,? xb,'1' sfbc,'' xjkh from dual ";
paramList.Add(new OleDbParameter("bjmc", dt.Rows[i]["bjmc"].ToString()));
paramList.Add(new OleDbParameter("xykh", arr[i]));
paramList.Add(new OleDbParameter("xb", dt.Rows[i]["xb"].ToString()));

}
if (sqlStr!="")
{
xs_czxt.ashx.Common.InsertBach(admin.master.Main.JG_Table2("xspjmdb", "1"), sqlStr, paramList.ToArray());
sqlStr = "";
paramList.Clear();
}

/// <summary>
/// 执行sqlserver数据库的批量插入
/// </summary>
/// <param name="connectionString"></param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
public void ExeSqlbulkcopy(string connectionString, string tableName, DataTable dt)
{
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = tableName;//数据库中的表名
sqlbulkcopy.WriteToServer(dt);

}

数字型字符串格式话
select trim(to_char(to_number('0.44'),'990.99')) id from dual