再接再厉VS 2008 sp1 + .NET 3.5 sp1(4) - Entity Framework(实体框架)之EntityClient, ObjectQuery

[索引页]
[源码下载]

再接再厉VS 2008 sp1 + .NET 3.5 sp1(4) - Entity Framework(实体框架)之EntityClient, ObjectQuery

作者：webabcd


介绍
以Northwind为示例数据库，ADO.NET Entity Framework之详解EntityClient, ObjectQuery

• EntityConnection - 与存储模型的连接
• EntityCommand - 对 EDM 执行的命令
• EntityParameter - 配置 EntityCommand 的参数
• EntityDataReader - 以只读、只进的数据流的方式获取数据（内存中始终只有一行）。相当于SqlDataReader
• ObjectQuery<T> - 通过 Entity SQL 或 查询语法 或 Linq 方法对概念模型做查询
• ObjectContext.CreateQuery<T>(Entity SQL) - 根据 esql 创建一个 ObjectQuery<T> 。（延迟）

示例
1、关于EntityClient
EntityClient.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.EntityClient;
using System.Data;

using VS2008SP1.Business;

public partial class EntityFramework_EntityClient : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Demo();

            result.InnerHtml += "<br />";

            Demo2();

            result.InnerHtml += "<br />";

            Demo3();
        }
    }

    void Demo()
    {
        string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString;
        // EntityConnection - 与存储模型的连接。构造函数的参数为连接字符串
        //     Open() - 打开连接
        //     Close() - 关闭连接
        //     CreateCommand() - 创建此连接的 EntityCommand 对象
        using (EntityConnection conn = new EntityConnection(strConn))
        {
            conn.Open();

            try
            {
                // EntityCommand - 对 EDM 执行的命令
                //     CommandType - 命令类型 [System.Data.CommandType枚举]
                //         CommandType.Text - esql语句。默认值
                //         CommandType.StoredProcedure - 存储过程名
                //         CommandType.TableDirect - 表名
                //     CommandText - 命令文本。esql语句或存储过程名或表名
                //     CommandTimeout - 超时时间。单位：秒
                using (EntityCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select value c from NorthwindEntities.Categories as c";

                    // EntityDataReader - 以只读、只进的数据流的方式获取数据（内存中始终只有一行）。相当于SqlDataReader
                    //     Read() - 读下一条记录
                    //     HasRows() - 是否还有可读数据
                    //     Close() - 关闭 EntityDataReader
                    // EntityCommand.ExecuteReader() - 执行命令，返回 EntityDataReader 对象
                    using (EntityDataReader edr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        while (edr.Read())
                        {
                            result.InnerHtml += edr["CategoryName"].ToString() + "<br />";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.InnerHtml += ex.ToString();
            }
            finally
            {
                conn.Close();
            }
        }

        /**//*
        SELECT 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[CategoryName] AS [CategoryName], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Picture] AS [Picture]
        FROM [dbo].[Categories] AS [Extent1]
        */
    }

    void Demo2()
    {
        // EntityConnection 构造函数的参数为 name = web.config中的connectionStrings中配置的连接字符串的name
        using (EntityConnection conn = new EntityConnection("name = NorthwindEntities"))
        {
            conn.Open();

            try
            {
                using (EntityCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select value s from NorthwindEntities.categories as s where s.categoryId = @categoryId";

                    // EntityParameter - 配置 EntityCommand 的参数
                    //     ParameterName - 参数名
                    //     Value- 参数值
                    //     Size - 参数大小
                    //     DbTyp - 参数类型 [System.Data.DbType 枚举]
                    //     IsNullable - 是否接受 null 值
                    EntityParameter param = new EntityParameter();
                    param.ParameterName = "categoryId";
                    param.Value = 1;

                    // EntityCommand.Parameters.Add() - 为 EntityCommand 增加参数
                    cmd.Parameters.Add(param);

                    using (EntityDataReader edr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        while (edr.Read())
                        {
                            result.InnerHtml += edr.GetString(1) + "<br />";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.InnerHtml += ex.ToString();
            }
            finally
            {
                conn.Close();
            }
        }

        /**//*
        exec sp_executesql N'SELECT 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[CategoryName] AS [CategoryName], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Picture] AS [Picture]
        FROM [dbo].[Categories] AS [Extent1]
        WHERE [Extent1].[CategoryID] = @categoryId',N'@categoryId int',@categoryId=1
        */
    }

    void Demo3()
    {
        // EntityConnectionStringBuilder - 构造连接字符串
        EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
        entityBuilder.Provider = "System.Data.SqlClient";
        entityBuilder.ProviderConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True";
        entityBuilder.Metadata = @"res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl";

        using (EntityConnection conn = new EntityConnection(entityBuilder.ConnectionString))
        {
            conn.Open();

            try
            {
                using (EntityCommand cmd = conn.CreateCommand())
                {
                    // 调用存储过程（需要先做好映射，然后指定概念模型中的函数名称）
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "NorthwindEntities.GetCategory";

                    EntityParameter param = new EntityParameter();
                    param.ParameterName = "CategoryID";
                    param.Value = 1;
                    cmd.Parameters.Add(param);

                    using (EntityDataReader edr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        while (edr.Read())
                        {
                            result.InnerHtml += edr["CategoryName"].ToString() + "<br />";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.InnerHtml += ex.ToString();
            }
            finally
            {
                conn.Close();
            }
        }

        /**//*
        exec [dbo].[spSelectCategory] @CategoryID=1 
        */
    }
}

2、关于ObjectQuery
ObjectQuery.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.EntityClient;
using System.Data;
using System.Data.Common;

using VS2008SP1.Business;

public partial class EntityFramework_ObjectQuery : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Demo();

            result.InnerHtml += "<br />";

            Demo2();

            result.InnerHtml += "<br />";

            Demo3();

            result.InnerHtml += "<br />";

            Demo4();
        }
    }

    private void Demo()
    {
        using (var ctx = new NorthwindEntities())
        {
            string esql = "select value c from NorthwindEntities.Categories as c";

            // ObjectQuery<T> - 通过 Entity SQL 或 查询语法 或 Linq 方法对概念模型做查询
            // ObjectContext.CreateQuery<T>(Entity SQL) - 根据 esql 创建一个 ObjectQuery<T> 。（延迟）
            ObjectQuery<Categories> query = ctx.CreateQuery<Categories>(esql);

            foreach (var c in query)
            {
                result.InnerHtml += c.CategoryName + "<br />";
            }
        }

        /**//*
        SELECT 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[CategoryName] AS [CategoryName], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Picture] AS [Picture]
        FROM [dbo].[Categories] AS [Extent1]
        */
    }

    private void Demo2()
    {
        using (var ctx = new NorthwindEntities())
        {
            string esql = "select c.CategoryName from NorthwindEntities.Categories as c";

            // ObjectQuery<T>()构造函数 - 根据 esql 创建一个 ObjectQuery ，返回 DbDataRecord 。（延迟）
            // System.Data.Objects.MergeOption枚举 - 如何将此次查询结果与之前同一 ObjectContext 的存在与内存的查询结果做合并
            //     MergeOption.AppendOnly - 追加。默认值
            //     MergeOption.OverwriteChanges - 以新结果为准
            //     MergeOption.PreserveChanges - 以原结果为准
            //     MergeOption.NoTracking - 取消变更跟踪，不会使用 ObjectStateManager，减少执行查询的时间，所有返回的实体将是分离的状态（detached state）
            ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(esql, ctx, MergeOption.NoTracking);

            // 可以在 ObjectQuery<T> 上使用 Linq 方法或查询语法
            foreach (DbDataRecord item in query.Take(3))
            {
                result.InnerHtml += item["CategoryName"].ToString() + "<br />";
            }
        }

        /**//*
        SELECT 
        [Limit1].[C1] AS [C1], 
        [Limit1].[CategoryName] AS [CategoryName]
        FROM ( SELECT TOP (3) 
            [Extent1].[CategoryName] AS [CategoryName], 
            1 AS [C1]
            FROM [dbo].[Categories] AS [Extent1]
        )  AS [Limit1]
        */
    }

    private void Demo3()
    {
        using (var ctx = new NorthwindEntities())
        {
            string esql = "select value count(c.CategoryID) from NorthwindEntities.Categories as c";

            // ObjectQuery<简单类型> 的示例（延迟）
            ObjectQuery<int> query = ctx.CreateQuery<int>(esql);

            result.InnerHtml += query.First().ToString() + "<br />";
        }

        /**//*
        SELECT 
        [Limit1].[A1] AS [C1]
        FROM ( SELECT TOP (1) 
            COUNT([Extent1].[CategoryID]) AS [A1]
            FROM [dbo].[Categories] AS [Extent1]
        )  AS [Limit1] 
        */
    }

    private void Demo4()
    {
        using (var ctx = new NorthwindEntities())
        {
            string esql = "select value c from NorthwindEntities.Categories as c";
            
            // 延迟
            ObjectQuery<Categories> query = ctx.CreateQuery<Categories>(esql);

            // ObjectQuery<T>.Execute() - 立即执行查询。返回 ObjectResult<T>
            ObjectResult<Categories> queryResult = query.Execute(MergeOption.NoTracking);

            foreach (var c in queryResult)
            {
                result.InnerHtml += c.CategoryName + "<br />";
            }
        }

        /**//*
        SELECT 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[CategoryName] AS [CategoryName], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Picture] AS [Picture]
        FROM [dbo].[Categories] AS [Extent1] 
        */
    }
}

OK
[源码下载]