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; } } }