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