MEMO-使用LINQ to SQL直接執行SQL指令

使用LINQ to SQL時，難免會遇到基於簡潔效率考量需要直接下SQL指令的場合。依我的習慣，遇到這類情境我就不用LINQ寫法硬幹了。DataContext物件提供了ExecuteCommand及ExecuteQuery兩個方法，可以直接撰寫有效率的SQL語法，交給DB執行批次更新動作或取回複合式查詢的結果。

但切記!! 直接操控SQL語法並不代表用直接組裝SQL指令字串，這樣很容易產生SQL Injection漏洞。基於這條開發基本常識，ExecuteCommand當然也鼔勵大家用Parameter處理動態參數，Method的第一個參數是CommandText，後續可再逐一傳入參數值。這裡CommandText的參數宣告寫法與傳統"@paramName"的格式不同，要透過{0}, {1}方式指定。實際執行時，冰雪聰明的DataContext會將{0}, {1}轉成@p0, @p1，並依傳入參數值物件的型別決定Parameter型別。

我寫了個簡單的程式，分別傳入字串、整數及日期做示範:

public static void Test3()

{

    using (MyLabDataClassesDataContext db = 

        new MyLabDataClassesDataContext())

    {

        db.Log = Console.Out;

        db.ExecuteCommand(@"

UPDATE HumanResources.Employee 

SET Title = {0}

WHERE EmployeeID = {1}

AND BirthDate = {2}",

             "Jedi Master",

             1,

             new DateTime(1972, 5, 15)

        );

        Console.Read();

    }

}

利用Log轉向，可以觀察實際被轉換成的SqlCommand資訊如下:

UPDATE HumanResources.Employee
SET Title = @p0
WHERE EmployeeID = @p1
AND BirthDate = @p2
-- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [Jedi Master]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [1972/05/15 上午 12:00:00
]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

很簡便吧!

查詢時的處理方法也差不多，但ExecuteQuyer必須指定查詢結果要對應的物件型別，如果查詢結果來自JOIN或有另外組合欄位的話，就沒有現成的物件型別可用，要自行定義物件來承接查詢結果。如以下範例:

class ResultClass

{

    public int EmployeeId { get; set; }

    public string Title { get; set; }

}

public static void Test4() 

{

    using (MyLabDataClassesDataContext db =

        new MyLabDataClassesDataContext())

    {

        db.Log = Console.Out;

        var q = db.ExecuteQuery<ResultClass>(@"

SELECT EmployeeID,Title 

FROM HumanResources.Employee 

WHERE Title LIKE '%' + {0} + '%'",

                             "Manager");

        foreach (ResultClass x in q)

        {

            Console.WriteLine("{0}-{1}",

                x.EmployeeId,

                x.Title

                );

        }

        Console.Read();

    }

}

執行結果如下:

SELECT EmployeeID,Title
FROM HumanResources.Employee
WHERE Title LIKE '%' + @p0 + '%'
-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Manager]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

3-Engineering Manager
6-Marketing Manager
21-Production Control Manager
30-Human Resources Manager
42-Information Services Manager
71-Finance Manager
90-Document Control Manager
139-Accounts Manager
150-Network Manager
158-Research and Development Manager
200-Quality Assurance Manager
217-Research and Development Manager
218-Facilities Manager
268-North American Sales Manager
274-Purchasing Manager
284-European Sales Manager
288-Pacific Sales Manager