bisproductoption.cs

一个网页的系统例子

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

namespace DataAccessLibrary
{
	/// <summary>
	/// BisProductOption 的摘要说明。
	/// </summary>
	public class BisProductOption : BisBase
	{
		

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

		public DataTable GetProductOption(string productID) 
		{
			SqlParameter[] param = new SqlParameter[1];
			param[0]  = new SqlParameter("@ProductID",SqlDbType.VarChar); 
			param[0].Value = productID;

			StringBuilder strBuilder = new StringBuilder();
			strBuilder.Append(" select ");
			strBuilder.Append("      a.ProductID");
			strBuilder.Append("     ,a.OptionID");
			strBuilder.Append("     ,b.ModisCode");
			strBuilder.Append("     ,convert(char(10),a.EffectiveDate,20) as EffectiveDate");
			strBuilder.Append("     ,a.OptionType");
			strBuilder.Append("     ,a.OptionPriceDealer");
			strBuilder.Append("     ,a.OptionPriceNormal");
			strBuilder.Append("     ,b.Description_ENG");
			strBuilder.Append("     ,b.Description_CHN");
			strBuilder.Append("     ,(select count(1) from MST_OptionColorMaterial where OptionID=a.OptionID) as ColorMaterialCount");
			strBuilder.Append(" from TBL_Product_OptionPrice a");
			strBuilder.Append(" left join MST_Option b on a.OptionID = b.OptionID");
			strBuilder.Append(" where EffectiveDate = (select convert(char(10),max(EffectiveDate),20) from TBL_Product_OptionPrice where ProductID=@ProductID and EffectiveDate<getdate())");
			strBuilder.Append("     and ProductID=@ProductID");
			strBuilder.Append(" order by OptionType");

			string strSql = strBuilder.ToString();

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