city.cs

工交车查询系统

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

namespace Sun188.iSBus.DataAccess
{
	/// <summary>
	/// City 的摘要说明。
	/// </summary>
	public class City
	{
		private OleDbConnection conn = null;
		private string provinceName;
		private string provinceDescription;
		private int provinceID;
		private string cityName;
		private string cityDescription;
		private int cityID;

		public City()
		{
			this.conn = new Conn().Connection();
		}

		#region 获取城市树型目录数据集======================
		/// <summary>
		/// 获取城市树形目录
		/// </summary>
		/// <returns>DataSet</returns>
		public DataSet GetCityTree()
		{
			try
			{
				string sql = "Select * from V_CityTree";							
				OleDbDataAdapter da = new OleDbDataAdapter(sql,this.conn);
				DataSet ds = new DataSet();
				da.Fill(ds,"City");				
				return ds;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}		
		#endregion

		#region 获取城市====================================
		/// <summary>
		/// 获取城市
		/// </summary>
		/// <returns>DataSet</returns>
		public DataSet GetCity()
		{
			try
			{
				string sql1 = "Select * from City";
				string sql2 = "Select * from Province";			
				OleDbDataAdapter da1 = new OleDbDataAdapter(sql1,this.conn);
				OleDbDataAdapter da2 = new OleDbDataAdapter(sql2,this.conn);
				DataSet ds = new DataSet();
				da1.Fill(ds,"City");
				da2.Fill(ds,"Province");
				return ds;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}

		/// <summary>
		/// 获取省份下的城镇列表
		/// </summary>
		/// <param name="provinceID">省份ID</param>
		/// <returns>DataSet</returns>
		public DataSet GetCity(int provinceID)
		{
			try
			{
				string sql1 = "Select * from City Where ProvinceID="+provinceID;					
				OleDbDataAdapter da = new OleDbDataAdapter(sql1,this.conn);				
				DataSet ds = new DataSet();
				da.Fill(ds,"City");				
				return ds;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}

		/// <summary>
		/// 根据城市名称取回城市ID
		/// </summary>
		/// <param name="cityName">CityName</param>
		/// <returns>int</returns>
		public int GetCityIDByCityName(string cityName)
		{
			int result = 0;
			try
			{
				string sql = "SELECT * FROM City WHERE CityName ='"+cityName+"'";
				OleDbCommand cmd = new OleDbCommand(sql,this.conn);
				//判断当前连接数据库的状态，如果连接关闭则打开
				if(conn.State!=ConnectionState.Open)
				{
					this.conn.Open();
				}
				OleDbDataReader dr = cmd.ExecuteReader();
				//读取数据
				if(dr.Read())
				{
					result = (int)dr["CityID"];
				}
				//关闭阅读器
				dr.Close();				
			}
			catch(System.Exception ex)
			{
				throw new Exception(ex.Message);
			}
			finally
			{
				//释放资源
				this.conn.Close();
				this.conn.Dispose();
			}
			return result;		
		}
		#endregion

		#region 获取省市====================================
		/// <summary>
		/// 获取省市
		/// </summary>
		/// <returns>DataSet</returns>
		public DataSet GetProvince()
		{
			try
			{
				string sql = "Select * from Province";								
				OleDbDataAdapter da = new OleDbDataAdapter(sql,this.conn);
				DataSet ds = new DataSet();
				da.Fill(ds,"Province");
				return ds;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}
		#endregion

		#region 搜索城市====================================
		/// <summary>
		/// 搜索城市
		/// </summary>
		/// <param name="cityName">城市名称</param>
		/// <returns>DataSet</returns>
		public DataSet GetCity(string cityName)
		{
			string sql = "";
			try
			{
				if(cityName == "")
				{
					sql = "Select Top 1 * from City order by CityID ASC";
				}
				else
				{
					sql = "Select * from City Where CityName='"+cityName+"'";
				}
				OleDbDataAdapter da = new OleDbDataAdapter(sql,this.conn);				
				DataSet ds = new DataSet();
				da.Fill(ds,"City");			
				return ds;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}
		#endregion

		#region 省市数据处理================================
		/// <summary>
		/// 新增省市资料
		/// </summary>
		/// <param name="name">名称</param>
		/// <param name="description">描述</param>
		/// <returns>true/false</returns>
		public bool AddNewProvince(string name,string description)
		{
			bool result = false;
			this.provinceName = name;
			this.provinceDescription = description;
			result = this.ProvinceProcess(PublicEnum.ProcessType.AddNew);
			return result;
		}

		/// <summary>
		/// 修改省市资料
		/// </summary>
		/// <param name="name">名称</param>
		/// <param name="description">描述</param>
		/// <param name="id">省市ID</param>
		/// <returns>true/false</returns>
		public bool ModifyProvince(string name,string description,int id)
		{
			bool result = false;
			this.provinceName = name;
			this.provinceDescription = description;
			this.provinceID = id;
			result = this.ProvinceProcess(PublicEnum.ProcessType.Modify);
			return result;
		}

		/// <summary>
		/// 删除省市资料
		/// </summary>
		/// <param name="provinceID">省市ID</param>
		/// <returns>true/false</returns>
		public bool DeleteProvince(int provinceID)
		{
			bool result = false;
			this.provinceID = provinceID;
			result = this.ProvinceProcess(PublicEnum.ProcessType.Delete);
			return result;
		}

		/// <summary>
		///数据处理
		/// </summary>
		/// <param name="processType">操作类型</param>
		/// <returns>true/false</returns>
		private bool ProvinceProcess(PublicEnum.ProcessType processType)
		{
			string sql = "";			
			bool result = false;

			switch(processType)
			{
				case PublicEnum.ProcessType.AddNew://新增
					sql = "insert into Province (ProvinceName,ProvinceDescription) values ('"+this.provinceName+"','"+this.provinceDescription+"')";
					break;
				case PublicEnum.ProcessType.Modify://修改
					sql = "Update Province Set ProvinceName='"+provinceName+"',ProvinceDescription='"+provinceDescription+"' Where ProvinceID="+this.provinceID+"";
					break;
				case PublicEnum.ProcessType.Delete://删除
					sql = "Delete * From Province Where ProvinceID="+this.provinceID+"";
					break;
			}

			//创建COMMAND
			OleDbCommand cmd = new OleDbCommand(sql,this.conn);
			//打开数据库连接
			if(conn.State != ConnectionState.Open)
			{
				this.conn.Open();
			}
			try
			{
				cmd.ExecuteNonQuery();
				result = true;
			}
			catch
			{
				result = false;
			}
			finally
			{
				this.conn.Close();
				this.conn.Dispose();
			}

			return result;

		}
		#endregion

		#region 城市数据处理================================
		/// <summary>
		/// 新增城市资料
		/// </summary>
		/// <param name="name">城市名称</param>
		/// <param name="description">城市描述</param>
		/// <param name="id">所属省市</param>
		/// <returns>true/false</returns>
		public bool AddNewCity(string name,string description,int id)
		{
			bool result = false;
			this.cityName = name;
			this.cityDescription = description;
			this.provinceID = id;
			result = this.CityProcess(PublicEnum.ProcessType.AddNew);
			return result;
		}

		/// <summary>
		/// 修改城市资料
		/// </summary>
		/// <param name="name">城市名称</param>
		/// <param name="description">城市描述</param>
		/// <param name="id">城市ID</param>
		/// <returns>true/false</returns>
		public bool ModifyCity(string name,string description,int id)
		{
			bool result = false;
			this.cityName = name;
			this.cityDescription = description;
			this.cityID = id;
			result = this.CityProcess(PublicEnum.ProcessType.Modify);
			return result;
		}

		/// <summary>
		/// 删除城市资料
		/// </summary>
		/// <param name="id">城市ID</param>
		/// <returns>true/false</returns>
		public bool DeleteCity(int id)
		{
			bool result = false;
			this.cityID = id;
			result = this.CityProcess(PublicEnum.ProcessType.Delete);
			return result;
		}

		/// <summary>
		///数据处理
		/// </summary>
		/// <param name="processType">操作类型</param>
		/// <returns>true/false</returns>
		private bool CityProcess(PublicEnum.ProcessType processType)
		{
			string sql = "";			
			bool result = false;

			switch(processType)
			{
				case PublicEnum.ProcessType.AddNew://新增
					sql = "insert into City (ProvinceID,CityName,CityDescription) values ("+this.provinceID+",'"+this.cityName+"','"+this.cityDescription+"')";
					break;
				case PublicEnum.ProcessType.Modify://修改
					sql = "Update City Set CityName='"+cityName+"',CityDescription='"+cityDescription+"' Where CityID="+this.cityID+"";
					break;
				case PublicEnum.ProcessType.Delete://删除
					sql = "Delete * From City Where CityID="+this.cityID+"";
					break;
			}

			//创建COMMAND
			OleDbCommand cmd = new OleDbCommand(sql,this.conn);
			//打开数据库连接
			if(conn.State != ConnectionState.Open)
			{
				this.conn.Open();
			}
			try
			{
				cmd.ExecuteNonQuery();
				result = true;
			}
			catch
			{
				result = false;
			}
			finally
			{
				this.conn.Close();
				this.conn.Dispose();
			}

			return result;

		}
		#endregion

	}
}