Setting ODBC connection dynamically in C# for Crystal Reports

I'm using C# 4.0. I have a CR report that uses an ODBC connection called Reports. I want to change that dynamically in C# so that I can point to the database I want at run time.

Here's the problem. The register entry of interest is
   HKEY_LOCAL_MACHINE\SOFTWARE.ODBC\ODBC\ODBC.INI\Reports

I changed the DATABASE field value from OldDB to NewDB. When I go to the CR designer in VS, choose Database Expert and then under My Connections browse the contents of the connection Reports, it pulls up OldDB.

Apparently My Connections binds to an ODBC connection at design time. I can set up a new connection to Reports and it shows up in My Connections as Reports1.

Any ideas?
LVL 1
BlearyEyeAsked:
Who is Participating?
 
msd1305Connect With a Mentor Commented:
Private Sub LoadReportDevelopment(ByVal reportPath As String)
            Try
            dim oReport as New CrystalDecisions.CrystalReports.Engine.ReportDocument                
                oReport.Load("#Report Path#")
                Dim OraServer As String = "#ServerName#"
                Dim OraUserName As String = "#UserName#"
                Dim OraUserPwd As String = "Password"
               
                oReport.SetDatabaseLogon(OraUserName, OraUserPwd, OraServer, "", True)

                Dim myConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
                Dim myLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo
                myConnectionInfo.ServerName = OraServer
                myConnectionInfo.DatabaseName = ""
                myConnectionInfo.UserID = OraUserName
                myConnectionInfo.Password = OraUserPwd
                For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In oReport.Database.Tables
                    myLogonInfo = myTable.LogOnInfo
                    myLogonInfo.ConnectionInfo = myConnectionInfo
                    myTable.ApplyLogOnInfo(myLogonInfo)

                    'Below code is required for stored procedures to find the package name.You can exclude it if you are not using stored procedures in your report.
                    '--------------------------------
                    Dim sqlQryGetPackageName As String = "select t.package_name from user_arguments t " _
                        & " where t.object_name='" & myTable.Location.ToUpper & "' and rownum=1 "
                    Dim PackageName As String = ""

                   'DBHelper is our customized database helper class. You can query the above sql expression in whichever way you want.
                    PackageName = DBHelper.GetValue_FirstCol_String(sqlQryGetPackageName)

                    If PackageName.Trim <> "" Then
                        myTable.Location = OraUserName.ToUpper & "." & PackageName.ToUpper & "." & myTable.Location
                    End If
                 '--------------------------------
                Next

                For Each subrpt As CrystalDecisions.CrystalReports.Engine.ReportDocument In oReport.Subreports
                    subrpt.SetDatabaseLogon(OraUserName, OraUserPwd, OraServer, "", True)
                    For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In subrpt.Database.Tables
                        myLogonInfo = myTable.LogOnInfo
                        myLogonInfo.ConnectionInfo = myConnectionInfo
                        myTable.ApplyLogOnInfo(myLogonInfo)

      'Below code is required for stored procedures to find the package name. You can exclude it if you are not using stored procedures in your report
                       '--------------------------------
                        Dim sqlQryGetPackageName As String = "select t.package_name from user_arguments t " _
                        & " where t.object_name='" & myTable.Location.ToUpper & "' and rownum=1 "
                        Dim PackageName As String = ""

                       'DBHelper is our customized database helper class. You can query the above sql expression in whichever way you want.
                        PackageName = DBHelper.GetValue_FirstCol_String(sqlQryGetPackageName)

                        If PackageName.Trim <> "" Then
                            myTable.Location = OraUserName.ToUpper & "." & PackageName.ToUpper & "." & myTable.Location
                        End If
       '--------------------------------
                    Next
                Next


            Catch ex As Exception
                Throw New Exception(ex.ToString())
            End Try
        End Sub
0
 
