前述: 對資料庫操作的封裝,相信網路上已經有一大堆,ORM框架,或者是.NET本身的EF,都很好的支持資料庫操作。這篇文章是分享自己所思考的,對資料庫操作的簡單封裝。我對於這篇文章,認為被瀏覽者所關註重點的是怎麼分析設計資料庫操作封裝,代碼是其次。而且,這是我第一篇文章,為了想好怎麼實現花了些天,代 ...
前述:
對資料庫操作的封裝,相信網路上已經有一大堆,ORM框架,或者是.NET本身的EF,都很好的支持資料庫操作。這篇文章是分享自己所思考的,對資料庫操作的簡單封裝。我對於這篇文章,認為被瀏覽者所關註重點的是怎麼分析設計資料庫操作封裝,代碼是其次。而且,這是我第一篇文章,為了想好怎麼實現花了些天,代碼是博客發表時現寫的。所以我想,使用可能還有bug,而且沒有try catch異常的設計。
這個框架我理應做到對資料庫無關,無論是哪個資料庫都能夠使用。不過,重點在於分析,而不是代碼。所以,為了更好的闡述,我只做了對sql Server的封裝,對其他的話,瀏覽者可以自己設計;框架可支持鏈式寫法,我想,在許多編程語言,大家對鏈式寫法大不會陌生,所以我想,資料庫訪問也可以做成鏈式的模式。這個框架不需要寫sql語句,對任何的操作,都只需要簡單的傳所需的參數,封裝好對應的操作。
在閱讀文章之前最好有些泛型、反射、Link的基礎,不然閱讀可能會有些費勁。
進入重點:
框架的結構比較簡單,使用簡單工廠模式,因此筆者就不畫一張UML圖來解釋,而用文字對裡面方法進行描述。
在設計工廠介面時候,應該考慮介面中應該含有鏈式寫法必須的三個階段(也稱部分):資料庫基本操作(打開,關閉,創建等)、資料庫的增刪改查、資料庫返回的數據(這裡我做為執行階段,估計大家會好奇為什麼不是上一階段,大家往下閱讀就知道)和不是必須的事務操作。
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.SqlClient; 7 8 namespace Dal 9 { 10 public interface DbHelper 11 { 12 /// <summary> 13 /// 創建資料庫連接 14 /// </summary> 15 /// <param name="connectionString">連接字元串</param> 16 /// <returns></returns> 17 DbHelper createConnection(string connectionString); 18 19 /// <summary> 20 /// 打開資料庫 21 /// </summary> 22 /// <returns></returns> 23 DbHelper openConnection(); 24 25 /// <summary> 26 /// 關閉資料庫 27 /// </summary> 28 /// <returns></returns> 29 DbHelper closeConnection(); 30 31 /// <summary> 32 /// 釋放sqlConnection對象 33 /// </summary> 34 void DisposedConnection(); 35 36 /// <summary> 37 /// 釋放sqlCommand對象 38 /// </summary> 39 void DisposedCommand(); 40 41 /// <summary> 42 /// 創建sqlCommand對象 43 /// </summary> 44 /// <returns></returns> 45 DbHelper createCommand(); 46 47 /// <summary> 48 /// 設置sqlCommand的類型 49 /// </summary> 50 /// <param name="type">CommandType枚舉類型</param> 51 /// <returns></returns> 52 DbHelper setCommandType(CommandType type); 53 54 /// <summary> 55 /// 要查詢的表(多表以逗號隔開)、存儲過程、視圖名 56 /// </summary> 57 /// <param name="Name"></param> 58 /// <returns></returns> 59 DbHelper FromName(string Name); 60 61 /// <summary> 62 /// 創建事務 63 /// </summary> 64 /// <returns></returns> 65 DbHelper beginTransaction(); 66 67 /// <summary> 68 /// 事務回滾 69 /// </summary> 70 /// <returns></returns> 71 DbHelper TransactionRowBack(); 72 73 /// <summary> 74 /// 事務提交 75 /// </summary> 76 /// <returns></returns> 77 DbHelper TransactionCommit(); 78 79 /// <summary> 80 /// 對多張表間的列進行聯繫 81 /// </summary> 82 /// <param name="Fields">表間聯繫的欄位</param> 83 /// <returns></returns> 84 DbHelper ForMulTable(string Fields); 85 86 /// <summary> 87 /// 查詢 88 /// </summary> 89 /// <param name="Fields">查詢欄位</param> 90 /// <param name="Where">查詢條件字典</param> 91 /// <param name="otherWhere">其他條件</param> 92 /// <returns></returns> 93 DbHelper Select(string Fields = "*", Dictionary<string, object> Where = null, string otherWhere = ""); 94 95 /// <summary> 96 /// 更新 97 /// </summary> 98 /// <param name="model">需要更新的對象</param> 99 /// <param name="Where">更新條件</param> 100 /// <param name="Fields">更新欄位</param> 101 /// <param name="otherWhere">其他條件</param> 102 /// <returns></returns> 103 DbHelper Update(object model, Dictionary<string, object> Where, string Fields = "", string otherWhere = ""); 104 105 /// <summary> 106 /// 插入 107 /// </summary> 108 /// <param name="model">需要插入的對象</param> 109 /// <param name="Fields">需要插入的欄位</param> 110 /// <returns></returns> 111 DbHelper Insert(object model, string Fields = ""); 112 113 /// <summary> 114 /// 刪除 115 /// </summary> 116 /// <param name="Where">刪除條件</param> 117 /// <param name="otherWhere">其他條件</param> 118 /// <returns></returns> 119 DbHelper Delete(Dictionary<string, object> Where, string otherWhere = ""); 120 121 122 /// <summary> 123 /// 查詢返回List 124 /// </summary> 125 /// <typeparam name="T">模型</typeparam> 126 /// <returns></returns> 127 List<T> ToList<T>() 128 where T : class ,new(); 129 130 131 /// <summary> 132 /// 查詢返回DataSet 133 /// </summary> 134 /// <param name="DatasetName"></param> 135 /// <returns></returns> 136 DataSet ToDataSet(string DatasetName); 137 138 /// <summary> 139 /// 查詢返回DataTable 140 /// </summary> 141 /// <returns></returns> 142 DataTable ToDataTable(); 143 144 /// <summary> 145 /// 執行存儲過程 146 /// </summary> 147 /// <param name="Parameter">存儲過程參數</param> 148 /// <returns></returns> 149 DbHelper ExcuteProc(Dictionary<string, object> Parameter); 150 151 /// <summary> 152 /// 執行返回查詢第一行第一列值 153 /// </summary> 154 /// <returns></returns> 155 object Result(); 156 157 /// <summary> 158 /// 返回執行的影響行數 159 /// </summary> 160 /// <returns></returns> 161 int ExcuteResult(); 162 163 /// <summary> 164 /// 用戶自定義sqlCommand 165 /// </summary> 166 /// <param name="fun">委托</param> 167 void UserDefineOperation(Action<dynamic> fun); 168 } 169 }
好了,看完代碼,大家對具體實現應該還是一頭霧水,那,接下來一步步分析具體實現,是以sql Server來分析。
在具體實現的類中SQLHelper,設計中所必須的欄位。在一開始設計時候,我在想怎麼給各個資料庫相容,因為它們使用的執行對象Command是不同的,所以為了能夠更好封裝的庫,將其設計sqlCommand不暴露給外部使用,而是在內部使用。暴露方法能夠設置com的屬性,以及ExcuteName就存放著執行數據的對象。
//連接字元串 string ConnectionString; //資料庫連接對象 private SqlConnection conn; //執行對象 SqlCommand com; //表、存儲過程、視圖名稱 string ExcuteName; //事務 SqlTransaction tran; //sql語句 StringBuilder sqlBuilderString; //參數 SqlParameter[] paras;
第一部分:資料庫基本操作
createConnection方法:這個方法其實就是new sqlConnection,對其賦值connectionString,也採用了大家一般使用的單例模式,這樣也會在執行的時候比較安全。不過這個單例是指一個Helper對應一個sqlConnection,而不是設計為static,因為我覺得有些項目在訪問的資料庫有可能有多個。而且在創建時候,對其進行打開和關閉一次,為了檢查能否真的能使用。
public DbHelper createConnection(string connectionString) { if (!ConnectionCanUse()) { this.ConnectionString = connectionString; conn = new SqlConnection(this.ConnectionString); } return this; } /// <summary> /// 檢查conn是否能用 /// </summary> /// <returns></returns> public bool ConnectionCanUse() { if (conn == null) return false; try { conn.Open(); conn.Close(); }catch(Exception e) { return false; } return true; }
打開、關閉、釋放connection和創建command就不作解釋了,因為裡面就一句話。
關於基本操作,還有就是關於sqlCommandType的設置,因為存儲過程和普通的語句等操作字元串明顯是不同,因此要寫個方法來設置它。
第二部分:增刪改查的操作 這裡就解釋為什麼sql語句不是在這個階段執行。我覺得,如果將具體的執行放在這個階段,那麼就會導致方法重載過多,為什麼?因為並不是所有人都能考慮到使用者要返回的類型,比如我想要List,或者DataSet等等,而且還會將這個方法的作用過重:在我設計的這些方法中,實操作的是對sql語句的生成,所以說為什麼不能在這邊執行,那麼就不能重用。是吧,這樣設計很靈活,將資料庫真正執行放在下個階段。而且這些方法都是鏈式的寫法,所以會對執行能夠很靈活的控制,最重要能夠重用,不需要寫別的重載方法,只需要一個方法。
生成sql語句在這也是簡單的封裝,如果要做起真的框架,我覺得sql字元串的組合還應該創建一個類,來更智能的組合用戶的需求。
自然,裡面使用到反射、Linq。不過筆者也一步步解釋,將怎麼設計分享給大家。
大家看到Select、Insert、Update、Delete的介面都有Where的條件字典。沒錯,反射就在這裡使用。為了考慮到資料庫的安全,所以sql自然只是簡單的拼接,還應該使用參數。所以,反射就用在Where生成參數上。大家也許還看到別的otherWhere,這個怎麼不設計成參數,因為Where能夠實現的,其實就是賦值語句,也就是表內某欄位 = 值,所以需要。在otherWhere中,存放的是其他特殊的條件。前面說這裡設計的不完美,就因為如此,其實有些條件 like 或者 使用or ,雖然能夠寫在otherWhere中,但是沒辦法使用參數來控制。
那麼接下來就是Fiels參數了,這個在各個方法充當不同的作用。Select是查詢的欄位,Update中是更新的欄位,在Insert中是插入的欄位,這樣就靈活的控制了。在這些欄位為空的時候,預設為全部,反射在這裡就使用了,遍歷模型對象中的屬性,然後將它們一個個填進sql語句中。在這裡比較註意的應該是插入,因為大家在寫sql語句時候是這樣的 insert tableName values(value,value....)這樣的格式,這樣是因為sql會自己對應值插入,而在程式中的模型類中,我想大家寫屬性可不是按順序的吧,所以在反射遍歷時候,就有可能將幾個本來待在某個列位置的值去換了位置的情況。所以,這裡在遍歷的時候,應該按插入的完全格式來設計,也就是 insert tableName(Field,Field...) values(value,value...)。
在這幾個方法中,Delete最簡單。
public DbHelper Select(string Fields = "*",Dictionary<string,object> Where = null,string otherWhere = "") { sqlBuilderString = new StringBuilder(); sqlBuilderString.AppendLine("select " + Fields + " from " + this.ExcuteName); List<SqlParameter> paras = new List<SqlParameter>(); sqlBuilderString.AppendLine(" where 1 = 1 "); if (Where != null && Where.Count > 0) { paras = new List<SqlParameter>(); //遍歷Where,將裡面的條件添加到sqlParamter和sql語句中 Where.Keys.ToList().ForEach(o => { sqlBuilderString.AppendLine(" and "+ o + " = @" + o); paras.Add(new SqlParameter(o, Where[o])); }); } if(!string.IsNullOrEmpty(otherWhere)) { sqlBuilderString.AppendLine(otherWhere); } this.paras = paras.ToArray(); return this; }
public DbHelper Update(object model,Dictionary<string, object> Where,string Fields = "", string otherWhere = "") { Type t = model.GetType(); List<string> keys = Where.Keys.ToList(); sqlBuilderString = new StringBuilder(); bool firstnode = true; sqlBuilderString.AppendLine("update "+ExcuteName + " set "); List<SqlParameter> paras = new List<SqlParameter>(); if(string.IsNullOrEmpty(Fields)) { t.GetProperties().ToList().ForEach(o => { if (!firstnode) sqlBuilderString.Append(","); else firstnode = false; if(!keys.Contains(o.Name)) { sqlBuilderString.AppendLine(o.Name + " = @"+o.Name); paras.Add(new SqlParameter(o.Name,o.GetValue(model,null))); } }); }else { Fields.Split(',').ToList().ForEach(o => { sqlBuilderString.AppendLine(o + " = @" + o); paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null))); }); } this.paras = paras.ToArray(); return this; }
public DbHelper Insert(object model,string Fields = "") { List<SqlParameter> paras = new List<SqlParameter>(); Type t = model.GetType(); sqlBuilderString = new StringBuilder(); sqlBuilderString.AppendLine("insert " + ExcuteName); if(string.IsNullOrEmpty(Fields)) { string s = ""; string s1=""; t.GetProperties().ToList().ForEach(o => { s += o.Name + ","; s1 += " @" + o.Name + ","; paras.Add(new SqlParameter(o.Name, o.GetValue(model, null))); }); s.Remove(s.LastIndexOf(','),1); s1.Remove(s.LastIndexOf(','), 1); sqlBuilderString.AppendLine("(" + s + ")"); sqlBuilderString.AppendLine(" values(" + s1 + ")"); }else { sqlBuilderString.AppendLine("(" + Fields + ")"); string s = ""; Fields.Split(',').ToList().ForEach(o => { s += " @" + o + ","; paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null))); }); sqlBuilderString.AppendLine(" values(" + s + ")"); } this.paras = paras.ToArray(); return this; }
public DbHelper Delete(Dictionary<string,object> Where,string otherWhere = "") { sqlBuilderString = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sqlBuilderString.AppendLine("delete " + ExcuteName); sqlBuilderString.AppendLine(" where 1 = 1 "); Where.Keys.ToList().ForEach(o => { sqlBuilderString.AppendLine(" and " + o + " = @" + o); paras.Add(new SqlParameter(o, Where[o])); }); this.paras = paras.ToArray(); return this; }
最後一個階段,那就是執行階段,這裡封裝了些執行的方法。
這個也是簡單,最重要的方法應該是setCommand,這個方法是對sqlCommand進行設置,執行的語句,以及添加參數。
private void setCommand() { if(com.CommandType== CommandType.StoredProcedure) { this.com.CommandText = ExcuteName; }else { this.com.CommandText = sqlBuilderString.ToString(); } this.paras.ToList().ForEach(o => { this.com.Parameters.Add(o); }); }
其他就是執行的語句。
public List<T> ToList<T>() where T:class ,new() { List<T> list = new List<T>(); setCommand(); SqlDataReader reader = com.ExecuteReader(); Type t = typeof(T); List<PropertyInfo> pros = t.GetProperties().ToList(); while(reader.Read()) { T model = new T(); pros.ForEach(o => { o.SetValue(model, reader[o.Name], null); }); list.Add(model); } reader.Dispose(); return list; } public DataSet ToDataSet(string DatasetName = "") { DataSet ds = new DataSet(); setCommand(); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(ds, string.IsNullOrEmpty(DatasetName) ? this.ExcuteName.Replace(",", "_") : DatasetName); adapter.Dispose(); return ds; } public DataTable ToDataTable() { DataTable dt = new DataTable(); setCommand(); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(dt); adapter.Dispose(); return dt; } public object Result() { setCommand(); return com.ExecuteScalar(); } public int ExcuteResult() { setCommand(); return com.ExecuteNonQuery(); } public DbHelper ExcuteProc(Dictionary<string,object> Parameter) { List<SqlParameter> paras = new List<SqlParameter>(); Parameter.Keys.ToList().ForEach(o => { paras.Add(new SqlParameter(o, Parameter[o])); }); return this; }
當然,還不能少了讓用戶自定義的方法,所以最後還留了個方法,參數是委托。委托裡面的參數還是動態類型,這就懂了吧,想用戶怎麼用,你就怎麼定義。
public void UserDefineOperation(Action<dynamic> fun) { fun(this.com); }
好了,設計也就到這裡,下麵就貼上SQLHelper完整的代碼。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Reflection; namespace Dal { public class SQLHelper:DbHelper { //連接字元串 string ConnectionString; //資料庫連接對象 private SqlConnection conn; //執行對象 SqlCommand com; //表、存儲過程、視圖名稱 string ExcuteName; //事務 SqlTransaction tran; //sql語句 StringBuilder sqlBuilderString; //參數 SqlParameter[] paras; private SQLHelper() { } /// <summary> /// 創建sqlHelper靜態方法 /// </summary> /// <returns></returns> public static DbHelper getInstance() { return new SQLHelper(); } /// <summary> /// /// </summary> /// <param name="connectionString"></param> /// <returns></returns> public DbHelper createConnection(string connectionString) { if (!ConnectionCanUse()) { this.ConnectionString = connectionString; conn = new SqlConnection(this.ConnectionString); } return this; } /// <summary> /// 檢查conn是否能用 /// </summary> /// <returns></returns> public bool ConnectionCanUse() { if (conn == null) return false; try { conn.Open(); conn.Close(); }catch(Exception e) { return false; } return true; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper openConnection() { if(conn.State != ConnectionState.Open) this.conn.Open(); return this; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper closeConnection() { if(conn.State != ConnectionState.Closed) this.conn.Close(); return this; } /// <summary> /// /// </summary> public void DisposedConnection() { if (!ConnectionBeUsed()) this.conn.Dispose(); } /// <summary> /// 檢查資料庫是否在被打開使用 /// </summary> /// <returns></returns>