Using the DbCommandSetDataProvider With Stored Procedures

Valid from List & Label 23
Use the DbCommandSetDataProvider in order to enable stored procedures as data sources. Pass your Command instances to it, and set the CommandType property to CommandType.StoredProcedure in each case. The exact procedure is shown below using a small C# example that uses the well-known Northwind database on a Microsoft SQL Server:
...
using (ListLabel LL = new ListLabel())
{
    // Configure connection to SQL Server
    SqlConnectionStringBuilder sqlConnectionParameters = new SqlConnectionStringBuilder();
    sqlConnectionParameters.DataSource = "<Your MS SQL Server Instance Name>";
    sqlConnectionParameters.InitialCatalog = "Northwind";
    sqlConnectionParameters.IntegratedSecurity = true;
    SqlConnection sqlConnection = new SqlConnection(sqlConnectionParameters.ToString());

    // Configure SqlCommand for stored procedure
    SqlCommand sqlStoredProcedureCommand = new SqlCommand("[dbo].[CustOrderHist]", sqlConnection);
    sqlStoredProcedureCommand.CommandType = CommandType.StoredProcedure;
    sqlStoredProcedureCommand.Parameters.AddWithValue("@CustomerID", "ALFKI");

    // Configure data provider
    DbCommandSetDataProvider dataProvider = new DbCommandSetDataProvider();
    dataProvider.AddCommand(sqlStoredProcedureCommand, "CustOrderHist");
    LL.DataSource = dataProvider;

    // Call designer
    LL.Design();
}
...

Starting with List & Label 26, you can even parametrize stored procedures, i.e. automatically create report parameters for the stored procedure’s parameters and use them as described here in our blog. A simple sample procedure would read:

CREATE PROCEDURE [dbo].[SelectAllCustomers] @CompanyName nvarchar(50) = 'Health Spa, Limited'
AS
SELECT * FROM SalesLT.Customer Customer WHERE Customer.CompanyName = @CompanyName

In order to create a report parameter for the @CompanyName parameter of the procedure you’d use the following code:

var provider = new DbCommandSetDataProvider();
var conection = new SqlConnection(...);
connection.Open();

// create command for stored procedure
var command = new SqlCommand("SelectAllCustomers", connection);
command.CommandType = CommandType.StoredProcedure;

// create parameter. Note the syntax for the value as documented in the blog post linked above
command.Parameters.AddWithValue("@CompanyName", "{{CompanyName=Health Spa, Limited}}");
provider.AddCommand(command, "SelectAllCustomers");

using (ListLabel LL = new ListLabel())
{
    LL.DataSource = provider;
    LL.Design();
}
...
IDKBTE001343 KBTE001343