BlearyEyeAuthor Commented:
btw, I know I an pull the data of interest into a DataSet or use an SQLDataReader and set the report source dynamically. But there are problems with that approach and I'd really just like to be able to change the ODBC info in the registry and have it adapt.
0
 
BlearyEyeAuthor Commented:
And, I did find this: http://social.msdn.microsoft.com/Forums/en/vscrystalreports/thread/258d7724-8907-4494-b70e-f59dd7ed5844

So it looks like ODE DB might work instead of ODBC. If so, the question is how to set the OLE DB connection source dynamically. I don't see anything in the registry that I can change.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
msd1305Commented:
Which database are you connecting your report to? Better avoid using ODBC. Why to make it machine dependent?

I would suggest you to setup Oracle or SQL connection from the report and then when you call it from C#, use SetDataSource method of Crystal report document object to set the server name, database name and credentials. This will give you the flexibility you are looking for.
0
 
BlearyEyeAuthor Commented:
Can you give me a pointer? I haven't seen SetDataSource used that way.
0
 
msd1305Commented:
I am really sorry. I mentioned wrong method name. I was actually talking about SetDatabaseLogon.

You can use it like this.

oReport is my report object (CrystalDecisions.CrystalReports.Engine.ReportDocument).

Oracle
--------
oReport.SetDatabaseLogon(OraUserName, OraUserPwd, OraServer, "", True)

SQL
----
oReport.SetDatabaseLogon(SQLUserName, SQLUserPwd, SQLServer, SQLDB, True)

0
 
mlmccCommented:
Another method would be to pass the dataset to the report as in this example
http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc
0
 
BlearyEyeAuthor Commented:
For a variety of reasons, I really want to avoid using datasets; I need to point to the database directly. As soon as I have a chance I'll look at SetDatabaseLogon.
0
 
msd1305Commented:
Yes. Even I personally prefer avoiding datasets mainly to keep the report independent of the application which adds a great flexibility for testing !
0
 
BlearyEyeAuthor Commented:
Looking at the doc for SetDatabaseLogon, http://msdn.microsoft.com/en-us/library/ms227750%28VS.80%29.aspx says:

Or call the SetDatabaseLogon() method with your server name, database name, user name and password.

    Note   This method does not change the server or database. You are restricted to only the default server and database that is specified within the report.

So it looks like I can't actually use this method. Has it worked for you?
0
 
msd1305Commented:
It used to be like that before (VS 2003, 2005). where we can change only the username and password and not servername.

But as u have mentioned in your problem that you are using C# 4.0 . So it obviously means that you are using VS 2010 and Crystal redist for VS 2010. Right?

We have the same environment and this method works perfectly with it. So give it a try.
0
 
BlearyEyeAuthor Commented:
Sorry I've been unresponsive ... distracted by deadlines ... will give this a try as soon as I can.
0
 
BlearyEyeAuthor Commented:
Am getting back to this. Can you give me a pointer to the MSDN doc for the VS 2010 version of SetDatabaseLogon? I can't seem to find it. I want to find out what overloads are available.
0
 
mlmccCommented:
I susoect there isn't one.  the CR versioon for VS2010 was developed separately and not included with VS2010

mlmcc
0
 
msd1305Commented:
Ok. Here is the link .
http://devlibrary.businessobjects.com/businessobjectsxi/en/en/crystalreports_dotnet_sdk/crystalreports_net_doc/doc/crsdk_net_doc/html/crtsktutorialsrdloggingsecuresqladdendumsetdatabaselogon.htm

We have almost the same environment as you have mentioned. But there is a small difference. Crystal DLLs for VS2010 are still beta so in my web application I am using CR2008 dlls.

declare an object, say objReport, of class ReportDocument and try/ when you put a dot(.) after objReport, you might not get this method in the list but write it as i mentioned in the above post. It should compile without any problem.

now when you deploy on production, you can install CRVS2010 on the server. This works.
0
 
