Project

It's likely that more than 50 percent of the code in the Library Project will directly involve database access, or manipulation of the data retrieved through ADO.NET. Constantly creating new Command and DataReader objects, while simple, is pretty hard on the fingers. Because so much of the code is repetitive, the code in this chapter's project will try to centralize some of that basic, boilerplate code.

Project Access

Load the "Chapter 10 (Before) Code" project, either through the New Project templates, or by accessing the project directly from the installation directory. To see the code in its final form, load "Chapter 10 (After) Code" instead.

Reference the Data Namespaces

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 if 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.

Insert Snippet

Insert Chapter 10, Snippet Item 1.

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

Connecting to the Database

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

Not-Really-Best Practices Warning

Maintaining the connection throughout the application goes against the advice I provided earlier that database connections should be short-lived. However, to keep the code as simple as possible for purposes of tutorial demonstration, I have chosen this approach. Also, because the Library Project is designed for a small installation base, it does not have the requirement of being highly scalable.

The ConnectDatabase procedure contains all the code needed to create this object. For now, I've just hard-coded 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.

Insert Snippet

Insert Chapter 10, Snippet Item 2.

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=MYSYSTEM\SQLEXPRESS;" &_       "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.

Insert Snippet

Insert Chapter 10, Snippet Item 3.

' ----- 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 the following method.

Insert Snippet

Insert Chapter 10, Snippet Item 4.

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.

Insert Snippet

Insert Chapter 10, Snippet Item 5.

CleanUpProgram() 

Interacting with the Database

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.

Insert Snippet

Insert Chapter 10, Snippet Item 6.

This snippet adds the following seven routines.

• Function CreateDataTable. Given a SQL statement, retrieve its results from the database, and put it all in a DataTable object. A SqlDataAdapter connects the SqlDataReader with the DataTable.

• Function CreateReader. Given a SQL statement, retrieve its results from the database, returning the associated SqlDataReader object.

• Sub ExecuteSQL. Sends a SQL statement to the database for processing.

• Function ExecuteSQLReturn. Sends a SQL statement to the database for processing, returning a single result value.

• Sub TransactionBegin. Begins a new transaction.

• Sub TransactionCommit. Commits the transaction, making all changes permanent.

• Sub TransactionRollback. Rolls back the transaction, undoing any changes that were part of the transaction.

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 each other. 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 

Processing Data Values

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.

Insert Snippet

Insert Chapter 10, Snippet Item 7.

• DBCombo. Takes the numeric code associated with a selected item in a ComboBox control and returns it as a string. If no item is selected, or if the value is 1, the routine returns "NULL."

• DBDate(String). Given a string containing a formatted date, returns a date ready for use in a SQL statement.

• DBDate(Date). Given a true date value, returns a string date ready for use in a SQL statement.

• DBGetDecimal. Returns a decimal number from a result set, even if the field contains a NULL value.

• DBGetInteger. Returns an integer number from a result set, even if the field contains a NULL value.

• DBGetText. Returns a string from a result set, even if the field contains a NULL value.

• DBNum. Prepares a number for use in a SQL statement.

• DBText. Prepares a string for use in a SQL statement.

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 

System-Level Configuration

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.

Insert Snippet

Insert Chapter 10, Snippet Item 8.

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. The 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.

Insert Snippet

Insert Chapter 10, Snippet Item 9.

' ----- 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.