/// <summary>
/// Executes the Query and retrieves any errors and number of rows affected
/// </summary>
/// <param name="oQuery">Query Object that contains the Sql String and any parameters</param>
/// <returns>Results object</returns>
private Results Run_SP(Query query) { return Process_SQL(query, CommandType.StoredProcedure); }
/// <summary>
/// Processes the passed in query. The Type of request is also passed in
/// </summary>
/// <param name="query">Query Object that contains the Sql String and any parameters</param>
/// <param name="oType">Query Type</param>
/// <returns>Results object</returns>
private Results Process_SQL(Query query, CommandType oType)
{
ResetError();
OpenConnection();
Results oResult = new Results();
string sSql = query.Sql;
try
{
using (OleDbCommand oCmd = new OleDbCommand(sSql, Connection))
{
oCmd.CommandType = oType;
if (query.ParameterList != null)
{
for (int iCount = 0; iCount < query.ParameterList.Count; iCount++)
{
oCmd.Parameters.Add(query.ParameterList[iCount].Name, query.ParameterList[iCount].Type).Value = query.ParameterList[iCount].Value;
}
}
oResult.Rows_Affected = oCmd.ExecuteNonQuery();
}
CloseConnection();
return oResult;
}
catch (Exception ex)
{
CloseConnection();
oResult.SqlException = ex;
Error = ex;
return oResult;
}
}
/// <summary>
/// Process a script from a file
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public Results RunScript(string path)
{
ResetError();
Query oQuery = new Query();
Results oResults = new Results();
try
{
if (!System.IO.File.Exists(path))
{
Results oNoResults = new Results();
Exception oFileNotFound = new System.IO.FileNotFoundException("File not found", path);
oNoResults.SqlException = oFileNotFound;
return oNoResults;
}
oQuery.Sql = System.IO.File.ReadAllText(path);
oResults = Process_SQL(oQuery, CommandType.Text);
}
catch (Exception ex)
{
oResults.SqlException = ex;
Error = ex;
}
return oResults;
}
/// <summary>
/// Executes the Query and retrieves any errors and number of rows affected
/// </summary>
/// <param name="oQuery">Query Object that contains the Sql String and any parameters</param>
/// <returns>Results object</returns>
private Results Run_DML(Query query) { return Process_SQL(query, CommandType.Text); }
public Results Procedure(Query oQuery) { return Run_SP(oQuery); }
/// <summary>
/// Returns a single valued function.
/// </summary>
/// <param name="oQuery"></param>
/// <returns></returns>
public string Function(Query oQuery)
{
ResetError();
try
{
Results oTable = Select(oQuery);
DataRow oRow = oTable.Table.Rows[0];
return oRow[0].ToString();
}
catch (Exception ex)
{
Error = ex;
return "";
}
}
public Results Alter(Query oQuery) { return Run_DML(oQuery); }
public Results Create(Query oQuery) { return Run_DML(oQuery); }
public Results Delete(Query oQuery) { return Run_DML(oQuery); }
public Results Insert(Query oQuery) { return Run_DML(oQuery); }
/// <summary>
/// Selects data based on the passed in query.
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public Results Select(Query query)
{
ResetError();
DataTable dTable = new DataTable();
Results oResults = new Results();
OpenConnection();
string sSql = query.Sql;
oResults.SqlString = sSql;
// Adds the passed in parameters to the query.
try
{
using (OleDbCommand oCmd = new OleDbCommand(sSql, Connection))
{
if (query.ParameterList != null)
{
for (int iCount = 0; iCount < query.ParameterList.Count; iCount++)
{ oCmd.Parameters.AddWithValue(query.ParameterList[iCount].Name, query.ParameterList[iCount].Value); }
}
dTable.Load(oCmd.ExecuteReader());
}
CloseConnection();
// Sets the results obj
oResults.Table = dTable;
oResults.Rows_Affected = dTable.Rows.Count;
}
catch (Exception ex)
{
CloseConnection();
oResults.SqlException = ex;
Error = ex;
}
return oResults;
}