快速文字資料匯入資料庫，速度和效能大幅度提升

公司的通訊計費管理系統有個重要的功能：文本資料匯入資料庫，文本資料的格式是：

分析出的主叫號碼,分析出的被叫號碼,開始時間(字串),結束時間(字串),通話時間長度,入中繼,出中繼,業務類別,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方法實現了資料的快速匯入，對於這兩個方法不懂的可以百度、Google下，效能提升千倍（有點誇張哦），資料匯入大幅度耗時減少，實現代碼如下：

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