第一次寫博客，想不到寫什麼好b（￣▽￣）d ，考慮的半天決定從sqlhelper開始，sqlhelper對程序員來說就像helloworld一樣，很簡單卻又很重要，helloworld代表著程序員萌新第一次寫代碼，而sqlhelper則是初次接觸數據庫（不知道這種說法對不對）。
好了不廢話了，下面直接上代碼（無話可說了）：
1 public class SQLHelper
2 {
3 // 超時時間
4 private static int Timeout = 1000;
5 // 數據庫名稱
6 public const String BestNet = "BestNet";
7 //存儲過程名稱
8 public const String UserInfoCURD = "UserInfoCURD";
9 // 數據庫連接字符串
10 private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>();
11
12 /// <summary>
13 /// SQLServer操作類(靜態構造函數)
14 /// </summary>
15 static SQLHelper()
16 {
17 ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;
18 foreach (ConnectionStringSettings config in configs)
19 {
20 ConnStrs.Add(config.Name, config.ConnectionString);
21 }
22 }
23
24 /// <summary>
25 /// 獲取數據庫連接
26 /// </summary>
27 /// <param name="database">數據庫(配置文件內connectionStrings的name)</param>
28 /// <returns>數據庫連接</returns>
29 private static SqlConnection GetConnection(string database)
30 {
31 if (string.IsNullOrEmpty(database))
32 {
33 throw new Exception("未設置參數：database");
34 }
35 if (!ConnStrs.ContainsKey(database))
36 {
37 throw new Exception("未找到數據庫：" + database);
38 }
39 return new SqlConnection(ConnStrs[database]);
40 }
41
42 /// <summary>
43 /// 獲取SqlCommand
44 /// </summary>
45 /// <param name="conn">SqlConnection</param>
46 /// <param name="transaction">SqlTransaction</param>
47 /// <param name="cmdType">CommandType</param>
48 /// <param name="sql">SQL</param>
49 /// <param name="parms">SqlParameter數組</param>
50 /// <returns></returns>
51 private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms)
52 {
53 SqlCommand cmd = new SqlCommand(sql, conn);
54 cmd.CommandType = cmdType;
55 cmd.CommandTimeout = Timeout;
56 if (transaction != null)
57 cmd.Transaction = transaction;
58 if (parms != null && parms.Length != 0)
59 cmd.Parameters.AddRange(parms);
60 return cmd;
61 }
62
63 /// <summary>
64 /// 查詢數據，返回DataTable
65 /// </summary>
66 /// <param name="database">數據庫</param>
67 /// <param name="sql">SQL語句或存儲過程名</param>
68 /// <param name="parms">參數</param>
69 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
70 /// <returns>DataTable</returns>
71 public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType)
72 {
73 if (string.IsNullOrEmpty(database))
74 {
75 throw new Exception("未設置參數：database");
76 }
77 if (string.IsNullOrEmpty(sql))
78 {
79 throw new Exception("未設置參數：sql");
80 }
81
82 try
83 {
84 using (SqlConnection conn = GetConnection(database))
85 {
86 conn.Open();
87
88 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
89 {
90 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
91 {
92 DataTable dt = new DataTable();
93 da.Fill(dt);
94 return dt;
95 }
96 }
97 }
98 }
99 catch (SqlException ex)
100 {
101 System.Text.StringBuilder log = new System.Text.StringBuilder();
102 log.Append("查詢數據出錯：");
103 log.Append(ex);
104 throw new Exception(log.ToString());
105 }
106 }
107
108 /// <summary>
109 /// 查詢數據，返回DataSet
110 /// </summary>
111 /// <param name="database">數據庫</param>
112 /// <param name="sql">SQL語句或存儲過程名</param>
113 /// <param name="parms">參數</param>
114 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
115 /// <returns>DataSet</returns>
116 public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)
117 {
118 if (string.IsNullOrEmpty(database))
119 {
120 throw new Exception("未設置參數：database");
121 }
122 if (string.IsNullOrEmpty(sql))
123 {
124 throw new Exception("未設置參數：sql");
125 }
126
127 try
128 {
129 using (SqlConnection conn = GetConnection(database))
130 {
131 conn.Open();
132
133 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
134 {
135 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
136 {
137 DataSet ds = new DataSet();
138 da.Fill(ds);
139 return ds;
140 }
141 }
142 }
143 }
144 catch (SqlException ex)
145 {
146 System.Text.StringBuilder log = new System.Text.StringBuilder();
147 log.Append("查詢數據出錯：");
148 log.Append(ex);
149 throw new Exception(log.ToString());
150 }
151 }
152
153 /// <summary>
154 /// 執行命令獲取唯一值(第一行第一列)
155 /// </summary>
156 /// <param name="database">數據庫</param>
157 /// <param name="sql">SQL語句或存儲過程名</param>
158 /// <param name="parms">參數</param>
159 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
160 /// <returns>獲取值</returns>
161 public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)
162 {
163 if (string.IsNullOrEmpty(database))
164 {
165 throw new Exception("未設置參數：database");
166 }
167 if (string.IsNullOrEmpty(sql))
168 {
169 throw new Exception("未設置參數：sql");
170 }
171 try
172 {
173 using (SqlConnection conn = GetConnection(database))
174 {
175 conn.Open();
176
177 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
178 {
179 return cmd.ExecuteScalar();
180 }
181 }
182 }
183 catch (SqlException ex)
184 {
185 System.Text.StringBuilder log = new System.Text.StringBuilder();
186 log.Append("處理出錯：");
187 log.Append(ex);
188 throw new Exception(log.ToString());
189 }
190 }
191
192 /// <summary>
193 /// 執行命令更新數據
194 /// </summary>
195 /// <param name="database">數據庫</param>
196 /// <param name="sql">SQL語句或存儲過程名</param>
197 /// <param name="parms">參數</param>
198 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
199 /// <returns>更新的行數</returns>
200 public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)
201 {
202 if (string.IsNullOrEmpty(database))
203 {
204 throw new Exception("未設置參數：database");
205 }
206 if (string.IsNullOrEmpty(sql))
207 {
208 throw new Exception("未設置參數：sql");
209 }
210
211 //返回(增刪改)的更新行數
212 int count = 0;
213
214 try
215 {
216 using (SqlConnection conn = GetConnection(database))
217 {
218 conn.Open();
219
220 using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
221 {
222 if (cmdType == CommandType.StoredProcedure)
223 cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
224
225 count = cmd.ExecuteNonQuery();
226
227 if (count <= 0)
228 if (cmdType == CommandType.StoredProcedure)
229 count = (int)cmd.Parameters["@RETURN_VALUE"].Value;
230 }
231 }
232 }
233 catch (SqlException ex)
234 {
235 System.Text.StringBuilder log = new System.Text.StringBuilder();
236 log.Append("處理出錯：");
237 log.Append(ex);
238 throw new Exception(log.ToString());
239 }
240 return count;
241 }
242
243 /// <summary>
244 /// 查詢數據，返回DataTable
245 /// </summary>
246 /// <param name="database">數據庫</param>
247 /// <param name="sql">SQL語句或存儲過程名</param>
248 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
249 /// <param name="values">參數</param>
250 /// <returns>DataTable</returns>
251 public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
252 {
253 SqlParameter[] parms = DicToParams(values);
254 return QueryDataTable(database, sql, parms, cmdType);
255 }
256
257 /// <summary>
258 /// 執行存儲過程查詢數據，返回DataSet
259 /// </summary>
260 /// <param name="database">數據庫</param>
261 /// <param name="sql">SQL語句或存儲過程名</param>
262 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
263 /// <param name="values">參數
264 /// <returns>DataSet</returns>
265 public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
266 {
267 SqlParameter[] parms = DicToParams(values);
268 return QueryDataSet(database, sql, parms, cmdType);
269 }
270
271 /// <summary>
272 /// 執行命令獲取唯一值(第一行第一列)
273 /// </summary>
274 /// <param name="database">數據庫</param>
275 /// <param name="sql">SQL語句或存儲過程名</param>
276 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
277 /// <param name="values">參數</param>
278 /// <returns>唯一值</returns>
279 public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
280 {
281 SqlParameter[] parms = DicToParams(values);
282 return QueryScalar(database, sql, parms, cmdType);
283 }
284
285 /// <summary>
286 /// 執行命令更新數據
287 /// </summary>
288 /// <param name="database">數據庫</param>
289 /// <param name="sql">SQL語句或存儲過程名</param>
290 /// <param name="cmdType">查詢類型(SQL語句/存儲過程名)</param>
291 /// <param name="values">參數</param>
292 /// <returns>更新的行數</returns>
293 public static int Execute(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
294 {
295 SqlParameter[] parms = DicToParams(values);
296 return Execute(database, sql, parms, cmdType);
297 }
298
299 /// <summary>
300 /// 創建參數
301 /// </summary>
302 /// <param name="name">參數名</param>
303 /// <param name="type">參數類型</param>
304 /// <param name="size">參數大小</param>
305 /// <param name="direction">參數方向(輸入/輸出)</param>
306 /// <param name="value">參數值</param>
307 /// <returns>新參數對象</returns>
308 public static SqlParameter[] DicToParams(IDictionary<string, object> values)
309 {
310 if (values == null) return null;
311
312 SqlParameter[] parms = new SqlParameter[values.Count];
313 int index = 0;
314 foreach (KeyValuePair<string, object> kv in values)
315 {
316 SqlParameter parm = null;
317 if (kv.Value == null)
318 {
319 parm = new SqlParameter(kv.Key, DBNull.Value);
320 }
321 else
322 {
323 Type t = kv.Value.GetType();
324 parm = new SqlParameter(kv.Key, NetToSql(kv.Value.GetType()));
325 parm.Value = kv.Value;
326 }
327
328 parms[index++] = parm;
329 }
330 return parms;
331 }
332
333
334 /// <summary>
335 /// .net類型轉換為Sql類型
336 /// </summary>
337 /// <param name="t">.net類型</param>
338 /// <returns>Sql類型</returns>
339 public static SqlDbType NetToSql(Type t)
340 {
341 SqlDbType dbType = SqlDbType.Variant;
342 switch (t.Name)
343 {
344 case "Int16":
345 dbType = SqlDbType.SmallInt;
346 break;
347 case "Int32":
348 dbType = SqlDbType.Int;
349 break;
350 case "Int64":
351 dbType = SqlDbType.BigInt;
352 break;
353 case "Single":
354 dbType = SqlDbType.Real;
355 break;
356 case "Decimal":
357 dbType = SqlDbType.Decimal;
358 break;
359
360 case "Byte[]":
361 dbType = SqlDbType.VarBinary;
362 break;
363 case "Boolean":
364 dbType = SqlDbType.Bit;
365 break;
366 case "String":
367 dbType = SqlDbType.NVarChar;
368 break;
369 case "Char[]":
370 dbType = SqlDbType.Char;
371 break;
372 case "DateTime":
373 dbType = SqlDbType.DateTime;
374 break;
375 case "DateTime2":
376 dbType = SqlDbType.DateTime2;
377 break;
378 case "DateTimeOffset":
379 dbType = SqlDbType.DateTimeOffset;
380 break;
381 case "TimeSpan":
382 dbType = SqlDbType.Time;
383 break;
384 case "Guid":
385 dbType = SqlDbType.UniqueIdentifier;
386 break;
387 case "Xml":
388 dbType = SqlDbType.Xml;
389 break;
390 case "Object":
391 dbType = SqlDbType.Variant;
392 break;
393 }
394 return dbType;
395 }
396
397 }
可以直接這樣調用：
1 IDictionary<string, object> values = new Dictionary<string, object>();
2 values.Add("@UserName", UserName);
3 values.Add("@PassWord", passWord);
4 object Scalar = SQLHelper.QueryScalar(SQLHelper.BestNet, SQLHelper.UserInfoCURD, CommandType.StoredProcedure, values);
