How do I log into SQL database to connect to a crystal report using ASP.NET MVC 5 controller (C#)

See more:

C#

CrystalReports

, +

Hello,

I have a Visual Studio 2017 solution with multiple projects. One of the projects contains all my reports. The web application was created using C#, ASP.NET MVC 5, MS SQL 2014, and I use Entity Framework Code-first for my data models.

The particular report I have created is rather complicated. In summary it collects financial data from several data tables and then display a "statement-like" report for a particular customer. In order to allow the user to save reports, I created the following Report Data Models to store the retrieved data from the rest of the application:

I am using the Crystal Reports for Visual Studio add-in.

1) Monthly Customer Payments - connects the "MonthlyCustomerPayments.rpt" report to the "CustomerMonthlyPaymentsReport" data model. This is the "main" or "master" report.


The following sub-reports/models are linked via the "ReportId" primary key to the main report/model.

2) Customer Summary - "CustomerSummary.rpt" - "CustomerSummary" data model.

3) Customer Service Fee Summary - "CustomerServiceFeeSummary.rpt" "CustomerServiceFeeSummary" data model.

4) Customer Account Profile Summary - "CustomerAccountProfileSummary.rpt" - "CustomerAccountProfileSummary" data model.

5) Customer Account Summary - "CustomerAccountSummary.rpt" - "CustomerAccountSummary" data model.

6) Customer Service Summary - "CustomerServiceSummary.rpt" - "CustomerServiceSummary" data model.

7) Customer Service Transaction Details - "Customer Service Transaction Details" - "CustomerServiceTransactionDetails" data model.


I hope the above makes sense in terms of the setup of the data models and their corresponding reports.

I have a Controller that collects the data for each data table, save it, and then display the report tables in a Razor view. On this view I have two buttons, one for "Export to PDF" and the other for "Export to Excel".

So the idea is that the Customer generates a report for a specified date range, save it and then have the option to export the report to PDF or Excel foemat.

I will use the PDF Export as an example. I have the following code for the PDF export button in the CustomerMonthlyPaymentReportsController:

public ActionResult ExportReportPDF(int id)
{
	string ServerName = System.Configuration.ConfigurationManager.AppSettings["DbServer"].ToString();       //Database server name to which report connected
        string DataBaseName = System.Configuration.ConfigurationManager.AppSettings["DbName"].ToString();       //Database name to which report connected
        string UserID = System.Configuration.ConfigurationManager.AppSettings["DbUser"].ToString();             //Database user name to which report connected
        string Password = System.Configuration.ConfigurationManager.AppSettings["DbPwd"].ToString();            //Database user password to which report connected

        ReportDocument crReportDocument = new ReportDocument();

        crReportDocument.Load(Path.Combine(Server.MapPath("~/Reports/Admin"), "MonthlyCustomerPayments.rpt"));
        crReportDocument.SetDataSource(db.CustomerMonthlyPaymentsReport.Where(x => x.ReportId == id).ToList());

        //crReportDocument.SetDatabaseLogon(UserID, Password);
        Logon(crReportDocument, ServerName, DataBaseName, UserID, Password);

        //crReportDocument = prepareReport(crReportDocument);

        Response.Buffer = false;
        Response.ClearContent();
        Response.ClearHeaders();

        string reportDescription = db.CustomerMonthlyPaymentsReport.Where(x => x.ReportId == id).Select(x => x.ReportDescription).First();

        try
        {
            Stream stream = crReportDocument.ExportToStream(ExportFormatType.PortableDocFormat);
            stream.Seek(0, SeekOrigin.Begin);
            string savedFileName = string.Format(reportDescription + ".pdf");
            return File(stream, "application/pdf", savedFileName);
        }
        catch
        {
            throw;
        }
}

The following code is used to log onto each report using SQL server credentials stored in the Web.Config file:

//Check whether crytal report can login to the server
private bool Logon(ReportDocument cr, string server, string database, string user_id, string password)
{
    // Declare and instantiate a new connection info object.
    ConnectionInfo ci;
    ci = new ConnectionInfo();

    ci.ServerName = server;
    ci.DatabaseName = database;
    ci.UserID = user_id;
    ci.Password = password;             //password;
    // ci.IntegratedSecurity = false;

    // If the ApplyLogon function fails then return a false for this function.
    // We are applying logon information to the main report at this stage.
    if (!ApplyLogon(cr, ci))
    {
        return false;
    }

    // Declare a subreport object.
    SubreportObject subobj;

    // Loop through all the report objects and locate subreports.
    // If a subreport is found then apply logon information to
    // the subreport.
    foreach (ReportObject obj in cr.ReportDefinition.ReportObjects)
    {
        if (obj.Kind == ReportObjectKind.SubreportObject)
        {
            subobj = (SubreportObject)obj;
            if (!ApplyLogon(cr.OpenSubreport(subobj.SubreportName), ci))
            {
                return false;
            }
        }
    }

    // Return True if the code runs to this stage.
    return true;

}

The following code is called by the Logon method:

private bool ApplyLogon(ReportDocument cr, ConnectionInfo ci)
{
    // This function is called by the "Logon" function
    // It loops through the report tables and applies
    // the connection information to each table.

    // Declare the TableLogOnInfo object and a table object for use later.
    TableLogOnInfo li;
    // For each table apply connection info.

    foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in cr.Database.Tables)
    {

        li = tbl.LogOnInfo;
        li.ConnectionInfo.ServerName = ci.ServerName;
        li.ConnectionInfo.DatabaseName = ci.DatabaseName;
        li.ConnectionInfo.UserID = ci.UserID;
        li.ConnectionInfo.Password = ci.Password;
        tbl.ApplyLogOnInfo(li);
        tbl.Location = ci.DatabaseName + ".dbo." + tbl.Name;

        // Verify that the logon was successful.
        // If TestConnectivity returns false, correct table locations.
        if (!tbl.TestConnectivity())
        {
            return false;

        }
    }
    return true;
}

I hope the above code makes sense. What I am trying to do is force the application to log onto the database using the specific credentials I specify in the Config.Web file. This is to allow me to test the Reports on my development PC, and to be able to specify the logon credentials for the Production (Live) server.

The issue I am having is that this all works fine on my Dev PC, but gives me the following error on the production server:

"Error in File MonthlyCustomerPayments 5412_8572_{99689A9A-3AB1-47CF-B744-2DA06097BBDD}.rpt:
Unable to connect: incorrect log on parameters."

From what I could figure out, it seems that it picks up my Dev PC's SQL server name embedded in the report, and my code above to log onto the production server's SQL server instance is ignored.

I know this is a log-winded explanation of my particular issue, but I have posted on many forums about this, and people seem to misunderstand the details and complexity of my report setup.

I hope someone that has used Entity Framework and Crystal Reports in an MVC application with a report and multiple sub-reports can give me advice regarding this issue.

Thank you for reading this, and I look forward to any suggestions/advice.

What I have tried:

I have tried countless suggestions for using ASPX and CrstalReport Viewer, but I'm afraid they do not work well with this complex report that has multiple sub-reports.

The code above I have implemented on the suggestion of a person in another forum, to log each report/sub-report into the database.