Selecting Data with the SqlDataSource Component

You can specify a SQL query for the SqlDataSource component to execute by setting its SelectCommand property.

If the SQL query returns more than one result sets, only the first set will be used.

The following example demonstrates a SQL query that retrieves a result set consisting of the names of all the persons in the Contact table from the AdventureWorks sample database:

SELECT FirstName, LastName FROM Person.Contact;

The following code example shows how to set the ConnectionString and SelectCommand properties of a SqlDataSource component to retrieve the data from the above SQL query:

Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
sqlDataSource.ConnectionString = "MyAdventureWorksDB";
sqlDataSource.SelectCommand = "SELECT FirstName, LastName FROM Person.Contact";

Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource()
sqlDataSource.ConnectionString = "MyAdventureWorksDB"
sqlDataSource.SelectCommand = "SELECT FirstName, LastName FROM Person.Contact"

If the database you are working with supports stored procedures, you can set the SelectCommand property to the name of an existing stored procedure and the SelectCommandType property to StoredProcedure to indicate that the SelectCommand property refers to a stored procedure. The following example demonstrates a simple stored procedure that you can create in SQL Server:

CREATE PROCEDURE GetAllContacts AS
SELECT FirstName, LastName FROM Person.Contact;
GO

To configure the SqlDataSource component to use this stored procedure, set the SelectCommand text to "GetAllContacts" and the SelectCommandType property to StoredProcedure:

Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
sqlDataSource.ConnectionString = "MyAdventureWorksDB";
sqlDataSource.SelectCommand = "GetAllContacts";
sqlDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;

Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource()
sqlDataSource.ConnectionString = "MyAdventureWorksDB"
sqlDataSource.SelectCommand = "GetAllContacts"
sqlDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure

At run time, the SqlDataSource component submits the text in the SelectCommand property to the database, and the database returns the result of the SQL query or stored procedure back to the SqlDataSource component. Any data items that are bound to the data source component display the result set on your report.