非常好用的数据库操作类

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Collections;


namespace test
{
    /**//// <summary>
    /// DbAccess 的摘要说名。
    /// </summary>
    public class DbAccess
    {

        private bool disposed = false;

        public DbAccess()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        private static string dbconnectstring = System.Configuration.ConfigurationSettings.AppSettings.Get("strConn");

        private static void curPage_Unload(object sender, EventArgs e)
        {
            Release();
        }

        public static Hashtable innerHash=new Hashtable();

        public static DbAccess Instance
        {
            get
            {
                Page curPage=(Page)HttpContext.Current.Handler;
                if(innerHash.Contains(curPage))
                    return (DbAccess)innerHash[curPage];
                else
                {
                    DbAccess obj=new DbAccess();
                    innerHash[curPage]=obj;
                    curPage.Unload+=new EventHandler(curPage_Unload);
                    return obj;
                }
            }
        }
        private static void Release()
        {
            Page curPage=(Page)HttpContext.Current.Handler;
            if(innerHash.Contains(curPage))
            {
                ((DbAccess)innerHash[curPage]).Dispose();
                innerHash.Remove(curPage);
            }
        }

        public static DbAccess GetObject(object obj)
        {
            if(innerHash.Contains(obj))
                return (DbAccess)innerHash[obj];
            else
            {
                DbAccess dbobj=new DbAccess();
                innerHash[obj]=dbobj;
                return dbobj;
            }
        }
        public static void Release(object obj)
        {
            if(innerHash.Contains(obj))
            {
                ((DbAccess)innerHash[obj]).Dispose();
                innerHash.Remove(obj);
            }
        }


        private SqlConnection innerCn=null;
        
        public System.Data.SqlClient.SqlConnection GetConn() 
        {
            if(innerCn==null)
            {
                innerCn=new SqlConnection(dbconnectstring);
                try
                {
                    innerCn.Open();
                }
                catch(Exception)
                {
                 //   MessageBox("打开数据库连接失败!");
                    return null;
                }
            }
            return innerCn;
        }
        /**//// <summary>
        ///执行SQL语句，返回DataTable对象。
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句,为字符串类型string</param>
        /// <returns>返回DataTable对象，由函数调用者主动dispose</returns>
        public System.Data.DataTable ExecuteSql(string strSQL)
        {    
            SqlConnection myCn =null;
            System.Data.SqlClient.SqlDataAdapter objDa=null;
            try
            {         
                myCn=GetConn();    
                if(myCn.State==ConnectionState.Closed)    myCn.Open();    

                objDa=new SqlDataAdapter(strSQL,myCn);
                DataSet objDs=new DataSet();
                objDa.Fill(objDs,"0");
                return objDs.Tables["0"];

            }
            catch(Exception e)
            {    
                throw new Exception(e.Message);
            }
            finally
            {
                objDa.Dispose();
            }
        }


        /**//// <summary>
        /// 判断是否有指定名称的表，判断是否有指定的字段
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(int)</returns>

        /// <summary>
        /// 按SQL语句返回DataReader对象
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(System.Data.SqlClient.SqlDataReader)</returns>
        public SqlDataReader ExecuteSqlReader(string strSql)
        {
            try
            {
                SqlConnection myCn=GetConn();
                SqlCommand myCmd=new SqlCommand(strSql,myCn);
                if(myCn.State==ConnectionState.Closed)    myCn.Open();
                SqlDataReader objReader=myCmd.ExecuteReader();
                return objReader;
            }
            catch(Exception)
            {
                return null;
            }
        }

        public SqlDataReader ExecuteSqlReaderMember(string strSql)
        {
            try
            {
                SqlConnection myCn=GetConn();
                SqlCommand myCmd=new SqlCommand(strSql,myCn);
                if(myCn.State==ConnectionState.Closed)    myCn.Open();
                SqlDataReader objReader=myCmd.ExecuteReader();
                return objReader;
            }
            catch(Exception)
            {
                return null;
            }
        }
        /**//// <summary>
        /// 执行SQL语句，返回数据集的第一行第一列的值(object)。
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(object)</returns>
        public object ExecuteSqlEx1(string strSQL)
        {
            SqlConnection myCn = GetConn();            
            SqlCommand myCmd = new SqlCommand(strSQL,myCn);
            try
            {
                if(myCn.State==ConnectionState.Closed)    myCn.Open();
                object r = myCmd.ExecuteScalar();
                if(Object.Equals(r,null))
                {
                    throw new Exception("无有效值");
                }
                else
                {
                    return r;
                }                
            }
            catch(System.Data.SqlClient.SqlException)
            {        
                return null;
            }
            finally
            {
                myCmd.Dispose();
            }
        }    

        /**//// <summary>
        /// 执行SQL语句，返回数据集的第一行第一列的值(int)。
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>(int)</returns>
        public int ExecuteSqlEx2(string strSQL)
        {
            SqlConnection myCn = GetConn();            
            SqlCommand myCmd = new SqlCommand(strSQL,myCn);
            try
            {
                if(myCn.State==ConnectionState.Closed)    myCn.Open();
                object r = myCmd.ExecuteScalar();
                if(Object.Equals(r,null))
                {
                    throw new Exception("无有效值");
                }
                else
                {
                    return Convert.ToInt32(r);
                }                
            }
            catch(System.Data.SqlClient.SqlException e)
            {                
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
            }
        }    
    
        /**//// <summary>
        /// 执行SQL语句，不返回任何结果。
        /// </summary>
        /// <param name="strSQL">(string)</param>
        /// <returns>bool</returns>
        public bool ExecuteSqlNoneResult(string strSQL)
        {
            SqlConnection myCn = GetConn();            
            SqlCommand myCmd = new SqlCommand(strSQL,myCn);
            try
            {
                if(myCn.State==ConnectionState.Closed)    myCn.Open();
                myCmd.ExecuteNonQuery();
            }
            catch(System.Data.SqlClient.SqlException e)
            {                
                throw new Exception(e.Message);                
            }
            finally
            {
                myCmd.Dispose();
            }
            return true;
        }    

        
        IDisposable 成员#region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        #endregion

        private void Dispose(bool disposing)
        {
            if(!this.disposed)
            {
                if(disposing)
                {
                    if(innerCn!=null)
                    {
                        if(innerCn.State==ConnectionState.Open)
                            innerCn.Close();
                        innerCn.Dispose();
                    }
                }
            }
            disposed = true;         
        }



    }
}

转载于:https://www.cnblogs.com/zhixiaoning/archive/2008/02/10/1066518.html