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