/// <summary>
/// Loads the routines from the database.
/// </summary>
/// <param name="dataProvider">The data provider.</param>
/// <returns>
/// The loaded routines.
/// </returns>
/// <exception cref="System.ArgumentNullException">dataProvider;dataProvider cannot be null</exception>
public static Collection<Routine> LoadFromDatabase(IObjectDataProvider dataProvider)
{
Collection<Routine> result = new Collection<Routine>();
if (dataProvider == null)
{
throw new ArgumentNullException("dataProvider", "dataProvider cannot be null");
}
using (IDataReader reader = dataProvider.LoadRoutineData())
{
while (reader.Read())
{
// Read the result data for the routine
string catalog = (string)reader["SPECIFIC_CATALOG"];
string schema = (string)reader["SPECIFIC_SCHEMA"];
string name = (string)reader["SPECIFIC_NAME"];
string routineTypeAsText = (string)reader["ROUTINE_TYPE"];
string definition = (string)reader["ROUTINE_DEFINITION"];
DateTime created = (DateTime)reader["CREATED"];
DateTime lastAltered = (DateTime)reader["LAST_ALTERED"];
// Build the proper data structure for routine type
RoutineType routineType = (RoutineType)Enum.Parse(typeof(RoutineType), routineTypeAsText, true);
// Build the proper data structure for return type
SqlType returnType = null;
if (!reader.IsDBNull("DATA_TYPE"))
{
// Read the result data for the routine's return type
string dataType = (string)reader["DATA_TYPE"];
int? characterMaximumLength = reader.GetNullable<int>("CHARACTER_MAXIMUM_LENGTH");
int? numericPrecision = reader.GetNullable<int>("NUMERIC_PRECISION");
int? numericPrecisionRadix = reader.GetNullable<int>("NUMERIC_PRECISION_RADIX");
int? numericScale = reader.GetNullable<int>("NUMERIC_SCALE");
int? dateTimePrecision = reader.GetNullable<int>("DATETIME_PRECISION");
string characterSetName = reader.GetNullableString("CHARACTER_SET_NAME");
string collationName = reader.GetNullableString("COLLATION_NAME");
returnType = new SqlType(dataType, characterMaximumLength, characterSetName, collationName, numericPrecision, numericPrecisionRadix, numericScale, dateTimePrecision);
}
// Build the new routine
Routine routine = new Routine(catalog, schema, name, routineType, returnType, definition, created, lastAltered);
result.Add(routine);
}
}
// Populate parameters
foreach (Routine routine in result)
{
RoutineParameter.PopulateParameters(routine, dataProvider);
}
return result;
}
/// <summary>
/// Load the data describing all parameters for a given routine.
/// </summary>
/// <param name="routine">The routine.</param>
/// <returns>
/// The parameter data
/// </returns>
/// <exception cref="System.ArgumentNullException">routine;routine cannot be null</exception>
public IDataReader LoadParametersDataForRoutine(Routine routine)
{
if (routine == null)
{
throw new ArgumentNullException("routine", "routine cannot be null");
}
using (SqlCommand command = new SqlCommand(SqlObjectDataProvider.RoutineParametersQuery, this.sqlConnection))
{
command.Parameters.AddWithValue("RoutineCatalog", routine.Catalog);
command.Parameters.AddWithValue("RoutineSchema", routine.Schema);
command.Parameters.AddWithValue("RoutineName", routine.Name);
return command.ExecuteReader();
}
}
public IDataReader LoadParametersDataForRoutine(Routine routine)
{
DataTable mockTableData = new DataTable();
return mockTableData.CreateDataReader();
}
