Thought of Genius

如果我們要撰寫一個存取SQL Server的簡易元件，而我們希望這個元件有以下幾個功能：
1. 當執行SQL陳述式時，使用傳遞參數的方式，以防止SQL Injection的發生。
2. programer撰寫程式時，可以不必考慮資料庫連線等等問題。
3. programer可以傳遞自製的SQL語法，執行 insert,update,delete 等命令。
4. programer可以傳遞自製的SQL語法，查詢資料並回傳結果集。
5. programer可以自由選擇是否執行交易。

<br />using System;<br />using System.Collections.Generic;<br />using System.Data;<br />using System.Data.SqlClient;<br />using System.Transactions;<br /><br />namespace DataAccess {<br /> public class RunSql {<br /> private SqlConnection m_cn;<br /> private SqlTransaction m_tx;<br /> private SqlCommand m_cmd;<br /> private SqlDataAdapter m_da;<br /> <br /> /// <summary><br /> /// Constructor.<br /> /// </summary><br /> public RunSql() {<br /> m_cn = new SqlConnection(Properties.Settings.Default.NorthwindConStr);<br /> }<br /><br /> /// <summary><br /> /// Destructor.<br /> /// </summary><br /> ~RunSql() {<br /> this.Dispose();<br /> }<br /><br /> /// <summary><br /> /// Open Connection.<br /> /// </summary><br /> private void OpenConnection() {<br /> if (m_cn.State == ConnectionState.Closed) {<br /> m_cn.Open();<br /> }<br /> }<br /><br /> /// <summary><br /> /// Starts to Transaction.<br /> /// </summary><br /> public void BeginTransaction() {<br /> this.OpenConnection();<br /> m_tx = m_cn.BeginTransaction();<br /> }<br /><br /> /// <summary><br /> /// Commit Transaction.<br /> /// </summary><br /> public void Commit() {<br /> m_tx.Commit();<br /> m_tx.Dispose();<br /> m_tx = null;<br /> }<br /><br /> /// <summary><br /> /// Rollback Transaction.<br /> /// </summary><br /> public void Rollback() {<br /> m_tx.Rollback();<br /> m_tx.Dispose();<br /> m_tx = null;<br /> }<br /><br /> /// <summary><br /> /// Releases all resources.<br /> /// </summary><br /> public void Dispose() {<br /> if (m_cn != null) {<br /> m_cn.Close();<br /> m_cn.Dispose();<br /> m_cn = null;<br /> }<br /> if (m_tx != null) {<br /> m_tx.Rollback();<br /> m_tx.Dispose();<br /> m_tx = null;<br /> }<br /> if (m_cmd != null) {<br /> m_cmd.Dispose();<br /> m_cmd = null;<br /> }<br /> if (m_da != null) {<br /> m_da.Dispose();<br /> m_da = null;<br /> }<br /> }<br /><br /> /// <summary><br /> /// Command Object Parameters Setting<br /> /// </summary><br /> /// <param name="cmdobj">Command Object</param><br /> /// <param name="dtn">SQL Parameter Set</param><br /> private void CommandPara(SqlCommand cmdobj, Dictionary<string,object> dtn) {<br /> cmdobj.Parameters.Clear();<br /> if (dtn != null) {<br /> if (dtn.Count > 0) {<br /> foreach (KeyValuePair<string, object> kvp in dtn) {<br /> SqlParameter Parameter = new SqlParameter();<br /> Parameter.ParameterName = kvp.Key;<br /> Parameter.Value = kvp.Value;<br /> cmdobj.Parameters.Add(Parameter);<br /> }<br /> }<br /> dtn.Clear();<br /> }<br /> }<br /><br /> /// <summary><br /> /// Execute insert, update, delete ...etc command.<br /> /// </summary><br /> /// <param name="cmdstr">SQL Statement</param><br /> /// <param name="dtn">SQL Parameter Set</param><br /> public void Execute(string cmdstr, Dictionary<string, object> dtn) {<br /> try {<br /> this.OpenConnection();<br /> m_cmd = new SqlCommand(cmdstr, m_cn, m_tx);<br /> this.CommandPara(m_cmd, dtn);<br /> m_cmd.ExecuteNonQuery();<br /> }<br /> catch (Exception sqlex) {<br /> if (m_tx != null) this.Rollback();<br /> throw sqlex;<br /> }<br /> finally {<br /> m_cmd.Dispose();<br /> m_cmd = null; <br /> }<br /> }<br /><br /> /// <summary><br /> /// Execute select command and return dataTable object.<br /> /// </summary><br /> /// <param name="cmdstr">SQL Statement</param><br /> /// <param name="dtn">SQL Parameter Set</param><br /> /// <returns>DataTable Object</returns><br /> public DataTable ExecuteDataTable(string cmdstr, Dictionary<string, object> dtn) {<br /> DataTable dt = new DataTable();<br /> try {<br /> this.OpenConnection();<br /> m_da = new SqlDataAdapter(cmdstr, m_cn);<br /> this.CommandPara(m_da.SelectCommand, dtn);<br /> m_da.SelectCommand.Transaction = m_tx;<br /> m_da.Fill(dt);<br /> }<br /> catch (Exception sqlex) {<br /> if (m_tx != null) this.Rollback();<br /> throw sqlex;<br /> }<br /> finally {<br /> m_da.Dispose();<br /> m_da = null; <br /> }<br /> return dt;<br /> }<br /> }<br />}<br />