Selecting Data with the SqlDataSource Component
You can specify a SQL query for the SqlDataSource
component to execute by setting its SelectCommand
property.
The
SqlDataSource
is designed to read data from the database but internally it uses the DbCommand.ExecuteReader method which does not limit the commands that can be executed against the database connection. Therefore, the command typed in theSelectCommand
property will be executed as-is, even if it contains statements such as DELETE, UPDATE, DROP, etc. To prevent potentially unwanted modifications to your database, we strongly recommend using connections with read-only permissions to fetch the data.If the SQL query returns more than one result set, 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.