快速文本数据导入数据库，速度和性能大幅度提升

时间：2010-10-15 来源：grayboy

分析出的主叫号码,分析出的被叫号码,开始时间(字符串),结束时间(字符串),通话时长,入中继,出中继,业务类别,CTX群标识,计费标志,终止原因

61915421,075486321177,2010-04-1023:59:34.91,2010-04-1100:00:04.46,29.55,2*37,2*7,7,0,1,0

61915400,02085512676,2010-04-1023:59:38.85,2010-04-1100:00:15.56,36.71,2*37,2*7,7,0,1,0

61915403,02084471816,2010-04-1023:59:40.82,2010-04-1100:00:17.36,36.54,2*37,2*7,7,0,1,0

61915419,02039991550,2010-04-1023:59:46.90,2010-04-1100:00:21.09,34.19,2*37,2*7,7,0,1,0

61915402,02084549963,2010-04-1023:59:41.21,2010-04-1100:00:27.28,46.07,2*37,2*7,7,0,1,0

4904341,428615236728484,2010-04-1023:59:10.67,2010-04-1100:00:28.59,77.92,2*5,2*6,7,0,1,0

61915415,02089239027,2010-04-1023:59:44.05,2010-04-1100:00:34.18,50.13,2*37,2*7,7,0,1,0

刚开始做的时候，使用的方法是用StreamReader的ReadLine（）方法一行一行的读取数据，然后使用string的Split（）方法根据逗号分割数据，然后手工在内存中构建一个DataTable，之后写方法把DataTable导入到数据库，具体实现如下：

1.将文本数据生成DateTable

public DataTable GetCdrText(string strFilePathName)
        {
            StreamReader objSr = null;
            string strFileName = "";
            try
            {
                strFileName = Path.GetFileName(strFilePathName);
                objSr = new StreamReader(strFilePathName, System.Text.Encoding.Default);

                //设置Table列'
                DataTable dt = new DataTable();
                dt.TableName = strFileName;
                string strFistLine = objSr.ReadLine().ToString();
                string[] columns = strFistLine.Split(",".ToCharArray());
                for (int i = 0; i < columns.Length; i++)
                {
                    if (columns[i].Equals("分析出的主叫号码"))
                    {
                        columns[i] = "分析出的主叫号码";
                    }
                    else if (columns[i].Equals("分析出的被叫号码"))
                    {
                        columns[i] = "分析出的被叫号码";
                    }
                    else if (columns[i].Equals("开始时间(字符串)"))
                    {
                        columns[i] = "开始时间";
                    }
                    else if (columns[i].Equals("结束时间(字符串)"))
                    {
                        columns[i] = "结束时间";
                    }
                    else if (columns[i].Equals("通话时长"))
                    {
                        columns[i] = "时长";
                    }
                    else if (columns[i].Equals("业务类别"))
                    {
                        columns[i] = "话务类型";
                    }
                    else if (columns[i].Equals("开始时间(整数)"))
                    {
                        columns[i] = "开始时间整数";
                    }
                    else if (columns[i].Equals("结束时间(整数)"))
                    {
                        columns[i] = "结束时间整数";
                    }

                    dt.Columns.Add(columns[i]);
                }
                dt.Columns.Add("ServiceCategory");

                string[] srvalue = null;
                while (objSr.Peek() > -1)
                {
                    srvalue = objSr.ReadLine().ToString().Split(",".ToCharArray());

                    //dtTmp.Rows.Clear();
                    dt.Rows.Add(srvalue);

                    //将数据添加到表里
                }
                objSr.Close();
                return dt;
            }
            catch (System.Exception e)
            {
                objSr.Close();
                throw e;
            }
        }

2.SQL语句设计

 public int InsertData(DataTable dt)
        {
            string strTableName = String.Empty;
            SqlParameter[] pParamete = null;
            string pSql = String.Empty;
            //表名

            pSql = "insert into OriginalData";
            pSql += " (CallerNumber,CalleeNumber,StartTime,EndTime,Duration,InTrunk,OutTrunk,ServiceCategory,CentrexGroupId,ChargeFlag,ReleaseReason) values";
            pSql += " (@分析出的主叫号码,@分析出的被叫号码,@开始时间,@结束时间,@时长,@入中继,@出中继,@话务类型,@CTX群标识,@计费标志,@终止原因)";
            pParamete = new SqlParameter[] { 
            new SqlParameter("@分析出的主叫号码", SqlDbType.VarChar, 20, "分析出的主叫号码"), 
            new SqlParameter("@分析出的被叫号码", SqlDbType.VarChar, 35, "分析出的被叫号码"),
            new SqlParameter("@开始时间", SqlDbType.DateTime, 30, "开始时间"),
            new SqlParameter("@结束时间", SqlDbType.DateTime, 30, "结束时间"),
            new SqlParameter("@时长", SqlDbType.Decimal, 18, "时长"),
            new SqlParameter("@话务类型", SqlDbType.VarChar, 6, "ServiceCategory"),
            new SqlParameter("@入中继", SqlDbType.VarChar, 6, "入中继"),
            new SqlParameter("@出中继", SqlDbType.VarChar, 6, "出中继"),
            new SqlParameter("@CTX群标识", SqlDbType.Int, 6, "CTX群标识"),
            new SqlParameter("@计费标志", SqlDbType.Int, 6, "计费标志"),
            new SqlParameter("@终止原因", SqlDbType.Int, 6, "终止原因")};

            return ExecuteInsert(pSql, dt, pParamete);
        }

