Procedure ResultSets

Procedure ResultSets

A stored procedure may return one or more result sets, a set of rows with columns.

In the DatabaseSchema model, a DatabaseStoredProcedure has a list property called ResultSets. Normally this will be an empty list; you have to explicitly load the result sets from the database.

To discover the result sets, we have to call the stored procedure. We use the DbDataAdapter.FillSchema method (in SqlServer this uses the FOR BROWSE clause; we rely on the provider for the equivalent in other databases). We wrap it in a transaction, that we roll back.

Warning!

If there are any parameters, we invent them. All numbers are 1, all strings are "1", all dates are the current date. For Oracle, ref cursors are handled correctly. If there are other types of parameters this may not work. If those parameters cause errors, the reading will fail.

The danger is that if the parameters cause a very long running operation, or there are side effects (sending emails etc). If you suspect the procedures can do this, or you don't know the database, do not try to read the procedures this way.

Use

Use the DatabaseSchemaReader.Procedures.ResultSetReader

In .net full framework, use this syntax:

var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var rsr = new ResultSetReader(schema);
rsr.Execute();
//...do something with schema

The feature is only available in Core 2.0 (and if the database provider is netstandard 2.0) In .netStandard 2.0, use this syntax:

using (var connection = new SqlConnection(connectionString))
{
    var dr = new DatabaseSchemaReader.DatabaseReader(connection);
    var schema = dr.ReadAll();
    var rsr = new ResultSetReader(schema);
    rsr.Execute();
	//...do something with schema
}

Then you can read the result sets. The name, DbDataType, Length and Nullable should be set.

var sproc = schema.StoredProcedures.Find(x => x.Name == "SalesByCategory");
var rs = sproc.ResultSets.First(); //there is only one result set
foreach (var rsColumn in rs.Columns)
{
	Console.WriteLine(rsColumn.Name);
	Console.WriteLine(rsColumn.DbDataType);
}