再接再厉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
[源码下载]
