[VS2010] ADO.NET Entity Framework: 由 Entity Object 執行 SQL 指令

http://www.dotblogs.com.tw/regionbbs/archive/2010/02/16/netfx.4.0.new.feature.ado.net.entity.framework.2.0.directly.execute.sql.commands.aspx

其實這個功能在 Entity Framework 中是有一點背道而馳的感覺。

不 過就算是要做到像 POCO 這樣的全自動化 (無 SQL) 的資料存取元件，在一些特殊情況下，還是會動用到 SQL 指令來做一些事情，在 Entity Framework 1.0 中並沒有辦法這樣做，通常會以最傳統的 DbConnection 來連接資料庫後，才能使用 DbCommand 來存取資料庫，到了 .NET Framework 4.0，ADO.NET 團隊在 ObjectContext 中加入了三個方法，讓開發人員可以直接在 ObjectContext 上直接使用 SQL 指令來存取資料庫，並且神奇的是，它可以自動將資料填到指定的型別中。

 

這三個方法是：

 

• ObjectContext.ExecuteStoreCommand()：執行非資料提取式的 SQL 指令 (即 INSERT, UPDATE 與 DELETE)，並回傳受影響的資料列數。
• ObjectContext.ExecuteStoreQuery<T>()：執行資料提取式的 SQL 指令 (即 SELECT)，並回傳一個指定型別的集合。
• ObjectContext.Translate<T>()：將 DbDataReader 中的資料轉換成指定型別的物件。

 

而這三個方法可以讓開發人員在完全導入 Entity Framework ORM Solution 之前，可以有一個過渡的功能可利用，不用為了要做 ORM 而大幅改寫核心程式碼以融入 Entity Framework，並且提供對資料來源最直接的控制。

 

我們就以前面的文章：[VS2010] ADO.NET Entity Framework: 建立多對多關聯模型 所建立的資料模型，來玩玩看這些功能吧。

 

1. 測試傳回內建型別的資料，請在 Main 方法中加入下列程式碼並執行：

 

static void Main(string[] args)
{
    using (SchoolDBContainer context = new SchoolDBContainer())
    {
        foreach (string courseName in context.ExecuteStoreQuery<string>("SELECT name FROM CoursesSet") )
        {
            Console.WriteLine("Course name: {0}", courseName);
        }
    }

    Console.ReadLine();
}

 

你應該會看到類似這樣的輸出：

 

 

ExecuteStoreQuery<T>() 透過傳入的 SQL 指令以及參數，將資料庫回傳的資料集以指定的型別來回傳，以上面的例子來說，就是把 CoursesSet 中的 name 屬性轉換成 string 型別的集合回傳，因為回傳的是一個 IEnumerate<T> 的集合，故可以直接使用 foreach 來巡覽並存取每個集合中的物件。它的第一個參數是 SQL 指令，第二個參數是 params 的參數陣列。ExecuteStoreQuery<T>() 可以支援的參數類型有三種：

 

1. 使 用 pattern 方式的參數指定，例如 ExecuteStoreQuery<string>(“SELECT name FROM CoursesSet WHERE CourseID = {0}”, 1234)，當然，這會有 SQL Injection 的問題，因此最好不要使用。
2. 使用參數化查詢方式的參數指定，例如 ExecuteStoreQuery<string>(“SELECT name FROM CoursesSet WHERE CourseID = @p0”, 1234)，這是建議的作法。
3. 若 想要對參數有更進一步的控制，可以直接傳入 DbParameter 物件，例如 ExecuteStoreQuery<string>(“SELECT name FROM CoursesSet WHERE CourseID = @p0”, new SqlParameter( “@p0 ”, 1234))。

 

2. 測試以類別裝載傳回的資料，請在 Main 方法中加入下列程式碼並執行：

 

public class Course
{
    public string CourseID { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using (SchoolDBContainer context = new SchoolDBContainer())
        {
            foreach (Course course in context.ExecuteStoreQuery<Course>("SELECT * FROM CoursesSet"))
            {
                Console.WriteLine("Course ID: {0}, name: {1}", course.CourseID, course.Name);
            }
        }

        Console.ReadLine();
    }
}

 

你應該可以看到這個畫面：

 

如果要傳回的是一組資料，而且具有明確的類別物件，可以在 <T> 中設定該物件類別，由 ExecuteStoreQuery<T> 來自動幫我們將傳回的資料裝載到物件的屬性中，當然，該物件有幾個條件必須符合：

1. 物件必須有預設無參數建構式，並且不是抽象類別。
2. 屬性名稱必須要與欄位名稱對應。
3. 屬性必須要是可讀寫 (read/write) 的。

 

若指定的類別中出現無法與欄位名稱對應的屬性時，ExecuteStoreQuery<T> 會略過該屬性的讀取，執行進行下一個屬性欄位的讀取，因此會發生資料遺漏的問題。

 

3. 測試非提取型 SQL 指令。請在 Main 中加入下列程式碼並執行：

 

public class Course
{
    public string CourseID { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using (SchoolDBContainer context = new SchoolDBContainer())
        {
            context.ExecuteStoreCommand("INSERT INTO CoursesSet (CourseID, Name) VALUES (@p0, @p1)",
                "C4", "Database Concepts");

            foreach (Course course in context.ExecuteStoreQuery<Course>("SELECT * FROM CoursesSet"))
            {
                Console.WriteLine("Course ID: {0}, name: {1}", course.CourseID, course.Name);
            }
        }

        Console.ReadLine();
    }
}

 

你應該可以看到下列畫面：

 

ExecuteStoreCommand() 與 ExecuteStoreQuery<T> 的方法差不多，但它是執行非提取型 SQL 指令，並回傳受影響的資料列數，如同 DbCommand.ExecuteNonQuery() 方法一樣。它的操作方式與 ExecuteStoreQuery<T> 相似，一樣可接受三種不同的參數，但筆者極強烈建議使用參數化查詢 。

 

4. 測試由 DbDataReader 轉換資料到物件。請在 Main 中加入下列程式碼並執行：

 

public class Course
{
    public string CourseID { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        SqlConnection connection = new SqlConnection(
            "Data Source=.;Initial Catalog=AdoEF_Example;Integrated Security=True;MultipleActiveResultSets=True");
        connection.Open();

        SqlCommand cmd = new SqlCommand("SELECT * FROM CoursesSet", connection);
        SqlDataReader reader = cmd.ExecuteReader();

        using (SchoolDBContainer context = new SchoolDBContainer())
        {
            foreach (Course course in context.Translate<Course>(reader) )
            {
                Console.WriteLine("Course ID: {0}, name: {1}", course.CourseID, course.Name);
            }
        }

        reader.Close();
        connection.Close();

        Console.ReadLine();
    }
}

 

你應該可以看到下列畫面：

 

Translate<T>() 方法的行為和 ExecuteStoreQuery<T> 類似，它雖然不是直接執行 SQL 指令，但它是可以允許開發人員使用原有的 ADO.NET 物件模型，呼叫 DbCommand.ExecuteReader() 傳回的 DbDataReader 轉換成指定型別的一個方法，而 ExecuteStoreQuery<T> 內部就是使用這個方法來轉換資料結果集到指定物件的。

 

參考資料：

Directly Executing Store Commands

How to: Directly Execute Commands Against the Data Source