创建类PetShop4.0 架构的项目（一） DAL层

最新推荐文章于 2011-12-01 13:16:00 发布

原创最新推荐文章于 2011-12-01 13:16:00 发布 · 1.2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#string #authorization #insert #数据库 #webservice #path

本文介绍PetShop项目的架构设计，包括数据库访问层(DAL)的创建过程。从DBUtility类到Model实体类，再到IDAL接口定义及具体实现，最后通过抽象工厂模式统一管理数据库操作。

部署运行你感兴趣的模型镜像

PetShop是微软公司提供的一个巨大而复杂的玩具。MSDN上说，这个架构可以扩展成N层架构。虽然觉得这话有自诩的成分在其中，但是在看完整个代码后，不得不承认，这个PetShop还真是个复杂的“玩具”。

那就玩玩好了，试着自己创建了一个。这里记录下：

按照习惯，还是从DAL层开始创建。依照以下顺序创建项目：

1、DBUtility

这个是数据库访问的基础，包含对应SqlServer的访问类SqlHelper和对应Oracle的访问类OracleHelper。创建好后，直接把PetShop中的两个类Ctrl+C过来就好。嘿嘿，我是很懒的。要注意的是这里使用了ConfigurationManager类来读取配置文件中的连接字符串

//数据库连接字符串

public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString;

public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;

public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;

public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;

要在配置文件中提供相应的配置信息才能够运行。顺便说下，PetShop中的数据库分为4个：
MSPetShop4——PetShop信息库，存储商品信息，供应商信息等基础信息
MSPetShop4Orders——订单库，存储订单信息
MSPetShop4Profiles——用户信息库，存储用户信息、购物车信息等
MSPetShop4Services——服务信息库，存储各种应用程序信息，dll路径等。
上面的四个连接，就分别对应这四个数据库

2、Model

这个是系统的实体类，按照自己的业务实体创建实体类吧。一般来说，我们的实体类有可能会用到Soap这样的协议中去，所以支持序列化还是必要的。不复杂，直接在类的头上加上[Serializable]这个特性描述就好。如：

    /// <summary>
    /// 订单业务实体
    /// </summary>
    [Serializable]
    public class OrderInfo {

        // 成员变量
        private int orderId;
        private DateTime date;
        private string userId;
        private CreditCardInfo creditCard;
        private AddressInfo billingAddress;
        private AddressInfo shippingAddress;
        private decimal orderTotal;
        private LineItemInfo[] lineItems;
        private Nullable<int> authorizationNumber;

        /// <summary>
        /// 默认构造方法
        /// WebService 的序列化机制需要
        /// </summary>
        public OrderInfo() { }

        /// <summary>
        /// 带初始值的构造方法
        /// </summary>
        /// <param name="orderId">主键</param>
        /// <param name="date">订货日期</param>
        /// <param name="userId">订货人(编号)</param>
        /// <param name="creditCard">订货信用卡</param>
        /// <param name="billing">订货地址</param>
        /// <param name="shipping">送货地址</param>
        /// <param name="total">订单价值</param>
        /// <param name="line">订单商品(数组)</param>
        /// <param name="authorization">信用卡注册号</param>
        public OrderInfo(int orderId, DateTime date, string userId, CreditCardInfo creditCard, AddressInfo billing, AddressInfo shipping, decimal total, LineItemInfo[] line, Nullable<int> authorization) {
            this.orderId = orderId;
            this.date = date;
            this.userId = userId;
            this.creditCard = creditCard;
            this.billingAddress = billing;
            this.shippingAddress = shipping;
            this.orderTotal = total;
            this.lineItems = line;
            this.authorizationNumber = authorization;
        }

        // 属性
        public int OrderId {
            get { return orderId; }
            set { orderId = value; }
        }

        public DateTime Date {
            get { return date; }
            set { date = value; }
        }

        public string UserId {
            get { return userId; }
            set { userId = value; }
        }

        public CreditCardInfo CreditCard {
            get { return creditCard; }
            set { creditCard = value; }
        }

        public AddressInfo BillingAddress {
            get { return billingAddress; }
            set { billingAddress = value; }
        }

        public AddressInfo ShippingAddress {
            get { return shippingAddress; }
            set { shippingAddress = value; }
        }

        public decimal OrderTotal {
            get { return orderTotal; }
            set { orderTotal = value; }
        }

        public LineItemInfo[] LineItems {
            get { return lineItems; }
            set { lineItems = value; }
        }

        public Nullable<int> AuthorizationNumber {
            get {return authorizationNumber;}
            set {authorizationNumber = value;}
        }
    }

