bus.cs

工交车查询系统

using System;
using System.Data;
using System.Data.OleDb;
using Sun188.iSBus.Common;

namespace Sun188.iSBus.DataAccess
{
	/// <summary>
	/// Bus 的摘要说明。
	/// </summary>
	public class Bus
	{
		#region 字段
		//每页记录的大小
		private int pageSize = 40;
		//总的记录数
		private int totalRecordCount = 0;
		//总页数
		private int totalPageCount = 0;
		//汽车名称
		private string mBusName;
		//汽车类型
		private string mBusType;
		//起点站
		private string mBusStartStop;
		//终点站
		private string mBusEndStop;
		//途经站点
		private string mBusDescription;
		//汽车ID
		private int mBusID;
		//城市ID
		private int mCityID;
		#endregion

		#region 构造函数
		public Bus()
		{
			
		}
		#endregion

		#region 根据BUSID返回数据集===========================================
		/// <summary>
		/// 根据BUSID返回数据集
		/// </summary>
		/// <param name="busID">BusID</param>
		/// <returns>DATASET</returns>
		public DataSet GetBusDataset(int busID)
		{
			//定义SQL语句
			string sql = "Select * from Bus Where BusID = "+busID;			
			//创建数据库连接实例
			OleDbConnection conn = new Conn().Connection();
			//创建数据库适配器
			OleDbDataAdapter da = new OleDbDataAdapter(sql,conn);
			//创建DATASET实例
			DataSet ds = new DataSet();					
			try
			{				
				da.Fill(ds);
				return ds;
			}
			catch(Exception ex)
			{	
				throw new Exception(ex.ToString());
			}
			finally
			{
				//释放资源
				conn.Close();
				conn.Dispose();
				ds.Dispose();
				da.Dispose();				
			}			
		}
		#endregion

		#region 根据CityID返回数据集==========================================
		/// <summary>
		/// 根据BUSID返回数据集
		/// </summary>
		/// <param name="cityID">城市ID</param>
		/// <returns>DATASET</returns>
		public DataSet GetBusData(int cityID)
		{
			//定义SQL语句
			string sql = "Select * from Bus Where CityID = "+cityID;			
			//创建数据库连接实例
			OleDbConnection conn = new Conn().Connection();
			//创建数据库适配器
			OleDbDataAdapter da = new OleDbDataAdapter(sql,conn);
			//创建DATASET实例
			DataSet ds = new DataSet();					
			try
			{				
				da.Fill(ds);
				return ds;
			}
			catch(Exception ex)
			{	
				throw new Exception(ex.ToString());
			}
			finally
			{
				//释放资源
				conn.Close();
				conn.Dispose();
				ds.Dispose();
				da.Dispose();				
			}			
		}
		#endregion

		#region 根据浏览类型返回数据集========================================
		/// <summary>
		/// 根据CITYID、浏览类型、BUSID返回数据集
		/// </summary>
		/// <param name="mCityID">城市ID</param>
		/// <param name="mBrowseType">浏览类</param>
		/// <param name="mLastBusID">上笔记录最后一条记录的BUSID</param>
		/// <returns>DATASET</returns>
		public DataSet GetBusDataset(int mCityID,DataBrowseEnum.BrowseEnum mBrowseType,int mLastBusID)
		{
			//定义SQL语句
			string sql = this.GetSql(mCityID,mBrowseType,mLastBusID);			
			//创建数据库连接实例
			OleDbConnection conn = new Conn().Connection();
			//创建数据库适配器
			OleDbDataAdapter da = new OleDbDataAdapter(sql,conn);
			//创建DATASET实例
			DataSet ds = new DataSet();					
			try
			{				
				da.Fill(ds);
				return ds;
			}
			catch(Exception ex)
			{	
				throw new Exception(ex.ToString());
			}
			finally
			{
				//释放资源
				conn.Close();
				conn.Dispose();
				ds.Dispose();
				da.Dispose();
				//统计当前城市下公共汽车的信息
				this.StatInfo(mCityID);	
			}
			
		}
		#endregion

