三层架构（1）-17

第一步：设计T_Customer数据表，如下：

第二步：新建一个文件夹命名为：Model。新建一个类，为Customer.cs，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExecuteReader执行查询.Model
{
    public class Customer
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public DateTime? BirthDay { get; set; }
        public string TelNum { get; set; }
        public int CustLevel { get; set; }

    }

}

第三步：新建一个文件夹ADL，新建一个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 ExecuteReader执行查询.DAL
{
    static class SqlHelper
    {
        //每个实例都需要查看是否修改连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["dbLoginConnStr"].ConnectionString;
        //封装方法的原则是：把不变的放到方法里，变化的方法参数中
        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static void ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    return cmd.ExecuteScalar();
                }
            }
        }
        public static DataSet ExecuteDataSet(string sql)
        {

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

         public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return  null;
            }
            else
            {
                return value;
            }
        }
         public static object ToDbValue(object value)//private是类内部的方法，现在需要变成公用的，所以修改private为public
         {
             if (value == null)
             {
                 return DBNull.Value;
             }
             else
             {
                 return value;
             }

         }
    }
}

第四步，新建一个CustomerDAL.cs类，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExecuteReader执行查询.Model;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteReader执行查询.DAL
{
    class CustomerDAL
    {//根据Id获取GetById(),DeleteById(),Update(),GetAll(),GetPageDate(分页数据),Insert(插入新数据)
      /*  public static GetById()不是一定要声明为static，若没有static在使用时候需要先new一个对象。在后面讲到工厂时候比较好理解。
        private static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return  null;
            }
            else
            {
                return value;
            }
        }
        private static object ToDbValue(object value)//private是类内部的方法
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }

        }//因为ToDbValue(object value)FromDbValue(object value)是公用的方法，所以可以把他们放在SqlHelper中，供其他的类使用。
        
       */
        public Customer ToCustomer(DataRow row)//把公共代码封装成为一个方法，提高代码的可用性，避免复用性。
        {
            Customer customer = new Customer();
            customer.Id = (long)row["Id"];
            customer.Name = (string)row["Name"];
            customer.TelNum = (string)row["TelNum"];
            customer.Address = (string)row["Address"];
            customer.CustLevel = (int)row["CustLevel"];
            customer.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);//数据库中BirthDay的值可能为DBNull.Value所以需要使用判断语句或者FromDBNull()方法
            return customer;
        }

        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("Id重复");
            }
            else
            {
                DataRow row = dt.Rows[0];
                return ToCustomer(row);
                //Customer customer = new Customer();
                //customer.Id=(long)row["Id"];
                //customer.Name = (string)row["Name"];
                //customer.TelNum=(string)row["TelNum"];
                //customer.Address=(string)row["Address"];
                //customer.CustLevel=(int)row["CustLevel"];
                //customer.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);//数据库中BirthDay的值可能为DBNull.Value所以需要使用判断语句或者FromDBNull()方法
                //return customer;
            }
        }
        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,[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));
        }
        public Customer[] GetAll()//public List<Customer> GetAll()返回一个list<>和一个Customer[]数组均可以。
        {
            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];//此处为 与GetById(long id)中的部分代码相同，所以封装一个为ToCustomer(DataRow row)
                customers[i] = ToCustomer(row);
                //Customer customer = new Customer();
                //customer.Id = (long)row["Id"];
                //customer.Name = (string)row["Name"];
                //customer.TelNum = (string)row["TelNum"];
                //customer.Address = (string)row["Address"];
                //customer.CustLevel = (int)row["CustLevel"];
                //customer.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
                //customers[i] = customer;

            }

            return customers;
        }

    }
}