BaseSqlMapDaoEx
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using IBatisNet.Common.Exceptions;
using IBatisNet.Common.Pagination;
using IBatisNet.DataAccess;
using IBatisNet.DataAccess.DaoSessionHandlers;
using IBatisNet.DataAccess.Interfaces;
using IBatisNet.DataMapper;
using PengeSoft.db;
using PengeSoft.db.IBatis;
using PengeSoft.Logging;
namespace DotNet.Common.IBatisUtil
{
/// <summary>
/// BaseSqlMapDao 实现的基类。
/// </summary>
public class BaseSqlMapDaoEx : BaseSqlMapDao
{
protected static readonly ILog _logger = LogManager.GetLogger(typeof(BaseSqlMapDaoEx));
/// <summary>
/// 批量插入(这个方法外部重写)
/// </summary>
/// <typeparam name="M"></typeparam>
/// <param name="listModels"></param>
/// <returns></returns>
public virtual bool BatchInsert<M>(IList<M> listModels,string connStr, string tbName) where M : class
{
bool flag = false;
try
{
DataTable dt = DataTableHelper.CreateTable<M>(listModels);
flag = ExecuteInsertCommand(connStr, tbName, dt);
}
catch
{
flag = false;
}
return flag;
}
/// <summary>
/// 执行插入命令
/// </summary>
/// <param name="connStr">sql连接字符串</param>
/// <param name="tableName">表名称</param>
/// <param name="dt">组装好的要批量导入的datatable</param>
/// <returns></returns>
protected virtual bool ExecuteInsertCommand(string connStr, string tableName, DataTable dt)
{
bool flag = false;
//SqlTransaction transaction = null;
//ISqlMapSession sesseion = this.SqlMapper.CreateSqlMapSession();
try
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
{
//sesseion.BeginTransaction();
//transaction = conn.BeginTransaction();
//服务器上目标表的名称
sbc.DestinationTableName = tableName;
sbc.BatchSize = 50000;
sbc.BulkCopyTimeout = 180;
for (int i = 0; i < dt.Columns.Count; i++)
{
//列映射定义数据源中的列和目标表中的列之间的关系
sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sbc.WriteToServer(dt);
flag = true;
//throw new Exception("Test...");
//transaction.Commit();//无效事务
//sesseion.Complete(); //无效事务
scope.Complete();//有效的事务
}
}
}
}
catch (Exception ex)
{
_logger.Error(string.Format("ExecuteInsertCommand_Error1：{0}", ex.StackTrace));
_logger.Error(string.Format("ExecuteInsertCommand_Error2：{0}", ex.Message));
//if (transaction != null)
//{
// transaction.Rollback();
//}
//if (sesseion != null)
//{
// sesseion.RollBackTransaction();
//}
//flag = false;
//string errMsg = ex.Message;
throw ex;
}
return flag;
}
}
}
View Code
DataTableHelper
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
namespace DotNet.Common.IBatisUtil
{
public class DataTableHelper
{
private static IList<string> CreateModelProperty<T>(T obj) where T : class
{
IList<string> listColumns = new List<string>();
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;
Type objType = typeof(T);
PropertyInfo[] propInfoArr = objType.GetProperties(bf);
foreach (PropertyInfo item in propInfoArr)
{
object[] objAttrs = item.GetCustomAttributes(typeof(TableColumnAttribute), true);
if (objAttrs != null && objAttrs.Length > 0)//取出实体对应表的实际列名
{
listColumns.Add(item.Name);
}
}
return listColumns;
}
private static DataTable CreateTable(IList<string> listColumns)
{
DataTable dt = new DataTable();
for (int i = 0; i < listColumns.Count; i++)
{
dt.Columns.Add(new DataColumn(listColumns[i]));
}
return dt;
}
public static DataTable CreateTable<T>(IList<T> listModels) where T : class
{
T model = default(T);
IList<string> listProperties = CreateModelProperty<T>(model);
DataTable dataTable = CreateTable(listProperties);
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;
Type objType = typeof(T);
PropertyInfo[] propInfoArr = objType.GetProperties(bf);
foreach (T itemModel in listModels)
{
DataRow dataRow = dataTable.NewRow();
foreach (PropertyInfo item in propInfoArr)
{
string propName = item.Name;
if (listProperties.Contains(propName))
{
var value = item.GetValue(itemModel, null);
dataRow[propName] = value;
}
}
dataTable.Rows.Add(dataRow);
}
return dataTable;
}
}
}
View Code
TableColumnAttribute
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DotNet.Common.IBatisUtil
{
/// <summary>
/// 标志是Person对象对应的真实表的列
/// </summary>
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Class, Inherited = true)]
public class TableColumnAttribute : Attribute
{
public string Description { get; set; }
}
}
View Code
注意：
1、时间插入时可能会报错： sbc.WriteToServer “SqlDateTime 溢出。必须介于 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间。”
一至没找到真正原因，直接不插入此列
一至没找到真正原因，直接不插入此列
