Code Generation; Generate Visual Basic Code Automatically

Introduction

 If you develop multitier applications that use a consistent data access framework, you will realize that most of your codes follow a certain pattern, and can be generated automatically.

This code generator is based on a Data Access Framework (DAF) from my earlier post.

Building a Data Access Framework (DAF)

It presents a simple way of building own code generator, you can build a tool that will automatically generate code at presentation and middle layers, importantly in a way that you want them to be.

Generate Visual Basic Code Automatically

Form that generates the Visual Basic Code

If you have an object say Customers as shown in form above, the code generator will create three files that you need to add to you’re solution

  • Customers Object.vb

This contains the Customers class, which defines methods such as SaveData, UpdateData, DeleteData, GetCustomers etc.

  • frmCustomers.Designer.vb

This is the partial class for the form frmCustomers

  • frmCustomers.vb

This contains code behind the form frmCustomers

 Key objects/methods in this code generator include:-

Fields
This class helps to work with key object’s properties that are enough for us to create the codes and user interfaces.

Public Class Fields

 

#Region ” Fields “

 

    Private m_TableName As String

 

    Private m_Name As String

    Private m_Caption As String

    Private m_DataType As String

    Private m_Key As String

    Private m_References As String

 

#End Region

 

#Region ” Properties “

 

    Public Property TableName() As String

        Get

            Return m_TableName

        End Get

        Set(ByVal value As String)

            m_TableName = value

        End Set

    End Property

 

    Public Property Name() As String

        Get

            Return m_Name

        End Get

        Set(ByVal value As String)

            m_Name = value

        End Set

    End Property

 

    Public Property Caption() As String

        Get

            Return m_Caption

        End Get

        Set(ByVal value As String)

            m_Caption = value

        End Set

    End Property

 

    Public Property DataType() As String

        Get

            Return m_DataType

        End Get

        Set(ByVal value As String)

            m_DataType = value

        End Set

    End Property

 

    Public Property Key() As String

        Get

            Return m_Key

        End Get

        Set(ByVal value As String)

            m_Key = value

        End Set

    End Property

 

    Public Property References() As String

        Get

            Return m_References

        End Get

        Set(ByVal value As String)

            m_References = value

        End Set

    End Property

 

#End Region

 

#Region ” Constructors “

 

    Public Sub New()

        MyBase.New()

    End Sub

 

    Public Sub New(ByVal tableName As String)

        MyClass.New()

        Me.TableName = tableName

    End Sub

 

#End Region

 

End Class

FieldsList
This method returns the object’s properties list that has been set through the datagridview

Private Function FieldsList() As List(Of Fields)

 

        Dim name As String

        Dim caption As String

        Dim dataType As String

        Dim key As String

        Dim references As String

 

        Dim lFields As New List(Of Fields)

 

        Try

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._ClassName = Me.txtClassName.Text

            ”””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

 

            If Me.dgvFields.RowCount <= 1 Then Throw New ArgumentException(“Must Register At least one field!”)

 

            For Each row As DataGridViewRow In Me.dgvFields.Rows

 

                If row.IsNewRow Then Exit For

 

                name = CStr(row.Cells.Item(Me.colName.Name).Value)

                If String.IsNullOrEmpty(name) Then Throw New ArgumentException(“All entries for name must be entered!”)

 

                dataType = CStr(row.Cells.Item(Me.colDataType.Name).Value)

                If String.IsNullOrEmpty(dataType) Then Throw New ArgumentException(“All entries for data type must be selected!”)

 

                key = CStr(row.Cells.Item(Me.colKey.Name).Value)

                If String.IsNullOrEmpty(key) Then

                    Throw New ArgumentException(“All entries for key must be selected!”)

                ElseIf key.ToUpper().Equals(“Foreign”.ToUpper()) Then

                    references = CStr(row.Cells.Item(Me.colReferences.Name).Value)

                    If String.IsNullOrEmpty(references) Then Throw New ArgumentException(“References must have a value for every key set as foreign!”)

                End If

 

            Next

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

 

            For rowNumber As Integer = 0 To Me.dgvFields.RowCount – 2

 

                Dim oFields As Fields = New Fields(Me._ClassName)

 

                name = CStr(Me.dgvFields.Item(Me.colName.Name, rowNumber).Value)

                caption = CStr(Me.dgvFields.Item(Me.colCaption.Name, rowNumber).Value)

                If String.IsNullOrEmpty(caption) Then caption = String.Empty

                dataType = CStr(Me.dgvFields.Item(Me.colDataType.Name, rowNumber).Value)

                key = CStr(Me.dgvFields.Item(Me.colKey.Name, rowNumber).Value)

                references = CStr(Me.dgvFields.Item(Me.colReferences.Name, rowNumber).Value)

                If String.IsNullOrEmpty(references) Then references = String.Empty

 

                With oFields

                    .TableName = Me._ClassName

                    .Name = name

                    .Caption = caption

                    .DataType = dataType

                    .Key = key

                    .References = references

                End With

 

                lFields.Add(oFields)

            Next

 

            Return lFields

 

        Catch ex As Exception

            Throw ex

 

        End Try

 

    End Function

GenerateFormClass
This method generates the code behind form

