/// <summary>
/// Execute the specified Stored Procedure and return a count
/// of the number of rows affected
/// Takes single ArrayListParameter alongside standard SqlParameters
/// Converts ArrayListParameter to SqlParameter, runs multiple times if required
/// </summary>
/// <param name="storedProcedure">Name of stored procedure to execute</param>
/// <param name="arrayParameter">Instance of the ArrayParameter class</param>
/// <param name="paramList">Array of Sql Parameters, may be null</param>
/// <returns>DataSet of results after executing SQL statement</returns>
public int ExecuteSQL(string storedProcedure, ArrayListParameter arrayParameter, SqlParameter[] paramList)
{
ArrayList parameters = null;
int result = 0;
int intermediaryResult = 0;
try
{
// Process ArrayListParameter
SqlParameter[] arrArrayParameterInstances = arrayParameter.ConvertToParameterInstances();
if (arrArrayParameterInstances.Length == 1)
{
// Simple case, send converted Array as parameter to standard method
parameters = new ArrayList();
parameters.Add(arrArrayParameterInstances[0]);
if (paramList != null)
{
foreach (SqlParameter parameter in paramList)
{
parameters.Add(parameter);
}
}
return ExecuteSQL(storedProcedure, (SqlParameter[])parameters.ToArray(typeof(SqlParameter)));
}
else
{
// Complex case, submit the query multiple times and merge results
parameters = new ArrayList();
foreach (SqlParameter objParameterInstance in arrArrayParameterInstances)
{
parameters.Clear();
parameters.Add(objParameterInstance);
if (paramList != null)
{
foreach (SqlParameter objParameter in paramList)
{
parameters.Add(objParameter);
}
}
intermediaryResult = ExecuteSQL(storedProcedure, (SqlParameter[])parameters.ToArray(typeof(SqlParameter)));
result += intermediaryResult;
}
}
}
catch (SqlException se)
{
throw se;
}
catch (Exception ex)
{
throw ex;
}
return result;
}
/// <summary>
/// Fill a dataset from the database using the specified Stored Procedure
/// statement and return the results
/// Takes single ArrayListParameter alongside standard SqlParameters
/// Converts ArrayListParameter to SqlParameter, runs multiple times if required
/// </summary>
/// <param name="storedProcedure">Name of stored procedure to execute</param>
/// <param name="arrayParameter">Instance of the ArrayParameter class</param>
/// <param name="paramList">Array of Sql Parameters, may be null</param>
/// <returns>DataSet of results after executing SQL statement</returns>
public DataSet GetDataSet(string storedProcedure, ArrayListParameter arrayParameter, SqlParameter[] paramList)
{
DataSet dsData = new DataSet();
ArrayList colParameters = null;
DataSet dsIntermediaryResult = null;
try
{
// Process ArrayListParameter
SqlParameter[] arrArrayParameterInstances = arrayParameter.ConvertToParameterInstances();
if (arrArrayParameterInstances.Length == 1)
{
// Simple case, send converted Array as parameter to standard method
colParameters = new ArrayList();
colParameters.Add(arrArrayParameterInstances[0]);
if (paramList != null)
{
foreach (SqlParameter objParameter in paramList)
{
colParameters.Add(objParameter);
}
}
return GetDataSet(storedProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
}
else
{
// Complex case, submit the query multiple times and merge results
colParameters = new ArrayList();
foreach (SqlParameter objParameterInstance in arrArrayParameterInstances)
{
colParameters.Clear();
colParameters.Add(objParameterInstance);
if (paramList != null)
{
foreach (SqlParameter objParameter in paramList)
{
colParameters.Add(objParameter);
}
}
dsIntermediaryResult = GetDataSet(storedProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
dsData.Merge(dsIntermediaryResult);
}
}
}
catch (SqlException se)
{
dsData = null;
throw se;
}
catch (Exception ex)
{
dsData = null;
throw ex;
}
if (dsData == null || dsData.Tables.Count == 0)
{
return null;
}
else
{
return dsData;
}
}
