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

Posted on 2010-10-15 18:22 grayboy 阅读(4963) 评论(32) 编辑收藏举报

公司的通讯计费管理系统有个重要的功能：文本数据导入数据库，文本数据的格式是：

分析出的主叫号码,分析出的被叫号码,开始时间(字符串),结束时间(字符串),通话时长,入中继,出中继,业务类别,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;
              }
           }