Private Sub GenerateFormClass()

 

        Try

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FieldsList = FieldsList()

            Dim outputDirectory As String = Me.txtOutputDirectory.Text

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            If String.IsNullOrEmpty(outputDirectory) Then Throw New ArgumentException(“Enter out put directory!”)

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            If Not Directory.Exists(outputDirectory) Then Directory.CreateDirectory(outputDirectory)

            Dim path As String = outputDirectory + “frm” + Me._ClassName + “.vb”

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Option Strict On”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Public Class frm” + Me._ClassName)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.Append(“#Region “” Fields “”")

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“#End Region”)

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Private Sub frm” + Me._ClassName + “_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.WaitCursor()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Using o” + Me._ClassName + ” As New DAFSampleData.” + Me._ClassName + “(“”.”", “”DAFSample”")”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“o” + Me._ClassName + “.SetConString(DAF.SQLDb.SQLConnectionMode.WindowsAuthentication)”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Using”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Catch ex As Exception”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“MessageBox.Show(ex.Message)”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Finally”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.Default()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Private Sub frm” + Me._ClassName + “_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“If e.KeyCode = Keys.Enter Then Me.ProcessTabKey(True)”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

 

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Close()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.Append(“Private Sub ResetControls()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“For Each ctlReset As Control In Me.Controls”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“If TypeOf ctlReset Is TextBox Then”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“CType(ctlReset, TextBox).Clear()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“ElseIf TypeOf ctlReset Is DateTimePicker Then”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“CType(ctlReset, DateTimePicker).Value = Today”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“CType(ctlReset, DateTimePicker).Checked = True”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End If”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Next”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.Append(“Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.WaitCursor()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Using o” + Me._ClassName + ” As New DAFSampleData.” + Me._ClassName + “()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“With o” + Me._ClassName)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(Me.SetDeleteParameters)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Dim deleteMSG As String = .Delete”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“MessageBox.Show(deleteMSG)”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Me.ResetControls()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End With”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Using”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Catch ex As Exception”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“MessageBox.Show(ex.Message)”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Finally”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.Default()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.Append(“Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(Me.KeyDeclarationList)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Dim o” + Me._ClassName + ” As New DAFSampleData.” + Me._ClassName + “()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Dim “ + Me._ClassName.Substring(0, 1).ToLower() + Me._ClassName.Substring(1) + ” As New DataTable()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.WaitCursor()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(Me.SetSearchParameters)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(Me._ClassName.Substring(0, 1).ToLower() + Me._ClassName.Substring(1) + ” = o”)

            Me._FormClass.Append(Me._ClassName + “.Get” + Me._ClassName + “(“)

            Me._FormClass.Append(Me.KeyArgumentsList)

            Me._FormClass.Append(“).Tables(“”" + Me._ClassName + “”")”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.ResetControls()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Dim dataSource As EnumerableRowCollection(Of DataRow) = “)

            Me._FormClass.Append(Me._ClassName.Substring(0, 1).ToLower() + Me._ClassName.Substring(1) + “.AsEnumerable()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(Me.LoadDataControls)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Catch ex As Exception”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“MessageBox.Show(ex.Message)”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Finally”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.Default()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.Append(“Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.WaitCursor()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Using o” + Me._ClassName + ” As New DAFSampleData.” + Me._ClassName + “()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“With o” + Me._ClassName)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(Me.SetObjectParameters)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“If .Save() Then MessageBox.Show(“”record successfully saved!”")”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Me.ResetControls()”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End With”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Using”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Catch ex As Exception”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“MessageBox.Show(ex.Message)”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Finally”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.Default()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormClass.Append(“Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.WaitCursor()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Using o” + Me._ClassName + ” As New DAFSampleData.” + Me._ClassName + “()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“With o” + Me._ClassName)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(Me.SetObjectParameters)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“Dim updateMSG As String = .Update”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“MessageBox.Show(updateMSG)”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“”””””””””””””””””””””””””””””””””””””””””””””””)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End With”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Using”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Catch ex As Exception”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“MessageBox.Show(ex.Message)”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Finally”)

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“Me.Cursor = Cursors.Default()”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(ControlChars.Tab)

            Me._FormClass.Append(“End Try”)

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Sub”)

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FormClass.AppendLine()

            Me._FormClass.AppendLine()

            Me._FormClass.Append(“End Class”)

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            If Me._FormClass.Length > 0 Then

                File.WriteAllText(path, Me._FormClass.ToString())

                Me._FormClass.Remove(0, Me._FormClass.Length)

                MessageBox.Show(“Form Class, generated successfully!”, “Code Generator!”, MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If

 

        Catch IOex As IOException

            MessageBox.Show(IOex.Message, “Code Generator!”, MessageBoxButtons.OK, MessageBoxIcon.Information)

 

        Catch ex As Exception

            MessageBox.Show(ex.Message, “Code Generator!”, MessageBoxButtons.OK, MessageBoxIcon.Information)

 

        End Try

 

    End Sub

GenerateFormPartialClass
This method generates the form’s partial class

Private Sub GenerateFormPartialClass()

 

        Try

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FieldsList = FieldsList()

            Dim outputDirectory As String = Me.txtOutputDirectory.Text

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            If String.IsNullOrEmpty(outputDirectory) Then Throw New ArgumentException(“Enter out put directory!”)

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            If Not Directory.Exists(outputDirectory) Then Directory.CreateDirectory(outputDirectory)

            Dim path As String = outputDirectory + “frm” + Me._ClassName + “.Designer.vb”

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Partial Class frm” + _ClassName + ” : Inherits System.Windows.Forms.Form”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘Form overrides dispose to clean up the component list.”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“<System.Diagnostics.DebuggerNonUserCode()> _”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Protected Overrides Sub Dispose(ByVal disposing As Boolean)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(ControlChars.Tab)

            Me._FormPartialClass.Append(“If disposing AndAlso components IsNot Nothing Then”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(ControlChars.Tab)

            Me._FormPartialClass.Append(ControlChars.Tab)

            Me._FormPartialClass.Append(“components.Dispose()”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(ControlChars.Tab)

            Me._FormPartialClass.Append(“End If”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(ControlChars.Tab)

            Me._FormPartialClass.Append(“MyBase.Dispose(disposing)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“End Sub”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘Required by the Windows Form Designer”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Private components As System.ComponentModel.IContainer”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘NOTE: The following procedure is required by the Windows Form Designer”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘It can be modified using the Windows Form Designer.”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘Do not modify it using the code editor.”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“<System.Diagnostics.DebuggerStepThrough()> _”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Private Sub InitializeComponent()”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad = New System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete = New System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave = New System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate = New System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose = New System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            ‘ define other controls

            Me._FormPartialClass.Append(Me.InitializeControls())

            Me._FormPartialClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FormPartialClass.Append(“Me.SuspendLayout()”)

            Me._FormPartialClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            ‘ set other controls properties

            Me._FormPartialClass.Append(Me.SetControlProperties())

            Me._FormPartialClass.AppendLine()

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._ControlY = Me._ControlY + 30

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘btnSave”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.FlatAppearance.BorderColor = System.Drawing.Color.DarkBlue”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.FlatStyle = System.Windows.Forms.FlatStyle.Flat”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.Location = New System.Drawing.Point(17, “ + Me._ControlY.ToString() + “)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.Name = “”btnSave”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.Size = New System.Drawing.Size(77, 23)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.Tag = “”" + _ClassName + “”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.Text = “”&Save”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnSave.UseVisualStyleBackColor = False”)

            Me._FormPartialClass.AppendLine()

 

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘btnDelete”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.FlatAppearance.BorderColor = System.Drawing.Color.DarkBlue”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.FlatStyle = System.Windows.Forms.FlatStyle.Flat”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.Location = New System.Drawing.Point(316, “ + Me._ControlY.ToString() + “)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.Name = “”btnDelete”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.Size = New System.Drawing.Size(72, 24)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.Tag = “”" + _ClassName + “”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.Text = “”&Delete”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnDelete.UseVisualStyleBackColor = False”)

            Me._FormPartialClass.AppendLine()

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._ControlY = Me._ControlY + 27

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘btnLoad”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.FlatAppearance.BorderColor = System.Drawing.Color.DarkBlue”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.FlatStyle = System.Windows.Forms.FlatStyle.Flat”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.Location = New System.Drawing.Point(17, “ + Me._ControlY.ToString() + “)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.Name = “”btnLoad”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.Size = New System.Drawing.Size(77, 23)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.Text = “”&Load”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnLoad.UseVisualStyleBackColor = True”)

            Me._FormPartialClass.AppendLine()

 

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘btnUpdate”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.FlatAppearance.BorderColor = System.Drawing.Color.DarkBlue”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.FlatStyle = System.Windows.Forms.FlatStyle.Flat”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.Location = New System.Drawing.Point(170, “ + Me._ControlY.ToString() + “)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.Name = “”btnUpdate”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.Size = New System.Drawing.Size(77, 23)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.Tag = “”" + _ClassName + “”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.Text = “”&Update”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnUpdate.UseVisualStyleBackColor = False”)

            Me._FormPartialClass.AppendLine()

 

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘btnClose”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.DialogResult = System.Windows.Forms.DialogResult.Cancel”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.FlatAppearance.BorderColor = System.Drawing.Color.DarkBlue”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.FlatStyle = System.Windows.Forms.FlatStyle.Flat”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.Location = New System.Drawing.Point(316, “ + Me._ControlY.ToString() + “)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.Name = “”btnClose”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.Size = New System.Drawing.Size(72, 24)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.Text = “”&Close”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.btnClose.UseVisualStyleBackColor = False”)

            Me._FormPartialClass.AppendLine()

 

            ‘ Set Form

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._ControlY = Me._ControlY + 50

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘frm” + _ClassName + “”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“‘”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.CancelButton = Me.btnClose”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.ClientSize = New System.Drawing.Size(415,  “ + Me._ControlY.ToString() + “)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Controls.Add(Me.btnLoad)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Controls.Add(Me.btnDelete)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Controls.Add(Me.btnSave)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Controls.Add(Me.btnUpdate)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Controls.Add(Me.btnClose)”)

            Me._FormPartialClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            ‘ Add form controls

            Me._FormPartialClass.Append(Me.AddFormControls())

            Me._FormPartialClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me._FormPartialClass.Append(“Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.KeyPreview = True”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.MaximizeBox = False”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Name = “”frm” + _ClassName + “”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.Text = “”" + _ClassName + “”"”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.ResumeLayout(False)”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Me.PerformLayout()”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“End Sub”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.AppendLine()

 

            Me._FormPartialClass.Append(“Friend WithEvents btnLoad As System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Friend WithEvents btnDelete As System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Friend WithEvents btnSave As System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Friend WithEvents btnUpdate As System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“Friend WithEvents btnClose As System.Windows.Forms.Button”)

            Me._FormPartialClass.AppendLine()

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            ‘ Declare other controls

            Me._FormPartialClass.Append(Me.DeclareControls())

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.AppendLine()

            Me._FormPartialClass.Append(“End Class”)

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

            If Me._FormPartialClass.Length > 0 Then

                File.WriteAllText(path, Me._FormPartialClass.ToString())

                Me._FormPartialClass.Remove(0, Me._FormPartialClass.Length)

                MessageBox.Show(“Form Partial Class, generated successfully!”, “Code Generator!”, MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If

 

        Catch IOex As IOException

            MessageBox.Show(IOex.Message, “Code Generator!”, MessageBoxButtons.OK, MessageBoxIcon.Information)

 

        Catch ex As Exception

            MessageBox.Show(ex.Message, “Code Generator!”, MessageBoxButtons.OK, MessageBoxIcon.Information)

 

        End Try

 

    End Sub

SaveXML
This method saves the set object properties as an XML file

Private Sub SaveXML(ByVal fileName As String, ByVal data As List(Of Fields))

 

        Dim encoding As New UnicodeEncoding()

        Dim XMLWriter As New XmlTextWriter(fileName, encoding)

 

        Try

 

            With XMLWriter

                .Formatting = Formatting.Indented

                .Indentation = 3

                .WriteStartDocument()

                .WriteStartElement(Me._ClassName)

            End With

 

            ”””””””””””””””””””””””””””””””””””””””””

            For Each oFields As Fields In data

                With XMLWriter

                    .WriteStartElement(“Field”)

                    .WriteAttributeString(“Name”, oFields.Name)

                    .WriteAttributeString(“Caption”, oFields.Caption)

                    .WriteAttributeString(“DataType”, oFields.DataType)

                    .WriteAttributeString(“Key”, oFields.Key)

                    .WriteAttributeString(“References”, oFields.References)

                    .WriteEndElement()

                End With

            Next

            ”””””””””””””””””””””””””””””””””””””””””

            XMLWriter.WriteEndElement()

            ”””””””””””””””””””””””””””””””””””””””””

 

        Catch XMLex As XmlException

            Throw XMLex

 

        Catch ex As Exception

            Throw ex

 

        Finally

            XMLWriter.Close()

 

        End Try

 

    End Sub

OpenXML
This method opens the saved XML file object properties

Private Sub OpenXML(ByVal fileName As String)

 

        Try

 

            Dim xmlDatadoc As New XmlDataDocument()

            Dim fields = New DataTable(Me._ClassName)

 

            ””””””””””””””””””””””””””””””””””””””””””””””””””

            Me.txtClassName.Clear()

            Me.dgvFields.Rows.Clear()

            ””””””””””””””””””””””””””””””””””””””””””””””””””

 

            xmlDatadoc.DataSet.ReadXml(fileName)

            fields = xmlDatadoc.DataSet.Tables(“Field”)

 

            If fields Is Nothing OrElse fields.Rows.Count < 1 Then Return

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me.txtClassName.Text = xmlDatadoc.FirstChild.Name

 

            For pos As Integer = 0 To fields.Rows.Count – 1

 

                ‘ Ensure that you add a new row

                Me.dgvFields.Rows.Add()

 

                Me.dgvFields.Item(Me.colName.Name, pos).Value = fields.Rows(pos).Item(“Name”)

                Me.dgvFields.Item(Me.colCaption.Name, pos).Value = fields.Rows(pos).Item(“Caption”)

                Me.dgvFields.Item(Me.colDataType.Name, pos).Value = fields.Rows(pos).Item(“DataType”)

                Me.dgvFields.Item(Me.colKey.Name, pos).Value = fields.Rows(pos).Item(“Key”)

                Me.dgvFields.Item(Me.colReferences.Name, pos).Value = fields.Rows(pos).Item(“References”)

 

            Next

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

        Catch XMLex As XmlException

            Throw XMLex

 

        Catch ex As Exception

            Throw ex

 

        End Try

 

    End Sub

Find full source code in the attached file (Generate_VB_Automatically.zip)

Wilson Kutegeka | Microsoft MVP – VB | C#
Developer | Promoter | ClinicMaster Software

Cel: +256 772 609113 | Web:
www.clinicmaster.net

follow me on twitter find me on facebook

 

Building a Data Access Framework (DAF)

Introduction

Imagine you need to architect a multitier, distributed .NET-based app with three logical layers—presentation and interface services, business logic with core functionalities, and data access, where all database stuff and messaging happens. For this type of application, layering is the key. If the lowest layer could be completely ignorant of the upper layers, the system would be nearly perfect.

When designing a layered system you must consider factors such as the ability to cascade changes (like new data schemas) through layers and the amount of overhead involved when data is moved from one layer to the next. Plus you need a business tier to execute your business logic, and you need a Data Access Framework (DAF) to provide Create/Read/Update/Delete (CRUD) functions to the rest of the system.

There are two primary ways to provide this functionality—by using a commercial Object-Relational Mapping (O-RM) tool or by rolling your own DAF. This post shows you the best way to go that is create your own.

DAF Design Issues

Normalized relational databases and objects generally don’t have one-to-one matchups between them. This means that someone has to write code that transmogrifies normalized rows and columns into business objects. This code is often called the Data Access Layer (DAL), which poses three challenges to the developer.

The first challenge of a DAL is that a relational database defines four DB operations: insert, update, delete, and select. In the object-oriented programming (OOP) world, the first thing developers can do to deal with these four behaviors is simplify them to two OOP behaviors: read and write. A select is the read, and update, insert, and delete are all consolidated into the write.

The second thing developers need to do is realize that business objects don’t need—and shouldn’t have—internal read and write baggage. Why? Because every business object would need redundant code for reading and writing. This code can be and should be factored out just as it’s done in DataSets and DataTables. Factoring out read/write shrinks the overall size of the code base.

Finally, the only real differences between any read and write are the SQL that performs the action, the inputs and outputs, and how these inputs and outputs are handled. Everything else about reading and writing is a connection, transaction, command, or reader, and the initiator—the behavior that sends the request to your database server. This means developers can factor out all of the latter behaviors and reduce the number of times they need to write the connect, transact, and command behavior.

Collectively, these three elements—consolidate behaviors to read and write, factor out persistence from business objects, and separate basic database operations from object-specific details—make up the collective data access solution this article provides.

 DAF Design Objectives

  • Reusable from one entity to the other and across different presentation layers.
  • Use Object-Oriented Features and adhere to design guidelines.
  • Be able to employ the best method(s) of accessing the database such as having a good stored procedure layer set up for CRUD (Create, Retrieve, Update, Delete) operations and using stored procedures makes a very clear delineation between areas of responsibility.
  • Abstract the .NET Framework Data Providers.
  • Be in control of all the details of your application.
  • Be compatible to the traditional multitier or n-tier application development approach.
  • Applications are freed from hard-coded dependencies on a particular data engine or logical model.
  • Mappings between the conceptual model and the storage-specific logical model can change without changing the application code.
  • Independent of the underlying database; Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
  • Multiple application models can be mapped to a single storage schema.

 Key Objects in the DAF

The DAF logically groups objects into those that can be implemented/inherited by any data provider objects and those objects that are data provider specific.

Common to all data provider objects found in the DAF.Common namespace are as follows:-

Interfaces

  • IData; has properties such as LoginID, LoginPassword, DatabaseName, UserID, DBPassword, Parameters, ReadData and SplitChar that are to be defined by any class that implements it.
  • IDbData; Inherits IDisposable and has methods such as Save, SaveData, Update, UpdateData, Delete, DeleteData, Load, Read, Execute and SetCommand.

 Classes

  • Data; implements IData Interface.
  • Parameter; defines two properties, Name and Value for the parameter array.

 Here are Provider Specific objects found in DAF.SQLDb namespace that inherit/implement the above interfaces. This post looks at SQL Server whose objects include:-

Interface

  • ISQLData; has properties such as ConString, Connection, Command, DataReader, ServerName and methods that include SaveCommand, UpdateCommand, DeleteCommand and SetConString.

 Enumeration

  • SQLConnectionMode; that helps to set connection mode to WindowsAuthentication or SQLServerAuthentication.

Classes

  • ParameterSQL; Inherits Parameter class and adds properties such as SQLDataType, ParDirection and Length.
  • DBConnect; Perhaps this is the most important class that is inherited by any object that accesses the database. It Inherits the Data class, Implements ISQLData Interface and defines a couple of properties such as ConString, Connection, Command, DataReader, ServerName and methods that include SetConString, Save, SaveData, SaveCommand, Update, UpdateData, Delete, DeleteData, DeleteCommand, SetCommand, Load, Execute, Read and Dispose.

Public MustInherit Class DBConnect : Inherits Data : Implements ISQLData

 

#Region ” Fields “

 

        Private Shared m_ConString As String

        Private _connection As New SqlConnection()

        Private _command As SqlCommand

        Private _dataReader As SqlDataReader

        Private Shared m_ServerName As String

 

#End Region

 

#Region ” Properties “

 

        Public Property ConString() As String Implements ISQLData.ConString

            Get

                Return m_ConString

            End Get

            Set(ByVal Value As String)

                m_ConString = Value

            End Set

        End Property

 

        Protected Property Connection() As SqlConnection Implements ISQLData.Connection

            Get

                Return _connection

            End Get

            Set(ByVal Value As SqlConnection)

                _connection = Value

            End Set

        End Property

 

        Protected Property Command() As SqlCommand Implements ISQLData.Command

            Get

                Return _command

            End Get

            Set(ByVal Value As SqlCommand)

                _command = Value

            End Set

        End Property

 

        Protected Property DataReader() As SqlDataReader Implements ISQLData.DataReader

            Get

                Return _dataReader

            End Get

            Set(ByVal Value As SqlDataReader)

                _dataReader = Value

            End Set

        End Property

 

        Public Property ServerName() As String Implements ISQLData.ServerName

            Get

                Return m_ServerName

            End Get

            Set(ByVal Value As String)

                m_ServerName = Value

            End Set

        End Property

 

#End Region

 

#Region ” Methods “

 

        ”’ <summary>

        ”’ Sets the MS SQL connection mode and connection string

        ”’ </summary>

        ”’ <param name=”_SQLConnectionMode”></param>

        ”’ <param name=”_ConString”></param>

        ”’ <remarks></remarks>

        Public Overloads Sub SetConString(ByVal _SQLConnectionMode As SQLConnectionMode, ByVal _ConString As String) _

                                Implements ISQLData.SetConString

 

            Dim conBuilder As New SqlConnectionStringBuilder()

 

            If String.IsNullOrEmpty(_ConString) Then

 

                Select Case True

 

                    Case _SQLConnectionMode = SQLConnectionMode.WindowsAuthentication

 

                        conBuilder.Clear()

                        conBuilder(“Server”) = Me.ServerName

                        conBuilder.InitialCatalog = Me.DatabaseName

                        conBuilder(“Integrated Security”) = “SSPI”

                        conBuilder.PersistSecurityInfo = False

                        ‘conBuilder.PacketSize = 4096

 

                        Me.ConString = conBuilder.ConnectionString()

 

                    Case _SQLConnectionMode = SQLConnectionMode.SQLServerAuthentication

 

                        With conBuilder

                            .Clear()

                            .DataSource = Me.ServerName

                            .InitialCatalog = Me.DatabaseName

                            .PersistSecurityInfo = True

                            .UserID = Me.UserID

                            .Password = Me.DBPassword

                        End With

 

                        Me.ConString = conBuilder.ConnectionString()

 

                End Select

 

            Else : Me.ConString = _ConString

 

            End If

 

        End Sub

 

        ”’ <summary>

        ”’ Sets the MS SQL connection mode

        ”’ </summary>

        ”’ <param name=”_SQLConnectionMode”></param>

        ”’ <remarks></remarks>

        Public Overloads Sub SetConString(ByVal _SQLConnectionMode As SQLConnectionMode) Implements ISQLData.SetConString

            Me.SetConString(_SQLConnectionMode, String.Empty)

        End Sub

 

        ”’ <summary>

        ”’ Saves the objects data and if successful true is returned otherwise false

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Overridable Function Save() As Boolean Implements ISQLData.Save

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = Me.ConString

 

                    ‘ Clean up to ensure that you supply only the required paraments

                    Me.Parameters.Clear()

 

                    Dim arrParameters As ArrayList = SaveData()

 

                    For pos As Integer = 0 To arrParameters.Count – 1

                        With CType(arrParameters(pos), ParameterSQL)

                            Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                        End With

                    Next

 

                    Try

 

                        Me.Connection.Open()

                        Return Me.Command.ExecuteNonQuery > 0

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

                    End Try

 

                End Using

            End Using

 

        End Function

 

        Protected Overridable Function SaveData() As ArrayList Implements ISQLData.SaveData

            Return Nothing

        End Function

 

        Friend Overridable Function SaveCommand() As SqlCommand Implements ISQLData.SaveCommand

 

            ‘ Clean up to ensure that you supply only the required paraments

            Me.Parameters.Clear()

 

            Dim arrParameters As ArrayList = SaveData()

 

            For pos As Integer = 0 To arrParameters.Count – 1

                With CType(arrParameters(pos), ParameterSQL)

                    Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                End With

            Next

 

            If Not Me.Parameters Is Nothing Then Me.Parameters.Clear()

            Return Me.Command

 

        End Function

 

        ”’ <summary>

        ”’ Updates the object’s data and returns message telling the number of records updated

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Overridable Overloads Function Update() As String Implements ISQLData.Update

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = Me.ConString

 

                    ‘ Clean up to ensure that you supply only the required paraments

                    Me.Parameters.Clear()

                    Dim arrParameters As ArrayList = UpdateData()

 

                    For pos As Integer = 0 To arrParameters.Count – 1

                        With CType(arrParameters(pos), ParameterSQL)

                            Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                        End With

                    Next

 

                    Try

 

                        Me.Connection.Open()

 

                        Dim updateMessage As String = Me.Command.ExecuteNonQuery().ToString()

                        Return “Record successfully updated!”

 

                    Catch eX As SqlException

                        Throw eX

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

                    End Try

 

                End Using

            End Using

 

        End Function

 

        Protected Overridable Function UpdateData() As ArrayList Implements ISQLData.UpdateData

            Return Nothing

        End Function

 

        Friend Overridable Function UpdateCommand() As SqlCommand Implements ISQLData.UpdateCommand

 

            ‘ Clean up to ensure that you supply only the required paraments

            Me.Parameters.Clear()

            Dim arrParameters As ArrayList = UpdateData()

 

            For pos As Integer = 0 To arrParameters.Count – 1

                With CType(arrParameters(pos), ParameterSQL)

                    Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                End With

            Next

 

            If Not Me.Parameters Is Nothing Then Me.Parameters.Clear()

            Return Me.Command

 

        End Function

 

        ”’ <summary>

        ”’ Deletes Object data and returns a message telling you the number of records deleted

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Overridable Overloads Function Delete() As String Implements ISQLData.Delete

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = Me.ConString

 

                    ‘ Clean up to ensure that you supply only the required paraments

                    Me.Parameters.Clear()

                    Dim arrParameters As ArrayList = DeleteData()

 

                    For pos As Integer = 0 To arrParameters.Count – 1

                        With CType(arrParameters(pos), ParameterSQL)

                            Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                        End With

                    Next

 

                    Try

                        Me.Connection.Open()

 

                        Dim deleteMessage As String = Me.Command.ExecuteNonQuery().ToString()

                        Return “Record successfully deleted!”

 

                    Catch eX As SqlException

                        Throw eX

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

                    End Try

 

                End Using

            End Using

 

        End Function

 

        Protected Overridable Function DeleteData() As ArrayList Implements ISQLData.DeleteData

            Return Nothing

        End Function

 

        Friend Overridable Function DeleteCommand() As SqlCommand Implements ISQLData.DeleteCommand

 

            ‘ Clean up to ensure that you supply only the required paraments

            Me.Parameters.Clear()

            Dim arrParameters As ArrayList = DeleteData()

 

            For intPos As Integer = 0 To arrParameters.Count – 1

                With CType(arrParameters(intPos), ParameterSQL)

                    Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                End With

            Next

 

            If Not Me.Parameters Is Nothing Then Me.Parameters.Clear()

 

            Return Me.Command

 

        End Function

 

        Protected Overloads Sub SetCommand(ByVal _SPName As String, ByVal ctName As CommandType) Implements ISQLData.SetCommand

            Me.Command = New SqlCommand(_SPName, Me.Connection)

            Me.Command.CommandType = ctName

        End Sub

 

        Protected Overloads Sub SetCommand(ByVal _SPName As String) Implements ISQLData.SetCommand

            Me.Command = New SqlCommand(_SPName, Me.Connection)

            Me.Command.CommandType = CommandType.StoredProcedure

        End Sub

 

        ”’ <summary>

        ”’ Returns data from the database as a dataset

        ”’ </summary>

        ”’ <param name=”_SPName”></param>

        ”’ <param name=”_tables”></param>

        ”’ <param name=”_Parameters”></param>

        ”’ <param name=”ctName”></param>

        ”’ <param name=”commTimeout”></param>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String, ByVal _Parameters As ArrayList, _

                                ByVal ctName As CommandType, ByVal commTimeout As Integer) As DataSet Implements ISQLData.Load

 

            Using Me.Connection

 

                Me.Connection.ConnectionString = MyClass.ConString

 

                Dim tables() As String = _tables.Split(Me.SplitChar)

                Dim tableName As String = “Table”

 

                Using daSQL As SqlDataAdapter = New SqlDataAdapter(_SPName, Me.Connection)

                    Using dsSQL As DataSet = New DataSet(tables(tables.GetLowerBound(0)))

 

                        daSQL.SelectCommand.CommandType = ctName

                        daSQL.SelectCommand.CommandTimeout = commTimeout

                        If IsNothing(_Parameters) = False Then

                            For pos As Integer = 0 To _Parameters.Count – 1

                                With CType(_Parameters(pos), ParameterSQL)

                                    daSQL.SelectCommand.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                                End With

                            Next

                        End If

 

                        Try

 

                            dsSQL.EnforceConstraints = False

 

                            With daSQL

 

                                .MissingMappingAction = MissingMappingAction.Passthrough

                                .MissingSchemaAction = MissingSchemaAction.AddWithKey

 

                                For pos As Integer = tables.GetLowerBound(0) To tables.GetUpperBound(0)

                                    If pos = 0 Then

                                        .TableMappings.Add(tableName, tables(pos).Trim())

                                    Else : .TableMappings.Add(tableName & pos, tables(pos).Trim())

                                    End If

                                Next

 

                                .Fill(dsSQL)

 

                            End With

 

                            dsSQL.EnforceConstraints = True

 

                            Return dsSQL

 

                        Catch eX As SqlException

                            Throw eX

 

                        Catch eX As Exception

                            Throw eX

 

                        Finally

                            If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                            Me.Connection.Close()

 

                        End Try

 

                    End Using

                End Using

            End Using

 

        End Function

 

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String, ByVal _Parameters As ArrayList, _

                               ByVal ctName As CommandType) As DataSet Implements ISQLData.Load

            Return Me.Load(_SPName, _tables, _Parameters, ctName, 0)

        End Function

 

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String, ByVal _Parameters As ArrayList) As DataSet Implements ISQLData.Load

            Return Me.Load(_SPName, _tables, _Parameters, CommandType.StoredProcedure, 0)

        End Function

 

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String) As DataSet Implements ISQLData.Load

            Return Me.Load(_SPName, _tables, Nothing, CommandType.StoredProcedure, 0)

        End Function

 

        ”’ <summary>

        ”’ calling this helps to execute DB scripts returning no data such as abackup script

        ”’ </summary>

        ”’ <param name=”_SPName”></param>

        ”’ <param name=”_Parameters”></param>

        ”’ <param name=”ctName”></param>

        ”’ <param name=”commTimeout”></param>

        ”’ <remarks></remarks>

        Protected Overloads Sub Execute(ByVal _SPName As String, ByVal _Parameters As ArrayList, _

                                ByVal ctName As CommandType, ByVal commTimeout As Integer) Implements ISQLData.Execute

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = MyClass.ConString

 

                    Me.Command = New SqlCommand(_SPName, Me.Connection)

                    Me.Command.CommandType = ctName

                    Me.Command.CommandTimeout = commTimeout

                    If IsNothing(_Parameters) = False Then

                        For pos As Integer = 0 To _Parameters.Count – 1

                            With CType(_Parameters(pos), ParameterSQL)

                                Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                            End With

                        Next

                    End If

 

                    Try

 

                        Me.Connection.Open()

                        Me.Command.ExecuteNonQuery()

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

 

                    End Try

 

                End Using

            End Using

 

        End Sub

 

        Protected Overloads Sub Execute(ByVal _SPName As String, ByVal _Parameters As ArrayList, _

                         ByVal ctName As CommandType) Implements ISQLData.Execute

            Me.Execute(_SPName, _Parameters, ctName, 0)

        End Sub

 

        Protected Overloads Sub Execute(ByVal _SPName As String, ByVal _Parameters As ArrayList) Implements ISQLData.Execute

            Me.Execute(_SPName, _Parameters, CommandType.StoredProcedure, 0)

        End Sub

 

        Protected Overloads Sub Execute(ByVal _SPName As String) Implements ISQLData.Execute

            Me.Execute(_SPName, Nothing, CommandType.StoredProcedure, 0)

        End Sub

 

        ”’ <summary>

        ”’ Returns data from adata reader as ahashtable

        ”’ </summary>

        ”’ <param name=”_SPName”></param>

        ”’ <param name=”_Parameters”></param>

        ”’ <param name=”ctName”></param>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Protected Overloads Function Read(ByVal _SPName As String, ByVal _Parameters As ArrayList, _

                                 ByVal ctName As CommandType) As Hashtable Implements ISQLData.Read

 

            Dim htReadData As New Hashtable()

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = MyClass.ConString

 

                    Me.Command = New SqlCommand(_SPName, Me.Connection)

                    Me.Command.CommandType = ctName

 

                    If IsNothing(_Parameters) = False Then

 

                        For pos As Integer = 0 To _Parameters.Count – 1

                            With CType(_Parameters(pos), ParameterSQL)

 

                                Select Case True

                                    Case Not IsNothing(.Value)

                                        Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

 

                                    Case IsNothing(.Value) And IsNothing(.Length) OrElse IsNothing(.Value) And .Length = 0

                                        Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .SQLDataType)).Direction = .ParDirection

 

                                    Case Else

                                        Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .SQLDataType, .Length)).Direction = .ParDirection

 

                                End Select

 

                            End With

                        Next

 

                        Try

 

                            Me.Connection.Open()

 

                            Me.Command.ExecuteNonQuery()

 

                            If IsNothing(_Parameters) = False Then

 

                                For pos As Integer = 0 To _Parameters.Count – 1

 

                                    With CType(_Parameters(pos), ParameterSQL)

 

                                        Select Case True

                                            Case .ParDirection = ParameterDirection.Output OrElse .ParDirection = ParameterDirection.InputOutput

                                                htReadData.Add(.Name.ToString(), Me.Command.Parameters(“@” & .Name.ToString()).Value)

                                        End Select

 

                                    End With

                                Next

                            End If

 

                            Return htReadData

 

                        Catch eX As SqlException

                            Throw eX

 

                        Catch eX As Exception

                            Throw eX

 

                        Finally

                            If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                            If Me.DataReader IsNot Nothing Then Me.DataReader.Close()

                            Me.Connection.Close()

                        End Try

 

                    End If

                    Return Nothing

 

                End Using

            End Using

 

        End Function

 

        Protected Overloads Function Read(ByVal _SPName As String, ByVal _Parameters As ArrayList) As Hashtable Implements ISQLData.Read

            Return Me.Read(_SPName, _Parameters, CommandType.StoredProcedure)

        End Function

 

        Protected Overloads Function Read(ByVal _SPName As String) As Hashtable Implements ISQLData.Read

            Return Me.Read(_SPName, Nothing, CommandType.StoredProcedure)

        End Function

 

