三层架构中DAL的好代码

SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace WpfApplication1.DAL
{
    static class SqlHelper
    {
        public static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

        public static int ExecuteNonQuery(string sql, 
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(string sql,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }

        public static DataTable ExecuteDataTable(string sql,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters);
                DataSet dataset = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dataset);
                return dataset.Tables[0];
            }
        }

        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }
    }
}

 

CustomerDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WpfApplication1.Model;
using System.Data.SqlClient;
using System.Data;

namespace WpfApplication1.DAL
{
    public class CustomerDAL
    {
       //根据Id获取GetById、Update、DeleteById、GetAll、GetPagedData（分页数据）
        //Insert（插入新数据）

        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        private Customer ToCustomer(DataRow row)
        {
            Customer cust = new Customer();
            cust.Id = (long)row["Id"];
            cust.Name = (string)row["Name"];
            cust.BirthDay =
                (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
            cust.Address = (string)row["Address"];
            cust.CustLevel = (int)row["CustLevel"];
            cust.TelNum = (string)row["TelNum"];
            return cust;
        }

        public Customer GetById(long id)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Id=@Id",
                new SqlParameter("@Id", id));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0];
                return ToCustomer(row);
                //Customer cust = new Customer();
                //cust.Id = (long)row["Id"];
                //cust.Name = (string)row["Name"];
                //cust.BirthDay = 
                //    (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
                //cust.Address = (string)row["Address"];
                //cust.CustLevel = (int)row["CustLevel"];
                //cust.TelNum = (string)row["TelNum"];
                //return cust;
            }
        }

        public void DeleteById(long id)
        {
            SqlHelper.ExecuteNonQuery("delete from T_Customer where Id=@Id",
                new SqlParameter("@Id", id));
        }

        public void Insert(Customer customer)
        {
            SqlHelper.ExecuteNonQuery(@"INSERT INTO [T_Customer]
                       ([Name]
                       ,[BirthDay]
                       ,[Address]
                       ,[TelNum]
                       ,[CustLevel])
                 VALUES
                       (@Name, @BirthDay,@Address,@TelNum,@CustLevel)",
                new SqlParameter("@Name",customer.Name),
                new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay)),
                new SqlParameter("@Address", customer.Address),
                new SqlParameter("@TelNum", customer.TelNum),
                new SqlParameter("@CustLevel", customer.CustLevel));
        }

        public void Update(Customer customer)
        {
            SqlHelper.ExecuteNonQuery(@"UPDATE [T_Customer]
                       SET [Name] = @Name
                          ,[BirthDay] = @BirthDay
                          ,[Address] = @Address
                          ,[TelNum] = @TelNum
                          ,[CustLevel] = @CustLevel
                     WHERE Id=@Id",
                                  new SqlParameter("@Name",customer.Name),
                                  new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay)),
                                  new SqlParameter("@Address", customer.Address),
                                  new SqlParameter("@TelNum", customer.TelNum),
                                  new SqlParameter("@CustLevel", customer.CustLevel)
                                  ,new SqlParameter("@Id",customer.Id));
        }

        public Customer[] GetAll()
        {
            //DRY：Don't Repeat yourself! 不要复制代码！
           DataTable table =  SqlHelper.ExecuteDataTable("select * from T_Customer");
           Customer[] customers = new Customer[table.Rows.Count];
           for (int i = 0; i < table.Rows.Count; i++)
           {
               DataRow row = table.Rows[i];
               //Customer customer = new Customer();
               //customer.Id = (long)row["Id"];
               //customer.Name = (string)row["Name"];
               //customer.BirthDay = (DateTime)SqlHelper.FromDbValue(row["BirthDay"]);
               //customer.CustLevel = (int)row["CustLevel"];
               //customer.TelNum = (string)row["TelNum"];
               //customer.Address = (string)row["Address"];

               //customers[i] = customer;
               customers[i] = ToCustomer(row);
           }
           return customers;
        }
    }
}

 

转载于:https://www.cnblogs.com/Harry369/archive/2013/06/13/3133630.html