productdb.cs

手机网店源码

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using MobileOnlineShop.Entities;

namespace MobileOnlineShop.DAL
{
	/// <summary>
	/// ProductDB 的摘要说明。
	/// </summary>
	public class ProductDB
	{

		static string connStr = ConfigurationSettings.AppSettings["ConnectionString"];
		/// <summary>
		/// 返回产品列表
		/// </summary>
		/// <param name="categoryID"></param>
		/// <returns></returns>
		public static DataView GetProducts(int categoryID,int currentPage,int pageSize) 
		{
			////设定导入的起始地址
			int startIndex = (currentPage-1)* pageSize;

			// 建立SqlConnection、SqlDataAdapter与DataSet的实例
			SqlConnection myConnection = new SqlConnection(connStr);
			SqlDataAdapter da = new SqlDataAdapter("GetProduct", myConnection);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
			DataSet ds = new DataSet();

			//向SqlCommand实例中添加参数
			SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
			parameterCategoryID.Value = categoryID;
			da.SelectCommand.Parameters.Add(parameterCategoryID);
			
			//执行命令
			da.Fill(ds,startIndex,pageSize,"Product");
   
			// 返回结果
			return ds.Tables["Product"].DefaultView;
		}

		/// <summary>
		/// 返回产品分类列表
		/// </summary>
		/// <returns></returns>
		public static SqlDataReader GetProductCategories() 
		{

			// 建立SqlConnection与SqlCommand的实例
			SqlConnection myConnection = new SqlConnection(connStr);
			SqlCommand myCommand = new SqlCommand("GetProductCategory", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;

			// 执行命令
			myConnection.Open();
			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			// 返回结果
			return result;
		}

		/// <summary>
		/// 计算总记录条数
		/// </summary>
		/// <returns></returns>
		public static int RecorderCalculate(int categoryID)
		{
			// 建立SqlConnection与SqlCommand的实例
			SqlConnection myConnection = new SqlConnection(connStr);
			SqlCommand myCommand = new SqlCommand("GetProductCount", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			
			//向SqlCommand实例中添加参数
			SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
			parameterCategoryID.Value = categoryID;
			myCommand.Parameters.Add(parameterCategoryID);

			//执行命令
			myConnection.Open();
			int result = (int)myCommand.ExecuteScalar();
            myConnection.Close();
			
			// 返回结果
			return result;
		}
		/// <summary>
		/// 通过产品型号搜索,返回一个数据集
		/// </summary>
		/// <param name="categoryName"></param>
		/// <returns></returns>
		public static DataSet SearchProductByModel(string categoryModel)
		{
			SqlConnection conn = new SqlConnection(connStr);
			DataSet ds = new DataSet(); 
			SqlDataAdapter da = new SqlDataAdapter("GetProductByCategory",conn);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter p = new SqlParameter("@categoryName",SqlDbType.NVarChar);
			p.Value = categoryModel;
			da.SelectCommand.Parameters.Add(p);
			da.Fill(ds); 			
			return ds;
		}
		/// <summary>
		/// 通过产品价格搜索,返回一个数据集
		/// </summary>
		/// <param name="minPrice"></param>
		/// <param name="maxPrice"></param>
		/// <returns></returns>
		public static DataSet SearchProductByPrice(decimal minPrice,decimal maxPrice)
		{
			SqlConnection conn = new SqlConnection(connStr);
			DataSet ds = new DataSet(); 
			SqlDataAdapter da = new SqlDataAdapter("GetProductByPrice",conn);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter p = new SqlParameter("@minPrice",SqlDbType.Money);
			SqlParameter p1 = new SqlParameter("@maxPrice",SqlDbType.Money);
//			SqlParameter p2 = new SqlParameter("@cateName",SqlDbType.NVarChar);
			p.Value = minPrice;
			p1.Value = maxPrice;
//			p2.Value = name;
			da.SelectCommand.Parameters.Add(p);
			da.SelectCommand.Parameters.Add(p1);
//			da.SelectCommand.Parameters.Add(p2);
			da.Fill(ds); 			
			return ds;
		}
		/// <summary>
		/// 根据产品名搜索产品,如:三星,联想
		/// </summary>
		/// <param name="categoryName"></param>
		/// <returns></returns>
		public static DataSet SearchProductByCategoryName(string categoryName)
		{
			SqlConnection conn = new SqlConnection(connStr);
			DataSet ds = new DataSet(); 
			SqlDataAdapter da = new SqlDataAdapter("GetProductByName",conn);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
		
			SqlParameter p3 = new SqlParameter("@category",SqlDbType.NVarChar);
		
			p3.Value = categoryName;
		
			da.SelectCommand.Parameters.Add(p3);
			da.Fill(ds); 			
			return ds;
		}
		/// <summary>
		/// 根据价格,品牌搜索商品
		/// </summary>
		/// <param name="productID"></param>
		/// <returns></returns>
		public static DataSet SearchProductByPriceName(decimal minPrice,decimal maxPrice,string categoryName)
		{
			SqlConnection conn = new SqlConnection(connStr);
			DataSet ds = new DataSet(); 
			SqlDataAdapter da = new SqlDataAdapter("GetProductByPriceName",conn);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter p1 = new SqlParameter("@minPrice",SqlDbType.Money);
			SqlParameter p2 = new SqlParameter("@maxPrice",SqlDbType.Money);
			SqlParameter p3 = new SqlParameter("@categoryName",SqlDbType.NVarChar);
			p1.Value = minPrice;
			p2.Value = maxPrice;
			p3.Value = categoryName;
			da.SelectCommand.Parameters.Add(p1);
			da.SelectCommand.Parameters.Add(p2);
			da.SelectCommand.Parameters.Add(p3);
			da.Fill(ds); 			
			return ds;
		}
		/// <summary>
		/// 通过产品ID,获得产品详情
		/// </summary>
		/// <param name="productID"></param>
		/// <returns></returns>
		public static Products GetProductDetails(int productID) 
		{
			Products myProductDetails = new Products();
			
			SqlConnection myConnection = new SqlConnection(connStr);
			SqlCommand myCommand = new SqlCommand("GetProductDetail", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
			parameterProductID.Value = productID;
			myCommand.Parameters.Add(parameterProductID);

			SqlParameter parameterUnitPrice = new SqlParameter("@UnitPrice", SqlDbType.Money, 8);
			parameterUnitPrice.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterUnitPrice);

			SqlParameter parameterCategoryName = new SqlParameter("@CategoryName", SqlDbType.NVarChar, 50);
			parameterCategoryName.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterCategoryName);

			SqlParameter parameterModel = new SqlParameter("@Model", SqlDbType.NVarChar, 50);
			parameterModel.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterModel);

			SqlParameter parameterProductImage = new SqlParameter("@ProductImage", SqlDbType.NVarChar, 50);
			parameterProductImage.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterProductImage);

			SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 3800);
			parameterDescription.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterDescription);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			try
			{
				myProductDetails.CategoryName = (string)parameterCategoryName.Value;
				myProductDetails.Model = (string)parameterModel.Value;
				myProductDetails.ProductImage = ((string)parameterProductImage.Value).Trim();
				myProductDetails.Description =((string)(parameterDescription.Value is DBNull ? String.Empty : parameterDescription.Value)).Trim();
			}
			catch
			{
				myProductDetails.CategoryName = "";
				myProductDetails.Model = "";
				myProductDetails.ProductImage = "";
				myProductDetails.Description = "";
			}
			try
			{
				myProductDetails.UnitPrice = (decimal)parameterUnitPrice.Value;
			}
			catch
			{
				myProductDetails.UnitPrice = 0;
			}
			
			
			return myProductDetails;
		}

		/// <summary>
		/// 返回热门商品
		/// </summary>
		/// <returns></returns>
		public static SqlDataReader GetPopularProducts()
		{
			SqlConnection myConnection = new SqlConnection(connStr);
			SqlCommand myCommand = new SqlCommand("ProductMostPopular", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;

			myConnection.Open();
			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			return result;
		}
	}
}