#End Region

 

#Region ” Destructor “

 

        Public Overridable Sub Dispose() Implements ISQLData.Dispose

            GC.Collect()

        End Sub

 

#End Region

 

    End Class

Using the DAF

To use the DAF, define a class such as Customers that inherits DBConnect and overrides SaveData, UpdateData, DeleteData methods. You can include several other methods that call the Load method to retrieve data as a dataset using different select criterion such as GetCustomers Note that if your SP returns multiple tables from the DB, then you need to supply comma separated datatable names to load the returned DB data, or if you just want to execute a certain script, you can just call Execute method. You can also return only one value from the DB by calling the Read method that returns a hashtable.

public class Customers : DBConnect

    {

        #region Fields

 

        private string m_CustID;

        private string m_FirstName;

        private string m_LastName;

        private DateTime m_JoinDate;

        private string m_Email;

        private decimal m_CreditLimit;

 

       #endregion

 

        #region Properties

 

        public string CustID { get { return m_CustID; } set { m_CustID = value; } }

        public string FirstName { get { return m_FirstName; } set { m_FirstName = value; } }

        public string LastName { get { return m_LastName; } set { m_LastName = value; } }

        public DateTime JoinDate { get { return m_JoinDate; } set { m_JoinDate = value; } }

        public string Email { get { return m_Email; } set { m_Email = value; } }

        public decimal CreditLimit { get { return m_CreditLimit; } set { m_CreditLimit = value; } }

 

        #endregion

 

        #region Constructors

 

        public Customers() : base() { }

 

        public Customers(string serverName, string databaseName)

            : base()

        {

            this.ServerName = serverName;

            this.DatabaseName = databaseName;

        }

 

        #endregion

 

        #region Methods

 

        protected override ArrayList SaveData()

        {

            this.SetCommand(“uspInsertCustomers”);

 

            Parameters.Add(new ParameterSQL(“CustID”, this.CustID));

            Parameters.Add(new ParameterSQL(“FirstName”, this.FirstName));

            Parameters.Add(new ParameterSQL(“LastName”, this.LastName));

            Parameters.Add(new ParameterSQL(“JoinDate”, this.JoinDate));

            Parameters.Add(new ParameterSQL(“Email”, this.Email));

            Parameters.Add(new ParameterSQL(“CreditLimit”, this.CreditLimit));

 

            return Parameters;

 

        }

 

        protected override ArrayList UpdateData()

        {

            this.SetCommand(“uspUpdateCustomers”);

 

            Parameters.Add(new ParameterSQL(“CustID”, this.CustID));

            Parameters.Add(new ParameterSQL(“FirstName”, this.FirstName));

            Parameters.Add(new ParameterSQL(“LastName”, this.LastName));

            Parameters.Add(new ParameterSQL(“JoinDate”, this.JoinDate));

            Parameters.Add(new ParameterSQL(“Email”, this.Email));

            Parameters.Add(new ParameterSQL(“CreditLimit”, this.CreditLimit));

 

            return Parameters;

 

        }

 

        protected override ArrayList DeleteData()

        {

            string where;

            string errorPart;

 

            where = “CustID = ‘” + this.CustID + “‘”;

            errorPart = “Cust No: “ + this.CustID;

            this.SetCommand(“uspDeleteObject”);

 

            Parameters.Add(new ParameterSQL(“ObjectName”, “Customers”));

            Parameters.Add(new ParameterSQL(“Where”, where));

            Parameters.Add(new ParameterSQL(“ErrorPart”, errorPart));

 

            return Parameters;

 

        }

 

 

        public DataSet GetCustomers(string custID)

        {

            Parameters.Add(new ParameterSQL(“CustID”, custID));

 

            return this.Load(“uspGetCustomers”, “Customers”, Parameters);

 

        }

 

        #endregion

    }

 Finally create a form that instantiates the Customers object and calls the respective methods,

