#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
#region MySql批量提交
///
/// MySql批量提交
///
///
/// 表名
/// 如果主键不是自增长ID，则可以输入空字符串
/// 需要插入表格内容
/// 主键是否自增长
///
public int SqlBulkToMySQl(string TbName, string PrintKey, List lstData, bool IsAutoPrintKey = true)
{
int RtnExe = 0;
string Sql = string.Empty;
try
{
if (lstData.Count < 1) return RtnExe;
List lstDtSel = new List();
foreach (var item in lstData)
{
lstDtSel.Add(item);
//不能超过一千条，设置1000以内的数值
if (lstDtSel.Count > 800)
{
//Sql = MySqlEceSql(TbName, PrintKey, lstData,IsAutoPrintKey);
Sql = MySqlEceSql(TbName, PrintKey, lstDtSel);
if (Sql.Length > 0)
{
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List();
Sql = string.Empty;
}
}
}
if (lstDtSel.Count > 0)
{
Sql = MySqlEceSql(TbName, PrintKey, lstDtSel);
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List();
Sql = string.Empty;
}
}
catch (Exception)
{
throw;
}
return RtnExe;
}
#endregion
#region SqlServer 批量提交
///
/// Sql批量提交
///
///
/// 表名
/// 如果主键不是自增长ID，则可以输入空字符串
/// 需要插入表格内容
/// 主键是否自增长
///
public int SqlBulkToSQlSERVER_MAIN(string TbName, string PrintKey, List lstData, bool IsAutoPrintKey = true)
{
int Rtn = 0;
try
{
if (lstData.Count > 300)
{
SqlBulkToSQl(TbName, lstData);
Rtn = lstData.Count();
}
else
{
Rtn= SqlBulkToSQlSERVER(TbName, PrintKey, lstData, IsAutoPrintKey);
}
}
catch (Exception)
{
throw;
}
return Rtn;
}
#region 方法1 ,此方法会造成大量的unused占用内存,但是执行效率高
///
/// 注意：DataTable中的列需要与数据库表中的列完全一致。,只支持sql servert
/// 已自测可用。
///
/// 数据库连接串
/// 数据库中对应的表名
/// 数据集
//public void SqlBulkCopyInsert(string conStr, string strTableName, DataTable dtData)
public void SqlBulkToSQl(string strTableName, List lstDt)
{
try
{
List lstDtSel = new List();
foreach (var item in lstDt)
{
lstDtSel.Add(item);
//不能超过一千条，设置1000以内的数值
if (lstDtSel.Count > 800)
{
DataTable dtList = ToDataTable(lstDtSel,null);
SqlBulkCopyInsert(strTableName, dtList);
lstDtSel = new List();
}
}
if (lstDtSel.Count > 0)
{
DataTable dtList = ToDataTable(lstDtSel, null);
SqlBulkCopyInsert(strTableName, dtList);
lstDtSel = new List();
}
}
catch (Exception ex)
{
throw (ex);
}
}
///
/// 注意：DataTable中的列需要与数据库表中的列完全一致。
/// 已自测可用。
///
/// 数据库连接串
/// 数据库中对应的表名
/// 数据集
//public void SqlBulkCopyInsert(string conStr, string strTableName, DataTable dtData)
public void SqlBulkCopyInsert(string strTableName, DataTable dtData)
{
try
{
if (dtData == null || dtData.Rows.Count < 1)
{
return;
}
int Ctn = 4;
while (Ctn > 1)
{
try
{
string conStr = _db.ConnectionString;
// eg conStr = "Server=localhost;Database=Test;Uid=sa;Pwd=123456;pooling=true;";
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(conStr)) //引用SqlBulkCopy
{
sqlRevdBulkCopy.DestinationTableName = strTableName; //数据库中对应的表名
sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count; //有几行数据
sqlRevdBulkCopy.WriteToServer(dtData); //数据导入数据库
sqlRevdBulkCopy.Close(); //关闭连接
}
Ctn = -1;
}
catch (Exception ex)
{
Thread.Sleep(200);
Ctn--;
if (Ctn == 1)
{
throw (ex);
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
#endregion
#region 方法二,此方法优化了方法1会生成大量unused的缺陷，但是执行效率不如方法1高
///
/// MySql批量提交
///
///
/// 表名
/// 如果主键不是自增长ID，则可以输入空字符串
/// 需要插入表格内容
/// 主键是否自增长
///
public int SqlBulkToSQlSERVER(string TbName, string PrintKey, List lstData, bool IsAutoPrintKey = true)
{
int RtnExe = 0;
string Sql = string.Empty;
try
{
if (lstData.Count < 1) return RtnExe;
List lstDtSel = new List();
foreach (var item in lstData)
{
lstDtSel.Add(item);
//不能超过一千条，设置1000以内的数值
if (lstDtSel.Count > 800)
{
//Sql = MySqlEceSql(TbName, PrintKey, lstData, IsAutoPrintKey);
Sql = SqlEceSql(TbName, PrintKey, lstDtSel);
if (Sql.Length > 0)
{
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List();
Sql = string.Empty;
}
}
}
if (lstDtSel.Count > 0)
{
Sql = SqlEceSql(TbName, PrintKey, lstDtSel);
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List();
Sql = string.Empty;
}
}
catch (Exception)
{
throw;
}
return RtnExe;
}
#endregion
#endregion
#region 共通
#region 生成Sql执行语句
#region 根据属性生成Mysql语句
///
/// 根据属性生成Mysql语句
///
/// 源对象类型
/// 目标对象类型
/// 源对象
/// 目标对
public static string MySqlEceSql(string TbName, string PrintKey, List lstIqc)
{
string Sql = string.Empty;
try
{
if (lstIqc.Count < 1) return Sql;
T s = lstIqc[0];
PropertyInfo[] pps = GetPropertyInfos(s.GetType());
Sql = Sql + string.Format($" INSERT INTO `{TbName}` ( ");
//剔除主键
List lst = new List();
foreach (var item in pps)
{
if (item.Name.ToUpper().Equals(PrintKey.ToUpper()))
{
continue;
}
else
{
lst.Add(item);
}
}
foreach (var item in lst)
{
Sql = Sql + string.Format($" `{item.Name} ` ,");
}
Sql = Sql.Substring(0, Sql.Length - 1);
Sql = Sql + string.Format($" ) values ");
foreach (var item in lstIqc)
{
Sql = Sql + string.Format($" ( ");
foreach (var itemP in lst)
{
var value = item.GetType().GetProperty(itemP.Name).GetValue(item, null);
if (itemP.PropertyType.FullName.Contains("System.DateTime"))
{
value = GetValDateFarmatValue3(value, DateTimeFormat.DateTime);
}
Sql = Sql + ($" '{value}',");
}
Sql = Sql.Substring(0, Sql.Length - 1);
Sql = Sql + string.Format($" ) ,");
}
Sql = Sql.Substring(0, Sql.Length - 1);
}
catch (Exception)
{
throw;
}
return Sql;
}
#endregion
#region 根据属性生成sql语句
///
/// 根据属性生成sql语句
///
/// 源对象类型
/// 目标对象类型
/// 源对象
/// 目标对
public static string SqlEceSql(string TbName, string PrintKey, List lstIqc)
{
string Sql = string.Empty;
try
{
if (lstIqc.Count < 1) return Sql;
T s = lstIqc[0];
PropertyInfo[] pps = GetPropertyInfos(s.GetType());
Sql = Sql + string.Format($" INSERT INTO {TbName} ( ");
//剔除主键
List lst = new List();
foreach (var item in pps)
{
if (item.Name.ToUpper().Equals(PrintKey.ToUpper()))
{
continue;
}
else
{
lst.Add(item);
}
}
foreach (var item in lst)
{
Sql = Sql + string.Format($" [{item.Name}],");
}
Sql = Sql.Substring(0, Sql.Length - 1);
Sql = Sql + string.Format($" ) values ");
foreach (var item in lstIqc)
{
Sql = Sql + string.Format($" ( ");
foreach (var itemP in lst)
{
var value = item.GetType().GetProperty(itemP.Name).GetValue(item, null);
if (itemP.PropertyType.FullName.Contains("System.DateTime"))
{
value = GetValDateFarmatValue3(value, DateTimeFormat.DateTime);
}
Sql = Sql + ($" '{value}',");
}
Sql = Sql.Substring(0, Sql.Length - 1);
Sql = Sql + string.Format($" ) ,");
}
Sql = Sql.Substring(0, Sql.Length - 1);
}
catch (Exception)
{
throw;
}
return Sql;
}
///
/// 时间类型状态
///
///
///
///
public static string GetValDateFarmatValue3(object objVal, DateTimeFormat format)
{
if (objVal == null || string.IsNullOrEmpty(objVal.ToString()))
{
return "";
}
else
{
switch (format)
{
case DateTimeFormat.DateTime:
return GetValDateTime(objVal).ToString("yyyy/MM/dd HH:mm:ss");
case DateTimeFormat.Date:
return GetValDateTime(objVal).ToString("yyyy/MM/dd");
case DateTimeFormat.DateStr:
return GetValDateTime(objVal).ToString("yyyy年MM月dd日");
case DateTimeFormat.Time:
return GetValDateTime(objVal).ToString("HH:mm:ss");
case DateTimeFormat.Hours:
return GetValDateTime(objVal).ToString("HH:mm");
default:
return GetValDateTime(objVal).ToString("yyyy/MM/dd HH:mm:ss");
}
}
}
///
/// 将当前值对象转换为DateTime类型
///
///
///
public static DateTime GetValDateTime(object objVal)
{
DateTime result = isNotNullVal(objVal) && DateTime.TryParse(objVal.ToString(), out result) ? result : new DateTime(1900, 1, 1);
return result;
}
///
/// 判断当前值是否为空，不为空true 反false
///
///
/// 不为空true 反false
public static bool isNotNullVal(object objVal)
{
if (objVal != null)
{
if (objVal.GetType() == typeof(DataTable))
{
return (objVal as DataTable).Rows.Count >= 1;
}
else if (objVal.GetType() == typeof(DataSet))
{
return (objVal as DataSet) != null && (objVal as DataSet).Tables.Count >= 1;
}
else if (objVal.GetType() == typeof(DataRow))
{
return (objVal as DataRow) != null;
}
else if (objVal.GetType() == typeof(DataRow[]))
{
return (objVal as DataRow[]).Length >= 1;
}
else if (objVal.GetType() == typeof(Array))
{
return (objVal as Array).Length >= 1;
}
else if (objVal.GetType() == typeof(string[]))
{
return (objVal as string[]).Length >= 1;
}
else if (objVal.GetType() == typeof(object[]))
{
return (objVal as object[]).Length >= 1;
}
return !string.IsNullOrEmpty(objVal.ToString());
}
return false;
}
#endregion
///
/// 获取类型的属性
///
///
///
public static PropertyInfo[] GetPropertyInfos(Type type)
{
return type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
}
#endregion
#region List转Table
///
/// 将泛型集合类转换成DataTable
///
/// 集合项类型
/// 集合
/// 需要返回的列的列名
/// 数据集(表)
public static DataTable ToDataTable(IList list, params string[] propertyName)
{
List propertyNameList = new List();
if (propertyName != null)
propertyNameList.AddRange(propertyName);
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
try
{
if (propertyNameList.Count == 0)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
else
{
if (propertyNameList.Contains(pi.Name))
result.Columns.Add(pi.Name, pi.PropertyType);
}
}
catch (Exception)
{
result.Columns.Add(pi.Name, Type.GetType("System.String"));
}
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
else
{
if (propertyNameList.Contains(pi.Name))
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
}
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
#endregion
#endregion
#endregion