Connecting to COM or .NET Data Sources

Crystal Reports provides direct access, or native, drivers for some databases. These drivers are written specifically for a particular database and are often the best choice. However, because hundreds of types of databases exist, Business Objects can't possibly write direct access drivers for all of them. So, often, users turn to using standard data access layers such as ODBC or OLEDB to connect to their databases. Often, the vendor of a database provides an ODBC driver or OLEDB provider so that other applications can access the vendor's database. Sometimes though, even this is not enough. Customers have data that they would like to report off of that is not accessible by any Crystal Reports data source driver or via ODBC or OLEDB. To accomplish this, customers often turn to the COM Data Source driver, the ADO.NET Data Source driver, or the Java Data Source driver. This section describes the COM version of the driver, but much of the theory applies to the ADO.NET and Java Data Source drivers as well.

Note

The Component Object Model, or COM, is a Microsoft-based technology for software component development. It's the underlying technology that runs Visual Basic and Active Server Pages. A COM object is a piece of code that adheres to the COM specification and is easily used by other components, either inside a single application or between disparate applications. Microsoft has chosen to replace COM as a supported development platform with .NET and as a result ADO.NET will supersede COM. With the volume of COM code that has already been produced, it is expected to be supported for many years to come.

Because COM is a popular technology, Crystal Decisions decided to leverage it to create an extensible data source driver mechanism. This COM Data Source driver doesn't connect to a databaserather it gets data from a COM object written by you. This means that if you are somewhat savvy in the Visual Basic world, you can write your own mini data source driver (called a COM Data Provider) that enables access to data that would otherwise be unavailable.

To better understand the concept of writing your own COM Data Provider, look at a few scenarios in which this can be beneficial.

Leveraging Legacy Mainframe Data

Although new technologies are surfacing at an alarming rate, many companies still have data held in legacy mainframe systems. Often, the nature of these systems doesn't allow for any kind of relational data access, and thus lowers the value of the system. However, these systems can often output text-based files, called print files or spool files, that contain the data held in the mainframe system. These text-based files are often more complicated than a set of simple comma-separated values and thus require a bridge between the files and a data access and reporting tool like Crystal Reports. Writing a COM Data Provider can serve just this purpose. The Data Provider would read the text files, parse out the required data, and return it to Crystal Reports for use in numerous reports.

Handling Complex Queries

Often, companies have a database that is accessible via standard Crystal Reports data access methods. However, the process of connecting to the database and performing a query can be quite complex. Sometimes this is because the database servers are constantly changing, queries are becoming more complex, and other business processes affect the complexity of the query. By writing a COM Data Provider, a clever person can abstract the location and complexity of the database interaction away from the user designing a report. The user simply connects to the Data Provider, and the rest of the logic is done transparently in the background.

Runtime Manipulation of Data

Performing a simple query against a database that returns a set of records is often all that is needed. However, sometimes logic needs to be incorporated into the query that cannot be expressed in the database query language (using SQL). Other times, per-user manipulation of data needs to be performed, such as removing all salaries stored in a database for all other users other than the currently logged-in user for confidentiality purposes (often called data-level security). This runtime manipulation can be performed by either a COM Data Provider or a .NET Dataset Provider.

These three scenarios outline just a few of the reasons why you might want to use the COM Data Source driver or .NET Dataset Provider and create your own Data Provider. The following sections describe the technical details of doing this. The example demonstrates creating a COM provider, but the same functionality can be provided from .NET. For the report author, both methods are equal and transparent.

Creating a COM or .NET Data Provider

COM Data Providers can be written in any development language or platform with the capability of creating COM objects. Most commonly, they are created in either Visual Basic or Visual C++. The following example uses Visual Basic, but it can easily be translated to other development languages. To create a simple COM Provider, follow these steps:

1.	Open Visual Basic and create a new project. Instead of choosing the standard project type of Standard EXE, choose ActiveX DLL (see Figure 15.1). ActiveX is another name for COM technology. Choosing this creates a project that contains a COM object (by default called Class1). Figure 15.1. Creating a new ActiveX DLL project in Visual Basic.
2.	The interface between the COM Data Provider that you create and the Crystal Reports COM Data Source driver is based on ActiveX Data Objects, or ADO. To use ADO in your project, you must first create a reference to it. From the Project menu inside Visual Basic, select References. From the list on the ensuing dialog, look for Microsoft ActiveX Data Objects. You might have just a single version of this on your machine, or you might have several. It's usually easiest to just select the latest version. Figure 15.2 illustrates this. Figure 15.2. The Visual Basic Project References dialog is shown here referencing the ADO Library.
3.	After that is done, the only thing left to do is create a function inside your class that returns an ADO recordset. The basic outline for this function is shown here. See the next section for more information on returning an ADO recordset. Public Function GetRecordset() As ADODB.Recordset Dim rs As New ADODB.Recordset ' Populate the recordset Set GetRecordset = rs End Function
4.	By default, the class is named Class1. It's best to give this a more meaningful name, such as DataProvider. Do this by selecting the Class1.cls file in the Project Explorer and changing the (Name) property from the Property Browser.
5.	Also, the project name is Project1 by default. It's best to give this a more meaningful name such as the company name or type of data name: for example, Xtreme or Sales. Do this by selecting Project1 Properties from the Project menu and changing the Project Name setting.
6.	Build the DLL by selecting Make from the File menu; the name is not important.
7.	Open the Crystal Reports Designer and create a new report. From the Data Explorer, choose Create New Connection and then expand the More Data Sources item; then choose COM Connectivity. This presents a dialog asking for you to enter the Program ID. To identify your COM Data Provider, enter the ProjectName.ClassName; for example, Xtreme.DataProvider.
8.	You'll receive a table list just like from a traditional database, but the table list is actually a list of methods on your COM object that return ADO recordsets.

