///--------------------------------------------------------------------------------
/// <summary>This method sets up a stored procedure command, loaded with values from
/// the input data access object where appropriate.</summary>
///
/// <param name="sprocName">Name of the stored procedure.</param>
/// <param name="sprocParams">The input stored procedure parameter values.</param>
///
/// <returns>A DbCommand for the stored procedure.</returns>
///--------------------------------------------------------------------------------
public DbCommand SetupSprocCommand(string sprocName, NameObjectCollection sprocParams)
{
GenericMethod sprocMethod = null;
DbCommand command = null;
try
{
// get sproc method from cache or db
sprocMethod = DbProcs.MethodList.Find("MethodName", sprocName);
if (sprocMethod == null)
{
// get sproc from db an add to cache
sprocMethod = GetSprocFromDB(sprocName, true);
}
// set up the base command information
command = _database.GetStoredProcCommand(sprocName);
command.Connection = Connection;
command.CommandTimeout = DBOptions.CommandTimeout;
// load the sproc parameters with input data if found
foreach (GenericParameter loopParameter in sprocMethod.MethodParameterList)
{
bool foundValue = false;
if (sprocParams[loopParameter.ParameterName] != null && sprocParams[loopParameter.ParameterName].GetString() != String.Empty)
{
foundValue = true;
}
if (foundValue == false && loopParameter.IsNullable == false && loopParameter.ParameterTypeCode == 1)
{
// required parameter not found, throw exception
throw new System.Exception("MoPlus.Data.BuildSprocParamsWithInputValues - required input value parameter " + loopParameter.ParameterName + " for stored procedure " + sprocName + " not found.");
}
_database.AddParameter(command, loopParameter.ParameterName, SqlHelper.GetDbTypeFromSqlDataType(loopParameter.DataTypeCode), loopParameter.Size, SqlHelper.GetParameterDirectionFromColumnType(loopParameter.ParameterTypeCode), loopParameter.IsNullable, (byte)loopParameter.Precision, (byte)4, loopParameter.ParameterName, DataRowVersion.Default, sprocParams[loopParameter.ParameterName]);
}
}
catch (Exception ex)
{
bool reThrow = ExceptionHandler.HandleException(ex);
if (reThrow)
{
throw;
}
}
return(command);
}
///--------------------------------------------------------------------------------
/// <summary>This method gets a sproc from the database and loads it into a generic
/// method. The results can be added to the stored procedure cache, if specified.</summary>
///
/// <param name="sprocName">Name of the stored procedure to get.</param>
/// <param name="addSprocToCache">Flag indicating whether to add sproc method to cache.</param>
///
/// <returns>A stored procedure from the db as a GenericMethod.</returns>
///--------------------------------------------------------------------------------
public GenericMethod GetSprocFromDB(string sprocName, bool addSprocToCache)
{
GenericMethod sprocMethod = null;
try
{
// go to the database to retrieve the sproc
DbCommand command = _database.GetStoredProcCommand("sp_sproc_columns");
string sprocShortName = sprocName.Replace("[", "").Replace("]", "");
if (sprocShortName.IndexOf('.') > 0)
{
sprocShortName = sprocShortName.Substring(sprocShortName.IndexOf('.') + 1);
}
_database.AddInParameter(command, "@procedure_name", DbType.String, sprocShortName);
command.Connection = Connection;
command.CommandTimeout = DBOptions.CommandTimeout;
IDataReader dbSproc = _database.ExecuteReader(command);
bool foundRows = false;
// add sproc parameters to method
while (dbSproc.Read())
{
if (foundRows == false)
{
// create the method with sproc name
sprocMethod = new GenericMethod();
sprocMethod.MethodName = sprocName;
sprocMethod.MethodParameterList = new SortableDataObjectList <GenericParameter>();
foundRows = true;
}
GenericParameter sprocParameter = new GenericParameter();
sprocParameter.ParameterName = dbSproc["COLUMN_NAME"].GetString();
if (sprocParameter.ParameterName.StartsWith("@") == true)
{
sprocParameter.ParameterName = sprocParameter.ParameterName.Substring(1);
}
sprocParameter.ParameterTypeCode = dbSproc["COLUMN_TYPE"].GetInt();
sprocParameter.DataTypeCode = dbSproc["DATA_TYPE"].GetInt();
sprocParameter.DataTypeName = dbSproc["TYPE_NAME"].GetString();
sprocParameter.Precision = dbSproc["PRECISION"].GetInt();
sprocParameter.Size = dbSproc["LENGTH"].GetInt();
sprocParameter.IsNullable = dbSproc["NULLABLE"].GetBool();
sprocParameter.Order = dbSproc["ORDINAL_POSITION"].GetInt();
sprocMethod.MethodParameterList.Add(sprocParameter);
}
dbSproc.Close();
if (foundRows == true)
{
if (addSprocToCache == true)
{
// add sproc to the cache
DbProcs.MethodList.Add(sprocMethod);
}
}
else
{
// sproc not found, throw exception
throw new System.Exception("MoPlus.Data.BuildSprocParamsWithInputValues - stored procedure" + sprocName + " not found in database.");
}
}
catch (Exception ex)
{
bool reThrow = ExceptionHandler.HandleException(ex);
if (reThrow)
{
throw;
}
}
return(sprocMethod);
}
