sqlbookshopprofileprovider.cs

一个非常好的网上书店系统

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// 为配置提供操作类
/// </summary>
public class SqlBookShopProfileProvider
{
	public SqlBookShopProfileProvider()	{	}
	// 匿名用户变量
	private const int AUTH_ANONYMOUS = 0;
	// 身份验证变量
	private const int AUTH_AUTHENTICATED = 1;
	// 验证设置
	private const int AUTH_ALL = 2;

    /// <summary>
    /// 获取当前用户的帐户信息
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="appName">应用程序名</param>
    /// <returns>帐户信息</returns>
    public AddressInfo GetAccountInfo(string userName, string appName)
    {
        //SQL选择语句
        string sqlSelect = "SELECT Account.Mail, Account.UserName, Account.Address,  Account.Code, Account.Country, Account.Phone FROM Account INNER JOIN Profiles ON Account.ProfileID = Profiles.ProfileID WHERE Profiles.Username = @Username AND Profiles.ApplicationName = @ApplicationName;";
        SqlParameter[] parms = {					   
            new SqlParameter("@Username", SqlDbType.VarChar, 256),
            new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256)};
        parms[0].Value = userName;
        parms[1].Value = appName;

        AddressInfo addressInfo = null;

        SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlSelect, parms);
        while (dr.Read())
        {
            addressInfo = new AddressInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5),dr.GetString(0));
        }
        dr.Close();

        return addressInfo;
    }

    /// <summary>
    ///更新当前用户的帐户地址
    /// </summary>
    /// <param name="uniqueID">配置ID</param>
    /// <param name="addressInfo">当前用户帐户地址信息</param>   
    public void SetAccountInfo(int uniqueID, AddressInfo addressInfo)
    {
        string sqlDelete = "DELETE FROM Account WHERE ProfileID = @UniqueID;";
        SqlParameter param = new SqlParameter("@UniqueID", SqlDbType.Int);
        param.Value = uniqueID;

        string sqlInsert = "INSERT INTO Account (ProfileID, Mail, UserName, Address, Code, Country, Phone) VALUES (@UniqueID, @Email, @UserName,@Address, @Code, @Country, @Phone);";

        //初始化参数数组并赋值
        SqlParameter[] parms = {					   
        new SqlParameter("@UniqueID", SqlDbType.Int),
        new SqlParameter("@Email", SqlDbType.VarChar, 80),
        new SqlParameter("@UserName", SqlDbType.VarChar, 80),
        new SqlParameter("@Address", SqlDbType.VarChar, 80),
        new SqlParameter("@Code", SqlDbType.VarChar, 80),
        new SqlParameter("@Country", SqlDbType.VarChar, 80),
        new SqlParameter("@Phone", SqlDbType.VarChar, 80)};

        parms[0].Value = uniqueID;
        parms[1].Value = addressInfo.Email;
        parms[2].Value = addressInfo.UserName;
        parms[3].Value = addressInfo.Address;
        parms[4].Value = addressInfo.ZipCode;
        parms[5].Value = addressInfo.Country;
        parms[6].Value = addressInfo.Phone;

        SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        conn.Open();
        //开始事务
        SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, param);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);
            //执行事务
            trans.Commit();
        }
        catch (Exception e)
        {
            //出现错误，回滚事务
            trans.Rollback();
            throw new ApplicationException(e.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    /// <summary>
    /// 获取购物篮信息
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="appName">应用程序名</param>
    /// <param name="isShoppingCart">购物篮标志</param>
    /// <returns>购物篮中数据</returns>
	public IList<CartItemInfo> GetCartItems(string userName, string appName, bool isShoppingCart) {
        string sqlSelect = "SELECT Cart.ItemId, Cart.ProductName,  Cart.Price, Cart.ProductId, Cart.Quantity FROM Profiles INNER JOIN Cart ON Profiles.ProfileID = Cart.ProfileID WHERE Profiles.Username = @Username AND Profiles.ApplicationName = @ApplicationName ;";

        //创建参数并赋值
		SqlParameter[] parms = {						   
			new SqlParameter("@Username", SqlDbType.VarChar, 256),
			new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256),
			new SqlParameter("@IsShoppingCart", SqlDbType.Bit)};
		parms[0].Value = userName;
		parms[1].Value = appName;
		parms[2].Value = isShoppingCart;

        //执行选择操作
        SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlSelect, parms);
		IList<CartItemInfo> cartItems = new List<CartItemInfo>();
		while(dr.Read()) {
			CartItemInfo cartItem = new CartItemInfo(dr.GetInt32(0), dr.GetString(1), dr.GetInt32(4), dr.GetDecimal(2), 0, dr.GetInt32(3));
			cartItems.Add(cartItem);
		}
		dr.Close();
		return cartItems;  
	}

    /// <summary>
    /// 更新当前用户的购物篮
    /// </summary>
    /// <param name="uniqueID">配置ID</param>
    /// <param name="cartItems">购物篮数据集合</param>
    /// <param name="isShoppingCart">购物篮标志/param>
	public void SetCartItems(int uniqueID, ICollection<CartItemInfo> cartItems, bool isShoppingCart) {
        string sqlDelete = "DELETE FROM Cart WHERE ProfileID = @ProfileID ;";
		SqlParameter parms1 = 	new SqlParameter("@ProfileID", SqlDbType.Int);
		parms1.Value = uniqueID;
        if (cartItems.Count > 0) {

            // 使用事务更新数据
            string sqlInsert = "INSERT INTO Cart (ProfileID, ItemId, ProductName,  Price,  ProductId, Quantity) VALUES (@ProfileID, @ItemId, @Name,  @Price,  @ProductId,  @Quantity);";

            //创建参数列表
            SqlParameter[] parms2 = {				   
			new SqlParameter("@ProfileID", SqlDbType.Int),	
			new SqlParameter("@ItemId", SqlDbType.VarChar, 10),
			new SqlParameter("@Name", SqlDbType.VarChar, 80),
			new SqlParameter("@Price", SqlDbType.Decimal, 8),
			new SqlParameter("@ProductId", SqlDbType.VarChar, 10),
			new SqlParameter("@Quantity", SqlDbType.Int)};
            parms2[0].Value = uniqueID;
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
            conn.Open();
            //开始事务
            SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try {
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, parms1);

                //将购物篮内容添加到表中
                foreach (CartItemInfo cartItem in cartItems) {
                    parms2[1].Value = cartItem.ItemId;
                    parms2[2].Value = cartItem.Name;
                    parms2[3].Value = cartItem.Price;
                    parms2[4].Value = cartItem.ProductId;
                    parms2[5].Value = cartItem.Quantity;
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms2);
                }
                //执行事务
                trans.Commit();
            }
            catch (Exception e) {
                //事务回滚
                trans.Rollback();
                throw new ApplicationException(e.Message);
            }
            finally {
                conn.Close();
            }
        }
        else 
            // 删除购物篮商品
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlDelete, parms1);
	}

    /// <summary>
    /// 更新当前用户的激活日期
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="activityOnly">激活标志</param>
    /// <param name="appName">应用程序名</param>
	public void UpdateActivityDates(string userName, bool activityOnly, string appName) {
		DateTime activityDate = DateTime.Now;
		string sqlUpdate;
		SqlParameter[] parms;
		if(activityOnly) {
            //更改激活日期
            sqlUpdate = "UPDATE Profiles Set LastActivityDate = @LastActivityDate WHERE Username = @Username AND ApplicationName = @ApplicationName;";
			parms = new SqlParameter[]{						   
				new SqlParameter("@LastActivityDate", SqlDbType.DateTime),
				new SqlParameter("@Username", SqlDbType.VarChar, 256),
				new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256)};
			parms[0].Value = activityDate;
			parms[1].Value = userName;
			parms[2].Value = appName;
		}
		else {
            //更改更新日期
			sqlUpdate = "UPDATE Profiles Set LastActivityDate = @LastActivityDate, LastUpdateDate = @LastUpdatedDate WHERE Username = @Username AND ApplicationName = @ApplicationName;";
			parms = new SqlParameter[]{
				new SqlParameter("@LastActivityDate", SqlDbType.DateTime),
				new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime),
				new SqlParameter("@Username", SqlDbType.VarChar, 256),