Database Programming with .NET


You are now ready for relational database programming. At this point, you have established your access ( user logon, password, and so forth) to a SQL Server 2000 installation. Your local workstation is equipped with the SQL Server client-side tools (Enterprise Manager, Query Analyzer, and so on). Perhaps you have even toured the Northwind and Pubs sample SQL Server databases. You are now ready to actually write some code. So, you may ask, "Where should I start?"

Let's assume for the moment that you have heard of ADO.NET. Maybe you have read somewhere that ADO.NET is "the" new database technology for .NET. So, your search begins.

It is likely that you might open VS .NET and do a search using the Help option on the menu bar. I tried this myself . I used "ADO.NET" as my "Look for:" value and "Fujitsu NetCOBOL for .NET" as my "Filtered by:" value. As shown in Figure 11-14, the search was unable to locate any Help items (as indicated by the "0 topics found" message at the bottom of the window).

click to expand
Figure 11-14: Searching VS .NET for ADO.NET information (filtered by "Fujitsu NetCOBOL for .NET")
start sidebar
Why Do I Use the VS .NET Help Option?

I have set out to guide you to (and through) the voluminous amounts of Help text that is available (in some cases, attempting to supplement what is available). Hence, I need to first confirm the availability of such Help text. Typically, after digesting the available material (if I am able to find any), I ask myself questions such as the following: Was this information clear or misleading? Is there anything that I can add, reword, or rephrase to improve the Help text that is already available? Will a mainframe analogy be helpful? Is this a good candidate to include in the "To Learn More" section at the end of the chapter? Naturally, the desired end result in following this approach is a useful guide to .NET for the mainframe programmer.

end sidebar
 

What, no topics found? How can that be? ADO.NET is after all a major .NET topic. After scribbling a note to myself ( Chris, remember to ask the helpful and responsive Fujitsu Software technical support contact about adding ADO.NET to their Help text ), I decided to do yet another search. This time, I changed the "Look for:" value to "(SQL) or (DATABASE)". I kept the "Filter by:" value set to "Fujitsu NetCOBOL for .NET". As you can see in Figure 11-15, the search returned about 28 relevant Help text items.

click to expand
Figure 11-15: Searching VS .NET for "(SQL) or (DATABASE)" “ related information (filtered by "Fujitsu NetCOBOL for .NET")

If you drill down into each of the 28 relevant Help text items, you will find full discussions on "how to use embedded SQL" in your COBOL .NET applications. You will also find supportive discussions (e.g., "how to connect to a server/database"). About 90 percent of this information will look familiar to you. In other words, these 28 relevant Help text items will be helpful to you if (and only if) you needed to continue coding your database logic using the mainframe DB2 legacy style.

Tip  

Giving Fujitsu Software the benefit of the doubt, I decided to search their NetCOBOL for .NET Web site ( http://www.netcobol.com/ ) and eventually found a link to an extremely useful Web site. As it turns out, Fujitsu Software provides the following Web site, which contains a healthy number of ADO.NET QuickStart samples (and other types of samples): http://www.netcobolsamples.com/. Although I hope Fujitsu will add additional VS .NET Help topics (even for ADO.NET) in future releases of their NetCOBOL for .NET product, the NetCOBOL samples Web site will certainly suffice for the time being.

Legacy-Style Database Programming in .NET

Obviously, I am biased when faced with the choice of using a "legacy" coding style or a "contemporary" coding style. Looking at this from a developer's point of view, sometimes it is just more exciting to use newer technology. Nevertheless, you cannot ignore that there are other points of view.

There could, in some cases, be good arguments for using a legacy coding style. Even when the playing field is leveled, when productivity and performance are equal, a case could be made for choosing one style over the other. However, be forewarned. Some developers may find themselves having to deal with "perceptions" as much as "reality." Who knows ? In the worst-case scenario, you may even need to deal with political issues.

start sidebar
Commendation vs. Condemnation

Fujitsu Software should not be criticized for providing backward compatibility in their .NET product. Rather, they should be commended. Some companies will see this as a way to gradually transition legacy applications over to the .NET platform ”a less steep learning curve.

Fujitsu is not alone in taking this approach of building backward compatibility into their products. Microsoft has also designed .NET to support its own legacy style of database programming. In pre-.NET Win32/Web applications, you will find Active Data Objects (ADO) being used for database programming. The technology of ADO preceded ADO.NET and continues to be supported in .NET. Using .NET's COM Interoperability technology, you can continue (when needed) to use ADO in your .NET applications.

end sidebar
 

Given these possibilities and unknowns, it will be prudent to at least take a quick peek at what legacy-style database programming looks like in COBOL .NET. The pseudo-code in Listing 11-1 shows the basic language elements and the required syntax. Please take a moment to read the programming code and the comments included within the pseudo-codeproject, LegacyDatabaseAccessCobol. Notice the use of embedded SQL encased within EXEC statements ”just like in the old days.

Listing 11-1: A COBOL .NET Pseudo-Code Project Demonstrating Legacy-Type Database Access
start example
 000010 IDENTIFICATION DIVISION. 000020 PROGRAM-ID. Program1 AS "Program1". 000030 ENVIRONMENT DIVISION. 000040 DATA DIVISION. 000050 WORKING-STORAGE SECTION. 000060  000070* Include the code below to represent the legacy 000080* style Host Structure or Host Variable. 000090* The Northwind/Categories Table is used as an example. 000100* In the past, you may have used the DCLGEN  000110* (or the Declaration Generator) in DB2I for this. 000120* A more traditional way of doing this would have  000130* been to have the "declared Host Structure" in a  000140* copybook. Then an INCLUDE statement would have been 000150* used. The same thing applies to the use of the  000160* SQL Communication Area. Legacy Styles might have  000170* normally used an INCLUDE statement. The SQLSTATE  000180* Variable below is used for this purpose. 000190     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 000200 01 Table-LIST. 000210    05 CategoryID       PIC S9(4) COMP-5.  000220    05 CategoryName     PIC X(15). 000230    05 Description      PIC X(16).  000240    05 Picture-Image    PIC G(15) DISPLAY-1. 000250 01 SQLSTATE            PIC X(5).  000260    EXEC SQL END DECLARE SECTION END-EXEC. 000270 000280 PROCEDURE DIVISION. 000290  000300* Include the code below to Connect to your data source 000310* Your Server and database connection information may vary 000320    EXEC SQL  000330         CONNECT TO '(LOCAL)' AS 'DemoODBC' USER 'sa/'  000340    END-EXEC. 000350  000360* Include the code below to declare a Cursor for 000370* Query. As with Legacy Styled coding, you would 000380* do this whenever you were expecting more than 000390* one row returned in your result set. 000400* The Northwind/Categories Table is used as an example. 000410    EXEC SQL DECLARE CategoriesCUR CURSOR FOR  000420       SELECT * FROM Categories 000430    END-EXEC. 000440 000450* Naturally, you need to Open your Cursor before using it.  000460    EXEC SQL OPEN CategoriesCUR END-EXEC  000470 000480* The code statement below would be used to actually 000490* Read the "Next Record" in the database table  000500    EXEC SQL 000510       FETCH CategoriesCUR INTO :Table-LIST  000520    END-EXEC.  000530 000540* Good housekeeping: you close your cursor when done 000550    EXEC SQL CLOSE CategoriesCUR END-EXEC.  000560 000570* Include the code below to Query your Data Source 000580* You would use this when only expecting one row in your 000590* return set. Notice the legacy style usage of a  000600* colon being used for the Host Variables. 000610* The Northwind/Categories Table is used as an example.  000620    EXEC SQL 000630    SELECT CategoryID,CategoryName,Description  000640                   INTO     :CategoryID, 000650                            :CategoryName, 000660                            :Description 000670                   FROM Categories 000680                   WHERE CategoryID = 7 000690    END-EXEC. 000700  000710* Include the code below to Disconnect from your data source 000720    EXEC SQL DISCONNECT CURRENT END-EXEC. 000730 END PROGRAM Program1. 
end example
 
Note  

The use of COMMIT and ROLLBACK commands as a way to simulate a logical unit of work (to ensure data integrity) continues to be supported in COBOL .NET. As you did in your legacy mainframe COBOL (database) applications, you can continue to use these two commands within the legacy-style EXEC delimiters.

I have referred to the COBOL .NET code in Listing 11-1 as being in pseudo-code form. In other words, although the code is syntactically correct and will compile, you will need to complete a few tasks before actually executing it. For example, you will need to add actual business logic to the Procedure Division. Additionally, the required environment setup needs to be completed as per Fujitsu's documentation.

Note  

Most legacy mainframe developers are likely to welcome a departure from the concerns of BINDING and PACKAGING their mainframe COBOL database programs.

The Environment Setup: To Do or Not to Do?

The typical environment setup may be familiar to those of you who have done COBOL coding on the Win32 platform before (using earlier COBOL compilers from Fujitsu Software or Micro Focus). For the rest of you, this will represent a learning curve. Assume for a moment that you were thinking about staying with the legacy style of database programming (in .NET). You would then need to review the environment setup steps detailed in Fujitsu's documentation. The following four environment setup topics would be of concern to you:

  • Using Fujitsu's runtime Environment Setup tool

  • Creating a runtime initialization file

  • Using Fujitsu's Open Database Connectivity (ODBC) Setup utility

  • Creating an ODBC information file

Note  

With all of the other things that you will be learning, I cannot see any practical reason why you might want to go through the effort of setting up your environment to support legacy database “style programming. At most, just know that it is possible (just in case).

Now, assume that you have decided to "upgrade" your database programming coding style. In that case, there would be very little reason to complete the environment setup. However, there would still be value gained simply from viewing the COBOL .NET pseudo-code shown in Listing 11-1. Why? The legacy coding “style code, whether done on the mainframe or here in COBOL .NET, will serve as an opportunity for analogies during the discussions in the next section.

Although I encourage you to learn more about working with ODBC (see the section "Open Database Connectivity Configuration" in Chapter 3), I'll stop just short of really questioning why anyone wanting to learn . NET and wanting to create .NET applications would choose to avoid using a .NET way of database programming (i.e., the ADO.NET technology). After all, there are two very good reasons why you should use the newer .NET programming style when coding .NET applications:

  • ADO.NET is designed to support a disconnected model for database programming. This is more appropriate for distributed Web applications.

  • ADO.NET is integrated with XML (I discuss this further in Chapter 12). XML integration becomes critical when you are working with Web and Web service applications.

Convinced yet? Don't worry. You'll turn now to explore the newer .NET way of database programming using the ADO.NET technology. After you see how easy it is to use the ADO.NET Framework objects, your choice will be much clearer.

.NET-Style Database Programming in .NET

Generally speaking, the .NET style of database programming means using the ADO.NET technology (managed objects) to access your database source. As mentioned in the previous section, the ADO.NET technology is characterized by its support for the Disconnected Application Model and XML. To further understand this technology, you will explore the following two major components that make up ADO.NET (see Figure 11-16):

click to expand
Figure 11-16: A general view of the ADO.NET technology
  • .NET data providers

  • .NET Dataset

.NET Data Providers

In your legacy mainframe database development (when using DB2), you understood that DB2 provided attachment facilities. These attachment facilities provided the connections (referred to as threads on the mainframe) between your application and the actual DB2 database. Furthermore, you may have been familiar with some of the other mainframe DB2 components, including Database Services, Relational Data Systems, Data Manager, and Buffer Manager. Functionally, these DB2 components are similar to the .NET data providers.

Each time you coded the EXEC delimiters to execute SQL commands in your mainframe COBOL programs, you were using the services of the DB2 components. With ADO.NET, you will use classes from the .NET Framework to leverage the services offered by the .NET data providers. .NET ships with the following data providers: [8]

  • SQL Server .NET Data Provider

  • Oracle .NET Data Provider

  • OLE DB .NET Data Provider

Each .NET data provider has a .NET namespace (containing classes and so forth) associated with it. If you were using the SQL Server .NET Data Provider, you would be interested in the System.Data.SqlClient namespace. If you were using the OLE DB .NET Data Provider, the System.Data.OleDb namespace would be most important to your ADO.NET development.

Tip  

If you want to do a search in VS .NET to find information on ADO.NET, simply remove the "Fujitsu" filter from the earlier search. Practically any other "Filter by:" choice (even the choice that reads "No Filter") will allow the search to retrieve ADO.NET-related Help topics.

Depending on the type of database (or data source) you are targeting, you would choose to use one provider/namespace over another. The SQL Server .NET Data Provider is optimized for Microsoft's SQL Server database (version 7.0, version 2000, and later) and the Microsoft Data Engine (MSDE). The OLE DB .NET Data Provider would be appropriate for older SQL Server database versions (version 6.5 and earlier) and Microsoft's Access database product. Because you are using the SQL Server 2000 product, you will use the SQL Server .NET Data Provider and the System.Data.SqlClient namespace.

Note  

If you find that you need to use another data provider (e.g., the OLE DB .NET Data Provider) in your own development, you will be able to apply all of the information I discuss here. Moving from one namespace (e.g., System.Data.SqlClient) to another (e.g., System.Data.OleDb) is rather easy. After making an adjustment for a small name "prefix" difference, you will find the namespaces to be almost identical. Incidentally, you will notice that both data provider namespaces (System.Data.SqlClient and System.Data.OleDb) contain objects that derive from the System.Data.Common namespace.

The System.Data.SqlClient Namespace

Let's now drill down into the System.Data.SqlClient namespace. Of the sixteen or so classes and the three delegates that this namespace contains, you should begin by first gaining a solid understanding of the following four classes:

  • System.Data.SqlClient.SqlConnection

  • System.Data.SqlClient.SqlCommand

  • System.Data.SqlClient.SqlDataReader

  • System.Data.SqlClient.SqlDataAdapter

You will use the SqlConnection class to connect to the SQL Server database. This class has properties and methods that you will use to accomplish this. One of the properties, ConnectionString , is used to communicate details such as the server name, user ID, and password. As you might have figured out by now, there is an Open and a Close method exposed by the SqlConnection class.

Tip  

There is a SqlTransaction object that you can create using the BeginTransaction method of the SqlConnection class. The use of the ADO.NET SqlTransaction class will remind you of your use of the DB2/ SQL COMMIT and ROLLBACK commands in your legacy COBOL programs.

Once you have a "Connection" object (providing a connection to your database), you will want to use the SqlCommand class. This class has properties and methods that facilitate the association between your Command object and your Connection object. You then set other properties and methods depending on whether or not you are executing a stored procedure, and depending on what type of results (in your result set) you are expecting to return. You are basically "executing" your command to achieve a desired result. Among the Command object's Execute methods are the following: [9]

  • ExecuteScalar: Use this method to send commands to the Connection object to return a single value.

  • ExecuteNonQuery: Use this method to execute commands that do not return rows.

  • ExecuteReader: This method sends commands to the Connection object to return a DataReader object (which I discuss further in this section).

    Note  

    You can use the constructor of the SqlCommand class to create a Command object. Optionally, you can use the Connection object's CreateCommand method to create Command objects.

This leaves you with the SqlDataReader and SqlDataAdapter classes ( DataReader and DataAdapter objects, respectively). Your choice of one of these classes really depends on your ultimate "data" need and application design. Generally speaking, you use DataReader objects when your data needs are met by a read-only, forward-only stream of data (output from your database). On the other hand, if your data needs extend beyond that, use of the SqlDataAdapter class may be more appropriate for your ADO.NET development.

Note  

The use of these ADO.NET "data provider" classes should remind you of your use of SQL/DB2 commands in your legacy mainframe DB2 applications. After all, during your years of legacy mainframe development, you understood the ideas of connecting to your database, executing SQL commands, and handling result sets.

Though this section presented a brief mention of the SqlDataAdapter class, you will have an opportunity to revisit this topic. In the next section, when you explore the "other" major component of ADO.NET, I further discuss the SqlDataAdapter class. (Hint: You use the SqlDataAdapter class as a bridge between your database and a Dataset object.)

Tip  

Soon after you are comfortable with the System.Data.SqlClient classes discussed in this section, be sure to look into the System.Data.SqlClient.SqlError class. You will find that the SqlError class provides similar functionality to that provided by the mainframe DB2 SQL Communications Area (SQLCA). As you recall, you used the SQLCA to collect any available "warning and error" information generated by the database engine. Typically, you included the SQLCA and appropriate conditional logic when you did database programming in your legacy mainframe COBOL applications. Good defensive programming design will direct your usage of the SqlError class.

.NET Dataset

I hinted in the previous section that the SqlDataAdapter plays a key role with the Dataset object. This is only the beginning ”I'm just scratching the surface of the Dataset topic. Nevertheless, before you go below the surface (and dive into the .NET namespace objects), I want to assist you in moving toward a better perspective of why .NET Datasets are important and when you might want to use one.

I have included a discussion in the following section that should make it easier for you to understand the usefulness offered by the new .NET Dataset object. The discussion follows an imagined scenario that draws from your legacy mainframe experience.

Increasing Data Demands

If you will, please imagine the following scenario: You are on the mainframe. You have reviewed a requirements document detailing the legacy COBOL DB2 application that you will create. [10] You turn to your trusted ISPF/DB2I tool to perform a Declaration Generator (DCLGEN) task.

Once the DCLGEN is complete, you use the COBOL INCLUDE statement to introduce the DB2 host structure/host variables to the Working-Storage portion of your COBOL program. You then proceed to code a complex SQL statement that will perform a database SELECT query (perhaps JOINing a few tables in the process). You embed the SQL inside of the EXEC delimiters and successfully complete your application development.

Then it happens. You discover that your query is actually going to return multiple records. No problem. First, you confirm that this was omitted from the requirements documentation. Next, you modify your code to include the appropriate DB2/SQL statements to support cursor-type [11] processing (e.g., CURSOR DECLARE, FETCH, and OPEN/CLOSE).

Tip  

As it would have been wise to use DB2I/SPUFI in this case (to test your query outside of your actual application), SQL Server's toolset (discussed earlier in the section "Client-Side Tools") offers similar advantages.

When your business analyst discovers that you are modifying the program, the analyst requests an additional business requirement: Change the application fromread-only to read and update. Your "completed" application now needs to be modified. You quickly add the FOR UPDATE clause to your SQL/DB2 query to enable your cursor for database updating. As you prepare your mainframe database program for production deployment, your business analyst comes back with "just one more" requirement.

Note  

As contrived as this scenario may appear, some developers (on the mainframe and Windows/Web environments) actually experience this on a daily basis. If you have not, consider yourself lucky.

You now find out that your program will need to be able to scroll (bidirectionally) through your result set. After silently swearing to yourself, you go ahead and add a SCROLL clause to your CURSOR declaration. With foresight, you even add an ARRAY in your program to create an in-memory table to hold the result set. Your business analyst then pays you another visit.

Tip  

In the case of ADO.NET's data provider classes, these two business requirements (bidirectional scrolling ability and updatable) would have been reasons to not use the SqlDataReader class. Recall that the SqlDataReader class is for read-only and forward-only data usage. The SqlDataAdapter class then becomes a more appropriate data provider class choice.

The business analyst informs you that there are a few more requirements. You are now told that your application will need to perform a second database query, dynamically join with the first query (the saved array), perform searches using the combination of both queried result sets, update the virtually joined data, apply the updates to the database, and then write out the virtually joined data in XML format to use as input for the new Web service [12] audit application.

As you sit there, stunned and amazed, your business analyst then informs you that the business users want the flexibility of being able to load your database application onto their laptops, pocket PCs, and tablet PCs to use while they are offline, in the field.

start sidebar
Business Users Are Not the Enemy

Our job, our professional livelihood, is given purpose by satisfying the growing needs of business users. The .NET platform's ADO.NET technology better enables your software development to address the new needs of your business users.

end sidebar
 

The way that this imagined scenario ends is really up to you. Perhaps you can end it by imagining that you eventually convert your legacy mainframe database application to a .NET application that uses ADO.NET. Imagining a happy ending to this scenario, you then discover ADO.NET offers an object called the .NET Dataset. Further discovery reveals to you that the .NET Dataset supports flexible, relational-like, in-memory data needs, XML formatting, and disconnected/offline usage.

Tip  

You can use the SqlDataAdapter class to create the Dataset class. Understand that there is additional overhead incurred with the SqlDataAdapter class. The overhead is justified only when it helps you meet your data usage needs. If your data needs can be satisfied with the SqlDataReader class, you should use it instead of the SqlDataAdapter (and Dataset) class.

To conclude this imagined scenario, say that you completed your application development using ADO.NET's Dataset class, easily satisfying all of the business requirements that your business analyst threw your way ”on schedule. On that note, let's return to reality and move on to learn about the details of the .NET Dataset.

The System.Data Namespace

As you certainly expected, there is a namespace provided to support the use of the .NET Dataset: System.Data . This namespace provides ADO.NET objects (dele-gates, interfaces, classes, and enumerations) for you to exploit. To better understand the usefulness of the System.Data objects, you will want to know the general structure of the .NET Dataset. (Please briefly revisit Figure 11-16 in the earlier section ".NET-Style Database Programming in .NET").

Note  

You can create a .NET Dataset in one of two ways: as a return type of the SqlDataAdapter class or independently without a SqlDataAdapter. Your choice typically will depend on what data source you are targeting to "fill" your Dataset. If you intend on filling your Dataset from a database query, then the SqlDataAdapter class is a good choice to create your Dataset. On the other hand, if you are filling your Dataset using any other data source (including data that is internal to your program), you can directly create your Dataset totally independent of any database or data provider objects.

Generally speaking, the .NET Dataset ( System.Data.Dataset ) is made up of one or more tables ( System.Data.DataTableCollection ). Each table( System.Data.DataTable ) is made up of a collection of columns ( System.Data.DataColumnCollection ) and a collection of rows ( System.Data.DataRowCollection ). The DataColumnCollection object is made up of individual columns ( System.Data.DataColumn ), with each column having a specific data type ( System.Data.DataType ). The DataRowCollection object is made up of individual rows ( System.Data.DataRow ).

Note  

You can create a System.Data.DataTable object either as part of a Dataset or as a stand-alone DataTable.

Of course, you will want to become familiar with many other System.Data objects. However, familiarizing yourself with the Dataset, DataTable, DataColumn, DataType, and DataRow objects should get you off to a good start. Remember, you will need the SqlDataAdapter object (discussed earlier in the section "The System.Data.SqlClient Namespace") if you are filling the Dataset from a database connection.

Tip  

Use the references in the "To Learn More" section at the end of this chapter to further your retraining effort. Additionally, the VS .NET Help text will prove to be very useful. Recall that the filter you use can greatly impact your ability to locate ADO.NET-related information.

I have discussed the ADO.NET data providers and the Dataset. Additionally, you have been introduced to the .NET namespaces associated with ADO.NET. In the next section, you will take a look at some sample programming code. There you will see the simplicity involved in putting ADO.NET to use.

Tip  

As you further your ADO.NET training, you will come across one other ADO.NET namespace: System.Data.SqlTypes. You will learn that this namespace mostly contains structures that offer a preferable way of mapping to native SQL Server data types.

Using ADO.NET

To demonstrate the use of the Data Provider and Dataset classes, I will use the SQL Server 2000 product as my data source. I will query the Microsoft-supplied sample Northwind database using a simple SQL SELECT command. I will create both the COBOL .NET and Visual Basic .NET (VB .NET) code using a VS .NET console template. First, I present the VB .NET sample code project (DataBaseAccessExampleVB), and then I present the COBOL .NET sample code project (DataBaseAccessExampleCobol). I suggest that you familiarize yourself with each sample code project (taking the bilingual approach). You should soon feel comfortable enough to enhance the sample code projects and further explore the relevant namespaces (e.g., consider writing the queried result set out to a text file).

Tip  

Microsoft has created a collection of components called Application Blocks for .NET. One of the Application Blocks available is the Data Access Application Block . This component set offers convenience by first packaging several ADO.NET classes and then exposing a collection of "shortcut" methods. When you take advantage of the Data Access Application Block, you can significantly reduce the number of coding lines required to use ADO.NET. I suggest that you first learn to use the ADO.NET class directly (as explained in this chapter). Later, you can easily switch to take advantage of the Data Access Application Block. You will find that the packaged routines will meet your needs most of the time. Use of the Data Access Application Block will require that you first download from Microsoft's Web site the Microsoft.ApplicationBlocks.Data.dll assembly. Afterward, you will be able to leverage the members of two managed classes: SqlHelper and SqlHelperParameterCache . For further details of this useful feature, see the reference provided in the "To Learn More" section (in the "Web Sites" subsection) at the end of this chapter. (For those using Oracle's database product, there is a corresponding Data Access Application Block assembly and managed "helper" class set available via Microsoft's MSDN Code Center Web site.)

ADO.NET with VB .NET Sample Code

You will notice in the sample code in Listing 11-2 ( copied from the Module1.vb file of DataBaseAccessExampleVB) that I have avoided the use of the VB .NET IMPORT statement. Although this statement is a good coding shortcut, for illustrative and training purposes it seems more useful to see exactly what namespace each object is coming from. In your own code, you may wish to use the IMPORT statement to make your code less verbose. It is up to you and your coding style/preference.

Note  

The ADO.NET assembly (System.Data.Dll) is already referenced by default in the DataBaseAccessExampleVB VB .NET project.

Listing 11-2: Sample VB .NET Code Demonstrating the Use of ADO.NET
start example
 'Sample Code using ADO.NET Technology Module Module1            Sub main()                  Call UseSqlDataAdapter(OptionalPreTableBuild)              Console.WriteLine(" ")                  Call UseSqlDataReader()              Console.ReadLine()            End Sub           Public Function OptionalPreTableBuild() As DataSet              'It is possible to obtain the "schema" or table structure              'directly/automatically from the SQL Server Database              'This section is added for training purposes.              'The information found in this section would be critical              'in the case of building a disconnected .NET dataset              'that may have a non-SQL Server Data Source.                  ' Create new DataTable.              Dim myDataTable As DataTable = _              New System.Data.DataTable("myCategories")                  ' Declare DataColumn and DataRow variables.              Dim myDataColumn As System.Data.DataColumn              Dim myDataRow As System.Data.DataRow                  ' Create 1st myDataColumn.               myDataColumn = New System.Data.DataColumn()              myDataColumn.DataType = System.Type.GetType("System.Int32")              myDataColumn.ColumnName = "CategoryID"              myDataColumn.Unique = True              myDataTable.Columns.Add(myDataColumn)                  ' Create 2nd myDataColumn.              myDataColumn = New System.Data.DataColumn()              myDataColumn.DataType = Type.GetType("System.String")              myDataColumn.ColumnName = "CategoryName"              myDataTable.Columns.Add(myDataColumn)                  ' Create 3rd myDataColumn.              myDataColumn = New System.Data.DataColumn()              myDataColumn.DataType = Type.GetType("System.String")              myDataColumn.ColumnName = "Description"              myDataTable.Columns.Add(myDataColumn)                  ' Create 4th myDataColumn.              myDataColumn = New System.Data.DataColumn()              myDataColumn.DataType = Type.GetType("System.Byte[]")              myDataColumn.ColumnName = "Picture"              myDataTable.Columns.Add(myDataColumn)                  ' Assign primary key column to CategoryID column              Dim PrimaryKeyColumns(0) As System.Data.DataColumn              PrimaryKeyColumns(0) = myDataTable.Columns("CategoryID")              myDataTable.PrimaryKey = PrimaryKeyColumns                  ' Reference the DataSet.              Dim myDataSet As New System.Data.DataSet()              ' Associate the Table with the Dataset.              myDataSet.Tables.Add(myDataTable)              myDataTable = Nothing              Return myDataSet           End Function           Public Sub UseSqlDataAdapter(ByVal myDataset As DataSet)               'Reference Data Provider Objects              Dim mySqlConnection As New System.Data.SqlClient.SqlConnection()              Dim mySqlDataAdapter As New System.Data.SqlClient.SqlDataAdapter()              Dim mySqlCommand As New System.Data.SqlClient.SqlCommand()                  'Reference Dataset Objects              Dim myDataRow As System.Data.DataRow                  'Prepare to Connect to SQL Server Database              'using Connection String              mySqlConnection.ConnectionString = _              "user id=sa;pwd=;Database=northwind;Server=(LOCAL)"                  'Associate the Command Object with the Connection Object              mySqlCommand.Connection = mySqlConnection              'Associate the Command Object with intended SQL Statement               mySqlCommand.CommandText = "Select * from Categories"              'Associate the DataAdapter Object with the Command Object              mySqlDataAdapter.SelectCommand = mySqlCommand              'Have the DataAdapter Object Execute the SQL Statement and              'store the result set in a DataSet DataTable named myCategories              mySqlDataAdapter.Fill(myDataset, "myCategories")              'Loop through the Dataset DataTable              'Write out one DataColumn per DataRow              For Each myDataRow In myDataset.Tables("myCategories").Rows                    Console.WriteLine(myDataRow("CategoryName").ToString())              Next                  'Close the Database Connection              mySqlConnection.Close()              mySqlConnection = Nothing              mySqlDataAdapter = Nothing              mySqlCommand = Nothing            End Sub            Public Sub UseSqlDataReader()              'Reference Data Provider Objects              Dim mySqlConnection As New System.Data.SqlClient.SqlConnection()              Dim mySqlDataReader As System.Data.SqlClient.SqlDataReader              Dim mySqlCommand As New System.Data.SqlClient.SqlCommand()                  'Connect to SQL Server Database using Connection String              mySqlConnection.ConnectionString = _              "user id=sa;pwd=;Database=northwind;Server=(LOCAL)"              mySqlConnection.Open()                  'Associate the Command Object with the Connection Object              mySqlCommand.Connection = mySqlConnection              'Associate the Command Object with intended SQL Statement               mySqlCommand.CommandText = "Select * from Categories"                  'Have the DataReader Object Execute the SQL Statement and              'store the result set in a DataReader Object              mySqlDataReader = mySqlCommand.ExecuteReader()                  'Loop through the DataReader Object, Advancing to each Record              'Write out one Column per Record              While mySqlDataReader.Read()              Console.WriteLine((mySqlDataReader.GetString(1)))           End While               'Close the DataReader          mySqlDataReader.Close()          'Close the Database Connection          mySqlConnection.Close()              mySqlConnection = Nothing          mySqlCommand = Nothing           End Sub     End Module 
end example
 

You will observe in the sample code in Listing 11-2 the ConnectionString of the System.Data.SqlClient.SqlConnection class has "user id," "pwd," and "Server" attributes. The values used to set these ConnectionString attributes may not be the same for every individual. They will certainly be different in a production application. The values that I used are for demonstration purposes only.

Tip  

Using the Enterprise Manager tool, check to make sure that the SQL Server database is started before you attempt to run the DataBaseAccessExampleVB sample project.

When you execute this sample project, you will see the contents of the CategoryName column from the Northwind.Categories table written to the console. In the next section, you will take a look at how the ADO.NET technology is implemented using COBOL .NET.

ADO.NET with COBOL .NET Sample Code

You will notice that the ADO.NET assembly (System.Data.Dll) reference is already added for this COBOL .NET project. The coding for ADO.NET data providers and .NET Datasets can take place. The COBOL .NET sample code in Listing 11-3 for the project DataBaseAccessExampleCobol provides a good example of how to use several ADO.NET classes to query a SQL Server database.

Listing 11-3: COBOL .NET Sample Code Demonstrating the Use of ADO.NET
start example
 000010* Sample Code using ADO.NET Technology  000020 IDENTIFICATION DIVISION. 000030 PROGRAM-ID. MAIN. 000040 ENVIRONMENT DIVISION. 000050 CONFIGURATION SECTION. 000060 REPOSITORY. 000070* .NET Framework Classes 000080    CLASS SqlConnection  AS "System.Data.SqlClient.SqlConnection" 000090    CLASS SqlDataAdapter As "System.Data.SqlClient.SqlDataAdapter" 000100    CLASS SqlCommand  As "System.Data.SqlClient.SqlCommand" 000110    CLASS SqlDataReader  As "System.Data.SqlClient.SqlDataReader" 000120    CLASS DataSet     As "System.Data.DataSet" 000130    CLASS DataTable   AS "System.Data.DataTable" 000140    CLASS DataRow     As "System.Data.DataRow" 000150    CLASS DataColumn  AS "System.Data.DataColumn" 000160    CLASS DataRowCollection  AS "System.Data.DataRowCollection" 000170    CLASS SystemType         AS "System.Type" 000180    CLASS DataColumnArray    AS "System.Data.DataColumn[]" 000190 000200    CLASS Sys-Integer        AS "System.Int32" 000210    CLASS Sys-String         AS "System.String" 000220    CLASS Sys-Objects        AS "System.Object[]" 000230    CLASS Sys-Object         AS "System.Object"  000240 000250* .NET Framework Properties  000260    PROPERTY PROP-ConnectionString AS "ConnectionString" 000270    PROPERTY PROP-Connection       AS "Connection" 000280    PROPERTY PROP-CommandText      AS "CommandText" 000290    PROPERTY PROP-SelectCommand    AS "SelectCommand" 000300    PROPERTY PROP-Columns          AS "Columns" 000310    PROPERTY PROP-Tables           AS "Tables" 000320    PROPERTY PROP-Rows             AS "Rows" 000330    PROPERTY PROP-DataType         AS "DataType" 000340    PROPERTY PROP-ColumnName       AS "ColumnName" 000350    PROPERTY PROP-Count            AS "Count" 000360    PROPERTY PROP-Item             AS "Item" 000370    PROPERTY PROP-ItemArray        AS "ItemArray" 000380    PROPERTY PROP-PrimaryKey       AS "PrimaryKey" 000390    PROPERTY PROP-Unique           AS "Unique". 000400* 000410 DATA DIVISION. 000420 WORKING-STORAGE SECTION. 000430 77 mySqlConnection  OBJECT REFERENCE SqlConnection. 000440 77 mySqlDataAdapter OBJECT REFERENCE SqlDataAdapter. 000450 77 mySqlCommand     OBJECT REFERENCE SqlCommand. 000460 77 mySqlDataReader  OBJECT REFERENCE SqlDataReader. 000470 77 myDataSet        OBJECT REFERENCE DataSet. 000480 77 myDataTable      OBJECT REFERENCE DataTable. 000490 77 myDataRow        OBJECT REFERENCE DataRow. 000500 77 myDataColumn     OBJECT REFERENCE DataColumn. 000510 77 myPrimaryKeyColumn OBJECT REFERENCE DataColumn. 000520 77 myPrimaryKeyColumns OBJECT REFERENCE DataColumnArray. 000530 77 myDataRowCollection OBJECT REFERENCE DataRowCollection. 000540 000550 000560 77 mySys-String  OBJECT REFERENCE Sys-String. 000570 77 mySys-Integer OBJECT REFERENCE Sys-Integer. 000580 77 mySys-Objects OBJECT REFERENCE Sys-Objects. 000590 77 mySys-Object  OBJECT REFERENCE Sys-Object. 000600 77 myDisplayString PIC x(30). 000610 77 myInt           PIC S9(9) COMP-5. 000620 77 myOtherInt      PIC S9(9) COMP-5. 000630 77 NOT-END-OF-READ PIC 1 USAGE BIT. 000640 01 NULL-X          PIC X(1). 000650 PROCEDURE DIVISION. 000660 000670    Perform 0000-OptionalPreTableBuild. 000680    Perform 1000-UseSqlDataAdapter. 000690    DISPLAY " " 000700    Perform 2000-UseSqlDataReader. 000710  000720    DISPLAY "Enter X and Press Enter to Exit.". 000730    ACCEPT NULL-X. 000740    Stop Run. 000750  000760************************************************ 000770 0000-OptionalPreTableBuild. 000780* It is possible to obtain the "schema" or table structure 000790* directly/automatically from the SQL Server Database 000800* This section is added for training purposes. 000810* The information found in this section would be critical 000820* in the case of building a disconnected .NET dataset 000830* that may have a non-SQL Server Data Source. 000840 000850* Create a new DataTable. 000860    INVOKE DataTable "NEW" USING BY VALUE "myCategories" 000870       RETURNING myDataTable. 000880 000890* Create 1st myDataColumn. 000900    INVOKE DataColumn "NEW" RETURNING myDataColumn. 000910    SET PROP-DataType OF myDataColumn TO 000920       SystemType::"GetType"("System.Int32"). 000930    SET PROP-ColumnName OF myDataColumn TO "CategoryID". 000940    SET PROP-Unique OF myDataColumn TO B"1". 000950    INVOKE PROP-Columns OF myDataTable "Add"  000960       USING BY VALUE myDataColumn. 000970  000980* Create 2nd myDataColumn. 000990    INVOKE DataColumn "NEW" RETURNING myDataColumn. 001000    SET PROP-DataType OF myDataColumn TO 001010       SystemType::"GetType"("System.String"). 001020    SET PROP-ColumnName OF myDataColumn TO "CategoryName". 001030    INVOKE PROP-Columns OF myDataTable "Add"  001040       USING BY VALUE myDataColumn. 001050  001060* Create 3rd myDataColumn. 001070    INVOKE DataColumn "NEW" RETURNING myDataColumn. 001080    SET PROP-DataType OF myDataColumn TO 001090       SystemType::"GetType"("System.String"). 001100    SET PROP-ColumnName OF myDataColumn TO "Description". 001110    INVOKE PROP-Columns OF myDataTable "Add"  001120       USING BY VALUE myDataColumn. 001130  001140* Create 4th myDataColumn. 001150    INVOKE DataColumn "NEW" RETURNING myDataColumn. 001160    SET PROP-DataType OF myDataColumn TO 001170          SystemType::"GetType"("System.Byte[]"). 001180    SET PROP-ColumnName OF myDataColumn TO "Picture". 001190    INVOKE PROP-Columns OF myDataTable "Add"  001200       USING BY VALUE myDataColumn. 001210  001220* Assign primary key column to CategoryID column. 001230    INVOKE DataColumnArray "NEW" USING BY VALUE 1 001240       RETURNING myPrimaryKeyColumns. 001250    INVOKE PROP-Columns OF myDataTable "get_Item"  001260       USING BY VALUE "CategoryID" 001270       RETURNING myPrimaryKeyColumn. 001280    INVOKE myPrimaryKeyColumns "Set"  001290       USING BY VALUE 0 myPrimaryKeyColumn. 001300    SET PROP-PrimaryKey OF myDataTable TO myPrimaryKeyColumns. 001310  001320* Reference the DataSet. 001330       INVOKE DataSet "NEW" RETURNING myDataSet. 001340* Associate the Table with the Dataset. 001350    INVOKE PROP-Tables OF myDataSet "Add"  001360       USING BY VALUE myDataTable. 001370 001380************************************************ 001390    1000-UseSqlDataAdapter. 001400  001410* Reference Data Provider Objects 001420    INVOKE SqlConnection "NEW" RETURNING mySqlConnection  001430    INVOKE SqlDataAdapter "NEW" RETURNING mySqlDataAdapter  001440    INVOKE SqlCommand "NEW" RETURNING mySqlCommand 001450  001460* Prepare to Connect to SQL Server Database 001470* using Connection String 001480    SET PROP-ConnectionString OF mySqlConnection TO 001490    "user id=sa;pwd=;Database=northwind;Server=(LOCAL)" 001500 001510* Associate the Command Object with the Connection Object 001520    SET PROP-Connection OF mySqlCommand TO mySqlConnection  001530* Associate the Command Object with intended SQL Statement 001540    SET PROP-CommandText OF mySqlCommand TO "Select * from Categories" 001550* Associate the DataAdapter Object with the Command Object 001560    SET PROP-SelectCommand OF mySqlDataAdapter TO mySqlCommand 001570 001580* Have the DataAdapter Object Execute the SQL Statement and 001590* store the result set in a DataSet DataTable named myCategories 001600    INVOKE mySqlDataAdapter "Fill"  001610       USING BY VALUE myDataSet, "myCategories" 001620  001630* Loop through the Dataset DataTable 001640* Write out one DataColumn per DataRow 001650    INVOKE PROP-Tables OF myDataSet "get_Item"  001660             USING BY VALUE "myCategories" 001670          RETURNING myDataTable  001680  001690    SET myDataRowCollection to PROP-Rows OF myDataTable 001700    SET mySys-Integer to PROP-Count of myDataRowCollection 001710  001720          SET myOtherInt TO mySys-Integer 001730          PERFORM VARYING myInt  001740                FROM 1 BY 1 UNTIL myInt > myOtherInt 001750  001760                INVOKE PROP-Rows OF myDataTable "Find"  001770                USING BY VALUE myInt RETURNING myDataRow 001780  001790                SET mySys-Objects TO PROP-ItemArray OF myDataRow 001800                INVOKE mySys-Objects "Get"  001810                  USING BY VALUE 1 RETURNING mySys-Object 001820                SET myDisplayString to mySys-Object::"ToString" () 001830                DISPLAY myDisplayString 001840               END-PERFORM. 001850 001860* Close the Database Connection 001870    INVOKE mySqlConnection "Close". 001880  001890    SET mySqlConnection TO NULL. 001900    SET mySqlDataAdapter TO NULL. 001910    SET mySqlCommand TO NULL. 001920    SET myDataTable TO NULL. 001930 001940************************************************ 001950 2000-UseSqlDataReader. 001960  001970* Reference Data Provider Objects 001980             INVOKE SqlConnection "NEW" RETURNING mySqlConnection  001990    INVOKE SqlCommand "NEW" RETURNING mySqlCommand 002000  002010* Connect to SQL Server Database using Connection String 002020    SET PROP-ConnectionString OF mySqlConnection TO 002030    "user id=sa;pwd=;Database=northwind;Server=(LOCAL)" 002040    INVOKE mySqlConnection "Open" 002050 002060* Associate the Command Object with the Connection Object 002070    SET PROP-Connection OF mySqlCommand TO mySqlConnection  002080* Associate the Command Object with intended SQL Statement 002090    SET PROP-CommandText OF mySqlCommand TO "Select * from Categories" 002100  002110* Have the DataReader Object Execute the SQL Statement and 002120* store the result set in a DataReader Object 002130 SET mySqlDataReader TO mySqlCommand::"ExecuteReader" ()  002140 002150* Loop through the DataReader Object, Advancing to each Record 002160* Write out one Column per Record 002170        SET NOT-END-OF-READ TO mySqlDataReader::"Read" ()  002180        PERFORM UNTIL NOT-END-OF-READ = B'0' 002190                SET myDisplayString to mySqlDataReader::"GetString" (1) 002200                DISPLAY myDisplayString 002210               SET NOT-END-OF-READ TO mySqlDataReader::"Read" ()  002220            END-PERFORM. 002230 002240* Close the DataReader 002250    INVOKE mySqlDataReader "Close". 002260* Close the Database Connection 002270    INVOKE mySqlConnection "Close". 002280  002290    SET mySqlConnection TO NULL. 002300    SET mySqlCommand TO NULL. 002310 002320 END PROGRAM MAIN. 
end example
 
Note  

The values used to set the ConnectionString attributes ("user id," "pwd," and "Server") of the System.Data.SqlClient.SqlConnection class may not be the same for each individual. The appropriate values for your installation may differ .

As you can see in the COBOL .NET sample code in Listing 11-3, working with the ADO.NET classes is rather straightforward. Depending on your data needs, you can choose to use either the DataReader class or the DataAdapter class ”whichever is more appropriate. Additionally, you may build your DataTable manually (as shown in Listing 11-3) or obtain the schema/structure directly from the SQL Server database.

[8] As of .NET version 1.0, the Oracle .NET Data Provider is available as a free download from Microsoft's Web site. I expect that this .NET Data Provider will eventually be bundled and shipped with the other .NET Data Providers. Likewise, there's an Open Database Connectivity (ODBC) Data Provider available as a separate download from Microsoft's Web site. Optionally, you're free to write a .NET data provider for any data source.

[9] There's another "Execute" method offered by the SQLCommand class named ExecuteXmlReader. I further discuss this method in Chapter 12.

[10] Wouldn't it be great if you always had a complete requirements document on which to base your software development?

[11] You'll come across the term "cursor" in the SQL Server context. Be aware that in legacy ADO technology, there was a server-side cursor and a client-side cursor. With ADO, a server-side cursor was used when "scrolling and updating" was desired. With ADO.NET, the trend has been to use the new .NET Dataset in cases where a legacy ADO server-side cursor would have been used.

[12] I further discuss Web services in Chapter 13.




COBOL and Visual Basic on .NET
COBOL and Visual Basic on .NET: A Guide for the Reformed Mainframe Programmer
ISBN: 1590590481
EAN: 2147483647
Year: 2003
Pages: 204

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net