博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
[索引页]
[源码下载]


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