三层2

namespace ExecuteReader.model
{
   public  class Customer
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public DateTime? Birthday { get; set; }
        public string Address { get; set; }
        public string TelNum { get; set; }
        public int CustLevel { get; set; }

    }
}

namespace ExecuteReader
{
    class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["adConnStr"].ConnectionString;

      
        public static int  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);
                    return cmd.ExecuteNonQuery();
                }

            }
        }

        public static object ExecuteScalary(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);
                    return cmd.ExecuteScalar();
                }

            }
        }
        //只用来执行查询结果比较少的sql
        public static DataTable ExecuteDataTables(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 dataset = new DataSet();
                    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;
        }
    }
}

namespace ExecuteReader.DAL
{
    public  class CustomerDAL
    {
        //根据Id获取 GetById  Update  DeleteById GetAll  GetPageData(分页数据)
        // Insert (插入新数据)


        //把公共代码封装到一个方法中，避免重复性的代码，提高代码复用性
        private static Customer ToCustomer(DataRow row)
        {
            Customer cust = new Customer();
            cust.Id = (long)row["Id"];
            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 static Customer GetById(long id )
        {
            DataTable dt = SqlHelper.ExecuteDataTables("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];
               // Customer cust = new Customer();
                //cust.Id = (long)row["Id"];
                //cust.Birthday = (DateTime ?) SqlHelper .FromDBValue ( row["Birthday"]);
                //cust.Address = (string )row["Address"];

                //cust.CustLevel = (int )row["CustLevel"];
                //cust.TelNum = (string )row["TelNum"];
                //return cust;
               return  ToCustomer(row);
            }
         
        }
     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 [dbo].[T_Customer]
           ([Name]
           ,[Birthday]
           ,[Address]
           ,[TelNum]
           ,[CustLevel])
     VALUES
           (@Name,@Birthday,@Address,@TelNum,@CustLevel)",
           new SqlParameter ("@Name", SqlHelper .ToDbValue ( customer.Name )),
           new SqlParameter("@Birthday", customer.Birthday),
           new SqlParameter("@Address", customer.Address),
           new SqlParameter("@TelNum", customer.TelNum),
           new SqlParameter("@CustLevel", customer.CustLevel));
     }
        public void Update (Customer costomer)
     {
         SqlHelper.ExecuteNonQuery(@"Update [T_Costomer]
                    set [Name] = @Name 
                        ,[Birthday] = @Birthday
                        ,[Address] = @Address
                        ,[TelNum] = @TelNum
                        ,[CustLevel] = @CustLevel
                        where Id = @ Id ",
              new SqlParameter("@Name", costomer.Name),
              new SqlParameter("@Birthday", SqlHelper .ToDbValue ( costomer.Birthday)),
              new SqlParameter("@Address", costomer.Address),
              new SqlParameter("@TelNum", costomer.TelNum),
              new SqlParameter("@CustLevel", costomer.CustLevel),
               new SqlParameter("@Id", costomer.Id));
     }
        public Customer [] GetAll()
        {
            //DRY Don't Repeat yourself!
            DataTable table = SqlHelper.ExecuteDataTables("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];
                customers[i] = ToCustomer(row);
            }
            return customers;
        }

    }
}