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

qq70191

http://blog.51cto.com/images/editer/InBlock.gif using System;

http://blog.51cto.com/images/editer/InBlock.gif using System.Collections.Generic;

http://blog.51cto.com/images/editer/InBlock.gif using System.Linq;

http://blog.51cto.com/images/editer/InBlock.gif using System.Text;

http://blog.51cto.com/images/editer/InBlock.gif using System.Data.SqlClient;

http://blog.51cto.com/images/editer/InBlock.gif using System.Data.Linq;

http://blog.51cto.com/images/editer/InBlock.gif using System.Configuration;

http://blog.51cto.com/images/editer/InBlock.gif

http://blog.51cto.com/images/editer/InBlock.gif namespace LinqDemo

http://blog.51cto.com/images/editer/InBlock.gif {

http://blog.51cto.com/images/editer/InBlock.gif         ///

http://blog.51cto.com/images/editer/InBlock.gif         /// 说明：这个类是为了演示.NET中的Linq to SQL的用法

http://blog.51cto.com/images/editer/InBlock.gif         /// 作者：周公（周金桥）

http://blog.51cto.com/images/editer/InBlock.gif         /// 日期：2010-03-01

http://blog.51cto.com/images/editer/InBlock.gif         ///

http://blog.51cto.com/images/editer/InBlock.gif         public>

http://blog.51cto.com/images/editer/InBlock.gif         {

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 统计用户总数

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public int Count()

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif方法一#region 方法一

http://blog.51cto.com/images/editer/InBlock.gif                         //使用SqlConnection来实例化DataContext对象

http://blog.51cto.com/images/editer/InBlock.gif                         SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LinqDemo.Properties.Settings.AspNetStudyConnectionString"].ConnectionString);

http://blog.51cto.com/images/editer/InBlock.gif                         DataContext context = new DataContext(connection);

http://blog.51cto.com/images/editer/InBlock.gif                         IEnumerable collection = context.ExecuteQuery("select count(1) from UserInfo");

http://blog.51cto.com/images/editer/InBlock.gif                         int count = collection.ElementAt(0);

http://blog.51cto.com/images/editer/InBlock.gif                         return count;

http://blog.51cto.com/images/editer/InBlock.gif                         #endregion

http://blog.51cto.com/images/editer/InBlock.gif

http://blog.51cto.com/images/editer/InBlock.gif方法二#region 方法二

http://blog.51cto.com/images/editer/InBlock.gif                         //UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         ////return context.UserInfo.Count(item => item.Age > 23);//带条件统计

http://blog.51cto.com/images/editer/InBlock.gif                         //return context.UserInfo.Count();

http://blog.51cto.com/images/editer/InBlock.gif                         #endregion

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 创建用户

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 用户实体

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public void Create(UserInfo info)

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         context.UserInfo.InsertOnSubmit(info);

http://blog.51cto.com/images/editer/InBlock.gif                         context.SubmitChanges();

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 读取用户信息

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 用户编号

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public UserInfo Read(int userId)

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         context.Log = Console.Out;

http://blog.51cto.com/images/editer/InBlock.gif                         var query = from item in context.UserInfo

http://blog.51cto.com/images/editer/InBlock.gif                                                 where item.UserID == userId

http://blog.51cto.com/images/editer/InBlock.gif                                                 select item;

http://blog.51cto.com/images/editer/InBlock.gif                         return query.First();

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 更新用户信息

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 用户实体

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public void Update(UserInfo info)

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfo ui = context.UserInfo.First(item => item.UserID == info.UserID);

http://blog.51cto.com/images/editer/InBlock.gif                         ui.Age = info.Age;

http://blog.51cto.com/images/editer/InBlock.gif                         ui.Email = info.Email;

http://blog.51cto.com/images/editer/InBlock.gif                         ui.Mobile = info.Mobile;

http://blog.51cto.com/images/editer/InBlock.gif                         ui.Phone = info.Phone;

http://blog.51cto.com/images/editer/InBlock.gif                         ui.RealName = info.RealName;

http://blog.51cto.com/images/editer/InBlock.gif                         ui.Sex = info.Sex;

http://blog.51cto.com/images/editer/InBlock.gif                         ui.UserName = info.UserName;

http://blog.51cto.com/images/editer/InBlock.gif                         context.SubmitChanges();

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 删除用户

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 用户编号

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public void Delete(int userId)

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif方法一#region 方法一

http://blog.51cto.com/images/editer/InBlock.gif                         //UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         //context.ExecuteCommand("delete from UserInfo where UserId=" + userId);

http://blog.51cto.com/images/editer/InBlock.gif                         #endregion

http://blog.51cto.com/images/editer/InBlock.gif方法二#region 方法二

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfo ui = context.UserInfo.First(item => item.UserID == userId);

http://blog.51cto.com/images/editer/InBlock.gif                         context.UserInfo.DeleteOnSubmit(ui);

http://blog.51cto.com/images/editer/InBlock.gif                         context.SubmitChanges();

http://blog.51cto.com/images/editer/InBlock.gif                         #endregion

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 删除用户

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 用户实体

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public void Delete(UserInfo info)

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         var userList = from Users

http://blog.51cto.com/images/editer/InBlock.gif                                                            in context.UserInfo

http://blog.51cto.com/images/editer/InBlock.gif                                                            where Users.UserID == info.UserID

http://blog.51cto.com/images/editer/InBlock.gif                                                            select Users;

http://blog.51cto.com/images/editer/InBlock.gif                         foreach (var user in userList)

http://blog.51cto.com/images/editer/InBlock.gif                         {

http://blog.51cto.com/images/editer/InBlock.gif                                 context.UserInfo.DeleteOnSubmit(user);

http://blog.51cto.com/images/editer/InBlock.gif                         }

http://blog.51cto.com/images/editer/InBlock.gif                         //context.UserInfo.DeleteOnSubmit(userList.First());

http://blog.51cto.com/images/editer/InBlock.gif                         //注意下面的写法是错误的

http://blog.51cto.com/images/editer/InBlock.gif                        // context.UserInfo.DeleteOnSubmit(info);

http://blog.51cto.com/images/editer/InBlock.gif                         context.SubmitChanges();

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 /// 获取用户表中编号最大的用户

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 ///

http://blog.51cto.com/images/editer/InBlock.gif                 public int GetMaxUserId()

http://blog.51cto.com/images/editer/InBlock.gif                 {

http://blog.51cto.com/images/editer/InBlock.gif                         UserInfoDataClassesDataContext context = new UserInfoDataClassesDataContext();

http://blog.51cto.com/images/editer/InBlock.gif                         int userId=context.UserInfo.Max(item => item.UserID);

http://blog.51cto.com/images/editer/InBlock.gif                         return userId;

http://blog.51cto.com/images/editer/InBlock.gif                 }

http://blog.51cto.com/images/editer/InBlock.gif         }

http://blog.51cto.com/images/editer/InBlock.gif }