一、复习配置文件、SqlHelper,自己重新写一遍。
二、数据库中的Null问题
数据库中的Null为不知道。
1、给数据库中写入Null数据
需求:如果没有输入姓名和年龄,要在数据库中显示NULL,而不是“”,或者0,怎么实现?
把可空的控件定义一个object对象,在对控件进行判断,如果未输入值,则该对象=DBNull.Value, 如输入值,则等于值
然后将此对象传值给数据库执行语句
object objName, objAge;
//通过判断,如果为空,则给数据库输入Dbnull数据
if (txtName.Text.Length <= 0)
objName = DBNull.Value;
else
objName = txtName.Text;
if (txtAge.Text.Length <= 0)
objAge = DBNull.Value;
else
objAge = txtAge.Text;
string sql = "insert T_Person(Name,Age,Height) values(@name,@age,@height)";
SqlHelper.ExecuteNonQuery(sql,
new SqlParameter("@name", objName),
new SqlParameter("@age", objAge),
new SqlParameter("@height", txtHeight.Text));
2、从数据库中读取Null数据
如果写成普通那样,程序会报错:
string sql = "select * from T_Person where Id=1";
DataTable dt = SqlHelper.SqlDataTable(sql);
DataRow row = dt.Rows[0];
string name = (string)row["Name"];
int age = (int)row["Age"];
int height = (int)row["Height"];
因为row["Name"]为DBNull.Value。无法给string类型赋值
所以我们要先判断一下读到的值,然后依条件给name赋值
//string name = (string)row["Name"];
string name = null;
if (row["Name"] != DBNull.Value)
name = (string)row["Name"];
//int age = (int)row["Age"];
int? age = null;
if(row["Age"] != DBNull.Value)
age = (int)row["Age"];
如果数据比较多,可以考虑将其封装成一个方法:
/// <summary>
/// 将要传入数据库的空值转成DBnull.Value
/// </summary>
/// <param name="value">要传入的值</param>
/// <returns>返回对象,如值为空则转为DBnull,不为空返回原值</returns>
private static object ToDBValue(object value)
{
if (value == null)
return DBNull.Value;
else
return value;
}
三、三层架构:
之前学的都是直接在界面(UI)中写SQL,对于大项目而言,这样做很难维护,而且复用性不强,三层架构是企业开发中常用的设计模式
概念:把数据库访问、业务逻辑、界面 分离成三层
初学者学习三层架构会比较难,可以先学习精简的三层架构,只用DAL(Data Access Layer)层,把数据库访问封装到DAL中,UI(界面)调用
DAL,原则是UI不出现SQL.。后面再学习真正的三层架构和代码生成器。
下面开始三层架构:
1、中间层Model:
新建一个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; }
2、数据库访问层:DAL
在项目中新建一个DAL文件夹
1)在DAL文件夹中新建一个SqlHelper类
除了之前写好的SqlHelper内容,新增两个封装的方法:ToDBValue和FromDBValue,用于程序数据null和数据库数据DBNull相互转换
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;
}
2)在DAL文件夹中新建一个CustomerDAL类,里面包含需要操作数据库的各种方法:
GetById、Update、DeleteById、GetAll、Insert、GetPageData(分页数据)
先封装一个会多次运用的方法:ToCustomer
//封装函数,将数据库中的数据读取为类
public static Customer ToCustomer(DataRow row)
{
Customer ct = new Customer();
ct.Id = (long)row["Id"];
ct.Name = (string)row["Name"];
ct.Birthday = (DateTime?)SqlHelper.FromDBValue(row["Birthday"]);
ct.Address = (string)row["Address"];
ct.TelNum = (string)row["TelNum"];
ct.CustLevel = (int)row["CustLevel"];
return ct;
}
开始添加数据操作的方法:
GetById:
public Customer GetById(long id)
{
string sql = "select * from T_Customer where Id=@id";
DataTable dt = SqlHelper.SqlDataTable(sql,
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];
//Customer ct = new Customer();
//ct.Id = id;
//ct.Name = (string)row["Name"];
//ct.Birthday = (DateTime?)SqlHelper.FromDBValue(row["Birthday"]);
//ct.Address = (string)row["Address"];
//ct.TelNum = (string)row["TelNum"];
//ct.CustLevel = (int)row["CustLevel"];
//return ct;
//以上代码被封装在ToCustomer方法中
return ToCustomer(row);
}
}
Update:
public void Update(Customer ct)
{
string sql = @"UPDATE [T_Customer] SET [Name] = @Name,[Birthday] = @Birthday
,[Address] = @Address,[TelNum] = @TelNum,[CustLevel] = @CustLevel WHERE Id=@id ";
SqlHelper.ExecuteNonQuery(sql,
new SqlParameter("@Name", ct.Name),
new SqlParameter("@Birthday", SqlHelper.ToDBValue(ct.Birthday)),
new SqlParameter("@Address", ct.Address),
new SqlParameter("@TelNum", ct.TelNum),
new SqlParameter("@CustLevel", ct.CustLevel),
new SqlParameter("@id",ct.Id));
}
DeleteById:
public void DeleteById(long id)
{
string sql = "delete from T_Customer where Id=@id";
SqlHelper.ExecuteNonQuery(sql,
new SqlParameter("@id", id));
}
GetAll:
public List<Customer> GetAll()
{
List<Customer> listCt = new List<Customer>();
DataTable dt = SqlHelper.SqlDataTable("select * from T_Customer");
DataRowCollection rows = dt.Rows;
foreach (DataRow row in rows)
{
//Customer ct = new Customer();
//ct.Id = (long)row["Id"];
//ct.Name = (string)row["Name"];
//ct.Birthday = (DateTime?)SqlHelper.FromDBValue(row["Birthday"]);
//ct.Address = (string)row["Address"];
//ct.TelNum = (string)row["TelNum"];
//ct.CustLevel = (int)row["CustLevel"];
//以上代码被封装到ToCustomer中
listCt.Add(ToCustomer(row));
}
return listCt;
}
GetAll:
public void Insert(Customer ct)
{
string sql = "insert into T_Customer(Name,Birthday,Address,TelNum,CustLevel) values(@name,@birthday,@address,@telNum,@custLevel)";
SqlHelper.ExecuteNonQuery(sql,
new SqlParameter("@name", ct.Name),
new SqlParameter("@birthday", SqlHelper.ToDBValue(ct.Birthday)),
new SqlParameter("@address", ct.Address),
new SqlParameter("@telNum", ct.TelNum),
new SqlParameter("@custLevel", ct.CustLevel));
}