强类型 DataContext

强类型 DataContext

 

public partial class NorthwindDataContext : DataContext

 

{

 

public Table<Customer> Customers;

 

public NorthwindDataContext(IDbConnection connection) : base(connection) { }

 

public NorthwindDataContext(stringconnection) : base(connection) { }

 

}

 

强类型数据上下文使代码更简洁：

 

Beijing ZJS Express Stock Limited Company

Address: The 11^th Floor,Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.

Postcode: 100016 Name: Liu Xiaohui Email:Xiaohui_liu0406@163.com

Tel: 13488810897  Office: 010-84561144-1816

Page 13 of 113

NorthwindDataContext ctx = new

DoNet Framework 3.5    系列

 

NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

 

GridView1.DataSource = from c in ctx.Customers where c.CustomerID.StartsWith("A")

 

select new {  顾客ID =c.CustomerID,                              顾客名 = c.Name,  城市  =c.City };

 

GridView1.DataBind();

 

DataContext 其实封装了很多实用的功能，下面一一介绍。

 

日志功能

 

using System.IO;

NorthwindDataContext ctx = new

 

NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

 

StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true); // Append

 

ctx.Log = sw;

 

GridView1.DataSource = from c in ctx.Customers where c.CustomerID.StartsWith("A")

 

select new {  顾客ID =c.CustomerID,                              顾客名 = c.Name,  城市  =c.City };

 

GridView1.DataBind();

 

sw.Close();

 

运行程序后在网站所在目录生成了log.txt，每次查询都会把诸如下面的日志追加到文本

 

文件中：

 

SELECT [t0].[CustomerID],[t0].[ContactName], [t0].[City]

 

FROM [Customers] AS [t0]

 

Beijing ZJS Express Stock Limited Company

Address: The 11^th Floor,Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.

Postcode: 100016 Name: Liu Xiaohui Email:Xiaohui_liu0406@163.com

Tel: 13488810897  Office: 010-84561144-1816

Page 14 of 113

WHERE [t0].[CustomerID] LIKE @p0

 

-- @p0: Input String (Size = 2; Prec = 0;Scale = 0) [A%]

DoNet Framework 3.5    系列

 

-- Context: SqlProvider(Sql2005) Model:AttributedMetaModel Build: 3.5.20706.1

 

应该说这样的日志对于调试程序是非常有帮助的。

 

探究查询

using System.Data.Common;

 

using System.Collections.Generic;

 

 

 

 

NorthwindDataContext ctx = new

 

NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

 

var select = from c in ctx.Customers where c.CustomerID.StartsWith("A") selectnew { 顾

 

客ID =c.CustomerID,                              顾客名 = c.Name,     城市 = c.City };

 

DbCommand cmd = ctx.GetCommand(select);

 

Response.Write(cmd.CommandText + "<br/>");

 

foreach (DbParameter parm incmd.Parameters)

 

   Response.Write(string.Format("参数名:{0},参数值:{1}<br/>", parm.ParameterName,

 

parm.Value));

 

Customer customer = ctx.Customers.First();

 

customer.Name = "zhuye";

 

IList<object> queryText =ctx.GetChangeSet().ModifiedEntities;

 

Response.Write(((Customer)queryText[0]).Name);

 

 

Beijing ZJS Express Stock Limited Company

Address: The 11^th Floor,Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.

Postcode: 100016 Name: Liu Xiaohui Email:Xiaohui_liu0406@163.com

Tel: 13488810897  Office: 010-84561144-1816

Page 15 of 113

DoNetFramework 3.5    系列

在这里，我们通过DataContext 的GetCommand 方法获取了查询对应的DbCommand，

 

并且输出了CommandText 和所有的DbParameter。之后，我们又通过GetChangeSet 方

 

法获取了修改后的实体，并输出了修改内容。

 

执行查询

 

 

NorthwindDataContext ctx = new

 

NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

 

string newcity = "Shanghai";

 

ctx.ExecuteCommand("update Customers set City={0} where CustomerID like'A%'",

 

newcity);

 

IEnumerable<Customer> customers = ctx.ExecuteQuery<Customer>("select* from

 

Customers where CustomerID like 'A%'");

 

GridView1.DataSource = customers;

 

GridView1.DataBind();

 

前一篇文章已经说了，虽然Linqto sql 能实现90％以上的TSQL 功能。但是不可否认，

 

对于复杂的查询，使用TSQL 能获得更好的效率。因此，DataContext 类型也提供了执行

 

SQL 语句的能力。

创建数据库

Beijing ZJS Express Stock Limited Company

Address: The 11^th Floor,Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.

Postcode: 100016 Name: Liu Xiaohui Email:Xiaohui_liu0406@163.com

Tel: 13488810897  Office: 010-84561144-1816

Page 16 of 113

DoNetFramework 3.5    系列

testContext ctx = new testContext("server=xxx;database=testdb;uid=xxx;pwd=xxx");

 

ctx.CreateDatabase();

[Table(Name = "test")]

 

public class test

 

{

 

       [Column(IsPrimaryKey = true, IsDbGenerated = true)]

 

public int ID { get; set; }

       [Column(DbType="varchar(20)")]

 

public string Name { get; set; }

 

}

 

 

 

 

public partial class testContext : DataContext

 

{

 

public Table<test> test;

 

public testContext(string connection) : base(connection){ }

 

}

 

这段代码在数据库中创建了名为testdb的数据库，等同于下面的脚本：

 

CREATE TABLE [dbo].[test](

 

 

 

Beijing ZJS Express Stock Limited Company

Address: The 11^th Floor,Zhaowei Building, Jiangtai Road, Chaoyang District of Beijing.

Postcode: 100016 Name: Liu Xiaohui Email:Xiaohui_liu0406@163.com

Tel: 13488810897  Office: 010-84561144-1816

Page 17 of 113

 

[ID] [int] IDENTITY(1,1) NOT NULL,

DoNet Framework 3.5    系列

 

(

[Name] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,

 

CONSTRAINT [PK_test] PRIMARYKEY CLUSTERED

 

[ID] ASC

 

)WITH(IGNORE_DUP_KEY = OFF) ON [PRIMARY]

 

) ON [PRIMARY]

 

同时，DataContext还提供了DeleteDatabase()方法，在这里就不列举了。