/// <summary> /// Execute none sql query. /// </summary> /// <param name="sql">sql command</param> /// <param name="parameters">slq parameter array. Null fro default.</param> /// <returns>Query result</returns> public int ExecuteNoneQuery(string sql, SqlParameter[] parameters = null) { int queryResult = -1; using (SqlConnection con = this.CreateConnection()) { try { SqlCommand command = con.CreateCommand(); command.CommandText = sql; if (parameters != null) { command.Parameters.AddRange(parameters); } con.Open(); queryResult = command.ExecuteNonQuery(); con.Close(); } catch (Exception ex) { Log log = new Log(ex); LogQueue.AddToLogQueue(log); } } return(queryResult); }
/// <summary> /// Get data set /// </summary> /// <param name="sql">sql command</param> /// <param name="parameters">Sql parameter array. Null for default.</param> /// <returns>Result data set</returns> public DataSet GetDataSet(string sql, SqlParameter[] parameters = null) { DataSet result = new DataSet(); using (SqlConnection con = this.CreateConnection()) { try { SqlCommand com = con.CreateCommand(); com.CommandText = sql; if (parameters != null) { com.Parameters.AddRange(parameters); } SqlDataAdapter sda = new SqlDataAdapter(com); sda.Fill(result); } catch (Exception ex) { Log log = new Log(ex); LogQueue.AddToLogQueue(log); } } return(result); }
/// <summary> /// Get ask key array /// </summary> /// <returns>key array</returns> private byte[] GetAseKey() { byte[] keyBytes; try { string key = GlobalConfig.GlobalPars["AesKey"]; if (string.IsNullOrEmpty(key)) { throw new Exception("Can not find aes key."); } else if (key.Length != 32) { throw new Exception("The lengh of aes key is incorrect."); } keyBytes = Encoding.UTF8.GetBytes(key); } catch (Exception ex) { LogQueue.AddToLogQueue(ex); keyBytes = null; } return(keyBytes); }
/// <summary> /// Get data table /// </summary> /// <param name="sql">sql command</param> /// <param name="parameters">sql parameter array. Null for default.</param> /// <returns>Result data table</returns> public DataTable GetDataTable(string sql, SqlParameter[] parameters = null) { DataTable result = null; try { DataSet dataSet = this.GetDataSet(sql, parameters); result = dataSet.Tables[0]; } catch (Exception ex) { LogQueue.AddToLogQueue(ex); } return(result); }
/// <summary> /// Create a instance of sql connection. /// </summary> /// <returns>New connection</returns> private SqlConnection CreateConnection() { SqlConnection con; try { string conStr = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}"; string dbServer = GlobalConfig.GlobalPars["DBServer"]; string dbName = GlobalConfig.GlobalPars["DBName"]; string dbUser = GlobalConfig.GlobalPars["DBUser"]; string dbPwd = GlobalConfig.GlobalPars["DBPassword"]; conStr = string.Format(conStr, dbServer, dbName, dbUser, dbPwd); con = new SqlConnection(conStr); } catch (Exception ex) { con = null; LogQueue.AddToLogQueue(ex); } return(con); }