SQL Server application using classes for data access
Introduction
This article describes how to create a Windows Desktop application using VB.NET programming language and SQL-Server database. The intended audience are developers with no direction for working with SQL-Server database operations or developers currently utilizing TableAdapter or DataAdapter methods to work with SQL-Server databases.
The code presented will provide all the functionality needed in most solutions while some of the functionality may not be robust for each developer reading this which was done intentionally to provide a base to work from rather than a monolithic solution that needs reverse engineering. If familiar with Entity Framework then the base is similar to Entity Framework but without some of the niceties e.g. Entity Framework core opens connections as needed, built in save methods and a good deal more. Learning the basics here can assist in taking the next step to Entity Framework.
Building the solution
- Requires Visual Studio 2015 or higher.
- Requires an instance of SQL-Server installed or SQL-Express edition installed.
- Run script.sql in SplashScreenIteratorDemo under the folder DataClasses. Make sure to change the server name from KARENS-PC to the name of your SQL-Server instance or to .\SQLEXPRESS.
Description
A modified Microsoft NorthWind database (specifically for customer information) is used to
demonstrate interacting with data in the sample application.
Overview of the application
Display customer information within a DataGridView with the ability to view, add, edit and remove customers and related tables by code or cascading rules within the database. All data retrieved from the backend database are stored in class objects at runtime were the classes are defined in a separate class project which allows a developer to reuse these classes in other solutions.
There are several bases classes housed in a separate class project responsible for handling runtime exceptions. The exception class is inherited in the second class which is responsible for providing a connection string to the database used within the application. Both the exception and connection classes have protected properties which can be modified in the class (DataOperations class in the application project) which accesses data but the caller of the data class may only read the properties which has been done do there is no possible way the caller can causes issues at the presentation level. All data operations are performed in the data layer with the presentation layer calling the data layer.
Custom components
Since the classes which present information within a DataGridView do not have the capability to sort as when a DataTable is the DataSource of a DataGridView a custom BindingList is used. Besides providing the ability to sort the component provides the ability to filter data. Filtering uses System.Linq.Dynamic functionality which presents an issue such as performing case insensitive filtering e.g. StringComparison.CurrentCultureIgnoreCase can not be resolved so a property was added to the Customer class which allows the dynamic filtering to work as the added property is compliable at runtime.
The second component, a custom BindingSource which inherits the native BindingSource and provides methods to work with the underlying Customer list.
Public Class CustomerBindingSource
Inherits BindingSource
Public Sub New(container As System.ComponentModel.IContainer)
MyBase.New(container)
End Sub
Public Sub New()
MyBase.New()
End Sub
Public Sub New(datasource As Object, datamember As String)
MyBase.New(datasource, datamember)
End Sub
''' <summary>
''' Return the Customer list
''' </summary>
''' <returns></returns>
Public Function Customers As List(Of Customer)
return CType(DataSource, List(Of Customer))
End Function
''' <summary>
''' Indicator that there is a current object.
''' </summary>
''' <returns></returns>
Public Function CurrentIsValid As Boolean
Return Current IsNot nothing
End Function
''' <summary>
''' Returns the current customer object
''' </summary>
''' <returns></returns>
Public Function CurrentCustomer() As Customer
Return CType(Current, Customer)
End Function
Public sub UpdateCurrentCustomer(pCustomer as Customer)
CurrentCustomer().CompanyName = pCustomer.CompanyName
CurrentCustomer().ContactFirstName = pCustomer.ContactFirstName
CurrentCustomer().ContactLastName = pCustomer.ContactLastName
CurrentCustomer().ContactTitle = pCustomer.ContactTitle
CurrentCustomer().ContactTypeIdentifier = pCustomer.ContactTypeIdentifier
CurrentCustomer().ContactIdentifier = pCustomer.ContactIdentifier
CurrentCustomer().PhoneNumber = pCustomer.PhoneNumber
CurrentCustomer().PhoneTypeDescription = pCustomer.PhoneTypeDescription
CurrentCustomer().PhoneTypeIdenitfier = pCustomer.PhoneTypeIdenitfier
CurrentCustomer().DevicesIdentifier = pCustomer.DevicesIdentifier
CurrentCustomer().Street = pCustomer.Street
CurrentCustomer().City = pCustomer.City
CurrentCustomer().PostalCode = pCustomer.PostalCode
CurrentCustomer().CountryIdentifier = pCustomer.CountryIdentifier
CurrentCustomer().CountryName = pCustomer.CountryName
CurrentCustomer().Active = pCustomer.Active
End sub
''' <summary>
''' Indicates if the current customer has a status of active
''' </summary>
''' <returns></returns>
Public Function CurrentIsActive As Boolean
Return CurrentCustomer().Active
End Function
''' <summary>
''' Returns the current primary key for the customer
''' </summary>
''' <returns></returns>
public Function CurrentIdentifier as Integer
Return CurrentCustomer().CustomerIdentifier
End Function
''' <summary>
''' Returns the current company name
''' </summary>
''' <returns></returns>
public Function CurrentCompanyName as String
Return CurrentCustomer().CompanyName
End Function
''' <summary>
''' Returns the current contact name
''' </summary>
''' <returns></returns>
public Function CurrentContactName as String
Return CurrentCustomer().ContactName
End Function
Public Function ContactFirstName As String
Return CurrentCustomer().ContactFirstName
End Function
Public Function ContactLastName As string
Return CurrentCustomer().ContactLastName
End Function
''' <summary>
''' Returns the contact type key
''' </summary>
''' <returns></returns>
public Function CurrentContactTypeIdentifier as Integer
Return CurrentCustomer().ContactTypeIdentifier
End Function
''' <summary>
''' Returns the phone type key
''' </summary>
''' <returns></returns>
public Function CurrentPhoneTypeIdentifier as Integer
Return CurrentCustomer().PhoneTypeIdenitfier
End Function
End Class
Instead of the need to type cast the current property from object to customer we can use a method in the custom BindingSource.
_bsCustomers.CurrentCustomer()
Prior to accessing the current property it’s prudent to check if current object is valid so another method provides easy access.
_bsCustomers.CurrentIsValid()
To update the current property from changed data in a modal form.
_bsCustomers.UpdateCurrentCustomer(currentCustomer)
The alternates are type cast objects again and again or create language extension methods e.g. (which must resides in a code module).
<Runtime.CompilerServices.Extension> _
Public Function CurrentCustomer(pBindingSource As BindingSource) As Customer
Return CType(pBindingSource.Current, Customer)
End Function
Retrieving data
Reference tables are read in using several SELECT statements setup as follows.
Dim selectStatements As String =
<SQL>
SELECT ContactTypeIdentifier,ContactTitle FROM dbo.ContactType;
SELECT PhoneTypeIdenitfier,PhoneTypeDescription FROM dbo.PhoneType;
SELECT id,CountryName FROM dbo.Countries
</SQL>.Value
Note the use of XML literals and semi-colons to separate each SELECT statement. Each SELECT statement is read in via SqlClient DataReader using NextResult method to move to the next SELECT statement.
Customer data uses one SELECT statement which was created in SQL-Server Management Studio to ensure the proper data would be returned. If SQL-Server Management Studio is not available a developer can create a text file in their project, change the file extension from .txt to .sql and write SQL statements. Bonus if using Visual Studio 2017 which provides Intellisense for database operations.
The following SELECT statement uses XML literals as with the reference tables mentioned above.
Private ReadOnly Property CustomerSelectStatement As String
get
Return _
<SQL>
SELECT Cust.CustomerIdentifier ,
Cust.CompanyName ,
Contact.FirstName + ' ' + Contact.LastName AS ContactName ,
Contact.FirstName ,
Contact.LastName ,
Cust.ContactTypeIdentifier,
ContactType.ContactTitle ,
Contact.ContactIdentifier ,
Devices.PhoneNumber ,
PhoneType.PhoneTypeDescription ,
Devices.PhoneTypeIdenitfier,
Cust.Street ,
Cust.City ,
Cust.PostalCode ,
Countries.CountryName ,
Countries.id,
Cust.ModifiedDate,
Devices.Active ,
Devices.Identifier
FROM Customers AS Cust
INNER JOIN ContactType ON Cust.ContactTypeIdentifier = ContactType.ContactTypeIdentifier
INNER JOIN Countries ON Cust.CountryIdentfier = Countries.id
INNER JOIN Contact ON Cust.ContactIdentifier = Contact.ContactIdentifier
INNER JOIN ContactContactDevices AS Devices ON Contact.ContactIdentifier = Devices.ContactIdentifier
INNER JOIN PhoneType ON Devices.PhoneTypeIdenitfier = PhoneType.PhoneTypeIdenitfier
WHERE (Devices.Active = 1 AND PhoneType.PhoneTypeIdenitfier = 3
)
ORDER BY Cust.CompanyName;
</SQL>.Value
End get
End Property
The query is executed in a function utilizing an Iterator which returns IEnumerable(Of Customer) using a DataReader coupled with Yield statement. This occurs when the application starts and displays results in a splash screen. There is a short delay in the read operation as there are 102 records and without the delay everything would happen too fast to notice. The idea here is the user of the application sees progress as company names are displayed in the splash screen. The proper use is not for one hundred or several hundred records but many thousands of records or perhaps a slow connection to the backend database.
Special care is needed if an exception is thrown while in the iterator method since the splash screen runs in a separate thread a delegate is required to close the splash screen in this case.
DataGridView setup
For each customer field/property which is displayed in the DataGridView a column needs to be created and the DataPropertyName set. The default behavior of generating columns is not performed the following line stops auto generation of columns in form load event.
Modifying data
Both adding new customers and editing customers is performed in a modal dialog rather than directly in the DataGridView. Validation is performed in the modal dialog for ensuring required fields are entered. If there are exceptions thrown during add or update operations raised from code or the backend database the insert or update are rolled back via a transaction object and by using a base exception class exceptions can be displayed to the user without halting the application.
Note that when viewing adding and update methods for the novice/beginner developer may think there is a lot of code vs working with Table or Data adapters yet there is freedom to change how a operation performs rather than rely on what goes behind a Table or Data adapter.
For both add and updates several tables are affected as the version of NorthWind uses related tables for contact details along with the base customer table.
Inserting
For inserts, one reference table is inserted, the new primary key is returned and used for the next insert then used in the last insert.
Public Function AddNewCustomer(pCustomer As customer) As Boolean
mHasException = False
dim contactInsertStatement =
<SQL>
INSERT INTO dbo.Contact
(FirstName
,LastName)
VALUES
(@FirstName
,@LastName);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Dim contactDevicesInsertStatement =
<SQL>
INSERT INTO dbo.ContactContactDevices
(ContactIdentifier
,PhoneTypeIdenitfier
,PhoneNumber
,Active)
VALUES
(@ContactIdentifier
,@PhoneTypeIdenitfier
,@PhoneNumber
,@ACTIVE);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Dim customerInsertStatement =
<SQL>
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactIdentifier
,ContactTypeIdentifier
,Street
,City
,PostalCode
,CountryIdentfier
,Phone
,ModifiedDate)
VALUES
(@CompanyName
,@ContactName
,@ContactIdentifier
,@ContactTypeIdentifier
,@Street
,@City
,@PostalCode
,@CountryIdentfier
,@Phone
,@ModifiedDate);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Dim contactIdentifier As Integer = 0
Dim contactDeviceIdentifier as Integer = 0
Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
cn.Open()
Dim sqlTran As SqlTransaction = cn.BeginTransaction()
Using cmd As New SqlCommand With
{
.Connection = cn,
.CommandText = contactInsertStatement,
.Transaction = sqlTran
}
Try
cmd.Parameters.AddWithValue("@FirstName",pCustomer.ContactFirstName)
cmd.Parameters.AddWithValue("@LastName",pCustomer.ContactLastName)
contactIdentifier = CInt(cmd.ExecuteScalar())
cmd.CommandText = contactDevicesInsertStatement
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier)
cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier",pCustomer.PhoneTypeIdenitfier)
cmd.Parameters.AddWithValue("@PhoneNumber",pCustomer.PhoneNumber)
cmd.Parameters.AddWithValue("@Active",True)
contactDeviceIdentifier = CInt(cmd.ExecuteScalar())
cmd.CommandText = customerInsertStatement
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName)
cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName)
cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier)
cmd.Parameters.AddWithValue("@ContactTypeIdentifier",pCustomer.ContactTypeIdentifier)
cmd.Parameters.AddWithValue("@Street",pCustomer.Street)
cmd.Parameters.AddWithValue("@City",pCustomer.City)
cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode)
cmd.Parameters.AddWithValue("@Phone",pCustomer.PhoneNumber)
cmd.Parameters.AddWithValue("@CountryIdentfier",pCustomer.CountryIdentifier)
cmd.Parameters.AddWithValue("@ModifiedDate",Now)
pCustomer.CustomerIdentifier = CInt(cmd.ExecuteScalar())
pCustomer.ContactIdentifier = contactIdentifier
pCustomer.DevicesIdentifier = contactDeviceIdentifier
sqlTran.Commit()
return True
Catch sex As SqlException
sqlTran.Rollback()
mHasException = True
Return False
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Updates
The update is structured similar adding a new customer while in the update we already have primary keys to find the proper rows and update these records. Since an exception may happen the operation is wrapped in a transaction and committed on success or rolled back on failure.
public Function UpdateCustomer(pCustomer as Customer) As Boolean
Dim successCount as Integer = 0
dim contactUpdateStatement =
<SQL>
UPDATE dbo.Contact
SET FirstName = @FirstName
,@LastName = @LastName
WHERE ContactIdentifier = @Id
</SQL>.Value
Dim contactDevicesUpdateStatement =
<SQL>
UPDATE dbo.ContactContactDevices
SET ContactIdentifier = @ContactIdentifier
,PhoneTypeIdenitfier = @PhoneTypeIdenitfier
,PhoneNumber = @PhoneNumber
,Active = @Active
WHERE Identifier = @Id
</SQL>.Value
Dim customerUpdateStatement =
<SQL>
UPDATE dbo.Customers
SET CompanyName = @CompanyName
,ContactName = @ContactName
,ContactIdentifier = @ContactIdentifier
,ContactTypeIdentifier = @ContactTypeIdentifier
,Street = @Street
,City = @City
,PostalCode = @PostalCode
,CountryIdentfier = @CountryIdentfier
,Phone = @Phone
,ModifiedDate = @ModifiedDate
WHERE CustomerIdentifier = @Id
</SQL>.Value
Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
cn.Open()
Dim sqlTran As SqlTransaction = cn.BeginTransaction()
Using cmd As New SqlCommand With {.Connection = cn, .CommandText = contactUpdateStatement, .Transaction = sqlTran}
Try
cmd.Parameters.AddWithValue("@FirstName", pCustomer.ContactFirstName)
cmd.Parameters.AddWithValue("@LastName", pCustomer.ContactLastName)
cmd.Parameters.AddWithValue("@Id", pCustomer.ContactIdentifier)
successCount = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.CommandText = contactDevicesUpdateStatement
cmd.Parameters.AddWithValue("@ContactIdentifier", pCustomer.ContactIdentifier)
cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier", pCustomer.PhoneTypeIdenitfier)
cmd.Parameters.AddWithValue("@PhoneNumber", pCustomer.PhoneNumber)
cmd.Parameters.AddWithValue("@Active", True)
cmd.Parameters.AddWithValue("@Id", pCustomer.DevicesIdentifier)
successCount += cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.CommandText = customerUpdateStatement
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@CompanyName", pCustomer.CompanyName)
cmd.Parameters.AddWithValue("@ContactName", pCustomer.ContactName)
cmd.Parameters.AddWithValue("@ContactIdentifier", pCustomer.ContactIdentifier)
cmd.Parameters.AddWithValue("@ContactTypeIdentifier", pCustomer.ContactTypeIdentifier)
cmd.Parameters.AddWithValue("@Street", pCustomer.Street)
cmd.Parameters.AddWithValue("@City", pCustomer.City)
cmd.Parameters.AddWithValue("@PostalCode", pCustomer.PostalCode)
cmd.Parameters.AddWithValue("@Phone", pCustomer.PhoneNumber)
cmd.Parameters.AddWithValue("@CountryIdentfier", pCustomer.CountryIdentifier)
cmd.Parameters.AddWithValue("@ModifiedDate", Now)
cmd.Parameters.AddWithValue("@Id",pCustomer.CustomerIdentifier)
successCount += cmd.ExecuteNonQuery()
sqlTran.Commit()
Return successCount = 3
Catch sex As SqlException
sqlTran.Rollback()
mHasException = True
Return False
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Removing records
To remove a record pressing DELETE key on a record prompts for confirmation. If confirmed three tables are removed with the addition of orders and order details as cascade deletes are set to true. As with inserts and updates since there are multiple tables a transaction wraps around the removal operation.
Public Function RemoveCustomer(pCustomer As Customer ) As Boolean
mHasException = False
Dim removeContactStatement = "DELETE FROM dbo.Contact WHERE ContactIdentifier = @Id"
Dim removeCustomerStatement = "DELETE FROM dbo.Customers WHERE CustomerIdentifier = @Id"
Dim removeDevicesStatement = "DELETE FROM dbo.ContactContactDevices WHERE ContactIdentifier = @Id"
Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
cn.Open()
Dim sqlTran As SqlTransaction = cn.BeginTransaction()
Using cmd As New SqlCommand With {.Connection = cn, .CommandText = removeCustomerStatement, .Transaction = sqlTran}
cmd.Parameters.AddWithValue("@Id", pCustomer.CustomerIdentifier)
Try
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.CommandText = removeDevicesStatement
cmd.Parameters.AddWithValue("@Id", pCustomer.ContactIdentifier)
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.CommandText = removeContactStatement
cmd.Parameters.AddWithValue("@Id", pCustomer.ContactIdentifier)
cmd.ExecuteNonQuery()
sqlTran.Commit()
Return True
Catch sex As SqlException
sqlTran.Rollback()
mHasException = True
mLastException = sex
Return False
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
A downside to creating classes as done in this code sample in tangent with usage in SQL statements is you the developer must be diligent to update the queries e.g. if a new column is added then in the read operation make sure to add the column which usually means adding the column to the add, edit methods along any place this data is needed when working with the data in the presentation layer also and run unit test methods against the changed code.
Organization of projects
Both custom components mentioned reside in their own class projects which permits usage in other projects were the only dependencies are on the customer class which resides in a class project for all classes required for this application. One can take both class projects and use in another solution which works with the same database. Both exception handling class and database connection class reside in a separate class project which can be used in another Visual Studio solution.
Classes specific to the application reside in folders within the application project. There are two custom My.Namespace classes which must be in the application as how the .NET Framework is setup these classes cannot be on another project. One is a wrapper for showing message boxes while the other is a stub for sending error emails.
Take a look at ControlsExtensions in the main project which provides an extension method Descendants which is a good method to target all controls of a specific type on a form in in any containers on the form. It’s really useful in this code sample for edit/add validation along with exception handling if an exception is thrown in form load of the main form.
<Runtime.CompilerServices.Extension> _
Public Iterator Function Descendants(Of T As Class)(ByVal control As Control) As IEnumerable(Of T)
For Each child As Control In control.Controls
Dim currentChild = TryCast(child, T)
If currentChild IsNot Nothing Then
Yield currentChild
End If
If child.HasChildren Then
For Each descendant As T In child.Descendants(Of T)()
Yield descendant
Next
End If
Next
End Function
Moving your code to this level
To move to this level a developer that is interested in this must take time to study how the code flows along with having a decent understanding why code has been setup as presented. The wrong approach is to copy and paste parts of code without having a understanding why. Couple code with a sound database schema which reflects your business requirements.
Where to go from here
Once there is a firm understanding of what has been presented consider working with Entity Framework. Entity Framework does require a significant amount of code to get rolling yet once into normal database operations Entity Framework is more elegant than TableAdapter, DataAdapter and even methods presented here. Why not skip right from TableAdapter or DataAdapter to Entity Framework? Because you will miss out on learning how things work which with Entity Framework is less transparent yet once learned is easy to understand.
Entity Framework Read example
In this example the classes which represent the same tables as in the above were created using “Design from database”. To keep things simple, the following loads Customers data.
Keeping simple, the following loads customers and displays several fields. In a real application data would be loaded into a DataGridView using the same custom BindingList as in the non-Entity Framework example above.
Imports System.Data.Entity
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim customerList as new List(Of Customer)
Dim customerIdentifiers = Enumerable.Range(1, 10).ToArray()
using cn As New NorthWindEntities()
cn.Configuration.ProxyCreationEnabled = False
cn.Configuration.LazyLoadingEnabled = true
customerList = cn.Customers.
Include(Function(cust As Customer) cust.Contact).
Include("Country").Include("ContactType").
Where(Function(cust) customerIdentifiers.Contains(cust.CustomerIdentifier)).
ToList()
End Using
For Each cust As customer In customerList
Console.WriteLine(
$"{cust.CompanyName,-35} {cust.Contact.FirstName,-12} {cust.Contact.LastName, -12} {cust.ContactType.ContactTitle}")
Next
End Sub
End Class
Important notes
cn.Configuration.ProxyCreationEnabled = False
cn.Configuration.LazyLoadingEnabled = true
The two lines above tell Entity Framework not to load related tables e.g. Orders in this case is excluded. Since related tables are not loaded we need to use Include which is shown two different ways, one specifying the table name in a string which is fragile since table names change over time this can break while the second method uses lambda to strongly type the expression where condition which is the preferred method. Include is shown for one level but also supports multiple levels also.
This short example provides a quick glance for reading data. Modifying data is very easy too e.g. create a new customer, populate it’s properties, mark the Entry state as modified followed by calling the save method on the context.
Public Sub UpdateCustomer(pCustomer As Customer)
Try
Using cn As New NorthWindEntities
cn.Entry(pCustomer).State = EntityState.Modified
cn.SaveChanges()
End Using
Catch ex As DbUpdateConcurrencyException
' handle Optimistic Concurrency exception
End Try
End Sub
Adding a new customer unlike using a managed data provider to insert and get the new primary key Entity Framework returns the new key upon a successful save.
As mentioned above, Entity Framework is a great option yet not always the best option for desktop application. If you are building desktop applications currently use the methods talk about above, when moving to web applications or service solutions look at Entity Framework current version.
Public Function AddCustomer(pCustomer As Customer) As Integer
Using cn As New NorthWindEntities
cn.Entry(pCustomer).State = EntityState.Added
'
' The save will bring back the new primary key
'
cn.SaveChanges()
'
' After saving the new primary key is contained in Customer
' The caller in this case creates a new record in the DataGridView
' DataSource, a BindingSource.
Return pCustomer.CustomerIdentifier
End Using
End Function
Screen shots
While data is loaded in the data class the function responsible is an Iterator using Yield to pass the current customer name via a delegate to the splash screen which runs in it's own thread.
Splash screen
Once finishing loading of data
Editing screen
Add screen
Remove current customer
Dynamic filter
Source code
Found on MSDN https://code.msdn.microsoft.com/SQL-Server-application-1d1368bc