Large database systems, such as SQL Server and Oracle, are standalone “servers” (hence the “SQL Server" name) that interact with client tools and applications only indirectly. These systems generally accept network connections from clients through a TCP/IP port or similar connection. Once authenticated, the client makes all its requests through this connection before disconnecting from the system.

Back in the early 1990s, Microsoft implemented ODBC (based on other existing standards) as a common system through which clients would connect to database servers, as well as other simpler data sources. Clients no longer had to worry about all the networking protocols needed to talk with a database; all that code was included in the ODBC driver.

Microsoft later released a similar data connection system called OLE DB, based on ActiveX technology. OLE DB drivers for common systems soon appeared, although you could still get to ODBC resources through a generic ODBC driver built into OLE DB.

In .NET, both ODBC and OLE DB are replaced by providers, libraries of code that provide all the communication between the database and your application. Providers are an integral part of ADO.NET, and you will have to use them to get to your databases. Fortunately, providers exist for the main database systems, and an “OLE DB provider” exists for systems without their own providers.

Four primary objects make up the programmer’s view of the provider:

Using these objects is a little involved, but not hard to understand. To connect to a typical relational database, such as SQL Server, and process data, follow these steps:

Although the .NET Framework includes data providers for a few different data systems, the remainder of this chapter’s discussion focuses only on the SQL Server provider, exposed through the System.Data.SqlClient namespace.

If you are going to do more than just quickly scan the data that comes back from a DataReader query, you will probably use a data set to store, manage, and optionally update your data. Each data set provides a generic disconnected view of data, whether it is data from a provider, or data that you build through code. Although each provider is tied to a specific database platform (such as Oracle) or communication standard (such as OLE DB), the objects in the data set realm are generic, and can interact with any of the platform-specific providers.

Three main objects make up the world of data sets:

Although data sets are most often used with providers, you can use them independently to build your own in-memory collection of tables and relationships. This is similar to the “client-side record sets” that you could build with pre-.NET ADO objects, although the features included with ADO.NET make data sets much more powerful than the older record sets.

I mentioned something called MARS in Table 10-1. MARS stands for “Multiple Active Result Sets.” Normally, a single Connection object allows only a single DataReader to be in use at any given moment. This limitation is bidirectional. If you are scanning a DataReader via a SELECT statement, you cannot issue INSERT, UPDATE, or DELETE statements on that same connection until you close the DataReader.

With the introduction of MARS, a single connection can now handle multiple data transmission activities in either direction. SQL Server added MARS support with its 2005 release; Oracle has supported MARS-like features since the initial .NET release.

MARS connections seem like a feature you would always want to enable. But they do add additional overhead to your application that can slow it down. Also, MARS does not always mix well with multithreaded applications.

Start up a new Windows Forms project in Visual Studio—just a plain Windows Forms project, not one of the Library-specific projects. Selecting the Data → Show Data Sources menu command brings up the Data Sources panel, as shown in Figure 10-2.

Figure 10-2. Where are the data sources?

New projects don’t include any data sources by default, so we need to add one. Click on the Add New Data Source link in the Data Sources panel. The Data Source Configuration Wizard guides you through the data source creation process:

Figure 10-4. The final step is to select the Activity table

Check out the Data Sources panel shown in Figure 10-5. It includes the new LibraryDataSet data source with its link to the Activity table.

Figure 10-5. Finally, a real data source

So, what is this data source anyway? It is simply a link to some portion of your database, wrapped up in a typical .NET object. Now that it’s part of your project, you can use it to access the data in the Activity table through your project’s code, or by drag-and-drop. In the Data Sources panel, you will find that the “Activity” entry is actually a drop-down list. Select Details from the list, as I’ve done in Figure 10-6. (The surface of Form1 must be displayed for this to work.)

Figure 10-6. Select the Details view instead of DataGrid

Finally, drag-and-drop the “Activity” entry onto the surface of Form1. When you let go, Visual Studio will add a set of controls to the form, plus a few more non-user-interface controls just below the form (see Figure 10-7).

Figure 10-7. A complete program without writing a single line of code

By just dragging-and-dropping, Visual Studio added all the necessary controls and links to turn your form into a turbo-charged Activity table editor. Try it now by pressing the F5 key. In the running program, you can use the Microsoft Access-style record access “VCR” control to move between the records in the Activity table. You can also modify the values of each record, add new records, or delete existing records (but please restore things back to their original state when you are done; we’ll need all the original records later). Talk about power! Talk about simplicity! Talk about unemployment lines! Who needs highly paid programmers like us when Visual Studio can do this for you?

In reality, Visual Studio isn’t doing all that much. It’s using a feature called “data binding” to link the on-form fields with the data source, the Library database’s Activity table. Data binding is a feature built into Windows Forms controls that allows them to automatically display and edit values in an associated data source, such as a database. It’s all sorted out through the properties of the control.

Select the FullNameTextBox control added to this project’s form, and then examine its properties. Right at the top is a property section named “(DataBindings).” Its Text subproperty contains “ActivityBindingSource - FullName,” a reference to the ActivityBindingSource non-user-interface control also added by Visual Studio. ActivityBindingSource, in turn, contains a reference to the LibraryDataSet object, the data source we created earlier. That data source links to SQL Server, to the Library database, and finally to the Activity table and its FullName field. Piece of cake!

If you count up all the objects involved in this data-binding relationship, you come up with something like 5,283 distinct objects. It’s no wonder that Visual Studio did so much of the work for you. Data binding provides a lot of convenience, but it also takes away a lot of your control as a developer. Although there are properties and events that let you manage aspects of the data binding and its update process, most of the essential code is hidden away inside the data-binding portions of .NET. You may not touch, taste, fold, spindle, or mutilate it, and that’s just bad. A quick look at one of my core programming beliefs says it all: good software includes maximum control for the developer, and minimum control for the user.

Part of your job as a developer is to provide a highly scripted environment for the user to access important data. This requires that you have control over the user’s experience through your source code. Certainly, you will defer much of that control to others when you use any third-party supplied tools. As long as those tools allow you to control the user experience to your level of satisfaction, that’s great. But I’ve always been disappointed with data binding, except when implementing a read-only display of data from the database. Similar features were in Visual Basic long before .NET arrived, and they have always made it difficult for the developer to control the various data interactions on the form.

Fortunately, if you eschew the data-binding features, Visual Basic will pass to you the responsibility of managing all interactions between the database and the user.

The first step on the road to the data-controlling lifestyle is to connect to the database using a connection string. If you have used ADO, you are already familiar with the connection strings used in ADO.NET, for they are generally the same. You probably also know that it is through connection strings that Microsoft keeps a tight rein on Windows developers. It’s not that they are complex; they are nothing more than strings of semicolon-separated parameters. But the parameters to include, and their exact format, are the stuff of legend. The MSDN documentation included with Visual Studio does provide some examples of connection strings, but not much detail. A third-party resource, http://www.connectionstrings.com, also provides numerous examples of valid connection string formats.

The connection string we will use to connect to the Library database, fortunately, isn’t overly complex. If you use your Microsoft Windows login to connect to the database, the following string will meet your needs (as one unbroken line):

Data Source=instance_name;Initial Catalog=Library;
Integrated Security=true

where instance_name is replaced by the name of your SQL Server instance or data source. The connection string we built visually earlier used “MYSYSTEMSQLEXPRESS” for its data source name.

To use SQL Server user IDs and passwords, try this format:

Data Source=instance_name;Initial Catalog=Library;
User ID=sa;Password=xyz

Of course, replace the user ID (sa) and password (xyz) with your own settings. If you want to include MARS support in your connection, add another semicolon-delimited component:

MultipleActiveResultSets=true

Use the connection string to create a SqlConnection object, and then open the connection. Create a brand-new Windows Forms application in Visual Studio. Add a Button control to the surface of Form1. Double-click the button to access its Click event handler. Then add the following code to that handler:

' ----- Assumes:
'         Imports System.Data
Dim libraryDB As New SqlClient.SqlConnection( _
   "Data Source=MYSYSTEMSQLEXPRESS;" & _
   "Initial Catalog=Library;Integrated Security=true")
libraryDB.Open(  )

Make sure you replace “MYSYSTEM” with your own system’s name. This entire block of code sure seems a lot easier to me than those 10 or 15 steps you had to follow earlier when setting up the connection through Visual Studio.

Once the connection is open, you can issue SELECT, INSERT, UPDATE, DELETE, or any other data manipulation language (DML) or data definition language (DDL) statement accepted by the database. A SqlCommand object prepares your SQL statement for use by the open connection. Here’s a statement that returns the description for entry number 1 in the Activity table:

SELECT FullName FROM Activity WHERE ID = 1

Creating a SqlCommand object that wraps around this statement is easy. The constructor for the SqlCommand object takes a SQL statement, plus a SqlConnection object. Add the following code to the end of your Button1_Click event handler:

Dim sqlStatement As New SqlClient.SqlCommand( _
   "SELECT FullName FROM Activity WHERE ID = 1", libraryDB)

The only thing left to do is to pass the SQL statement to the database, via the connection, and retrieve the results as a SqlDataReader object. Once we get the data, process each record using that object’s Read method. You access individual fields by name through the default Item collection. Add this additional code to the end of your Button1_Click event handler:

Dim sqlResults As SqlClient.SqlDataReader = _
   sqlStatement.ExecuteReader(  )
sqlResults.Read(  )
MsgBox(CStr(sqlResults.Item("FullName")))

' ----- Since Item is the default property, this works too...
' MsgBox(CStr(sqlResults("FullName")))

' ----- This shortened syntax also works...
' MsgBox(CStr(sqlResults!FullName))

Taking all these blocks of code together displays the message shown in Figure 10-8.

Figure 10-8. Basic data retrieved from a database

When you’re finished, make sure you close all the connections you opened. Add this last bit of code to the end of your Button1_Click event handler:

sqlResults.Close(  )
libraryDB.Close(  )

Making changes to database tables is coded just like data retrieval, but no SqlDataReader is needed. Instead of using the ExecuteReader method, use the ExecuteNonQuery method, which returns no results.

Dim sqlStatement As New SqlClient.SqlCommand( _
   "UPDATE Activity SET FullName = 'Sleeps all day'" & _
   " WHERE ID = 1", libraryDB)
sqlStatement.ExecuteNonQuery(  )

SQL Server 2005 has a convenient feature that will return a single field from a new record created via an INSERT statement. If you look back at the Library Project’s database design, you will see that the ID fields in many of the tables are generated automatically. Traditionally, if you wanted to immediately retrieve the ID field for a new record, you first had to INSERT the record, and then perform a separate SELECT statement, returning the new record’s ID field.

INSERT INTO CodeSeries (FullName)
   VALUES ('Children''s Books')

SELECT ID FROM CodeSeries
  WHERE FullName = 'Children''s Books'

SQL Server’s OUTPUT INSERTED clause combines both of these statements into a single action.

INSERT INTO CodeSeries (FullName)
   OUTPUT INSERTED.ID
   VALUES ('Children''s Books')

When the INSERT is complete, SQL Server returns the ID field as a result set, just as though you had issued a separate SELECT statement. The SqlCommand’s ExecuteScalar method is a simple way to retrieve a single value from a SQL query.

sqlStatement = New SqlClient.SqlCommand( _
   "INSERT INTO CodeSeries (FullName) " & _
   "OUTPUT INSERTED.ID VALUES ('Children''s Books')", _
   libraryDB)
Dim newID As Integer = CInt(sqlStatement.ExecuteScalar(  ))

The Library Project code has references to several of the important .NET namespaces, such as System, System.Windows.Forms, and Microsoft.VisualBasic. However, it doesn’t yet reference any of the ADO.NET namespaces. (Recall that “referencing” means accessing a .NET DLL in a project and using its types in your code.) Before using them in code, we need to create references to them. This is done through the project properties window, on the References tab. You’ll see a list of those assemblies already referenced by the application (see Figure 10-9).

Figure 10-9. References included in the Library Project

To add new references, click the Add button just below the list, and select Reference if prompted for the type of reference to add. On the Add Reference form, the .NET tab should already be active. It’s pretty amazing to see just how many .NET assemblies are installed on your system already. But don’t just sit there gawking: select both System.Data and System.Xml from the component list, and then click the OK button. The list of references in the project properties should now include both of the selected namespace libraries.

We can now refer to classes in the System.Data namespace directly. But typing “System.Data” before each use of a data-related class will get tedious. We could sprinkle “Imports System.Data” statements throughout the files in the project, but Visual Studio provides a more centralized solution. Since you still have the References tab open, look down to the “Imported namespaces” section. The large checklist indicates which namespaces should be automatically imported throughout your application. These namespaces don’t require separate Imports statements in your code, but your source code acts as though you had added them anyway. Go ahead and select the checkbox next to the System.Data entry in this list. Then close the project properties window.

Most of the new code for this chapter appears in the General.vb file, so open it now. We will use two project-level (global) variables to manage the primary database connection to the Library database. The first variable, LibraryDB, is a SqlConnection object that uses our connection string for the Library database. A related object, HoldTransaction, will hold a SqlTransaction object when a transaction is in effect. Add these two lines to the General module. I put them just before the existing CenterText method.

Public LibraryDB As System.Data.SqlClient.SqlConnection
Public HoldTransaction As System.Data.SqlClient.SqlTransaction

Since the Library Project will depend so much on the database, we will build the SqlConnection object when first starting up the application.

The ConnectDatabase procedure contains all the code needed to create this object. For now, I’ve just hardcoded the connection string into the routine. In a later chapter, we’ll include that connection information as part of a configuration system. Add the following routine to your General module. Make sure you change the reference to “MYSYSTEM” to whatever is needed on your own system.

Public Function ConnectDatabase(  ) As Boolean
   ' ----- Connect to the database. Return True on success.
   Dim connectionString As String

   ' ----- Initialize.
   HoldTransaction = Nothing

   ' ----- Build the connection string.
   ' !!! WARNING: Hardcoded for now.
   connectionString = "Data Source=MYSYSTEMSQLEXPRESS;" & _
      "Initial Catalog=Library;Integrated Security=true"

   ' ----- Attempt to open the database.
   Try
      LibraryDB = New SqlClient.SqlConnection(connectionString)
      LibraryDB.Open(  )
   Catch ex As Exception
      GeneralError("ConnectDatabase", ex)
      Return False
   End Try

   ' ----- Success.
   Return True
End Function

This project’s “main” routine is actually the MyApplication_Startup application event, from the ApplicationEvents.vb source code file. (That’s one that you will have to use the Show All Files button to see.) To build the connection object at startup, add the following code to the end of that event handler.

' ----- Connect to the database.
If (ConnectDatabase(  ) = False) Then
   Me.HideSplashScreen(  )
   e.Cancel = True
   Return
End If

When the user exits the Library application, the code will call the CleanUpProgram method to properly dispose of the connection object. Return to the General.vb module, and add that method.

Public Sub CleanUpProgram(  )
   ' ----- Prepare to exit the program.
   On Error Resume Next
   LibraryDB.Close(  )
End Sub

To simplify things, we’ll call this routine from the application’s MyApplication_Shutdown event handler, back in the ApplicationEvents.vb file.

CleanUpProgram(  )

Now that the database connection is established, it’s time to do something with it. The first four centralized routines implement much of the code we discussed earlier: the creation of data readers and tables, and the processing of general SQL code. Add them to the General module.

This snippet adds the following seven routines:

None of these routines include their own error-processing code; they either suppress errors with an “On Error Resume Next” statement, or rely on the calling routine to trap errors. This lets the calling routine take specific action based on the type of error generated. All of these routines are pretty similar to one another. Here is the code for CreateReader; one interesting part is the use of the HoldTransaction object when a transaction is in effect.

Public Function CreateReader(ByVal sqlText As String) _
      As SqlClient.SqlDataReader
   ' ----- Given a SQL statement, return a data reader.
   Dim dbCommand As SqlClient.SqlCommand
   Dim dbScan As SqlClient.SqlDataReader

   ' ----- Try to run the statement. Note that no error
   '       trapping is done here. It is up to the calling
   '       routine to set up error checking.
   dbCommand = New SqlClient.SqlCommand(sqlText, LibraryDB)
   If Not (HoldTransaction Is Nothing) Then _
dbCommand.Transaction = HoldTransaction
   dbScan = dbCommand.ExecuteReader(  )
   dbCommand = Nothing
   Return dbScan
End Function

Building SQL statements by hand involves a lot of string manipulation, plus conditional processing for those times when data may be missing. For instance, if you want to store a text value in the database, you have to prepare it for use by a SQL statement (special processing for single quote marks), but if the text value is zero-length, you pass the word NULL in the statement instead. All of this data preparation can clog up your code, so why not centralize it? The eight routines in this section either prepare data for use in SQL statements, or adjust retrieved data for use in the application.

Here is the code for the DBText routine. Strings in SQL statements must be surrounded by single quotes, and any embedded single quotes must be doubled.

Public Function DBText(ByVal origText As String) As String
   ' ----- Prepare a string for insertion in a SQL statement.
   If (Trim(origText) = "") Then
      Return "NULL"
   Else
      Return "'" & Replace(origText, "'", "''") & "'"
   End If
End Function

The last blocks of code support the quick update and retrieval of system-wide configuration values stored in the SystemValue table of the Library database. The GetSystemValue routine returns the current setting of a configuration value when supplied with the value name. SetSystemValue updates (or adds, if needed) a named configuration value. Both of these routines appear in the General module.

Public Function GetSystemValue( _
      ByVal valueName As String) As String
   ' ----- Return the data portion of a system value
   '       name-data pair.
   Dim sqlText As String
   Dim returnValue As String

   ' ----- Retrieve the value.
   returnValue = ""
   sqlText = "SELECT ValueData FROM SystemValue WHERE " & _
      "UPPER(ValueName) = " & DBText(UCase(valueName))
   Try
      returnValue = DBGetText(ExecuteSQLReturn(sqlText))
   Catch ex As Exception
      GeneralError("GetSystemValue", ex)
   End Try
   Return returnValue
End Function

Public Sub SetSystemValue(ByVal valueName As String, _
      ByVal valueData As String)
   ' ----- Update a record in the SystemValue table.
   Dim sqlText As String

   Try
      ' ----- See if the entry already exists.
      sqlText = "SELECT COUNT(*) FROM SystemValue WHERE " & _
         "UPPER(ValueName) = " & DBText(UCase(valueName))
      If (CInt(ExecuteSQLReturn(sqlText)) > 0) Then
         ' ----- Value already exists.
         sqlText = "UPDATE SystemValue " & _
            "SET ValueData = " & DBText(valueData) & _
            " WHERE UPPER(ValueName) = " & _
            DBText(UCase(valueName))
      Else
         ' ----- Need to create value.
         sqlText = "INSERT INTO SystemValue " & _
            (ValueName, ValueData) VALUES (" & _
            DBText(valueName) & ", " & _
            DBText(valueData) & ")"
      End If

      ' ----- Update the value.
      ExecuteSQL(sqlText)
   Catch ex As System.Exception
      GeneralError("SetSystemValue", ex)
   End Try
End Sub

The GetSystemValue routine is clear. It simply retrieves a single value from the database. SetSystemValue has to first check whether the configuration value to update already exists in the database. If it does, it modifies the records. Otherwise, it adds a full new record. To determine whether the record exists, it requests a count of records matching the system value name. It queries the database through our new ExecuteSqlReturn method, which returns a single value from a query. In this case, the value is the count of the matching records.

sqlText = "SELECT COUNT(*) FROM SystemValue WHERE " & _
   "UPPER(ValueName) = " & DBText(UCase(valueName))
If (CInt(ExecuteSQLReturn(sqlText)) > 0) Then

Using the GetSystemValue routine is easy, so let’s use it right now. Go back to the MyApplication_Startup event handler in ApplicationEvents.vb, and add the following code to the end of the routine.

' ----- Check the database version.
Dim productionDBVersion As String
productionDBVersion = Trim(GetSystemValue("DatabaseVersion"))
If (CInt(Val(productionDBVersion)) <> UseDBVersion) Then
   MsgBox("The program cannot continue due to an " & _
      "incompatible database. The current database " & _
      "version is '" & productionDBVersion & _
      "'. The application version is '" & _
      UseDBVersion & "'.", _
      MsgBoxStyle.OkOnly Or MsgBoxStyle.Critical, ProgramTitle)
   CleanUpProgram(  )
   Me.HideSplashScreen(  )
   e.Cancel = True
   Return
End If

Once in a while, I found it necessary to modify the structure of a database to such an extent that older versions of an application either crashed, or would cause major data headaches. To prevent this, I added a database version setting, DatabaseVersion, and use this code block to test against it. If the program doesn’t match the expected database version, it will refuse to run.

Now that we have some basic database access tools, we’re ready to start adding some real data interaction code to the Library application.