The SQL Data Source Wizard allows you to create a new or edit an existing SqlDataSource component. This article describes the SQL Data Source Wizard steps:
Choose data connection
Choose an existing connection from the list of connections or specify a new connection. The Select from existing data connections option contains a drop-down that lists the available predefined connection strings.
Local connection strings from the application configuration file and theconnections are listed.
In the context of the Report Server, all report server data connections that the current user has permissions to read.
The default implementation of theinterface - enables the designer users to save and use connection strings from the local Web Report Designer settings file. The location of the designer settings file is specified with the required constructor argument
If using the designer from a Report Server, this drop-down lists the server data connections. Server reports support only saved server data connections.
For Report Server report, user create/read permissions for data connections are required.
Data connection options
For server reports, this page is skipped when an existing report server connection is used.
Choose "Use as shared connection" to store the connection string under a specific name or use the connection from the shared store. The specific action depends on the wizard context and is described in the radio button description. The second option, "Embedded in the report definition" will save the connection string in the report definition. Thus it will be available only for this report and will be available no matter the report definition location.
Embedded connection string in the report functionality is forbidden in the Report Server context otherwise, this can be controlled by overriding thethat returns serialized in camelCase JSON.
For new Report Server reports, embedded connections option is not allowed.
Saving connection strings simplifies the process of maintaining your application if the database connection changes. In the event of a change in the database connection, edit the connection string in the application store (configuration) as opposed to editing all the report definition. However, to render the report definition in the context of another project or application, make sure that a connection string with the same name is available in the application and have access to the database.
Configure Data Source Command
Choose whether to base the SqlDataSource on a SQL Select Statement or a Stored Procedure:
Selecting the "Select Statement" option will enable entering a SQL query statement directly in the text box below.
Choosing the "Stored Procedure" option allows you to select the name of an existing database Stored Procedure from the drop-down list below, which is responsible for the data retrieval.
Configure Data Source Parameters
Specify the parameters for the SQL Query or Stored Procedure. Click "New" to add a new parameter to the SqlDataSource component or "Delete" to remove an existing parameter. Use the arrow buttons to change the order of the parameters when necessary.
Specify a valid name for each data source parameter in the "Name" column. You can also choose that name from the list of existing parameter names, which are derived directly from the SQL Query Statement or Stored Procedure.
Choose the appropriate database type for the parameter in the "DbType" column. This type should match exactly the type of the expression in the SQL query statement or the type of the parameter from the stored procedure.
Specify a default value for the parameter in the "Value" column or bind it via an expression.
Bind a data source parameter directly to a report parameter to use the standard Report Parameter user interface to provide values for data source parameters. The drop-down list of the "Value" column displays all existing report parameters. Alternatively, invoke the Edit Expression dialog by choosing <Expression> from the list, to specify an arbitrary expression to evaluate for the parameter value.
Configure Design Time Parameters
This step allows you to specify a value for each data source parameter that can be used at design time to retrieve the schema and preview the SqlDataSource component data.
Specifying design-time values for the parameters is necessary because the designer might need to execute the SQL query or stored procedure of the data source to populate the schema displayed in the Explorer tool window and the Edit Expression dialog. These values do not affect the execution of the SQL query at runtime.
Preview Data Source Results
Preview the result set returned by the SqlDataSource before completing the wizard. Click Execute Query to execute the SQL query or stored procedure and display the retrieved data.
If the data source has parameters, the preview uses the Design Time Parameters values.
This is the last step of the wizard. After pressing "Finish" the wizard will configure the SqlDataSource component with the specified settings and close.