bisproduct.cs

一个网页的系统例子

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Common;

namespace DataAccessLibrary
{
	/// <summary>
	/// BisProduct 的摘要说明。
	/// </summary>
	public class BisProduct:BisBase
	{
		public DataTblProduct objTblProduct = new DataTblProduct();

		public BisProduct()
		{
		
		}

		/// <summary>
		/// 检索所有车型
		/// </summary>
		/// <returns></returns>
		public DataTable GetProductModel()
		{
			string strSql = " select ProductID from TBL_Product";

			DataSet ds = SqlHelper.ExecuteDataset(this.strConnString,CommandType.Text,strSql,null);
			if (ds.Tables[0].Rows.Count > 0)
			{
				return ds.Tables[0];
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 检索某款车型
		/// </summary>
		/// <param name="productID"></param>
		/// <returns></returns>
		public DataTable GetProductInfoAndValue(string productID)
		{
			StringBuilder strBuilder = new StringBuilder();
			strBuilder.Append(" select");
			strBuilder.Append("      a.*");
			strBuilder.Append("     ,b.EffectiveDate");
			strBuilder.Append("     ,b.BasePriceDealer");
			strBuilder.Append("     ,b.BasePriceLow");
			strBuilder.Append("     ,b.BasePriceNormal");
			strBuilder.Append(" from TBL_Product a");
			strBuilder.Append(" left join TBL_Product_Price b on a.ProductID = b.ProductID");
			strBuilder.Append(" where a.ProductID = @ProductID");
			strBuilder.Append("     and EffectiveDate = (select convert(char(10),max(EffectiveDate),20) from TBL_Product_Price where ProductID=@ProductID and EffectiveDate<getdate())");

			String strSql = strBuilder.ToString();

			this.objTblProduct.ProductID = productID;
			SqlParameter[] param = this.getSqlParameter();
			DataSet ds = SqlHelper.ExecuteDataset(this.strConnString,CommandType.Text,strSql,param);
			if (ds.Tables[0].Rows.Count > 0)
			{
				return ds.Tables[0];
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 车型信息修改
		/// </summary>
		/// <param name="objTblProduct"></param>
		/// <returns></returns>
		public bool UpdateProductInfo(DataTblProduct objTblProduct)
		{
			StringBuilder strBuilder = new StringBuilder();
			strBuilder.Append(" update TBL_Product set");
			strBuilder.Append("      ModisCode = @ModisCode");
			strBuilder.Append("     ,ProductName = @ProductName");
			strBuilder.Append("     ,PreFixChassis = @PreFixChassis");
			strBuilder.Append("     ,PreFixEngine = @PreFixEngine");
			strBuilder.Append("     ,NetWeight = @NetWeight");
			strBuilder.Append("     ,GrossWeight = @GrossWeight");
			strBuilder.Append("     ,Measurement = @Measurement");
			strBuilder.Append("     ,Capacity = @Capacity");
			strBuilder.Append("     ,IsProduct = @IsProduct");
			strBuilder.Append(" where ProductID = @ProductID");

			string strSql = strBuilder.ToString();

			this.objTblProduct = objTblProduct;
			SqlParameter[] param = this.getSqlParameter();
			try
			{
				SqlHelper.ExecuteNonQuery(this.strConnString,CommandType.Text,strSql,param);
			}
			catch(SqlException ex)
			{
				return false;
			}

			return true;
			
		}
		
		/// <summary>
		/// 将数据模型对象映射到Command命令所用的SqlParameter对象
		/// </summary>
		/// <returns></returns>
		private SqlParameter[] getSqlParameter()
		{
			SqlParameter[] param = new SqlParameter[12];
			param[0]  = new SqlParameter("@ProductID",SqlDbType.VarChar);    
			param[1]  = new SqlParameter("@ModisCode",SqlDbType.VarChar);    
			param[2]  = new SqlParameter("@ProductName",SqlDbType.NVarChar);  
			param[3]  = new SqlParameter("@PreFixChassis",SqlDbType.VarChar);
			param[4]  = new SqlParameter("@PreFixEngine",SqlDbType.VarChar); 
			param[5]  = new SqlParameter("@NetWeight",SqlDbType.Int);    
			param[6]  = new SqlParameter("@GrossWeight",SqlDbType.Int);  
			param[7]  = new SqlParameter("@Measurement",SqlDbType.Decimal);  
			param[8]  = new SqlParameter("@Capacity",SqlDbType.Decimal);     
			param[9]  = new SqlParameter("@IsProduct",SqlDbType.Char);    
			param[10] = new SqlParameter("@PicPath1",SqlDbType.NVarChar);
			param[11] = new SqlParameter("@PicPath2",SqlDbType.NVarChar); 

			param[0].Value  = objTblProduct.ProductID;    
			param[1].Value  = objTblProduct.ModisCode;    
			param[2].Value  = objTblProduct.ProductName;  
			param[3].Value  = objTblProduct.PreFixChassis;
			param[4].Value  = objTblProduct.PreFixEngine; 
			param[5].Value  = ConvertType.GetNullableDBValue(objTblProduct.NetWeight);    
			param[6].Value  = ConvertType.GetNullableDBValue(objTblProduct.GrossWeight);  
			param[7].Value  = ConvertType.GetNullableDBValue(objTblProduct.Measurement);  
			param[8].Value  = ConvertType.GetNullableDBValue(objTblProduct.Capacity);     
			param[9].Value  = objTblProduct.IsProduct;    
			param[10].Value = objTblProduct.PicPath1;
			param[11].Value = objTblProduct.PicPath2;

			return param;
		}

		
	}
}