ADO.NET与ORM的比较(3)Linq to SQL实现CRUD

 using System; 
 using System.Collections.Generic; 
 using System.Linq; 
 using System.Text; 
 using System.Data.SqlClient; 
 using System.Data.Linq; 
 using System.Configuration; 
    
 namespace LinqDemo 
 { 
         /// <summary> 
         /// 说明：这个类是为了演示.NET中的Linq to SQL的用法 
         /// 作者：周公（周金桥） 
         /// 日期：2010-03-01 
         /// </summary> 
         public class LinqCRUD 
         { 
                 /// <summary> 
                 /// 统计用户总数 
                 /// </summary> 
                 /// <returns></returns> 
                 public int Count() 
                 { 
方法一#region 方法一 
                         //使用SqlConnection来实例化DataContext对象 
                         SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LinqDemo.Properties.Settings.AspNetStudyConnectionString"].ConnectionString); 
                         DataContext context = new DataContext(connection); 
                         IEnumerable<int> collection = context.ExecuteQuery<int>("select count(1) from UserInfo"); 
                         int count = collection.ElementAt<int>(0); 
                         return count; 
                         #endregion 
    
方法二#region 方法二 
                         //UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         ////return context.UserInfo.Count<UserInfo>(item => item.Age > 23);//带条件统计 
                         //return context.UserInfo.Count<UserInfo>(); 
                         #endregion 
                 } 
                 /// <summary> 
                 /// 创建用户 
                 /// </summary> 
                 /// <param name="info">用户实体</param> 
                 /// <returns></returns> 
                 public void Create(UserInfo info) 
                 { 
                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         context.UserInfo.InsertOnSubmit(info); 
                         context.SubmitChanges(); 
                 } 
                 /// <summary> 
                 /// 读取用户信息 
                 /// </summary> 
                 /// <param name="userId">用户编号</param> 
                 /// <returns></returns> 
                 public UserInfo Read(int userId) 
                 { 
                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         context.Log = Console.Out; 
                         var query = from item in context.UserInfo 
                                                 where item.UserID == userId 
                                                 select item; 
                         return query.First<UserInfo>(); 
                 } 
                 /// <summary> 
                 /// 更新用户信息 
                 /// </summary> 
                 /// <param name="info">用户实体</param> 
                 /// <returns></returns> 
                 public void Update(UserInfo info) 
                 { 
                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         UserInfo ui = context.UserInfo.First<UserInfo>(item => item.UserID == info.UserID); 
                         ui.Age = info.Age; 
                         ui.Email = info.Email; 
                         ui.Mobile = info.Mobile; 
                         ui.Phone = info.Phone; 
                         ui.RealName = info.RealName; 
                         ui.Sex = info.Sex; 
                         ui.UserName = info.UserName; 
                         context.SubmitChanges(); 
                 } 
                 /// <summary> 
                 /// 删除用户 
                 /// </summary> 
                 /// <param name="userId">用户编号</param> 
                 /// <returns></returns> 
                 public void Delete(int userId) 
                 { 
方法一#region 方法一 
                         //UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         //context.ExecuteCommand("delete from UserInfo where UserId=" + userId); 
                         #endregion 
方法二#region 方法二 
                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         UserInfo ui = context.UserInfo.First<UserInfo>(item => item.UserID == userId); 
                         context.UserInfo.DeleteOnSubmit(ui); 
                         context.SubmitChanges(); 
                         #endregion 
                 } 
    
                 /// <summary> 
                 /// 删除用户 
                 /// </summary> 
                 /// <param name="userId">用户实体</param> 
                 /// <returns></returns> 
                 public void Delete(UserInfo info) 
                 { 
                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         var userList = from Users    
                                                            in context.UserInfo 
                                                            where Users.UserID == info.UserID    
                                                            select Users; 
                         foreach (var user in userList) 
                         { 
                                 context.UserInfo.DeleteOnSubmit(user); 
                         } 
                         //context.UserInfo.DeleteOnSubmit(userList.First<UserInfo>()); 
                         //注意下面的写法是错误的 
                        // context.UserInfo.DeleteOnSubmit(info); 
                         context.SubmitChanges(); 
                 } 
    
                 /// <summary> 
                 /// 获取用户表中编号最大的用户 
                 /// </summary> 
                 /// <returns></returns> 
                 public int GetMaxUserId() 
                 { 
                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext(); 
                         int userId=context.UserInfo.Max<UserInfo>(item => item.UserID); 
                         return userId; 
                 } 
         } 
 }

 [System.Data.Linq.Mapping.DatabaseAttribute(Name="AspNetStudy")] 
 public partial class UserInfoDataClassesDataContext : System.Data.Linq.DataContext 
 { 
 //.....省略其它代码 
 }

 using System; 
 using System.Collections.Generic; 
 using System.Linq; 
 using System.Text; 
 using NUnit.Framework; 
 using LinqDemo; 
    
 namespace NUnitTest 
 { 
         [TestFixture] 
         public class LinqTest 
         { 
                 private LinqCRUD instance = null; 
                 [SetUp] 
                 public void Initialize() 
                 { 
                         instance = new LinqCRUD(); 
                 } 
                 [Test] 
                 /// <summary> 
                 /// 统计用户总数 
                 /// </summary> 
                 /// <returns></returns> 
                 public void Count() 
                 { 
                         Assert.Greater(instance.Count(), 0); 
                 } 
                 [Test] 
                 /// <summary> 
                 /// 创建用户 
                 /// </summary> 
                 /// <param name="info">用户实体</param> 
                 /// <returns></returns> 
                 public void Create() 
                 { 
                         UserInfo info = new UserInfo() 
                         { 
                                 Age = 12, 
                                 Email = "[email protected]", 
                                 Mobile = "13812345678", 
                                 Phone = "01012345678", 
                                 RealName = "测试" + DateTime.Now.Millisecond.ToString(), 
                                 Sex = true, 
                                 UserName = "zhoufoxcn" + DateTime.Now.Millisecond.ToString() 
                         }; 
                         instance.Create(info); 
                 } 
                 [Test] 
                 /// <summary> 
                 /// 读取用户信息 
                 /// </summary> 
                 /// <param name="userId">用户编号</param> 
                 /// <returns></returns> 
                 public void Read() 
                 { 
                         UserInfo info = instance.Read(1); 
                         Assert.NotNull(info); 
                 } 
                 [Test] 
                 /// <summary> 
                 /// 更新用户信息 
                 /// </summary> 
                 /// <param name="info">用户实体</param> 
                 /// <returns></returns> 
                 public void Update() 
                 { 
                         UserInfo info = instance.Read(1); 
                         info.RealName = "测试" + DateTime.Now.Millisecond.ToString(); 
                         instance.Update(info); 
                 } 
                 [Test] 
                 /// <summary> 
                 /// 删除用户 
                 /// </summary> 
                 /// <param name="userId">用户编号</param> 
                 /// <returns></returns> 
                 public void DeleteByID() 
                 { 
                         int userId = instance.GetMaxUserId(); 
                         instance.Delete(userId); 
                 } 
    
                 [Test] 
                 /// <summary> 
                 /// 删除用户 
                 /// </summary> 
                 /// <param name="userId">用户实体</param> 
                 /// <returns></returns> 
                 public void Delete() 
                 { 
                         int userId = instance.GetMaxUserId(); 
                         UserInfo info = instance.Read(userId); 
                         //Console.WriteLine("MaxUserId=" + userId); 
                         instance.Delete(info); 
                 } 
         } 
 }