三层架构(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; } } }