		#region 根据浏览类型生成相应的SQL语句=================================
		/// <summary>
		/// 根据浏览类型生成相应的SQL语句
		/// </summary>
		/// <param name="mBrowseType">浏览类型</param>
		/// <returns>string</returns>
		private string GetSql(int mCityID,DataBrowseEnum.BrowseEnum mBrowseType,int mLastBusID)
		{
			string sql = "";
			//根据浏览类型生成相应的SQL语句
			switch(mBrowseType)
			{
				case DataBrowseEnum.BrowseEnum.First:
					sql = @"Select Top "+pageSize+" * from Bus Where CityID = "+mCityID+" order by BusID ASC";
					break;
				case DataBrowseEnum.BrowseEnum.Prv:
					sql = @"Select Top "+pageSize+" * from Bus Where CityID = "+mCityID+" and BusID < "+mLastBusID+" order by BusID DESC";
					break;
				case DataBrowseEnum.BrowseEnum.Next:
					sql = @"Select Top "+pageSize+" * from Bus Where CityID = "+mCityID+" and BusID > "+mLastBusID+" order by BusID ASC";
					break;
				case DataBrowseEnum.BrowseEnum.Last:
					sql = @"Select Top "+pageSize+" * from Bus Where CityID = "+mCityID+" order by BusID DESC";
					break;
			}
			return sql;
		}
		#endregion

		#region 统计当前城市下公共汽车的信息==================================
		/// <summary>
		/// 统计当前城市下公共汽车的信息
		/// </summary>
		/// <param name="cityID">城市ID</param>
		private void StatInfo(int cityID)
		{
			string sql = "Select Count(BusID) from Bus Where CityID = "+cityID+"";			
			OleDbConnection conn = new Conn().Connection();
			OleDbCommand cmd = new OleDbCommand(sql,conn);
			conn.Open();
			try
			{
				OleDbDataReader dr = cmd.ExecuteReader();
				if(dr.Read())
				{
					this.totalRecordCount = Convert.ToInt32(dr[0].ToString());
				}
			}
			catch(Exception ex)
			{
				throw new Exception(ex.ToString());
			}

			if((totalRecordCount%pageSize) > 0)
			{
				this.totalPageCount = totalRecordCount/pageSize + 1;
			}
			else
			{
				this.totalPageCount = totalRecordCount/pageSize;
			}
			
		}

		#endregion

		#region 根据起始站终点站搜索某城市下公共汽车的信息====================
		/// <summary>
		/// 根据起始站终点站搜索某城市下公共汽车的信息
		/// </summary>
		/// <param name="cityID">城市ID</param>
		/// <param name="startStopName">起点站</param>
		/// <param name="endStopName">终点站</param>
		/// <returns>DataSet</returns>
		public DataSet SearchBus(int cityID,string startStopName,string endStopName)
		{
			 string strQuery =@"SELECT DISTINCTROW * From Bus"
							+" Where BusID in "
							+" (SELECT DISTINCTROW BusStop.BusID FROM Stop INNER JOIN BusStop ON Stop.StopID = BusStop.StopID WHERE Stop.StopName like '%"+startStopName+"%' and CityID ="+cityID+")"
							+" and BusID in"
							+" (SELECT DISTINCTROW BusStop.BusID FROM Stop INNER JOIN BusStop ON Stop.StopID = BusStop.StopID WHERE Stop.StopName like '%"+endStopName+"%' and CityID ="+cityID+")";
			OleDbConnection conn = new Conn().Connection();
			OleDbDataAdapter da = new OleDbDataAdapter(strQuery,conn);
			DataSet ds = new DataSet();
			da.Fill(ds);
			return ds;			
		}

		#endregion

		#region [汽车管理]数据处理============================================
		/// <summary>
		/// 新增汽车资料
		/// </summary>
		/// <param name="busName">汽车名</param>
		/// <param name="busType">汽车类型</param>
		/// <param name="busStartStop">起点站</param>
		/// <param name="busEndStop">终点站</param>
		/// <param name="busDescription">途经站点</param>
		/// <returns>true/false</returns>
		public bool AddNewBus(string busName,string busType,string busStartStop,string busEndStop,string busDescription,int cityID)
		{
			//返回结果
			bool result = false;

			//字段赋值
			this.mCityID = cityID;
			this.mBusName = busName;
			this.mBusType = busType;
			this.mBusStartStop = busStartStop;
			this.mBusEndStop = busEndStop;
			this.mBusDescription = busDescription;

			//处理结果
			result = this.BusProcess(PublicEnum.ProcessType.AddNew);
			return result;
		}

