Changing the database and server in a C# report that uses a SQL Server 2008 Stored Procedure

[thesierras]

I'm using C# in Visual Studio 2010 to create an interface that displays Crystal Reports already generated in 8.5 and 9.0. The reports are all based upon result sets returned from stored procedures in SQL Server 2008. Most of the stored procedures accept parameters, but I chose an example without parameters to try to simplifiy this issue. We have multiple client databases across multiple servers, but the structure of the data and the stored procedure names are consistant across the client databases. Through our legacy front end (written in Visual FoxPro) we simply change the server name, database name, user name and password and the reports run correctly across the appropriate client's data. I'm having a problem getting C# to work in that same way.

This particular example is to run a sepcific report for a specific client database. You'll see in the code that I have two different reports that I have tested. When I use the RPT that runs across a table, it works correctly, but when I run the RPT that runs across a SP, I get nothing. Both have been tested directly through CR and work fine. Any Suggestions?

:
:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;

:
:

private void button1_Click(object sender, EventArgs e)
{

    ReportDocument cryRpt = new ReportDocument();
    TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
    TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
    ConnectionInfo crConnectionInfo = new ConnectionInfo();
    Tables CrTables ;

//// Table - Local_Clients
    cryRpt.Load("C:\\CRTestWithTable.rpt");
//// Stored Procedure - Database Integrity
//  cryRpt.Load("C:\\CRTestWithSP.rpt");

    crConnectionInfo.ServerName = "MYSERVERNAME";
    crConnectionInfo.DatabaseName = "MYDBNAME";
    crConnectionInfo.UserID = "MYUSERNAME";
    crConnectionInfo.Password = "MYPASSWORD";

    CrTables = cryRpt.Database.Tables;
    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
    {
        crtableLogoninfo = CrTable.LogOnInfo;
        crtableLogoninfo.ConnectionInfo = crConnectionInfo;
        CrTable.ApplyLogOnInfo(crtableLogoninfo);
    }

    crystalReportViewer1.ReportSource = cryRpt;
    crystalReportViewer1.Refresh();
}

:
:

Thanks in advance.

 

[thesierras]

It took some effort, but I figured it out on my own. I'm putting the answer here for others who may have the same question. I'm a newbie to C# coding, so this may not be the best solution, but it works. I'm using a console application and pushing the report out to a PDF.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main()
        {
            ReportDocument cryRpt = new ReportDocument();
            TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
            ConnectionInfo crConnectionInfo = new ConnectionInfo();

            cryRpt.Load("C:\\MyReportFolder\\MyReportName.rpt");
            crConnectionInfo.ServerName = "MyServerName";
            crConnectionInfo.DatabaseName = "MyDatabaseName";
            crConnectionInfo.UserID = "MyUserID";
            crConnectionInfo.Password = "MyPassword";
/*
 * Our Crystal RPT files were built in Crystal Reports 8.5.  We used stored procedures as the data source.
 * They defaulted to MyDatabase.dbo.Proc(MyStoredProcedure), but we needed the reports to run on a variety of 
 * databases all having the same structure, so we stripped off the "MyDatabase.".  The end result was that
 * our report location contained something that looked like "dbo.Proc(MyStoredProcedure)".  The syntax that 
 * worked for this purpose was "MyStoredProcedure;1", so the .Replace function is used to take the original
 * value of the data location and convert that to the new format when the change of database and reconnection
 * is done in this routine.
*/

            foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in cryRpt.Database.Tables)
            {
                crtableLogoninfo = CrTable.LogOnInfo;
                crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                CrTable.ApplyLogOnInfo(crtableLogoninfo);
                string crStoredProcedureName = CrTable.Location.ToUpper();
                crStoredProcedureName = crStoredProcedureName.Replace("DBO.", "");
                crStoredProcedureName = crStoredProcedureName.Replace("PROC(", "");
                crStoredProcedureName = crStoredProcedureName.Replace(")", ";1");
                CrTable.Location = crStoredProcedureName;
            }

            ExportOptions CrExportOptions;
            DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
            PdfRtfWordFormatOptions CrFormatTypeOptionsPDF = new PdfRtfWordFormatOptions();
            CrDiskFileDestinationOptions.DiskFileName = "C:\\MyOutputFolder\\MyReportName.PDF";
            CrExportOptions = cryRpt.ExportOptions;
            {
                CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
                CrExportOptions.FormatOptions = CrFormatTypeOptionsPDF;
            }
            try
/*
 * The stored procedures that am calling have only one parameter named MyWildcard and I'm passing a value of
 * "ABC%" to that parameter.  More parameters can be added and passed, but this is just a sample.
*/
            {
                cryRpt.SetParameterValue("@MyWildcard", "ABC%");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            try
            {
                cryRpt.Export();
            }
            catch (Exception ex)
            {
                string MainString = "";
                MainString = ex.ToString();
                Console.WriteLine(ex.ToString());
            }
        }
    }
    // Create the derived exception class.
    class SecondLevelException : Exception
    {
        const int SecondLevelHResult = unchecked((int)0x81234567);

        // Set HResult for this exception, and include it in 
        // the exception message.
        public SecondLevelException(string message, Exception inner) :
            base(string.Format("(HRESULT:0x{1:X8}) {0}",
              message, SecondLevelHResult), inner)
        {
            HResult = SecondLevelHResult;
        }
    }

}