Wednesday 8 August 2007

Reading the CrystalReportViewer report connection string from the Web.config in Crystal Reports

Again, another common requirement when working with Crystal Reports. So just where is the official documentation? ...

When using the CrystalReportViewer control it is possible to override the report's default database connection information with your own. To do this with SQL Server is well catered for, but what about us poor developers lumbered with ODBC? Perhaps we want to store and deal with connection strings from the web.config too!

Thankfully it is possible to get Crystal Reports to use a custom connection string for ODBC as this code snippet shows:

// Get document
ReportDocument doc = this.CrystalReportSource1.ReportDocument;

// Set connection string from config in existing LogonProperties
doc.DataSourceConnections[0].LogonProperties.Set("Connection String",
ConfigurationManager.AppSettings["connectionString"]);

// Add existing properties to a new collection
NameValuePairs2 logonProps = new NameValuePairs2();
logonProps.AddRange(doc.DataSourceConnections[0].LogonProperties);

// Set our new collection to be the defaults
// This causes Crystal Reports to actually use our changed properties
doc.DataSourceConnections[0].SetLogonProperties(logonProps);

How Does It Work?

The key section is the call to SetLogonProperties which causes Crystal Reports to use the new connection properties. Despite being modifiable the existing LogonProperties of a DataSourceConnection are actually read only.

12 comments :

Unknown said...

Hi, I have been searching for some documentation on how to do this for some time now. Quite frustrating.
Anyhow, I am having trouble trying to get your code snippet to work for me. The problem is the 'LogonProperties' & 'SetLogonProperties' methods. I am getting compiler error CS0117:'CrystalDecisions.Shared.IConnectionInfo' does not contain a definition for 'LogonProperties' etc.
Do you have any further advice on what I need to do to define these methods?

Alex Lea said...

I too encountered this error using an older version of Crystal Reports where the documented methods were not available. I downloaded the latest version of the runtime through the rather confusing BusinessObjects site and that seemed to fix the problems.

I'm using Crystal Reports XI Release 2.

It does work, honest :)

Unknown said...

Apart from getting the error 'CrystalDecisions.Shared.IConnectionInfo' does not contain a definition for 'LogonProperties'

I failed to undersatnd what is this type declaration NameValuePairs2() ?
Couldn't find any reference to it

Anonymous said...

hemant:

NameValuePairs2 is a type of the CrystalDecisions.Shared namespace

You can either add a "using" statement to include CrystalDecisions.Shared namespace or declare CrystalDecisions.Shared.NameValuePairs2.

Krishna said...

Mate,

I don't think your solution actually works.

I've wasted 3 hrs in my life trying / hoping it would ... but nah.

It compiles and all (since I'm using CR XI rel 2, VS 2005, ASP.NET .NET Fx 2.0 with a SQL Server 2005 database.

Sorry. But in the end I just had to go and do some shitty code like this:

----------------

string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionStringName"].ConnectionString;

//Trace.Warn("cnn = " + connectionString);
NameValueCollection nvcol = Util.GetConnectionParts(connectionString);

//shitty code warning: due to shitty library from Crystal reports:
string user = nvcol.Get("User ID");
string pwd = nvcol.Get("Password");
string server = nvcol.Get("Data Source");
string db = nvcol.Get("Initial Catalog");
doc.SetDatabaseLogon(user, pwd, server, db);
//shitty code end

----------

Alex Lea said...

Interesting. Do you have more than one connection in your report? The posted code does assume there is a single data connection hence the hard coded [0] index - this would need changing if you had several.

Good to see another solution to this irritating and unnecessary problem.

Anonymous said...

Thanks it sure help me.
In a shity away as you said :). Unfortunly i didn't found another way either.

See you

Unknown said...

I developed something like Krishna said. It's in Vb.net:

Dim myConnectionInfo As New ConnectionInfo
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("dbConnection").ConnectionString()

myConnectionInfo.ServerName = getPartConnectionString("Data Source", myConnectionString)
myConnectionInfo.DatabaseName = getPartConnectionString("Initial Catalog", myConnectionString)
myConnectionInfo.UserID = getPartConnectionString("User ID", myConnectionString)
myConnectionInfo.Password = getPartConnectionString("PWD", myConnectionString)

Private Function getPartConnectionString(ByVal part As String, ByVal _connectionString As String) As String

Dim inicio As Integer
Dim partTemp As String
Dim partResult As String

inicio = InStr(_connectionString, part) + Len(part) + 1

For contPartConn As Integer = inicio To Len(_connectionString)
partTemp = Mid(_connectionString, contPartConn, 1)
If partTemp = ";" Then Exit For
partResult += partTemp
Next

Return partResult

End Function

Tristan Collier said...

The following seems to work fine for me:

protected void Page_Init(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnectionStringBuilder SConn = new System.Data.SqlClient.SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

ReportDocument doc = new ReportDocument();
doc.Load(Server.MapPath("Crystalreport.rpt"));

doc.SetDatabaseLogon(SConn.UserID, SConn.Password, SConn.DataSource, SConn.InitialCatalog);

CrystalReportViewer1.ReportSource = doc;

}

BNayak said...

Tristan,

The code that you have didnt work for me when the report is already assigned (during design time) to the crystalviewer.

What worked is this:

string _connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
SqlConnectionStringBuilder SConn = new SqlConnectionStringBuilder(_connectionString);

this.CrystalReportSource1.ReportDocument.DataSourceConnections[0].SetConnection(SConn.DataSource, SConn.InitialCatalog, SConn.UserID, SConn.Password);

Anonymous said...

I don't get the password in LogonProperties object and hence the login fails. I have't used any kind of encryption. I do get the User name, etc but not the password!

Anonymous said...

I know this is an old post... but I was having all sorts of difficulties trying to determine how to change the hard coded database location for a report using an Access database.

The following code sets the location of an Access database at run time:

// Load up the report
_report = new ReportDocument();
_report.Load(_report_file);

// Set the actual database location
_report.DataSourceConnections[0].SetConnection(Properties.Settings.Default.DatabaseLocation + "\\" + Properties.Settings.Default.DatabaseFilename, "", "Admin", "");

// Set report to the viewer
reportViewer.ReportSource = _report;

This code assumes that you are using only one database in the report. You may have to use more than one of the DataSourceConnections[] for your report.

Good luck!