oledbaccessobj.cs

个人信息的源代码

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Specialized;
using System.IO;


namespace DataAccessLayer
{
	/// <summary>
	/// 使用OLEDB访问数据库。
	/// </summary>
	public class OLEDBAccessObj
	{
		public OLEDBAccessObj()
		{
			_ConnectionStr = "";
			this.CreateObject();
		}
		public OLEDBAccessObj(string strConnection)
		{
			_ConnectionStr = strConnection;
			this.CreateObject();
		}

		private OleDbConnection _Connection;
		private OleDbCommand _Command;
		private OleDbDataAdapter _DataAdapter;
		private OleDbDataReader _DataReader;
		private DataSet _DataSet;

		//OleDb连接字符串
		private string _ConnectionStr;

		/// <summary>
		/// 获取和设置OleDb连接字符串
		/// </summary>
		public string ConnectionStr
		{
			get
			{
				return _ConnectionStr;
			}
			set
			{
				_ConnectionStr = value;
			}
		}
		/// <summary>
		/// 设置Command对象的CommandText属性
		/// </summary>
		public string strSQLCommand
		{
			get
			{
				if(_Command == null)
				{
					return "";
				}
				else
				{
					return _Command.CommandText;
				}
			}
			set
			{
				if(_Command == null)
				{
					throw new Exception("设置SQL命令的Command对象为null");
				}
				
				_Command.CommandType = CommandType.Text;
				_Command.CommandText = value;
				
			}
		}
		/// <summary>
		/// 创建对象，由构造函数调用
		/// </summary>
		private void CreateObject()
		{
			_ConnectionStr = "";
			_Connection = new OleDbConnection();
			_Command = new OleDbCommand();
			_Command.Connection = _Connection;
			_DataAdapter = new OleDbDataAdapter(_Command);
		}
		/// <summary>
		/// 连接数据库，成功返回true，错误抛出异常
		/// </summary>
		/// <returns></returns>
		public bool ConnectDB()
		{
			if(_ConnectionStr == string.Empty)
				throw new Exception("未指定连接字符串");
			if(_Connection.State == ConnectionState.Open)
				throw new Exception("数据库连接已经打开");

			_Connection.ConnectionString = _ConnectionStr;

			try
			{
				_Connection.Open();
			}
			catch(InvalidOperationException ErrObj)
			{
				throw new Exception("数据库连接已经被打开了，不能再一次打开，在OLEDBAccessObj.ConnectDB中，系统提示：" + ErrObj.Message);
			}
			catch(OleDbException ErrObj)
			{
				for(int i=0; i<ErrObj.Errors.Count; i++)
				{
					throw new Exception("Index#" + i.ToString() + " Message:" + ErrObj.Errors[i].Message
						+ " Native:" + ErrObj.Errors[i].NativeError.ToString()
						+ " Source:" + ErrObj.Errors[i].Source
						+ " SQL:" + ErrObj.Errors[i].SQLState);
				}
				return false;
			}
			return true;
		}
		/// <summary>
		/// 关闭数据库连接
		/// </summary>
		/// <returns></returns>
		public void CloseConnection()
		{
			_Connection.Close();
			if(!(_DataSet == null))
			{
				_DataSet.Dispose();
			}
		}
		/// <summary>
		/// 根据SQL命令返回数据集
		/// </summary>
		/// <param name="strSQL">SQL命令</param>
		/// <param name="strTableName">数据集中的表名</param>
		/// <returns></returns>
		public DataSet GetSQLDataSet(string strSQL,string strTableName)
		{
			try
			{
				_Command.CommandType = CommandType.Text;
				_Command.CommandText = strSQL;
				_DataSet = new DataSet();

				if(strTableName == null)
				{
					_DataAdapter.Fill(_DataSet);
				}
				else
				{
					_DataAdapter.Fill(_DataSet,strTableName);
				}
				return _DataSet;
			}
			catch(Exception ex)
			{
				throw new Exception("根据SQL命令返回数据集时出错，在OLEDBAccessObj.getSQLDataSet中，系统提示：" + ex.Message);
			}
		}
		/// <summary>
		/// 根据SQL命令返回DataTable
		/// </summary>
		/// <param name="strSQL">SQL命令</param>
		/// <param name="strTableName">指定表名，如果输入null，则自动指定其名字为table1</param>
		/// <returns></returns>
		public DataTable GetSQLDataTable(string strSQL,string strTableName)
		{
			try
			{
				_Command.CommandType = CommandType.Text;
				_Command.CommandText = strSQL;
				DataTable dt;

				if(strTableName == null)
				{
					dt = new DataTable("table1");
				}
				else
				{
					dt = new DataTable(strTableName);
				}
				_DataAdapter.Fill(dt);
				return dt;
			}
			catch(Exception ex)
			{
				throw new Exception("根据SQL命令返回DataTable时出错，在OLEDBAccessObj.getSQLDataTable中，系统提示：" + ex.Message);
			}
		}
		/// <summary>
		/// 根据SQL命令返回只读的记录集
		/// </summary>
		/// <param name="strSQL">SQL命令</param>
		/// <returns></returns>
		public OleDbDataReader GetSQLDataReader(string strSQL)
		{
			_Command.CommandType = CommandType.Text;
			_Command.CommandText = strSQL;
			try
			{
				_DataReader = _Command.ExecuteReader();
				return _DataReader;
			}
			catch(InvalidExpressionException ex)
			{
				throw new Exception("根据SQL命令返回记录集时出错，在OLEDBAccessObj.getSQLDataReader中，系统提示：" + ex.Message);
			}
		}
		/// <summary>
		/// 执行不返回记录集的SQL命令，返回数据库中受影响的行数
		/// </summary>
		/// <param name="strSQL"></param>
		/// <returns></returns>
		public int ExecSQLCommand(string strSQL)
		{
			_Command.CommandType = CommandType.Text;
			_Command.CommandText = strSQL;
			int ret;

			try
			{
				ret = _Command.ExecuteNonQuery();
			}
			catch(InvalidOperationException ex)
			{
				throw new Exception("执行SQL命令时出错，在OLEDBAccessObj.ExecSQLCommand中，系统提示：" + ex.Message);
			}
			return ret;
		}
		/// <summary>
		/// 执行不返回记录集的存储过程，返回数据库中受影响的行数
		/// </summary>
		/// <param name="strSQL"></param>
		/// <returns></returns>
		public int ExecSQLCommand2(string strSQLCommandName,ListDictionary Parameters)
		{
			_Command.CommandType = CommandType.StoredProcedure;
			_Command.CommandText = strSQLCommandName;
			int ret;
			
			//加入参数
			_Command.Parameters.Clear();
			foreach(DictionaryEntry myDE in Parameters)
			{
				_Command.Parameters.Add(myDE.Key.ToString(),myDE.Value);
			}

			try
			{
				ret = _Command.ExecuteNonQuery();
			}
			catch(InvalidOperationException ex)
			{
				throw new Exception("执行存储过程时出错，在OLEDBAccessObj.ExecSQLCommand2中，系统提示：" + ex.Message);
			}
			return ret;
		}
		/// <summary>
		/// 更新DataSet中的数据到数据库，如果不指定表名，更新所有表
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="dsTableName"></param>
		/// <returns></returns>
		public bool UpdateDB(DataSet ds,string dsTableName)
		{
			try
			{
				if(strSQLCommand == "")
					throw new Exception("要更新数据库，必须指定一条有效的Select命令，该命令可返回原始数据集");
				OleDbCommandBuilder bld = new OleDbCommandBuilder(_DataAdapter);
				_DataAdapter.InsertCommand = bld.GetInsertCommand();
				_DataAdapter.UpdateCommand = bld.GetUpdateCommand();
				_DataAdapter.DeleteCommand = bld.GetDeleteCommand();
				if(dsTableName == null)
				{
					_DataAdapter.Update(ds);
				}
				else
				{
					_DataAdapter.Update(ds,dsTableName);
				}
			}
			catch(Exception ex)
			{
				throw new Exception("更新数据库时出错，在OLEDBAccessObj.UpdateDB中，系统提示：" + ex.Message);
			}
			return true;
		}
		/// <summary>
		/// 撤销DataSet中的数据改动
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="dsTableName"></param>
		/// <returns></returns>
		public void DBRejectChanges(DataSet ds,string dsTableName)
		{	
			if(ds == null)
				throw new Exception("DataSet不能为null，在OLEDBAccessObj.DBRejectChanges中");

			if(dsTableName == null)
			{
				ds.RejectChanges();
			}
			else
			{
				ds.Tables[dsTableName].RejectChanges();
			}
		}
		/// <summary>
		/// 更新DataTable中的数据到数据库
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="dsTableName"></param>
		/// <returns></returns>
		public bool UpdateDB(DataTable dt)
		{
			try
			{
				if(strSQLCommand == "")
					throw new Exception("要更新数据库，必须指定一条有效的Select命令，该命令可返回原始数据集");
				OleDbCommandBuilder bld = new OleDbCommandBuilder(_DataAdapter);
				_DataAdapter.InsertCommand = bld.GetInsertCommand();
				_DataAdapter.UpdateCommand = bld.GetUpdateCommand();
				_DataAdapter.DeleteCommand = bld.GetDeleteCommand();
				
				_DataAdapter.Update(dt);
			}
			catch(Exception ex)
			{
				throw new Exception("更新数据库时出错，在OLEDBAccessObj.UpdateDB中，系统提示：" + ex.Message);
			}
			return true;
		}
		/// <summary>
		/// 撤销DataTable中的数据改动
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="dsTableName"></param>
		/// <returns></returns>
		public void DTRejectChanges(DataTable dt)
		{	
			if(dt == null)
				throw new Exception("DataTable不能为null，在OLEDBAccessObj.DTRejectChanges中");
			dt.RejectChanges();
		}
		/// <summary>
		/// 取出指定表中指定字段的最大值，要保证字段的有效性