使用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


Comments

Be the first to post a comment

Post a comment