Customers Registration Form

 Code under Save button is as follows

private void btnSave_Click(object sender, EventArgs e)

        {

            try

            {

                using (DAFSampleData.Customers oCustomers = new DAFSampleData.Customers())

                {

 

                        oCustomers.CustID = this.txtCustID.Text;

                        oCustomers.FirstName = this.txtFirstName.Text;

                        oCustomers.LastName = this.txtLastName.Text;

                        oCustomers.JoinDate = this.dtpJoinDate.Value;

                        oCustomers.Email = this.txtEmail.Text;

                        oCustomers.CreditLimit = Convert.ToDecimal(this.txtCreditLimit.Text);

 

                        ////////////////////////////////////////////////////////////////////////////////////////////////////

                        if (oCustomers.Save()) {MessageBox.Show(“Cust ID: “ + oCustomers.CustID + ” successfully saved!”);}

                        ////////////////////////////////////////////////////////////////////////////////////////////////////

 

                }

 

                //////////////////////////

                this.ResetControls();

                //////////////////////////

 

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

 

        }

Code under Load button is as follows

private void btnLoad_Click(object sender, EventArgs e)

        {

            DataTable customers = new DataTable();

 

            try

            {

                DAFSampleData.Customers oCustomers = new DAFSampleData.Customers();

                customers = oCustomers.GetCustomers(this.txtCustID.Text).Tables["Customers"];

 

                /////////////////////////////////////////////////////////////////////////////////////////////////////

                this.ResetControls();

                /////////////////////////////////////////////////////////////////////////////////////////////////////

 

                EnumerableRowCollection<DataRow> cust = customers.AsEnumerable();

 

                /////////////////////////////////////////////////////////////////////////////////////////////////////

 

                this.txtCustID.Text = (from c in cust select c.Field<String>(“CustID”)).First();

                this.txtFirstName.Text = (from c in cust select c.Field<String>(“FirstName”)).First();

                this.txtLastName.Text = (from c in cust select c.Field<String>(“LastName”)).First();

                this.dtpJoinDate.Value = (from c in cust select c.Field<DateTime>(“JoinDate”)).First();

                this.txtEmail.Text = (from c in cust select c.Field<String>(“Email”)).First();

                this.txtCreditLimit.Text = (from c in cust select c.Field<Decimal>(“CreditLimit”)).First().ToString();

 

                //////////////////////////////////////////////////////////////////////////////////////////////////////             

 

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

 

        }

Code under Update button is as follows

private void btnUpdate_Click(object sender, EventArgs e)

        {

            try

            {

                using (DAFSampleData.Customers oCustomers = new DAFSampleData.Customers())

                {

 

                    oCustomers.CustID = this.txtCustID.Text;

                    oCustomers.FirstName = this.txtFirstName.Text;

                    oCustomers.LastName = this.txtLastName.Text;

                    oCustomers.JoinDate = this.dtpJoinDate.Value;

                    oCustomers.Email = this.txtEmail.Text;

                    oCustomers.CreditLimit = Convert.ToDecimal(this.txtCreditLimit.Text);

 

                    /////////////////////////////////////////////////////////////////////

                    string updateMSG = oCustomers.Update();

                    MessageBox.Show(updateMSG);

 

                    /////////////////////////////////////////////////////////////////////

 

                }

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

Code under Delete button is as follows

private void btnDelete_Click(object sender, EventArgs e)

        {

            try

            {

                using (DAFSampleData.Customers oCustomers = new DAFSampleData.Customers())

                {

 

                    oCustomers.CustID = this.txtCustID.Text;

 

                    /////////////////////////////////////////////////////////////////////

                    string deleteMSG = oCustomers.Delete();

                    MessageBox.Show(deleteMSG);

 

                    /////////////////////////////////////////////////////////////////////

                    this.ResetControls();                  

                    /////////////////////////////////////////////////////////////////////

 

                }

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

Finally run the SP that are also provided for in the Stored Procedure folder. No need to write them manually you can use tool such as Code Generation; Generate Stored Procedures Automatically from my earlier post.

Create Customers Table Script 

create table Customers
(CustID varchar(10) not null constraint pkCustID primary key,
FirstName varchar(20),
LastName varchar(20),
JoinDate smalldatetime,
Email varchar(20),
CreditLimit money
)
go

Insert Into Customers SP

create proc uspInsertCustomers(
@CustID varchar(10),
@FirstName varchar(20),
@LastName varchar(20),
@JoinDate smalldatetime,
@Email varchar(20),
@CreditLimit money
) as

declare @ErrorMSG varchar(200)

if exists(select CustID from Customers where CustID = @CustID)
 begin
  set @ErrorMSG = ‘The record with %s: %s, you are trying to enter already exists’
  raiserror(@ErrorMSG, 16, 1, ‘Cust ID’, @CustID)
  return 1
 end

begin
insert into Customers
(CustID, FirstName, LastName, JoinDate, Email, CreditLimit)
values
(@CustID, @FirstName, @LastName, @JoinDate, @Email, @CreditLimit)
return 0
end
go

Update Customers SP

create proc uspUpdateCustomers(
@CustID varchar(10),
@FirstName varchar(20),
@LastName varchar(20),
@JoinDate smalldatetime,
@Email varchar(20),
@CreditLimit money
) as

declare @ErrorMSG varchar(200)

if not exists(select CustID from Customers where CustID = @CustID)
 begin
  set @ErrorMSG = ‘The record with %s: %s, you are trying to enter does not exist in the registered %s’
  raiserror(@ErrorMSG, 16, 1, ‘Cust ID’, @CustID, ‘Customers’)
  return 1
 end

begin
update Customers set
FirstName = @FirstName, LastName = @LastName, JoinDate = @JoinDate, Email = @Email, CreditLimit = @CreditLimit
where CustID = @CustID
return 0
end
go

Get Customers SP

create proc uspGetCustomers(
@CustID varchar(10)
) as

declare @ErrorMSG varchar(200)

if not exists(select CustID from Customers where CustID = @CustID)
 begin
  set @ErrorMSG = ‘The record with %s: %s, you are trying to enter does not exist in the registered %s’
  raiserror(@ErrorMSG, 16, 1, ‘Cust ID’, @CustID, ‘Customers’)
  return 1
 end
else
begin
 select CustID, FirstName, LastName, JoinDate, Email, CreditLimit
 from Customers
 where CustID = @CustID
return 0
end
go

Delete SP

 Create one delete stored procedure that can be used to delete data from different tables

Conclusion

If you follow some basic rules and commit them to habit, writing data-access code will be faster, easier, and more reusable, save you trips to the server, and allow you to keep your data separate.

Note

  • Remember to download the complete code from the attached file (Data Access Framework.zip), that  shows usage in both VB and C#
  • This post contains a stripped down version of code that is an extract from my framework that I have used for over five years now. One application that uses it can be downloaded from http://www.clinicmaster.net/Downloads.htm
  • Follow me on twitter (@Kutegz) to get information for more posts.

 Next Post (s)

  •  Code Generation; Generate VB/C# Code Automatically

 

 Wilson Kutegeka | Microsoft MVP – VB | C#
Developer | Promoter | ClinicMaster Software

Cel: +256 772 609113 | Web:
www.clinicmaster.net

follow me on twitter find me on facebook

 

Restore MS SQL Server database from within your application(s)

Introduction

This post is a follow-up of my previous post; Backup MS SQL Server database from within your application(s). It presents a simple way in which you could incorporate a restore tool from with in your application.

Simply download the source code with the restore form that you need to just add into your application

Restore MS SQL Server database from within your application

Key Methods include

LoadServers; this method loads all local servers

    ”’ <summary>

    ”’ Loads local servers

    ”’ </summary>

    ”’ <remarks></remarks>

    Private Sub LoadServers()

 

        Try

            Me.Cursor = Cursors.WaitCursor

 

            Dim servers As New DataTable()

            servers = SmoApplication.EnumAvailableSqlServers(True)

 

            Me.cboServerName.Items.Clear()

 

            For Each server As DataRow In servers.Rows

                Me.cboServerName.Items.Add(server.Item(“Name”))

            Next

 

        Catch ex As Exception

            Return

 

        Finally

            Me.Cursor = Cursors.Default

 

        End Try

 

    End Sub

LoadDataFiles; this method first checks the validity of the device, and then loads data and log files from the backup device

    ”’ <summary>

    ”’ loads data and log files

    ”’ </summary>

    ”’ <param name=”fileName”></param>

    ”’ <remarks></remarks>

    Private Sub LoadDataFiles(ByVal fileName As String)

 

        Dim restoreServer As New Server()

        Dim dataRestore As New Restore()

        Dim data As New DataTable()

        Dim valid As Boolean

 

        Try

 

            Me.Cursor = Cursors.WaitCursor

            Me.dgvRestore.Rows.Clear()

 

            dataRestore.Devices.AddDevice(fileName, DeviceType.File)

            valid = dataRestore.SqlVerify(restoreServer)

 

            If valid Then

                data = dataRestore.ReadFileList(restoreServer)

 

                If data Is Nothing OrElse data.Rows.Count < 1 Then Return

 

                ”””””””””””””””””””””””””””””””””””””””””””””””””’

                For pos As Integer = 0 To data.Rows.Count – 1

                    ‘ Ensure that you add a new row

                    Me.dgvRestore.Rows.Add()

                    Me.dgvRestore.Item(Me.colLogicalName.Name, pos).Value = data.Rows(pos).Item(“LogicalName”)

                    Me.dgvRestore.Item(Me.colPhysicalName.Name, pos).Value = data.Rows(pos).Item(“PhysicalName”)

                    Me.dgvRestore.Item(Me.colSize.Name, pos).Value = data.Rows(pos).Item(“Size”)

                Next

                ”””””””””””””””””””””””””””””””””””””””””””””””””’

                Me.btnRestore.Enabled = True

                ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Else

                Me.ClearControls()

                Throw New ArgumentException(“Invalid backup file!”)

            End If

 

        Catch exSMO As SmoException

            Throw exSMO

 

        Catch ex As Exception

            Throw ex

 

        Finally

            Me.Cursor = Cursors.Default

 

        End Try

 

    End Sub

Restore button performs the restoration process

    Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click

 

        Try

 

            Cursor.Current = Cursors.WaitCursor

 

            Dim dataLogicalName As String

            Dim logLogicalName As String

            Dim dataPhysicalName As String

            Dim logPhysicalName As String

 

            Dim serverName As String = Me.cboServerName.Text.ToString()

            Dim dataBaseName As String = Me.txtDataBaseName.Text.ToString()

            Dim fileName As String = Me.txtBackupFile.Text.ToString()

 

            If String.IsNullOrEmpty(serverName) Then Throw New ArgumentException(“Enter server name!”)

            If String.IsNullOrEmpty(dataBaseName) Then Throw New ArgumentException(“Enter data base name!”)

            If String.IsNullOrEmpty(fileName) Then Throw New ArgumentException(“Load backup file!”)

 

            Dim restoreServer As New Server(serverName)

            Dim dataRestore As New Restore()

 

            dataRestore.Action = RestoreActionType.Database

            dataRestore.Database = dataBaseName

 

            ”set the devices: file, tape etc

            dataRestore.Devices.AddDevice(fileName, DeviceType.File)

 

            ”progress meter stuff

            Me.proRestoreDatabase.Minimum = 0

            Me.proRestoreDatabase.Value = 10

            Me.proRestoreDatabase.Maximum = 100

 

            ”this gives the % complete by handling the event

            ”provided by SMO on the percent complete; we will

            ”update the progress meter in the event handler

 

            ”set the progress meter to 10% by default

            dataRestore.PercentCompleteNotification = 10

            dataRestore.ReplaceDatabase = Me.chkOverwriteDatabase.Checked

 

            AddHandler dataRestore.PercentComplete, AddressOf Me.RestoreProgressEventHandler

 

            ”use below to relocate ths mdf files

            If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 0).Value)) Then

                dataLogicalName = String.Empty

            Else : dataLogicalName = CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 0).Value)

            End If

 

            If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 0).Value)) Then

                dataPhysicalName = String.Empty

            Else : dataPhysicalName = CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 0).Value)

            End If

 

            If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 1).Value)) Then

                logLogicalName = String.Empty

            Else : logLogicalName = CStr(Me.dgvRestore.Item(Me.colLogicalName.Name, 1).Value)

            End If

 

            If String.IsNullOrEmpty(CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 1).Value)) Then

                logPhysicalName = String.Empty

            Else : logPhysicalName = CStr(Me.dgvRestore.Item(Me.colPhysicalName.Name, 1).Value)

            End If

 

            With dataRestore.RelocateFiles

                .Add(New RelocateFile(dataLogicalName, dataPhysicalName))

                .Add(New RelocateFile(logLogicalName, logPhysicalName))

            End With

 

            dataRestore.SqlRestore(restoreServer)

 

            MessageBox.Show(“Restore completed successfully!”)

 

        Catch exSMO As SmoException

            Me.proRestoreDatabase.Value = 0

            MessageBox.Show(exSMO.Message)

 

        Catch ex As Exception

            Me.proRestoreDatabase.Value = 0

            MessageBox.Show(ex.Message)

 

        Finally

            Me.proRestoreDatabase.Value = 0

            Cursor.Current = Cursors.Default

 

        End Try

 

    End Sub

 

    Private Sub RestoreProgressEventHandler(ByVal sender As System.Object, ByVal e As PercentCompleteEventArgs)

        ”increase the progress bar up by the percent

        Me.proRestoreDatabase.Value = e.Percent

    End Sub

