dataaccessor.cs

来自「ASP.NET的一些开发实例,有论坛管理系统等」· CS 代码 · 共 530 行

using System;
using System.Data;
using System.Data.OleDb;
using Service;

namespace Service
{
	/// <summary>
	/// OledbDataAccess 的摘要说明。
	/// </summary>
	public class DataAccessor
	{
		private static string ConStr;
		private System.Data.OleDb.OleDbConnection Conn;
		public	System.Data.OleDb.OleDbCommand Cmd;
		private System.Data.OleDb.OleDbTransaction Trans;
		public bool Result;

		/// <summary>
		/// 构造方法
		/// </summary>
		public DataAccessor()
		{
			if(ConStr == null)
			{
				ConStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
			}
			Conn = new System.Data.OleDb.OleDbConnection();
			Conn.ConnectionString = ConStr;
			Cmd = Conn.CreateCommand();

			Result = false;
		}

		/// <summary>
		/// 析构方法
		/// </summary>
		~DataAccessor()
		{
			if(this == null)
				return;
			this.Conn.Dispose();
		}

		/// <summary>
		/// 连接状态属性 State
		/// </summary>
		private System.Data.ConnectionState State
		{
			get
			{
				return Conn.State;
			}
		}


		/// <summary>
		/// Cmd字段的CommandType属性
		/// </summary>
		public System.Data.CommandType CmdType
		{
			get
			{
				return Cmd.CommandType;
			}
			set
			{
				Cmd.CommandType = value;
			}
		}

		
		/// <summary>
		/// 查询字符串属性 Sql
		/// </summary>
		public string Sql
		{
			get
			{
				return Cmd.CommandText;
			}
			set
			{
				Cmd.CommandText = value;
			}
		}




		/// <summary>
		/// 打开数据库连接
		/// </summary>
		/// <returns></returns>
		public bool Open()
		{
			try
			{
				Conn.Open();
				Trans = Conn.BeginTransaction();

				Cmd.Transaction = Trans;
				return true;
			}
			catch
			{
				return false;
			}
		}



		/// <summary>
		/// 提交或回滚事务并关闭数据库连接，所有使用本对象的方法最后必须执行此方法
		/// </summary>
		/// <returns></returns>
		public bool Close()
		{
			try
			{
				//提交或回滚事务
				if (this.Result == true)
				{
					Trans.Commit();
				}
				else
				{
					Trans.Rollback();
				}

				//关闭数据库连接
				if(Conn.State == System.Data.ConnectionState.Open)
				{
					Conn.Close();
				}
				return true;
			}
			catch
			{
				return false;
			}
		}



		/// <summary>
		/// 执行Cmd对象的ExecuteNonQuery()方法
		/// </summary>
		/// <returns></returns>
		public int ExecuteNonQuery()
		{
			//如果未打开连接则先打开连接
			if (this.State == System.Data.ConnectionState.Closed)
			{
				this.Open();
			}

			try
			{
				this.Result = true;
				return Cmd.ExecuteNonQuery();
			}
			catch
			{
				this.Result = false;
				return -1;
			}
			finally
			{
				Cmd.Parameters.Clear();
				CmdType = System.Data.CommandType.Text;
			}
		}





		/// <summary>
		/// 执行Cmd对象的ExecuteNonQuery()方法
		/// </summary>
		/// <param name="sql">执行数据库操作的SQL语句</param>
		/// <returns>返回受影响的行数，如果产生异常则返回-1</returns>
		public int ExecuteNonQuery(string sql)
		{
			//如果未打开连接则先打开连接
			if (this.State == System.Data.ConnectionState.Closed)
			{
				this.Open();
			}

			try
			{
				this.Sql = sql;
				this.Result = true;
				return Cmd.ExecuteNonQuery();
			}
			catch
			{
				this.Result = false;
				return -1;
			}
			finally
			{
				Cmd.Parameters.Clear();
				CmdType = System.Data.CommandType.Text;
			}
		}




		/// <summary>
		/// 执行Cmd对象的ExecuteReader()方法
		/// </summary>
		/// <returns></returns>
		public System.Data.OleDb.OleDbDataReader ExecuteReader()
		{
			//如果未打开连接则先打开连接
			if (this.State == System.Data.ConnectionState.Closed)
			{
				this.Open();
			}

			try
			{
				this.Result = true;
				return Cmd.ExecuteReader();
			}
			catch
			{
				this.Result = false;
				return null;
			}
			finally
			{
				Cmd.Parameters.Clear();
				CmdType = System.Data.CommandType.Text;
			}
		}



		/// <summary>
		/// 执行Cmd对象的ExecuteScalar()方法
		/// </summary>
		/// <returns></returns>
		public object ExecuteScalar()
		{
			//如果未打开连接则先打开连接
			if (this.State == System.Data.ConnectionState.Closed)
			{
				this.Open();
			}

			try
			{
				this.Result = true;
				return Cmd.ExecuteScalar();
			}
			catch
			{
				this.Result = false;
				return null;
			}
			finally
			{
				Cmd.Parameters.Clear();
				CmdType = System.Data.CommandType.Text;
			}
		}


