Using Parameters with the SqlDataSource Component

Users often request data based on parameters that can be resolved or evaluated only at run time. For example, the data displayed in a report might depend on a specific input value. If the user selects a different value, that data can also change. Whether the value is changed explicitly by the user or programmatically by the application, the SQL query that you submit to the database can be made more flexible and more maintainable if it is a parameterized SQL query, in which elements of the SQL statement are bound to parameters and evaluated at run time. Moreover using parameterized SQL queries to filter the data of the report might improve the performance and decrease the memory footprint of your application, because the filter is applied directly on the database server and only the necessary data is downloaded on the middle tier or client machine.

The SqlDataSource component supports parameterized SQL queries by associating the parameters you add to the Parameters collection with placeholders in the SelectCommand query. Parameter values can be evaluated with any expression which conforms to the common expression syntax supported by the reporting engine. This grants you a great deal of flexibility on how you can supply your SQL queries with parameters. For example, you can bind a SQL query parameter directly to a report parameter without any extra coding at all.

Each SqlDataSource parameter should be linked to an Expression, for example, a Report Parameter value in the report definition. The SqlDataSource parameters cannot receive values directly from the ReportSource.Parameters collection.

The syntax used for the placeholders varies, depending on the type of the database. If you are working with SQL Server, the parameter name begins with the '@' character, and its name corresponds to the name of the parameter object in the Parameters collection. For Oracle databases the parameters are prefixed with the ':' character instead, and are referenced by name in the Parameters collection as well. If you are working with an ODBC or OLE DB database, parameters in a parameterized statement are not named and instead are specified with the placeholder character '?'.

The SqlDataSource Wizard can detect SQL parameters listed in the SQL query, and it will ask you to provide values for them at Configure Data Source Parameters step. The information is provided by the selected.NET data provider, which implementation is important for determining what SQL syntax can be used for the SQL query.

Using Parameters with the SqlClient Provider

The System.Data.SqlClient provider supports named parameters as placeholders, as shown in the following example:

SELECT * FROM Person.Contact WHERE FirstName = @FirstName AND LastName = @LastName

With named parameters, the order in which the parameters are specified in the command's parameters collection is not important. However, you must ensure that the parameter names that you use in your SQL command correspond to the names of the parameters in the associated collection. The following example shows how to use named parameters in an SQL command for a SqlDataSource component that uses the System.Data.SqlClient provider:

Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
sqlDataSource.ConnectionString = "MyAdventureWorksDB";
sqlDataSource.SelectCommand = "SELECT * FROM Person.Contact WHERE FirstName = @FirstName AND LastName = @LastName";
sqlDataSource.Parameters.Add("@FirstName", System.Data.DbType.String, "John");
sqlDataSource.Parameters.Add("@LastName", System.Data.DbType.String, "Smith");

Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource()
sqlDataSource.ConnectionString = "MyAdventureWorksDB"
sqlDataSource.SelectCommand = "SELECT * FROM Person.Contact WHERE FirstName = @FirstName AND LastName = @LastName"
sqlDataSource.Parameters.Add("@FirstName", System.Data.DbType.String, "John")
sqlDataSource.Parameters.Add("@LastName", System.Data.DbType.String, "Smith")

Using Parameters with the OLE DB and ODBC Providers

The System.Data.OleDb and System.Data.Odbc providers support only positional parameters identified by the '?' character, as shown in the following example:

SELECT * FROM Person.Contact WHERE FirstName = ? AND LastName = ?

When you use the System.Data.OleDb and System.Data.Odbc providers with parameterized SQL query statements, the order in which you specify the parameter placeholders must match exactly the order of the parameter objects in the Parameters collection of the SqlDataSource component. Specifying names for the parameters in this case is only for informative purposes, these names are not used when binding the parameter values to the SQL query statement.

The following example shows how to specify parameters for a SqlDataSource component that uses the System.Data.OleDb provider:

Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
sqlDataSource.ProviderName = "System.Data.OleDb";
sqlDataSource.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI";
sqlDataSource.SelectCommand = "SELECT * FROM Person.Contact WHERE FirstName = ? AND LastName = ?";
sqlDataSource.Parameters.Add("FirstName", System.Data.DbType.String, "John");
sqlDataSource.Parameters.Add("LastName", System.Data.DbType.String, "Smith");

Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource()
sqlDataSource.ProviderName = "System.Data.OleDb"
sqlDataSource.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI"
sqlDataSource.SelectCommand = "SELECT * FROM Person.Contact WHERE FirstName = ? AND LastName = ?"
sqlDataSource.Parameters.Add("FirstName", System.Data.DbType.String, "John")
sqlDataSource.Parameters.Add("LastName", System.Data.DbType.String, "Smith")

Binding SQL Query Parameters to Report Parameters

The following code example shows how to bind the parameters of an SQL query to the corresponding parameters of the report using expressions:

var report = new Report1();
report.ReportParameters.Add("FirstName", ReportParameterType.String, "John");
report.ReportParameters.Add("LastName", ReportParameterType.String, "Smith");
Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
sqlDataSource.ConnectionString = "MyAdventureWorksDB";
sqlDataSource.SelectCommand = "SELECT * FROM Person.Contact WHERE FirstName = @FirstName AND LastName = @LastName";
sqlDataSource.Parameters.Add("@FirstName", System.Data.DbType.String, "=Parameters.FirstName");
sqlDataSource.Parameters.Add("@LastName", System.Data.DbType.String, "=Parameters.LastName");
report.DataSource = sqlDataSource;

Dim report As Telerik.Reporting.Report = New Telerik.Reporting.Report()
report.ReportParameters.Add("FirstName", ReportParameterType.String, "John")
report.ReportParameters.Add("LastName", ReportParameterType.String, "Smith")
Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource()
sqlDataSource.ConnectionString = "MyAdventureWorksDB"
sqlDataSource.SelectCommand = "SELECT * FROM Person.Contact WHERE FirstName = @FirstName AND LastName = @LastName"
sqlDataSource.Parameters.Add("@FirstName", System.Data.DbType.String, "=Parameters.FirstName")
sqlDataSource.Parameters.Add("@LastName", System.Data.DbType.String, "=Parameters.LastName")
report.DataSource = sqlDataSource