批量更新sql语句javabatchupdate_批量更新数据（BatchUpdate）
批量更新数据(BatchUpdate)/// ///批量更新数据，注意：如果有timestamp列，要移除/// /// 源数据 /// ⽬标table /// 主键，根据主键来更新数据，不是⾃增长 /// ⾃增长列 /// 列名 /// 条件 /// 批量更新的数量 /// public Boolean BatchUpdate(DataTable sourceTable, string targetTableName, string primaryKeyName, string identity, string[] columnsName, string limitWhere, int batchNum) { if (string.IsNullOrEmpty(targetTableName)) return false; if (string.IsNullOrEmpty(primaryKeyName)) return false; if (columnsName == null || columnsName.Length<= 0) return false;DataSet ds = new DataSet();
ds.Tables.Add(sourceTable);
ds.Tables[0].TableName = targetTableName; using (SqlConnection conn = newSqlConnection(strConn))
{
conn.Open();using (SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))//使⽤加强读写锁事务，避免脏读
{ try{foreach (DataRow row in ds.Tables[0].Rows)
{
row.AcceptChanges();
row.SetModified();//所有⾏设置为可修改
} string cols = string.Join(",", columnsName);
cols= "[" + cols.Replace(",", "],[") + "]";
SqlCommand cmd= new SqlCommand(string.Format("SELECT {2} FROM {0} WHERE {1}", targetTableName, limitWhere, cols), conn);
cmd.Transaction=transaction;
SqlDataAdapter adapter= newSqlDataAdapter(cmd);
SqlCommandBuilder sqlCmdBuilder= newSqlCommandBuilder(adapter);
adapter.AcceptChangesDuringFill= false;
adapter.Fill(ds);string updateSQL = string.Empty;
SqlParameter[] paras= new SqlParameter[columnsName.Length]; for (int i = 0; i < columnsName.Length; i++)
{//去除⾃增长ID ，根据可控的⾃增Id进⾏更新 if (columnsName[i] != primaryKeyName && columnsName[i] != identity)
{
updateSQL+= ("[" + columnsName[i] + "]" + "=@" + columnsName[i] + ",");
}if (sourceTable.Columns[i].DataType.Name == "Int32")
{
paras[i]= new SqlParameter("@" + columnsName[i], SqlDbType.Int, 10, columnsName[i]);
}else if (sourceTable.Columns[i].DataType.Name == "DateTime")
{
paras[i]= new SqlParameter("@" + columnsName[i], SqlDbType.DateTime, 23, columnsName[i]);
}else if (sourceTable.Columns[i].DataType.Name == "Double")
{
paras[i]= new SqlParameter("@" + columnsName[i], SqlDbType.Decimal, 23, columnsName[i]);
}else{
paras[i]= new SqlParameter("@" + columnsName[i], SqlDbType.NVarChar, -1, columnsName[i]);//-1表⽰⼤于4000的长度
}
}if (!string.IsNullOrEmpty(updateSQL))
{
updateSQL= updateSQL.Trim(',');
}string limitSql = ("[" + primaryKeyName + "]" + "=@" +primaryKeyName);
SqlCommand updateCmd= new SqlCommand(string.Format("UPDATE {0} SET {1} WHERE {2}", targetTableName, updateSQL, limitSql)); //设置不修改源Table
updateCmd.UpdatedRowSource =UpdateRowSource.None;
adapter.UpdateCommand=updateCmd;
adapter.UpdateCommand.Parameters.AddRange(paras);
updateCmd.Transaction=transaction;
adapter.UpdateCommand.CommandTimeout= 36000;//超时时间
adapter.UpdateBatchSize =batchNum;
adapter.Update(ds, targetTableName);
ds.AcceptChanges();
transaction.Commit();return true;
}catch(Exception ex)
{
transaction.Rollback();return false; throwex;
}finally{
conn.Close();
conn.Dispose();
}
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。