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.
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
This contains the Customers class, which defines methods such as SaveData, UpdateData, DeleteData, GetCustomers etc.
This is the partial class for the form frmCustomers
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
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,
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