Connecting the SqlDataSource Component to Data Source
When you configure the
SqlDataSource component, you set the
ProviderName property to the type of database (the default is
System.Data.SqlClient ) and the
ConnectionString property to a connection string that includes information required to connect to the database. The contents of a connection string differ depending on what type of database the data source component is accessing. For example, the
System.Data.SqlClient provider requires a server name, database (catalog) name, and information about how to authenticate the user when connecting to SQL Server. For information on valid connection strings, see the
ConnectionString property topics for the
Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource(); sqlDataSource.ProviderName = "System.Data.SqlClient"; sqlDataSource.ConnectionString = "Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True"; sqlDataSource.SelectCommand = "SELECT * FROM Production.Product";
Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource() sqlDataSource.ProviderName = "System.Data.SqlClient" sqlDataSource.ConnectionString = "Data Source=(local)\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True" sqlDataSource.SelectCommand = "SELECT * FROM Production.Product"
Instead of setting connection strings at design time as property settings in the
SqlDataSource component, you can store them centrally as part of your application's configuration settings using the
connectionStrings configuration element. This enables you to manage connection strings independently of your reports, including encrypting them using Protected Configuration.
The following example shows how to connect to the SQL Server AdventureWorks sample database using a connection string which stored in the
connectionStrings configuration element named
<configuration> <connectionStrings> <add name="MyAdventureWorksDB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>
Configuration files in XML format are used in .NET Framework applications. In .NET Core and .NET 6/7 applications the configuration file usually is in JSON-format and is called
appsettings.json. The configuration of connection strings section is explained in the following documentation article: How to Host Reports Service in ASP.NET Core in .NET 6.
When the connection string is stored in the configuration file, you need to specify the name of the configuration element as a value for the
ConnectionString property of
SqlDataSource. Specifying a value for the
ProviderName property is no longer necessary, since that information is already present in the configuration element itself.
Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource(); sqlDataSource.ConnectionString = "MyAdventureWorksDB"; sqlDataSource.SelectCommand = "SELECT * FROM Production.Product";
Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource() sqlDataSource.ConnectionString = "MyAdventureWorksDB" sqlDataSource.SelectCommand = "SELECT * FROM Production.Product"
SqlDataSource component retrieves data using a SQL statement defined through the
SelectCommand property. If the data source component connects to a database that supports stored procedures, you can specify the name of a stored procedure in place of the SQL statement. You can create parameterized commands that include placeholders for values to be supplied at run time. The following example shows a typical parameterized SQL select command:
SELECT CustomerID, CompanyName FROM Customers WHERE City = @City
You can create parameter objects that specify where the command should get parameter values from at run time. You can also use expressions which values will be evaluated when processing the report or pass specific values programmatically.