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                         try113                         {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             else141             {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             else154             {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             else167             {168                 return null;169             }170         }171         // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1172         public static int ExecuteSQL(String SqlString, Hashtable MyHashTb)173         {174             int count = -1;175             Open();176             try177             {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                     else199                     {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             catch207             {208                 count = -1;209             }210             finally211             {212                 Close();213             }214             return count;215         }216         // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1217         public static int ExecuteSQL(String SqlString)218         {219             int count = -1;220             Open();221             try222             {223                 SqlCommand cmd = new SqlCommand(SqlString, Connection);224                 count = cmd.ExecuteNonQuery();225             }226             catch227             {228                 count = -1;229             }230             finally231             {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             try246             {247                 foreach (string str in SqlStrings)248                 {249                     cmd.CommandText = str;250                     cmd.ExecuteNonQuery();251                 }252                 trans.Commit();253             }254             catch255             {256                 success = false;257                 trans.Rollback();258             }259             finally260             {261                 Close();262             }263             return success;264         }265         // Trans 266         public static bool ExecuteSqlByTrans(List
     
       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             try275             {276                 foreach (SqlAndPrams item in list)277                 {278                     if (item.cmdParms==null)279                     {280                         cmd.CommandText = item.sql;281                         cmd.ExecuteNonQuery();282                     }283                     else284                     {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             finally309             {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                     try322                     {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                         else330                         {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                     try349                     {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                         else358                         {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                     try377                     {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                         else386                         {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                     try405                     {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         //执行查询语句，返回DataSet419         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                     try429                     {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-18469         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                 else482                 {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         #endregion509     }510 }