要注意的是需要一个默认构造方法，即使你没有使用它。因为在WebService中如果序列化这个实体，需要这个默认构造

3、IDAL

从这里开始我们要开始添加引用了。这里我们要使用到之前建立的实体类，所以引用Model。
PetShop中使用了抽象工厂模式，所以，现在我们要创建接口了。对应每个Model中的实体创建各自的接口。还是拿Order来说，创建如下的类：

    /// <summary>
    /// DAL中订单对象接口
    /// </summary>
    public interface IOrder {

        /// <summary>
        /// 插入订单
        /// </summary>
        /// <param name="order">订单业务实体</param>
        /// <returns>订单编号</returns>
        void Insert(OrderInfo order);

        /// <summary>
        /// 按订单ID, 获取订单信息
        /// </summary>
        /// <param name="orderId">订单ID</param>
        /// <returns>订单业务实体</returns>
        OrderInfo GetOrder(int orderId);
    }

一般来说，接口中包含的方法，就是在外部要使用的那些方法了，所以这里开始就和业务的逻辑有些相关了。要决定如何使用。如在PetShop中针对商品（Item）类就是如下建立接口的：

    /// <summary>
    /// DAL中商品对象接口
    /// </summary>
    public interface IItem{

        /// <summary>
        /// 按产品ID, 获取商品对象
        /// </summary>
        /// <param name="productId">产品ID</param>
        /// <returns>商品实体类集合接口</returns>
        IList<ItemInfo> GetItemsByProduct(string productId);

        /// <summary>
        /// 按商品ID, 获取商品信息
        /// </summary>
        /// <param name="itemId">商品ID</param>
        /// <returns>商品业务实体</returns>
        ItemInfo GetItem(string itemId);
    }

4、SQLServerDAL/OracleDAL

好了，可以针对数据库创建各自的具体数据库访问和命令执行了。要用到之前的所有项目，所以引用吧。这里需要引用DBUtility、Model、IDAL
针对不同的实体创建类吧，要实现之前针对实体创建的接口才行，还是拿Order来说，如下：
5、DALFactory