		/// <summary>
		/// 修改汽车资料
		/// </summary>
		/// <param name="busName">汽车名</param>
		/// <param name="busType">汽车类型</param>
		/// <param name="busStartStop">起点站</param>
		/// <param name="busEndStop">终点站</param>
		/// <param name="busDescription">途经站点</param>
		/// <param name="busID">汽车ID</param>
		/// <returns>true/false</returns>
		public bool ModifyBus(string busName,string busType,string busStartStop,string busEndStop,string busDescription,int busID)
		{
			//返回结果
			bool result = false;

			//字段赋值
			this.mBusName = busName;
			this.mBusType = busType;
			this.mBusStartStop = busStartStop;
			this.mBusEndStop = busEndStop;
			this.mBusDescription = busDescription;
			this.mBusID = busID;

			//处理结果
			result = this.BusProcess(PublicEnum.ProcessType.Modify);
			return result;
		}

		/// <summary>
		/// 删除汽车资料
		/// </summary>
		/// <param name="busID">汽车ID</param>
		/// <returns>true/false</returns>
		public bool DeleteBus(int busID)
		{
			//返回结果
			bool result = false;
			//字段赋值			
			this.mBusID = busID;
			//处理结果
			result = this.BusProcess(PublicEnum.ProcessType.Delete);
			return result;
		}

		/// <summary>
		/// [汽车管理]数据处理
		/// </summary>
		/// <param name="processType">操作类型</param>
		/// <returns>true/false</returns>
		private bool BusProcess(PublicEnum.ProcessType processType)
		{
			//返回结果
			bool result = false;
			//SQL语句
			string sql = "";			

			switch(processType)
			{
				case PublicEnum.ProcessType.AddNew://新增
					sql =@"INSERT INTO Bus (CityID,BusName,BusType,BusStartStop,BusEndStop,BusDescription) VALUES ("+this.mCityID+",'"+this.mBusName+"','"+this.mBusType+"','"+this.mBusStartStop+"','"+this.mBusEndStop+"','"+this.mBusDescription+"')"; 
					break;
				case PublicEnum.ProcessType.Modify://修改
					sql =@"UPDATE Bus SET BusName='"+this.mBusName+"',BusType='"+this.mBusType+"',BusStartStop='"+this.mBusStartStop+"',BusEndStop='"+this.mBusEndStop+"',BusDescription='"+this.mBusDescription+"'WHERE BusID="+this.mBusID; 
					break;
				case PublicEnum.ProcessType.Delete://删除
					sql =@"DELETE * FROM Bus WHERE BusID="+this.mBusID+"";
					break;
			}

			//创建数据库连接实例
			OleDbConnection conn = new Common.Conn().Connection();
			//创建COMMAND实例
			OleDbCommand cmd = new OleDbCommand(sql,conn);
			if(conn.State != ConnectionState.Open)
			{
				conn.Open();
			}
			try
			{
				cmd.ExecuteNonQuery();
				result = true;
			}
			catch
			{
				result = false;
			}
			finally
			{
				//关闭数据库连接释放资源
				conn.Close();
				conn.Dispose();
			}
			return result;
		}
		
		#endregion

		#region 属性定义区====================================================
		/// <summary>
		/// 每页的大小
		/// </summary>
		public int PageSize
		{
			get{return this.pageSize;}
		}
		/// <summary>
		/// 总的记录数
		/// </summary>
		public int TotalRecordCount
		{
			get{return this.totalRecordCount;}
		}
		/// <summary>
		/// 总的页数
		/// </summary>
		public int TotalPageCount
		{
			get{return this.totalPageCount;}
		}
		
		#endregion

	}
}