Programming Database Apps with ADO.NET

Programming Database Applications with ADO.NET

ADO.NET provides a number of ways of interacting with a database within a Visual Basic program. This article focuses on a subset of ADO.NET that enables you to perform the basic database operations of selecting, inserting, updating, and deleting data. Code for using MS-Access and SQL Server databases will be presented.

NOTE: When building a VB database application with MS-Access (Jet 4.0) as the backend database on a 64-bit version of Windows, you must set the Target CPU to x86. How to do this is shown at the end of this document.

If your application will be interacting with a database, you must first add references to the appropriate namespaces. To do so, first open the Properties page for the project (from the "Project" menu, as shown in the screen shot on the right):

On the "References" tab, in the "Imported namespaces" list:

If using MS-Access, check System.Data.OleDb:

If using SQL Server, check System.Data.SqlClient:

You will need to have a connection string for your database defined in your program. This can be done with a string variable, typically a Public variable in a module.

The following defines a connection string for an Access database, in Access 2000 format, named "cust.mdb", residing in the same directory as the executable program:

Public DB_CONN_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _

& My.Application.Info.DirectoryPath & "\cust.mdb"

The following defines a connection string for a local SQL Server database named "Customers". In this case, the name of the local server is the computer name, which in this example is "DELLXPS8700":

Public DB_CONN_STRING As String = "user id=sa;password=;Integrated Security=True;" _

& "data source=DELLXPS8700;initial catalog=Customers"

For all of the SQL operations discussed here (SELECT, INSERT, UPDATE, and DELETE), you will need a Connection and a Command object. Additionally, for the SELECT operation, you will need a DataReader object.

For a SELECT operation, you open the DataReader using the ExecuteReader method of the Command object and loop through the resultset using the Read method of the DataReader.

For INSERT, UPDATE, and DELETE operations, you use the ExecuteNonQuery method of the Command object to run the SQL.

For an Access database, the Connection, Command, and DataReader objects are OleDbConnection, OleDbCommand, and OleDbDataReader, respectively.

For a SQL Server database, the Connection, Command, and DataReader objects are SqlDbConnection, SqlDbCommand, and SqlDbDataReader, respectively.

Selecting Data and Processing the Resultset

The following code shows how to perform a SELECT operation and process the resultset using an Access database:

Dim objConn As OleDbConnection

Dim objCmd As OleDbCommand

Dim objReader As OleDbDataReader

Dim strSQL As String

strSQL = "SELECT FirstName" _

& " , LastName" _

& " , Address" _

& " , City" _

& " , State" _

& " , Zip" _

& " , PhoneNumber" _

& " , CustID" _

& " FROM Customer " _

& " ORDER BY LastName" _

& " , FirstName"

objConn = New OleDbConnection(DB_CONN_STRING)

objCmd = New OleDbCommand(strSQL, objConn)

objCmd.CommandType = CommandType.Text

objCmd.Connection.Open()

objReader = objCmd.ExecuteReader

Do While objReader.Read

' do whatever processing needs to be done with the current record

Debug.Print(objReader("FirstName") & " " & objReader("LastName"))

Loop

objReader.Close()

objConn.Close()

