Changing Oracle Databases at Run-time with Crystal Reports Viewers in ASP.NET

As part of the legacy application upgrade I’ve been doing, I decided to move the Crystal Reports to a Web application rather that having the users access them directly on Crystal Reports XI via Citrix, which has been the method they have been using.  This has several advantages, the primary one being speed.  One problem that plagued me was how to change the logon information at run-time, such that the application would automatically point at the correct Oracle database (development, test, or production) based on which server the report was being accessed from.

The solution I found was to set up the Oracle Database connection as a DSN in ODBC, and connecting to the ODBC DSN rather than using the Oracle Client directly.  This is not the only way to do it, but it seems to be the best way for my purposes.

In the code-behind file for the page that contains the Crystal Reports Viewer, I placed the following code that handles the same event that renders the viewer.

Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
Dim connInfo As New ConnectionInfo
Dim rptDoc As New ReportDocument

' setup the connection
With connInfo
.ServerName = "oracledsn" ' ODBC DSN in quotes, not Oracle server or database name
.DatabaseName = "" ' leave empty string here
.UserID = "username" ' database user ID in quotes
.Password = "password"  'database password in quotes
End With

' load the Crystal Report
rptDoc.Load(Server.MapPath(Utilities.AppSettingsFunction.getValue("ReportFolder") & ddlReports.SelectedValue))

' add required parameters
If pnlstartdates.Visible Then
rptDoc.SetParameterValue("REPORT_DATE", txtSingleDate.Text)
End If

' apply logon information

For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
repTblLogonInfo.ConnectionInfo = connInfo

' Set, bind, and display Crystal Reports Viewer data source
Session("rptDoc") = rptDoc
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
 End Sub

The logon info above can easily be stored in web.config instead of hard-coding it as above.

Incidentally, I chose to put my Crystal Reports Viewer in an ASP.NET AJAX Update Panel, which is why the ReportSource of the viewer is stored in a Session variable.  If you choose to do this, the viewer must be databound in the Init event (not the Load event) to show up properly.

Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
If Not Page.IsPostBack Then
txtSingleDate.Text = Now.Date()
ElseIf Session("rptDoc") IsNot Nothing Then
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
End If
 End Sub

Setting the Connection String Programmatically on a Crystal Reports ReportDocument

Programmatically setting the connection string for a Crystal Reports ReportDocument will allow you to have the report automatically use the active connection string for the rest of the application (as in previous posts). In this case, the report is created using a method that is triggered by clicking a button on a form. The log on information must be applied to each table in the ReportDocument.

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim crReportDocument As ReportDocument
            crReportDocument = New ReportDocument
            crReportDocument.Load(<<name of reportdocument>>)

            Dim sConn As SqlConnectionStringBuilder = New SqlConnectionStringBuilder(<<connection string>>)

            Dim tables As CrystalDecisions.CrystalReports.Engine.Tables = crReportDocument.Database.Tables

            For Each table As CrystalDecisions.CrystalReports.Engine.Table In tables
                Dim tableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo = table.LogOnInfo
                tableLogOnInfo.ConnectionInfo.ServerName = sConn.DataSource
                tableLogOnInfo.ConnectionInfo.DatabaseName = sConn.InitialCatalog
                tableLogOnInfo.ConnectionInfo.IntegratedSecurity = True

            ' ...

       End Sub

Dynamically Setting the Connection String for Session State on SQL Server

In ASP.NET, there are four ways to store Session State.  The way I prefer to store it is using SQL Server.  However, instead of storing it in the ASPstate database, which is the default way to store it on SQL Server, I choose to store it in the application database, which is a “custom SQL database” implementation.

When storing Session State in the application database, the web.config will have an entry that points at the database server and the name of the database:

<sessionState mode="SQLServer" timeout="20" allowcustomsqldatabase="true"
   sqlconnectionstring="Data Source=Server;Initial Catalog=databasename; User ID=UserID;
   Password=Password;" cookieless="false" />

Because this is set in web.config, it suffers from the same problem that I wrote about in this post — namely, that the connection string must be changed when moving between different environments (such as development, test / staging , and production).

To remedy this, a function that returns the dynamic connection string for the application should already be in place.  Something like this will work:

using System.Configuration;

public class ConnectionStrings
   public static string _AppDB_ConnectionString = ConfigurationManager.ConnectionStrings["AppDB_ConnectionString_" + ConfigurationManager.AppSettings.Get("Environment").ToUpper()].ConnectionString;

(This is assuming that you have an “Environment” key set in the appSettings section of either web.config or machine.config, and that your connection strings are named accordingly in your web.config connectionStrings section.)

Next, a PartitionResolver class must be created that can get the correct connection string by using the function declared above:

public class MyPartitionResolver : System.Web.IPartitionResolver
   public void Initialize()
   //Empty Initializer

   public string ResolvePartition(object key)
     return ConnectionStrings._AppDB_ConnectionString;

Lastly, the sessionState entry in web.config must be modified to look like this:

<sessionState mode="SQLServer" allowCustomSqlDatabase="true" partitionResolverType="MyPartitionResolver"
regenerateExpiredSessionId="true" compressionEnabled="true" useHostingIdentity="true" timeout="480"/>

Note: The options after “partitionResolverType” can be set according to your needs.

