C#對SQL Server資料庫封裝

class SqlHelper<br /> {</p><p> private string sqlConnString; // 資料庫連接字串<br /> private SqlConnection sqlConn = new SqlConnection(); // SQL資料庫連接對象</p><p> private ConnectionState sqlConnPreState = ConnectionState.Closed; //原來資料庫的串連狀態</p><p> /// <summary><br /> /// 資料庫連接字串屬性。<br /> /// </summary><br /> public string SqlConnectionString<br /> {<br /> get<br /> {<br /> return sqlConnString;<br /> }<br /> set<br /> {<br /> sqlConnString = value;<br /> sqlConn.ConnectionString = sqlConnString;<br /> }<br /> }</p><p> public SqlHelper()<br /> { </p><p> }<br /> /// <summary><br /> /// 建構函式。<br /> /// </summary><br /> /// <param name="strSqlCon">資料庫連接字串。</param><br /> public SqlHelper(string strSqlCon)<br /> {<br /> sqlConnString = strSqlCon;<br /> sqlConn.ConnectionString = sqlConnString;</p><p> }</p><p> /// <summary><br /> /// 解構函式。<br /> /// </summary><br /> ~SqlHelper()<br /> {<br /> try<br /> {<br /> Close();<br /> sqlConn.Dispose();<br /> }<br /> catch (Exception ex)<br /> {</p><p> throw ex;<br /> }</p><p> }</p><p> /// <summary><br /> /// 開啟資料庫連接。<br /> /// </summary><br /> public void Open()<br /> {<br /> try<br /> {<br /> if (sqlConn.State == ConnectionState.Closed)<br /> {<br /> sqlConn.Open();<br /> sqlConnPreState = ConnectionState.Open;<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }</p><p> }</p><p> /// <summary><br /> /// 關閉資料庫連接。<br /> /// </summary><br /> public void Close()<br /> {<br /> try<br /> {<br /> if (sqlConn.State != ConnectionState.Closed)<br /> {<br /> sqlConn.Close();<br /> sqlConnPreState = ConnectionState.Closed;<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }</p><p> /// <summary><br /> /// 擷取查詢的資料表。<br /> /// </summary><br /> /// <param name="strSQL">要查詢的SQL語句。</param><br /> /// <param name="parametes">傳入的參數，無參數時使用NULL。</param><br /> /// <returns></returns><br /> public DataTable GetDataTable(string strSQL, params SqlParameter[] parametes)<br /> {<br /> DataSet ds = new DataSet();<br /> DataTable dt = new DataTable();</p><p> try<br /> {<br /> //sqlConn.Open();<br /> if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原來的狀態為關閉且當前串連未開啟<br /> {<br /> sqlConn.Open();<br /> }</p><p> SqlCommand sqlCmd = sqlConn.CreateCommand();<br /> sqlCmd.CommandText = strSQL;</p><p> if (parametes != null)<br /> {<br /> sqlCmd.Parameters.Clear();<br /> sqlCmd.Parameters.AddRange(parametes);<br /> }</p><p> SqlDataAdapter da = new SqlDataAdapter(sqlCmd);<br /> da.Fill(ds);<br /> dt = ds.Tables[0];<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> finally<br /> {<br /> if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed ) //若原來的狀態為關閉且者當前串連未關閉則關閉<br /> {<br /> sqlConn.Close();<br /> }<br /> }<br /> return dt;</p><p> }</p><p> /// <summary><br /> /// 擷取查詢的資料集。<br /> /// </summary><br /> /// <param name="strSQL">要查詢的SQL語句。</param><br /> /// <param name="parametes">傳入的參數，無參數時使用NULL。</param><br /> /// <returns></returns><br /> public DataSet GetDataSet(string strSQL, params SqlParameter[] parametes)<br /> {<br /> DataSet ds = new DataSet();<br /> try<br /> {<br /> //sqlConn.Open();<br /> if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原來的狀態為關閉且當前串連未開啟<br /> {<br /> sqlConn.Open();<br /> }<br /> SqlCommand sqlCmd = sqlConn.CreateCommand();<br /> sqlCmd.CommandText = strSQL;</p><p> if (parametes != null)<br /> {<br /> sqlCmd.Parameters.Clear();<br /> sqlCmd.Parameters.AddRange(parametes);<br /> }</p><p> SqlDataAdapter da = new SqlDataAdapter(sqlCmd);<br /> da.Fill(ds);<br /> sqlConn.Close();<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> finally<br /> {<br /> if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed) //若原來的狀態為關閉且者當前串連未關閉則關閉<br /> {<br /> sqlConn.Close();<br /> }<br /> }<br /> return ds;<br /> }</p><p> /// <summary><br /> /// 返回SqlDataReader對象。該函數需要在外部開啟和關閉串連操作。<br /> /// </summary><br /> /// <param name="strSQL">傳入的SQL語句。</param><br /> /// <param name="parametes">傳入的參數，無參數時使用NULL。</param><br /> /// <returns></returns><br /> public SqlDataReader ExecuteReader(string strSQL, params SqlParameter[] parametes)<br /> {<br /> SqlDataReader reader;<br /> try<br /> {</p><p> SqlCommand sqlCmd = sqlConn.CreateCommand();<br /> sqlCmd.CommandText = strSQL;</p><p> if (parametes != null)<br /> {<br /> sqlCmd.Parameters.Clear();<br /> sqlCmd.Parameters.AddRange(parametes);<br /> }</p><p> reader = sqlCmd.ExecuteReader();<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> return reader;<br /> }</p><p> /// <summary><br /> /// 返回執行T-SQL語句受影響的行數。<br /> /// </summary><br /> /// <param name="strSQL">執行的SQL語句。</param><br /> /// <param name="parametes">傳入的參數，無參數時使用NULL。</param><br /> /// <returns></returns><br /> public int ExecuteNonQuery(string strSQL, params SqlParameter[] parametes)<br /> {</p><p> int sqlInt = -1;<br /> try<br /> {<br /> if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原來的狀態為關閉且當前串連未開啟<br /> {<br /> sqlConn.Open();<br /> }</p><p> SqlCommand sqlCmd = sqlConn.CreateCommand();<br /> sqlCmd.CommandText = strSQL;</p><p> if (parametes != null)<br /> {<br /> sqlCmd.Parameters.Clear();<br /> sqlCmd.Parameters.AddRange(parametes);<br /> }</p><p> sqlInt = sqlCmd.ExecuteNonQuery();<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> finally<br /> {<br /> if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed) //若原來的狀態為關閉且者當前串連未關閉則關閉<br /> {<br /> sqlConn.Close();<br /> }<br /> }<br /> return sqlInt;</p><p> }</p><p> /// <summary><br /> /// 執行查詢並返回第一行第一列的值。<br /> /// </summary><br /> /// <param name="strSQL">執行的SQL語句。</param><br /> /// <param name="parametes">傳入的參數，無參數時使用NULL。</param><br /> /// <returns></returns><br /> public object ExecuteScalar(string strSQL, params SqlParameter[] parametes)<br /> {<br /> object obj;<br /> try<br /> {<br /> if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原來的狀態為關閉且當前串連未開啟<br /> {<br /> sqlConn.Open();<br /> }</p><p> SqlCommand sqlCmd = sqlConn.CreateCommand();<br /> sqlCmd.CommandText = strSQL;</p><p> if (parametes != null)<br /> {<br /> sqlCmd.Parameters.Clear();<br /> sqlCmd.Parameters.AddRange(parametes);<br /> }</p><p> obj = sqlCmd.ExecuteScalar();<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> finally<br /> {<br /> if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed) //若原來的狀態為關閉且者當前串連未關閉則關閉<br /> {<br /> sqlConn.Close();<br /> }<br /> }<br /> return obj;<br /> }</p><p> }