0

傳入字串或數字陣列當作篩選參數是很常見的SQL查詢情境，例如: 使用者在UI勾選取10項類別代碼，希望從Products資料表找出這10類的所有產品，轉換成SQL語法，相當於SELECT * FROM Products WHERE CategoryId IN (1,3,8,...,215)。

遇到這類需求，好傻好天真的開發者不小心會寫成恐怖的SQL Injection自殺式查詢:

string sql = "SELECT * FROM Products WHERE CategoryId IN (" +
                   string.Joing(", ", Request["categories"].Split(',')) + ")";

【嚴正提醒】直接將使用者輸入內容組成SQL字串如同在加油站放鞭炮，為害人害己的自殺行為，按江湖上的規矩，應判唯一死刑(阿魯巴到死!)! 請所有開發人員格外留意。

串SQL字串的做法大錯特錯，出了新手村的開發者都知道，DB查詢要用Parameter才是王道，但面對WHERE IN情境，得配合IN條件的資料筆數一一生出對應的Parameter，有點難度。不過，這可難不倒老江湖，薑!薑!薑!薑~ 

            using (SqlConnection cn = new SqlConnection(cnStr))
            {
                cn.Open();
                var cmd = cn.CreateCommand();
                string[] ids = "1,4".Split(',');
                int idx = 0;
                List<string> list = new List<string>();
                foreach (string id in ids) {
                    string pn = "@p" + idx++;
                    cmd.Parameters.Add(pn, SqlDbType.Int).Value = id;
                    list.Add(pn);
                }
                cmd.CommandText = "SELECT * FROM Products WHERE CategoryID IN ("
                    + string.Join(",", list.ToArray()) + ")";
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(dr["ProductName"]);
                }
                Console.Read();
            }

依IN條件筆數在SQL字串加入變數，再逐一產生SqlParameter放進SqlCommand.Parameters，運用List<string>、String.Join()的技巧，程式碼尚稱簡潔，但隱約覺得有點笨拙。比較大的問題是--這個做法很難搬進Stored Procedure，畢竟Stored Procudure的輸入參數必須預先定義寫死，不像C#有params object[] args可用!

我想起了TVP(Table-Value-Paramter， 資料表值參數，SQL2008起支援)! 如果能將WHERE IN篩選條件用陣列參數傳給SQL，多麼優雅呀!!

先來個小測試，在SQL建立NVarChar與Int型別的Table型別，基本上就能涵蓋大部分的WHERE IN應用。接著宣告一個@cattIds變數，塞入1跟4兩筆資料，當成北風資料庫Products資料表的類別WHERE IN條件，成功!

CREATE TYPE dbo.Str64Array 
    AS TABLE(item NVARCHAR(64))
CREATE TYPE dbo.IntArray 
    AS TABLE(item INT)
 
DECLARE @catgIds dbo.IntArray
INSERT INTO @catgIds VALUES(1);
INSERT INTO @catgIds VALUES(4);
SELECT * FROM Products 
WHERE CategoryID IN 
(SELECT Item FROM @catgIds)

下一步，把戰場拉回.NET，改用ADO.NET呼叫。@catgIds SqlParameter的型別是SqlDbType.Structured，需傳入DataTable物件當值。為便於重複利用，我寫了個小函式GetTVPValue<T>(params T[] args)，透過泛型技巧跟params彈性參數個數，就能用GetTVPValue<int>(1,2,3)或GetTVPValue<string>("A","B","C")輕鬆產生所需的DataTable。

改用TVP傳送WHERE IN參數後，程式碼是不是清爽多了呢?

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace ConsoleApplication1
{
    class Program
    {
        static string cnStr = 
            "Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind";
 
        static DataTable GetTVPValue<T>(params T[] args)
        {
            DataTable t = new DataTable();
            t.Columns.Add("Item", typeof(T));
            foreach (T item in args)
            {
                t.Rows.Add(item);
            }
            return t;
        }
 
        static void Main(string[] args)
        {
            using (SqlConnection cn = new SqlConnection(cnStr))
            {
                cn.Open();
                var cmd = cn.CreateCommand();
                cmd.CommandText = 
@"SELECT * FROM Products 
WHERE CategoryID IN 
(SELECT Item FROM @catgIds)";
                var p = cmd.Parameters.Add("@catgIds", SqlDbType.Structured);
                p.TypeName = "IntArray";
                p.Value = GetTVPValue<int>(1, 4);
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(dr["ProductName"]);
                }
                Console.Read();
            }
        }
    }
}

同樣的概念，搬到Stored Procedure自然也是一氣喝成:

CREATE PROCEDURE SelectProductsByCategoryId (
    @catgIds dbo.IntArray READONLY
)
AS
SELECT * FROM Products 
WHERE CategoryID IN 
    (SELECT Item FROM @catgIds)

 

            using (SqlConnection cn = new SqlConnection(cnStr))
            {
                cn.Open();
                var cmd = cn.CreateCommand();
                cmd.CommandText = "SelectProductsByCategoryId";
                cmd.CommandType = CommandType.StoredProcedure;
                var p = cmd.Parameters.Add("@catgIds", SqlDbType.Structured);
                p.TypeName = "IntArray";
                p.Value = GetTVPValue<int>(1, 4);
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(dr["ProductName"]);
                }
                Console.Read();
            }

搞定收工!


Comments

Be the first to post a comment

Post a comment