Currently I am extending and enhancing the reporting system for our test management tool Zeta Test.

In this article I will describe the issues I discovered (and solved) when trying to design one single report that works with both Microsoft SQL Server and VistaDB.

Creating a report

I decided to go with XtraReports from DevExpress as the reporting system.

The basic design steps creating and deploying a report with XtraReports is similar to methods when working with e.g. Microsoft SQL Server Reporting Services:

1. Create a new report in the designer of XtraReports.
2. Specify connection string/settings (using the SqlClient namespace classes).
3. Specify tables/SQL queries to use as the data source for the report.
4. Design your report graphically.
5. Save the report to a .REPX file.
6. Pack the .REPX file into the deployment setup.
7. Ship the setup to the customers.

Displaying a report

In Zeta Test I had to perform the following steps when displaying a report:

1. Load the report.
2. Replace the design-time connection string by the connection string to the currently used database of the currently loaded project in Zeta Test.
3. Display the report in a print-preview form.

Two databases

Since a project in Zeta Test can either use Microsoft SQL Server or VistaDB as the back-end database, I wanted to design a single report so that it can be used with both back-end databases.

Inially I planned to simply design the reports with an OleDB connection to the SQL Server database and then later when displaying the report for a VistaDB database, simply switch the connection string to the VistaDB database.

Unfortunately there is no OleDB provider for VistaDB. Therefore this solution did not work. Another idea was to simply leave the SqlClient classes that were used when designing the report as they are, only changing the connection string. This did not work, either.

So I did come up with another solution that works quite well as of writing this article:

Rewriting the .REPX report

A .REPX report file is a C# source code file that gets compiled when loading the report with the XtraReport.LoadLayout method.

I decided to give the following „algorithm“ a try:

1. Load a .REPX file that was designed with the SqlClient classes as a string into memory.
2. Do some string replacements to replace all SqlClient classes by their VistaDB counterparts.
3. Call XtraReport.LoadLayout on the replaced string.
4. Display the report in a print-preview form.

Luckily, after some try and error I actually was able to get this working!

In the following chapter I outline some of the methods I created in order to hopefully give other developers some ideas to use in their own applications:

Code to rewrite a .REPX report

The following method is the main method that is being called in order to load and display a report. It is a member of the print-preview form:

public void Initialize(
    FileInfo reportFilePath,
    Pair<string, string>[] parameters)
{
    var report = new XtraReport();

    if (Host.Current.CurrentProject.Settings.IsVistaDBDatabase)
    {
        var rawContent = UnicodeString.ReadTextFile(reportFilePath);

        var replacedContent = replaceSqlClientForVistaDB(rawContent);

        using (var ms = new MemoryStream(
            Encoding.Default.GetBytes(replacedContent)))
        {
            report.LoadLayout(ms);
        }
    }
    else
    {
        // Assumes that the report was designed with SQL Server.
        // If not, one would have to replace the other way, i.e.
        // from VistaDB to SQL Server.
        // I am leaving this out until really needed.
        report.LoadLayout(reportFilePath.FullName);
    }

    // --
    // Change connection string.

    var da = report.DataAdapter as DbDataAdapter;

    if (da != null)
    {
        adjustConnectionString(da.SelectCommand);
        adjustConnectionString(da.InsertCommand);
        adjustConnectionString(da.DeleteCommand);
        adjustConnectionString(da.UpdateCommand);
    }

    // --
    // Adjust parameters.

    if (parameters != null && parameters.Length > 0)
    {
        foreach (var parameter in parameters)
        {
            if (containsParameter(report.Parameters, parameter.First))
            {
                report.Parameters[parameter.First].Value = parameter.Second;
            }
        }
    }

    // --
    // Bind the report's printing system to the print control.

    printControl.PrintingSystem = report.PrintingSystem;

    // Generate the report's print document.
    report.CreateDocument();
}

The actual method to replace the string is the following:

private static string replaceSqlClientForVistaDB(string content)
{
    if (string.IsNullOrEmpty(content))
    {
        return content;
    }
    else
    {
        var result = content;

        // Add reference.
        result =
            result.Replace(
                Environment.NewLine + @"///   </References>",
                string.Format(
                    Environment.NewLine + @"///     <Reference Path=""{0}"" />" +
                    Environment.NewLine + @"///   </References>",
                    typeof (VistaDB.IVistaDBValue).Assembly.Location
                    ));

        // Change class and namespace names.
        result =
            result.Replace(
                @"System.Data.SqlClient.Sql",
                @"VistaDB.Provider.VistaDB");

        // Comment out non-available VistaDB properties.
        result = Regex.Replace(
            result,
            @"(^.*?FireInfoMessageEventOnUserErrors.*?$)",
            @"//$1",
            RegexOptions.Multiline);

        return result;
    }
}

And the method to replace the connection string is defined as follows:

private static void adjustConnectionString(DbCommand command)
{
    if (command != null)
    {
        var conn = command.Connection;

        if (conn != null)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.ConnectionString = getConnectionString();
        }
    }
}

Other methods are left out for simplicity. If you want to see them, simply drop me a note, e.g. by e-mail.

Epilog

In this short article I’ve shown you a way to modify an XtraReports report during runtime of your application to work with both a Microsoft SQL Server and a VistaDB database without the need to design separate reports.

As of writing this article the reports I tested work quite well, probably/possible I will have to do further adjustments when getting to more complex reports. I will update the article then.

I am looking forward for lot of feedback! 🙂