The following code shows how to perform a SELECT operation and process the resultset using a SQL Server database. Note that the only difference here is the prefix on the Connection, Command, and DataReader objects - it uses the prefix "Sql" instead of "OleDb". The only other difference (not an issue with the code below) could be the use of DBMS-specific functions or syntax in the SELECT statement itself (for example, if a date literal was being referenced, you would have to delimit it with single quotes (') in SQL Server or pound signs (#) in Access).

Dim objConn As SqlConnection

Dim objCmd As SqlCommand

Dim objReader As SqlDataReader

Dim strSQL As String

strSQL = "SELECT FirstName" _

& " , LastName" _

& " , Address" _

& " , City" _

& " , State" _

& " , Zip" _

& " , PhoneNumber" _

& " , CustID" _

& " FROM Customer " _

& " ORDER BY LastName" _

& " , FirstName"

objConn = New SqlConnection(DB_CONN_STRING)

objCmd = New SqlCommand(strSQL, objConn)

objCmd.CommandType = CommandType.Text

objCmd.Connection.Open()

objReader = objCmd.ExecuteReader

Do While objReader.Read

' do whatever processing needs to be done with the current record

Debug.Print(objReader("FirstName") & " " & objReader("LastName"))

Loop

objReader.Close()

objConn.Close()

Referencing Columns from the DataReader Resultset

Note that the syntax to reference a field (column) from the DataReader is objReader("field_name"). Alternatively, an index can be used: objReader(0) would refer to FirstName, objReader(1) would refer to LastName, and so on.

Handling Nulls

To prevent "invalid use of null" errors, the IsDBNull function can be used to test a database field for a null value:

If IsDBNull(objReader("some_field")) Then ...

This could be wrapped in a function that will return a zero-length string if the database field is null. Below, a function named "ReplaceDBNull" has been written to accomplish this:

Public Function ReplaceDBNull(ByVal pobjDBField As Object) As String

If IsDBNull(pobjDBField) Then

Return ""

Else

Return pobjDBField.ToString

End If

End Function

This function could then be used whenever you refer to a database field elsewhere in your code:

Debug.Print(ReplaceDBNull(objReader("FirstName")))

Using HasRows

Another useful property of the DataReader object is HasRows. HasRows is a Boolean property indicating whether or not the SELECT statement returned any rows. You can test this in code and take action accordingly. Example:

strSQL = "SELECT LastName FROM Customer WHERE CustID = 12345"

...

If objReader.HasRows Then

objReader.Read

MsgBox("Customer's last name is " & objReader("LastName"))

Else

MsgBox("Customer not found.")

End If

...

Inserting, Updating, and Deleting Data

The INSERT, UPDATE, and DELETE database operations can all be handled the same way once the SQL statement has been set. As in the previous example, a string variable named strSQL is declared:

Dim strSQL As String

Assign the appropriate SQL statement to the variable:

strSQL = "INSERT INTO Customer( CustID"

strSQL &= " , FirstName"

strSQL &= " , LastName"

strSQL &= " , Address"

strSQL &= " , City"

strSQL &= " , State"

strSQL &= " , Zip"

strSQL &= " , PhoneNumber"

strSQL &= ") VALUES ("

strSQL &= intIDField

strSQL &= ", '" & Replace(txtFirst.Text, "'", "''") & "'"

strSQL &= ", '" & Replace(txtLast.Text, "'", "''") & "'"

strSQL &= ", '" & Replace(txtAddr.Text, "'", "''") & "'"

strSQL &= ", '" & Replace(txtCity.Text, "'", "''") & "'"

strSQL &= ", '" & txtState.Text & "'"

strSQL &= ", '" & txtZip.Text & "'"

strSQL &= ", '" & strPhone & "'"

strSQL &= ")"

- or -

strSQL = "UPDATE Customer SET "

strSQL &= " FirstName = '" & Replace(txtFirst.Text, "'", "''") & "'"

strSQL &= ", LastName = '" & Replace(txtLast.Text, "'", "''") & "'"

strSQL &= ", Address = '" & Replace(txtAddr.Text, "'", "''") & "'"

strSQL &= ", City = '" & Replace(txtCity.Text, "'", "''") & "'"

strSQL &= ", State = '" & txtState.Text & "'"

strSQL &= ", Zip = '" & txtZip.Text & "'"

strSQL &= ", PhoneNumber = '" & strPhone & "'"

strSQL &= " WHERE CustID = " & intIDField

- or -

strSQL = "DELETE FROM Customer WHERE CustID = " & intCustID

Then use the OleDb... or Sql... Connection and Command objects to execute the SQL (using the ExecuteNonQuery method of the Command object):

MS-Access

SQL Server

Dim objConn As New OleDbConnection(DB_CONN_STRING)

Dim objCommand As New OleDbCommand(strSQL, objConn)

objCommand.CommandType = CommandType.Text

objCommand.Connection.Open()

objCommand.ExecuteNonQuery()

objConn.Close()

Dim objConn As New SqlConnection(DB_CONN_STRING)

Dim objCommand As New SqlCommand(strSQL, objConn)

objCommand.CommandType = CommandType.Text

objCommand.Connection.Open()

objCommand.ExecuteNonQuery()

objConn.Close()

Handling Data with Embedded Single Quotes

You'll notice that in the INSERT and UPDATE statements above, for fields that could contain single qutoes, the Replace function is being used to replace occurrences of one single quote with two single quotes. This is necessary due to the fact that the delimiter for text (character) fields is the single quote; so if the data itself contains a single quote, it must be "escaped" by using two single quotes.

For example, assume we have the statement:

strSQL = "UPDATE Customer SET LastName = '" & txtLast.Text & "' WHERE CustID = " & intIDField

Assume that the TextBox named txtLast contained O'Brien and that the value of the variable intIDField was 123. The effective SQL statement would be:

UPDATE Customer SET LastName = 'O'Brien' WHERE CustID = 123

When the DBMS attempts to execute this, the delimiters would not be interpreted correctly and an error would occur. The fix would be ensure we replace any occurrence of single quotes in the last name with two single quotes:

strSQL = "UPDATE Customer SET LastName = '" & Replace(txtLast.Text, "'", "''") & "' WHERE CustID = " & intIDField

Resulting in the effective SQL statement:

UPDATE Customer SET LastName = 'O''Brien' WHERE CustID = 123

which would execute correctly.

Affected Records

The ExecuteNonQuery method of the Command object returns an integer value that represents the number of records affected by the query. If desired, you can capture this and report the value back to the user.

Example:

strSQL = "UPDATE Customer SET ... "

Dim intRecordsAffected As Integer

...

Dim objConn As New OleDbConnection(DB_CONN_STRING)

Dim objCommand As New OleDbCommand(strSQL, objConn)

objCommand.CommandType = CommandType.Text

objCommand.Connection.Open()

intRecordsAffected = objCommand.ExecuteNonQuery()

objConn.Close()

...

MsgBox(intRecordsAffected & " records were updated.")

Using Transactions

A transaction is a set of two or more SQL statements that should be executed as a unit - they should either all succeed or all fail together. To use transactions, the OleDbTransaction or SqlTransaction object (depending on whether you are using Access or SQL Server, respectively) must be used. The start of a transaction commences with the BeginTransaction method of the Connection object, and ends successfully with the Commit method of the Transaction object; or, if an error occurs along the way, the transaction is cancelled with the Rollback method of the Transaction object.

The code snippet below shows how to structure a transaction. It assumes there is a table called "OrderMaster" which contains data about a sales order as a whole, and "OrderDetail" which contains line item details for each order. The order detail data to be written is contained in a ListView named "lvwOrderDetail":

Private Sub SaveOrderData()

Dim objTrans As OleDbTransaction = Nothing ' for SQL Server use SqlTransaction

Dim objConn As New OleDbConnection(DB_CONN_STRING) ' for SQL Server use SqlConnection

Dim objCmd As New OleDbCommand("", objConn) ' for SQL Server use SqlCommand

Dim strSQL As String

Try

objConn.Open()

objTrans = objConn.BeginTransaction

objCmd.Transaction = objTrans

strSQL = "INSERT INTO OrderMaster ..."

objCmd.CommandText = strSQL

objCmd.ExecuteNonQuery()

For intX as Integer = 0 To lvwOrderDetail.Items.Count - 1

strSQL = "INSERT INTO OrderDetail ..."

objCmd.CommandText = strSQL

objCmd.ExecuteNonQuery()

objTrans.Commit()

Catch ex As Exception

MsgBox("Error during save of order data:" & vbNewLine & ex.Message, MsgBoxStyle.Critical, "Error on Save")

If objTrans IsNot Nothing Then objTrans.Rollback()

End Try

End Sub

Considerations when using an MS-Access Backend on a 64-bit Version of Windows

When building a VB database application with MS-Access (Jet 4.0) as the backend database on a 64-bit version of Windows, you will probably receive the following error when attempting to run the program:

To remedy this problem, you must set the Target CPU to x86 when compiling the program. This is easily done when using the Professional (or higher) version of Visual Studio, as shown in the screen shots below. However - this option is NOT available with VB 2010 Express (you will NOT see the "Target CPU" drop-down on the Advanced Compiler Settings screen in VB 2010 Express).

For VB 2010 Express, you must set the platform target manually in the project file. This is not difficult to do. The steps are as follows:

First, make sure the VB project in question is closed. Then in WIndows, navigate to the project (".vbproj") file:
Right-click on the file and choose Open With --> Choose Porgram ...
Select Notepad from the "Open With" dialog.
In the project file, locate the first <PropertyGroup> tag:
Add the line <PlatformTarget>x86</Platform> after the first <PropertyGroup> line. Save and close the file. Then you can open the VB project as normal.