1 using JKTAC_LMIS.Entity;
2 using System;
3 using System.Collections;
4 using System.Collections.Generic;
5 using System.Configuration;
6 using System.Data;
7 using System.Data.SqlClient;
8 using System.Linq;
9 using System.Security.Cryptography;
10 using System.Text;
11 using System.Threading.Tasks;
12
13 namespace JKTAC_LMIS.DAL
14 {
15 public abstract class DbHelperSQL
16 {
17 public DbHelperSQL()
18 {
19
20 }
21 //定义连接字符串。
22 //protected static string ConnectionString = DecryptDBStr(ConfigurationManager.AppSettings["SQLConnectionString"], "zhangweilong");
23 protected static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
24 protected static SqlConnection Connection;
25 //定义数据库的打开和关闭方法
26 protected static void Open()
27 {
28 if (Connection == null)
29 {
30 Connection = new SqlConnection(ConnectionString);
31 }
32 if (Connection.State.Equals(ConnectionState.Closed))
33 {
34 Connection.Open();
35 }
36 }
37 protected static void Close()
38 {
39 if (Connection != null)
40 {
41 Connection.Close();
42 }
43 }
44 //判断用Sql查询的数据是否存在,true表示存在，False表示不存在
45 public static bool Exists(string strSql)
46 {
47 object obj = DbHelperSQL.GetSingle(strSql);
48 int cmdresult;
49 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
50 {
51 cmdresult = 0;
52 }
53 else
54 {
55 cmdresult = int.Parse(obj.ToString());
56 }
57 if (cmdresult == 0)
58 {
59 return false;
60 }
61 else
62 {
63 return true;
64 }
65 }
66 public static bool Exists(string strSql, params SqlParameter[] cmdParms)
67 {
68 object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
69 int cmdresult;
70 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
71 {
72 cmdresult = 0;
73 }
74 else
75 {
76 cmdresult = int.Parse(obj.ToString());
77 }
78 if (cmdresult == 0)
79 {
80 return false;
81 }
82 else
83 {
84 return true;
85 }
86 }
87 //返回SqlDataReader数据集,使用完后记得关闭SqlDataReader
88 public static SqlDataReader GetDataReader(string SqlString)
89 {
90 try
91 {
92 Open();
93 SqlCommand cmd = new SqlCommand(SqlString, Connection);
94 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
95 }
96 catch (System.Data.SqlClient.SqlException ex)
97 {
98 throw new Exception(ex.Message);
99 }
100 }
101 // 公有方法，获取数据，返回一个DataSet。
102 public static DataSet GetDataSet(string SqlString)
103 {
104 using (SqlConnection connection = new SqlConnection(ConnectionString))
105 {
106 connection.Open();
107 using (SqlCommand cmd = new SqlCommand(SqlString, connection))
108 {
109 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
110 {
111 DataSet ds = new DataSet();
112 try
113 {
114 da.Fill(ds, "ds");
115 cmd.Parameters.Clear();
116 }
117 catch (System.Data.SqlClient.SqlException ex)
118 {
119 throw new Exception(ex.Message);
120 }
121 return ds;
122 }
123 }
124 }
125 }
126 // 公有方法，获取数据，返回一个DataTable。
127 public static DataTable GetDataTable(string SqlString)
128 {
129 DataSet dataset = GetDataSet(SqlString);
130 return dataset.Tables[0];
131 }
132 // 公有方法，获取数据，返回首行首列。
133 public static string GetSHSL(string SqlString)
134 {
135 DataSet dataset = GetDataSet(SqlString);
136 if (dataset.Tables[0].Rows.Count > 0)
137 {
138 return Convert.ToString(dataset.Tables[0].Rows[0][0].ToString());
139 }
140 else
141 {
142 return "";
143 }
144 }
145 // 公有方法，获取数据，返回首行首列的INT值。
146 public static string GetSHSLInt(string SqlString)
147 {
148 DataSet dataset = GetDataSet(SqlString);
149 if (dataset.Tables[0].Rows.Count > 0)
150 {
151 return Convert.ToString(dataset.Tables[0].Rows[0][0].ToString());
152 }
153 else
154 {
155 return "0";
156 }
157 }
158 // 公有方法，获取数据，返回一个DataRow。
159 public static DataRow GetDataRow(string SqlString)
160 {
161 DataSet dataset = GetDataSet(SqlString);
162 if (dataset.Tables[0].Rows.Count > 0)
163 {
164 return dataset.Tables[0].Rows[0];
165 }
166 else
167 {
168 return null;
169 }
170 }
171 // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
172 public static int ExecuteSQL(String SqlString, Hashtable MyHashTb)
173 {
174 int count = -1;
175 Open();
176 try
177 {
178 SqlCommand cmd = new SqlCommand(SqlString, Connection);
179 foreach (DictionaryEntry item in MyHashTb)
180 {
181 string[] CanShu = item.Key.ToString().Split('|');
182 if (CanShu[1].ToString().Trim() == "string")
183 {
184 cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
185 }
186 else if (CanShu[1].ToString().Trim() == "int")
187 {
188 cmd.Parameters.Add(CanShu[0], SqlDbType.Int);
189 }
190 else if (CanShu[1].ToString().Trim() == "text")
191 {
192 cmd.Parameters.Add(CanShu[0], SqlDbType.Text);
193 }
194 else if (CanShu[1].ToString().Trim() == "datetime")
195 {
196 cmd.Parameters.Add(CanShu[0], SqlDbType.DateTime);
197 }
198 else
199 {
200 cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
201 }
202 cmd.Parameters[CanShu[0]].Value = item.Value.ToString();
203 }
204 count = cmd.ExecuteNonQuery();
205 }
206 catch
207 {
208 count = -1;
209 }
210 finally
211 {
212 Close();
213 }
214 return count;
215 }
216 // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
217 public static int ExecuteSQL(String SqlString)
218 {
219 int count = -1;
220 Open();
221 try
222 {
223 SqlCommand cmd = new SqlCommand(SqlString, Connection);
224 count = cmd.ExecuteNonQuery();
225 }
226 catch
227 {
228 count = -1;
229 }
230 finally
231 {
232 Close();
233 }
234 return count;
235 }
236 // 公有方法，执行一组Sql语句。返回是否成功,采用事务管理，发现异常时回滚数据
237 public static bool ExecuteSQL(string[] SqlStrings)
238 {
239 bool success = true;
240 Open();
241 SqlCommand cmd = new SqlCommand();
242 SqlTransaction trans = Connection.BeginTransaction();
243 cmd.Connection = Connection;
244 cmd.Transaction = trans;
245 try
246 {
247 foreach (string str in SqlStrings)
248 {
249 cmd.CommandText = str;
250 cmd.ExecuteNonQuery();
251 }
252 trans.Commit();
253 }
254 catch
255 {
256 success = false;
257 trans.Rollback();
258 }
259 finally
260 {
261 Close();
262 }
263 return success;
264 }
265 // Trans
266 public static bool ExecuteSqlByTrans(List<SqlAndPrams> list)
267 {
268 bool success = true;
269 Open();
270 SqlCommand cmd = new SqlCommand();
271 SqlTransaction trans = Connection.BeginTransaction();
272 cmd.Connection = Connection;
273 cmd.Transaction = trans;
274 try
275 {
276 foreach (SqlAndPrams item in list)
277 {
278 if (item.cmdParms==null)
279 {
280 cmd.CommandText = item.sql;
281 cmd.ExecuteNonQuery();
282 }
283 else
284 {
285 cmd.CommandText = item.sql;
286 cmd.CommandType = CommandType.Text;//cmdType;
287 cmd.Parameters.Clear();
288 foreach (SqlParameter parameter in item.cmdParms)
289 {
290 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
291 (parameter.Value == null))
292 {
293 parameter.Value = DBNull.Value;
294 }
295 cmd.Parameters.Add(parameter);
296 }
297 cmd.ExecuteNonQuery();
298 }
299
300 }
301 trans.Commit();
302 }
303 catch(Exception e)
304 {
305 success = false;
306 trans.Rollback();
307 }
308 finally
309 {
310 Close();
311 }
312 return success;
313 }
314 // 执行一条计算查询结果语句，返回查询结果（object）。
315 public static object GetSingle(string SQLString)
316 {
317 using (SqlConnection connection = new SqlConnection(ConnectionString))
318 {
319 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
320 {
321 try
322 {
323 connection.Open();
324 object obj = cmd.ExecuteScalar();
325 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
326 {
327 return null;
328 }
329 else
330 {
331 return obj;
332 }
333 }
334 catch (System.Data.SqlClient.SqlException e)
335 {
336 connection.Close();
337 throw e;
338 }
339 }
340 }
341 }
342 public static object GetSingle(string SQLString, int Times)
343 {
344 using (SqlConnection connection = new SqlConnection(ConnectionString))
345 {
346 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
347 {
348 try
349 {
350 connection.Open();
351 cmd.CommandTimeout = Times;
352 object obj = cmd.ExecuteScalar();
353 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
354 {
355 return null;
356 }
357 else
358 {
359 return obj;
360 }
361 }
362 catch (System.Data.SqlClient.SqlException e)
363 {
364 connection.Close();
365 throw e;
366 }
367 }
368 }
369 }
370 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
371 {
372 using (SqlConnection connection = new SqlConnection(ConnectionString))
373 {
374 using (SqlCommand cmd = new SqlCommand())
375 {
376 try
377 {
378 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
379 object obj = cmd.ExecuteScalar();
380 cmd.Parameters.Clear();
381 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
382 {
383 return null;
384 }
385 else
386 {
387 return obj;
388 }
389 }
390 catch (System.Data.SqlClient.SqlException e)
391 {
392 throw e;
393 }
394 }
395 }
396 }
397 // 执行SQL语句，返回影响的记录数
398 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
399 {
400 using (SqlConnection connection = new SqlConnection(ConnectionString))
401 {
402 using (SqlCommand cmd = new SqlCommand())
403 {
404 try
405 {
406 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
407 int rows = cmd.ExecuteNonQuery();
408 cmd.Parameters.Clear();
409 return rows;
410 }
411 catch (System.Data.SqlClient.SqlException e)
412 {
413 throw e;
414 }
415 }
416 }
417 }
418 //执行查询语句，返回DataSet
419 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
420 {
421 using (SqlConnection connection = new SqlConnection(ConnectionString))
422 {
423 SqlCommand cmd = new SqlCommand();
424 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
425 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
426 {
427 DataSet ds = new DataSet();
428 try
429 {
430 da.Fill(ds, "ds");
431 cmd.Parameters.Clear();
432 }
433 catch (System.Data.SqlClient.SqlException ex)
434 {
435 throw new Exception(ex.Message);
436 }
437 return ds;
438 }
439 }
440 }
441 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
442 {
443 if (conn.State == ConnectionState.Open)
444 conn.Close();
445 if (conn.State != ConnectionState.Open)
446 conn.Open();
447 cmd.Connection = conn;
448 cmd.CommandText = cmdText;
449 if (trans != null)
450 cmd.Transaction = trans;
451 cmd.CommandType = CommandType.Text;//cmdType;
452 if (cmdParms != null)
453 {
454
455
456 foreach (SqlParameter parameter in cmdParms)
457 {
458 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
459 (parameter.Value == null))
460 {
461 parameter.Value = DBNull.Value;
462 }
463 cmd.Parameters.Add(parameter);
464 }
465 }
466 }
467
468 #region 执行存储过程 Add by LQB 2014-12-18
469 public static object RunProcedure(string storedProcName, IDataParameter[] paramenters)
470 {
471 using (SqlConnection connection = new SqlConnection(ConnectionString))
472 {
473 connection.Open();
474 SqlCommand command = BuildQueryCommand(connection, storedProcName, paramenters);
475 object obj = command.ExecuteNonQuery();
476 //object obj = command.Parameters["@Output_Value"].Value; //@Output_Value和具体的存储过程参数对应
477 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
478 {
479 return null;
480 }
481 else
482 {
483 return obj;
484 }
485 }
486 }
487
488 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
489 {
490 SqlCommand command = new SqlCommand(storedProcName, connection);
491 command.CommandType = CommandType.StoredProcedure;
492 foreach (SqlParameter parameter in parameters)
493 {
494 if (parameter != null)
495 {
496 // 检查未分配值的输出参数,将其分配以DBNull.Value.
497 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
498 (parameter.Value == null))
499 {
500 parameter.Value = DBNull.Value;
501 }
502 command.Parameters.Add(parameter);
503 }
504 }
505
506 return command;
507 }
508 #endregion
509 }
510 }
浙公网安备 33010602011771号