将数据批量导入到数据库

1、sql类

（1）、

public class SqlFactory
    {
        /// <summary>
        /// 数据库连接配置文件
        /// </summary>
        private static string dbfile;

        public static string Dbfile
        {
            get { return SqlFactory.dbfile; }
            set { SqlFactory.dbfile = value; }
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns>Connection实例</returns>
        public static IDbConnection CreateConnection()
        {
            try
            {
                IDbConnection con = null;
                //加载并读取数据库连接配置文件
                XmlOperate xmlop = new XmlOperate();
                DataTable dt = xmlop.XmlToTable(Dbfile, "Config");
                string dbType = dt.Rows[0]["DbType"].ToString();
                string connectionString = dt.Rows[0]["ConnectionString"].ToString();
                switch (dbType.ToLower())
                {
                    case "access":   //access连接方式
                        con = new OleDbConnection(connectionString);
                        break;
                    case "sqlserver":        //SQL Server连接方式
                        con = new SqlConnection(connectionString);
                        break;
                    case "oracle":    //Oracle连接方式
                        con = new OracleConnection(connectionString);
                        break;
                }
               
                return con;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

}

（2）、/// <summary>
    /// 数据操作类
    /// </summary>
    public class DBHelper
    {

　　public DBHelper()
        {
            SqlFactory.Dbfile = AppDomain.CurrentDomain.BaseDirectory + "dbconfig.xml";
            this.con = SqlFactory.CreateConnection();
            this.adpt = SqlFactory.CreateDataAdapter();
        }

　　private static DBHelper _instance;
        /// <summary>
        /// AccessHelper单例
        /// </summary>
        public static DBHelper Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new DBHelper();
                return _instance;
            }
        }

　　/// <summary>
        /// 返回用于批量导入数据的SqlBulkCopy实例
        /// </summary>
        /// <returns></returns>
        public SqlBulkCopy GetSqlBulkCopy()
        {
            try
            {
                SqlFactory.Dbfile = AppDomain.CurrentDomain.BaseDirectory + "dbconfig.xml";
                IDbConnection newConn = SqlFactory.CreateConnection();
                SqlBulkCopy sbc=new SqlBulkCopy(newConn.ConnectionString);
                //每批次提交5000行数据
                sbc.BatchSize = 5000;
                //延长超时时间
                sbc.BulkCopyTimeout = 300;
                return sbc;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

}

2、界面代码：

#region 将数据批量导入到数据库
                //清空临时表数据
                string delete_sql = string.Format("DELETE FROM Temp_TMP WHERE Curflag='{0}'", _curflag);
                DBHelper.Instance.Modify(delete_sql);//执行SQL语句

                //批量导入数据到验货明细临时表
                SqlBulkCopy sbCopy = DBHelper.Instance.GetSqlBulkCopy();
                //目的数据库表名
                sbCopy.DestinationTableName = "Temp_TMP";
                //数据表字段与数据库表字段映射
                sbCopy.ColumnMappings.Add("条码", "Barcode");
                sbCopy.ColumnMappings.Add("出库数量", "OutStorageAmount");
                sbCopy.ColumnMappings.Add("出库价格", "OutStoragePice");
                sbCopy.ColumnMappings.Add("货位编号", "LocationNumber");
                sbCopy.ColumnMappings.Add("操作人", "CreateUser");
                sbCopy.ColumnMappings.Add("操作时间", "CreateTime");
                sbCopy.ColumnMappings.Add("备注", "Remark");
                sbCopy.ColumnMappings.Add("curflag", "Curflag");
                //导入
                sbCopy.WriteToServer(newDt);
                sbCopy.Close();
#endregion