Download the source code RestoreDatabase.zip attached below

Wilson Kutegeka | Microsoft MVP – VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web:
www.clinicmaster.net

Backup MS SQL Server database from within your application(s)

Introduction

The need to back up databases on a regular basis is a major component of managing any production system. This post presents a simple way in which you could incorporate a backup tool from with in your application.

Simply download the source code with the backup form that you need to just add into your application

Backup MS SQL Server database from within your application

 
Key Methods include

LoadServers; this method loads all local servers

    ”’ <summary>

    ”’ Loads all local servers

    ”’ </summary>

    ”’ <remarks></remarks>

    Private Sub LoadServers()

 

        Try

            Me.Cursor = Cursors.WaitCursor

 

            Dim servers As New DataTable()

            servers = SmoApplication.EnumAvailableSqlServers(True)

 

            Me.cboServerName.Items.Clear()

 

            For Each server As DataRow In servers.Rows

                Me.cboServerName.Items.Add(server.Item(“Name”))

            Next

 

        Catch ex As Exception

            Return

 

        Finally

            Me.Cursor = Cursors.Default

 

        End Try

 

    End Sub

 LoadDatabases; this method loads databases for the selected server

    ”’ <summary>

    ”’ Loads databases for the selected server

    ”’ </summary>

    ”’ <param name=”serverName”></param>

    ”’ <remarks></remarks>

    Private Sub LoadDatabases(ByVal serverName As String)

 

        Try

 

            Me.Cursor = Cursors.WaitCursor

            Me.cboDataBaseName.Items.Clear()

 

            Dim server As New Server(serverName)

 

            For Each database As Database In server.Databases

                Me.cboDataBaseName.Items.Add(database.Name)

            Next

 

        Catch ex As Exception

            Return

 

        Finally

            Me.Cursor = Cursors.Default

 

        End Try

 

    End Sub

 Backup button performs the backup process

    Private Sub btnBackup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackup.Click

 

        Try

 

            Cursor.Current = Cursors.WaitCursor

 

            Dim serverName As String = Me.cboServerName.Text.ToString()

            Dim fileName As String = Me.txtBackupFile.Text.ToString()

 

            If String.IsNullOrEmpty(serverName) Then Throw New ArgumentException(“Enter server name”)

            If String.IsNullOrEmpty(fileName) Then Throw New ArgumentException(“Load backup file!”)

 

            Dim backupServer As New Server(serverName)

            Dim dataBackup As New Backup()

 

            dataBackup.Action = BackupActionType.Database

            dataBackup.Database = Me.cboDataBaseName.Text.ToString()

 

            ”set the devices: file, tape etc

            dataBackup.Devices.AddDevice(fileName, DeviceType.File)

 

            ”set this if you want to do incremental

            dataBackup.Incremental = Me.chkIncremental.Checked

 

            ”progress meter stuff

            Me.proBackupDatabase.Minimum = 0

            Me.proBackupDatabase.Value = 10

            Me.proBackupDatabase.Maximum = 100

 

            ”this gives the % complete by handling the event

            ”provided by SMO on the percent complete; we will

            ”update the progress meter in the event handler

 

            ”set the progress meter to 10% by default

            dataBackup.PercentCompleteNotification = 10

            AddHandler dataBackup.PercentComplete, AddressOf Me.BackupProgressEventHandler

 

            dataBackup.SqlBackup(backupServer)

 

            MessageBox.Show(“Backup completed successfully!”)

 

        Catch exSMO As SmoException

            Me.proBackupDatabase.Value = 0

            MessageBox.Show(exSMO.Message)

 

        Catch ex As Exception

            Me.proBackupDatabase.Value = 0

            MessageBox.Show(ex.Message)

 

        Finally

            Me.proBackupDatabase.Value = 0

            Cursor.Current = Cursors.Default

 

        End Try

 

    End Sub

 

    Private Sub BackupProgressEventHandler(ByVal sender As System.Object, ByVal e As PercentCompleteEventArgs)

        ”increase the progress bar up by the percent

        Me.proBackupDatabase.Value = e.Percent

    End Sub

Related Post:

Restore MS SQL Server database from within your application(s)

 

Code Generation; Generate Stored Procedures Automatically

Introduction

A couple of times when developing a new application, you’ll need to write some stored procedures for the basic CRUD (Create, Read, Update, and Delete) operations for each table in your database. Without at least these four, the software cannot be considered complete.

