WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
My first article on this topic demonstrated database reads-to-entities. Reads are relatively easy. Just read one or more result sets and build objects; you don't need transactions. This article demonstrates database writes. The write part of managing a data access layer (DAL) is where things can go awry. Writing requires managing changes, validation, transactions, and a variety of changes, including new, changed, and deleted objects.
This article demonstrates how to add and modify objects in a collection and then, using the basic DAL introduced in my previous article, how to manage writing those changes back to the persistence store. Although the code sample implicitly demonstrates insert and update behaviors, the text provides hints for handling validation and deletion too.
Modifying the Custom Objects
This example uses a simple console application to modify objects. (Listing 1 shows the code for the demo.) The beauty of a custom objects approach is that literally any kind of client can be used to modify the objects; they are just classes. Custom business objects also are lightweight and easy to use, and they behave in a persistence-layer-agnostic way.
Listing 1: Custom Business Objects Demo
Imports MYVB.BusinessObjects Imports MYVB.DataAccess Module Module1 Sub Main() Dim customers As List(Of Customer) = _ CustomerAccess.GetCustomers() For Each c As Customer In customers Console.WriteLine(c.ContactName) Next ' Add a customer, modify a second customer, and save Dim newCustomer As Customer = New Customer( _ "PAUKI", "Fatman Soda Company", "P. Kimmel", _ "Boss", "", "", "", "", "", "", "") customers.Add(newCustomer) customers(0).ContactName = "Paul Kimmel" ' Trying to write all is terribly inefficient ' You could A) track changes or B) insert only those you ' know are new or have changed CustomerAccess.PutCustomers(customers) Console.ReadLine() End Sub End Module
As you can see, you can modify the collection and objects in any way desired. All of the writing is done at the collection level. However, you could easily write one object at a time, manage deletes, or incorporate change management logic to promote writing only objects that have changed.
Implementing the Write Behavior
For the CustomerAccess class to work with the basic DAL, you simply need to orchestrate calling the basic DataAccess class's Write method (see Listing 2). The DataAccess.Write method needs to know the stored procedure to call (although you could include a literal SQL overloaded method), what kind of object it is writing, a write event handler, whether anything comes back from the write, and the list of parameters and values to send to the stored procedure.
Listing 2: Both DataAccess.Write Methods
Public Shared Sub Write(Of T)(ByVal o As T, _ ByVal procedureName As String, _ ByVal handler As WriteEventHandler(Of T), _ ByVal ParamArray parameters() As IDbDataParameter) Using connection As IDbConnection = _ factory.CreateConnection(factory.ConnectionString) connection.Open() Write(o, procedureName, handler, connection, Nothing, parameters) End Using End Sub Public Shared Sub Write(Of T)(ByVal o As T, _ ByVal procedureName As String, _ ByVal handler As WriteEventHandler(Of T), _ ByVal connection As IDbConnection, _ ByVal transaction As IDbTransaction, _ ByVal ParamArray parameters() As IDbDataParameter) Dim command As IDbCommand = factory.CreateCommand(procedureName) command.CommandType = CommandType.StoredProcedure command.Connection = connection command.Transaction = transaction If (parameters Is Nothing = False) Then Dim p As IDbDataParameter For Each p In parameters command.Parameters.Add(p) Next End If command.ExecuteNonQuery() If (handler <> Nothing) Then handler(o, command) End If End Sub
The first version of Write creates a connection for you, and the second version permits passing in both a connection and a transaction.
Because the basic DataAccess class manages just the general behavior, you do need to extend the specific entity data-access classes to indicate the objects to act on and how to act on them. For example, writing a Customer means you have to tell the DataAccess class about Customer stored procedures and Customer fields. The complete listing for the CustomerAccess class is shown in Listing 3, and the remaining sub-sections describe each of the new elements.
Listing 3: The Complete Implementation of the CustomerAccess Class
Imports System Imports System.Data Imports System.Diagnostics Imports System.Configuration Imports System.Transactions Imports System.Collections.Generic Imports System.Web Imports MYVB.BusinessObjects Public Class CustomerAccess Public Shared Function GetCustomers() As List(Of Customer) Return DataAccess.Read(Of List(Of Customer))("GetCustomers", _ AddressOf OnReadCustomers) End Function Public Shared Sub PutCustomers(ByVal customers As List(Of Customer)) ' Reference and import System.Transactions Using scope As TransactionScope = New _ TransactionScope(TransactionScopeOption.Required) For Each c As Customer In customers PutCustomer(c) Next scope.Complete() End Using End Sub ' what if unique key is auto. Use write handler to set after insert ' change stored proedure to insert on null key ' key becomes output parameters Public Shared Sub PutCustomer(ByVal cust As Customer) Debug.Assert(cust Is Nothing = False) Dim Factory As DbFactory = DbFactory.CreateFactory() Using connection As IDbConnection = Factory.CreateConnection( _ Factory.ConnectionString) connection.Open() ' build parameters and write Dim customerID As IDbDataParameter = _ Factory.CreateParameter("@CustomerID", cust.CustomerID) customerID.DbType = DbType.String customerID.Size = 5 customerID.Direction = ParameterDirection.Input Dim companyName As IDbDataParameter = _ Factory.CreateParameter("@CompanyName", cust.CompanyName) companyName.DbType = DbType.String companyName.Size = 40 companyName.Direction = ParameterDirection.Input Dim contactName As IDbDataParameter = _ Factory.CreateParameter("@ContactName", cust.ContactName) contactName.DbType = DbType.String contactName.Size = 30 contactName.Direction = ParameterDirection.Input Dim contactTitle As IDbDataParameter = _ Factory.CreateParameter("@ContactTitle", cust.ContactTitle) contactTitle.DbType = DbType.String contactTitle.Size = 30 contactTitle.Direction = ParameterDirection.Input Dim address As IDbDataParameter = _ Factory.CreateParameter("@Address", cust.Address) address.DbType = DbType.String address.Size = 60 address.Direction = ParameterDirection.Input Dim city As IDbDataParameter = _ Factory.CreateParameter("@City", cust.City) city.DbType = DbType.String city.Size = 15 city.Direction = ParameterDirection.Input Dim region As IDbDataParameter = _ Factory.CreateParameter("@Region", cust.Region) region.DbType = DbType.String region.Size = 15 region.Direction = ParameterDirection.Input Dim postalCode As IDbDataParameter = _ Factory.CreateParameter("@PostalCode", cust.PostalCode) postalCode.DbType = DbType.String postalCode.Size = 10 postalCode.Direction = ParameterDirection.Input Dim country As IDbDataParameter = _p> Factory.CreateParameter("@Country", cust.Country) country.DbType = DbType.String country.Size = 15 country.Direction = ParameterDirection.Input Dim As IDbDataParameter = _ Factory.CreateParameter("@Phone", cust.Phone) phone.DbType = DbType.String phone.Size = 24 phone.Direction = ParameterDirection.Input Dim fax As IDbDataParameter = _ Factory.CreateParameter("@Fax", cust.Fax) fax.DbType = DbType.String fax.Size = 24 fax.Direction = ParameterDirection.Input DataAccess.Write(Of Customer)(cust, _ "PutCustomer", Nothing, connection, Nothing, _ customerID, _ companyName, _ contactName, _ contactTitle, _ address, _ city, _ region, _ postalCode, _ country, _ phone, _ fax) End Using End Sub 'ALTER PROCEDURE dbo.GetCustomersAndOrders 'AS 'SELECT * FROM CUSTOMERS * FROM ORDERS Public Shared Function GetCustomersWithOrders() As List(Of Customer) Return DataAccess.Read(Of List(Of Customer)) _ ("GetCustomersAndOrders", _ AddressOf OnReadCustomersWithOrders) End Function Public Shared Function OnReadCustomersWithOrders( _ ByVal reader As IDataReader) As List(Of Customer) Debug.Assert(reader Is Nothing = False) Dim customers As List(Of Customer) = New List(Of Customer)() If (reader Is Nothing) Then Return customers customers = OnReadCustomers(reader) If (reader.NextResult()) Then Dim allOrders As List(Of Orders) = _ DataAccess.OnReadAnyList(Of Orders)(reader) Dim customer As Customer Dim _order As Orders For Each customer In customers For Each _order In allOrders If (_order.CustomerID = customer.CustomerID) Then customer.CustomerOrders.Add(_order) End If Next Next End If Return customers End Function Public Shared Function OnReadCustomers( _ ByVal reader As IDataReader) As List(Of Customer) If (reader Is Nothing) Then Return New List(Of Customer)() Dim customers As List(Of Customer) = New List(Of Customer)() While (reader.Read()) customers.Add(OnReadCustomer(reader)) End While Return customers End Function Private Shared Function OnReadCustomer( _ ByVal reader As IDataReader) As Customer Debug.Assert(reader Is Nothing = False) Dim customerID As String = "" Dim companyName As String = "" Dim contactName As String = "" Dim contactTitle As String = "" Dim address As String = "" Dim city As String = "" Dim region As String = "" Dim postalCode As String = "" Dim country As String = "" Dim phone As String = "" Dim fax As String = "" customerID = DataAccess.SafeRead(Of String)(customerID, _ reader, "CustomerID") companyName = DataAccess.SafeRead(Of String)(companyName, reader, _ "CompanyName") contactName = DataAccess.SafeRead(Of String)(contactName, reader, _ "ContactName") contactTitle = DataAccess.SafeRead(Of String)(contactTitle, reader, _ "ContactTitle") address = DataAccess.SafeRead(Of String)(address, reader, "Address") city = DataAccess.SafeRead(Of String)(city, reader, "City") region = DataAccess.SafeRead(Of String)(region, reader, "Region") postalCode = DataAccess.SafeRead(Of String)(postalCode, reader, _ "PostalCode") country = DataAccess.SafeRead(Of String)(country, reader, "Country") phone = DataAccess.SafeRead(Of String)(phone, reader, "Phone") fax = DataAccess.SafeRead(Of String)(fax, reader, "Fax") Return New Customer(customerID, companyName, contactName, _ contactTitle, address, city, region, postalCode, country, _ phone, fax) End Function End Class
Although this code is straightforward and monolithic, its consistency across entities in any domain promotes code generation. At a minimum, a tool like CodeRush (when it finally offers a VB version) will make writing this code a breeze.