List(of 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(of T) generic in Visual Basic to Save data into the database wrapped in a transaction in your Data access Layer
Imports System.Data.SqlClient
Imports System.Collections.Generic
Public Class frmClients
#Region " Fields "
Private clients As New DataTable()
#End Region
Private Sub frmClients_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With clients.Columns
.Add("ClientID", GetType(String))
.Add("FirstName", GetType(String))
.Add("LastName", GetType(String))
End With
With clients.Rows
.Add(New Object() {"001", "Wilson", "Kutegeka"})
.Add(New Object() {"002", "John", "Magezi"})
.Add(New Object() {"003", "Peter", "Okello"})
.Add(New Object() {"004", "Jane", "Miranda"})
End With
Me.dgvClients.DataSource = clients
End Sub
Private Sub btnSaveAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAll.Click
Dim records As Integer
Dim oClients As New Clients()
Dim lClients As New List(Of Clients)
Try
Me.Cursor = Cursors.WaitCursor
For pos As Integer = 0 To clients.Rows.Count - 1
oClients = New Clients()
With clients.Rows(pos)
If IsDBNull(.Item("ClientID")) Then
oClients.ClientID = String.Empty
Else : oClients.ClientID = CStr(.Item("ClientID"))
End If
If IsDBNull(.Item("FirstName")) Then
oClients.FirstName = String.Empty
Else : oClients.FirstName = CStr(.Item("FirstName"))
End If
If IsDBNull(.Item("LastName")) Then
oClients.LastName = String.Empty
Else : oClients.LastName = CStr(.Item("LastName"))
End If
End With
lClients.Add(oClients)
Next
records = SaveAll(lClients, True)
MessageBox.Show(records & " records inserted!")
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub
End Class
#Region " Inside your DAL "
Public MustInherit Class DBConnect
#Region " Fields "
#End Region
#Region " Properties "
#End Region
#Region " Methods "
Public Overridable Function SaveCommand() As SqlCommand
Return Nothing
End Function
Public Overridable Function UpdateCommand() As SqlCommand
Return Nothing
End Function
Public Overridable Function DeleteCommand() As SqlCommand
Return Nothing
End Function
#End Region
End Class
Public 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
Get
Return m_ClientID
End Get
Set(ByVal Value As String)
m_ClientID = Value
End Set
End Property
Public Property FirstName() As String
Get
Return m_FirstName
End Get
Set(ByVal Value As String)
m_FirstName = Value
End Set
End Property
Public Property LastName() As String
Get
Return m_LastName
End Get
Set(ByVal Value As String)
m_LastName = Value
End Set
End Property
#End Region
#Region " Methods "
Public Overrides Function SaveCommand() As SqlCommand
Using 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 With
Return comm
End Using
End Function
#End Region
End Class
Public Module Program
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' </summary>
''' <param name="_ConString"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString(ByVal _ConString As String) As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
If String.IsNullOrEmpty(_ConString) Then
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
Return conBuilder.ConnectionString
Else : Return _ConString
End If
End Function
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' and uses the default connection string
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString() As String
Return ConString(String.Empty)
End Function
''' <summary>
''' Saves all objects of type DBConnect in the supplied list and returns number of records saved.
''' If IsTransaction is true, all data will save or all will fail. Otherwise only data with no error(s) will save.
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <param name="IsTransaction"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function SaveAll(Of T As DBConnect)(ByVal _List As List(Of T), ByVal IsTransaction As Boolean) As Integer
Dim records As Integer
Using conn As New SqlConnection()
Try
If _List Is Nothing OrElse _List.Count < 1 Then Throw New ArgumentException("List is not set or it’s empty!")
conn.ConnectionString = ConString()
conn.Open()
If IsTransaction = True Then
Using tran As SqlTransaction = conn.BeginTransaction()
For Each list As T In _List
Try
Using comm As SqlCommand = list.SaveCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while saving, transaction canceled!" & vbCrLf & ex.Message)
End Try
Next
tran.Commit()
End Using
ElseIf IsTransaction = False Then
For Each list As T In _List
Try
Using comm As SqlCommand = list.SaveCommand()
comm.Connection = conn
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Next
End If
Return records
Catch eX As SqlException
Throw eX
Catch eX As Exception
Throw eX
Finally
conn.Close()
End Try
End Using
End Function
''' <summary>
''' Saves all objects of type DBConnect in the supplied list and returns number of records saved.
''' Only data with no error(s) will save.
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function SaveAll(Of T As DBConnect)(ByVal _List As List(Of T)) As Integer
Return SaveAll(_List, False)
End Function
''' <summary>
''' Function used to fade out a closing form using a user defined number of steps
''' </summary>
''' <param name="_Form"></param>
''' <param name="NumberOfSteps"></param>
''' <remarks></remarks>
Public Sub FadeClosingForm(ByVal _form As Form, ByVal numberOfSteps As Byte)
Dim stepValue As Double = Convert.ToDouble(100.0F / numberOfSteps)
Dim dOpacity As Double = 100.0F
For b As Byte = 0 To numberOfSteps
_form.Opacity = dOpacity / 100
_form.Refresh()
dOpacity -= stepValue
Next
End Sub
End Module
#End Region