遇到 Too many parameters were provided in this RPC request. The maximum is 2100. 的狀況時的處理模式
前言：
因為導入資安專案後，同仁已經針對 SQL 語法改採用 Parameters.Add 的方法撰寫。
但近來 HR 同仁因為有大量資料寫入的需求，遇到 Parameters 參數使用超過 2100 個的上限的例子 (如下圖)
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.
以下紀錄兩個方法，處理這問題：
- Parameters.Add 批次寫入。
- SqlBulkCopy 大量資料寫入。
實作(一) Parameters.Add 批次寫入：
一樣使用 Parameters.Add，但會依 Parameters.Count 批次寫入資料 (效率差)
protected void Button1_Click(object sender, EventArgs e)
{
//==================================================
//
// 1. 加入參考：System.Transactions
// 2. using System.Transactions;
//
//==================================================
//引用stopwatch物件 (計算執行時間)
Stopwatch sw = new Stopwatch();
sw.Start();
//創造 DataTable，準備來接資料
DataTable dt = new DataTable();
dt.Columns.Add("EMPNO_dt", typeof(int));
dt.Columns.Add("LOGIN_TM_dt", typeof(string));
dt.Columns.Add("LOGIN_IP_dt", typeof(string));
dt.Columns.Add("LOGIN_ERR_dt", typeof(string));
//一口氣做10萬筆資料，來測試
for (int i = 0; i < 100000; i++)
{
DataRow dr = dt.NewRow();
dr["EMPNO_dt"] = i.ToString();
dr["LOGIN_TM_dt"] = "A" + i.ToString();
dr["LOGIN_IP_dt"] = "B" + i.ToString();
dr["LOGIN_ERR_dt"] = "C" + i.ToString();
dt.Rows.Add(dr);
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;
options.Timeout = new TimeSpan(0, 10, 0);
using (TransactionScope Scop = new TransactionScope(TransactionScopeOption.Required, options))
{
try
{
int intCount = 0;
string sqlStatement_Select = string.Empty;
string sqlStatement_Insert = string.Empty;
DataSet ds = new DataSet();
SqlCommand sqlCommand = new SqlCommand();
SqlCommand sqlCommand2 = new SqlCommand();
//sqlStatement_Select = "SELECT Z1, Z2, Z3, Z4 FROM dbo.HR050 with(nolock);";
//sqlConn.Open();
//SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement_Select, sqlConn);
//adapter.Fill(ds);
//sqlConn.Close();
sqlConn.Open();
sqlCommand.Connection = sqlConn;
//ds.Tables[0].Rows.Count = 100000 (10萬筆)
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
sqlStatement_Insert += string.Format("INSERT INTO dbo.HR130 (EMPNO, LOGIN_TM, LOGIN_IP, LOGIN_ERR) "
+ " values (@EMPNO{0}, @LOGIN_TM{0}, @LOGIN_IP{0}, @LOGIN_ERR{0});", intCount);
sqlCommand.Parameters.Add(string.Format("@EMPNO{0}", intCount), SqlDbType.Char).Value = row["EMPNO_dt"].ToString().Trim();
sqlCommand.Parameters.Add(string.Format("@LOGIN_TM{0}", intCount), SqlDbType.Char).Value = row["LOGIN_TM_dt"].ToString().Trim();
sqlCommand.Parameters.Add(string.Format("@LOGIN_IP{0}", intCount), SqlDbType.Char).Value = row["LOGIN_IP_dt"].ToString().Trim();
sqlCommand.Parameters.Add(string.Format("@LOGIN_ERR{0}", intCount), SqlDbType.Char).Value = row["LOGIN_ERR_dt"].ToString().Trim();
intCount++;
//計算 Parameters 總數，當超過2000時，先執行現階段的 SQL 語句
if (sqlCommand.Parameters.Count > 2000)
{
sqlCommand.CommandText = sqlStatement_Insert;
sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
sqlStatement_Insert = "";
}
}
//當跳出迴圈後，執行尚未執行的 SQL 語句
if (sqlStatement_Insert != "")
{
sqlCommand.CommandText = sqlStatement_Insert;
sqlCommand.ExecuteNonQuery();
}
Scop.Complete();
}
}
catch (SqlException ex)
{
Response.Write(ex.Message);
}
finally
{
sqlConn.Close();
}
sw.Stop();
Response.Write(sw.Elapsed.TotalSeconds);
}
}
測試結果：
- 資料筆數：10萬筆
- 測試次數：10次
- 平均時間：299.61474306 (秒)
實作(二) SqlBulkCopy 大量資料寫入：
使用 SqlBulkCopy (效率好)
protected void Button1_Click(object sender, EventArgs e)
{
//引用stopwatch物件 (計算執行時間)
Stopwatch sw = new Stopwatch();
sw.Start();
//創造 DataTable，準備來接資料
DataTable dt = new DataTable();
dt.Columns.Add("EMPNO_dt", typeof(int));
dt.Columns.Add("LOGIN_TM_dt", typeof(string));
dt.Columns.Add("LOGIN_IP_dt", typeof(string));
dt.Columns.Add("LOGIN_ERR_dt", typeof(string));
//一口氣做10萬筆資料，來測試
for (int i = 0; i < 100000; i++)
{
DataRow dr = dt.NewRow();
dr["EMPNO_dt"] = i.ToString();
dr["LOGIN_TM_dt"] = "A" + i.ToString();
dr["LOGIN_IP_dt"] = "B" + i.ToString();
dr["LOGIN_ERR_dt"] = "C" + i.ToString();
dt.Rows.Add(dr);
}
//==================================================
//
// 1. 加入參考：System.Transactions
// 2. using System.Transactions;
//
//==================================================
//以下是我參閱 demo大 的範例與註解，並自行加上一點說明
//================================================================
//
// 1.參考資料 http://demo.tc/Post/283
// 2.參考資料 http://bibby.be/2008/04/blog-post_25.html
// 3.參考資料 http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlbulkcopy_members(VS.80).aspx
//
//==============================================================
using (TransactionScope TranScope = new TransactionScope())
{
using (sqlConn)
{
try
{
sqlConn.Open();
string sqlstatement = @"Delete From dbo.HR130;";
SqlCommand sqlCommand = new SqlCommand(sqlstatement, sqlConn);
sqlCommand.ExecuteNonQuery();
//宣告SqlBulkCopy
using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConn))
{
//設定一個批次量寫入多少筆資料
sqlBC.BatchSize = 1000;
//設定逾時的秒數
sqlBC.BulkCopyTimeout = 60;
//設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。
sqlBC.NotifyAfter = dt.Rows.Count;
sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(CallSqlRowsCopied);
//設定要寫入的資料庫
sqlBC.DestinationTableName = "dbo.HR130";
//對應資料行 (來源欄位,目的地欄位)
sqlBC.ColumnMappings.Add("EMPNO_dt", "EMPNO");
sqlBC.ColumnMappings.Add("LOGIN_TM_dt", "LOGIN_TM");
sqlBC.ColumnMappings.Add("LOGIN_IP_dt", "LOGIN_IP");
sqlBC.ColumnMappings.Add("LOGIN_ERR_dt", "LOGIN_ERR");
//開始寫入
sqlBC.WriteToServer(dt);
//完成交易
TranScope.Complete();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
}
sw.Stop();
Response.Write(sw.Elapsed.TotalSeconds);
}
void CallSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Response.Write("成功");
}
測試結果：
- 資料筆數：10萬筆
- 測試次數：10次
- 平均時間：3.32959143 (秒)
補充：
使用 SqlBulkCopy 時，特別用 Profiler 來追蹤
--第一步
Delete From dbo.HR130;
--第二步
select @@trancount; SET FMTONLY ON select * from dbo.HR130 SET FMTONLY OFF exec ..sp_tablecollations_90 N'dbo.HR130'
--第三步
insert bulk dbo.HR130 ([EMPNO] Char(8) COLLATE Latin1_General_CS_AS, [LOGIN_TM] Char(20) COLLATE Latin1_General_CS_AS, [LOGIN_IP] Char(15) COLLATE Latin1_General_CS_AS, [LOGIN_ERR] Char(8) COLLATE Latin1_General_CS_AS)
以下是引用 MSDN 的說明：
Microsoft SQL Server 包含名為 bcp 的常用命令列公用程式，可將大型檔案快速地大量複製到 SQL Server 資料庫中的資料表或檢視表。 SqlBulkCopy 類別可讓您撰寫會提供類似功能的 Managed 程式碼方案。 還可採用其他方式將資料載入 SQL Server 資料表 (例如，INSERT 陳述式)，但 SqlBulkCopy 的效能優勢明顯高於它們。 SqlBulkCopy 類別可用於僅將資料寫入 SQL Server 資料表。 但是資料來源不僅限於 SQL Server；可使用任何資料，只要該資料可載入到 DataTable 執行個體，或可使用 IDataReader 執行個體進行讀取。 使用 SqlBulkCopy 類別，您可以執行：
單一大量複製作業
多項大量複製作業
在交易內的大量複製作業
注意事項： 要注意的狀況是，如果使用 SQL Server 2005 SP1 ，並使用 SqlBulkCopy 時，可能會造成 SQL Server Crash 的狀況，所以還是先將 SQL Server 2005 版本更新後，再使用比較好。 請參閱：FIX： SQL Server 2005 可能會停止回應使用 SqlBulkCopy 類別從另一個資料來源中匯入資料時
結語說明：
以上的示範，寫法、觀念上不足之處，請大家見諒，也麻煩大家不吝給予指教，感恩。
Source Code：MoveLargeData_WithSqlBulkCopy.zip
參考資料：
1. demo 大：使用SqlBulkCopy將資料批次寫入資料庫
2. bibby 大：用SqlBulkCopy大量寫入資料庫結合交易
3. MSDN：SqlBulkCopy 類別
