.net使用SqlBulkCopy导入数据

来自：http://wangfu-02.iteye.com/blog/1485373

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