Linq to SQL

Linq	System.Collections.Generic.IEnumerable<T>
Linq to DataSet	System.Data.EnumerableRowCollection<DataRow>
Linq to SQL	System.Linq.IQueryable<T>
Linq to XML	System.Collections.Generic.IEnumerable<T>

System.Data.Linq.IExecuteResult

System.Data.Linq.IFunctionResult

System.Data.Linq.IMultipleResults

System.Data.Linq.ISingleResult<T>

System.Data.Linq.ITable

映射

DataContext 是一个转换器，它将LINQ查询操作转换为 SQL语名

DataContext 使用Connection 来访问数据库，

DataContext提供了以下功能：

执行SQL语句
同步映射对象与数据库
以日志形式记录DataContext生成的SQL
创建和删除数据库

Connection 属性(连接对象)

SubmitChanges 方法(数据更新到数据库中)

DBML

Database Mark Language。数据库描述语言，

是一种xml格式的文档，用来描述数据库。

使用sqlmetal产生dbml

sqlmetal /server:yourserver /database:northwind /dbml:YourDbml.dbml

使用dbml生成code

sqlmetal YourDbml.dbml /code: nwind.cs

可以用language选项，控制生成vb.net或c#语言的代码

弱DataContext的使用

[System.Data.Linq.Mapping.Table(Name = "dbo.tab1")]

public partial class tab1

