pub.cs

中国工商网电子商务购物中心系统EMall 软件介绍： 完全公开源代码,并无任何许可限制,特别基于大型电子商务网站的系统开发 Microsoft SQL Server 2000后台数据库

		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;
		}
        
		/// <summary>
		/// 返回最TOP的几个商品列表
		/// </summary>
		/// <param name="sTop">限定记录条数</param>
		/// <param name="sORDERBY">指定排列字段</param>
		/// <param name="sCategoryID">附加有指定商品类别</param>
		/// <returns>返回最TOP的几个商品列表</returns>
		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;
		}

		/// <summary>
		/// SqlDataAdapter方式的存储过程.列表.商品.所有.指定OrderBy
		/// </summary>
		/// <param name="OrderBy">指定OrderBy</param>
		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);
		}
		
		/// <summary>
		/// 返回最TOP的几个链接列表
		/// </summary>
		/// <param name="sTop">限定记录条数</param>
		/// <param name="sORDERBY">指定排列字段</param>
		/// <returns>返回最TOP的几个链接列表</returns>
		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;
		}		
		
		/// <summary>
		/// 获取指定商品ID的明细
		/// </summary>
		/// <param name="ProductID">要查看的商品ID</param>
		/// <returns>返回明细</returns>
		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;
		}

		/// <summary>
		/// 获取商品列表.按指定字段类型
		/// </summary>
		/// <param name="sFieldName">字段类型</param>
		/// <returns></returns>
		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;
		}

		/// <summary>
		/// 显示指定购物车ID的记录
		/// </summary>
		/// <param name="sCartID">购物车ID</param>
		/// <returns></returns>
		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;
		}

		/// <summary>
		/// 获取指定购物车ID的总金额
		/// </summary>
		/// <param name="sCartID">购物车ID</param>
		/// <returns>总金额</returns>
		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;
			}
		}

		/// <summary>
		/// 把当前商品加入指定的购物车里
		/// </summary>
		/// <param name="sCartID">购物车ID</param>
		/// <param name="iProductID">商品ID</param>
		/// <param name="iQuantity">商品数量</param>
		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();
		}

		/// <summary>
		/// 购物车.更新数量
		/// </summary>
		/// <param name="sCartID">指定购物车ID</param>
		/// <param name="iProductID">指定商品ID</param>
		/// <param name="iQuantity">要更新的数量</param>
		public void spShoppingCartUpdateQty(string sCartID, int iProductID, int iQuantity) 
		{
			if (iQuantity < 0) 
			{
				throw new Exception("系统提示: 数量不能小于 0, 请检查各项输入是否一些正确");
			}
			ConnOpen();
			SqlCommand1 = new SqlCommand("spShoppingCartUpdateQty", SqlConnection1);
			SqlCommand1.CommandType = CommandType.StoredProcedure;

			SqlParameter Param1 = new SqlParameter("@CartID", SqlDbType.VarChar, 50);
			SqlParameter Param2 = new SqlParameter("@ProductID", SqlDbType.Int, 4);
			SqlParameter Param3 = new SqlParameter("@Quantity", SqlDbType.Int, 4);
			SqlCommand1.Parameters.Add(Param1);
			SqlCommand1.Parameters.Add(Param2);
			SqlCommand1.Parameters.Add(Param3);
			Param2.Value = iProductID;
			Param1.Value = sCartID;
			Param3.Value = iQuantity;

			SqlCommand1.ExecuteNonQuery();
		}

		/// <summary>
		/// 购物车.删除所选项
		/// </summary>
		/// <param name="sCartID">指定购物车ID</param>
		/// <param name="iProductID">指定商品ID</param>
		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();
		}

		/// <summary>
		/// 将购物车确认为订单
		/// </summary>
		/// <param name="iCustomerID">指定客户ID</param>
		/// <param name="sCartID">指定购物车ID</param>
		/// <param name="sMemo">加入备注说明</param>
		/// <returns>返回所生成的订单ID给客户</returns>
		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;
		}

		/// <summary>
		/// 作用: 从一个购物车中转到另一个购物车中
		/// 情形: 应用于在登录或者注册过程完成后, 将临时购物车转到该用户的购物车中.
		/// </summary>
		/// <param name="sOldCartId">原来的购物车ID</param>
		/// <param name="sNewCartId">现在的购物车ID</param>
		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();
		}

		/// <summary>
		/// 清空购物车
		/// </summary>
		/// <param name="sCartID"></param>
		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.-----------------------其他实用过程/函数---------------------------
		/// <summary>
		/// 检查今天昨天前天及以前的天
		/// </summary>
		/// <param name="dt">要检查的表</param>
		/// <returns>返回检查结果</returns>
		/// <example><%# CheckDate((DateTime)DataBinder.Eval(Container.DataItem,"DateTimes").ToString(yyyyMMdd)) %></example>
		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;
		}

		/// <summary>
		/// 截取指定长度的子字符串
		/// </summary>
		/// <param name="sStr">需要截取的字符串</param>
		/// <param name="Len">目标长度</param>
		/// <returns>返回截取后的字符串</returns>
		/// <example><%#  GetSubString(DataBinder.Eval(Container.DataItem,"Memo").ToString()) %></example>
		public string GetSubString(string sStr, int Len)
		{
			if (sStr.Length > Len)
			{
				return sStr.Substring(0, Len) + "...";
			}
			else
			{
				return sStr;
			}
		}
	}
}