SqlDataSource Wizard of the Report Designer

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:

1. 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. A Report 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 a base for a new connection that you can save or use as embedded in the next step. For the Report Server reports the user needs to create permission for data connections to see other sources connections and to add a new connection.

2. 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. 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 definitions 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.

3. 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.

   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 the SelectCommand 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.

4. 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.

5. 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. At design time there is no expression context, so expressions are not supported and the values must be constant.

   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 Data Explorer tool window and the Edit Expression dialog. These values do not affect the execution of the SQL query at runtime, however.

6. 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.