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.
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