ASP.NET之資料訪問類代碼詳解

using System; using System.Data; using System.Data.SqlClient;  namespace SysClassLibrary { /// <summary> /// DataAccess 的摘要說明。 /// <description>資料處理基類,調用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description> /// </summary> public class DataAccess { #region 屬性 protected static SqlConnection conn=new SqlConnection(); protected static SqlCommand comm=new SqlCommand(); #endregion public DataAccess() { //init(); } #region 內建函式 靜態方法中不會執行DataAccess()建構函式 /// <summary> /// 開啟資料庫連接 /// </summary> private static void openConnection() { if (conn.State == ConnectionState.Closed) { //SysConfig.ConnectionString 為系統配置類中連接字串，如："server=localhost;database=databasename;uid=sa;pwd=;" conn.ConnectionString = SysConfig.ConnectionString ; comm.Connection =conn; try { conn.Open(); } catch(Exception e) { throw new Exception(e.Message); } } } /// <summary> /// 關閉當前資料庫連接 /// </summary> private static void closeConnection() { if(conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); comm.Dispose(); } #endregion /// <summary> /// 執行Sql查詢語句 /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> public static void ExecuteSql(string sqlstr) { try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; comm.ExecuteNonQuery();  } catch(Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } /// <summary> /// 執行預存程序 /// </summary> /// <param name="procName">預存程序名</param> /// <param name="coll">SqlParameters 集合</param> public static void ExecutePorcedure(string procName,SqlParameter[] coll) { try { openConnection(); for(int i=0;i<coll.Length;i++) { comm.Parameters .Add(coll); } comm.CommandType=CommandType.StoredProcedure ; comm.CommandText =procName; comm.ExecuteNonQuery(); } catch(Exception e) { throw new Exception(e.Message); } finally { comm.Parameters.Clear(); closeConnection(); } } /// <summary> /// 執行預存程序並返回資料集 /// </summary> /// <param name="procName">預存程序名稱</param> /// <param name="coll">SqlParameter集合</param> /// <param name="ds">DataSet </param> public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds) { try { SqlDataAdapter da=new SqlDataAdapter(); openConnection(); for(int i=0;i<coll.Length;i++) { comm.Parameters .Add(coll); } comm.CommandType=CommandType.StoredProcedure ; comm.CommandText =procName; da.SelectCommand =comm; da.Fill(ds); } catch(Exception e) { throw new Exception(e.Message); } finally { comm.Parameters.Clear(); closeConnection(); } } /// <summary> /// 執行Sql查詢語句並返回第一行的第一條記錄,傳回值為object 使用時需要拆箱操作 -> Unbox /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <returns>object 傳回值 </returns> public static object ExecuteScalar(string sqlstr) { object obj=new object(); try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; obj=comm.ExecuteScalar();  } catch(Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } return obj; } /// <summary> /// 執行Sql查詢語句,同時進行交易處理 /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> public static void ExecuteSqlWithTransaction(string sqlstr) { SqlTransaction trans ; trans=conn.BeginTransaction(); comm.Transaction =trans; try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; comm.ExecuteNonQuery();  trans.Commit(); } catch { trans.Rollback(); } finally { closeConnection(); } } /// <summary> /// 返回指定Sql語句的SqlDataReader，請注意，在使用後請關閉本對象，同時將自動調用closeConnection()來關閉資料庫連接 /// 方法關閉資料庫連接 /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <returns>SqlDataReader對象</returns> public static SqlDataReader dataReader(string sqlstr) { SqlDataReader dr=null; try { openConnection(); comm.CommandText =sqlstr; comm.CommandType =CommandType.Text ; dr=comm.ExecuteReader(CommandBehavior.CloseConnection);  } catch { try { dr.Close(); closeConnection(); } catch { } } return dr; } /// <summary> /// 返回指定Sql語句的SqlDataReader，請注意，在使用後請關閉本對象，同時將自動調用closeConnection()來關閉資料庫連接 /// 方法關閉資料庫連接 /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <param name="dr">傳入的ref DataReader 對象</param> public static void dataReader(string sqlstr,ref SqlDataReader dr) { try { openConnection(); comm.CommandText =sqlstr; comm.CommandType =CommandType.Text ; dr=comm.ExecuteReader(CommandBehavior.CloseConnection);  } catch { try { if(dr!=null && !dr.IsClosed) dr.Close(); } catch { } finally { closeConnection(); } } } /// <summary> /// 返回指定Sql語句的DataSet /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <returns>DataSet</returns> public static DataSet dataSet(string sqlstr) { DataSet ds= new DataSet(); SqlDataAdapter da=new SqlDataAdapter(); try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; da.SelectCommand =comm; da.Fill(ds); } catch(Exception e) { throw new Exception(e.Message);  } finally { closeConnection(); } return ds; } /// <summary> /// 返回指定Sql語句的DataSet /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <param name="ds">傳入的引用DataSet對象</param> public static void dataSet(string sqlstr,ref DataSet ds) { SqlDataAdapter da=new SqlDataAdapter(); try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; da.SelectCommand =comm; da.Fill(ds); } catch(Exception e) { throw new Exception(e.Message);  } finally { closeConnection(); } } /// <summary> /// 返回指定Sql語句的DataTable /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <returns>DataTable</returns> public static DataTable dataTable(string sqlstr) { SqlDataAdapter da=new SqlDataAdapter(); DataTable datatable=new DataTable(); try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; da.SelectCommand =comm; da.Fill(datatable); } catch(Exception e) { throw new Exception(e.Message);  } finally { closeConnection(); } return datatable; } /// <summary> /// 執行指定Sql語句,同時給傳入DataTable進行賦值  /// </summary> /// <param name="sqlstr">傳入的Sql語句</param> /// <param name="dt">ref DataTable dt </param> public static void dataTable(string sqlstr,ref DataTable dt) { SqlDataAdapter da=new SqlDataAdapter(); try { openConnection(); comm.CommandType =CommandType.Text ; comm.CommandText =sqlstr; da.SelectCommand =comm; da.Fill(dt); } catch(Exception e) { throw new Exception(e.Message);  } finally { closeConnection(); } } /// <summary> /// 執行帶參數預存程序並返回資料集合 /// </summary> /// <param name="procName">預存程序名稱</param> /// <param name="parameters">SqlParameterCollection 輸入參數</param> /// <returns></returns> public static DataTable dataTable(string procName,SqlParameterCollection parameters) {  SqlDataAdapter da=new SqlDataAdapter(); DataTable datatable=new DataTable(); try { openConnection(); comm.Parameters.Clear(); comm.CommandType=CommandType.StoredProcedure ; comm.CommandText =procName; foreach(SqlParameter para in parameters) { SqlParameter p=(SqlParameter)para; comm.Parameters.Add(p); } da.SelectCommand =comm; da.Fill(datatable); } catch(Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } return datatable; } public static DataView dataView(string sqlstr) { SqlDataAdapter da=new SqlDataAdapter(); DataView dv=new DataView(); DataSet ds=new DataSet(); try { openConnection(); comm.CommandType=CommandType.Text; comm.CommandText =sqlstr; da.SelectCommand =comm; da.Fill(ds); dv=ds.Tables[0].DefaultView; } catch(Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } return dv; } } }