{

private string _ID;

private string _a;

public tab1()

{

}

[System.Data.Linq.Mapping.Column(Storage = "_ID", DbType = "NVarChar(50) NOT NULL", CanBeNull = false, IsPrimaryKey = true)]

public string ID

{

get

{

return this._ID;

}

set

{

this._ID = value;

}

[System.Data.Linq.Mapping.Column(Storage = "_a", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]

public string a

{

get

{

return this._a;

}

set

{

this._a = value;

}

System.Data.Linq.DataContext tt = new DataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

System.Data.Linq.Table<tab1> ts = tt.GetTable<tab1>();

this.dataGridView1.DataSource = ts;

this.dataGridView1.DataSource = ts.Where(p => p.ID == "1");

tt.SubmitChanges();

强DataContext的使用

System.Data.Linq.DataContext

System.Data.Linq.Mapping.MappingSource,

System.Data.Linq.Mapping.AttributeMappingSource

System.Data.Linq.Table<tab1>

Attrib

[System.Data.Linq.Mapping.Table(Name = "dbo.tab1")]

[System.Data.Linq.Mapping.Column(Storage = "_ID", DbType = "NVarChar(50) NOT NULL", CanBeNull = false, IsPrimaryKey = true)]

创建映射对象并创建数据库

public partial class ttDataContext : System.Data.Linq.DataContext

{

private static System.Data.Linq.Mapping.MappingSource mappingSource = new System.Data.Linq.Mapping.AttributeMappingSource();

static ttDataContext()

{ }

public ttDataContext(string connection) :

base(connection, mappingSource)

{ }

public System.Data.Linq.Table<tab1> tab1

{

get

{ return this.GetTable<tab1>(); }

}

[System.Data.Linq.Mapping.Table(Name = "dbo.tab1")]

public partial class tab1

{

private string _ID;

private string _a;

public tab1()

{

}

[System.Data.Linq.Mapping.Column(Storage = "_ID", DbType = "NVarChar(50) NOT NULL", CanBeNull = false, IsPrimaryKey = true)]

public string ID

{

get

{

return this._ID;

}

set

{

this._ID = value;

}

[System.Data.Linq.Mapping.Column(Storage = "_a", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]

public string a

{

get

{

return this._a;

}

set

{

this._a = value;

}

ttDataContext tt = new ttDataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

bool b = tt.DatabaseExists();

if (!b)

{

tt.CreateDatabase();

}

创建映射对象并加载数据

映射见上

ttDataContext tt = new ttDataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

this.dataGridView1.DataSource = tt.tab1;

this.dataGridView1.DataSource = tt.tab1.Where(p => p.ID == "1");

数据库->映射对象（可视化）

创建dbml

从数据源添加表、视图、函数、存储过程映射

指定表的数据操作方法

默认为自动生成SQL语句，可以为插入、更新、删除指定到自定义函数或存储过程上

设置字段映射属性

具体说明见[字段映射属性]

设置函数或存储过程的属性

完善验证器

#region Extensibility Method Definitions

partial void OnCreated();

partial void InsertDBItem(DBItem instance);

partial void UpdateDBItem(DBItem instance);

partial void DeleteDBItem(DBItem instance);

partial void InsertDBItemList(DBItemList instance);

partial void UpdateDBItemList(DBItemList instance);

partial void DeleteDBItemList(DBItemList instance);

partial void InsertDBPerson(DBPerson instance);

partial void UpdateDBPerson(DBPerson instance);

partial void DeleteDBPerson(DBPerson instance);

partial void InsertDBPersonExtension(DBPersonExtension instance);

partial void UpdateDBPersonExtension(DBPersonExtension instance);

partial void DeleteDBPersonExtension(DBPersonExtension instance);

#endregion

可以用partial 类实现这些partial 方法

DataContext对象、表对象的类结构

视图对象的类结构

函数、存储过程的类结构

映射对象->数据库（可视化）

创建dbml

设计

创建数据库

CreateDatabase 创建数据库

DatabaseExists 数据库是否存在

DeleteDatabase 删除数据库

ttDataContext tt = new ttDataContext("Data Source=.;Initial Catalog=tt;Integrated Security=True");

bool b = tt.DatabaseExists();

if(!b)

{

tt.CreateDatabase();

}

字段映射属性 Data.Linq.Mapping.Column

Access

见上图说明

Auto Generated Value

见上图说明

Auto-Sync

见上图说明

Nulllable

见上图说明

Primary Key

见上图说明

Read Only

见上图说明

Server Data Type

见上图说明

Source

见上图说明

Update Check

见上图说明

Type

见上图说明

Delay Loaded (延迟加载)

当将某个字段[Delay Loaded]设为真，对集合Single或ToList时，为真的字段并没有从数据库加载，只有真正读取时，才会从数据库加载该字段的数控
对数据量大的字段最好使用这种方法
默认为不延迟加载

不延迟加载的效果

tttDataContext ttt = new tttDataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

t v = ttt.t.Single(p => p.aStr == "abcde");

事件跟踪器

exec sp_executesql N'SELECT [t0].[aStr], [t0].[bByte]

FROM [dbo].[t] AS [t0]

WHERE [t0].[aStr] = @p0',N'@p0 nvarchar(5)',@p0=N'abcde'

延迟加载的效果

tttDataContext ttt = new tttDataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

t v = ttt.t.Single(p => p.aStr == "abcde");

事件跟踪器

exec sp_executesql N'SELECT [t0].[aStr]

FROM [dbo].[t] AS [t0]

WHERE [t0].[aStr] = @p0',N'@p0 nvarchar(5)',@p0=N'abcde'

只有当执行

byte[] bs = v.bByte;

事件跟踪器

exec sp_executesql N'SELECT [t0].[bByte]

FROM [dbo].[t] AS [t0]

WHERE [t0].[aStr] = @p0',N'@p0 nvarchar(10)',@p0=N'abcde '

Time Stamp (并发时间戳)

在不同的DataContext之间做更新

using (LinqTestDBDataContext a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True"))

{

r = a.DBItem.Single(p => p.ItemID == "a");

r.ItemMatter = "new ";

}

using (LinqTestDBDataContext a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True"))

{

a.DBItem.Attach(r, true);

this.dataGridView1.DataSource = a.DBItem;

// a.SubmitChanges();

}

LinqTestDBDataContext a1 = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

DBItem r = null;

r = a1.DBItem.Single(p => p.ItemID == "a");

r.ItemMatter = "new ";

LinqTestDBDataContext a2 = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

a2.DBItem.AttachAll(a1.DBItem);

this.dataGridView1.DataSource = a2.DBItem;

System.Data.Linq.DataContext操作

SubmitChanges方法 (并发处理)

// 如果主键也并发修改了，会报错

用数据库的新值回添客户端

try

{

// 表示即使发生冲突也要继续

a.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);

// 表示只要发生冲突就不再继续

// a.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);

// a.SubmitChanges();

}

catch (System.Data.Linq.ChangeConflictException ex)

{

foreach (System.Data.Linq.ObjectChangeConflict occ in a.ChangeConflicts)

{

DBItem p = (DBItem)occ.Object;

// 以当前客户端中的值为准

//occ.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);

// 以当前数据库中的值为准

// occ.Resolve(System.Data.Linq.RefreshMode.KeepCurrentValues);

// 如果数据库中的值没有发生变化，则以当前客户端中的值为准。否则，则以当前数据库中的值为准

//occ.Resolve(System.Data.Linq.RefreshMode.KeepChanges);

foreach (System.Data.Linq.MemberChangeConflict mcc in occ.MemberConflicts)

{

// 当前客户端中的值

string currentValue = mcc.CurrentValue.ToString();

// 原来数据库中的值

string originalValue = mcc.OriginalValue.ToString();

// 当前数据库中的值

string databaseValue = mcc.DatabaseValue.ToString();

Console.WriteLine("当前客户端中的值:{0},原来数据库中的值:{1},当前数据库中的值:{2}", currentValue, originalValue, databaseValue);

// 以当前客户端中的值为准

mcc.Resolve(System.Data.Linq.RefreshMode.OverwriteCurrentValues);

// 以当前数据库中的值为准

// mcc.Resolve(System.Data.Linq.RefreshMode.KeepCurrentValues);

// 如果数据库中的值没有发生变化，则以当前客户端中的值为准。否则，则以当前数据库中的值为准

// mcc.Resolve(System.Data.Linq.RefreshMode.KeepChanges);

}

Refresh 刷新映射对象

默认

当数据库通过其它程序插入新记录时，读取a.DBItem时会自动得到新数据
当数据库通过其它程序修改记录的主键时，读取a.DBItem时会自动得到新数据
当数据库通过其它程序修改记录的非主键时，读取a.DBItem时不会自动得到新数据,要使用Refresh的.RefreshMode.OverwriteCurrentValues刷新

a.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, a.DBItem);

RefreshMode.KeepCurrentValues	强制 Refresh 方法使用从数据库检索的值替换原始值。不会修改当前值。
RefreshMode.KeepChanges	强制 Refresh 方法保留已更改的当前值，但将其他值更新为数据库值。
RefreshMode.OverwriteCurrentValues	强制 Refresh 方法使用数据库中的值重写所有当前值。

DataContext.GetChangeSe方法 (得到改变内容的行)

System.Data.Linq.ChangeSet cs =DataContext.GetChangeSet()

将得DataContext中所有表的改变内容的行，具体是那个表的，还要筛选一下

添加的行

LinqTestDBDataContext a;

a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

System.Collections.Generic.IList<object> o = a.GetChangeSet().AddedEntities;

删除的行

LinqTestDBDataContext a;

a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

System.Collections.Generic.IList<object> o = a.GetChangeSet().RemovedEntities;

修改的行

LinqTestDBDataContext a;

a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

System.Collections.Generic.IList<object> o = a.GetChangeSet().ModifiedEntities;

Data.Linq.Table<TEntity> 行集合操作

行集合

行集合的Linq查询结果集是System.Linq.IQueryable<T>

IsReadOnly 属性

bool { get; }

集合是否为只读

Attach

见Time Stamp (并发时间戳)

可对一些实体使用 Attach 方法。这些实体在一个 DataContext 中创建并序列化到客户端，随后被反序列化回来，以便执行更新或删除操作。因为新的 DataContext 无法跟踪已断开连接的实体的原始值是什么，所以，客户端会负责提供这些值

不要尝试对尚未通过序列化进行分离的实体执行 Attach 操作。尚未序列化的实体仍与延迟加载程序保持关联。如果实体改由第二个数据上下文跟踪，延迟加载程序可能会导致意外结果。

附加新的实体后，将会初始化所有子集合（例如，关联表中的实体的 EntitySet 集合）的延迟加载程序。调用 SubmitChanges 后，子集合的成员将被置于 Unmodified 状态。若要更新子集合的成员，必须显式调用 Attach，并指定该实体。

Attach(TEntity)

如果执行开放式并发检查时需要原始值，请将已断开连接或"已分离"的实体附加到新的 DataContext。

在此版本的 Attach 中，假定实体处于其原始值状态。调用此方法后，可以更新其字段，例如使用从客户端发送的其他数据进行更新。

Attach(TEntity, Boolean)

以修改或未修改状态将实体附加到 DataContext。如果为 True，则以修改状态附加这些实体

如果以修改状态附加，则实体必须声明一个版本号或不得参与更新冲突检查。附加新的实体后，将会初始化所有子集合（例如，关联表中的实体的 EntitySet 集合）的延迟加载程序。调用 SubmitChanges 后，子集合的成员将被置于 Unmodified 状态。若要更新子集合的成员，必须显式调用 Attach，并指定该实体。

Attach(TEntity, TEntity)

通过指定实体及其原始状态，以修改或未修改状态将实体附加到 DataContext。

original:与包含原始值的数据成员具有相同实体类型的实例。

附加新的实体后，将会初始化所有子集合（例如，关联表中的实体的 EntitySet 集合）的延迟加载程序。调用 SubmitChanges 后，子集合的成员将被置于 Unmodified 状态。若要更新子集合的成员，必须显式调用 Attach，并指定该实体

AttachAll 方法

见Time Stamp (并发时间戳)

如果以修改状态附加，则实体必须声明一个版本号或不得参与更新冲突检查。

AttachAll<TSubEntity>(IEnumerable<TSubEntity>)

此方法将集合中的所有实体附加到新的 DataContext。附加新的实体后，将会初始化所有子集合（例如，关联表中的实体的 EntitySet 集合）的延迟加载程序。调用 SubmitChanges 后，子集合的成员将被置于 Unmodified 状态。若要更新子集合的成员，必须显式调用 Attach，并指定该实体

AttachAll<TSubEntity>(IEnumerable<TSubEntity>), Boolean)

如果对象拥有时间戳或 RowVersion 成员，则为 true，如果执行开放式并发检查时要使用原始值，则为 false

此方法以修改或未修改状态将集合的所有实体附加到 DataContext。如果以修改状态附加，则实体必须声明一个版本号或不得参与更新冲突检查。如果以未修改状态附加，则假定实体表示原始值。调用此方法后，可在调用 SubmitChanges 前使用来自客户端的其他信息修改实体的字段。有关更多信息，请参见 N 层应用程序中的数据检索和 CUD 操作 (LINQ to SQL)。

GetModifiedMembers 方法

见行版本

ModifiedMemberInfo[] GetModifiedMembers(TEntity entity)

GetNewBindingList 方法

见行版本

IBindingList GetNewBindingList()

GetOriginalEntityState 方法,行的历史数据

LinqTestDBDataContext a;

a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

var v =a.DBItem.GetOriginalEntityState(a.DBItem.First());

System.Console.WriteLine("{0},{1}",v.ItemID,v.ItemMatter);

System.Console.WriteLine("{0},{1}", a.DBItem.First().ItemID, a.DBItem.First().ItemMatter);

查询

集合全部

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

System.Data.Linq.Table<DBItem> t = a.DBItem;

foreach (var temp in t)

{

Console.WriteLine("{0},{1}", temp.ItemID, temp.ItemMatter);

}

Lambda

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

System.Linq.IQueryable<DBItem> t = a.DBItem.Where (p=>p.ItemID=="a");

foreach (var temp in t)

{

Console.WriteLine("{0},{1}", temp.ItemID, temp.ItemMatter);

}

Linq

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var t = from temp in a.DBItem where temp.ItemID == "a" select temp;

foreach (var temp in t)

{

Console.WriteLine("{0},{1}", temp.ItemID, temp.ItemMatter);

}

查询并返回新组合(投影)

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var t = from temp in a.DBItem select new { temp.ItemID, temp.ItemMatter, bak = "wxd" };

//System.Linq.IQueryable tt = a.DBItem.Select(p => new { p.ItemID, p.ItemMatter, bak = "wxd" });

this.dataGridView1.DataSource = tt;

基于关系的查询

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = a.DBItem.Where(p => p.ItemID == "a").Single();

var rr = r.DBItemList.Select(p => new {p.NameID,p.ItemID,p.ItemValue,p.AutoId });

var rrr = rr.ToList();

this.dataGridView1.DataSource = rrr;

联合查询

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = from temp1 in a.DBPerson

join temp2 in a.DBPersonExtension on temp1.NameID equals temp2.NameID

select new { temp1.NameID ,temp1.Name,temp2.Age,temp2.Sex,temp2.Login};

this.dataGridView1.DataSource = r;

添加记录

InsertAllOnSubmit<TSubEntity>)

将集合中所有处于 pending insert 状态的实体添加到 DataContext。

在调用 SubmitChanges 之前，已添加的实体将不会位于查询结果中

InsertOnSubmit

将处于 pending insert 状态的实体添加到此

在调用 SubmitChanges 之前，将不会查看此表的查询结果中已添加的实体。

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

//单条

DBItem r = new DBItem();

r.ItemID = "x";

r.ItemMatter = "new add x";

a.DBItem.InsertOnSubmit(r);

//批量

List<DBItem> rs = new List<DBItem> { new DBItem { ItemID = "y", ItemMatter = "new add y" }, new DBItem { ItemID = "z", ItemMatter = "new add z" } };

a.DBItem.InsertAllOnSubmit(rs);

//提交

a.SubmitChanges();

基于关系添加记录

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var x = a.DBItem.Single(p => p.ItemID == "a");

DBItemList r1 = new DBItemList { ItemID = "x", ItemValue = 400, NameID = "n01" }; //ItemID是x

DBItemList r2 = new DBItemList { ItemValue = 500, NameID = "n01" }; //ItemID是 null

//基于关系添加记录

x.DBItemList.Add(r1);

x.DBItemList.Add(r2);

a.SubmitChanges();

删除记录

DeleteAllOnSubmit(<TSubEntity>)

将集合中的所有实体置于 pending delete 状态

直到调用 SubmitChanges 后，才会看到查询结果中不存在已移除实体。必须首先附加已断开连接的实体，然后才能将其删除

DeleteOnSubmit

将此表中的实体置为 pending delete 状态

直到调用 SubmitChanges 后，才会看到查询结果中不存在已移除实体。必须首先附加已断开连接的实体，然后才能将其删除

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

//单条

var r = a.DBItem.Where(p => p.ItemID == "x").Single();

a.DBItem.DeleteOnSubmit(r);

//批量

System.Linq.IQueryable<DBItem> rr = a.DBItem.Where(p => p.ItemMatter.Contains("new"));

a.DBItem.DeleteAllOnSubmit(rr);

//提交

a.SubmitChanges();

修改记录

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

//单条

var r = a.DBItem.Where(p => p.ItemID == "a").Single();

r.ItemMatter = r.ItemMatter + "!";

//批量

System.Linq.IQueryable<DBItem> rr = a.DBItem.Where(p => p.ItemMatter.Contains("this"));

List<DBItem> rrr = rr.ToList();

rrr.ForEach(p=>p.ItemMatter=p.ItemMatter+"!");

//提交

a.SubmitChanges();

分页

Skip与Take方法

在数据库中进行，使用的是ROW_NUMBER() OVER (ORDER BY [列])

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

int BeginN = 8;

int N = 5;

var r = a.DBItemList.Skip(BeginN).Take(N);

this.dataGridView1.DataSource = r;

分组

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = from temp in a.DBItemList

group temp by temp.ItemID into gp

select gp;

List<DBItemList> l = new List<DBItemList>();

foreach (var s in r)

{

Console.WriteLine("{0}组", s.Key); ;

foreach (var ss in s)

{

Console.WriteLine("{0},{1},{2}",ss.ItemID,ss.NameID,ss.ItemValue);

}

a组

a,n01,4

a,n01,5

a,n02,2

a,n02,3

a,n02,6

a,n03,3

b组

b,n03,5

b,n01,2

b,n01,1

c组

c,n01,4

c,n01,5

c,n02,2

c,n02,3

c,n02,6

c,n03,3

汇总

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var x = from tp1 in a.DBItemList.Select(p => new { p.ItemID }).Distinct()

join tp2 in a.DBItemList on tp1.ItemID equals tp2.ItemID

into newtab

select new { tp1.ItemID, ValueSum = newtab.Sum(p => p.ItemValue) };

this.dataGridView1.DataSource = x;

与该效果相同

select ItemID ,sum(ItemValue) as ValueSum from dbo.DBItemList group by ItemID

二进制数据操作

添加

tttDataContext ttt = new tttDataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

t r = new t();

r.aStr = "abcde";

r.bByte = new byte[] { 65, 66, 67, 68, 69 };

ttt.t.InsertOnSubmit(r);

ttt.SubmitChanges();

读取

tttDataContext ttt = new tttDataContext("Data Source=.;Initial Catalog=ttt;Integrated Security=True");

t v= ttt.t.Single(p => p.aStr == "abcde");

byte[] bs = v.bByte;

foreach (byte b in bs)

{

System.Console.WriteLine(b);

}

事物处理

SubmitChanges 会做默认的事务处理

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

//修改

var r1 = a.DBItem.Where(p => p.ItemID == "a").Single();

r1.ItemMatter = r1.ItemMatter + "!";

//添加

DBItem r2 = new DBItem();

r2.ItemID = "x";

r2.ItemMatter = "new add x";

a.DBItem.Add(r2);

// //添加

//有主键冲突

List<DBItem> rs = new List<DBItem> { new DBItem { ItemID = "x", ItemMatter = "new add y" },

new DBItem { ItemID = "z", ItemMatter = "new add z" } };

a.DBItem.AddAll(rs);

//如果提交时有错误，所有操作都回滚

a.SubmitChanges();

Transaction 属性

System.Data.Common.DbTransaction 执行SQL语句、存储过程的事物

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

if (a.Connection.State != ConnectionState.Open)

{

a.Connection.Open();

}

//只有连接对象打开时才能开始事物

System.Data.Common.DbTransaction tran = a.Connection.BeginTransaction();

a.Transaction = tran;

try

{

//没有事物，后面的出错，前面的已执行

//有了事物，将成为一组。Commit()时同时提交。

a.ExecuteCommand("insert into DBItem (ItemID,ItemMatter) values ('x' ,'hello')");

a.PRinsertDBItem("x", "this is x1");

a.PRinsertDBItem("x2", "this is x2");

a.Transaction.Commit();

}

catch

{

a.Transaction.Rollback();

}

System.Transactions.TransactionScope 执行SQL语句、存储过程的事物

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

using (System.Transactions.TransactionScope tc = new TransactionScope())

{

try

{

//没有事物，后面的出错，前面的已执行

//有了事物，将成为一组。Commit()时同时提交。

a.ExecuteCommand("insert into DBItem (ItemID,ItemMatter) values ('x' ,'hello')");

a.PRinsertDBItem("x1", "this is x1");

a.PRinsertDBItem("x2", "this is x2");

tc.Complete();

}

catch

{ }

}

映射函数、存储过程

调用无返回值的存储过程

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

int n = a.PRinsertDBItem("xxx", "this pr test"); // 返回值是受影响的行数

System.Console.WriteLine(n); //0

调用有返回值的存储过程

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

int? v=0;

int n= a.PRadd(10,22,ref v); // 返回值是受影响的行数

System.Console.WriteLine(v); //220

System.Console.WriteLine(n); //0

调用返回表的存储过程

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = a.PRSelectDBItem("a");

// System.Data.Linq.ISingleResult<PRSelectDBItem_结果> r = a.PRSelectDBItem("a");

this.dataGridView1.DataSource = t.ToList();

调用返回值函数

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

int? v = a.FUadd(1, 2);

System.Console.WriteLine(v);

调用返回表函数

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

System.Linq.IQueryable<FUSelectDBItem_结果> r = a.FUSelectDBItem("%");

this.dataGridView1.DataSource = r;

执行SQL语句

ExecuteCommand 方法

执行SQL语句，并返回受影响的数

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

//使用参数时字符型 {0} ,不用 '{0}'

int i1= a.ExecuteCommand("insert into DBItem (ItemID,ItemMatter) values ({0} ,{1})", "z", "this is test");

int i2 = a.ExecuteCommand("insert into DBItem (ItemID,ItemMatter) values ('y' ,'hello')");

int i3 = a.ExecuteCommand("update DBItem set ItemMatter='hello' where ItemMatter='this is test'");

int i4 = a.ExecuteCommand("delete from DBItem where ItemMatter='hello'");

ExecuteQuery 方法

执行Select语句，并返回行集合

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

//使用参数时字符型 {0} ,不用 '{0}'

var v= a.ExecuteQuery<DBItem>("select * from DBItem");

this.dataGridView1.DataSource = v.ToList();

使用DbDataReader数据源

Translate方法

SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

DataContext a = new DataContext(conn);

SqlCommand cmd = new SqlCommand("select * from DBItem", conn);

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

List<DBItem> ls= a.Translate<DBItem>(reader).ToList();

this.dataGridView1.DataSource = ls;

conn.Close();

补充说明

数据是延时的

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = a.DBItemList; // 日志没有记录，事件跟踪器没有记录

//-----------以下代码执行LINQ3次，事件跟踪器也有3次记录--------------

this.dataGridView1.DataSource = r; //执行LINQ

System.Threading.Thread.Sleep(2000);

List<DBItemList> l = r.ToList(); //执行LINQ

System.Threading.Thread.Sleep(2000);

foreach (var tp in r) //执行LINQ

{

System.Console.WriteLine(tp);

}

多个LINQ查询会合并成一个SQL提交

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var x =from tp in a.DBItemList

where tp.NameID=="n01"

select tp;

var y = x.Where(p => p.ItemID == "a");

this.dataGridView1.DataSource =y.Take(5);

事件跟踪器

exec sp_executesql N'SELECT TOP 5 [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE ([t0].[ItemID] = @p0) AND ([t0].[NameID] = @p1)',N'@p0 nvarchar(1),@p1 nvarchar(3)',@p0=N'a',@p1=N'n01'

自动缓存

查询后数据会被缓存，如果之后以主键查询的话，会先在缓存里查找

命中例子

LinqTestDBDataContext a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

var r = a.DBItemList.Where(p => p.ItemID=="a");

this.dataGridView1.DataSource = r; //没有此句，查询就没执行

DBItemList v1 = a.DBItemList.Single(p => p.AutoId == 27); //缓存中有，在缓存中查

事件跟踪器

exec sp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'a'

没命中例子

LinqTestDBDataContext a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

var r = a.DBItemList.Where(p => p.ItemID=="a");

this.dataGridView1.DataSource = r; //没有此句，查询就没执行

DBItemList v2 = a.DBItemList.Single(p => p.AutoId == 7); //缓存中没有，在数据库中查

事件跟踪器

exec sp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'a'

exec sp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE [t0].[AutoId] = @p0',N'@p0 int',@p0=7

GetCommand方法 (得到linq 查询对应的SQL语句)

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = a.DBItem.Where(p => p.ItemID == "a");

System.Data.Common.DbCommand cmd = a.GetCommand(r);

System.Console.WriteLine("SQL语句:{0}", cmd.CommandText);

foreach (System.Data.Common.DbParameter parm in cmd.Parameters)

{

System.Console.WriteLine(string.Format("参数名:{0},参数值:{1}", parm.ParameterName, parm.Value));

}

SQL语句:SELECT [t0].[ItemID], [t0].[ItemMatter]

FROM [dbo].[DBItem] AS [t0]

WHERE [t0].[ItemID] = @p0

参数名:@p0,参数值:a

Log 属性(日志记录器)

日志记录器

System.IO.StreamWriter sw = new System.IO.StreamWriter(@"c:\a.txt", true);

private void button1_Click(object sender, EventArgs e)

{

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

a.Log = sw;

var r = a.DBItemList;

this.dataGridView1.DataSource = r;

}

private void button2_Click(object sender, EventArgs e)

{

//写入流

sw.Flush();

sw.Close();

}

Mapping 属性(映射信息)

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

string s1 = a.Mapping.DatabaseName;//LinqTestDB

System.Console.WriteLine("数据库:{0}", s1);

string s2 = a.Mapping.ProviderType.ToString();//System.Data.Linq.SqlClient.SqlProvider

System.Console.WriteLine("ProviderType:{0}", s2);

string s3 = a.Mapping.ContextType.ToString();//WindowsFormsApplication10.LinqTestDBDataContext

System.Console.WriteLine("ContextType:{0}", s3);

var v1 = a.Mapping.GetFunctions();

foreach (var tp in v1)

{

System.Console.WriteLine("Function:{0}", tp.Name);

}

//Function:PRinsertDBItem

//Function:PRadd

//Function:PRSelectDBItem

//Function:FUadd

//Function:FUSelectDBItem

var v3 = a.Mapping.GetTables();

foreach (var tp in v3)

{

System.Console.WriteLine("TableName:{0},RowType:{1}", tp.TableName, tp.RowType);

}

//TableName:dbo.DBItem,RowType:DBItem

//TableName:dbo.DBItemList,RowType:DBItemList

//TableName:dbo.DBPerson,RowType:DBPerson

//TableName:dbo.DBPersonExtension,RowType:DBPersonExtension

//TableName:dbo.ViewItem,RowType:ViewItem

//TableName:dbo.ViewItemlistSum,RowType:ViewItemlistSum

//TableName:dbo.ViewPersionPersionextension,RowType:ViewPersionPersionextension

LoadOptions属性（主外查询数据加载选项）

不使用数据加载选项

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

var r = a.DBItem;

foreach (var s in r)

{

// 每一次迭代都会执行一条SQL语句来返回当前DBItem的DBItemList

foreach (var ss in s.DBItemList)

{

System.Console.WriteLine("{0},{1},{2}", ss.ItemID, ss.NameID, ss.ItemValue);

}

a,n01,4

a,n01,5

a,n02,2

a,n02,3

a,n02,6

a,n03,3

a,n01,4

b,n03,5

b,n01,2

b,n01,1

c,n01,4

c,n01,5

c,n02,2

c,n02,3

c,n02,6

c,n03,3

事件跟踪器

SELECT [t0].[ItemID], [t0].[ItemMatter] FROM [dbo].[DBItem] AS [t0]

exec sp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'a'

exec sp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'b'

exec sp_executesql N'SELECT [t0].[AutoId], [t0].[NameID], [t0].[ItemID], [t0].[ItemValue]

FROM [dbo].[DBItemList] AS [t0]

WHERE [t0].[ItemID] = @p0',N'@p0 nvarchar(1)',@p0=N'c'

使用数据加载选项

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();

// DBItem以LEFT JOIN的方式关联DBItemList

options.LoadWith<DBItem>(p => p.DBItemList);

a.LoadOptions = options;

var r = a.DBItem;

foreach (var s in r)

{

foreach (var ss in s.DBItemList)

{

System.Console.WriteLine("{0},{1},{2}", ss.ItemID, ss.NameID, ss.ItemValue);

}

a,n01,4

a,n01,5

a,n02,2

a,n02,3

a,n02,6

a,n03,3

a,n01,4

b,n03,5

b,n01,2

b,n01,1

c,n01,4

c,n01,5

c,n02,2

c,n02,3

c,n02,6

c,n03,3

事件跟踪器

SELECT [t0].[ItemID], [t0].[ItemMatter], [t1].[AutoId], [t1].[NameID], [t1].[ItemID] AS [ItemID2], [t1].[ItemValue], (

SELECT COUNT(*)

FROM [dbo].[DBItemList] AS [t2]

WHERE [t2].[ItemID] = [t0].[ItemID]

) AS [count]

FROM [dbo].[DBItem] AS [t0]

LEFT OUTER JOIN [dbo].[DBItemList] AS [t1] ON [t1].[ItemID] = [t0].[ItemID]

ORDER BY [t0].[ItemID], [t1].[AutoId]

使用数据加载选项加载数据，并且限制所要加载的数据

LinqTestDBDataContext a = new LinqTestDBDataContext();

a.Connection.ConnectionString = "Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True";

System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();

options.LoadWith<DBItem>(p => p.DBItemList);

options.AssociateWith<DBItem>(p=>p.DBItemList.Where(pp=>pp.NameID=="n01"));

a.LoadOptions = options;

var r = a.DBItem;

foreach (var s in r)

{

foreach (var ss in s.DBItemList)

{

System.Console.WriteLine("{0},{1},{2}", ss.ItemID, ss.NameID, ss.ItemValue);

}

a,n01,4

a,n01,5

a,n01,4

b,n01,2

b,n01,1

c,n01,4

c,n01,5

事件跟踪器

exec sp_executesql N'SELECT [t0].[ItemID], [t0].[ItemMatter], [t1].[AutoId], [t1].[NameID], [t1].[ItemID] AS [ItemID2], [t1].[ItemValue], (

SELECT COUNT(*)

FROM [dbo].[DBItemList] AS [t2]

WHERE ([t2].[NameID] = @p0) AND ([t2].[ItemID] = [t0].[ItemID])

) AS [count]

FROM [dbo].[DBItem] AS [t0]

LEFT OUTER JOIN [dbo].[DBItemList] AS [t1] ON ([t1].[NameID] = @p0) AND ([t1].[ItemID] = [t0].[ItemID])

ORDER BY [t0].[ItemID], [t1].[AutoId]',N'@p0 nvarchar(3)',@p0=N'n01'

System.Data.Linq.SqlClient.SqlMethods

Like 静态方法

LinqTestDBDataContext a = new LinqTestDBDataContext("Data Source=.;Initial Catalog=LinqTestDB;Integrated Security=True");

var li = a.DBItem .Where(p => System.Data.Linq.SqlClient.SqlMethods.Like(p.ItemMatter, "this is%"));

dataGridView1.DataSource =li;

使用WCF的注意

设置序列化

命令

sqlmetal /code:test.cs /namespace:wxwinter /language:csharp /server:wxwinter /database:wxwinterWFTempDB /sprocs /functions /pluralize /serialization:unidirectional

设计器

posted @ 2009-01-05 13:53 WXWinter(冬) 阅读(9002) 评论(14) 编辑收藏举报

刷新页面返回顶部