/// <summary> Execute a SQL statement or stored procedure and return a data reader </summary>
/// <param name="DbType"> Type of database ( i.e., MSSQL, PostgreSQL ) </param>
/// <param name="DbConnectionString"> Database connection string </param>
/// <param name="DbCommandType"> Database command type </param>
/// <param name="DbCommandText"> Text of the database command, or name of the stored procedure to run </param>
/// <param name="DbParameters"> Parameters for the SQL statement </param>
public static EalDbReaderWrapper ExecuteDataReader(EalDbTypeEnum DbType, string DbConnectionString, CommandType DbCommandType, string DbCommandText, List <EalDbParameter> DbParameters)
{
if (DbType == EalDbTypeEnum.MSSQL)
{
// Create the SQL connection
SqlConnection sqlConnect = new SqlConnection(DbConnectionString);
try
{
sqlConnect.Open();
}
catch (Exception ex)
{
throw new ApplicationException("Unable to open connection to the database." + Environment.NewLine + ex.Message, ex);
}
// Create the SQL command
SqlCommand sqlCommand = new SqlCommand(DbCommandText, sqlConnect)
{
CommandType = DbCommandType
};
// Copy all the parameters to this adapter
sql_add_params_to_command(sqlCommand, DbParameters);
// Fill the dataset to return
SqlDataReader reader;
// Try to open the reader.. if there was an error, close the database connection
// before passing out the exception
try
{
reader = sqlCommand.ExecuteReader();
}
catch
{
sqlConnect.Close();
throw;
}
// Create the reader wrapper
EalDbReaderWrapper returnValue = new EalDbReaderWrapper(sqlConnect, reader);
// Copy any output values back to the parameters
sql_copy_returned_values_back_to_params(returnValue, sqlCommand.Parameters, DbParameters);
// Return the dataset
return(returnValue);
}
if (DbType == EalDbTypeEnum.PostgreSQL)
{
throw new ApplicationException("Support for PostgreSQL with SobekCM is targeted for early 2016");
}
throw new ApplicationException("Unknown database type not supported");
}
// Copy any output values back to the parameters
private static void sql_copy_returned_values_back_to_params(EalDbReaderWrapper Wrapper, SqlParameterCollection SqlParams, EalDbParameter[] EalParams)
{
// Copy over any values as necessary
int i = 0;
foreach (EalDbParameter thisParameter in EalParams)
{
// If this parameter is null, just go to the next
if (thisParameter == null)
{
continue;
}
if ((thisParameter.Direction == ParameterDirection.Output) || (thisParameter.Direction == ParameterDirection.InputOutput))
{
Wrapper.Add_Parameter_Copy_Pair(thisParameter, SqlParams[i]);
}
i++;
}
}
// Copy any output values back to the parameters
private static void sql_copy_returned_values_back_to_params(EalDbReaderWrapper Wrapper, SqlParameterCollection SqlParams, EalDbParameter[] EalParams)
{
// Copy over any values as necessary
int i = 0;
foreach (EalDbParameter thisParameter in EalParams)
{
if ((thisParameter.Direction == ParameterDirection.Output) || (thisParameter.Direction == ParameterDirection.InputOutput))
{
Wrapper.Add_Parameter_Copy_Pair(thisParameter, SqlParams[i]);
}
i++;
}
}
/// <summary> Execute a SQL statement or stored procedure and return a data reader </summary>
/// <param name="DbType"> Type of database ( i.e., MSSQL, PostgreSQL ) </param>
/// <param name="DbConnectionString"> Database connection string </param>
/// <param name="DbCommandType"> Database command type </param>
/// <param name="DbCommandText"> Text of the database command, or name of the stored procedure to run </param>
/// <param name="DbParameters"> Parameters for the SQL statement </param>
public static EalDbReaderWrapper ExecuteDataReader(EalDbTypeEnum DbType, string DbConnectionString, CommandType DbCommandType, string DbCommandText, List<EalDbParameter> DbParameters)
{
if (DbType == EalDbTypeEnum.MSSQL)
{
// Create the SQL connection
SqlConnection sqlConnect = new SqlConnection(DbConnectionString);
try
{
sqlConnect.Open();
}
catch (Exception ex)
{
throw new ApplicationException("Unable to open connection to the database." + Environment.NewLine + ex.Message, ex);
}
// Create the SQL command
SqlCommand sqlCommand = new SqlCommand(DbCommandText, sqlConnect)
{
CommandType = DbCommandType
};
// Copy all the parameters to this adapter
sql_add_params_to_command(sqlCommand, DbParameters);
// Fill the dataset to return
SqlDataReader reader;
// Try to open the reader.. if there was an error, close the database connection
// before passing out the exception
try
{
reader = sqlCommand.ExecuteReader();
}
catch
{
sqlConnect.Close();
throw;
}
// Create the reader wrapper
EalDbReaderWrapper returnValue = new EalDbReaderWrapper(sqlConnect, reader);
// Copy any output values back to the parameters
sql_copy_returned_values_back_to_params(returnValue, sqlCommand.Parameters, DbParameters);
// Return the dataset
return returnValue;
}
if (DbType == EalDbTypeEnum.PostgreSQL)
{
throw new ApplicationException("Support for PostgreSQL with SobekCM is targeted for early 2016");
}
throw new ApplicationException("Unknown database type not supported");
}
// Copy any output values back to the parameters
private static void sql_copy_returned_values_back_to_params(EalDbReaderWrapper Wrapper, SqlParameterCollection SqlParams, List<EalDbParameter> EalParams)
{
// Copy over any values as necessary
int i = 0;
foreach (EalDbParameter thisParameter in EalParams)
{
// If this parameter is null, just go to the next
if (thisParameter == null)
continue;
if ((thisParameter.Direction == ParameterDirection.Output) || (thisParameter.Direction == ParameterDirection.InputOutput))
{
Wrapper.Add_Parameter_Copy_Pair(thisParameter, SqlParams[i]);
}
i++;
}
}
