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,
