databaseoperate.cs

大二做的课程设计。一个学生信息管理系统

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

namespace StudentLibrary.DataAccess
{

    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("data source=.;integrated security=sspi;initial catalog=accp")
        {
            //
            // 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;
                }
            }
        }
    }
}