dbcontrol.cs

该系统是留言版系统

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using LeaveMessageMVC.MVC.BusinessLogicLayer.ParameterInformation;
using LeaveMessageMVC.MVC.BusinessLogicLayer;
using LeaveMessageMVC.MVC.BusinessLogicLayer.SomeFunctions;

namespace LeaveMessageMVC.MVC.DataBaseControlLayer
{
    /// <summary>
    /// DBControl 的摘要说明
    /// </summary>
    public static class DBControl
    {
        /// <summary>
        /// 数据库 连接字符串
        /// </summary>
        /// 备注：如果 ConfigurationManager.AppSettings["xxx"] 指定的“xxx”在web.config中不存在，
        ///       则 ConnectionString = null
        static private string ConnectionString = ConfigurationManager.AppSettings["DBCONNECTIONSTRING"];

        /// <summary>
        /// 数据库连接 对象（for SQL Server）
        /// </summary>
        static private SqlConnection LMDBConnection;

        /// <summary>
        /// 数据库连接 对象（for Access）
        /// </summary>
        static private OleDbConnection DBConnection;

        /// <summary>
        /// 判断是否为 Access数据库 连接
        /// </summary>
        static private bool AccessCnt = false;

        /// <summary>
        /// 用以保存 异常 信息的字符串
        /// </summary>
        static private Exception SomeExceptions = new Exception();

        /// <summary>
        /// 获取异常信息
        /// </summary>
        public static Exception DBControlError
        {
            get
            {
                return SomeExceptions;
            }
        }

        /// <summary>
        /// 判断是否为Access连接的对外接口
        /// </summary>
        public static bool AccessConnection
        {
            get
            {
                return AccessCnt;
            }
        }

        /// <summary>
        /// 链接并打开数据库
        /// </summary>
        /// <returns>返回是否成功</returns>
        public static bool DBOpen()
        {
            bool yesorno = true;

            string JudgeAccessDB = "Microsoft.Jet.OLEDB.4.0";

            //如果连接字符串里含有"Microsoft.Jet.OLEDB.4.0"字样，则为Access连接
            if (ConnectionString.Contains(JudgeAccessDB))
            {
                AccessCnt = true;
                try
                {
                    if (LMDBConnection == null)
                    {
                        DBConnection = new OleDbConnection(ConnectionString);
                    }
                    if (DBConnection.State.Equals(ConnectionState.Closed))
                    {
                        DBConnection.Open();
                    }
                }
                catch (Exception DBOpenEx)
                {
                    SomeExceptions = DBOpenEx;
                    yesorno = false;
                }
            }
            else
            {
                //否则判定为SQL Server连接
                try
                {
                    if (LMDBConnection == null)
                    {
                        LMDBConnection = new SqlConnection(ConnectionString);
                    }
                    if (LMDBConnection.State.Equals(ConnectionState.Closed))
                    {
                        LMDBConnection.Open();
                    }
                }
                catch (Exception DBOpenEx)
                {
                    SomeExceptions = DBOpenEx;
                    yesorno = false;
                }
            }

            return yesorno;

        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public static void DBClose()
        {
            if (!AccessCnt)
            {
                if (LMDBConnection != null)
                    LMDBConnection.Close();
            }
            else
            {
                if (DBConnection != null)
                    DBConnection.Close();
            }
        }

        /// <summary>
        /// 获取 数据库 数据 for SQL Server（DataReader获取数据）
        /// </summary>
        /// <returns>返回查询到的数据</returns>
        public static SqlDataReader DBSelect(string ReadCode)
        {
            SqlDataReader dr = null;
            try
            {
                DBOpen();
                SqlCommand cmd = new SqlCommand(ReadCode, LMDBConnection);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception DBReaderEx)
            {
                SomeExceptions = DBReaderEx;
                dr = null;
            }
            //finally
            //{
            //    DBClose();
            //}

            return dr;
        }

        /// <summary>
        /// 获取 数据库 数据 for Access（DataAdapter读取数据并填充到DataSet）
        /// </summary>
        /// <returns>返回查询到的数据</returns>
        public static DataSet aDBSelect(string ReadCode)
        {
            OleDbDataAdapter oda = new OleDbDataAdapter();
            try
            {
                DBOpen();
                oda.SelectCommand = new OleDbCommand(ReadCode, DBConnection);
            }
            catch (Exception DBReaderEx)
            {
                SomeExceptions = DBReaderEx;
                oda = null;
            }

            DataSet ds = new DataSet();
            oda.Fill(ds);

            return ds;
        }

        /// <summary>
        /// 执行插入、更新、删除
        /// </summary>
        /// <param name="IUDCode">插入、修改、删除语句</param>
        /// <returns>返回执行插入/更新/删除操作后影响的行数。0 为没有受影响函数；失败返回 -1</returns>
        public static int DBExcuteIUD(string IUDCode)
        {
            int TriggerRows = -1;
            try
            {
                DBOpen();
                if (!AccessCnt)
                {
                    SqlCommand cmd = new SqlCommand(IUDCode, LMDBConnection);
                    TriggerRows = cmd.ExecuteNonQuery();
                }
                else
                {
                    OleDbCommand cmd = new OleDbCommand(IUDCode, DBConnection);
                    TriggerRows = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception DBExcuteEx)
            {
                SomeExceptions = DBExcuteEx;
                TriggerRows = -1;
            }
            finally
            {
                DBClose();
            }

            return TriggerRows;
        }

        /// <summary>
        /// 获取数据库中 表 的行数
        /// </summary>
        /// <returns></returns>
        public static int TableCount()
        {
            int count = 0;
            string SQL_SelectCount = "SELECT COUNT(*) FROM " + LMManage.Tables;
            try
            {
                DBOpen();
                if (!AccessCnt)
                {
                    SqlCommand cmd = new SqlCommand(SQL_SelectCount, LMDBConnection);
                    SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (rd.Read())
                        count = rd.GetSqlInt32(0).Value;    //这就是你要的表的总行数了！   
                    rd.Close();
                }
                else
                {
                    OleDbCommand cmd = new OleDbCommand(SQL_SelectCount, DBConnection);
                    OleDbDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (rd.Read())
                        count = rd.GetInt32(0);    //这就是你要的表的总行数了！   
                    rd.Close();
                }
            }
            catch (Exception DBExcuteEx)
            {
                SomeExceptions = DBExcuteEx;
                count = -1;
            }
            finally
            {
                DBClose();
            }

            return count;
        }

        /// <summary>
        /// 获取数据库操作相关的异常信息，供管理员查看
        /// </summary>
        /// <param name="ex"></param>
        /// <returns></returns>
        public static string DBExceptionEvent()
        {
            return DBControlError.ToString();
        }
    }
}
//该源码下载自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)
//edit by ５１-ａｓｐｘ