sql server DbHelperSQL类

  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 }