public class Order : IOrder {

//静态常量，T-SQL语句，参数名等

private const string SQL_INSERT_ORDER = "Declare @ID int; Declare @ERR int; INSERT INTO Orders VALUES(@UserId, @Date, @ShipAddress1, @ShipAddress2, @ShipCity, @ShipState, @ShipZip, @ShipCountry, @BillAddress1, @BillAddress2, @BillCity, @BillState, @BillZip, @BillCountry, 'UPS', @Total, @BillFirstName, @BillLastName, @ShipFirstName, @ShipLastName, @AuthorizationNumber, 'US_en'); SELECT @ID=@@IDENTITY; INSERT INTO OrderStatus VALUES(@ID, @ID, GetDate(), 'P'); SELECT @ERR=@@ERROR;";

private const string SQL_INSERT_ITEM = "INSERT INTO LineItem VALUES( ";

private const string SQL_SELECT_ORDER = "SELECT o.OrderDate, o.UserId, o.CardType, o.CreditCard, o.ExprDate, o.BillToFirstName, o.BillToLastName, o.BillAddr1, o.BillAddr2, o.BillCity, o.BillState, BillZip, o.BillCountry, o.ShipToFirstName, o.ShipToLastName, o.ShipAddr1, o.ShipAddr2, o.ShipCity, o.ShipState, o.ShipZip, o.ShipCountry, o.TotalPrice, l.ItemId, l.LineNum, l.Quantity, l.UnitPrice FROM Orders as o, lineitem as l WHERE o.OrderId = @OrderId AND o.orderid = l.orderid";

private const string PARM_USER_ID = "@UserId";

private const string PARM_DATE = "@Date";

private const string PARM_SHIP_ADDRESS1 = "@ShipAddress1";

private const string PARM_SHIP_ADDRESS2 = "@ShipAddress2";

private const string PARM_SHIP_CITY = "@ShipCity";

private const string PARM_SHIP_STATE = "@ShipState";

private const string PARM_SHIP_ZIP = "@ShipZip";

private const string PARM_SHIP_COUNTRY = "@ShipCountry";

private const string PARM_BILL_ADDRESS1 = "@BillAddress1";

private const string PARM_BILL_ADDRESS2 = "@BillAddress2";

private const string PARM_BILL_CITY = "@BillCity";

private const string PARM_BILL_STATE = "@BillState";

private const string PARM_BILL_ZIP = "@BillZip";

private const string PARM_BILL_COUNTRY = "@BillCountry";

private const string PARM_TOTAL = "@Total";

private const string PARM_BILL_FIRST_NAME = "@BillFirstName";

private const string PARM_BILL_LAST_NAME = "@BillLastName";

private const string PARM_SHIP_FIRST_NAME = "@ShipFirstName";

private const string PARM_SHIP_LAST_NAME = "@ShipLastName";

private const string PARM_AUTHORIZATION_NUMBER = "@AuthorizationNumber";

private const string PARM_ORDER_ID = "@OrderId";

private const string PARM_LINE_NUMBER = "@LineNumber";

private const string PARM_ITEM_ID = "@ItemId";

private const string PARM_QUANTITY = "@Quantity";

private const string PARM_PRICE = "@Price";

/// <summary>

/// 插入一个订单

/// </summary>

/// <param name="order">OrderInfo订单</param>

public void Insert(OrderInfo order) {

StringBuilder strSQL = new StringBuilder();

//获取每个command的parameter数组

SqlParameter[] orderParms = GetOrderParameters();

SqlCommand cmd = new SqlCommand();

// 初始化参数(parameter)

orderParms[0].Value = order.UserId;

orderParms[1].Value = order.Date;

orderParms[2].Value = order.ShippingAddress.Address1;

orderParms[3].Value = order.ShippingAddress.Address2;

orderParms[4].Value = order.ShippingAddress.City;

orderParms[5].Value = order.ShippingAddress.State;

orderParms[6].Value = order.ShippingAddress.Zip;

orderParms[7].Value = order.ShippingAddress.Country;

orderParms[8].Value = order.BillingAddress.Address1;

orderParms[9].Value = order.BillingAddress.Address2;

orderParms[10].Value = order.BillingAddress.City;

orderParms[11].Value = order.BillingAddress.State;

orderParms[12].Value = order.BillingAddress.Zip;

orderParms[13].Value = order.BillingAddress.Country;

orderParms[14].Value = order.OrderTotal;

orderParms[15].Value = order.BillingAddress.FirstName;

orderParms[16].Value = order.BillingAddress.LastName;

orderParms[17].Value = order.ShippingAddress.FirstName;

orderParms[18].Value = order.ShippingAddress.LastName;

orderParms[19].Value = order.AuthorizationNumber.Value;

foreach (SqlParameter parm in orderParms)

cmd.Parameters.Add(parm);

// 创建数据库连接

using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringOrderDistributedTransaction)) {

// 插入订单状态

strSQL.Append(SQL_INSERT_ORDER);

SqlParameter[] itemParms;

// 循环所购商品项, 插入订单

int i = 0;

foreach (LineItemInfo item in order.LineItems) {

strSQL.Append(SQL_INSERT_ITEM).Append(" @ID").Append(", @LineNumber").Append(i).Append(", @ItemId").Append(i).Append(", @Quantity").Append(i).Append(", @Price").Append(i).Append("); SELECT @ERR=@ERR+@@ERROR;");

//取得缓存的参数(parameter)

itemParms = GetItemParameters(i);

itemParms[0].Value = item.Line;

itemParms[1].Value = item.ItemId;

itemParms[2].Value = item.Quantity;

itemParms[3].Value = item.Price;

//向command中加入参数

foreach (SqlParameter parm in itemParms)

cmd.Parameters.Add(parm);

i++;

}

conn.Open();

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = strSQL.Append("SELECT @ID, @ERR").ToString();

// 读取查询返回, 应该是返回错误数量

using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {

// 读取返回的 @ERR

rdr.Read();

// 如果错误数量不为0则throw一个异常

if (rdr.GetInt32(1) != 0)

throw new ApplicationException("DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED");

}

//清除command中的参数数组

cmd.Parameters.Clear();

}

/// <summary>

/// 从数据库中读取一个订单

/// </summary>

/// <param name="orderId">订单编号</param>

/// <returns>所有订单的信息</returns>

public OrderInfo GetOrder(int orderId) {

OrderInfo order = new OrderInfo();

//创建参数

SqlParameter parm = new SqlParameter(PARM_ORDER_ID, SqlDbType.Int);

parm.Value = orderId;

//执行查询读取订单

using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, parm)) {

if (rdr.Read()) {

//从第一行中读出订单头

AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null, "email");

AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null, "email");

