PROGRAMer NEVER SLEEPS

前言

• 其實這是一個常見的問題...網路上資料很多，但只有自己整理過一遍才是自己的…
• 類似文章：[C#] 碰到 SQL IN 時，怎麼將陣列參數化？（to MODEL : Dapper + Slapper.AutoMapper）

 

基本介紹

開發環境

1. Microsoft Visual Studio .NET 2017 Professional
2. Oracle
3. LINQ

參考資料

• https://www.codeproject.com/Questions/1039085/How-Can-I-Pass-Dynamic-Array-To-Sql-Query-In-Cshar
• https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause/337792#337792

---

常見作法

以下是我們常見的作法，

由於OracleCommand或SQLCommand，不像Dapper有提供DynamicParameter的功能能使用，

所以碰到 xxx IN ( 'a', 'b' ,'c' ) 這種需求的時候，最直接的方法就是…自己組裝SQL Statement。

string inClause = "";

//組裝SQL查詢子句
for (int i = 0; i < empIDList.Length; i++) 
{
   if (i > 0) 
   {
      inClause += ", ";
   }
   inClause += " '" + empIDList[0] + "' ";
}

//組合SQL語法
string cmdText = " SELECT * FROM empData WHERE empID IN (" + inClause + ") ";

OracleCommand cmd = new OracleCommand(cmdText);

這種簡單粗暴的方法不是不行，但光是弱掃就會馬上被提示有SQL Injection的風險，

就不用說真的發生SQL Injection時的後果了.....

 

 

實作

這邊採用的方式也算是簡單粗暴的一種，

一樣是透過迴圈組裝，只是這次組裝的有兩個東西：

1. 參數名稱 (Parameter Name)
2. 參數值 (Parameter Value)

實際的作法如下

string cmdText = " SELECT * FROM empData WHERE empID IN ({0}) ";

//用LINQ為每個參數加上自己的編號
string[] paramArray = empIDList.Select((s, i) => ":IDS" + i.ToString()).ToArray();

//每個參數之間使用逗號當作分隔符號
string inClause = string.Join(",", paramArray);

OracleCommand cmd = new OracleCommand(string.Format(cmdText, inClause));

//為每個參數指定內容
for (int i = 0; i < paramArray.Length; i++)
{
   cmd.Parameters.AddWithValue(paramArray[i], empIDList[i]);
}

 

很難想像的話，我改說白話文...

實際運行的結果就像

//假設我們傳入的陣列是
string[] empIDList = new string[] {"001", "002", "003", "004"};

cmd.CommandText = " SELECT * FROM empData WHERE empID IN (:IDS0,:IDS1,:IDS2,:IDS3) ";

cmd.Parameters[":IDS0"] = "001";
cmd.Parameters[":IDS1"] = "002";
cmd.Parameters[":IDS2"] = "003";
cmd.Parameters[":IDS3"] = "004";

 

還是不懂？ 那我建議你照抄就是了....哈哈哈哈哈哈哈哈哈

 

最後，提供完整程式碼

/// <summary>
/// 帶出員工資料
/// </summary>
/// <param name="empIDList">欲查詢的員工工號清單</param>
/// <returns></returns>
public DataTable getEmpInfo(string[] empIDList)
{
   DataTable dt = new DataTable();
   using (OracleConnection conn = new OracleConnection(connectionString))
   {
      string cmdText = " SELECT * FROM empData WHERE empID IN ({0}) ";

      //用LINQ為每個參數加上自己的編號
      string[] paramArray = empIDList.Select((s, i) => ":IDS" + i.ToString()).ToArray();

      //每個參數之間使用逗號當作分隔符號
      string inClause = string.Join(",", paramArray);

      OracleCommand cmd = new OracleCommand(string.Format(cmdText, inClause));

      //為每個參數指定內容
      for (int i = 0; i < paramArray.Length; i++)
      {
         cmd.Parameters.AddWithValue(paramArray[i], empIDList[i]);
      }

      conn.Open();
      cmd.Connection = conn;
      cmd.CommandType = CommandType.Text;

      OracleDataReader dr = cmd.ExecuteReader();
      dt.Load(dr);

      conn.Close();
      conn.Dispose();
   }
   return dt;
}

 

使用這個方法，由於全部都參數化了，就可以完整又正確的避開原先的困擾了...

 

但是！實作的作法絕對不只有一種，

上面的參考資料，裡面就有提供三種方式..

有興趣可以自行參考咯~

 Written By Felix Hsieh