orders.cs

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

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


namespace Data.BLL
{
	/// <summary>
	/// 定单表 操作
	/// </summary>
	public class Orders
	{
		SqlConnection con;//定义一个连接对象
		
		#region 构造函数 连接数据库
		public Orders()
		{
			con=new SqlConnection("server=127.0.0.1;database=BOOKSHOP;user id=sa;password=;");
		}
		#endregion

		#region 创建模型
		/// <summary>
		/// 得到模型
		/// </summary>
		/// <param name="cOrderID">Orders 主键</param>
		/// <returns>model</returns>
		public Data.Model.Orders getModel(string cOrderID,string cBookName)
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器
			strSql.AppendFormat("select * from Orders where cOrderID='{0}' and cBookName='{1}'",cOrderID,cBookName);//定义一个sql语句 添加到字符串容器中
			SqlDataAdapter adapter=new SqlDataAdapter(strSql.ToString(),con);//创建一个数据适配器
			DataSet ds=new DataSet();//创建数据集
			DataTable dt=new DataTable();//创建数据表
			adapter.Fill(ds,"Orders");//填充数据
			dt=ds.Tables["Orders"];

			Data.Model.Orders model=new Data.Model.Orders();//实例化一个模型

			if(dt.Rows.Count>0)//判断是否存在值
			{
				model.cOrderID=dt.Rows[0]["cOrderID"].ToString();
				model.cBookName=dt.Rows[0]["cBookName"].ToString();
				model.mPrice=double.Parse(dt.Rows[0]["mPrice"].ToString());
				model.iQuantity=int.Parse(dt.Rows[0]["iQuantity"].ToString());
				model.mAllPrice=double.Parse(dt.Rows[0]["mAllPrice"].ToString());
				model.dOrderDate=DateTime.Parse(dt.Rows[0]["dOrderDate"].ToString());//数据表单的字段放入模型model中
			}
			con.Close();//关闭连接

			return model;//返回model
		}

		public Data.Model.Orders getModels(string cOrderID,string cUserID)
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器
			strSql.AppendFormat("select * from Orders where cOrderID='{0}' and cUserID='{1}'",cOrderID,cUserID);//定义一个sql语句 添加到字符串容器中
			SqlDataAdapter adapter=new SqlDataAdapter(strSql.ToString(),con);//创建一个数据适配器
			DataSet ds=new DataSet();//创建数据集
			DataTable dt=new DataTable();//创建数据表
			adapter.Fill(ds,"Orders");//填充数据
			dt=ds.Tables["Orders"];

			Data.Model.Orders model=new Data.Model.Orders();//实例化一个模型

			if(dt.Rows.Count>0)//判断是否存在值
			{
				model.cOrderID=dt.Rows[0]["cOrderID"].ToString();
				model.cBookName=dt.Rows[0]["cBookName"].ToString();
				model.mPrice=double.Parse(dt.Rows[0]["mPrice"].ToString());
				model.iQuantity=int.Parse(dt.Rows[0]["iQuantity"].ToString());
				model.mAllPrice=double.Parse(dt.Rows[0]["mAllPrice"].ToString());
				model.dOrderDate=DateTime.Parse(dt.Rows[0]["dOrderDate"].ToString());//数据表单的字段放入模型model中
			}
			con.Close();//关闭连接

