/// <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);
}