private void TestWithParameterValueThatRowsAreReturned()
{
var p = _tvfTableInfo.GetAllParameters().Single();
p.Value = "5";
p.SaveToDatabase();
var qb = new QueryBuilder("", "");
qb.AddColumnRange(_tvfCatalogue.GetAllExtractionInformation(ExtractionCategory.Any));
var sql = qb.SQL;
var db = DataAccessPortal.GetInstance().ExpectDatabase(_tvfTableInfo, DataAccessContext.InternalDataProcessing);
using (var con = db.Server.GetConnection())
{
con.Open();
var r = db.Server.GetCommand(sql, con).ExecuteReader();
int rowsReturned = 0;
while (r.Read())
{
rowsReturned++;
Assert.NotNull(r["chi"]);
Assert.NotNull(r["definitionID"]);
}
Assert.AreEqual(rowsReturned, 5);
}
}
private bool SynchronizeParameters(TableValuedFunctionImporter importer, ICheckNotifier notifier)
{
var discoveredParameters = _toSyncTo.GetCurrentDatabase().ExpectTableValuedFunction(_tableToSync.GetRuntimeName(), _tableToSync.Schema).DiscoverParameters();
var currentParameters = _tableToSync.GetAllParameters();
//For each parameter in underlying database
foreach (DiscoveredParameter parameter in discoveredParameters)
{
ISqlParameter existingCatalogueReference = currentParameters.SingleOrDefault(p => p.ParameterName.Equals(parameter.ParameterName));
if (existingCatalogueReference == null)// that is not known about by the TableInfo
{
bool create = notifier.OnCheckPerformed(
new CheckEventArgs(
"TableInfo " + _tableToSync +
" is a Table Valued Function but it does not have a record of the parameter " +
parameter.ParameterName + " which appears in the underlying database", CheckResult.Fail,
null, "Create the Parameter"));
if (!create)
{
return(false); //no longer synched
}
importer.CreateParameter(_tableToSync, parameter);
}
else
{
//it is known about by the Catalogue but has it mysteriously changed datatype since it was imported / last synced?
var dbDefinition = importer.GetParamaterDeclarationSQL(parameter);
//if there is a disagreement on type etc
if (existingCatalogueReference.ParameterSQL != dbDefinition)
{
bool modify =
notifier.OnCheckPerformed(
new CheckEventArgs(
"Parameter " + existingCatalogueReference.ParameterName + " is declared as '" + dbDefinition +
"' but in the Catalogue it appears as '" +
existingCatalogueReference.ParameterSQL + "'", CheckResult.Fail, null,
"Change the definition in the Catalogue to '" + dbDefinition + "'"));
if (!modify)
{
return(false);
}
existingCatalogueReference.ParameterSQL = dbDefinition;
existingCatalogueReference.SaveToDatabase();
}
}
}
//Find redundant parameters - parameters that the catalogue knows about but no longer appear in the table valued function signature in the database
foreach (ISqlParameter currentParameter in currentParameters)
{
if (!discoveredParameters.Any(p => p.ParameterName.Equals(currentParameter.ParameterName)))
{
bool delete =
notifier.OnCheckPerformed(
new CheckEventArgs(
"TableInfo " + _tableToSync +
" is a Table Valued Function, in the Catalogue it has a parameter called " +
currentParameter.ParameterName +
" but this parameter no longer appears in the underlying database", CheckResult.Fail,
null, "Delete Parameter " + currentParameter.ParameterName));
if (!delete)
{
return(false);
}
((IDeleteable)currentParameter).DeleteInDatabase();
}
}
return(true);
}