public SQLQuery(string sql, SQLQueryType queryType = SQLQueryType.DataReader)
{
this.OriginalSQL = sql;
this.ModifiedSQL = OriginalSQL;
SQLQueryType = queryType;
Parameters = new Dictionary <string, object>();
InParameters = new Dictionary <string, List <object> >();
GroupNumber = 0;
OrderNumber = 0;
CausedAbort = false;
Executed = false;
}
private T ExecuteSQL <T>(SQLQueryType QueryType, string Query, List <SqlParameter> Parameters = null, int Timeout = 1000)
{
//Tracks timeout
Stopwatch sw = new Stopwatch();
sw.Start();
T result = default(T);
try
{
using (SqlConnection DB = new SqlConnection(this.ConnectionString))
{
DB.Open();
using (SqlCommand DBCommand = new SqlCommand(Query, DB))
{
//Set the timeout value
DBCommand.CommandTimeout = Timeout;
//Set the parameters if any were specified
if (Parameters != null)
{
foreach (SqlParameter p in Parameters)
{
DBCommand.Parameters.Add(p);
}
}
switch (QueryType)
{
case SQLQueryType.DT:
DataTable DT = new DataTable();
using (SqlDataAdapter DA = new SqlDataAdapter(DBCommand))
{
DA.Fill(DT);
}
result = (T)((object)DT);
break;
case SQLQueryType.DS:
DataSet DS = new DataSet();
using (SqlDataAdapter DA = new SqlDataAdapter(DBCommand))
{
DA.Fill(DS);
}
result = (T)((object)DS);
break;
case SQLQueryType.NonQuery:
result = (T)((object)DBCommand.ExecuteNonQuery());
break;
case SQLQueryType.Scalar:
object ScalarResult = (DBCommand.ExecuteScalar());
if (ScalarResult == DBNull.Value)
{
result = default(T);
}
else
{
result = (T)ScalarResult;
}
break;
default:
throw new Exception("Invalid Query Type!");
}
}
DB.Close();
}
}
catch (Exception ex)
{
//First check for a time out exception
if (ex.Message.StartsWith("Timeout expired"))
{
Exceptions.SQLTimeoutException timeout = new Exceptions.SQLTimeoutException("SQL Time Out", ex);
timeout.QueryString = Query;
timeout.TimeOut = Timeout;
//Give the calling app a chance to handle this exception or retry since this error could be recovered given more time
ProcessSQLException(timeout);
//If the handling app wants us to retry, then retry now with the new timeout...
if (timeout.Retry)
{
return(ExecuteSQL <T>(QueryType, Query, Parameters, timeout.TimeOut));
}
}
System.Diagnostics.Debug.Print("\r\n*** SQL Exception: " + ex.Message + "\r\nQuery: " + Query + "\r\n");
Exceptions.SQLException timeout2 = new Exceptions.SQLException("SQL Exception\r\n" + ex.Message, ex);
timeout2.QueryString = Query;
//Give the calling app a chance to handle this exception, or option to retry since this error could be recovered given more time
ProcessSQLException(timeout2);
}
this.QueriesExecuted++;
LastExecuteTime = sw.Elapsed;
TotalExecuteTimes += sw.Elapsed;
return(result);
}