I do consider that you use stored procedures for such database operations because they offer the following benefits among others

  • Stored procedures encapsulate query code at the server, rather than inside your application. This allows you to make changes to queries without having to recompile your application.
  • Stored procedures can be used for better defined application security. You can deny all rights on the base tables, grant execute only on the procs. This gives you a much smaller security footprint to manage.
  • Stored procedures are compiled code. Though the latest versions of MSSQL does a better job of storing execution plans – so this isn’t as big of an issue as it used to be, but still something to consider.
  • Stored procedures eliminate SQL injection risk ONLY when used correctly. Make sure to use the parameters the right way inside the stored proc – stored procs that are just executing concatenated dynamic SQL inside them aren’t doing anyone any good.
  • Stored procedures allow you to define an application interface for the database, so that the system can be shared between multiple applications without having to duplicate logic in those applications.

Additionally, you may need to script the entire database creation for packaging with the installer, such that the accompanying database of your application is created by the installer at the client machine

Writing these Stored Procedures can be very repetitive and time consuming. Once you are familiar with how to write then, it can become a chore having to mindlessly type them all out.

This post presents a simple way of building own code generator, you can build a tool that will automatically generate stored procedures and importantly in a way that you want them to be.

Generate Stored Procedure Automatically

Form that generates the stored procedures automatically

Key objects/methods in this code generator include:-

Properties; This class helps to work with key table design properties that are enough for us to create table or manipulate its data

Public Class Properties

 

#Region ” Fields “

 

    Private m_TableName As String

 

    Private m_Name As String

    Private m_Caption As String

    Private m_DataType As String

    Private m_Key As String

    Private m_References As String

 

    Private m_Nullable As Boolean

    Private m_DefaultValue As String

 

#End Region

 

#Region ” Properties “

 

    Public Property TableName() As String

        Get

            Return m_TableName

        End Get

        Set(ByVal value As String)

            m_TableName = value

        End Set

    End Property

 

    Public Property Name() As String

        Get

            Return m_Name

        End Get

        Set(ByVal value As String)

            m_Name = value

        End Set

    End Property

 

    Public Property Caption() As String

        Get

            Return m_Caption

        End Get

        Set(ByVal value As String)

            m_Caption = value

        End Set

    End Property

 

    Public Property DataType() As String

        Get

            Return m_DataType

        End Get

        Set(ByVal value As String)

            m_DataType = value

        End Set

    End Property

 

    Public Property Key() As String

        Get

            Return m_Key

        End Get

        Set(ByVal value As String)

            m_Key = value

        End Set

    End Property

 

    Public Property References() As String

        Get

            Return m_References

        End Get

        Set(ByVal value As String)

            m_References = value

        End Set

    End Property

 

 

    Public Property Nullable() As Boolean

        Get

            Return m_Nullable

        End Get

        Set(ByVal value As Boolean)

            m_Nullable = value

        End Set

    End Property

 

    Public Property DefaultValue() As String

        Get

            Return m_DefaultValue

        End Get

        Set(ByVal value As String)

            m_DefaultValue = value

        End Set

    End Property

 

#End Region

 

#Region ” Constructors “

 

    Public Sub New()

        MyBase.New()

    End Sub

 

    Public Sub New(ByVal tableName As String)

        MyClass.New()

        Me.TableName = tableName

    End Sub

 

#End Region

 

End Class

PropertiesList; This method returns the table properties list that has been set through the datagridview

    ”’ <summary>

    ”’ Returns a list of key table properties that have been set

    ”’ </summary>

    ”’ <returns></returns>

    ”’ <remarks></remarks>

    Private Function PropertiesList() As List(Of Properties)

 

        Dim name As String

        Dim caption As String

        Dim nullable As Boolean

        Dim dataType As String

        Dim key As String

        Dim references As String

        Dim defaultValue As String

 

        Dim lProperties As New List(Of Properties)

 

        Try

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

            If String.IsNullOrEmpty(Me.txtTableName.Text.ToString().Trim()) Then

                Me.txtTableName.Focus()

                Throw New ArgumentException(“Must Enter Table Name!”)

            End If

            Me._TableName = Me.txtTableName.Text.ToString().Trim()

            ”””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

 

            If Me.dgvProperties.RowCount <= 1 Then Throw New ArgumentException(“Must Register At least one property!”)

 

            For Each row As DataGridViewRow In Me.dgvProperties.Rows

 

                If row.IsNewRow Then Exit For

 

                name = CStr(row.Cells.Item(Me.colName.Name).Value)

                If String.IsNullOrEmpty(name) Then

                    Throw New ArgumentException(“All entries for name must be entered!”)

                End If

 

                dataType = CStr(row.Cells.Item(Me.colDataType.Name).Value)

                If String.IsNullOrEmpty(dataType) Then

                    Throw New ArgumentException(“All entries for data type must be entered!”)

                End If

 

                key = CStr(row.Cells.Item(Me.colKey.Name).Value)

                If String.IsNullOrEmpty(key) Then

                    Throw New ArgumentException(“All entries for key must be selected!”)

                ElseIf key.ToUpper().Equals(“Foreign”.ToUpper()) Then

                    references = CStr(row.Cells.Item(Me.colReferences.Name).Value)

                    If String.IsNullOrEmpty(references) Then

                        Throw New ArgumentException(“References must have a value for every key set as foreign!”)

                    End If

                End If

 

            Next

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

 

            For rowNo As Integer = 0 To Me.dgvProperties.RowCount – 2

 

                Dim oProperties As Properties = New Properties(Me._TableName)

 

                name = CStr(Me.dgvProperties.Item(Me.colName.Name, rowNo).Value)

                caption = CStr(Me.dgvProperties.Item(Me.colCaption.Name, rowNo).Value)

                If String.IsNullOrEmpty(caption) Then caption = String.Empty

                nullable = CBool(Me.dgvProperties.Item(Me.colNullable.Name, rowNo).Value)

                dataType = CStr(Me.dgvProperties.Item(Me.colDataType.Name, rowNo).Value)

                key = CStr(Me.dgvProperties.Item(Me.colKey.Name, rowNo).Value)

                references = CStr(Me.dgvProperties.Item(Me.colReferences.Name, rowNo).Value)

                If String.IsNullOrEmpty(references) Then references = String.Empty

                defaultValue = CStr(Me.dgvProperties.Item(Me.colDefaultValue.Name, rowNo).Value)

                If String.IsNullOrEmpty(defaultValue) Then defaultValue = String.Empty

 

                With oProperties

                    .TableName = Me._TableName

                    .Name = name

                    .Caption = caption

                    .Nullable = nullable

                    .DataType = dataType

                    .Key = key

                    .References = references

                    .DefaultValue = defaultValue

                End With

 

                lProperties.Add(oProperties)

 

            Next

 

            Return lProperties

 

        Catch ex As Exception

            Throw ex

 

        End Try

 

    End Function

GenerateCreateTableSP; This method generates the create table stored procedure 

    ”’ <summary>

    ”’ Generates the create table stored procedure

    ”’ </summary>

    ”’ <remarks></remarks>

    Private Sub GenerateCreateTableSP()

 

 

        Try

 

            ””””””””””””””””””””””””””””””””””””””””””””””””

            ”””’Header”””””””””””””””””””””””””””””””””””””””””’

            ””””””””””””””””””””””””””””””””””””””””””””””””

 

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“——————————————————————————————————”)

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“————– Create Table: “ & Me._TableName & ” ——————————————————”)

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“——————————————————————————————————”)

            Me._Scripts.AppendLine()

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“if exists (select * from sysobjects where name = ‘” & Me._TableName & “‘)”)

            Me._Scripts.AppendLine()

            Me._Scripts.Append(ControlChars.Tab)

            Me._Scripts.Append(“drop table “ & Me._TableName)

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“go”)

            Me._Scripts.AppendLine()

            Me._Scripts.AppendLine()

 

            ””””””””””””””””””””””””””””””””””””””””””””””””

            ”””’Body””””””””””””””””””””””””””””””””””””””””””’

            ””””””””””””””””””””””””””””””””””””””””””””””””

 

            ””””””””””””””””””””””””””””””””””””””””””””””””

            Dim defineColumn As New StringBuilder(String.Empty)

            If Me._PropertiesList Is Nothing OrElse Me._PropertiesList.Count < 1 Then Return

            ””””””””””””””””””””””””””””””””””””””””””””””””

 

            For pos As Integer = 0 To Me._PropertiesList.Count – 1

 

                With Me._PropertiesList.Item(pos)

 

                    defineColumn.Append(.Name)

                    defineColumn.Append(Chr(Keys.Space))

                    defineColumn.Append(.DataType)

                    If Not .Nullable Then defineColumn.Append(” not null”)

                    If Not String.IsNullOrEmpty(.References) Then

                        defineColumn.AppendLine()

                        defineColumn.Append(“constraint fk” & .Name & Me._TableName & ” references “ & .References & ” (“ & .Name & “)”)

                    End If

                    If .Name.ToUpper().Equals(Me.LastKeyColumn.ToUpper()) Then

                        If Me.CountPrimaryKeys > 1 Then

                            defineColumn.Append(“,”)

                            defineColumn.AppendLine()

                            defineColumn.Append(GetPrimaryKeyConstraint())

                        Else

                            defineColumn.AppendLine()

                            defineColumn.Append(“constraint pk” & .Name & ” primary key”)

                        End If

                    ElseIf Not String.IsNullOrEmpty(.DefaultValue) Then

                        defineColumn.Append(Chr(Keys.Space))

                        defineColumn.Append(“constraint df” & .Name & Me._TableName & ” default “ & .DefaultValue)

                    End If

                    If pos < Me._PropertiesList.Count – 1 Then

                        defineColumn.Append(“,”)

                        defineColumn.AppendLine()

                    End If

                End With

            Next

 

            Me._Scripts.Append(“create table “ & Me._TableName)

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“(“)

            Me._Scripts.Append(defineColumn.ToString())

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“)”)

            Me._Scripts.AppendLine()

            Me._Scripts.Append(“go”)

            Me._Scripts.AppendLine()

            Me._Scripts.AppendLine()

 

        Catch ex As Exception

            Throw ex

        End Try

 

    End Sub

 SaveXML; This method saves the set table properties as an XML file

    ”’ <summary>

    ”’ Saves table colums (Properties) as XML

    ”’ </summary>

    ”’ <param name=”fileName”></param>

    ”’ <param name=”data”></param>

    ”’ <remarks></remarks>

    Private Sub SaveXML(ByVal fileName As String, ByVal data As List(Of Properties))

 

        Dim encoding As New UnicodeEncoding()

        Dim XMLWriter As New XmlTextWriter(fileName, encoding)

 

        Try

 

            With XMLWriter

                .Formatting = Formatting.Indented

                .Indentation = 3

                .WriteStartDocument()

                .WriteStartElement(Me._TableName)

            End With

 

            ”””””””””””””””””””””””””””””””””””””””””

            For Each oProperties As Properties In data

                With XMLWriter

                    .WriteStartElement(“Property”)

                    .WriteAttributeString(“Name”, oProperties.Name)

                    .WriteAttributeString(“Caption”, oProperties.Caption)

                    .WriteAttributeString(“Nullable”, CStr(oProperties.Nullable))

                    .WriteAttributeString(“DataType”, oProperties.DataType)

                    .WriteAttributeString(“Key”, oProperties.Key)

                    .WriteAttributeString(“References”, oProperties.References)

                    .WriteAttributeString(“DefaultValue”, oProperties.DefaultValue)

                    .WriteEndElement()

                End With

            Next

            ”””””””””””””””””””””””””””””””””””””””””

            XMLWriter.WriteEndElement()

            ”””””””””””””””””””””””””””””””””””””””””

 

        Catch XMLex As XmlException

            Throw XMLex

 

        Catch ex As Exception

            Throw ex

 

        Finally

            XMLWriter.Close()

 

        End Try

 

    End Sub

