///中国工商网.电子商务购物中心系统.EMall
///演示地址:http://emall.12986.com
///中国工商网 - http://www.12986.com ///Copyright (c) 1998-2005
///作者: Dili J.F. Senders
///技术支持:如果任何技术问题,请登录中国工商网技术支持网站咨询:http://cs.12986.com/
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
{
///
/// Pub 的摘要说明。
///
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 = "" + SiteName +"";
}
// 1.——————————————————数据库操作——————————————————————
///
/// 连接打开
///
public void ConnOpen()
{
SqlConnection1 = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Conn"]);
SqlConnection1.Open();
}
///
/// 连接关闭(之前清除DateSet1中的数据)
///
public void ConnClose()
{
if (DataSet1 != null)
{
DataSet1.Clear();
}
if (SqlConnection1 != null)
{
SqlConnection1.Close();
}
}
///
/// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
///
/// 打开表Sql语句
public void Fill(string sSQL)
{
ConnOpen();
SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
DataSet1 = new DataSet();
SqlDataAdapter1.Fill(DataSet1);
}
///
/// 引用存储过程构造DataSet
///
/// 要引用的存储过程
public void FillDataSet(string sp)
{
ConnOpen();
SqlDataAdapter1 = new SqlDataAdapter(sp, SqlConnection1);
DataSet1 = new DataSet();
SqlDataAdapter1.Fill(DataSet1);
}
///
/// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
///
/// 用于表映谢的源表的名称
/// 打开表Sql语句
public void Fill(string TableName,string sql)
{
ConnOpen();
SqlDataAdapter1 = new SqlDataAdapter(sql, SqlConnection1);
DataSet1 = new DataSet();
SqlDataAdapter1.Fill(DataSet1, TableName);
}
///
/// 给DataSet对象增加映谢表
///
/// SQL语句
public void FillAdd(string sSQL)
{
SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
SqlDataAdapter1.Fill(DataSet1);
}
///
/// 给DataSet对象增加映谢表
///
/// SQL语句
public void FillAdd(string TableName, string sSQL)
{
SqlDataAdapter1 = new SqlDataAdapter(sSQL, SqlConnection1);
SqlDataAdapter1.Fill(DataSet1, TableName);
}
///
/// 获取最大值
///
///
///
///
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;
}
///
/// 获得包含在DataSet对象的映谢表集合中的index为0的映谢表
///
/// SQL语句
public void GetTable(string sSQL)
{
Fill(sSQL);
DataTable1 = DataSet1.Tables[0];
}
///
/// 获得符合该Sql语句的表记录数
///
/// SQL语句
/// 返回表记录条数
public int GetRowCount(string sSQL)
{
Fill(sSQL);
try
{
int Count = DataSet1.Tables[0].Rows.Count;
ConnClose();
return Count;
}
catch
{
ConnClose();
return 0;
}
}
///
/// 通过传Sql语句关键key值获得表中一行的数据
///
/// 带关键Key值参数的SQL语句
public void GetRowRecord(string sSQL)
{
Fill(sSQL);
DataRow1 = DataSet1.Tables[0].Rows[0];
ConnClose();
}
///
/// 执行Transact-SQL语句,对数据库记录做插入,修改,删除等操作
///
/// Transact-SQL语句
public void ExeSql(string sSQL)
{
ConnOpen();
SqlCommand1 = new SqlCommand(sSQL, SqlConnection1);
SqlCommand1.ExecuteNonQuery();
SqlCommand1.Dispose();
}
// 2.——————————————————绑定控件——————————————————————
///
/// 绑定DataGrid控件并显示数据
///
/// Sql语句
/// DataGrid控件id值
public void BindDataGrid(string sSQL, DataGrid DataGrid1)
{
Fill(sSQL);
DataGrid1.DataSource = DataSet1.Tables[0].DefaultView;
DataGrid1.DataBind();
}
///
/// 绑定DataList控件并显示数据
///
/// Sql语句
/// DataList控件ID值
public void BindDataList(string sSQL, DataList DataList1)
{
Fill(sSQL);
DataList1.DataSource = DataSet1.Tables[0].DefaultView;
DataList1.DataBind();
}
///
/// 绑定Repeater控件并显示数
///
/// Sql语句
/// Repeater控件ID值
public void BindRepeater(string sSQL,Repeater Repeater1)
{
Fill(sSQL);
Repeater1.DataSource = DataSet1.Tables[0].DefaultView;
Repeater1.DataBind();
}
// 3.——————————————————操作业务——————————————————————
///
/// 返回当前用户的购物车ID
///
/// 返回当前用户的购物车ID
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();
}
}
///
/// 显示记录列表.最新消息
///
///
public SqlDataReader spNewsList()
{
ConnOpen();
SqlCommand1 = new SqlCommand("spNewsList", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// 用户登录
///
/// 登录时的电子邮件
/// 相应的密码
///
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();
}
///
/// 更新密码
///
/// 登录时的电子邮件
/// 相应的密码
/// 新的密码
///
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();
}
///
/// 获取商品列表
///
/// 查询字段值, 如货号或者品名等/按商品分类值
/// true/false: 按指定查询字段值/按商品分类
///
public SqlDataReader spProductsSearch(string sStr, bool T)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spProductsSearch", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Para1 = new SqlParameter("@Search", SqlDbType.VarChar, 255);
SqlParameter Para2 = new SqlParameter("@SearchType", SqlDbType.Bit, 1);
SqlCommand1.Parameters.Add(Para1);
SqlCommand1.Parameters.Add(Para2);
Para1.Value = sStr;
Para2.Value = T;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// 返回最TOP的几个商品列表
///
/// 限定记录条数
/// 指定排列字段
/// 附加有指定商品类别
/// 返回最TOP的几个商品列表
public SqlDataReader spProductsTop(string Top, string OrderBy, string CategoryID)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spProductsTop", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
SqlParameter Para2 = new SqlParameter("@OrderBy", SqlDbType.VarChar, 50);
SqlParameter Para3 = new SqlParameter("@CategoryID", SqlDbType.VarChar, 25);
SqlCommand1.Parameters.Add(Para1);
SqlCommand1.Parameters.Add(Para2);
SqlCommand1.Parameters.Add(Para3);
Para1.Value = Top;
Para2.Value = OrderBy;
Para3.Value = CategoryID;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// SqlDataAdapter方式的存储过程.列表.商品.所有.指定OrderBy
///
/// 指定OrderBy
public void spProductsAllOrderBy(string OrderBy)
{
ConnOpen();
SqlDataAdapter1 = new SqlDataAdapter("spProductsTop", SqlConnection1);
SqlDataAdapter1.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
SqlParameter Para2 = new SqlParameter("@OrderBy", SqlDbType.VarChar, 50);
SqlParameter Para3 = new SqlParameter("@CategoryID", SqlDbType.VarChar, 25);
SqlDataAdapter1.SelectCommand.Parameters.Add(Para1);
SqlDataAdapter1.SelectCommand.Parameters.Add(Para2);
SqlDataAdapter1.SelectCommand.Parameters.Add(Para3);
Para1.Value = "100 PERCENT";
Para2.Value = OrderBy;
Para3.Value = "-100";
DataSet1 = new DataSet();
SqlDataAdapter1.Fill(DataSet1);
}
///
/// 返回最TOP的几个链接列表
///
/// 限定记录条数
/// 指定排列字段
/// 返回最TOP的几个链接列表
public SqlDataReader spCustomersTop(string sTop, string sORDERBY)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spCustomersTop", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Para1 = new SqlParameter("@Top", SqlDbType.VarChar, 25);
SqlParameter Para2 = new SqlParameter("@ORDERBY", SqlDbType.VarChar, 50);
SqlCommand1.Parameters.Add(Para1);
SqlCommand1.Parameters.Add(Para2);
Para1.Value = sTop;
Para2.Value = sORDERBY;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// 获取指定商品ID的明细
///
/// 要查看的商品ID
/// 返回明细
public SqlDataReader spProductDetail(int ProductID)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spProductDetail", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Para1 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
SqlCommand1.Parameters.Add(Para1);
Para1.Value = ProductID;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// 获取商品列表.按指定字段类型
///
/// 字段类型
///
public SqlDataReader GetProductsOrderBy(string sFieldName)
{
ConnOpen();
SqlCommand1 = new SqlCommand("GetProductsOrderBy", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@FieldName", SqlDbType.VarChar, 255);
SqlCommand1.Parameters.Add(Param1);
Param1.Value = sFieldName;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// 显示指定购物车ID的记录
///
/// 购物车ID
///
public SqlDataReader spShoppingCartList(string sCartID)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spShoppingCartList", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
SqlCommand1.Parameters.Add(Param1);
Param1.Value = sCartID;
SqlDataReader result = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
///
/// 获取指定购物车ID的总金额
///
/// 购物车ID
/// 总金额
public decimal spShoppingCartTotal(string sCartID)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spShoppingCartTotal", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
SqlParameter Param2 = new SqlParameter("@TotalCost", SqlDbType.Money, 8);
SqlCommand1.Parameters.Add(Param1);
SqlCommand1.Parameters.Add(Param2);
Param1.Value = sCartID;
Param2.Direction = ParameterDirection.Output;
SqlCommand1.ExecuteNonQuery();
if (Param2.Value.ToString() != "")
{
return (decimal)Param2.Value;
}
else
{
return 0;
}
}
///
/// 把当前商品加入指定的购物车里
///
/// 购物车ID
/// 商品ID
/// 商品数量
public void spShoppingCartAddItem(string sCartID, int iProductID, int iQuantity)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spShoppingCartAddItem", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
SqlParameter Param2 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
SqlParameter Param3 = new SqlParameter("@Quantity", SqlDbType.Int, 4);
SqlCommand1.Parameters.Add(Param1);
SqlCommand1.Parameters.Add(Param2);
SqlCommand1.Parameters.Add(Param3);
Param1.Value = iProductID;
Param2.Value = sCartID;
Param3.Value = iQuantity;
SqlCommand1.ExecuteNonQuery();
}
///
/// 购物车.更新数量
///
/// 指定购物车ID
/// 指定商品ID
/// 要更新的数量
public void spShoppingCartUpdateQty(string sCartID, int iProductID, int iQuantity)
{
if (iQuantity
/// 购物车.删除所选项
///
/// 指定购物车ID
/// 指定商品ID
public void spShoppingCartRemoveItem(string sCartID, int iProductID)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spShoppingCartRemoveItem", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
SqlParameter Param2 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
SqlCommand1.Parameters.Add(Param1);
SqlCommand1.Parameters.Add(Param2);
Param1.Value = sCartID;
Param2.Value = iProductID;
SqlCommand1.ExecuteNonQuery();
}
///
/// 将购物车确认为订单
///
/// 指定客户ID
/// 指定购物车ID
/// 加入备注说明
/// 返回所生成的订单ID给客户
public int spOrdersInsert(int iCustomerID, string sCartID, string sMemo)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spOrdersInsert", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
SqlParameter Param2 = new SqlParameter("@CustomerID", SqlDbType.Int, 4);
SqlParameter Param3 = new SqlParameter("@Memo", SqlDbType.VarChar, 2000);
SqlParameter Param4 = new SqlParameter("@OrderID", SqlDbType.Int, 4);
SqlCommand1.Parameters.Add(Param1);
SqlCommand1.Parameters.Add(Param2);
SqlCommand1.Parameters.Add(Param3);
SqlCommand1.Parameters.Add(Param4);
Param1.Value = sCartID;
Param2.Value = iCustomerID;
Param3.Value = sMemo;
Param4.Direction = ParameterDirection.Output;
SqlCommand1.ExecuteNonQuery();
return (int)Param4.Value;
}
///
/// 作用: 从一个购物车中转到另一个购物车中
/// 情形: 应用于在登录或者注册过程完成后, 将临时购物车转到该用户的购物车中.
///
/// 原来的购物车ID
/// 现在的购物车ID
public void spShoppingCartMigrate(String sOldCartId, String sNewCartId)
{
ConnOpen();
SqlCommand1 = new SqlCommand("spShoppingCartMigrate", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@OldCartId ", SqlDbType.VarChar, 50);
SqlParameter Param2 = new SqlParameter("@NewCartId ", SqlDbType.VarChar, 50);
SqlCommand1.Parameters.Add(Param1);
SqlCommand1.Parameters.Add(Param2);
Param1.Value = sOldCartId;
Param2.Value = sNewCartId;
SqlCommand1.ExecuteNonQuery();
}
///
/// 清空购物车
///
///
public void EmptyCart(string sCartID)
{
ConnOpen();
SqlCommand1 = new SqlCommand("ShoppingCartEmpty", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
SqlCommand1.Parameters.Add(Param1);
Param1.Value = sCartID;
SqlCommand1.ExecuteNonQuery();
}
// 4.-----------------------其他实用过程/函数---------------------------
///
/// 检查今天昨天前天及以前的天
///
/// 要检查的表
/// 返回检查结果
///
public string CheckDate(DateTime dt)
{
string sStr;
DateTime Today;
Today = DateTime.Today;
if( dt.Date == Today )
sStr = "今天";
else if( dt.AddDays(1).Date == Today )
sStr = "昨天";
else if( dt.AddDays(2).Date == Today )
sStr = "前天";
else sStr = dt.Year.ToString() + "年" + dt.Month.ToString() + "月" + dt.Day.ToString() + "日";
sStr += " " + dt.Hour.ToString() + ":" + dt.Minute.ToString();
return sStr;
}
///
/// 截取指定长度的子字符串
///
/// 需要截取的字符串
/// 目标长度
/// 返回截取后的字符串
///
public string GetSubString(string sStr, int Len)
{
if (sStr.Length > Len)
{
return sStr.Substring(0, Len) + "...";
}
else
{
return sStr;
}
}
}
}
中国工商网.电子商务购物中心系统.EMall
最新推荐文章于 2024-05-03 00:28:05 发布