How to use SQL functions as a data source in List & Label

Knowledgebase English KB
1

Introduction

This article shows how to use SQL functions as a data source in List & Label.

Notes

Please note that it is mandatory to disable the following two properties of the DbCommandSetDataProvider.

  • Property DisableIdentifierValidation
    List & Label relies on the SQL statements used being correct and therefore does not check the column names themselves for correctness, i.e. the SQL statements are passed through 1:1. Therefore set this property to true.

  • Property MinimalSelect
    The adjustment of the SQL statements at print time by List & Label must be prevented. Therefore set this property to false as well.

    Caution: This can lead to performance losses depending on the size of the data source.

Programming example

// Create a new DbCommandSetDataProvider object
DbCommandSetDataProvider provider = new DbCommandSetDataProvider();

// Disable the IdentifierValidation
provider.DisableIdentifierValidation = true;

// Create a new SQL Command
SqlCommand command = new SqlCommand("select * from myFunction(@Param)");

// Set up a SQL Connection
SqlConnection sqlConnection = new SqlConnection(<Add your SQL Connection here>);
command.Connection = sqlConnection;

// Set the CommandType to Text
command.CommandType = CommandType.Text;

// Add the Parameter
command.Parameters.AddWithValue("Param", 1);

// Pass the SQL Command to the DbCommandSetDataProvider
provider.AddCommand(command, "myFunction");

// Do not adapt the Select statement
provider.MinimalSelect = false;

// Pass the DataSource to List & Label
LL.DataSource = provider;