【.NET】使用SqlBulkCopy导入数据

.net2.0后ado.net提供了一个快速导入sqlserver的方法sqlbulkcopy.导入效率非常高.

包装了一个简单的sqlbulkcopy类，用于数据从datatable导入到sqlserver.代码如下：

using System; 
using System.Collections.Generic; 
using System.Data; 
using System.Data.SqlClient; 
using System.Text; 

namespace taihe.framework.tools.import 
{ 
    public class SqlBulkCopy 
    { 
        private int timeout = 1800; 
        private System.Data.SqlClient.SqlBulkCopy sqlBulkCopy = null; 

        public SqlBulkCopy() 
        { 
        } 

        public SqlBulkCopy(int timeout) 
        { 
            this.timeout = timeout; 
        } 

        public void WriteToDataBase(DataTable source, string tableName,bool useTransaction,string dataBaseConnString,bool clearTable) 
        { 
            //判断表是否存在 
            taihe.framework.data.datalayer.SqlDataHelper dataHelper = new data.datalayer.SqlDataHelper(dataBaseConnString); 
            dataHelper.IsConnString = true; //使用数据库连接字符串创建sqlserver操作对象 
            string sql = "select * from sys.objects where type='U' and name='" + tableName + "'"; 
            DataTable dt = dataHelper.ExecuteDataSet(sql).Tables[0]; 
            if (dt.Rows.Count > 0) 
            { 
                if (clearTable == true) 
                { 
                    sql = "drop table " + tableName + "";   //清除已存在的表 
                } 
                else 
                { 
                    throw new Exception("要创建的表已存在"); 
                } 
            } 
            this.CreateTable(dataHelper, source.Columns, tableName); 
            sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(dataBaseConnString); 
            if (useTransaction == true) 
            { 
                sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(dataBaseConnString, SqlBulkCopyOptions.UseInternalTransaction); //导入的数据在一个事务中 
            } 
            sqlBulkCopy.DestinationTableName = tableName; 
            foreach (DataColumn c in source.Columns) 
            { 
                sqlBulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName); 
            } 
            sqlBulkCopy.BulkCopyTimeout = this.timeout;  //超时时间 
            sqlBulkCopy.BatchSize = 3000;  //每次传输3000行 
            sqlBulkCopy.WriteToServer(source); 
        } 

        public void CreateTable(taihe.framework.data.datalayer.SqlDataHelper dataHelper, System.Data.DataColumnCollection columns,string tableName) 
        { 
            StringBuilder sb = new StringBuilder(); 
            sb.Append("create table [" + tableName + "] (autoId  int identity(1,1),"); 
            foreach (DataColumn column in columns) 
            { 
                sb.Append(" [" + column.ColumnName + "] " + this.GetTableColumnType(column.DataType) + ","); 
            } 
            string sql = sb.ToString(); 
            sql = sql.TrimEnd(','); 
            sql += ")"; 
            dataHelper.ExecuteNonQuery(sql); 
        } 

        private string GetTableColumnType(System.Type type) 
        { 
            string result = "varchar(255)"; 
            string sDbType = type.ToString(); 
            switch (sDbType) 
            { 
                case "System.String": 
                    break; 
                case "System.Int16": 
                    result = "int"; 
                    break; 
                case "System.Int32": 
                    result = "int"; 
                    break; 
                case "System.Int64": 
                    result = "float"; 
                    break; 
                case "System.Decimal": 
                    result="decimal(18,4)"; 
                    break; 
                case "System.Double": 
                    result = "decimal(18,4)"; 
                    break; 
                case "System.DateTime": 
                    result = "datetime"; 
                    break; 
                default: 
                    break; 
            } 
            return result; 
        } 
    } 
}