深入宠物店PetShop-SQLServerDAL数据访问与Sample-Duwamish比较
先看看PetShop内DALFactory内数据工厂与数据库交互的根源-SQLServerDAL,采用的是SqlHelper.cs这一类文件,相信大家对这个都不会感到陌生了.在做PetShop的数据库访问之前,首先对关系进行对象建模,即我们通常所说到的O/R模型,在Modle层中对[Account]表中关于用户地址[Address]进行建模,[当然,这是微软开发小组对于对象粒度的划分,你也可以扩展到抽取Account部分的Balance等等进行扩展],这种思路对于今后的数据库扩展发生的连带影响可以降低到最少.And I like it^^
namespace PetShop.Model {
/// <summary>
/// Business entity used to model addresses
/// </summary>
[Serializable]
public class AddressInfo {
// Internal member variables
private string _firstName;
private string _lastName;
private string _address1;
private string _address2;
private string _city;
private string _state;
private string _zip;
private string _country;
private string _phone;
public AddressInfo(string firstName, string lastName, string address1, string address2, string city, string state, string zip, string country, string phone) {
this._firstName = firstName;
this._lastName = lastName;
this._address1 = address1;
this._address2 = address2;
this._city = city;
this._state = state;
this._zip = zip;
this._country = country;
this._phone = phone;
}
}
}
以上,我省略了所有属性,只把主干模型抽取出来讨论
在底层的IDAL Interface Data Access Logic Layer我们可以看到用到的SQLCommand.Text以及采用的是DataReader以类似指针的方法返回结果,只满足所需的最小数据量,而不是用整个DataSet作为数据传输媒介,
将原先所做的模型进行字段对应,从而在以后如果数据库扩展字段,只需要调整Model以及访问函数一次即可,其它BizLogic则正常运转.看看代码:
namespace PetShop.SQLServerDAL {
/// <summary>
/// Summary description for AccountDALC.
/// </summary>
public class Account : IAccount{
private const string SQL_SELECT_ACCOUNT = "SELECT Account.Email, Account.FirstName, Account.LastName, Account.Addr1, Account.Addr2, Account.City, Account.State, Account.Zip, Account.Country, Account.Phone, Profile.LangPref, Profile.FavCategory, Profile.MyListOpt, Profile.BannerOpt FROM Account INNER JOIN Profile ON Account.UserId = Profile.UserId INNER JOIN SignOn ON Account.UserId = SignOn.UserName WHERE SignOn.UserName = @UserId AND SignOn.Password = @Password";
/// <summary>
/// 对返回结果集Mapping到Address模型 /// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public AddressInfo GetAddress(string userId) {
AddressInfo address= null;
SqlParameter[] addressParms = GetAddressParameters();
addressParms[0].Value = userId;
using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_ADDRESS, addressParms)) {
if (rdr.Read()) {
address = new AddressInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8));
}
}
return address;
}
我省略了Connection那些字段.在Ado.Net中,我们都知道DataSet是断开链接的,是放在系统内存中的,PetShop在打开数据连接之后，并没有马上读取数据，而是将DataReader传递给另外的对象来执行数据读的操作，然后才关闭连接。这样，数据连接的时间加长了，而数据库连接是一项非常宝贵的服务器资源，相比之下，Dawamish在连接数据库之后马上进行填充，然后迅速释放掉数据库连接的方式更加有利于大量用户的并发访问.因此个人认为对于Master这种配置表还是应该一次性读出,毕竟在一般应用中,客户Account表一般不超过10000条,作为访问量高的数据,只需要定时刷新内存,然后数据访问只需用Rowfilter进行检索即可,避开对链接的频繁交互,在实际应用中会有更好的效果.而对于订单Orders这种还是对数据库进行RealTime访问比较合适.毕竟对于企业级开发来说,程序的健壮性,稳定性,性能是首要考虑的.
我们再来看看Duwamish得数据访问,前期工作是对表DataTable进行对应,以避免下次重复写那么多遍字段名,烦都烦死了,那可是体力活,我上次维护的110 个字段采用的那种架构真是恐怖,写好模块,我都背出来110个字段了,TMD
public class Customers : IDisposable
{
//
// DataSetCommand object
//
private SqlDataAdapter dsCommand;
//
// Stored procedure parameter names
//
private const String PKID_PARM = "@PKId";
private const String EMAIL_PARM = "@Email";
private const String NAME_PARM = "@Name";
private const String ADDRESS_PARM = "@Address";
private const String COUNTRY_PARM = "@Country";
private const String PASSWORD_PARM = "@Password";
private const String PHONE_PARM = "@PhoneNumber";
private const String FAX_PARM = "@Fax";
/// <summary>
/// Constructor for Customers.
/// <remarks>Initialize the internal DataSetCommand object.</remarks>
/// </summary>
public Customers()
{
//
// Create the DataSetCommand
//
dsCommand = new SqlDataAdapter();
dsCommand.TableMappings.Add("Table", CustomerData.CUSTOMERS_TABLE);
}
} // class Customers
} // namespace Duwamish7.DataAccess
public CustomerData GetCustomerByEmail(String emailAddress, String password)
{
//
// Check preconditions
//
ApplicationAssert.CheckCondition(emailAddress != String.Empty, "Email address is required",
ApplicationAssert.LineNumber);
ApplicationAssert.CheckCondition(password != String.Empty, "Password is required",
ApplicationAssert.LineNumber);
//
// Get the customer dataSet
//
CustomerData dataSet;
using (DataAccess.Customers customersDataAccess = new DataAccess.Customers())
{
dataSet = customersDataAccess.LoadCustomerByEmail(emailAddress);
}
//
// Verify the customer's password
//
DataRowCollection rows = dataSet.Tables[CustomerData.CUSTOMERS_TABLE].Rows;
if ( ( rows.Count == 1 ) && rows[0][CustomerData.PASSWORD_FIELD].Equals(password) )
{
return dataSet;
}
else
{
return null;
}
}
不过现在开发有了很多O/R Mapping工具,很多映射代码都可以交给他们处理,毕竟企业级开发要做的好,卖点还是在于你的业务逻辑强悍,能确实帮他们分析问题解决问题,并在知识库中提供参考依据.用DataSet 还有一个好处就是在于返回DataSet或者DataTable,可以用.Net Framework自带的DataSet.ReadXML,DataSet.GetXML进行XML转换操作,不过据说开销比较大,对于XML这个跨平台的媒介来说,基本上主要用于订单,财务报表和配置部分较多,一般应用中不会引起很大开销.
对了,我还有一个菜鸟级的问题,我怎么能贴彩色的源码啊,这个Blogger自带的Style->Code方法发布出来的代码太难看了,都看不清楚关键字,希望有那位大侠告知.Thanks ^^
