Using List<T> Generic in your data layer
List<T> is basically a better ArrayList. It is optimized for speed, size, and power. Use it for majority of internal implementations whenever you need to store items in a container. This example shows you how you can use a List<T> generic in VB .NET to Save data into the database wrapped in a transaction in your Data access Layer Explanation
- Create a base object say DBConnect that other object to save data to the database will derive from
- Create a SaveAll method that allow an object of type DBConnect to save data in an optional transactional manner
- Create an object such as Clients that derives from DBConnect
- Define a list of Clients objects and call SaveAll to process them as a transaction
See code below
Imports
System.Data
Imports
System.Data.SqlClient
Imports
System.Collections.GenericPublic Class frmClients
Private Sub btnSaveAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAll.ClickDim oClients As New Clients Dim lClients As New List(Of Clients)
Dim records As IntegerDim dt As New DataTable
TryMe.Cursor = Cursors.WaitCursor
dt.Columns.Add("ClientID", GetType(String))
dt.Columns.Add(
"FirstName", GetType(String))dt.Columns.Add("LastName", GetType(String))
dt.Rows.Add(
New Object() {"001", "Wilson", "Kutegeka"})dt.Rows.Add(New Object() {"002", "John", "Magezi"})
dt.Rows.Add(
New Object() {"003", "Peter", "Okello"})dt.Rows.Add(New Object() {"004", "Jane", "Miranda"})
For pos As Integer = 0 To dt.Rows.Count - 1oClients = New Clients With dt.Rows(pos)
If IsDBNull(.Item("ClientID")) ThenoClients.ClientID = String.Empty Else : oClients.ClientID = CStr(.Item("ClientID"))
End If
If IsDBNull(.Item("FirstName")) ThenoClients.FirstName = String.Empty Else : oClients.FirstName = CStr(.Item("FirstName"))
End If
If IsDBNull(.Item("LastName")) ThenoClients.LastName = String.Empty Else : oClients.LastName = CStr(.Item("LastName"))
End If
End With
lClients.Add(oClients)
Nextrecords = SaveAll(lClients, True)
MessageBox.Show(records &
" records inserted!")Catch ex As Exception
MessageBox.Show(ex.Message)
FinallyMe.Cursor = Cursors.Default
End Try
End SubPrivate Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()End Sub
End
Class
#
Region " Objects "Public MustInherit Class DBConnect
#
Region " Fields "
#
End Region
#
Region " Properties "
#
End Region
#
Region " Methods "Friend Overridable Function SaveCommand() As SqlCommand
Return Nothing
End FunctionFriend Overridable Function UpdateCommand() As SqlCommand
Return Nothing
End FunctionFriend Overridable Function DeleteCommand() As SqlCommand
Return Nothing
End Function#End Region
End
ClassPublic Class Clients : Inherits DBConnect
#
Region " Fields "
Private m_ClientID As String
Private m_FirstName As String
Private m_LastName As String
#
End Region
#
Region " Properties "
Public Property ClientID() As String
GetReturn m_ClientID
End GetSet(ByVal Value As String)
m_ClientID = Value
End Set
End Property
Public Property FirstName() As String
GetReturn m_FirstName
End GetSet(ByVal Value As String)
m_FirstName = Value
End Set
End Property
Public Property LastName() As String
GetReturn m_LastName
End GetSet(ByVal Value As String)
m_LastName = Value
End Set
End Property
#
End Region
#
Region " Methods "Friend Overrides Function SaveCommand() As SqlCommandUsing comm As SqlCommand = New SqlCommand("uspInsertClients")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters.AddWithValue("@ClientID", Me.ClientID)
.AddWithValue(
"@FirstName", Me.FirstName).AddWithValue("@LastName", Me.LastName)
End WithReturn comm
End Using
End Function#End Region
End
ClassPublic Module modMain
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' </summary>
''' <param name="_ConString"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString(Optional ByVal _ConString As String = Nothing) As String
Dim serverName As String
Dim databaseName As StringDim conBuilder As New SqlConnectionStringBuilder
serverName =
"(local)"
databaseName =
"Accounting"
If _ConString = Nothing OrElse _ConString = String.Empty Then
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder(
"Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo =
FalseReturn conBuilder.ConnectionString Else : Return _ConString
End If
End Function
''' <summary>
''' Set IsTransaction to true if you want all data to save or all to fail
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <param name="IsTransaction"></param>
''' <returns></returns>
''' <remarks></remarks>
'''
Public Function SaveAll(Of T As DBConnect)(ByRef _List As List(Of T), Optional ByVal IsTransaction As Boolean = False) As Integer
Dim records As IntegerDim comm As SqlCommand
Dim tran As SqlTransaction = NothingUsing conn As New SqlConnection
Try
conn.ConnectionString = ConString()
conn.Open()
If IsTransaction = True Then
tran = conn.BeginTransaction()
For Each list As T In _List
Try
comm = list.SaveCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Transaction was canceled!" & vbCrLf & ex.Message)
End Try
Next
tran.Commit()
ElseIf IsTransaction = False ThenFor Each list As T In _List
Try
comm = list.SaveCommand()
comm.Connection = conn
If comm.ExecuteNonQuery > 0 Then records += 1Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Next
End IfReturn records
Catch eX As SqlExceptionThrow eX
Catch eX As ExceptionThrow eX
Finally
conn.Close()
End Try
End UsingEnd Function
End
Module
#
End Region