使用linq进行增删改查操作例子

 public class CustomerModel
    {
//获取web.config中的数据库连接
        private static string con = ConfigurationManager.AppSettings["ConnectionString"];
        private DataClassDataContext dc = new DataClassDataContext(con);
        /// <summary>
        /// 分页查询客户信息
        /// </summary>
        /// <param name="customer">客户代码</param>
        /// <param name="PageSize">每页显示数据条数</param>
        /// <param name="PageIndex">第几页</param>
        /// <param name="id">客户id</param>
        /// <returns></returns>
        public string QueryCustomer(string customer,int PageSize,int PageIndex,int id)
        {
            Table<Customers> TBcustomer = dc.GetTable<Customers>();
            var query = from a in TBcustomer
                                         select
                                             new 
                                             {
                                                 ID = a.ID,
                                                 Customer = a.Customer,
                                                 Name = a.Name,
                                                 EmployeeID = a.EmployeeID,
                                                 EmployeeName = a.EmployeeName,
                                                 Region = a.Region,
                                                 Country = a.Country,
                                                 BU = a.BU,
                                                 Tiers = a.Tiers,
                                                 NewGroup = a.NewGroup
                                             };
            if (id == 0)
            {
                query=query.Where(c => c.Customer.Contains(customer));
                return JsonHelper.ObjectToJson<Customers>(dc.ExecuteQuery<Customers>(query.Skip((PageIndex - 1) * PageSize).Take(PageSize * 2)));
            }
            else
            {
                query=query.Where(c => c.ID==id);
                return JsonHelper.JsonSerializer<Customers>(dc.ExecuteQuery<Customers>(query.Skip((PageIndex - 1) * PageSize).Take(PageSize * 2))[0]);
            }
            
        }

        /// <summary>
        /// 查询总记录条数
        /// </summary>
        /// <param name="customer">客户代码</param>
        /// <returns></returns>
        public string QueryCustomerCount(string customer)
        {
            Table<Customers> TBcustomer = dc.GetTable<Customers>();
            var query = from a in TBcustomer
                        where a.Customer.Contains(customer)
                        select 1;
            return query.Count().ToString();
                      
        }

        /// <summary>
        /// 删除客户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string DeleteCustomer(int id)
        {
            try
            {
                Table<Customers> TBcustomer = dc.GetTable<Customers>();
                var del = TBcustomer.SingleOrDefault(c => c.ID == id);
                TBcustomer.DeleteOnSubmit(del);
                dc.SubmitChanges();
                return "1";
            }
            catch (Exception)
            {

                return "0";
            }
        }

        /// <summary>
        /// 查询客户代码是否存在
        /// </summary>
        /// <param name="customer">客户代码</param>
        /// <param name="id">客户id</param>
        /// <returns></returns>
        public string ExistCustomer(string customer,int id)
        {
            Table<Customers> TBcustomer = dc.GetTable<Customers>();
            var query = from a in TBcustomer
                        where a.Customer==customer && a.ID!=id 
                        select 1;
            return query.Count().ToString();

        }

        //新增客户信息
        public string AddCustomer(string customer)
        {
            try
            {
                Customers cs = JsonHelper.JsonDeserialize<Customers>(customer);
                Table<Customers> TBcustomer = dc.GetTable<Customers>();
                TBcustomer.InsertOnSubmit(cs);
                dc.SubmitChanges();
                return "1";
            }
            catch (Exception)
            {

                return "0";
            }
        }

        public string EditCustomer(string customer)
        {
            try
            {
                Customers cs = JsonHelper.JsonDeserialize<Customers>(customer);
                Table<Customers> TBcustomer = dc.GetTable<Customers>();
                Customers b = TBcustomer.First(c => c.ID == cs.ID);
                b.Customer = cs.Customer;
                b.Name = cs.Name;
                b.EmployeeID = cs.EmployeeID;
                b.EmployeeName = cs.EmployeeName;
                b.Region = cs.Region;
                b.Country = cs.Country;
                b.BU = cs.BU;
                b.Tiers = cs.Tiers;
                b.NewGroup = cs.NewGroup;
                dc.SubmitChanges();
                return "1";
            }
            catch (Exception)
            {

                return "0";
            }

            
        }
    }

实体类Customers.cs代码如下：

[Table(Name = "你的数据库表名")]
    public class Customers
    {
        [Column(IsPrimaryKey = true,IsDbGenerated=true)]
        public int ID { get; set; }
        [Column]
        public string Customer { get; set; }
        [Column]
        public string Name { get; set; }
        [Column]
        public string EmployeeID { get; set; }
        [Column]
        public string EmployeeName { get; set; }
        [Column]
        public string Region { get; set; }
        [Column]
        public string Country { get; set; }
        [Column]
        public string BU { get; set; }
        [Column]
        public string Tiers { get; set; }
        [Column]
        public string NewGroup { get; set; }
    }

DataContextExtensions.cs代码如下：

public static class DataContextExtensions
    {
        public static List<T> ExecuteQuery<T>(this DataContext dataContext, IQueryable query)
        {
            DbCommand command = dataContext.GetCommand(query);
            dataContext.OpenConnection();

            using (DbDataReader reader = command.ExecuteReader())
            {
                return dataContext.Translate<T>(reader).ToList();
            }
        }

        private static void OpenConnection(this DataContext dataContext)
        {
            if (dataContext.Connection.State == ConnectionState.Closed)
            {
                dataContext.Connection.Open();
            }
        }
    }

其中类jsonhelper的代码见：http://blog.163.com/chen_hui/blog/static/22764309520160132275619/