internal DataTable GetData(string _sql) { DataSet dsRst = new DataSet(); DataTable dtRst = new DataTable(); Conn cn = new Conn(); if (!cn.isOpen) { cn.Connection.Open(); cn.isOpen = true; }; SqlCommand cmd = new SqlCommand(_sql, cn.Connection); cmd.CommandTimeout = 1; SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmd); myDataAdapter.Fill(dsRst); if (cn.isOpen) { cn.Connection.Close(); }; dtRst = dsRst.Tables[0]; cn = null; return dtRst; }
internal int ExcuteQuerryByTran(string sql, object[] objParam) { try { Conn cn = new Conn(); if (!cn.isOpen) { cn.Connection.Open(); cn.isOpen = true; }; SqlCommand cmd = new SqlCommand(sql, cn.Connection); for (int j = 0; j < objParam.Length; j++) { if (objParam[j] == null) { objParam[j] = ""; } cmd.Parameters.Add(new SqlParameter("Param" + j.ToString(), objParam[j])); }; int iRst = cmd.ExecuteNonQuery(); if (cn.isOpen) { cn.Connection.Close(); cn.isOpen = false; }; return iRst; } catch (Exception ex) { if (!ex.Data.Contains("sql")) { ex.Data.Add("sql", sql); } throw ex; }; }
internal bool Delete(string _tabName, string _where) { bool bRst = false; string sql = @" DELETE FROM " + _tabName + " WHERE " + _where; Conn cn = new Conn(); try { if (!cn.isOpen) { cn.Connection.Open(); cn.isOpen = true; }; SqlCommand MyCommand = new SqlCommand(); //定义一个数据库操作指令 MyCommand.Connection = cn.Connection; MyCommand.CommandText = sql; int iRst = MyCommand.ExecuteNonQuery(); if (cn.isOpen) { cn.Connection.Close(); cn.isOpen = false; }; if (iRst > 0) { bRst = true; } else { bRst = false; }; } catch (Exception) { throw; } finally { if (cn.isOpen) { cn.Connection.Close(); cn.isOpen = false; }; } return bRst; }
internal bool UpdateMany (string _tabName, List<string> _field, List<string> _value, string _where) { bool bRst = false; string strSQL = @"UPDATE " + _tabName + " SET "; for (int i = 0; i < _field.Count; i++) { if (i < _field.Count - 1) { strSQL += _field[i] + " = '" + _value[i] + "', "; } else if (i == _field.Count - 1) { strSQL += _field[i] + " = '" + _value[i] + "' "; break; }; }; strSQL += " WHERE " + _where; Conn cn = new Conn(); try { if (!cn.isOpen) { cn.Connection.Open(); cn.isOpen = true; }; SqlCommand MyCommand = new SqlCommand(); //定义一个数据库操作指令 MyCommand.Connection = Conn.sqlCon; MyCommand.CommandText = strSQL; int iRst = MyCommand.ExecuteNonQuery(); if (cn.isOpen) { cn.Connection.Close(); }; if (iRst > 0) { bRst = true; } else { bRst = false; }; } catch (Exception) { throw; } finally { if (cn.isOpen) { cn.Connection.Close(); }; }; return bRst; }
internal int GetCount(string _table, string _where) { int iRst = 0; string strSql = "SELECT COUNT(1) FROM " + _table + " WITH(NOLOCK) WHERE " + _where; Conn cn = new Conn(); try { SqlCommand MyCommand = new SqlCommand(); //定义一个数据库操作指令 MyCommand.Connection = cn.Connection; MyCommand.CommandText = strSql; if (!cn.isOpen) { cn.Connection.Open(); cn.isOpen = true; } var rst = MyCommand.ExecuteScalar(); iRst = Int32.Parse(rst.ToString()); } catch (Exception e) { iRst = 0; throw; } finally { if (cn.isOpen) { cn.Connection.Close(); }; } return iRst; }