Returning an ADO Recordset

There are generally two ways to obtain an ADO recordset: performing a database query and constructing it yourself. The following code example illustrates how to perform a database query and obtain a recordsetin this case using a query against the Xtreme sample database.

[View full width]
Com Public Function CustomerOrders() As ADODB.Recordset         Dim rs As New ADODB.Recordset         Dim conn As New ADODB.Connection         conn.Open("DSN=Xtreme Sample Database 11.5")         Dim sql As String         sql = "SELECT * FROM Customer, Orders WHERE Customer.[Customer ID] = Orders. [Customer ID]"        rs.Open(sql, conn)        CustomerOrders = rs    End Function .NET (C#) System.Data.DataSet getReportData()         {             // DataSet object that will be returned to the report             DataSet dataSet = new DataSet();             //ODBC Connection object             System.Data.Odbc.OdbcConnection myConnection;             //ODBC Connection String             string strConnection ="DSN=Xtreme Sample Database 11.5";             myConnection = new System.Data.Odbc.OdbcConnection (strConnection);             //ODBC data apdapter to fill the dataset with the results from the query             System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter();             //open the connection             myConnection.Open();             //create sql command             System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.  OdbcCommand("SELECT * FROM Customer", myConnection);             command.CommandType = CommandType.Text;             // set the select command             adapter.SelectCommand = command;             //Fill the dataset             adapter.Fill(dataSet);             return dataSet;         }

The question you might be asking yourself is how this query could be parameterized. The COM Data Source driver handles this nicely. It maps any arguments you have defined to your method into report parameters. The following code example illustrates a Data Provider function that has a parameter:

Public Function Customers(CountryParam As String) As ADODB.Recordset     Dim rs As New ADODB.Recordset     rs.Open "SELECT * FROM Customer WHERE Country = '" & CountryParam & "'", _     "DSN=Xtreme Sample Database 11.5"     Set Customers = rs End Function

When a Data Provider with a parameterized method is used from the Report Designer, the user is prompted for a parameter value.

As was mentioned previously, one way to obtain a recordset is to perform a query. Listing 15.1 illustrates how to construct a recordset on the fly and read data out of a text file.

Listing 15.1. A COM Data Provider That Parses Data from a CSV File

Public Function CSVText(ByVal FileName As String) As ADODB.Recordset         Dim rs As New ADODB.Recordset         ' Open the text file         Dim FileSystem As New IWshRuntimeLibrary.FileSystemObject         Dim fileText As IWshRuntimeLibrary.TextStream         fileText = FileSystem.OpenTextFile(FileName)         ' Read the first line of text to grab the field names         Dim buffer As String         buffer = fileText.ReadLine()         Dim fields() As String         fields = Split(buffer, ",")         Dim i         For i = LBound(fields) To UBound(fields)             ' Add a field in the recordset for each field in the csv file             rs.Fields.Append(fields(i), ADODB.DataTypeEnum.adBSTR)         Next         rs.Open()         ' Read the contents of the file         While Not fileText.AtEndOfStream             buffer = fileText.ReadLine()             rs.AddNew()             For i = LBound(fields) To UBound(fields)                 ' Grab the field values                 fields = Split(buffer, ",")                 rs(i).Value = fields(i)             Next             rs.Update()         End While         CSVText = rs     End Function Public Function CSVText(ByVal FileName As String) As ADODB.Recordset         Dim rs As New ADODB.Recordset         ' Open the text file         Dim FileSystem As New IWshRuntimeLibrary.FileSystemObject         Dim fileText As IWshRuntimeLibrary.TextStream         fileText = FileSystem.OpenTextFile(FileName)         ' Read the first line of text to grab the field names         Dim buffer As String         buffer = fileText.ReadLine()         Dim fields() As String         fields = Split(buffer, ",")         Dim i         For i = LBound(fields) To UBound(fields)             ' Add a field in the recordset for each field in the csv file             rs.Fields.Append(fields(i), ADODB.DataTypeEnum.adBSTR)         Next     rs.Open()     ' Read the contents of the file     While Not fileText.AtEndOfStream         buffer = fileText.ReadLine()         rs.AddNew()         For i = LBound(fields) To UBound(fields)             ' Grab the field values             fields = Split(buffer, ",")             rs(i).Value = fields(i)         Next         rs.Update()     End While     CSVText = rs End Function

This code could be used as is or adopted to meet the needs of other kinds of files or data sources. Using the COM Data Source driver gives you complete flexibility and control over the data source.