Dynamically Set the ConnectionString Attribute on a SqlDataSource ASP.NET Control

I store the connection strings for all three of my environments (development, test, production) in the “connectionstrings” section of web.config on most of my ASP.NET Web applications. When deploying the app to one of these environments, I either have to change a key called “Environment” in the appSettings section of web.config, or I can just put it in machine.config on the server. Connection strings:

<add name="AppDB_ConnectionString_DEVELOPMENT"
         connectionString="Data Source=DEV_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString_TEST"
         connectionString="Data Source=TEST_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString_PRODUCTION"
         connectionString="Data Source=PROD_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>

Environment key:

   <add key="Environment" value="PRODUCTION"/>

Setting the connection string attribute on a SqlDataSource control can be done in a number of ways. It can be programmatically done in the code-behind file, and this is the way it is most often done dynamically. This is not always convenient, as you may have to spend lots of time determining the event during which the string must be set.

SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("whatever")

For instance, if the SqlDataSource control is embedded inside the EditTemplate of a FormView control, it will not be accessible during Page.Load, and if a parameter of the SqlDataSource control is databound, you may have to navigate a Byzantine maze of ASP.NET lifecycle events to find out that it needs to be set in the Load event of some seemingly unrelated control.

To solve this problem, many people give up and use only one active connection string at a time in web.config, and comment the others out:

<add name="AppDB_ConnectionString" connectionString="
         Data Source=DEV_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<!-- <add name="AppDB_ConnectionString" connectionString="
         Data Source=TEST_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/>
<add name="AppDB_ConnectionString" connectionString="
         Data Source=PROD_DBSERVERNAME;
         Initial Catalog=DBNAME; ... providerName="System.Data.SqlClient"/> -->

Then, “dynamically” setting the string in the SqlDataSource markup like this:

<asp:SqlDataSource ID="SqlChildren" runat="server"
   ConnectionString="<%$ ConnectionStrings:AppDB_ConnectionString %>"
   SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
      <asp:ControlParameter ControlID="lblAppID" DefaultValue="0"
        Name="SourceID" PropertyName="Text" Type="Int32" />

This works fine, but will require a change in the connection string in web.config to move from one environment to another. At first, I assumed (and we all know what that does!) that one could dynamically set the attribute using the standard inline < %= %> notation like this:

<asp:SqlDataSource ID="SqlChildren" runat="server"
   System.Configuration.ConfigurationManager.AppSettings.Get("Environment") &
   ...[connection string] ... %>"
   SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
      <asp:ControlParameter ControlID="lblAppID" DefaultValue="0" Name="SourceID"
        PropertyName="Text" Type="Int32" />

Anyone who has tried this already knows what happened next. This caused a run-time error when the SqlDataSource tried to bind to the expression. After much wailing and gnashing of teeth, I found that it IS possible to dynamically set the connection string in a manner similar to the standard way! Instead of the < %= %> expression, a custom expression using the   < %$ %> notation will work.

First, to build the custom expression in a class:

Imports System
Imports System.CodeDom
Imports System.Web.UI
Imports System.ComponentModel
Imports System.Web.Compilation

' Apply ExpressionEditorAttributes to allow the
' expression to appear in the designer.
<ExpressionPrefix("ConnStringExpression")> _
<ExpressionEditor("ConnStringExpressionEditor")> _
Public Class ConnStringExpressionBuilder
   Inherits ExpressionBuilder
   ' Create a method that will return the result
   ' set for the expression argument.
   Public Shared Function GetEvalData(ByVal expression As String, _
     ByVal target As Type, ByVal entry As String) As Object
      Return System.Configuration.ConfigurationManager.AppSettings("Environment") &
         "_" & _
   End Function

   Public Overrides Function GetCodeExpression(ByVal entry _
     As BoundPropertyEntry, ByVal parsedData As Object, ByVal context _
     As ExpressionBuilderContext) As CodeExpression
      Dim type1 As Type = entry.DeclaringType
      Dim descriptor1 As PropertyDescriptor = _
      Dim expressionArray1(2) As CodeExpression
      expressionArray1(0) = New CodePrimitiveExpression(entry.Expression.Trim())
      expressionArray1(1) = New CodeTypeOfExpression(type1)
      expressionArray1(2) = New CodePrimitiveExpression(entry.Name)
      Return New CodeCastExpression(descriptor1.PropertyType, _
      New CodeMethodInvokeExpression(New CodeTypeReferenceExpression _
      (MyBase.GetType()), "GetEvalData", expressionArray1))
   End Function
End Class

Next, in your web.config file, add this to the compilation section:

<add expressionPrefix="ConnStringExpression"

Lastly, change the ConnectionString attribute in the markup for the SqlDataSource control to:

<asp:SqlDataSource ID="SqlChildren" runat="server"
    ConnectionString="<%$ ConnStringExpression:AppDB %>"
SelectCommand="sp_SourceSelectAllChildren" SelectCommandType="StoredProcedure">
<asp:ControlParameter ControlID="lblAppID" DefaultValue="0"
            Name="SourceID" PropertyName="Text" Type="Int32" />


You can now have multiple connection strings declared in the web.config, and set them dynamically in your markup rather than in the code-behind.

Note: What goes after the “ConnStringExpression:” is incidental in this case; I just put “AppDB” for readability purposes.