分享一个简单的SQL工具-BuildQuery
标签：
SQL Server
BuildQuery类是能快速，容易地构建一个复杂的INSERT或者UPDATE 的SQL查询语句。这个类将接收的一些参数，输出有效的SQL语句。它有一个方法能刷新内部数据，因此这个类可以无数次使用，而无需反复创建和销毁实例对象。Buid，IDSettings，和FlushAllData 方法用于设置数据。 Insert, Update, 和Delete 方法用于处理数据。只有 Build 和IDSettings带参数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 | public class BuildQuery { #region Class Variables int numFieldsCount, dicpos; Dictionary<string, string> dicFields = new Dictionary<string, string>(); Dictionary<string, string> dicData = new Dictionary<string, string>(); Dictionary<string, bool> dicQuotes = new Dictionary<string, bool>(); List<string> listIDFields = new List<string>(); List<string> listIDValues = new List<string>(); List<bool> listIDQuotes = new List<bool>(); List<string> listIDOperators = new List<string>(); Boolean IdentityCheck; string strTable; #endregion #region Constructor /// <summary> /// Creates an instance of this class. /// </summary> public BuildQuery() { } #endregion #region Properties /// <summary> /// Gets or sets the table that the resulting query will work with. /// </summary> public string Table { get { return strTable; } set { strTable = value; } } /// <summary> /// Gets or sets whether the query is set to return a new identity value. /// </summary> public bool GetIdentity { get { return IdentityCheck; } set { IdentityCheck = value; } } #endregion #region Methods /// <summary> /// Clears all of the internal dictionaries so that a new query can be created. /// </summary> public void FlushAllData() { numFieldsCount = 0; dicpos = 0; dicFields.Clear(); dicData.Clear(); dicQuotes.Clear(); listIDFields.Clear(); listIDValues.Clear(); listIDQuotes.Clear(); listIDOperators.Clear(); } /// <summary> /// Adds data to the query. /// </summary> /// <param name="InputField">String value containing the column in which the data will appear.</param> /// <param name="InputData">String value containing the data that is to be used.</param> /// <param name="InputQuotes">Boolean value indicating whether the data should be surrounded by quotes.</param> public void Build(string InputField, string InputData, bool InputQuotes) { if (!(dicFields.ContainsKey(InputField))) { dicFields.Add(InputField, InputField); dicData.Add(InputField, InputData); dicQuotes.Add(InputField, InputQuotes); } else { dicData[InputField] = InputData; dicQuotes[InputField] = InputQuotes; } } /// <summary> /// Sets the WHERE portion of the query (if applicable) using the specified operator (default is "=".) /// </summary> /// <param name="IDField">String containing the column to be used.</param> /// <param name="IDValue">String containing the value to be used.</param> /// <param name="IDQuotes">Boolean indicating whether the value should have quotes around it.</param> public void IDSettings(string IDField, string IDValue, bool IDQuotes) { listIDFields.Add(IDField); listIDValues.Add(IDValue); listIDQuotes.Add(IDQuotes); listIDOperators.Add("="); } /// <summary> /// Sets the WHERE portion of the query (if applicable) using the specified operator (default is "=".) /// </summary> /// <param name="IDField">String containing the column to be used.</param> /// <param name="IDValue">String containing the value to be used.</param> /// <param name="IDQuotes">Boolean indicating whether the value should have quotes around it.</param> /// <param name="IDOperator">String containing the logic operator to be used in place of the default.</param> public void IDSettings(string IDField, string IDValue, bool IDQuotes, string IDOperator) { listIDFields.Add(IDField); listIDValues.Add(IDValue); listIDQuotes.Add(IDQuotes); listIDOperators.Add(IDOperator); } /// <summary> /// Returns an Input query using the data provided. /// </summary> public string Insert() { StringBuilder InsertString = new StringBuilder(); dicpos = 0; numFieldsCount = dicData.Count; InsertString.AppendFormat("INSERT INTO {0} (", strTable); //Put all of the fields into the query foreach (KeyValuePair<string, string> I in dicFields) { InsertString.Append(I.Value); dicpos++; if (dicpos + 1 <= numFieldsCount) { InsertString.Append(", "); } } dicpos = 0; InsertString.Append(") VALUES ("); //Put all of the data into the query foreach (KeyValuePair<string, string> K in dicData) { if (dicQuotes[K.Key]) { InsertString.Append("'"); } InsertString.Append(dicData[K.Key]); if (dicQuotes[K.Key]) { InsertString.Append("'"); } dicpos++; if (dicpos + 1 <= numFieldsCount) { InsertString.Append(", "); } } InsertString.Append(")"); if (IdentityCheck) { InsertString.AppendFormat("SET NOCOUNT ON;{0};SELECT @@Identity As LastID", InsertString.ToString()); } return InsertString.ToString(); } /// <summary> /// Returns an Update query using the data provided. /// </summary> public string Update() { StringBuilder UpdateString = new StringBuilder(); dicpos = 0; numFieldsCount = dicData.Count; UpdateString.AppendFormat("UPDATE {0} SET ", strTable); //Match up fields and data foreach (KeyValuePair<string, string> I in dicFields) { UpdateString.AppendFormat("{0} = ", I.Value); if (dicQuotes[I.Key]) { UpdateString.Append("'"); } UpdateString.Append(dicData[I.Key]); if (dicQuotes[I.Key]) { UpdateString.Append("'"); } dicpos++; if (dicpos + 1 <= numFieldsCount) { UpdateString.Append(", "); } } UpdateString.Append(" WHERE "); int Conditions = 0; for (int IDCount = 0; IDCount < listIDFields.Count; IDCount++) { if (Conditions > 0) { UpdateString.Append(" AND "); } UpdateString.AppendFormat("{0} {1}", listIDFields[IDCount], listIDOperators[IDCount]); if (listIDQuotes[IDCount]) { UpdateString.Append("'"); } UpdateString.Append(listIDValues[IDCount]); if (listIDQuotes[IDCount]) { UpdateString.Append("'"); } Conditions++; } return UpdateString.ToString(); } /// <summary> /// Returns a Delete query using the data provided. /// </summary> public string Delete() { StringBuilder DeleteString = new StringBuilder(); DeleteString.AppendFormat("DELETE FROM {0} WHERE ", strTable); int Conditions = 0; for (int IDCount = 0; IDCount < listIDFields.Count; IDCount++) { if (Conditions > 0) { DeleteString.Append(" AND "); } DeleteString.AppendFormat("{0} {1}", listIDFields[IDCount], listIDOperators[IDCount]); if (listIDQuotes[IDCount]) { DeleteString.Append("'"); } DeleteString.Append(listIDValues[IDCount]); if (listIDQuotes[IDCount]) { DeleteString.Append("'"); } Conditions++; } return DeleteString.ToString(); } #endregion } |
BuildQuery类的Build是用来接收查询字段及其值，以及对数据进行处理。这个方法有三个参数，可以如下调用：
1 | obj.Build(InputField, InputData, InputQuotes); |
InputField - 字符串值，包含显示的列。InputData - 字符串值包含被使用的数据。InputQuotes - 布尔值，表示数据是否应该用引号。
1 2 3 4 5 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyField = "Name";MyData = "John Smith";MyQuotes = true;MyQuery.Build(MyField, MyData, MyQuotes); |
BuildQuery的FlushAllData 方法是用来清除通过Build存储的所有数据。FlushAllData 将能如下调用：
1 | obj.FlushAllData() |
例子：
1 2 3 4 5 6 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyQuery.Table = "MyTable";MyQuery.Build(MyField, MyData, MyQuotes);strQuery = MyQuery.Insert;MyQuery.FlushAllData();//The object is now ready to work on a different set of data |
BuildQuery 的IDSettings方法将用来设置 UPDATE 或者DELETE 得where条件。基本的IDSettings方法有三个参数，还有一个四个参数的重载。它能被如下调用：
1 | obj.IDSettings(IDField, IDValue, IDQuotes); |
或者：
1 | obj.IDSettings(IDField, IDValue, IDQuotes, IDOperator); |
IDField - 包含了使用列的字符串。IDValue - 包含值的字符串。IDQuotes - 布尔值，值是否带引号。IDOperator - 字符串包含逻辑操作符它将取代默认的'='。
1 2 3 4 5 6 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyField = "Name";MyData = "John Smith";MyQuotes = true;MyOperator = ">=";MyQuery.IDSettings(MyField, MyData, MyQuotes, MyOperator); |
BuildQuery 的Insert方法将产生一条准备在数据库中执行的有效地INSERT查询语句。这个方法需要设置Table属性。并至少有一个实例生成方法已被成功调用。Insert能如下使用:
1 | obj.Insert(); |
例子：
1 2 3 4 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyQuery.Table = "MyTable";MyQuery.Build(MyField, MyData, MyQuotes);String strQuery = MyQuery.Insert(); |
BuildQuery的Update方法用来产生一条准备在数据库中执行的有效地 UPDATE查询语句。这个方法需要设置Table属性。对象实例至少成功调用一次IDSettings方法和Build方法各一次。Update方法能如下使用:
1 | obj.Update(); |
例子：
1 2 3 4 5 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyQuery.Table = "MyTable";MyQuery.IDSettings("MyID", MyValue, false);objBuildQuery.Build(MyField, MyData, true);String strQuery = MyQuery.Update(); |
BuildQuery的Delete方法用于永久删除从在数据库中指定的表的一个或多个行。这个方法需要设置Table属性。对象实例至少成功调用一次IDSettings方法。Delete方法能如下使用:
1 | obj.Delete(); |
这个类有两个属性：Table 和GetIdentity，两个属性都是可读可写的
Table 属性用来设置BuildQuery类使用的数据表。直到这个属性有值，否者如果产生查询的方法都会返回一个SQl错误
1 | obj.Table = value; |
例子：
1 2 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyQuery.Table = "MyTable"; |
GetIdentity 属性用来得到和设置产生INSERT 查询语句插入数据之后是否返回一个行的标识种子，这个属性默认是false。
1 | obj.GetIdentity = true; |
例子：
1 2 | NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();MyQuery.GetIdentity = true; |
点击查看更多内容
为 TA 点赞
0 评论
共同学习，写下你的评论
暂无评论
作者其他优质文章
正在加载中
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