非常好用的数据库操作类(转)

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;
        }    
    
   #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;         
        }

    }
}