3.将整个Table的数据插入到数据库

   public int ExecuteInsert(string SQLString, DataTable dt, params SqlParameter[] cmdParms)
        {
            SqlDataAdapter objAdapter = null;
            SqlCommand objComm = null;

            try
            {
                SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"]);
                if (objConn.State == ConnectionState.Closed)
                {
                    objConn.Open();
                }
                objComm = new SqlCommand(SQLString, objConn);
                objComm.CommandTimeout = 0;
                objAdapter = new SqlDataAdapter();

                for (int i = 0; i < cmdParms.Length; i++)
                {
                    objComm.Parameters.Add(cmdParms[i]);
                }

                objAdapter.UpdateCommand = objComm;
                objAdapter.InsertCommand = objComm;
                int intRet = objAdapter.Update(dt);
                objConn.Close();
                return intRet;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

用这个方法操作的时候，对于4M左右的文本文件还是可以胜任的，但是当10多M的时候，每导入一个都需要很长的时间，而且有的时候还会出现内存不足的提醒（做开发的电脑配制垃圾，1G物理内存，1G虚拟内存）。所以，这种方法对我来说是不行的，于是就百度、google终于找到了更好的解决方案，使用SQLSERVER的OPENROWSET和BULK方法实现了数据的快速导入，对于这两个方法不懂的可以百度、谷歌下，性能提升千倍（有点夸张哦），数据导入大幅度耗时减少，实现代码如下：

1.用xml文件格式话数据文件

<?xml version="1.0"?> 
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
          <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
          <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
          <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="CallerNumber" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="2" NAME="CalleeNumber" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="3" NAME="StartTime" xsi:type="SQLDATETIME"/>
    <COLUMN SOURCE="4" NAME="EndTime"   xsi:type="SQLDATETIME"/>
          <COLUMN SOURCE="5" NAME="Duration" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="6" NAME="InTrunk" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="7" NAME="OutTrunk" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="8" NAME="ServiceCategory" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="9" NAME="CentrexGroupId" xsi:type="SQLSMALLINT"/>
    <COLUMN SOURCE="10" NAME="ChargeFlag" xsi:type="SQLSMALLINT"/>
          <COLUMN SOURCE="11" NAME="ReleaseReason" xsi:type="SQLSMALLINT"/>
  </ROW>
</BCPFORMAT>

2.拼接SQL语句

  if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    DateTime dt1 = DateTime.Now;
                    string strFileName1 = openFileDialog1.FileName;
                    string strFileName2 = AppDomain.CurrentDomain.BaseDirectory + "format.xml";
                    StringBuilder stbSql=new StringBuilder ();
                    stbSql.Append("insert into OriginalData(CallerNumber,CalleeNumber,StartTime,EndTime,Duration,ServiceCategory,");
                    stbSql.Append("InTrunk,OutTrunk,CentrexGroupId,ChargeFlag,ReleaseReason)");
                    stbSql.Append("select CallerNumber,CalleeNumber,StartTime,EndTime,CEILING(Duration),ServiceCategory,InTrunk,OutTrunk,CentrexGroupId,ChargeFlag,ReleaseReason ");
                    stbSql.AppendFormat(" from OPENROWSET(BULK '{0}',FORMATFILE='{1}',FIRSTROW=2) AS T",strFileName1,strFileName2);
                    ExecuteInsert(stbSql.ToString());//这个只是简单的执行SQL语句的方法，这里不贴了
                    DateTime dt2 = DateTime.Now;
                    TimeSpan ts = dt2 - dt1;
                    label1.Text += ts.ToString();
                }
                catch (Exception ex)
                {
                    
                    throw ex;
                }
             }

下面来对比下执行速度，我所选择的导入的文本文件的大小是10M，由于我是将数据导入到同一数据库同一表中，所以每次导入后我都会用truncate table清空数据

可以看出，TxtRead方法耗时是BULK方法的55/2大约27倍

写的不好，欢迎大家拍砖，谁有更好的方法的话一起来交流！

附源码：WinDemo