databaseoperate.cs

三层体系结构的应用-学员管理系统 一个学校需要一个学生管理系统。分以下功能: 管理班档案 管理学生档案 学校开了多门课程

using System;
using System.Data;
using System.Data.SqlClient;

namespace Aptech.Student.Common
{
	
	public class DataBaseOperate:IDisposable
	{
		// 保存数据库连接字符串
		private string m_sConnectionString;
		
		// 数据库连接标志
		private bool m_bConnected;
		
		// 存储过程开启标志
		private bool m_bTransaction;
		
		
		//数据库连接对象
		private SqlConnection m_sqlConnection;
		private SqlCommand m_sqlCommand;
		private SqlTransaction m_sqlTransaction;
		private SqlDataAdapter m_sqlDataAdapter;
		
		/// <summary>
		/// 构造函数，初始化内部变量
		/// </summary>
		public DataBaseOperate()
			:this("user id=sa;password=510902;data source=\"YAOYANG\\YAOYANG\";initial catalog=Student")
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		
		public DataBaseOperate(
			string sConnectionString)
		{
			if (sConnectionString != "")
			{
				m_sConnectionString = sConnectionString;
				m_bConnected = false;
			}
			m_bTransaction = false;
		}

		/// <summary>
		/// 连接数据库，并打开数据库连接
		/// </summary>
		/// <returns>成功返回true</returns>
		private bool ConnectDataBase()
		{
			if (!m_bConnected)
			{
				try
				{
					if (m_sqlConnection == null)
					{
						m_sqlConnection = new SqlConnection(m_sConnectionString);
						m_sqlConnection.Open();
					}

					if (m_sqlCommand == null)
					{
						m_sqlCommand = new SqlCommand();
					}
					m_bConnected = true;
					m_sqlCommand.Connection = m_sqlConnection;
				}
				catch(SqlException e)
				{
					throw e;
				}
			}
			return true;
		}
		
		/// <summary>
		/// 关闭数据库，释放数据库资源
		/// </summary>
		/// <returns>成功返回true</returns>
		public bool CloseDataBase()
		{
			Dispose();
			return true;
		}

		/// <summary>
		/// 执行数据库查询操作
		/// </summary>
		/// <param name="sQuery">查询的Sql语句</param>
		/// <param name="sTableName">返回数据集的表名</param>
		/// <returns>返回数据集</returns>
		public DataSet Search(
			string	sQuery,
			string	sTableName )
		{
				
			//若连接数据库失败抛出错误
			if (!ConnectDataBase())
			{
				throw(new ApplicationException("没有建立数据库连接。"));
			}
			
			DataSet dataSet = new DataSet();
			m_sqlCommand.CommandType = System.Data.CommandType.Text;
			m_sqlCommand.CommandText = sQuery;
			m_sqlDataAdapter = new SqlDataAdapter();
			m_sqlDataAdapter.SelectCommand = m_sqlCommand;
			try
			{
				m_sqlDataAdapter.Fill(
					dataSet,
					sTableName);
			}
			catch(SqlException e)
			{
				//如果正在执行事务，回滚
				if(m_bTransaction)
				{
					m_sqlTransaction.Rollback();
				}
				throw e;
			}
			return dataSet;
		}

		
		/// <summary>
		///  执行修改数据库操作，修改、删除等无返回值的操作
		/// </summary>
		/// <param name="sSql">执行的SQL语句</param>
		/// <returns>成功执行返回True</returns>
		public bool Execute(
			string	sSql)
		{
			
			if (!ConnectDataBase())
			{
				throw(new ApplicationException("没有建立数据库连接")); 
			}
			
			m_sqlCommand.CommandType = System.Data.CommandType.Text;
			m_sqlCommand.CommandText = sSql;
			try
			{
				m_sqlCommand.ExecuteNonQuery();				
			}
			catch(SqlException e)
			{
				//如果正在执行事务，回滚
				if(m_bTransaction)
				{
					m_sqlTransaction.Rollback();
				}
				throw e;
			}
			return true;			
		}

		/// <summary>
		/// 开始事务处理功能，之后执行的全部数据库操作语句需要调用提交函数（commit）生效
		/// </summary>
		public void StartTransation()
		{
			//若连接数据库失败抛出错误
			if (!ConnectDataBase())
			{
				throw(new ApplicationException("没有建立数据库连接。"));
			}

			m_bTransaction = true;
			m_sqlTransaction = m_sqlConnection.BeginTransaction(
				IsolationLevel.ReadCommitted);
			m_sqlCommand.Transaction = m_sqlTransaction;
			
		}

		/// <summary>
		/// 当前待处理事务提交，失败全部回滚
		/// <returns>成功提交返回true</returns>
		public bool Commit()
		{
			//如果没有开启事务处理功能，不做任何操作，直接返回成功
			if (!m_bTransaction)
			{
				return true;
			}

			try
			{
				m_sqlTransaction.Commit();
			}
			catch(SqlException e)
			{
				m_sqlTransaction.Rollback();
				throw e;
			}
			return true;
		}

		/// <summary>
		/// 除去对象资源.
		/// </summary>
		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(true); 
		}

		/// <summary>
		///	释放对象实例变量.
		/// </summary>
		protected virtual void Dispose(
			bool bDispose)
		{
			if (!bDispose)
				return; 
			
			if (m_bConnected)
			{
				if (m_sqlConnection.State != ConnectionState.Closed )
				{
					m_sqlConnection.Dispose();
					m_sqlConnection.Close();
					
					m_sqlCommand = null;
					m_sqlConnection = null;
					m_sqlTransaction = null;

					m_bConnected = false;
				}
			}
		}
	}
}