iBatis.net扩展批量插入

 

 

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;
        }

    }
}

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;
        }
    }
}

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; }
    }
}

 

注意：

1、时间插入时可能会报错：  sbc.WriteToServer    “SqlDateTime 溢出。必须介于 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间。”

一至没找到真正原因，直接不插入此列 

 

一至没找到真正原因，直接不插入此列