mlmccCommented:
Actually the production VS2010 dlls  have just been released

http://www.businessobjects.com/jump/xi/crvs2010/default1.asp

mlmcc
0
 
BlearyEyeAuthor Commented:
I'll update to the production version & try the method.
0
 
BlearyEyeAuthor Commented:
With no little difficulty, I was able to get the beta version uninstalled (see http://forums.sdn.sap.com/thread.jspa?messageID=9795681� for details) and then install the production version.

When I fired up VS 2010, it offered to convert my reports. I accepted.

In my code I added
DeviceDataViewer.SetDatabaseLogon("", "");
where DeviceDataViewer is a CrystalReportViewer object. I'm getting the error message
CrystalDecisions.Windows.Forms.CrystalReportViewer' does not contain a definition for 'SetDatabaseLogon' ...
Am I doing something wrong?

Also ... intellisense did not recognize the method.
0
 
mlmccCommented:
I don't know.  I haven't used the VS versions

mlmcc
0
 
BlearyEyeAuthor Commented:
Any thoughts? I can't get the SetDatabaseLogon method to work ...
0
 
msd1305Commented:
Private oReport As CrystalDecisions.CrystalReports.Engine.ReportDocument
oReport = New CrystalDecisions.CrystalReports.Engine.ReportDocument
                oReport.Load("#ReportPath#")
                Dim OraServer As String = ""
                Dim OraUserName As String = ""
                Dim OraUserPwd As String = ""
               
oReport.SetDatabaseLogon(OraUserName, OraUserPwd, OraServer, "", True)

Dim subRptCount As Integer = oReport.Subreports.Count
                For i As Integer = 0 To subRptCount - 1
                                      oReport.Subreports.Item(i).SetDatabaseLogon(OraUserName, OraUserPwd, OraServer, "", True)
                Next

                    Dim paramFields As New CrystalDecisions.Shared.ParameterFields
                    Dim paramField As New CrystalDecisions.Shared.ParameterField
                    Dim discreteVal As New CrystalDecisions.Shared.ParameterDiscreteValue
                    Dim paramField1 As New CrystalDecisions.Shared.ParameterField
                    Dim discreteVal1 As New CrystalDecisions.Shared.ParameterDiscreteValue

                    paramField.ParameterFieldName = "StartDate"
                    discreteVal.Value = #Replace with your DateValue#
                    paramField.CurrentValues.Add(discreteVal)
                    paramFields.Add(paramField)

                    paramField1.ParameterFieldName = "EndDate"
                    discreteVal1.Value = #Replace with your date value#
                    paramField1.CurrentValues.Add(discreteVal1)
                    paramFields.Add(paramField1)

                    For Each pf As CrystalDecisions.Shared.ParameterField In paramFields
                        oReport.SetParameterValue(pf.ParameterFieldName, pf.CurrentValues)
                    Next

                      CrystalReportViewer1.ReportSource = oReport
                      CrystalReportViewer1.ParameterFieldInfo = paramFields




----------------
This is a working code. Hope this helps. This is in VB but it shoudnt be difficult for you to convert it in C#.
0
 
msd1305Commented:
SetDatabaseLogon is the property of CrystalDecisions.CrystalReports.Engine.ReportDocument .

Its not a property of crystal report viewer. That is why you are not getting its definition.
0
 
BlearyEyeAuthor Commented:
Yes, you're right. I was trying the wrong object. So now, my problem is how to actually use the SetDatabaseLogon method.

msd1305 gave the link to the doc, http://devlibrary.businessobjects.com/businessobjectsxi/en/en/crystalreports_dotnet_sdk/crystalreports_net_doc/doc/crsdk_net_doc/html/crtsktutorialsrdloggingsecuresqladdendumsetdatabaselogon.htm. There are 3 overloads, each of which requires username and password. In my case, I'm using integrated security. My SQL connection string looks like this:
(local)\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True
If it helps, the Javascript version (without escape characters) is
Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=MyDb;Data Source=(local)\SQLEXPRESS;Initial File Name="";Server SPN=""
I'm perplexed about the arguments I should provide to SetDatabaseLogon.

0
 
msd1305Commented:
SetDatabaseLogon("your username","your password","SQL server name","database name", True)
0
 
BlearyEyeAuthor Commented:
that doesn't seem to cover my connection string but i guess i'll give it a try. I've spent endless hours getting sql connections working in the past ...
0
 
BlearyEyeAuthor Commented:
I want to continue on this issue. I was delayed by int'l travel & east coast winter storm, but am back now.
0
 
BlearyEyeAuthor Commented:
I added the code shown below. DeviceDataReport instantiates a class inherited from ReportClass. The SetDatabaseLogon executes without exception but seems to have no effect. The resulting report is unvaried. It doesn't matter what I put into the server or database arguments; what's shown in the code snippet is patently wrong and should have caused an error of some sort. What's missing?

 
try {
                DeviceDataReport.SetDatabaseLogon("", "", "SQLEXPRESSx", "STUVx");
            }
            catch (Exception ex) {
                MessageBox.Show(ex.ToString());
            }

Open in new window

0
 
mlmccCommented:
Does the report have saved data?
That is the default setting for Crystal and the report won't try to use the database until it is refreshed or the saved data is discarded.

There is a report object method DiscardSavedData
You can also change the report to not save the data.

mlmcc
0
 
mlmccCommented:
One way to tell is hit F5 or the Refresh icon on the report when viewing.

mlmcc
0
 
BlearyEyeAuthor Commented:
There's no refresh icon on the report and f5 has no effect. I'll try the DiscardSavedData ...
0
 
BlearyEyeAuthor Commented:
I'm having some difficulty finding out where to use DiscardSavedData in the code. I have two objects: a CrystalReportViewer object and report object that inherits from ReportClass. Neither of those have a DiscardSavedData method.

I found that the code set ShowRefreshButton to false; i set it to true. When I clicked on the refresh button, it asked for all the parameters again. I left them blank and clicked ok. It displayed a blank report (as it should have) but didn't complain that the parameters I had provided in SetDatabaseLogon were false.

So far as I can tell, SetDatabaseLogon is being ignored completely in my code.
0
 
mlmccConnect With a Mentor Commented:
This tutorial is for VS2005 Crystal but it should be similar.  Teh databse logon information starts on p120

http://www.businessobjects.com/global/pdf/dev_zone/VS2005_Walkthroughs.pdf

mlmcc
0
 
mlmccCommented:
The discardsaveddata might be HasSavedData

mlmcc
0
 
BlearyEyeAuthor Commented:
The tutorial is surprisingly hard to read; lots of little pieces and no big picture. Anyway, I followed the section "Logging onto a Secure SQL Server Database Using Integrated Security", using the embedded report approach. The code I used is below. I get an exception that it "Failed to open the connection". I tried the ServerName with and without the "(local)\" prefix.

The ConnectionInfo parameters are the ones that I've used successfully to make a connection to the database elsewhere in the program.

ConnectionInfo connectionInfo = new ConnectionInfo();
            connectionInfo.DatabaseName = "MyDB";
            connectionInfo.IntegratedSecurity = true;
            //connectionInfo.ServerName = @"(local)\SQLEXPRESS";
            connectionInfo.ServerName = @"SQLEXPRESS";

            Tables reportTables = DeviceDataReport.Database.Tables;
            foreach (Table theTable in reportTables) {
                TableLogOnInfo theTableLogoninfo = theTable.LogOnInfo;
                theTableLogoninfo.ConnectionInfo = connectionInfo;
                theTable.ApplyLogOnInfo(theTableLogoninfo);
            }

Open in new window

0
 
mlmccCommented:
I agree.  It is probably good if you can just follow it from step 1 through a done report but to find specific information is difficult

One of the biggest issues I see here is how to change the database.  I have found the easiest way is to build the report against a dataset rather than a database then in the application you can build the dataset and pass it to the report.

mlmcc
0
 
BlearyEyeAuthor Commented:
I actually did that for this report at one point, and still have the code (commented out). But I don't like the idea since (a) it adds complexity for maintenance if tables change and (b) it's in-memory and there's the possibility that eventually the report dataset will become too large.

If there's no recourse, I'll just stick with ODBC. It's a pain but do-able. However the code I wrote, following the tutorial's guide, looks plausible. It would be nice to know what's wrong.
0
 
mlmccCommented:
I agree, it would e nice.  It seems to work for some just fine and in others there are problems.  I don't think I have seen any code that just seems to work for everyone.

Does the report have a subreport?
If so you have to handle the subreport connections separately

mlmcc
0
 
BlearyEyeAuthor Commented:
No, no subreport ...
0
 
mlmccCommented:
The main problem is Crystal does its own connection to the database and how it does that based on the values presented is an unknown.  

mlmcc
0
 
msd1305Connect With a Mentor Commented:
I had the same problem. All reports were created using Production database connection and they would not work if I try with the application that uses staging database unless I manipulate TNS Names entries.

Finally I was able to resolve it yesterday. The idea (or rather a problem) is that you have to explicitely set each and every table's and stored procedure's database logon details. Good things is that crystal gives you access to all its objects and related information but problem that I faced was with stored procedures. If stored procedures are stored inside packages then crystal loses that information or rather doesnt expose that information so I had to actually write a small oracle query to find out package name from the procedure name in order to set the database logon details.

I am sorry if this was too descriptive. But I am off today and I dont have access to my code. So the above mentioned could help you and give you the directions. Otherwise I'll paste my code tomorrow.

Thanks!
0
 
BlearyEyeAuthor Commented:
msd1305: I'm glad there's a way out on this. Yes, I'd appreciate the technical details.
0
 
msd1305Commented:
Sorry I dont have the code with C# syntaxes. But it shouldnt be very difficult to convert this in C#.
Hope this helps.
0
 
BlearyEyeConnect With a Mentor Author Commented:
Thanks. Your example helped me locate http://snippets.dzone.com/posts/show/4029 which had an example for SQL Server. My revised code is shown below.  What's changed is that I added the line to set table location and I removed the line to set ServerName in connectionInfo (see the example in http://msdn.microsoft.com/en-us/library/ms227736%28v=VS.80%29.aspx which does not set ServerName).

All is well so far. I can change DatabaseName and get a report that varies by the database. Very nice.

However, I was puzzled that when I tried to set ServerName I would get an exception. I took a look at the table's LogOnInfo.ConnectionInfo.ServerName property as stored in the report. It contains "MyDBReports", which is the name of the ODBC connection the report would ordinarily use. I can set that to null string and things work but if I set it to "SQLEXPRESS" or "(local)\SQLEXPRESS" as I might expect, things break.

So now I'm wondering how CR identifies the server. I don't see anywhere in the table properties where SQLEXPRESS is cited. Any thots?

ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.DatabaseName = "MyDB";
connectionInfo.IntegratedSecurity = true;

Tables reportTables = DeviceDataReport.Database.Tables;
foreach (Table theTable in reportTables) {
    TableLogOnInfo theTableLogoninfo = theTable.LogOnInfo;
    theTableLogoninfo.ConnectionInfo = connectionInfo;
    theTable.ApplyLogOnInfo(theTableLogoninfo);
    theTable.Location = connectionInfo.DatabaseName + ".dbo." + theTable.Location;
}

Open in new window

0
 
BlearyEyeAuthor Commented:
I included my comment as part of the solution as it summarizes my results. However, I credit the EE respondents for providing the solution.
0
All Courses

From novice to tech pro — start learning today.