			return model;//返回model
		}

		#endregion

		#region 向Orders表中添加记录
		/// <summary>
		/// 添加操作
		/// </summary>
		/// <param name="model"></param>
		public bool add(Data.Model.Orders model)
		{
			StringBuilder strSql=new StringBuilder();

			strSql.Append("insert into Orders values(");
			strSql.AppendFormat("'{0}',",model.cOrderID);
			strSql.AppendFormat("'{0}',",model.cBookName);
			strSql.AppendFormat("'{0}',",model.cUserID);
			strSql.AppendFormat("{0},",model.mPrice);
			strSql.AppendFormat("{0},",model.iQuantity);
			strSql.AppendFormat("{0},",model.mAllPrice);
			strSql.AppendFormat("'{0}')",model.dOrderDate);//格式化sql字符串

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

		#region 从Orders表中删除记录
		/// <summary>
		/// 删除操作
		/// </summary>
		/// <param name="cOrderID">根据主键删除记录</param>
		public bool delete(string cOrderID,string cBookName)
		{
			StringBuilder strSql=new StringBuilder();

			strSql.AppendFormat("delete Orders where cOrderID='{0}' and cBookName='{1}'",cOrderID,cBookName);

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

		/// <summary>
		/// 根据用户名删除定单
		/// </summary>
		/// <param name="cUserID"></param>
		/// <returns></returns>
		public bool delete(string cUserID)
		{
			StringBuilder strSql=new StringBuilder();

			strSql.AppendFormat("delete Orders where cUserID='{0}'",cUserID);

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

		public bool deletes(string cBookName)
		{
			StringBuilder strSql=new StringBuilder();

			strSql.AppendFormat("delete Orders where cBookName='{0}'",cBookName);

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

		#endregion

		#region 更新Orders记录
		/// <summary>
		/// 更新操作
		/// </summary>
		/// <param name="model"></param>
		public bool update(Data.Model.Orders model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update Orders set ");
			strSql.AppendFormat("cBookName='{0}'",model.cBookName);
			strSql.AppendFormat(",cUserID='{0}'",model.cUserID);
			strSql.AppendFormat(",mPrice={0}",model.mPrice);
			strSql.AppendFormat(",iQuantity={0}",model.iQuantity);
			strSql.AppendFormat(",mAllPrice={0}",model.mAllPrice);
			strSql.AppendFormat(",dOrderDate='{0}'",model.dOrderDate);
			strSql.AppendFormat(" where cOrderID='{0}'",model.cOrderID);

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

		#region  执行SQL

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="strSql">SQL语句</param>
		/// <returns></returns>
		public bool Execute(string strSql)
		{
			bool b=false;

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

			try
			{
				con.Open();
				command.ExecuteNonQuery();
				con.Close();
				b=true;
			}
			catch(Exception e)
			{
			}
			finally
			{
				con.Close();
			}

			return b;
		}

		#endregion

		#region 得到数据库表 Orders 主键
		public ArrayList getPrimaryKeyList()
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select cOrderID,cBookName from Orders ");
			
			SqlCommand cmd=new SqlCommand(strSql.ToString(),con);
			SqlDataReader read=cmd.ExecuteReader();

			ArrayList list=new ArrayList();
			while(read.Read())
			{
				list.Add(read[0].ToString());
				list.Add(read[1].ToString());
			}
			con.Close();
			return list;

		}
		#endregion 

		#region 得到特定用户复合主键

		public ArrayList getOrder(string user)
		{
			con.Open();
			StringBuilder strSql=new StringBuilder();
			strSql.AppendFormat("select cOrderID,cBookName from Orders where cUserID='{0}'",user);
			
			SqlCommand cmd=new SqlCommand(strSql.ToString(),con);
			SqlDataReader read=cmd.ExecuteReader();

			ArrayList list=new ArrayList();
			while(read.Read())
			{
				list.Add(read[0].ToString());
				list.Add(read[1].ToString());
			}
			con.Close();
			return list;
		}

		#endregion

		#region 获得是最大的定单ID号

		/// <summary>
		/// 得到最大的定单ID号
		/// </summary>
		/// <returns></returns>
		public int getMaxOrderID()
		{
			con.Open();

			StringBuilder strSql=new StringBuilder();//实例化一个字符串容器
			strSql.Append("select cOrderID=max(cOrderID) from Orders");
			SqlCommand command=new SqlCommand(strSql.ToString(),con);
			SqlDataReader read=command.ExecuteReader();

			int Max=0;

			while(read.Read())
			{
				if(read.GetValue(0).ToString()=="null"||read.GetValue(0).ToString()=="")
				{
				}
				else
				{
					Max=int.Parse(read[0].ToString());
				}
			}

			con.Close();

			return Max;
		}

		#endregion
	}
}