Search This Blog

Wednesday, May 5, 2010

How to pass Database logon info to a Crystal Report at runtime in C#

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

private CrystalReport1 crReportDocument = new
CrystalReport1 ();
private Database crDatabase;
private Tables crTables;
private Table crTable;
private TableLogOnInfo crTableLogOnInfo;
private ConnectionInfo crConnectionInfo = new
ConnectionInfo ();

//Setup the connection information structure
//to log on to the data source for the report.
// If using ODBC, this should be the DSN. If using
// OLEDB, etc, this should be the physical server name


crConnectionInfo.ServerName = "DSN or
Server Name";

// If you are connecting to Oracle there is no
// DatabaseName. Use an empty
// string i.e. crConnectionInfo.DatabaseName = "";

crConnectionInfo.DatabaseName = "DatabaseName";
crConnectionInfo.UserID = "Your UserID";
crConnectionInfo.Password = "Your Password";

// This code works for both user tables and stored
procedures

//Get the table information from the report
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;

//Loop through all tables in the report and apply the
//connection information for each table.
for (int i = 0; i < crTables.Count; i++)
{
crTable = crTables ;
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo =
crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);

//If your DatabaseName is changing at runtime, specify
//the table location. For example, when you are
reporting
//off of a Northwind database on SQL server
//you should have the following line of code:

crTable.Location = crConnectionInfo.DatabaseName + ".dbo." +
crTable.Location.Substring(crTable.Location.LastIndexOf
(".") + 1)
}

//Set the viewer to the report object to
//be previewed.

crystalReportViewer1.ReportSource =
crReportDocument;