Oracle批量插入与修改，结合Sqlsugar
批量插入大概两万条数据，sqlsugar 的 .ExecuteCommandAsync()执行速度大概再80秒甚至更久，完全无法满足要求，因为架构是winfrom 无法使用sqlsugar自带的 BulkCopy，又因为Oracle dll版本低 无法使用Oracle自带的BulkCopy，更换版本需要申请，再网上看到了一个大佬的方法，满足了需求，在此记录一下 https://www.pudn.com/news/62d92bea864d5c73acdb025c.html
这是一个通用的批量插入与修改，我修改了一下代码，如有问题，请指出：
/// <summary>
/// 批量插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">数据实体类</param>
/// <returns></returns>
public static int BulkInsert<T>(List<T> list)
{
try
{
Type model = typeof(T);
List<PropertyInfo> pi = GetMappedField<T>();
#region 生成数据源与参数
OracleParameter[] paras = new OracleParameter[pi.Count];//用于存放数据
string[] propertys = new string[pi.Count];
string[] paras_propertys = new string[pi.Count];
for (int i = 0; i < pi.Count; i++)
{
PropertyInfo p = pi[i];
//获取数据库对应字段名
SugarColumn[] sugarColumns = (SugarColumn[])p.GetCustomAttributes(typeof(SugarColumn), false);
string name = sugarColumns[0].ColumnName;
propertys[i] = name;
paras_propertys[i] = $":{name}";
Type pt = p.PropertyType;
//字段是否可可为空
bool IsNullable = pt.Name == "Nullable`1";
var colArr = new List<object>();
foreach (var item in list)
{
object val = p.GetValue(item, null);
colArr.Add((val == null && !IsNullable) ? DBNull.Value : val);
}
//获取类型
OracleDbType dt = OracleDbType.Varchar2;
GetOracleDbType(IsNullable, p, pt, out dt);
paras[i] = new OracleParameter($":{name}", dt) { Value = colArr.ToArray() };
}
#endregion 生成数据源与参数
#region 获取表名
string tableName = model.Name;
//sqlsugar架构的表属性名
SugarTable[] tableDesc = (SugarTable[])model.GetCustomAttributes(typeof(SugarTable), false);
tableName = tableDesc.First().TableName;
#endregion 获取表名
string sql = $"INSERT INTO {tableName}({string.Join(",", propertys)}) VALUES({string.Join(",", paras_propertys)})";
int result = ExecuteNonQuery(list.Count, sql, paras);
return result;
}
catch (Exception err)
{
throw err;
}
}
/// <summary>
/// 获取映射的字段
/// </summary>
/// <returns></returns>
public static List<PropertyInfo> GetMappedField<T>()
{
try
{
Type model = typeof(T);
List<PropertyInfo> proTemp = model.GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public).ToList();
#region 过滤出与表对应的字段
List<PropertyInfo> pi = new List<PropertyInfo>();
for (int i = 0; i < proTemp.Count; i++)
{
PropertyInfo p = proTemp[i];
string name = p.Name;
Type pt = p.PropertyType;
//是否泛型 如：List<User> Int? 等
//bool t1 = pt.IsGenericType;
//Console.WriteLine($"序号：{i + 1}；字段{ p.Name}；类型{ pt.Name }；IsGenericType：{pt.IsGenericType}");
if (pt.BaseType == null)
{
continue;
}
//被标记不与数据库映射的字段SugarColumn
SugarColumn[] caArray = (SugarColumn[])p.GetCustomAttributes(typeof(SqlSugar.SugarColumn), true);
if (caArray.Any(a => a.IsIgnore == true))
{
continue;
}
Object[] caArray2 = p.GetCustomAttributes(typeof(NotMappedAttribute), true);
if (caArray2.Length > 0)
{
continue;
}
//基本类型
if (p.PropertyType.Namespace == "System")
{
pi.Add(p);
}
else if (p.PropertyType.IsEnum || pt.BaseType.Name == "Enum" || p.PropertyType.BaseType.Name == "Enum")
{
pi.Add(p);
}
else
{
//不与数据库映射的字段
continue;
}
}
#endregion 过滤出与表对应的字段
return pi;
}
catch (Exception err)
{
throw err;
}
}
/// <summary>
/// 获取数据基本类型
/// </summary>
/// <param name="IsNullable">是否为空</param>
/// <param name="p"></param>
/// <param name="pt"></param>
/// <param name="dt"></param>
public static void GetOracleDbType(bool IsNullable, PropertyInfo p, Type pt, out OracleDbType dt)
{
dt = OracleDbType.Varchar2;
try
{
if (p.PropertyType.Namespace == "System")
{
if (IsNullable)
{
//可为空时找真实基本类型
pt = pt.GetGenericArguments()[0];
}
switch (pt.Name)
{
case "String":
dt = OracleDbType.Varchar2;
break;
case "Short":
case "Int":
case "Int16":
dt = OracleDbType.Int16;
break;
case "Int32":
dt = OracleDbType.Int32;
break;
case "Decimal":
dt = OracleDbType.Decimal;
break;
case "Long":
case "Int64":
case "Double":
dt = OracleDbType.Long;
break;
case "DateTime":
dt = OracleDbType.Date;
break;
default:
break;
}
}
else if (p.PropertyType.IsEnum || pt.BaseType.Name == "Enum" || p.PropertyType.BaseType.Name == "Enum")
{
//枚举单独处理
dt = OracleDbType.Int32;
}
}
catch (Exception err)
{
throw err;
}
}
/// <summary>
/// 数据库执行
/// </summary>
/// <param name="count">数据行数</param>
/// <param name="sql">执行sql</param>
/// <param name="paras">执行参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(int count, string sql, OracleParameter[] paras)
{
int result = -1;
try
{
#region 数据处理
using (var command = Connection.CreateCommand())
{
command.ArrayBindCount = count;
command.FetchSize = 1000;
command.CommandText = sql.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(paras);
command.BindByName = true;
result = command.ExecuteNonQuery();
}
#endregion 数据处理
}
catch (Exception err)
{
throw err;
}
return result;
}
/// <summary>
/// 批量修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">数据实体类</param>
/// <returns></returns>
public static int BulkUpdate<T>(List<T> list)
{
try
{
Type model = typeof(T);
List<PropertyInfo> pi = GetMappedField<T>();
#region 生成数据源与参数
OracleParameter[] paras = new OracleParameter[pi.Count];
var updateSet = new List<string>();
var updateWhere = new List<string>();
for (int i = 0; i < pi.Count; i++)
{
string set = "";
PropertyInfo p = pi[i];
//获取数据库对应字段名
SugarColumn[] sugarColumns = (SugarColumn[])p.GetCustomAttributes(typeof(SugarColumn), false);
string name = sugarColumns[0].ColumnName;
//是否为主键 批量修改默认根据主键修改数据
if (sugarColumns[0].IsPrimaryKey)
{
updateWhere.Add($"{name}=:{name}");
}
else
{
set = $"{name}=:{name}";
}
Type pt = p.PropertyType;
//字段是否可可为空
bool IsNullable = pt.Name == "Nullable`1";
var colArr = new List<object>();
foreach (var item in list)
{
object val = p.GetValue(item, null);
colArr.Add((val == null && !IsNullable) ? DBNull.Value : val);
}
//获取类型
OracleDbType dt = OracleDbType.Varchar2;
GetOracleDbType(IsNullable, p, pt, out dt);
if (!string.IsNullOrWhiteSpace(set))
{
updateSet.Add(set);
}
paras[i] = new OracleParameter($":{name}", dt) { Value = colArr.ToArray() };
}
#endregion 生成数据源与参数
#region 获取表名
string tableName = model.Name;
//sqlsugar架构的表属性名
SugarTable[] tableDesc = (SugarTable[])model.GetCustomAttributes(typeof(SugarTable), false);
tableName = tableDesc.First().TableName;
#endregion 获取表名
string sql = $"UPDATE {tableName} SET {string.Join(",", updateSet)} WHERE {string.Join(" AND ", updateWhere)}";
int result = ExecuteNonQuery(list.Count, sql, paras);
return result;
}
catch (Exception err)
{
throw err;
}
}
测试之后只需要两秒就可以完成两万条数据的修改，非常nice