private static List <DatabaseArgument> StoredProcedureArguments(DataView dataView)
{
DataTable arguments = dataView.Table;
List <DatabaseArgument> list = new List <DatabaseArgument>();
var argumentsKeyMap = new ArgumentsKeyMap(arguments);
foreach (DataRowView row in dataView)
{
var argName = row[argumentsKeyMap.ParameterName].ToString();
//check if it's already there
var argument = AddArgumentToList(list, argName);
argument.ProcedureName = row[argumentsKeyMap.SprocName].ToString();
argument.SchemaOwner = row[argumentsKeyMap.OwnerKey].ToString();
AddPackage(row, argumentsKeyMap.PackageKey, argument);
if (!string.IsNullOrEmpty(argumentsKeyMap.OrdinalKey))
{
argument.Ordinal = Convert.ToDecimal(row[argumentsKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
}
argument.DatabaseDataType = row[argumentsKeyMap.DatatypeKey].ToString();
AddInOut(row, argumentsKeyMap.InoutKey, argument);
if (argumentsKeyMap.Db2ColumnTypeKey != null)
{
ApplyColumnType((string)row[argumentsKeyMap.Db2ColumnTypeKey], argument);
}
//Oracle: these can be decimals, but we'll assume ints
if (argumentsKeyMap.LengthKey != null)
{
argument.Length = GetNullableInt(row[argumentsKeyMap.LengthKey]);
}
if (argumentsKeyMap.PrecisionKey != null)
{
argument.Precision = GetNullableInt(row[argumentsKeyMap.PrecisionKey]);
}
if (argumentsKeyMap.ScaleKey != null)
{
argument.Scale = GetNullableInt(row[argumentsKeyMap.ScaleKey]);
}
}
return(list);
}
public void UpdateArguments(DatabaseSchema databaseSchema, DataTable arguments)
{
if (arguments.Columns.Count == 0)
{
return; //empty datatable
}
var argumentsKeyMap = new ArgumentsKeyMap(arguments);
var argumentGrouping =
arguments.Rows.OfType <DataRow>()
.ToLookup(
x =>
(x[argumentsKeyMap.OwnerKey] ?? string.Empty) + "." +
(x[argumentsKeyMap.SprocName] ?? string.Empty));
bool hasPackage = !string.IsNullOrEmpty(argumentsKeyMap.PackageKey);
//project the sprocs (which won't have packages) into a distinct view
DataTable sprocTable;
arguments.DefaultView.Sort = argumentsKeyMap.SprocName;
if (!hasPackage)
{
sprocTable = arguments.DefaultView.ToTable(true, argumentsKeyMap.SprocName, argumentsKeyMap.OwnerKey); //distinct
}
else
{
sprocTable = arguments.DefaultView.ToTable(true, argumentsKeyMap.SprocName, argumentsKeyMap.OwnerKey, argumentsKeyMap.PackageKey);
}
var sprocFilter = StoredProcedureFilter;
var packFilter = PackageFilter;
//go thru all sprocs with arguments- if not in sproc list, add it
foreach (DataRow row in sprocTable.Rows)
{
string name = row[argumentsKeyMap.SprocName].ToString();
//a procedure without a name?
if (string.IsNullOrEmpty(name))
{
continue;
}
if (sprocFilter != null && sprocFilter.Exclude(name))
{
continue;
}
string owner = row[argumentsKeyMap.OwnerKey].ToString();
if (argumentsKeyMap.IsDb2)
{
//ignore db2 system sprocs
if (IsDb2SystemSchema(owner))
{
continue;
}
}
string package = null; //for non-Oracle, package is always null
if (hasPackage)
{
package = row[argumentsKeyMap.PackageKey].ToString();
if (string.IsNullOrEmpty(package))
{
package = null; //so we can match easily
}
else if (packFilter != null && packFilter.Exclude(package))
{
continue;
}
}
var rows = argumentGrouping[owner + "." + name];
if (!string.IsNullOrEmpty(argumentsKeyMap.OrdinalKey))
{
rows = rows.OrderBy(r => r[argumentsKeyMap.OrdinalKey]);
}
List <DatabaseArgument> args = StoredProcedureArguments(rows, argumentsKeyMap);
DatabaseStoredProcedure sproc = FindStoredProcedureOrFunction(databaseSchema, name, owner, package);
if (sproc == null) //sproc in a package and not found before?
{
sproc = CreateProcedureOrFunction(databaseSchema, args);
sproc.Name = name;
sproc.SchemaOwner = owner;
sproc.Package = package;
}
else
{
//clear any existing arguments before we add them
sproc.Arguments.Clear();
}
sproc.Arguments.AddRange(args);
}
}
public void UpdateArguments(DatabaseSchema databaseSchema, DataTable arguments)
{
if (arguments.Columns.Count == 0)
{
return; //empty datatable
}
var argumentsKeyMap = new ArgumentsKeyMap(arguments);
bool hasPackage = !string.IsNullOrEmpty(argumentsKeyMap.PackageKey);
//project the sprocs (which won't have packages) into a distinct view
DataTable sprocTable;
using (DataView sprocNames = new DataView(arguments))
{
sprocNames.Sort = argumentsKeyMap.SprocName;
if (!hasPackage)
{
sprocTable = sprocNames.ToTable(true, argumentsKeyMap.SprocName, argumentsKeyMap.OwnerKey); //distinct
}
else
{
sprocTable = sprocNames.ToTable(true, argumentsKeyMap.SprocName, argumentsKeyMap.OwnerKey, argumentsKeyMap.PackageKey);
}
}
var sprocFilter = StoredProcedureFilter;
var packFilter = PackageFilter;
//go thru all sprocs with arguments- if not in sproc list, add it
foreach (DataRow row in sprocTable.Rows)
{
string name = row[argumentsKeyMap.SprocName].ToString();
//a procedure without a name?
if (string.IsNullOrEmpty(name))
{
continue;
}
if (sprocFilter != null && sprocFilter.Exclude(name))
{
continue;
}
string owner = row[argumentsKeyMap.OwnerKey].ToString();
if (argumentsKeyMap.IsDb2)
{
//ignore db2 system sprocs
if (IsDb2SystemSchema(owner))
{
continue;
}
}
string package = null; //for non-Oracle, package is always null
if (hasPackage)
{
package = row[argumentsKeyMap.PackageKey].ToString();
if (string.IsNullOrEmpty(package))
{
package = null; //so we can match easily
}
else if (packFilter != null && packFilter.Exclude(package))
{
continue;
}
}
using (DataView dv = new DataView(arguments))
{
//match sproc name and schema
dv.RowFilter = string.Format(CultureInfo.InvariantCulture, "[{0}] = '{1}' AND ISNULL([{2}],'') = '{3}'",
argumentsKeyMap.SprocName, name, argumentsKeyMap.OwnerKey, owner);
if (!string.IsNullOrEmpty(argumentsKeyMap.OrdinalKey))
{
dv.Sort = argumentsKeyMap.OrdinalKey;
}
List <DatabaseArgument> args = StoredProcedureArguments(dv);
DatabaseStoredProcedure sproc = FindStoredProcedureOrFunction(databaseSchema, name, owner, package);
if (sproc == null) //sproc in a package and not found before?
{
sproc = CreateProcedureOrFunction(databaseSchema, args);
sproc.Name = name;
sproc.SchemaOwner = owner;
sproc.Package = package;
}
else
{
//clear any existing arguments before we add them
sproc.Arguments.Clear();
}
sproc.Arguments.AddRange(args);
}
}
}
