The SQL Data Source Wizard allows you to create a new or edit an existing SqlDataSource component based on a SQL select statement or a database stored procedure. After the wizard appears you have to perform the following steps:
Choose data connection
Choose an existing connection from the list of connections or specify a new connection. All local, server (if connected to Report Server) and Server Explorer (for Visual Studio) connections are listed.
Server Explorer connection will not be listed if it is having the same connection string alias as a local one. If you choose a Server Explorer connection, you can save it as local or use it as embedded in the next step.
If you have connected Standalone Report Designer to a Report Server you can see the server connections in this list. Server report can use only server connections and a local report can use only local connections. If you choose a connection from another source it will be used as base for a new connection that you can save or use as embedded in the next step.
For report server report the user need create permission for data connections to see other sources connections and to add a new connection.
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 in the application configuration file under a specific name or use the connection from the shared store. What is the exact action is described in the radio button description below. 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.
For new server reports embedded connections option is not allowed.
Saving connection strings in the application configuration file (or application store for connections) simplifies the process of maintaining your application if the database connection changes. In the event of a change in the database connection you can edit the connection string in the application store (configuration) as opposed to editing all the report definition and having to recompile your application. However if you want to render the report definition in the context of another project or application you have to make sure that connection string with the same name is available in the application and have access to the database.
Configure Data Source Command
Choose whether you want to base your SqlDataSource on a SQL select statement or a stored procedure:
Choosing the "Select Statement" option allows you to enter a SQL query statement directly in the text box below. Alternatively you can click "Query Builder" to invoke the Visual Studio Query Builder dialog. In the context of the Standalone Report Designer the button will invoke the Query Designer in the Standalone Designer. Both are user-friendly interfaces for building SQL queries.
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.
You can bind a data source parameter directly to a report parameter. This way you can use the standard report parameter user interface to provide values for your data source parameters. The drop-down list of the "Value" column displays all existing report parameters. You can create a new report parameter by choosing <New Report Parameter> from the list. Alternatively you can invoke the Edit Expression dialog by choosing <Expression> from the list. This allows you 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 of the SqlDataSource component.
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 in order to populate the schema displayed in the Data Explorer tool window and the Edit Expression dialog. These values do not affect the execution of the SQL query at runtime however.
Preview Data Source Results
You can 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 wizard displays a popup dialog before each preview, which allows you to enter the parameter 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.