		/// <summary>
		/// 执行Cmd对象的ExecuteScalar()方法
		/// </summary>
		/// <returns></returns>
		public object ExecuteScalar(string sql)
		{
			Sql = sql;
			return ExecuteScalar();
		}



		/// <summary>
		/// 查询SQL语句返回的结果个数
		/// </summary>
		/// <param name="column">列名</param>
		/// <param name="table">表名</param>
		/// <param name="condition">查询条件</param>
		/// <returns>结果个数</returns>
		public int ExecuteCount(string column, string table, string condition)
		{
			Sql = "Select count(" + column + ") From " + table + " Where " + condition;
			return int.Parse(this.ExecuteScalar().ToString());
		}
		

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <returns>DataTable类型的结果集</returns>
		public System.Data.DataTable ExecuteDataTable(string sql)
		{
			//如果未打开连接则先打开连接
			if (this.State == System.Data.ConnectionState.Closed)
			{
				this.Open();
			}

			//声明并创建一个用于保存数据的DataTable对象
			DataTable  theDT = new DataTable();

			try
			{
				//创建数据适配器对象
				this.Sql = sql;
				System.Data.OleDb.OleDbDataAdapter OleDA = new OleDbDataAdapter(this.Cmd);

				//调用数据适配器对象的Fill方法，将查询结果
				OleDA.Fill(theDT);

				//调用完毕，将数据管理器对象的Result属性置为true
				this.Result = true;

				return theDT;
			}
			catch(Exception e)
			{
				this.Result = false;

				Console.Write(e.Message);
				return null;
			}
			finally
			{
				Cmd.Parameters.Clear();
				CmdType = System.Data.CommandType.Text;
			}
		}


		/// <summary>
		/// 查询一条记录
		/// </summary>
		/// <param name="sql">查询语句</param>
		/// <returns>查询结果记录</returns>
		public DataRow ExecuteRecord(string sql)
		{
			return this.ExecuteDataTable(sql).Rows[0];
		}


		/// <summary>
		/// 查询新的主键值
		/// </summary>
		/// <param name="table">表名</param>
		/// <param name="key">主键列名</param>
		/// <returns>新的主键值</returns>
		public int GetNewID(string table, string key)
		{
			string sql = "Select Max("+ key + ")+1 From " + table;
			object result = this.ExecuteScalar(sql);
			if(result == DBNull.Value)
			{
				return 1;
			}
			else
			{
				return int.Parse(result.ToString());
			}
		}


		/// <summary>
		/// 查询一个值是否在数据库中
		/// </summary>
		/// <returns></returns>
		public bool ExecuteIfExist(string sql, RequestionType ReqType)
		{
			object obj = null;
			bool result = true;

			//如果未打开连接则先打开连接
			if (this.State == System.Data.ConnectionState.Closed)
			{
				this.Open();
			}

			this.Sql = sql;
			obj = Cmd.ExecuteScalar();

			try
			{
				this.Result = true;

				if(obj == null)																			//无查询结果
				{
					switch (ReqType)
					{
						case RequestionType.IfExist:
							result = false;
							break;
						case RequestionType.IsEmptyString:
							result = false;
							break;
						case RequestionType.IsNull:
							result = false;
							break;
						case RequestionType.NotEmpty:
							result = false;
							break;
						default:
							result = false;
							break;
					}
					return result;
				}
				else if(obj == System.DBNull.Value)											//如果查询结果是null
				{
					switch (ReqType)
					{
						case RequestionType.IfExist:
							result = true;
							break;
						case RequestionType.IsEmptyString:
							result = false;
							break;
						case RequestionType.IsNull:
							result = true;
							break;
						case RequestionType.NotEmpty:
							result = false;
							break;
						default:
							result = false;
							break;
					}
					return result;
				}
				else if (obj.ToString() == "")												//如果查询结果是空字符串
				{
					switch (ReqType)
					{
						case RequestionType.IfExist:
							result = true;
							break;
						case RequestionType.IsEmptyString:
							result = true;
							break;
						case RequestionType.IsNull:
							result = false;
							break;
						case RequestionType.NotEmpty:
							result = false;
							break;
						default:
							result = false;
							break;
					}
					return result;
				}
				else																		//如果查询结果是非空值
				{
					switch (ReqType)
					{
						case RequestionType.IfExist:
							result = true;
							break;
						case RequestionType.IsEmptyString:
							result = false;
							break;
						case RequestionType.IsNull:
							result = false;
							break;
						case RequestionType.NotEmpty:
							result = true;
							break;
						default:
							result = false;
							break;
					}
					return result;
				}
			}
			catch
			{
				return false;
			}
			finally
			{
				Cmd.Parameters.Clear();
				CmdType = System.Data.CommandType.Text;
			}
		}
	}

	// *******************************************************************************************

	/// <summary>
	/// 查询类型
	/// </summary>
	public enum RequestionType
	{
		/// <summary>
		/// 是否存在该值
		/// </summary>
		IfExist,

		/// <summary>
		/// 该值是否为空
		/// </summary>
		IsNull,

		/// <summary>
		/// 该值是否为空字符串
		/// </summary>
		IsEmptyString,

		/// <summary>
		/// 该值是否存在（非空，也非空值）
		/// </summary>
		NotEmpty

	}


}