pub.cs

电子商城源代码,包含项目工程文件! 数据库建表语句 具体功能包括电子购物车,在线购物!

using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace EMall
{
	/// <summary>
	/// Pub 的摘要说明。
	/// </summary>
	public class Pub
	{
		// 数据库公用变量
		public SqlConnection SqlConnection1; 
		public SqlCommand SqlCommand1;
		public SqlDataAdapter SqlDataAdapter1;
		public SqlDataAdapter SqlDataAdapter2;
		public SqlDataReader SqlDataReader1;
		public SqlCommandBuilder SqlCommandBuilder1;
		public DataSet DataSet1;
		public DataTable DataTable1;
		public DataRow DataRow1;

		// 非数据库公用变量
		public string LoginName;		// 登录用户
		public string SiteName = System.Configuration.ConfigurationSettings.AppSettings["SiteName"];			// 站点名称
		public string SiteNav;			// 站点导航
		public string sTitle = System.Configuration.ConfigurationSettings.AppSettings["SiteName"];			// 站点标题

		// 页面导航
		public int PageSize = 5;		// 每页显示记录数
		public int RecordCount, PageCount, CurrentPage;

		public Pub()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		// 0.——————————————————通用操作——————————————————————

		public void NavInfo(string sMsg, string sMsgURL, string sMsgTarget)
		{
			sTitle = sTitle + " -- " + sMsg;
			SiteNav = "<a href=./>" + SiteName +"</a>";
		}


		// 1.——————————————————数据库操作——————————————————————

		/// <summary>
		/// 连接打开
		/// </summary>
		public void ConnOpen()
		{   
			SqlConnection1 = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Conn"]);
			SqlConnection1.Open();
		}

		/// <summary>
		/// 连接关闭(之前清除DateSet1中的数据)
		/// </summary>
		public void ConnClose()
		{
			if (DataSet1 != null)
			{
				DataSet1.Clear();
			}
			if (SqlConnection1 != null)
			{
				SqlConnection1.Close();
			}
		}

		/// <summary>
		/// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
		/// </summary>
		/// <param name="sSQL">打开表Sql语句</param>
		public void Fill(string sSQL)
		{  	
			ConnOpen();
			SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
			DataSet1 = new DataSet();
			SqlDataAdapter1.Fill(DataSet1);	
		}

		/// <summary>
		/// 引用存储过程构造DataSet
		/// </summary>
		/// <param name="sp">要引用的存储过程</param>
		public void FillDataSet(string sp)
		{  	
			ConnOpen();
			SqlDataAdapter1 = new SqlDataAdapter(sp, SqlConnection1);
			DataSet1 = new DataSet();
			SqlDataAdapter1.Fill(DataSet1);
		}

		/// <summary>
		/// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
		/// </summary>
		/// <param name="TableName">用于表映谢的源表的名称</param>
		/// <param name="sql">打开表Sql语句</param>
		public void Fill(string TableName,string sql)
		{  
			ConnOpen();
			SqlDataAdapter1 = new SqlDataAdapter(sql, SqlConnection1);
			DataSet1 = new DataSet();
			SqlDataAdapter1.Fill(DataSet1, TableName);
		}

		/// <summary>
		/// 给DataSet对象增加映谢表
		/// </summary>
		/// <param name="sSQL">SQL语句</param>
		public void FillAdd(string sSQL)
		{
			SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
			SqlDataAdapter1.Fill(DataSet1);
		}

		/// <summary>
		/// 给DataSet对象增加映谢表
		/// </summary>
		/// <param name="sSQL">SQL语句</param>
		public void FillAdd(string TableName, string sSQL)
		{
			SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
			SqlDataAdapter1.Fill(DataSet1, TableName);
		}

		/// <summary>
		/// 获取最大值
		/// </summary>
		/// <param name="fID"></param>
		/// <param name="TableName"></param>
		/// <returns></returns>
		public string GetMaxId(string fID, string TableName)
		{
			string sKey;
			string sSQL="SELECT TOP 1 " + fID + " FROM " + TableName + " ORDER BY " + fID + " DESC";
			if (GetRowCount(sSQL) == 0)
			{
				sKey="1";
			}
			else
			{
				GetRowRecord(sSQL);
				sKey = (int.Parse(DataRow1[fID].ToString()) + 1).ToString(); // 获得数据库表key值
			}
			return sKey;
		}

		/// <summary>
		/// 获得包含在DataSet对象的映谢表集合中的index为0的映谢表
		/// </summary>
		/// <param name="sSQL">SQL语句</param>
		public void GetTable(string sSQL)
		{
			Fill(sSQL);
			DataTable1 = DataSet1.Tables[0];
		}

		/// <summary>
		/// 获得符合该Sql语句的表记录数
		/// </summary>
		/// <param name="sSQL">SQL语句</param>
		/// <returns>返回表记录条数</returns>
		public int GetRowCount(string sSQL)
		{
			Fill(sSQL);
			try
			{
				int Count = DataSet1.Tables[0].Rows.Count;
				ConnClose();
				return Count;
			}
			catch
			{
				ConnClose();
				return 0;
			}
		}

		/// <summary>
		/// 通过传Sql语句关键key值获得表中一行的数据
		/// </summary>
		/// <param name="sSQL">带关键Key值参数的SQL语句</param>
		public void GetRowRecord(string sSQL)
		{
			Fill(sSQL);
			DataRow1 = DataSet1.Tables[0].Rows[0];
			ConnClose();
		}

		/// <summary>
		/// 执行Transact-SQL语句,对数据库记录做插入,修改,删除等操作
		/// </summary>
		/// <param name="sSQL">Transact-SQL语句</param>
		public void ExeSql(string sSQL)
		{   
			ConnOpen();
			SqlCommand1 = new SqlCommand(sSQL, SqlConnection1);
			SqlCommand1.ExecuteNonQuery();
			SqlCommand1.Dispose();
		}

		// 2.——————————————————绑定控件——————————————————————
        
		/// <summary>
		/// 绑定DataGrid控件并显示数据
		/// </summary>
		/// <param name="sSQL">Sql语句</param>
		/// <param name="DataGrid1">DataGrid控件id值</param>
		public void BindDataGrid(string sSQL, DataGrid DataGrid1)
		{
			Fill(sSQL);
			DataGrid1.DataSource = DataSet1.Tables[0].DefaultView;
			DataGrid1.DataBind();
		}

		/// <summary>
		/// 绑定DataList控件并显示数据
		/// </summary>
		/// <param name="sSQL">Sql语句</param>
		/// <param name="DataList1">DataList控件ID值</param>
		public void BindDataList(string sSQL, DataList DataList1)
		{
			Fill(sSQL);
			DataList1.DataSource = DataSet1.Tables[0].DefaultView;
			DataList1.DataBind();
		}

		/// <summary>
		/// 绑定Repeater控件并显示数
		/// </summary>
		/// <param name="sSQL">Sql语句</param>
		/// <param name="Repeater1">Repeater控件ID值</param>
		public void BindRepeater(string sSQL,Repeater Repeater1)
		{
			Fill(sSQL);
			Repeater1.DataSource = DataSet1.Tables[0].DefaultView;
			Repeater1.DataBind();
		}

		// 3.——————————————————操作业务——————————————————————

		/// <summary>
		/// 返回当前用户的购物车ID
		/// </summary>
		/// <returns>返回当前用户的购物车ID</returns>
		public String GetShoppingCartID() 
		{
			System.Web.HttpContext Context = System.Web.HttpContext.Current;
			// 如果该用户已经通过验证后登录了系统, 那么以该用户的 CustomerId作为购物车ID
			if (Context.User.Identity.Name != "") 
			{
				return Context.User.Identity.Name;
			}
			// 否则, 如果浏览器Cookie中已经分配给该用户一个购物车ID
			if (Context.Request.Cookies["ShoppingCartID"] != null) 
			{
				return Context.Request.Cookies["ShoppingCartID"].Value;
			}
			else 
			{
				// 利用System.Guid Class产生一个随机GUID(全球唯一标识号)
				Guid TempCartId = Guid.NewGuid();
				// 回写到客户端指定的cookie中
				Context.Response.Cookies["ShoppingCartID"].Value = TempCartId.ToString();
				// 同时返回 TempCartId
				return TempCartId.ToString();
			}
		}

		/// <summary>
		/// 显示记录列表.最新消息
		/// </summary>
		/// <returns></returns>
		public SqlDataReader spNewsList() 
		{
			ConnOpen();
			SqlCommand1 = new SqlCommand("spNewsList", SqlConnection1);
			SqlCommand1.CommandType = CommandType.StoredProcedure;
			SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
			return result;
		}

		/// <summary>
		/// 用户登录
		/// </summary>
		/// <param name="sEmail">登录时的电子邮件</param>
		/// <param name="sPassword">相应的密码</param>
		/// <returns></returns>
		public String spCustomersLogin(string sEmail, string sPassword) 
		{
			ConnOpen();
			SqlCommand1 = new SqlCommand("spCustomersLogin", SqlConnection1);
			SqlCommand1.CommandType = CommandType.StoredProcedure;

			SqlParameter Param1 = new SqlParameter("@Email", SqlDbType.VarChar, 100);
			SqlParameter Param2 = new SqlParameter("@Password", SqlDbType.VarChar, 50);
			SqlParameter Param3 = new SqlParameter("@ID", SqlDbType.Int, 4);
			SqlCommand1.Parameters.Add(Param1);
			SqlCommand1.Parameters.Add(Param2);
			SqlCommand1.Parameters.Add(Param3);
			Param1.Value = sEmail;
			Param2.Value = sPassword;
			Param3.Direction = ParameterDirection.Output;

			SqlCommand1.ExecuteNonQuery();
			int ID = (int)(Param3.Value);
			if (ID == 0)
				return null;
			else
				return ID.ToString();
		}

		/// <summary>
		/// 更新密码
		/// </summary>
		/// <param name="sEmail">登录时的电子邮件</param>
		/// <param name="sPassword">相应的密码</param>
		/// <param name="sPasswordNew">新的密码</param>
		/// <returns></returns>
		public String spCustomersUpdatePassword(string sEmail, string sPassword, string sPasswordNew) 
		{
			ConnOpen();
			SqlCommand1 = new SqlCommand("spCustomersUpdatePassword", SqlConnection1);
			SqlCommand1.CommandType = CommandType.StoredProcedure;

			SqlParameter Param1 = new SqlParameter("@Email", SqlDbType.VarChar, 100);
			SqlParameter Param2 = new SqlParameter("@PasswordOld", SqlDbType.VarChar, 50);
			SqlParameter Param3 = new SqlParameter("@PasswordNew", SqlDbType.VarChar, 50);
			SqlParameter Param4 = new SqlParameter("@Result", SqlDbType.VarChar, 100);
			SqlCommand1.Parameters.Add(Param1);
			SqlCommand1.Parameters.Add(Param2);
			SqlCommand1.Parameters.Add(Param3);
			SqlCommand1.Parameters.Add(Param4);
			Param1.Value = sEmail;
			Param2.Value = sPassword;
			Param3.Value = sPasswordNew;
			Param4.Direction = ParameterDirection.Output;

			SqlCommand1.ExecuteNonQuery();
			return Param4.Value.ToString();
		}

		/// <summary>
		/// 获取商品列表
		/// </summary>
		/// <param name="sStr">查询字段值, 如货号或者品名等/按商品分类值</param>
		/// <param name="T">true/false: 按指定查询字段值/按商品分类</param>
		/// <returns></returns>
		public SqlDataReader spProductsSearch(string sStr, bool T) 
		{
			ConnOpen();
			SqlCommand1 = new SqlCommand("spProductsSearch", SqlConnection1);
			SqlCommand1.CommandType = CommandType.StoredProcedure;