MS-Access with VB.NET: Best practices (Part 2)
See also
Best practices part 1 of this series and Part 3
Introduction
This code sample/article is a continuation of part 1 where the goal is to provide developers with basic to intermediate to advance methods for working with Microsoft Access databases for desktop solutions utilizing VB.NET programming language with Microsoft Visual Studio 2017. Before working with the information in this code sample start with part 1 which covers all the basics for designing a database, using base classes for connections with or without a database password, exception handling. Basics for reading data, removal of data where in this part adding and editing data will be covered along with other important topics. Part 3 concludes the series.
Building the solution
Use the following link for updated source code.
Inserting records
Adding new records has two flavors,
- add record(s) without the need to return it's key, while
- the other is to add record(s) and return the key.
Here, the focus will be on returning the primary key. First write code to add a record using the following model, create a connection and a command object with one parameter for each field to be inserted into the new record. Unlike prior examples in this series, parameters are not known by a question mark but instead named parameters even though MS-Access reads parameters by ordinal position. Using named parameters is easy to read and when there are issues easier to track down issues and finally if the database is changed to SQL-Server which uses named parameters part of your work is done already.
Signature for the add method
Public Function AddNewRow(
pName As String,
pContact As String,
pContactTitle As String,
ByRef pIdentfier As Integer) As Boolean
Each field is passed in strong typed which assist to prevent errors. An alternate method would be to pass in a DataRow or use a strongly typed class to represent fields to use in the add new record operation.
cmd.ExecuteReader runs the SQL INSERT, once executed set the command query to get the new primary key and set the incoming variable identifier with the key. If all works as expected the caller can use the new key to then be able to reference the record for read, update and delete operations. Refer to the following form (first procedure) to see how this method is called.
Public Function AddNewRow(
pName As String,
pContact As String,
pContactTitle As String,
ByRef pIdentfier As Integer) As Boolean
Dim success As Boolean = True
Try
Using cn As New OleDbConnection(ConnectionStringWithPassword)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = <SQL>
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle
)
Values
(
@CompanyName,
@ContactName,
@ContactTitle
)
</SQL>.Value
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
success = False
End Try
Return success
End Function
This alternate method for adding a new record uses a class instance to supply data. The parameter customerList is for inserting multiple (or one) records.
Note that the command does not use a using statement, instead the command is created for each iteration of an insert. Alternately the command may be created once, create the parameters once then populated the parameters on each iteration if not for the second query to obtain the new primary key.
''' <summary>
''' Add many customer records
''' </summary>
''' <param name="customersList">List of new customers to add to the Customer table</param>
''' <returns>True if successful, false on error</returns>
Public Function AddNewCustomerRecords(customersList As List(Of Customer)) As Boolean
mHasException = False
Dim affected As Integer = 0
Try
Using cn As New OleDbConnection With {.ConnectionString = ConnectionString}
cn.Open()
For Each customer As Customer In customersList
'
' Create a command for adding a new record. This must
' be done each iteration as in the code to get the new primary
' key the command is altered.
'
Dim cmdAdd = AddRecordCommand(cn)
cmdAdd.Parameters("@CompanyName").Value = customer.CompanyName
cmdAdd.Parameters("@ContactName").Value = customer.ContactName
cmdAdd.Parameters("@EstablishedYear").Value = customer.EstablishedYear
cmdAdd.Parameters("@Incorporated").Value = customer.Incorporated
'
' Add record
'
affected = cmdAdd.ExecuteNonQuery()
'
' If affected equals 1, this means the record was added,
' in turn get the new primary key by changing the command text.
' No need to clear parameters.
'
If affected = 1 Then
cmdAdd.CommandText = "Select @@Identity"
customer.Id = CInt(cmdAdd.ExecuteScalar)
End If
Next
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
'
' Exit on the first exception.
'
Return False
End Try
Return IsSuccessFul
End Function
Method to create an OleDb command with parameters.
''' <summary>
''' Used to setup parameters for the method AddNewCustomerRecords
''' </summary>
''' <param name="cn">A OleDbConnection that has been created as in AddNewCustomerRecords</param>
''' <returns>Fully prepared command object</returns>
Private Function AddRecordCommand(cn As OleDbConnection) As OleDbCommand
Dim cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer
(
CompanyName,
ContactName,
EstablishedYear,
Incorporated
)
VALUES
(
@CompanyName,
@ContactName,
@EstablishedYear,
@Incorporated
)
</SQL>.Value
cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@CompanyName",
.DbType = DbType.String})
cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@ContactName",
.DbType = DbType.String})
cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@EstablishedYear",
.OleDbType = OleDbType.Integer})
cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@Incorporated",
.OleDbType = OleDbType.Date})
Return cmd
End Function
TIP If at anytime a query does not work as expected using parameters makes determine an issue is sometimes almost impossible leading a developer to move away from parameters and move to old string concatenation or dream up your own method to deal with parameters such as creating a helper using IFormatProvider, something like the following.
Public Class SqlFormatter
Implements ICustomFormatter
Public Function Format(
pFormat As String,
pArg As Object,
pFormatProvider As IFormatProvider) As String _
Implements ICustomFormatter.Format
If pArg Is Nothing Then
Return "NULL"
End If
If TypeOf pArg Is String Then
Return "'" & CStr(pArg).Replace("'", "''") & "'"
End If
If TypeOf pArg Is Date Then
Return "'" & CDate(pArg).ToString("MM/dd/yyyy") & "'"
End If
If TypeOf pArg Is IFormattable Then
Return CType(pArg, IFormattable).
ToString(pFormat, Globalization.CultureInfo.InvariantCulture)
End If
Return pArg.ToString()
End Function
End Class
Helper class (for working in the form code)
Public Module Helpers
Public Function Sql(formattable As FormattableString) As String
Return formattable.ToString(New SqlFormatProvider())
End Function
End Module
Form code
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) _
Handles Button1.Click
Dim values As New Dictionary(Of String, String) From
{{"FirstName", "Karen"}, {"LastName", "O'Brien"}}
Dim strSQLSave = Sql(
$"INSERT INTO Table1 (FirstName, LastName) values ({values("Karen")}, {values("Payne")})")
Console.WriteLine(strSQLSave)
End Sub
End Class
There is a better method to debug SQL with parameters using the following class.
Namespace DataHelpers
Public Module CommandPeeker
''' <summary>
''' Provides the ability to view a parameterized query values when using parameter names
''' such as @CompanyName but will not work with a parameter simply named ?.
''' </summary>
''' <param name="sender"></param>
''' <param name="token">Alternate parameter start token</param>
''' <returns></returns>
''' <remarks></remarks>
<Runtime.CompilerServices.Extension()>
Public Function ActualCommandTextByNames(
sender As IDbCommand,
Optional token As String = "@") As String
Dim sb As New Text.StringBuilder(sender.CommandText)
Dim emptyParameterNames =
(
From T In sender.Parameters.Cast(Of IDataParameter)()
Where String.IsNullOrWhiteSpace(T.ParameterName)).FirstOrDefault
If emptyParameterNames IsNot Nothing Then
Return sender.CommandText
End If
For Each p As IDataParameter In sender.Parameters
Select Case p.DbType
Case DbType.AnsiString, DbType.AnsiStringFixedLength,
DbType.Date, DbType.DateTime, DbType.DateTime2,
DbType.Guid, DbType.String, DbType.StringFixedLength, DbType.Time, DbType.Xml
If p.ParameterName(0) = token Then
If p.Value Is Nothing Then
Throw New Exception("no value given for parameter '" &
p.ParameterName & "'")
End If
sb = sb.Replace(
p.ParameterName, $"'{p.Value.ToString.Replace("'", "''")}'")
Else
sb = sb.Replace(
String.Concat(
token, p.ParameterName), $"'{p.Value.ToString.Replace("'", "''")}'")
End If
Case Else
sb = sb.Replace(p.ParameterName, p.Value.ToString)
End Select
Next
Return sb.ToString
End Function
End Module
End Namespace
Usage and doing a comparison between what is normally seen with parameters and with the extension method above.
Console.WriteLine(cmd.CommandText)
Console.WriteLine(cmd.ActualCommandTextByNames())
The output of both lines above will display in the Visual Studio Output window.
This is the display for the first console write line, we see no values.
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle
)
Values
(
@CompanyName,
@ContactName,
@ContactTitle
)
While the output for the second write line using ActualCommandTextByBName the values are exposed which were passed into the query.
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle
)
Values
(
'Karen''s coffee',
'Karen Payne',
'Accounting Manager'
)
Best to use parameters instead of string concatenation or creating your own routine to deal with values for any SQL statement.
Updating records
Since in the insert record passing values in one example and a list in another, this update will be done by passing a DataRow to the update method. Both inserts or updates can be done with values passed, a list or single instance of a class, a DataRow or a DataTable.
There are common ways to create parameters, using cmd.Parameters.Add and cmd.Parameters.AddWithValue. In the update method, AddWithValue is used as done in a prior example in part one of this series where the command object and parameters were created in a different method then were executed.
.
An assertion is performed on cmd.ExecuteNonQuery to see if the value returned is 1 which indicates that the row was actually updated.
Public Function UpdateRow(pRow As DataRow) As Boolean
Try
Using cn As New OleDbConnection(ConnectionStringWithPassword)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = <SQL>
UPDATE
Customer
SET
Process = @Process,
CompanyName = @CompanyName,
ContactName = @ContactName,
ContactTitle = @ContactTitle
WHERE Identifier = @Identifier
</SQL>.Value
Dim processParameter As New OleDbParameter With
{
.DbType = DbType.Boolean,
.ParameterName = "@Process",
.Value = pRow.Field(Of Boolean)("Process")
}
cmd.Parameters.Add(processParameter)
Dim companyNameParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "@CompanyName",
.Value = pRow.Field(Of String)("CompanyName")
}
cmd.Parameters.Add(companyNameParameter)
Dim contactNameParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "@ContactName",
.Value = pRow.Field(Of String)("ContactName")
}
cmd.Parameters.Add(contactNameParameter)
Dim contactTitleParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "@ContactTitle",
.Value = pRow.Field(Of String)("ContactTitle")
}
cmd.Parameters.Add(contactTitleParameter)
Dim identifierParameter As New OleDbParameter With
{
.DbType = DbType.Int32,
.ParameterName = "@Identifier",
.Value = pRow.Field(Of Integer)("Identifier")
}
cmd.Parameters.Add(identifierParameter)
Try
cn.Open()
Dim affected = cmd.ExecuteNonQuery
Return affected = 1
Catch ex As Exception
mHasException = True
mLastException = ex
Return IsSuccessFul
End Try
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Return IsSuccessFul
End Try
End Function
TIPS
- When adding or updating records there should be two assertions, one did the operation work or not and secondly was an exception thrown. It's possible for an operation like an update to fail without an exception e.g. in a multi-user environment the record to update was removed by another user.
- Before performing an insert or update, validate that field values match business rules, for example when entering a customer they must have a name but may not need a phone number right away.
- Understand how to deal with database constraints. Developers without proper knowledge of databases will set a constraint in the database then wonder why there is a constraint violation when attempting to work on a field that for instance does not permit duplicates or as discussed before cascading rules prohibit an action.
Removing/deleting records
Removing a record in a single table solution is easy while in a relational database model considerations must be applied e.g. can a customer record be removed without removal of child tables or can a child table be removed while keeping the parent table which sends someone back to the drawing board if during testing or in production a delete operation throws a runtime exception.
- Consider database rules to allow or disallow an action.
- Business rules coupled with proper cascading properties of multiple related tables.
Removal at the core is simple, pass in a primary key, call the DELETE and do assertions as discussed above. If the record does not exists and no errors the method returns false.
Public Function RemoveCurrentCustomer(pIdentfier As Integer) As Boolean
Try
Using cn As New OleDbConnection(ConnectionStringWithPassword)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = "DELETE FROM Customer WHERE Identifier = @Identifier"
Dim identifierParameter As New OleDbParameter With
{
.DbType = DbType.Int32,
.ParameterName = "@Identifier",
.Value = pIdentfier
}
cmd.Parameters.Add(identifierParameter)
Try
cn.Open()
Dim affected = cmd.ExecuteNonQuery
Return affected = 1
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Return IsSuccessFul
End Try
End Function
Working with transactions
*"MS- Access databases include support for transactions, which enable multiple database commands to be grouped into a single logical unit of work. The database commands that comprise a transaction are guaranteed to be atomic, meaning that either all commands will fail or all will succeed. *
In general, transactions are implemented through SQL statements using the following pattern:
- Indicate the start of a transaction.
- Execute the SQL statements that comprise the transaction.
- If there is an error in one of the statements from Step 2, rollback the transaction.
- If all of the statements from Step 2 complete without error, commit the transaction.
When modifying data in a batch transaction, atomicity is not always needed. In some scenarios, it may be acceptable to have some data modifications succeed and others in the same batch fail, such as when deleting a set of emails from an email client. If there s a database error midway through the deletion process, it is probably acceptable that those records processed without error remain deleted. In such cases, the DAL does not need to be modified to support database transactions. There are other batch operation scenarios, however, where atomicity is vital. When a customer moves her funds from one bank account to another, two operations must be performed: the funds must be deducted from the first account and then added to the second. While the bank may not mind having the first step succeed but the second step fail, its customers would understandably be upset." [docs.microsodft.com]
In the following code sample, only one operation is performed to keep clear how to implement a transaction, in reality, a customer and an order and order details may be added. If the order details failed then the order and customer insert would rollback along with the order details.
Public Function AddNewRow(
companyName As String,
contactName As String,
establishedYear As Integer,
incorporated As Date,
ByRef pIdentfier As Integer) As Boolean
Using cn As New OleDbConnection(ConnectionString)
Dim transaction As OleDbTransaction = Nothing
Dim cmd = AddRecordCommand(cn)
Try
cn.Open()
transaction = cn.BeginTransaction()
cmd.Transaction = transaction
cmd.Parameters("@CompanyName").Value = companyName
cmd.Parameters("@ContactName").Value = contactName
cmd.Parameters("@EstablishedYear").Value = establishedYear
cmd.Parameters("@Incorporated").Value = incorporated
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
transaction.Commit()
Catch oleDbException As OleDbException
transaction.Rollback()
mHasException = True
mLastException = oleDbException
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
Return IsSuccessFul
End Function
Working with Data Readers
Data readers are a good choice to read data that does not need to be changed. For example, a table contains country names is needed to be displayed in a ComboBox to allow a user to select a user's country where in the record added or modified only the primary key for the country is remembered in a number field while another example is to read one or more tables to create a list.
In the following example, the database holds computer information where in this case there is an interested to learn besides the current user of the computer who is the past has used this computer.
Two classes to represent data read via the data readers.
Public Class ComputerAsset
Public Property AssetId() As Integer
Public Property UserName() As String
Public Property ComputerName() As String
Public Property ServiceTag() As String
Public Property FormerUsers() As List(Of User)
Public Sub New()
FormerUsers = New List(Of User)
End Sub
Public Overrides Function ToString() As String
Return ComputerName
End Function
End Class
Public Class User
Public Property UserId() As Integer
Public Property AssetId() As Integer
Public Property Name() As String
Public Overrides Function ToString() As String
Return $"{Name}"
End Function
End Class
Special note: Each class overrides ToString as this will be picked up for what to display in the user interface, in this case, ListBox controls, same works for ComboBox controls.
Moving on to the read operations, since there are two data readers used in a loop each reader is given a clear name to assist in writing the code.
The outer while statement obtains computers then in the inner while user information is obtained while in the outer loop. As in all other code samples, the same exception handling is used.
Imports System.Data.OleDb
Imports KarensBaseClasses
Public Class DatabaseOperations
Inherits AccessConnection
Public Sub New()
DefaultCatalog = IO.Path.Combine(
AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
End Sub
Public Function GetComputerNames() As List(Of ComputerAsset)
Dim results = New List(Of ComputerAsset)
mHasException = False
Dim selectComputerStatement =
"SELECT AssetId, UserName, ComputerName, ServiceTag " &
"FROM ComputerAsset;"
Dim selectUserStatement =
"SELECT UserId, AssetId, UserName " &
"FROM [User] WHERE AssetId = @AssetId;"
Dim assetId As Integer = 0
Using cn As New OleDbConnection(ConnectionString)
Using cmdComputer As New OleDbCommand With {.Connection = cn}
cmdComputer.CommandText = selectComputerStatement
Try
cn.Open()
Dim readerComputer = cmdComputer.ExecuteReader()
While readerComputer.Read()
assetId = readerComputer.GetInt32(0)
Dim currentComputer = New ComputerAsset() With
{
.assetId = assetId,
.UserName = readerComputer.GetString(1),
.ComputerName = readerComputer.GetString(2),
.ServiceTag = readerComputer.GetString(3)
}
results.Add(currentComputer)
Using cmdUser As New OleDbCommand With {.Connection = cn}
cmdUser.CommandText = selectUserStatement
cmdUser.Parameters.AddWithValue("@AssetId", assetId)
Dim readerUser = cmdUser.ExecuteReader()
While readerUser.Read()
currentComputer.FormerUsers.Add(New User() With
{
.assetId = assetId,
.UserId = readerUser.GetInt32(0),
.Name = readerUser.GetString(2)
})
End While
End Using
End While
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return results
End Function
End Class
In the calling form, one ListBox will be populated with computers then as items change from a user traversing items the SelectedIndex event is triggered which then changes the DataSource of the second ListBox for users of the computer. A DataSet with a relationship could also be used in tangent with two BindingSource components but in this case, there are only a handful of records so using data readers with lightweight classes makes sense.
Summary
In this article how to add, update and delete records has been presented for basic CRUD operations along with using transactions for both single and multiple operations failures for commits and rollbacks. Data readers for read only access to data.
Implementation, tie a Customer (DataTable) to an Order (DataTable) by the key field Identifier which resides in both tables.