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