Setting the Generic ADO.Net Data Source

The Generic ADO.Net Data Source executes queries against any data source supported by ADO.Net. ADO.Net providers are provided for Oracle, OLE DB, ODBC and SQL Server as part of the ASP.Net environment. Additional providers can be downloaded and installed for many other databases, including MySQL (see http://dev.mysql.com/downloads/connector/net/5.0.html) , IBM DB2 (see http://www-306.ibm.com/software/data/db2/windows/dotnet.html) and others. However, unlike the Microsoft SQL Server Data Source, a Connection String must be manually entered because each database provider has its own format for these strings. There are many resources on the web, such as http://www.connectionstrings.com, that help build these connection strings. Note: This Data Source does not support the "{oQ}" and "{dO}" tokens since it is assumed the queries are for databases that are not related to DNN.

The Generic ADO.Net Data Source can be configured in the "Data Source Settings" section under Report settings.

Here a report title can be entered. Note: "Title" and "Description" are both displayed on the module when "Show Info Pane" is selected and they are also included in exported content. The URL Querystring parameters to be used in this query must be entered into the "Allowed Url Parameters" text box.

Generic ADO.Net Data Source must be selected as the "Active Data Source" and these setting configured:

• At ADO.Net Data Provider, select a data provider from a list of installed ADO.Net data providers.
• In the Connection String text box, enter a complete ADO.Net Connection String for connecting to the data source.
• In the Parameter Prefix text box, enter a parameter prefix - OR - Leave this field blank as required. Review the section on Parameters (See "Overview of Data Sources") and see notes below for more details.

The SQL query can be set by uploading an SQL file or entering an SQL query into the Query text box.

Optional. See "Setting Converters".

Note: The Generic ADO.Net Data Source supports the four default parameters listed (See "Overview of Data Sources"). However, since different database providers support parameters in different ways, there are a few caveats:

• They cannot be used with Data Sources such as Microsoft Access which do not support named parameters.
• If the data source expects parameter names to start with a specific prefix (for example, SQL Server requires they be prefixed with the "@" character), that prefix must be entered in the Parameter Prefix setting.
• They cannot be used with Data Sources that expect parameter names to end with a specific suffix.