Solved

Setting ODBC connection dynamically in C# for Crystal Reports

Posted on 2010-11-13
45
4,894 Views
1 Endorsement
Last Modified: 2012-05-10
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?
1
Comment
Question by:BlearyEye
  • 22
  • 11
  • 11
45 Comments
 
LVL 1

Author Comment

by:BlearyEye
ID: 34127401
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34127464
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
 
LVL 4

Expert Comment

by:msd1305
ID: 34129610
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34129696
Can you give me a pointer? I haven't seen SetDataSource used that way.
0
 
LVL 4

Expert Comment

by:msd1305
ID: 34129728
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34133556
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34134302
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
 
LVL 4

Expert Comment

by:msd1305
ID: 34134754
Yes. Even I personally prefer avoiding datasets mainly to keep the report independent of the application which adds a great flexibility for testing !
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34135996
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
 
LVL 4

Expert Comment

by:msd1305
ID: 34136037
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34166968
Sorry I've been unresponsive ... distracted by deadlines ... will give this a try as soon as I can.
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34259604
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34259804
I susoect there isn't one.  the CR versioon for VS2010 was developed separately and not included with VS2010

mlmcc
0
 
LVL 4

Expert Comment

by:msd1305
ID: 34263826
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34264272
Actually the production VS2010 dlls  have just been released

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

mlmcc
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34286690
I'll update to the production version & try the method.
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34295955
With no little difficulty, I was able to get the beta version uninstalled (see http://forums.sdn.sap.com/thread.jspa?messageID=9795681&#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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34296210
I don't know.  I haven't used the VS versions

mlmcc
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34327803
Any thoughts? I can't get the SetDatabaseLogon method to work ...
0
 
LVL 4

Expert Comment

by:msd1305
ID: 34327881
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
 
LVL 4

Expert Comment

by:msd1305
ID: 34327885
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34331869
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Expert Comment

by:msd1305
ID: 34332558
SetDatabaseLogon("your username","your password","SQL server name","database name", True)
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34373677
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34507060
I want to continue on this issue. I was delayed by int'l travel & east coast winter storm, but am back now.
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34507096
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34508605
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34508612
One way to tell is hit F5 or the Refresh icon on the report when viewing.

mlmcc
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34512992
There's no refresh icon on the report and f5 has no effect. I'll try the DiscardSavedData ...
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34518578
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 34518768
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34518777
The discardsaveddata might be HasSavedData

mlmcc
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 34547629
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34547898
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34548741
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34548854
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34549453
No, no subreport ...
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34554082
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
 
LVL 4

Assisted Solution

by:msd1305
msd1305 earned 250 total points
ID: 34608116
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
 
LVL 1

Author Comment

by:BlearyEye
ID: 34610712
msd1305: I'm glad there's a way out on this. Yes, I'd appreciate the technical details.
0
 
LVL 4

Accepted Solution

by:
msd1305 earned 250 total points
ID: 34612509
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
 
LVL 4

Expert Comment

by:msd1305
ID: 34612512
Sorry I dont have the code with C# syntaxes. But it shouldnt be very difficult to convert this in C#.
Hope this helps.
0
 
LVL 1

Assisted Solution

by:BlearyEye
BlearyEye earned 0 total points
ID: 34613682
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
 
LVL 1

Author Closing Comment

by:BlearyEye
ID: 34859970
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now