DataContext是LINQ to SQL操作实体类建立后紧接着需要操作的对象。本文总结其常用函数和属性的作用及常用用法,实体类是上一节的GuestInfo.cs类,数据库也采用上一节的数据库Database1.mdb,下面的示例程序对对象和属性按其功能进行了必要的分组,代码中也有详尽的注释。代码如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Linq; using System.IO; using System.Data.SqlClient; namespace DataContext对象成员 { /// <summary> /// 用于数据库连接的DataContext类成员 /// </summary> class Program { static void Main(string[] args) { //连接字符串 string constring = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\Visual Studio 2010\LINQ_to_SQL\LINQ_To_SQL自定义数据库和实体类\Database1.mdf;Integrated Security=True;User Instance=True"; //1.构造函数 DataContext dc = new DataContext(constring); //2.GetTable Table<GuestInfoEntity> table = dc.GetTable<GuestInfoEntity>(); var query = table.AsEnumerable(); foreach(var g in query) { Console.WriteLine("{0} {1} {2} {3}",g.ID,g.Name,g.Age,g.Tel ); } Console.WriteLine("-----------------"); Console.ReadKey(); string fileName = @"E:\Visual Studio 2010\LINQ_to_SQL\LINQ_To_SQL自定义数据库和实体类\Database1.mdf"; //3.DatabaseExists、DeleteDatabase、CreateDatabase if (dc.DatabaseExists()) { Console.WriteLine("数据库文件已经存在."); dc.DeleteDatabase(); } dc.CreateDatabase(); if (dc.DatabaseExists()) { Console.WriteLine("{0} 数据库文件创建成功.",Path.GetFileName(fileName )); } Console.WriteLine("-----------------"); Console.ReadKey(); //4_1.ExecuteCommand 作为LINQ补充直接用SQL指令操作数据库 dc.ExecuteCommand("insert into tb_GuestInfo(Name,Age,Tel) values({0},{1},{2})","DebugLZQ",25,"198****1336"); foreach (var r in dc.GetTable<GuestInfoEntity>()) { Console.WriteLine("{0} {1} {2} {3}",r.ID,r.Name,r.Age,r.Tel ); } Console.WriteLine("-----------------"); Console.ReadKey(); //4_2.ExecuteQuery 作为LINQ补充直接用SQL指令操作数据库 var rows= dc.ExecuteQuery<GuestInfoEntity>("select * from tb_GuestInfo"); foreach (var r in rows) { Console.WriteLine("{0} {1} {2} {3}", r.ID, r.Name, r.Age, r.Tel); } Console.WriteLine("-----------------"); Console.ReadKey(); //4_3Translate 将DbReader转换为LINQ对象 string queryString = "select * from tb_GuestInfo"; SqlConnection connection = new SqlConnection(constring); SqlCommand cmd = new SqlCommand(queryString, connection); connection.Open(); var result = dc.Translate<GuestInfoEntity>(cmd.ExecuteReader());//ADO.NET转换LINQ foreach (var r in result) { Console.WriteLine("{0} {1} {2} {3}", r.ID, r.Name, r.Age, r.Tel); } connection.Close(); Console.WriteLine("-----------------"); Console.ReadKey(); //5.SubmitChanges 应用修改 var firstrow = (from p in dc.GetTable<GuestInfoEntity>() select p).First(); firstrow.Name =firstrow.Name +"A"; dc.SubmitChanges();//应用修改 foreach (var r in dc.GetTable<GuestInfoEntity>() ) { Console.WriteLine("{0} {1} {2} {3}", r.ID, r.Name, r.Age, r.Tel); } Console.WriteLine("-----------------"); Console.ReadKey(); //6.GetChangeSet方法 返回DataContext对象插入、删除、修改过的对象 ChangeSet cs = dc.GetChangeSet(); foreach (var r in cs.Updates ) { GuestInfoEntity t = r as GuestInfoEntity; Console.WriteLine("{0} {1} {2} {3}", t.ID,t.Name,t.Age,t.Tel ); } Console.WriteLine("-----------------"); Console.ReadKey(); //7.Refresh刷新实体对象 var row1=(from g in dc.GetTable<GuestInfoEntity>() select g).First(); row1.Age = row1.Age + 5; dc.SubmitChanges(); dc.Refresh(RefreshMode.OverwriteCurrentValues, row1);// foreach (var r in dc.GetTable<GuestInfoEntity>()) { Console.WriteLine("{0} {1} {2} {3}", r.ID, r.Name, r.Age, r.Tel); } Console.WriteLine("7-----------------"); Console.ReadKey(); //ChangeConflicts属性 返回DataContext操作数据库时产生的并发冲突合集 //Transaction属性 设置或返回DataContext跟其他ADO.NET程序共享的事物对象 //ObjectTrackingEnabled属性 开启或关闭DataContext实体对象的状态跟踪 //8.Log属性 返回DataContext产生的SQL命令 dc.Log = Console.Out;//控制台输出DataContext产生的SQL语句 foreach (var r in dc.GetTable<GuestInfoEntity>()) { Console.WriteLine("{0} {1} {2} {3}", r.ID, r.Name, r.Age, r.Tel); } Console.WriteLine("-----------------"); Console.ReadKey(); } } }
程序中的注释很详细,不再赘述。
程序的运行结果如下:
操作单一表格的Table<TEntity>类
前面介绍了DataContext类,它可以用来映射和连接数据库,执行SQL命令,跟踪实体对象的状态。
下面介绍Table<TEntity>表示表格记录,它是一个泛型集合类,它的元素就是表格实体对象。它提供一组方法,对元素进行添加删除操作,并可以通过DataContext将这些操作保存到数据库。
表还是前面的那张表,在项目中添加了一个LINQ to SQL类。重点是InsertOnSubmit、DeleteOnSubmit等方法。
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace LINQ_to_SQL_Table { /// <summary> /// 操作单一表格的Table<TEntity>类 /// </summary> class Program { static void Main(string[] args) { //1.a.Attach附加实体 DataClasses1DataContext dc1 = new DataClasses1DataContext(); tb_GuestInfo guset = new tb_GuestInfo() { Id=1, Name = "DebugLZQ", Age = 35, Tel = "138****8888" }; dc1.tb_GuestInfo.Attach(guset);//这样的Attach仅仅附加实体,数据库没有更新 dc1.SubmitChanges(); //显示附加成功 foreach (var g in dc1.tb_GuestInfo) { Console.WriteLine("{0} {1} {2} {3}", g.Id, g.Name, g.Age, g.Tel); } Console.WriteLine("---------"); //显示数据库没有更新 DataClasses1DataContext dc2 = new DataClasses1DataContext(); foreach (var g in dc2.tb_GuestInfo) { Console.WriteLine("{0} {1} {2} {3}", g.Id, g.Name, g.Age, g.Tel); } Console.WriteLine("------------------------"); Console.ReadKey(); //2.InsertOnSubmit dc2.tb_GuestInfo.InsertOnSubmit(guset); dc2.SubmitChanges(); foreach (var g in dc2.tb_GuestInfo) { Console.WriteLine("{0} {1} {2} {3}", g.Id, g.Name, g.Age, g.Tel); } Console.WriteLine("------------------------"); Console.ReadKey(); //2b.InsertAllOnSubmit 插入集合 List<tb_GuestInfo> lst = new List<tb_GuestInfo>() { new tb_GuestInfo(){ Name="AA", Age=25,Tel="133****3333"}, new tb_GuestInfo(){ Name="BB", Age=25,Tel="135****5555"} }; dc2.tb_GuestInfo.InsertAllOnSubmit(lst); dc2.SubmitChanges(); foreach (var g in dc2.tb_GuestInfo) { Console.WriteLine("{0} {1} {2} {3}", g.Id, g.Name, g.Age, g.Tel); } Console.WriteLine("------------------------"); Console.ReadKey(); // //3.DeleteOnSubmit tb_GuestInfo entity = (from g in dc2.tb_GuestInfo where g.Name == "AA" select g).Single(); dc2.tb_GuestInfo.DeleteOnSubmit(entity);// dc2.SubmitChanges(); foreach (var g in dc2.tb_GuestInfo) { Console.WriteLine("{0} {1} {2} {3}", g.Id, g.Name, g.Age, g.Tel); } Console.WriteLine("------------------------"); Console.ReadKey(); //3b.DeleteAllOnSubmit IEnumerable<tb_GuestInfo> entitys = from g in dc2.tb_GuestInfo where g.Name == "AA" || g.Name == "BB" select g; dc2.tb_GuestInfo.DeleteAllOnSubmit(entitys); dc2.SubmitChanges(); foreach (var g in dc2.tb_GuestInfo) { Console.WriteLine("{0} {1} {2} {3}", g.Id, g.Name, g.Age, g.Tel); } Console.WriteLine("------------------------"); Console.ReadKey(); } } }
程序运行结果如下:
更多SQL内容来自木庄网络博客