commonoperator.cs

数据库课程设计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Common
{
    public class CommonOperator
    {private string strConnectionString = "";
        private bool bConnected;
        public static SqlConnection sqlConnection;
        private SqlCommand sqlCommand;
        private bool dataAccessYN;
        public CommonOperator()
        {
            strConnectionString = "server=.;uid=sa;pwd=;database=libbook;";
        }

       
        private static SqlConnection objSql = null;
        /// <summary>
        /// 静态方法，得到连库对象
        /// </summary>
        /// <returns>返回连库对象</returns>
        public SqlConnection getSqlCon()
        {
            try
            {
                objSql = new SqlConnection("server=(local);uid=sa;pwd=;database=libbook");
                objSql.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return objSql;
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="sqlCon"></param>
        /// <returns></returns>
        public void CloseConn(SqlConnection sqlCon)
        {
            try
            {
                sqlCon.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        public CommonOperator(string _strConnectionString)
        {
            if (_strConnectionString != null)
            {
                strConnectionString = _strConnectionString;
                bConnected = false;
            }
            dataAccessYN = false;
        }
        public CommonOperator(string _dataAccessName, bool YN)
        {
            if (YN == true)
            {
                if (_dataAccessName != null)
                {
                    strConnectionString = "server=.;uid=sa;pwd=;database=" + _dataAccessName + ";";
                    bConnected = false;
                }
                dataAccessYN = true;
            }
        }
        public bool ConnectDb()
        {
            if (!bConnected)
            {
                try
                {
                    if (sqlConnection == null)
                    {
                        if (!dataAccessYN)
                        {
                            sqlConnection = new SqlConnection(strConnectionString);
                            sqlConnection.Open();
                        }
                        else
                        {
                            sqlConnection = new SqlConnection(strConnectionString);
                            sqlConnection.Open(); 
                        }

                    }

                    if (sqlCommand == null)
                    {
                        sqlCommand = new SqlCommand();
                    }
                    bConnected = true;
                    sqlCommand.Connection = sqlConnection;

                }
                catch (SqlException e)
                {
                    throw e;

                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            return true;
        }

        public bool CloseDb()
        {
            Dispose();
            return true;
        }


        public bool Execute(string sSql)
        {

            if (!ConnectDb())
            {
                throw (new ApplicationException("没有建立数据库连接"));
            }

            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sSql;
            try
            {
                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (SqlException e)
            {
                throw new Exception("执行SQL语句时出现问题" + e.Message);

                return false;
            }
            catch (Exception e)
            {
                throw e;

            }

        }

        public bool sqlAdapter(string tableName, string queryString)
        {

            if (!ConnectDb())
            {
                throw new Exception("数据库没有连接。");
            }
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(queryString, sqlConnection);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset, tableName);
                return true;
            }
            catch (SqlException e)
            {
                throw new Exception("数据库错误。" + e.Message);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Search(String l_sQuery, String l_sTableName)
        {
            String sql = l_sQuery;
            String sTableName = l_sTableName;

            if (!ConnectDb())
            {
                throw new Exception("数据库连接失败"); ;
            }

            DataTable cDataTable = new DataTable();
            DataSet cDataSet = new DataSet();
            try
            {
                sqlCommand.CommandType = System.Data.CommandType.Text;
                sqlCommand.CommandText = sql;
                sqlCommand.Connection = sqlConnection;
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = sqlCommand;
                adapter.Fill(cDataSet, sTableName);
            }
            catch (SqlException e)
            {
                throw e;

            }

            DataTable dtResult = cDataSet.Tables[0];
            return dtResult;
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(true);
        }
        protected virtual void Dispose(bool disposing)
        {

            if (!disposing)
                return;
            if (bConnected)
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Dispose();
                    sqlConnection.Close();

                    sqlCommand = null;
                    sqlConnection = null;

                    bConnected = false;
                }


            }
        }

        public void Affranchise()
        {

            sqlConnection = null;
            sqlCommand = null;
            bConnected = false;
        }

    
    }
}