OpenXML; This method opens the saved XML file table properties

    ”’ <summary>

    ”’ Opens table colums (Properties) from an XML file

    ”’ </summary>

    ”’ <param name=”fileName”></param>

    ”’ <remarks></remarks>

    Private Sub OpenXML(ByVal fileName As String)

 

        Try

 

            Dim xmlDatadoc As New XmlDataDocument()

            Dim properties = New DataTable(Me._TableName)

 

            ””””””””””””””””””””””””””””””””””””””””””””””””””

            Me.txtTableName.Clear()

            Me.dgvProperties.Rows.Clear()

            ””””””””””””””””””””””””””””””””””””””””””””””””””

 

            xmlDatadoc.DataSet.ReadXml(fileName)

            properties = xmlDatadoc.DataSet.Tables(“Property”)

 

            If properties Is Nothing OrElse properties.Rows.Count < 1 Then Return

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

            Me.txtTableName.Text = xmlDatadoc.FirstChild.Name

            For pos As Integer = 0 To properties.Rows.Count – 1

 

                ‘ Ensure that you add a new row

 

                With Me.dgvProperties

                    .Rows.Add()

 

                    .Item(Me.colName.Name, pos).Value = properties.Rows(pos).Item(“Name”)

                    .Item(Me.colCaption.Name, pos).Value = properties.Rows(pos).Item(“Caption”)

                    .Item(Me.colNullable.Name, pos).Value = properties.Rows(pos).Item(“Nullable”)

                    .Item(Me.colDataType.Name, pos).Value = properties.Rows(pos).Item(“DataType”)

                    .Item(Me.colKey.Name, pos).Value = properties.Rows(pos).Item(“Key”)

                    .Item(Me.colReferences.Name, pos).Value = properties.Rows(pos).Item(“References”)

                    .Item(Me.colDefaultValue.Name, pos).Value = properties.Rows(pos).Item(“DefaultValue”)

 

                End With

 

            Next

 

            ”””””””””””””””””””””””””””””””””””””””””””””””””’

 

        Catch XMLex As XmlException

            Throw XMLex

 

        Catch ex As Exception

            Throw ex

 

        End Try

 

    End Sub

Find full source code in the attached file

Note

 You can download the source code Generate_SP_Automatically.zip attached below

Wilson Kutegeka | Microsoft MVP – VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web:
www.clinicmaster.net

 

I’m a VB: Interview with the Microsoft Visual Basic Team

Who is the “typical VB.NET developer”? Is there one? There are millions of VB.NET developers in the world, and they each have their own unique story. MS VB Team decided to talk to some of them to find out what kinds of applications they’re building, what technologies they’re using, and what their favorite features are. Watch the interviews and find out for yourself who today’s VB.NET developers really are!

check out:

http://msdn.microsoft.com/en-us/vbasic/dd776132.aspx

Don’t forget Wilson Kutegeka

http://blogs.msdn.com/vbteam/pages/i-m-a-vb-wilson-kutegeka-vb-mvp.aspx

 

Custom Controls – Smart Picture Box

Introduction

SmartPictureBox Control: A picture box control is one of the most popular controls to display and received an image from a user. However, most of the time you’ll find yourself writing repetitive tasks such as

  • Load an image from file
  • Save an image from the control onto a file
  • Handle edit operations such as Copy, Cut, Paste, Delete, Undo, etc.

The SmartPictureBox control puts this together trough a context menu as shown below

SmartPictureBox Control with its context menu

 SmartPictureBox Control with its context menu

Members of interest of SmartPictureBox include the following:-

Properties

  • ReadOnly

If set to true, the pop up menu of the control will be hidden.

  • ImageSizeLimit

This property sets or gets the Maximum image file size limit.

 

Other Feature(s) Include

  • Enabling and disabling the menu appropriately depending upon what’s on the clipboard etc.

You can download the source code attached below

Process database actions, such as save, update and delete on different kinds of objects (or object lists) as a transaction inside your data access layer using generics and a couple of helper objects

Assuming that you want to save into Payments and PaymentsDetails tables for a Client who has an account and ensuring that the balance in Accounts table is also updated.

This example shows how you can save, update and delete different objects of different types (or object lists) as a transaction in your DAL. It’s related to my previous post that you can find at. Using List(Of T) Generic in your data access layer (DAL)- Improved However, this could only process a list of the same type.

Explanation

  • Create an enumeration (say Action) that you’ll use to specify an action to be performed on the list
  • Create an interface (say ISQLData) for objects that are going to participate in a transaction to implement.
  • Create a base class say DBConnect that implements ISQLData for other classes that will participate in the transaction to derive from.
  • Create a helper class (say TransactionList) that will help to store object lists with action to be performed on the list
  • Create a method (say DoTransactions) that processes the transactions
  • Create objects such as Payments, PaymentDetails and Accounts that derives from DBConnect
  • Define a list of the above objects and call DoTransactions to process them as a transaction.

Enumeration

    Public Enum Action

        Save

        Update

        Delete

    End Enum

Interface

    ”’ <summary>

    ”’ Objects that will participate in transaction

    ”’ must implement this interface

    ”’ </summary>

    ”’ <remarks></remarks>

    Public Interface ISQLData

 

        Property ConString() As String

        Function SaveCommand() As SqlCommand

        Function UpdateCommand() As SqlCommand

        Function DeleteCommand() As SqlCommand

 

    End Interface

Helper Objects and Methods

    ”’ <summary>

    ”’ Helper class that stores a list of objects

    ”’ that implement ISQLData interface

    ”’ </summary>

    ”’ <typeparam name=”T”></typeparam>

    ”’ <remarks></remarks>

    Public Class TransactionList(Of T As ISQLData)

 

#Region ” Fields “

        Private m_list As List(Of T)

        Private m_Action As Action

#End Region

 

#Region ” Properties “

 

        Public Property List() As List(Of T)

            Get

                Return m_list

            End Get

            Set(ByVal value As List(Of T))

                m_list = value

            End Set

        End Property

 

        Public Property Action() As Action

            Get

                Return m_Action

            End Get

            Set(ByVal Value As Action)

                m_Action = Value

            End Set

        End Property

 

#End Region

 

#Region ” Constructors “

 

        Public Sub New()

            MyBase.New()

        End Sub

 

        Public Sub New(ByVal _list As List(Of T), ByVal _Action As Action)

            MyClass.New()

            Me.List = _list

            Me.Action = _Action

        End Sub

 

#End Region

 

#Region ” Methods “

#End Region

 

    End Class

 

    ”’ <summary>

    ”’ Base class that implements ISQLData

    ”’ </summary>

    ”’ <remarks></remarks>

    Public MustInherit Class DBConnect : Implements ISQLData

 

#Region ” Fields “

        ‘ Objects that will participate in transactions

        ‘ will use the same connection string, lets define it here

        Private Shared m_ConString As String

#End Region

 

#Region ” Properties “

 

        Public Property ConString() As String Implements ISQLData.ConString

            Get

                Return m_ConString

            End Get

            Set(ByVal Value As String)

                m_ConString = Value

            End Set

        End Property

#End Region

 

#Region ” Methods “

 

        Public Overridable Function SaveCommand() As SqlCommand Implements ISQLData.SaveCommand

            Return Nothing

        End Function

 

        Public Overridable Function UpdateCommand() As SqlCommand Implements ISQLData.UpdateCommand

            Return Nothing

        End Function

 

        Public Overridable Function DeleteCommand() As SqlCommand Implements ISQLData.DeleteCommand

            Return Nothing

        End Function

 

#End Region

 

    End Class

 

        ”’ <summary>

        ”’ Performs all specified actions such as Save,

        ”’ Update and Delete on a list of objects of type

        ”’ TransactionList having a list of objects of type

        ”’ IDBConnect and returns number of records affected.

        ”’ </summary>

        ”’ <typeparam name=”T”></typeparam>

        ”’ <param name=”_List”></param>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Function DoTransactions(Of T As ISQLData)(ByVal _List As List(Of TransactionList(Of T))) 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 empty!”)

 

                    ‘exit the loop as soon as an item with set connection string is found

                    For Each items As TransactionList(Of T) In _List

                        For Each list As T In items.List

                            If list IsNot Nothing Then

                                conn.ConnectionString = list.ConString()

                                Exit For

                            End If

                        Next

                        If Not String.IsNullOrEmpty(conn.ConnectionString) Then Exit For

                    Next

 

                    conn.Open()

 

                    Using tran As SqlTransaction = conn.BeginTransaction()

 

                        For Each items As TransactionList(Of T) In _List

 

                            Select Case items.Action

 

                                Case Action.Save

 

                                    For Each list As T In items.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

 

                                Case Action.Update

 

                                    For Each list As T In items.List

 

                                        Try

                                            Using comm As SqlCommand = list.UpdateCommand()

                                                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 updating, transaction was canceled!” & vbCrLf & ex.Message)

 

                                        End Try

                                    Next

 

                                Case Action.Delete

 

                                    For Each list As T In items.List

                                        Try

 

                                            Using comm As SqlCommand = list.DeleteCommand()

                                                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 deleting, transaction was canceled!” & vbCrLf & ex.Message)

 

                                        End Try

                                    Next

 

                            End Select

                        Next

 

                        tran.Commit()

 

                    End Using

 

                    Return records

 

                Catch eX As SqlException

                    Throw eX

 

                Catch eX As Exception

                    Throw eX

 

                Finally

                    conn.Close()

                End Try

 

            End Using

 

        End Function

 

        ”’ <summary>

        ”’ Construct a connection string in a way that eliminates SQL injection

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Function ConString() As String

 

            Dim conBuilder As New SqlConnectionStringBuilder()

 

            Dim serverName As String = “(local)”

            Dim databaseName As String = “Accounting”

 

            conBuilder.Clear()

            conBuilder(“Server”) = serverName

            conBuilder.InitialCatalog = databaseName

            conBuilder(“Integrated Security”) = “SSPI”

            conBuilder.PersistSecurityInfo = False

 

            Return conBuilder.ConnectionString

 

        End Function

Participating Objects (Payments, PaymentDetails, Accounts)

    Public Class Payments : Inherits DBConnect

 

#Region ” Fields “

        Private m_ReceiptNo As Integer

        Private m_AccountNo As Integer

        Private m_PayDate As Date

#End Region

 

#Region ” Properties “

 

        Public Property ReceiptNo() As Integer

            Get

                Return m_ReceiptNo

            End Get

            Set(ByVal value As Integer)

                m_ReceiptNo = value

            End Set

        End Property

 

        Public Property AccountNo() As Integer

            Get

                Return m_AccountNo

            End Get

            Set(ByVal value As Integer)

                m_AccountNo = value

            End Set

        End Property

 

        Public Property PayDate() As Date

            Get

                Return m_PayDate

            End Get

            Set(ByVal value As Date)

                m_PayDate = value

            End Set

        End Property

#End Region

 

#Region ” Constructors “

 

        Public Sub New()

            MyBase.New()

        End Sub

 

        Public Sub New(ByVal _ConString As String)

            MyClass.New()

            Me.ConString = _ConString

        End Sub

 

#End Region

 

#Region ” Methods “

 

        Public Overrides Function SaveCommand() As SqlCommand

 

            Using comm As SqlCommand = New SqlCommand(“uspInsertPayments”)

 

                comm.CommandType = CommandType.StoredProcedure

 

                With comm.Parameters

                    .AddWithValue(“@ReceiptNo”, Me.ReceiptNo)

                    .AddWithValue(“@AccountNo”, Me.AccountNo)

                    .AddWithValue(“@PayDate”, Me.PayDate)

                End With

 

                Return comm

 

            End Using

 

        End Function

 

#End Region

 

    End Class

 

    Public Class PaymentDetails : Inherits DBConnect

 

#Region ” Fields “

        Private m_ReceiptNo As Integer

        Private m_ProductNo As Integer

        Private m_Price As Decimal

#End Region

 

#Region ” Properties “

 

        Public Property ReceiptNo() As Integer

            Get

                Return m_ReceiptNo

            End Get

            Set(ByVal value As Integer)

                m_ReceiptNo = value

            End Set

        End Property

 

        Public Property ProductNo() As Integer

            Get

                Return m_ProductNo

            End Get

            Set(ByVal value As Integer)

                m_ProductNo = value

            End Set

        End Property

 

        Public Property Price() As Decimal

            Get

                Return m_Price

            End Get

            Set(ByVal value As Decimal)

                m_Price = value

            End Set

        End Property

#End Region

 

#Region ” Constructors “

 

        Public Sub New()

            MyBase.New()

        End Sub

 

        Public Sub New(ByVal _ConString As String)

            MyClass.New()

            Me.ConString = _ConString

        End Sub

 

#End Region

 