order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), null, billingAddress, shippingAddress, rdr.GetDecimal(21), null, null);

IList<LineItemInfo> lineItems = new List<LineItemInfo>();

LineItemInfo item = null;

//根据第一行及其子行创建商品列表

do {

item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));

lineItems.Add(item);

} while (rdr.Read());

order.LineItems = new LineItemInfo[lineItems.Count];

lineItems.CopyTo(order.LineItems, 0);

}

return order;

}

/// <summary>

/// 获取缓存参数的内部方法

/// </summary>

/// <returns></returns>

private static SqlParameter[] GetOrderParameters() {

SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_ORDER);

if (parms == null) {

parms = new SqlParameter[] {

new SqlParameter(PARM_USER_ID, SqlDbType.VarChar, 80),

new SqlParameter(PARM_DATE, SqlDbType.DateTime, 12),

new SqlParameter(PARM_SHIP_ADDRESS1, SqlDbType.VarChar, 80),

new SqlParameter(PARM_SHIP_ADDRESS2, SqlDbType.VarChar, 80),

new SqlParameter(PARM_SHIP_CITY, SqlDbType.VarChar, 80),

new SqlParameter(PARM_SHIP_STATE, SqlDbType.VarChar, 80),

new SqlParameter(PARM_SHIP_ZIP, SqlDbType.VarChar, 50),

new SqlParameter(PARM_SHIP_COUNTRY, SqlDbType.VarChar, 50),

new SqlParameter(PARM_BILL_ADDRESS1, SqlDbType.VarChar, 80),

new SqlParameter(PARM_BILL_ADDRESS2, SqlDbType.VarChar, 80),

new SqlParameter(PARM_BILL_CITY, SqlDbType.VarChar, 80),

new SqlParameter(PARM_BILL_STATE, SqlDbType.VarChar, 80),

new SqlParameter(PARM_BILL_ZIP, SqlDbType.VarChar, 50),

new SqlParameter(PARM_BILL_COUNTRY, SqlDbType.VarChar, 50),

new SqlParameter(PARM_TOTAL, SqlDbType.Decimal, 8),

new SqlParameter(PARM_BILL_FIRST_NAME, SqlDbType.VarChar, 80),

new SqlParameter(PARM_BILL_LAST_NAME, SqlDbType.VarChar, 80),

new SqlParameter(PARM_SHIP_FIRST_NAME, SqlDbType.VarChar, 80),

new SqlParameter(PARM_SHIP_LAST_NAME, SqlDbType.VarChar, 80),

new SqlParameter(PARM_AUTHORIZATION_NUMBER, SqlDbType.Int)};

SqlHelper.CacheParameters(SQL_INSERT_ORDER, parms);

}

return parms;

}

private static SqlParameter[] GetItemParameters(int i) {

SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_ITEM + i);

if (parms == null) {

parms = new SqlParameter[] {

new SqlParameter(PARM_LINE_NUMBER + i, SqlDbType.Int, 4),

new SqlParameter(PARM_ITEM_ID+i, SqlDbType.VarChar, 10),

new SqlParameter(PARM_QUANTITY+i, SqlDbType.Int, 4),

new SqlParameter(PARM_PRICE+i, SqlDbType.Decimal, 8)};

SqlHelper.CacheParameters(SQL_INSERT_ITEM + i, parms);

}

return parms;

}

        好了，万事俱备，只欠工厂了。这个项目就是传说中的抽象工厂了，需要引用IDAL。
        代码非常简单，如下：
        好了，一个简单的抽象工厂模式下的DAL层这里就算建好了。我们在BusinessLogic层次中只需要引用DALFactory、IDAL、Model就可以使用了。直接使用DALFactory中的Create方法创建实例，然后调用其方法就可以实现对数据库的增删查改。
        至于，在PetShop中还提供了的Message机制和Membership机制就下次再讨论了。

/// <summary>

/// 抽象工厂类, 从配置文件创建DAL

/// </summary>

public sealed class DataAccess {

// 从配置文件获取我们要使用的DAL类型

private static readonly string path = ConfigurationManager.AppSettings["WebDAL"];

private static readonly string orderPath = ConfigurationManager.AppSettings["OrdersDAL"];

private DataAccess() { }

/// <summary>

/// 创建目录访问对象实例

/// </summary>

/// <returns>目录接口实例</returns>