c# - Report asking for database login on setting DataTable as DataSource

Question

Report designer creates crystal report in designer with direct connection to database with ODBC (DSN) connection. Same reports are executed via a Winform(C#) application via DSN and provides Database Server, database, user ID and password.

I need to make such changes to the Crystal Report object. ReportDocument should not directly connect to Database via DSN. Instead, we will bring data via service by calling respective stored procedure and parameters as System.Data.DataTable. This DataTable object should be used to populate/generate reports.

I do get the stored procedure and parameter information from ReportDocument.DataBase.Tables[I].Location and ReportDocument.DataDefinition object respectively. After setting DataSource with ReportDocument.DataBase.Tables[I].SetDataSource(DataTable), it still ask for database/server and user credential to connect to server.

Can we achieve scenario and populate report with in-memory table instead of direct connection to database over ODBC?

Answer 1

Two things you need to focus on:

1. You are connecting to same server and database name which were used to design the report.
2. You are connecting to a different database or server.

Scenario 1: Same server and database name

In this case you need to provide the credentials using SetDatabaseLogon method as follows

'crDoc1 is your ReportDocument
'dtDataTable is your DataTable

'set database logon info
crDoc1.SetDatabaseLogon("db_user_name", "db_password", "db_server_name_or_ip", "database_name");

'set DataTable as DataSource
crDoc1.SetDataSource(dtDataTable)

Scenario 2: Different server OR database name

In this case you need to provide the credentials using ApplyLogOnInfo method

ConnectionInfo crConInfo = new ConnectionInfo();
TableLogOnInfo crTblLogonInfo = new TableLogOnInfo();

crConInfo.ServerName = "db_server_name_or_ip";
crConInfo.DatabaseName = "database_name";
crConInfo.UserID = "db_user_name";
crConInfo.Password = "db_password";  

'crDoc1 is your ReportDocument
'dtDataTable is your DataTable

'Set DataSource to your DataTable
crDoc1.SetDataSource(dtDataTable)

'after setting the DataSource apply Logon credentials to each table in ReportDocument
Tables CrTables = crDoc1.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables) {
    crTblLogonInfo = CrTable.LogOnInfo;
    crTblLogonInfo.ConnectionInfo = crConInfo;
    CrTable.ApplyLogOnInfo(crTblLogonInfo);
}
crDoc1.Refresh();
CrystalReportViewer1.ReportSource = crDoc1;

Notes: If you have any sub reports, you need to apply SetDatabaseLogon and/or ApplyLogOnInfo to all the sub reports and their tables respectively.

UPDATE:
Applying ApplyLogOnInfo to subreports

foreach (ReportDocument srSubReport in crDoc1.Subreports) {
    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in srSubReport.Database.Tables) {
        crTblLogonInfo = CrTable.LogOnInfo;
        crTblLogonInfo.ConnectionInfo = crConInfo;
        CrTable.ApplyLogOnInfo(crTblLogonInfo);
    }
}