#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
#region MySql批量提交
/// <summary>
/// MySql批量提交
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="TbName">表名</param>
/// <param name="PrintKey">如果主键不是自增长ID，则可以输入空字符串</param>
/// <param name="lstData">需要插入表格内容</param>
/// <param name="IsAutoPrintKey">主键是否自增长</param>
/// <returns></returns>
public int SqlBulkToMySQl<T>(string TbName, string PrintKey, List<T> lstData, bool IsAutoPrintKey = true)
{
int RtnExe = 0;
string Sql = string.Empty;
try
{
if (lstData.Count < 1) return RtnExe;
List<T> lstDtSel = new List<T>();
foreach (var item in lstData)
{
lstDtSel.Add(item);
//不能超过一千条，设置1000以内的数值
if (lstDtSel.Count > 800)
{
//Sql = MySqlEceSql<T>(TbName, PrintKey, lstData,IsAutoPrintKey);
Sql = MySqlEceSql<T>(TbName, PrintKey, lstDtSel);
if (Sql.Length > 0)
{
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List<T>();
Sql = string.Empty;
}
}
}
if (lstDtSel.Count > 0)
{
Sql = MySqlEceSql<T>(TbName, PrintKey, lstDtSel);
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List<T>();
Sql = string.Empty;
}
}
catch (Exception)
{
throw;
}
return RtnExe;
}
#endregion
#region SqlServer 批量提交
/// <summary>
/// Sql批量提交
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="TbName">表名</param>
/// <param name="PrintKey">如果主键不是自增长ID，则可以输入空字符串</param>
/// <param name="lstData">需要插入表格内容</param>
/// <param name="IsAutoPrintKey">主键是否自增长</param>
/// <returns></returns>
public int SqlBulkToSQlSERVER_MAIN<T>(string TbName, string PrintKey, List<T> lstData, bool IsAutoPrintKey = true)
{
int Rtn = 0;
try
{
if (lstData.Count > 300)
{
SqlBulkToSQl<T>(TbName, lstData);
Rtn = lstData.Count();
}
else
{
Rtn= SqlBulkToSQlSERVER<T>(TbName, PrintKey, lstData, IsAutoPrintKey);
}
}
catch (Exception)
{
throw;
}
return Rtn;
}
#region 方法1 ,此方法会造成大量的unused占用内存,但是执行效率高
/// <summary>
/// 注意：DataTable中的列需要与数据库表中的列完全一致。,只支持sql servert
/// 已自测可用。
/// </summary>
/// <param name="conStr">数据库连接串</param>
/// <param name="strTableName">数据库中对应的表名</param>
/// <param name="dtData">数据集</param>
//public void SqlBulkCopyInsert(string conStr, string strTableName, DataTable dtData)
public void SqlBulkToSQl<T>(string strTableName, List<T> lstDt)
{
try
{
List<T> lstDtSel = new List<T>();
foreach (var item in lstDt)
{
lstDtSel.Add(item);
//不能超过一千条，设置1000以内的数值
if (lstDtSel.Count > 800)
{
DataTable dtList = ToDataTable<T>(lstDtSel,null);
SqlBulkCopyInsert(strTableName, dtList);
lstDtSel = new List<T>();
}
}
if (lstDtSel.Count > 0)
{
DataTable dtList = ToDataTable<T>(lstDtSel, null);
SqlBulkCopyInsert(strTableName, dtList);
lstDtSel = new List<T>();
}
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 注意：DataTable中的列需要与数据库表中的列完全一致。
/// 已自测可用。
/// </summary>
/// <param name="conStr">数据库连接串</param>
/// <param name="strTableName">数据库中对应的表名</param>
/// <param name="dtData">数据集</param>
//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高
/// <summary>
/// MySql批量提交
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="TbName">表名</param>
/// <param name="PrintKey">如果主键不是自增长ID，则可以输入空字符串</param>
/// <param name="lstData">需要插入表格内容</param>
/// <param name="IsAutoPrintKey">主键是否自增长</param>
/// <returns></returns>
public int SqlBulkToSQlSERVER<T>(string TbName, string PrintKey, List<T> lstData, bool IsAutoPrintKey = true)
{
int RtnExe = 0;
string Sql = string.Empty;
try
{
if (lstData.Count < 1) return RtnExe;
List<T> lstDtSel = new List<T>();
foreach (var item in lstData)
{
lstDtSel.Add(item);
//不能超过一千条，设置1000以内的数值
if (lstDtSel.Count > 800)
{
//Sql = MySqlEceSql<T>(TbName, PrintKey, lstData, IsAutoPrintKey);
Sql = SqlEceSql<T>(TbName, PrintKey, lstDtSel);
if (Sql.Length > 0)
{
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List<T>();
Sql = string.Empty;
}
}
}
if (lstDtSel.Count > 0)
{
Sql = SqlEceSql<T>(TbName, PrintKey, lstDtSel);
int Add = _db.Execute(Sql);
RtnExe = RtnExe + Add;
lstDtSel = new List<T>();
Sql = string.Empty;
}
}
catch (Exception)
{
throw;
}
return RtnExe;
}
#endregion
#endregion
#region 共通
#region 生成Sql执行语句
#region 根据属性生成Mysql语句
/// <summary>
/// 根据属性生成Mysql语句
/// </summary>
/// <typeparam name="S">源对象类型</typeparam>
/// <typeparam name="T">目标对象类型</typeparam>
/// <param name="s">源对象</param>
/// <param name="t">目标对</param>
public static string MySqlEceSql<T>(string TbName, string PrintKey, List<T> 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<PropertyInfo> lst = new List<PropertyInfo>();
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语句
/// <summary>
/// 根据属性生成sql语句
/// </summary>
/// <typeparam name="S">源对象类型</typeparam>
/// <typeparam name="T">目标对象类型</typeparam>
/// <param name="s">源对象</param>
/// <param name="t">目标对</param>
public static string SqlEceSql<T>(string TbName, string PrintKey, List<T> 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<PropertyInfo> lst = new List<PropertyInfo>();
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;
}
/// <summary>
/// 时间类型状态
/// </summary>
/// <param name="objVal"></param>
/// <param name="format"></param>
/// <returns></returns>
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");
}
}
}
/// <summary>
/// 将当前值对象转换为DateTime类型
/// </summary>
/// <param name="objVal"></param>
/// <returns></returns>
public static DateTime GetValDateTime(object objVal)
{
DateTime result = isNotNullVal(objVal) && DateTime.TryParse(objVal.ToString(), out result) ? result : new DateTime(1900, 1, 1);
return result;
}
/// <summary>
/// 判断当前值是否为空，不为空true 反false
/// </summary>
/// <param name="objVal"></param>
/// <returns>不为空true 反false</returns>
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
/// <summary>
/// 获取类型的属性
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static PropertyInfo[] GetPropertyInfos(Type type)
{
return type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
}
#endregion
#region List<T>转Table
/// <summary>
/// 将泛型集合类转换成DataTable
/// </summary>
/// <typeparam name="T">集合项类型</typeparam>
/// <param name="list">集合</param>
/// <param name="propertyName">需要返回的列的列名</param>
/// <returns>数据集(表)</returns>
public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
{
List<string> propertyNameList = new List<string>();
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
