C#.NET Crystal report application dynamic database connection change

Is this the same question
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_22689913.html

mlmcc

Well, I've tried several different ways. At first I figured the only thing I had to do was change the ReportDocument.SetDatabaseLogon() method to match whatever database I needed. This didn't work. So i started applying the LogOnInfo to every table the report file uses. Code shown:

using System;
using System.Data;
using System.Collections.Specialized;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;

public partial class RunReport : System.Web.UI.Page
{
    private string _reportPath = ConfigurationManager.AppSettings["PathToReportFiles"].ToString();
    protected string strSelectionFormula = "";
    protected string dbName = "";
    protected string dbUser = "";
    protected string dbPass = "";
    protected string serverName = "";

    /// <summary>
    /// Loads a crystal report file into the current aspx page. Accepts query string arguments: ReportID, DB, dbUsername
    /// and dbPassword. Only ReportID is required.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>

    protected void Page_Load(object sender, EventArgs e)
    {
         //call method to pull database information from web.config for each specific environment
         GetDbInfo();

         try
         {
             //load the report file specified in the query string
             doc.Load(Server.MapPath(_reportPath.Replace("\\\\", "\\") + this.Request.QueryString["ReportID"].ToString()), OpenReportMethod.OpenReportByTempCopy);
         }
         catch
         {
             throw new System.Exception("Could not load the requested report file. Please specify a report file in the query string.");
         }

         //call the method to apply login information to each table in the report
         FixDatabase(doc);

         //set database login information for the entire report object
         doc.SetDatabaseLogon(dbUser, dbPass, serverName, dbName);

         //load selection formula from query string, or default it to nothing
         try
         {
             strSelectionFormula = this.Request.QueryString["SelectionFormula"].ToString();
         }
         catch
         {
             strSelectionFormula = "";
         }
         finally
         {
             doc.DataDefinition.RecordSelectionFormula = strSelectionFormula;
         }

         Viewer.ReportSource = doc;
    }

        /// <summary>
        /// Dissects the query string for user supplied database information.
        /// </summary>
        protected void GetDbInfo()
        {
            // check for a user supplied database name through query string
            try
            {
                //create a new name value collection to collect database info from web.config
                NameValueCollection dataBaseInfo = new NameValueCollection();

                //get database name from query string
                dataBaseInfo = (NameValueCollection)ConfigurationManager.GetSection("DatabaseConfigurations/" + this.Request.QueryString["Database"].ToString());

                //gather db name, db query user, db query pass
                dbUser = dataBaseInfo.Get("User").ToString();
                dbPass = dataBaseInfo.Get("Pass").ToString();
                dbName = dataBaseInfo.Get("Name").ToString();
                serverName = dataBaseInfo.Get("ServerName").ToString();
            }
            catch
            {
                throw new System.Exception("Error loading database information. Please specify which database to use in the query string.");
            }
     }

     private void FixDatabase (ReportDocument report)
     {
           foreach (CrystalDecisions.CrystalReports.Engine.Table table in report.Database.Tables)
           {
                 TableLogOnInfo logOnInfo = table.LogOnInfo;
                 if (logOnInfo != null)
                 {
                     table.LogOnInfo.TableName = table.Name;
                     table.LogOnInfo.ConnectionInfo.UserID = dbUser;
                     table.LogOnInfo.ConnectionInfo.Password = dbPass;
                     table.LogOnInfo.ConnectionInfo.DatabaseName = dbName;
                     table.LogOnInfo.ConnectionInfo.ServerName = serverName;
                     table.ApplyLogOnInfo(table.LogOnInfo);
                 }
           }

          //call this method recursively for each subreport
           foreach (ReportObject reportObject in report.ReportDefinition.ReportObjects)
           {
               if (reportObject.Kind == ReportObjectKind.SubreportObject)
               {
                   FixDatabase(report.OpenSubreport(((SubreportObject)reportObject).SubreportName), un, pass, dbName, serverName);
               }
           }
    }
}

Yeah, just removed it. I couldn't get any answers earlier so I was trying everything.

It was right there staring at me in the face the entire time. Thank you.

Glad i could help

mlmcc

I tried the code above and it actually keeps displayin the original data that the report was bound to. I added ReportViewer.RefreshReport at the end and now the report displays blank.

Here is my code. for now everfything is hard coded until I get the proof of concept working. Also I moved the ApplyTableInfo to the beginning of the loop because I noticed that if I used it at the end my connection data was getting reset.

Also another thing I noticed happening is that when I look at the table's LogOnInfo.ConnectionInfo the
password is an empty string while in the report I definately set it. So I am not sure why does it coem inlike that. Maybe that is the reason that even after I specify a password for the new connection it does not work...

 protected void Page_Load(object sender, EventArgs e)
    {
 
       
       // this.CrystalReportSource1.ReportDocument.Load(@"C:\PROJ 2009\SmartLab 4.0\ePMC Web\Development\Code\ePMC.Web\ReportNoUserNameUDA.rpt");
        ReportDocument doc = new ReportDocument();
        doc.Load(@"C:\ReportName.rpt");
 
 
        //2) Once I have the data I need to apply it to the connection of the report
        ConnectionInfo crConnection = new ConnectionInfo();
        crConnection.UserID = "username";
        crConnection.ServerName = "serverName";
        crConnection.DatabaseName = "";
        crConnection.Password = "password";
 
 
        AssignConnectionInfo(doc, crConnection);
 
        doc.SetDatabaseLogon(crConnection.UserID, crConnection.Password, crConnection.ServerName, crConnection.DatabaseName);
 
 
        CrystalReportViewer1.ReportSource = doc;
        CrystalReportViewer1.RefreshReport();
 
 
    }
 
 
 private void AssignConnectionInfo(ReportDocument document, ConnectionInfo crConnection)
    {
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in document.Database.Tables)
        {
            TableLogOnInfo logOnInfo = table.LogOnInfo;
            if (logOnInfo != null)
            {
                table.ApplyLogOnInfo(table.LogOnInfo);
                table.LogOnInfo.TableName = table.Name;
                table.LogOnInfo.ConnectionInfo.UserID = crConnection.UserID;
                table.LogOnInfo.ConnectionInfo.Password = crConnection.Password;
                table.LogOnInfo.ConnectionInfo.DatabaseName = crConnection.DatabaseName;
                table.LogOnInfo.ConnectionInfo.ServerName = crConnection.ServerName;
                
            }
        }
 
 
 
    }

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49: