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).
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.
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.
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. |
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.
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.
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.
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.
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 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.
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):
.NET data providers
.NET Dataset
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. |
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. |
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.
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.
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.
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.
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. |
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.) |
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. |
'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
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.
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.
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.
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.