bookdata.cs

一个web电子商务系统（我的毕业设计）

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

namespace Data.BLL
{
	public class BookData
	{
		SqlConnection con;
		public BookData()
		{
			con=new SqlConnection("server=127.0.0.1;database=BOOKSHOP;user id=sa;password=;");
		}

		/// <summary>
		/// 获得数据模型
		/// </summary>
		/// <param name="cBookName">表主键</param>
		/// <returns></returns>
		public Data.Model.BookData getModel(string cBookName)
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器

			strSql.AppendFormat("select * from BookData where cBookName='{0}'",cBookName);//向字符容器里添加一条 SQL 语句
			SqlDataAdapter adapter=new SqlDataAdapter(strSql.ToString(),con);
			DataSet ds=new DataSet();
			DataTable dt=new DataTable();
			adapter.Fill(ds,"BookData");
			dt=ds.Tables["BookData"];

			Data.Model.BookData model=new Data.Model.BookData();//实例化此数据表的模型

			if(dt.Rows.Count>0)
			{//有数据则向模型里存入数据
				model.cBookName=dt.Rows[0]["cBookName"].ToString();
				model.mPrice=double.Parse(dt.Rows[0]["mPrice"].ToString());
				model.cEditor=dt.Rows[0]["cEditor"].ToString();
				model.cBookType=dt.Rows[0]["cBookType"].ToString();
				model.cISBN=dt.Rows[0]["cISBN"].ToString();
				model.cPublisher=dt.Rows[0]["cPublisher"].ToString();
				model.mCheapPrice=double.Parse(dt.Rows[0]["mCheapPrice"].ToString());
				model.cImagePath=dt.Rows[0]["cImagePath"].ToString();
				model.iQuantity=int.Parse(dt.Rows[0]["iQuantity"].ToString());
				model.iSaleQty=int.Parse(dt.Rows[0]["iSaleQty"].ToString());
				model.cStatus=dt.Rows[0]["cStatus"].ToString();
				model.cDescription=dt.Rows[0]["cDescription"].ToString();

			}
			con.Close();

			return model;
		}


		/// <summary>
		/// 添加数据行
		/// </summary>
		/// <param name="model">要添加表的模型</param>
		public bool add(Data.Model.BookData model)
		{
			StringBuilder strSql=new StringBuilder();

			strSql.Append("insert into BookData values(");
			strSql.AppendFormat("'{0}'",model.cBookName);
			strSql.AppendFormat(",{0}",model.mPrice);
			strSql.AppendFormat(",'{0}'",model.cEditor);
			strSql.AppendFormat(",'{0}'",model.cBookType);
			strSql.AppendFormat(",'{0}'",model.cISBN);
			strSql.AppendFormat(",'{0}'",model.cPublisher);
			strSql.AppendFormat(",{0}",model.mCheapPrice);
			strSql.AppendFormat(",'{0}'",model.cImagePath);
			strSql.AppendFormat(",{0}",model.iQuantity);
			strSql.AppendFormat(",{0}",model.iSaleQty);
			strSql.AppendFormat(",'{0}'",model.cStatus);
			strSql.AppendFormat(",'{0}')",model.cDescription);

			return this.Execute(strSql.ToString());//调用执行SQL命令的函数并返回结果true or false
		}

		/// <summary>
		/// 删除数据表行
		/// </summary>
		/// <param name="cUserID">数据表主键</param>
		public bool delete(string cBookName)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.AppendFormat("delete BookData where cBookName='{0}'",cBookName);

			return this.Execute(strSql.ToString());//调用执行SQL命令的函数并返回结果true or false
		}

		/// <summary>
		/// 更新数据表行
		/// </summary>
		/// <param name="model">要更新表模型</param>
		public bool update(Data.Model.BookData model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update BookData set ");
			strSql.AppendFormat("mPrice={0}",model.mPrice);
			strSql.AppendFormat(",cEditor='{0}'",model.cEditor);
			strSql.AppendFormat(",cBookType='{0}'",model.cBookType);
			strSql.AppendFormat(",cISBN='{0}'",model.cISBN);
			strSql.AppendFormat(",cPublisher='{0}'",model.cPublisher);
			strSql.AppendFormat(",mCheapPrice={0}",model.mCheapPrice);
			strSql.AppendFormat(",cImagePath='{0}'",model.cImagePath);
			strSql.AppendFormat(",iQuantity={0}",model.iQuantity);
			strSql.AppendFormat(",iSaleQty={0}",model.iSaleQty);
			strSql.AppendFormat(",cStatus='{0}'",model.cStatus);
			strSql.AppendFormat(",cDescription='{0}'",model.cDescription);
			strSql.AppendFormat(" where cBookName='{0}'",model.cBookName);

			return this.Execute(strSql.ToString());//调用执行SQL命令的函数并返回结果true or false
		}

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="strSql">SQL语句</param>
		/// <returns></returns>
		public bool Execute(string strSql)
		{
			bool b=false;
			try
			{
				con.Open();
				SqlCommand command=new SqlCommand(strSql.ToString(),con);
				command.ExecuteNonQuery();
				con.Close();
				b=true;
			}
			catch(Exception e)
			{
			}
			finally
			{
				con.Close();
			}
			return b;
		}

		/// <summary>
		/// 获得所有的"作者"或"书本类型"或"出版社"列表
		/// </summary>
		/// <param name="cField">字段名</param>
		/// <param name="cTable">表名</param>
		/// <returns></returns>
		public ArrayList getEBPList(string cField,string cTable)
		{
			con.Open();

			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器

			strSql.AppendFormat("select DISTINCT {0} from {1}",cField,cTable);//向字符容器里添加一条 SQL 语句
			SqlCommand command=new SqlCommand(strSql.ToString(),con);
			SqlDataReader read=command.ExecuteReader();

			ArrayList list=new ArrayList();

			while(read.Read())
			{
				list.Add(read[cField].ToString());//将数据加入数组列表里
			}

			con.Close();

			return list;
		}

		/// <summary>
		/// 获得表的所有主键列表
		/// </summary>
		/// <returns></returns>
		public ArrayList getPrimaryKeyList()
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器

			strSql.Append("select cBookName from BookData");
//			strSql.Append("select top {0} cBookName from BookData where cBookName not in (select top {1} cBookName from BookData)",iStartRow,iRowNum);

			SqlCommand command=new SqlCommand(strSql.ToString(),con);
			SqlDataReader read=command.ExecuteReader();

			ArrayList list=new ArrayList();

			while(read.Read())
			{
				list.Add(read[0].ToString());//将数据加入数组列表里
			}

			con.Close();

			return list;
		}

		/// <summary>
		/// 获得特定要求的所有主键列表
		/// </summary>
		/// <param name="cFieldName">字段名</param>
		/// <param name="cFieldValue">字段值</param>
		/// <returns></returns>
		public ArrayList getPrimaryKeyList(string cFieldName,string cFieldValue)
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器

			strSql.AppendFormat("select cBookName from BookData where {0}='{1}'",cFieldName,cFieldValue);
			//			strSql.Append("select top {0} cBookName from BookData where cBookName not in (select top {1} cBookName from BookData)",iStartRow,iRowNum);

			SqlCommand command=new SqlCommand(strSql.ToString(),con);
			SqlDataReader read=command.ExecuteReader();

			ArrayList list=new ArrayList();

			while(read.Read())
			{
				list.Add(read[0].ToString());//将数据加入数组列表里
			}

			con.Close();

			return list;
		}
	}
}