#Region ” Methods “

 

        Public Overrides Function SaveCommand() As SqlCommand

 

            Using comm As SqlCommand = New SqlCommand(“uspInsertPaymentDetails”)

 

                comm.CommandType = CommandType.StoredProcedure

 

                With comm.Parameters

                    .AddWithValue(“@ReceiptNo”, Me.ReceiptNo)

                    .AddWithValue(“@ProductNo”, Me.ProductNo)

                    .AddWithValue(“@Price”, Me.Price)

                End With

 

                Return comm

 

            End Using

 

        End Function

 

#End Region

 

    End Class

 

    Public Class Accounts : Inherits DBConnect

 

#Region ” Fields “

        Private m_TranID As Integer

        Private m_AccountNo As Integer

        Private m_Amount As Decimal

        Private m_Balance As Decimal

#End Region

 

#Region ” Properties “

 

        Public Property TranID() As Integer

            Get

                Return m_TranID

            End Get

            Set(ByVal value As Integer)

                m_TranID = value

            End Set

        End Property

 

        Public Property AccountNo() As Integer

            Get

                Return m_AccountNo

            End Get

            Set(ByVal value As Integer)

                m_AccountNo = value

            End Set

        End Property

 

        Public Property Amount() As Decimal

            Get

                Return m_Amount

            End Get

            Set(ByVal value As Decimal)

                m_Amount = value

            End Set

        End Property

 

        Public Property Balance() As Decimal

            Get

                Return m_Balance

            End Get

            Set(ByVal value As Decimal)

                m_Balance = value

            End Set

        End Property

 

#End Region

 

#Region ” Constructors “

 

        Public Sub New()

            MyBase.New()

        End Sub

 

        Public Sub New(ByVal _ConString As String)

            MyClass.New()

            Me.ConString = _ConString

        End Sub

 

#End Region

 

#Region ” Methods “

 

        Public Overrides Function UpdateCommand() As SqlCommand

 

            Using comm As SqlCommand = New SqlCommand(“uspUpdateAccounts”)

 

                comm.CommandType = CommandType.StoredProcedure

 

                With comm.Parameters

                    .AddWithValue(“@TranID”, Me.TranID)

                    .AddWithValue(“@AccountNo”, Me.AccountNo)

                    .AddWithValue(“@Amount”, Me.Amount)

                    .AddWithValue(“@Balance”, Me.Balance)

                End With

 

                Return comm

 

            End Using

 

        End Function

 

#End Region

 

    End Class

User Interface Code

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

 

        Try

 

            Me.Cursor = Cursors.WaitCursor

 

            Dim transactions As New List(Of TransactionList(Of DBConnect))

 

            ‘ Make sure that atleast one object(Payments in this case)

            ‘ sets the connection string

            Dim oPayments As New Payments(ConString)

            Dim lPayments As New List(Of DBConnect)

 

            Dim oPaymentDetails As New PaymentDetails()

            Dim lPaymentDetails As New List(Of DBConnect)

 

            Dim oAccounts As New Accounts()

            Dim lAccounts As New List(Of DBConnect)

 

            ‘ Set Payments list to save

            With lPayments

                .Add(New Payments() With {.ReceiptNo = 101, .AccountNo = 1001, .PayDate = Today()})

            End With

 

            ‘ Set PaymentDetails list to save

            With lPaymentDetails

                .Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 1, .Price = 500})

                .Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 2, .Price = 550})

                .Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 3, .Price = 200})

                .Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 4, .Price = 180})

            End With

 

            ‘ Set Accounts list to update

            With lAccounts

                .Add(New Accounts() With {.TranID = 2, .AccountNo = 1001, .Amount = 800, .Balance = 8900})

            End With

 

            ‘ Populate the transactions list

            With transactions

                .Add(New TransactionList(Of DBConnect)(lPayments, Action.Save))

                .Add(New TransactionList(Of DBConnect)(lPaymentDetails, Action.Save))

                .Add(New TransactionList(Of DBConnect)(lAccounts, Action.Update))

            End With

 

            ‘ Process transactions

            Dim records As Integer = Common.DoTransactions(transactions)

 

            MessageBox.Show(String.Format(“{0} record(s) processed!”, records))

 

        Catch ex As Exception

            MessageBox.Show(ex.Message)

 

        Finally

            Me.Cursor = Cursors.Default

 

        End Try

 

    End Sub

 Source Code (DoTransactions.zip) attached

Wilson Kutegeka | Microsoft MVP – VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web:
www.clinicmaster.net

 

Create one delete stored procedure that can be used to delete data from different tables

Stored procedures offer powerful security control over the database and importantly, provide an abstraction layer between the physical structure of the database and the logical way in which it’s used.

Thus, whenever possible, database access should be performed through the use of stored procedures. If this is the case, you’ll quickly realize that delete stored procedure is identical for most delete operations with an exception of table name to delete from and the where clause.

Below is an example of a delete stored procedure that you can use to delete data from different tables

if exists (select * from sysobjects where name = ‘uspDeleteObject’)
 drop proc uspDeleteObject
go

create proc uspDeleteObject(
@ObjectName varchar(40) ,
@Where varchar(200) ,
@ErrorPart varchar(100) = null
)  as

exec(‘declare @Records int declare @errorMSG varchar(200)
select  @Records  =  count(*) from ‘ + @ObjectName + ‘ where ‘ + @Where + ‘
if @Records <= 0
begin
 set @errorMSG = ”The record ‘ + @ErrorPart + ‘, you are trying to delete does not exist in the registered ‘ + @ObjectName + ‘.”
 raiserror(@errorMSG,16, 1) 
 return
end
delete from  ‘ + @ObjectName + ‘ where ‘ + @Where + ”)

go

– exec uspDeleteObject ‘Logins’, ‘LoginID = ”Admin”’, ‘Login ID: Admin’

You can now call such a stored procedure from your VB code as follows

    ”’ <summary>

    ”’ Use uspDeleteObject stored procedure to delete data

    ”’ from different tables. This deletes from Clients table

    ”’ whose primary key is ClientID

    ”’ </summary>

    ”’ <returns></returns>

    ”’ <remarks></remarks>

    Public Function Delete() As Boolean

 

        Dim where As String = “ClientID = ‘” & Me.ClientID & “‘”

        Dim errorPart As String = “Client ID: “ & Me.ClientID

 

        ‘ Assuming Clients table had a composite key (ClientID and Version),

        ‘ the where and errorPart would be as follows

        ‘ where = “ClientID = ‘” & Me.ClientID & “‘ and Version = ‘” & Me.Version & “‘”

        ‘ errorPart = “ClientID: ” & Me.ClientID & ” and Version: ” & Me.Version

 

        Using conn As New SqlConnection()

            conn.ConnectionString = ConString()

 

            Using comm As SqlCommand = New SqlCommand(“uspDeleteObject”, conn)

                comm.CommandType = CommandType.StoredProcedure

 

                With comm.Parameters

                    .AddWithValue(“@ObjectName”, “Clients”)

                    .AddWithValue(“@Where”, where)

                    .AddWithValue(“@ErrorPart”, errorPart)

                End With

 

                Try

                    conn.Open()

                    Return comm.ExecuteNonQuery() > 0

 

                Catch eX As SqlException

                    Throw eX

 

                Catch eX As Exception

                    Throw eX

 

                Finally

                    conn.Close()

                End Try

 

            End Using

        End Using

    End Function

    ”’ <summary>

    ”’ This construct of connection string eliminates SQL injection

    ”’ </summary>

    ”’ <param name=”_ConString”></param>

    ”’ <returns></returns>

    ”’ <remarks></remarks>

    Public Function ConString() As String

 

        Dim conBuilder As New SqlConnectionStringBuilder()

 

        Dim serverName As String = “(local)”

        Dim databaseName As String = “Accounting”

 

        conBuilder.Clear()

        conBuilder(“Server”) = serverName

        conBuilder.InitialCatalog = databaseName

        conBuilder(“Integrated Security”) = “SSPI”

        conBuilder.PersistSecurityInfo = False

        Return conBuilder.ConnectionString 

 

    End Function

Note:
You can modify the delete function such that the errorPart, where clause and tableName are supplied as its parameters.

 

Operator equal to (=) Overloading example

Well I’ve not encountered many scenarios where I really needed to overload an operator. However operator overloading can save you a lot of coding time like in the double data entered application example I worked on and applied the concept.

This is how it goes.

Two users (first and second entry user) will enter two entries of the same record, it doesn’t matter who enters the first though. If both users enter correctly, the two entries will be merged into a final verified entry. Otherwise both entries will be saved and compared for error(s) before merging them.

This case needed retrieving a previously saved entry by the first user, compare it with one about to be saved, and if they’re the same, update the saved copy to verified otherwise display fields that are different for both  users for editing.

Operator equal to (=) Overloading was used as a short cut to this implementation, a simplified code sample is as follows

Public Class Customer

 

#Region ” Fields “

 

    Private m_CustID As Integer

    Private m_FirstName As String

    Private m_LastName As String

    Private m_Address As String

    Private m_Telephone As String

    Private m_Email As String

 

#End Region

 

#Region ” Properties “

 

    Public Property CustID() As Integer

        Get

            Return m_CustID

        End Get

        Set(ByVal Value As Integer)

            m_CustID = 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

 

    Public Property Address() As String

        Get

            Return m_Address

        End Get

        Set(ByVal Value As String)

            m_Address = Value

        End Set

    End Property

 

    Public Property Telephone() As String

        Get

            Return m_Telephone

        End Get

        Set(ByVal Value As String)

            m_Telephone = Value

        End Set

    End Property

 

    Public Property Email() As String

        Get

            Return m_Email

        End Get

        Set(ByVal Value As String)

            m_Email = Value

        End Set

    End Property

 

#End Region

 

#Region ” Constructors “

 

    Public Sub New()

        MyBase.New()

    End Sub

 

#End Region

 

#Region ” Operator = Overloading “

 

    ”’ <summary>

    ”’ Overloading  = operator is as simple as creating a method.

    ”’ In fact, operator overloads are really just methods created with the Operator keyword

    ”’ Note that the comaprison excludes CustID field, which is the primary key

    ”’ </summary>

    ”’ <param name=”lhs”></param>

    ”’ <param name=”rhs”></param>

    ”’ <returns></returns>

    ”’ <remarks></remarks>

    Public Shared Operator =(ByVal lhs As Customer, ByVal rhs As Customer) As Boolean

 

        If lhs Is Nothing OrElse rhs Is Nothing Then Return False

 

        If lhs.FirstName.ToUpper() <> rhs.FirstName.ToUpper() Then Return False

        If lhs.LastName.ToUpper() <> rhs.LastName.ToUpper() Then Return False

        If lhs.Address.ToUpper() <> rhs.Address.ToUpper() Then Return False

        If lhs.Telephone <> rhs.Telephone Then Return False

        If lhs.Email <> rhs.Email Then Return False

 

        Return True

 

    End Operator

 

    ”’ <summary>

    ”’ When you define = operator, VB requires that you also

    ”’ define the operator for the inverse operation (not equal to) operator.

    ”’ Simply reverse the = operand and use the not equal to  operator

    ”’ </summary>

    ”’ <param name=”lhs”></param>

    ”’ <param name=”rhs”></param>

    ”’ <returns></returns>

    ”’ <remarks></remarks>

    Public Shared Operator <>(ByVal lhs As Customer, ByVal rhs As Customer) As Boolean

        Return Not lhs = rhs

    End Operator

 

#End Region

 

#Region ” Methods “

 

#End Region

 

End Class

You can now use equal to (=) as shown in the following code

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

 

        ‘Get the previously saved customer 101, by user one.

        ‘For simplicity, we will just assign this customer as follows

        Dim oCustByUser1 As New Customer() _

                With {.CustID = 101, .FirstName = “Wilson”, .LastName = “Kutegeka”, _

                .Address = “Kampala”, .Telephone = “+256 772 609113″}

 

        ‘Pick the new customer record 101, by user two from your interface.

        ‘For simplicity, we will assign this customer as well

        Dim oCustByUser2 As New Customer() _

                With {.CustID = 101, .FirstName = “Wilson”, .LastName = “Kutegeka”, _

                .Address = “Kampala-Uganda”, .Telephone = “+256 772 609113″}

 

        ‘This will not verify because adress is not the same

        If oCustByUser1 = oCustByUser2 Then

            ‘Update the first entry to verified

            MessageBox.Show(“Verified!”)

        Else

            ‘Check to see which fields are not the same and report to user

            MessageBox.Show(“Some Fields are not the same!”)

        End If

 

    End Sub