Click here to Skip to main content
15,860,943 members
Articles / Database Development / SQL Server
Article

SQL and VB.NET Code Generator

Rate me:
Please Sign up or sign in to vote.
3.72/5 (31 votes)
23 Feb 20055 min read 219.9K   10.4K   98   14
An article on generating SQL strored procedures and VB.NET function calls.

Sample Image

Introduction

I have come across many projects that generate either VB/C# code or SQL code. This application generates both SQL stored procedures for a table and the VB.NET function module to call the stored procedures. This is a very useful tool for generating required code especially when you are converting ASP/VB code to ASP.NET/VB.NET. Also, this application has the options to select the table fields for generating a stored procedure.

Background

I developed the core portions of this code when I was working on a project that uses Microsoft Application Blocks for .NET to access the SQL Server 2000 database. We had classes that encapsulate data access and entity classes for each of the business objects like order, customer etc.

The idea is to create a tool that will aid in generating stored procedures, functions and entities for manipulating a database. For example, taking a very common situation, to create an order, we need to insert a row in the order table and one or more rows in the order detail table. There could be other tables involved, like, special instructions, delivery address (could be more than one delivery point). For this purpose, there will be one or more stored procedures to insert rows, and these stored procedures are executed via a VB.NET data access class. This class will encapsulate all the functionality (data access logic) required to add, delete, modify the orders, which is nothing but inserting, deleting and updating the rows in Order and OrderDetails (and other) tables. The VB.NET function to insert an order will receive an "Order" object as parameter, and prepares the SQL command parameters required to call a stored procedure (for e.g., usp_InsertOrder) to insert the rows into the Order, the OrderDetails and other tables.

Using the code

In the first step, the user is presented with a list of the available SQL servers in the network, which are populated into a ComboBox by calling NetServerEnum API. This API can be used to get the list of other servers like domain controllers etc. I have modified the code to suit the purpose and created a new class so that it can be used in any other project.

When the "connect" button is clicked after supplying the user name and password for the server to login, the available databases in the selected server are displayed. Select Tables or Stored Procedures (radio buttons) based on what needs to be displayed. When Tables option is selected, you have the option to create an entity class, or insert, delete or update stored procedure otherwise. DataGrid dgColumns will be populated with the table fields when "Tables" option is selected, and with stored procedure parameters when "Stored Procedures" option is selected.

Generating entity class

For creating the entity class, all the columns of the selected table are used. Each column will be prefixed with underscore and becomes a private variable, and a public property is also created. The code for this is very simple, just iterate through the table rows and generate the property for each table row.

VB
strBuilder.Append("Public class " & strEntityName & vbCrLf)
strBuilder.Append("#Region " & Chr(34) & _
  " Private variables " & Chr(34) & vbCrLf)

'Build Private variables list based 
'on the table column names and data type
For i = 0 To dsColumns.Tables("Tables").Rows.Count - 1
    strBuilder.Append("Private _" & _
      dsColumns.Tables("Tables").Rows(i).Item("COLUMN_NAME"))
    strBuilder.Append(" as " & _
      GetVBDataType(dsColumns.Tables("Tables").Rows(i).Item("TYPE_NAME"))_
       & vbCrLf)
Next
strBuilder.Append(vbCrLf)
strBuilder.Append("# end Region " & _
  vbCrLf) ' Provate variable region ends

'Build Propertis 
strBuilder.Append("#Region " & Chr(34) _
  & " Properties " & Chr(34) & vbCrLf)
For i = 0 To dsColumns.Tables("Tables").Rows.Count - 1
    strBuilder.Append("Public Property ")
    dbType = GetVBDataType(dsColumns.Tables("Tables").Rows(i).Item("TYPE_NAME"))
    strColumnName = dsColumns.Tables("Tables").Rows(i).Item("COLUMN_NAME")
    strBuilder.Append(strColumnName & "() As " & dbType & vbCrLf)
    strBuilder.Append("Get" & vbCrLf)
    strBuilder.Append("return _" & strColumnName & vbCrLf)
    strBuilder.Append("end Get" & vbCrLf)

    strBuilder.Append("Set(ByVal Value As " & dbType & ")" & vbCrLf)
    strBuilder.Append("_" & strColumnName & " = Value" & vbCrLf)
    strBuilder.Append("end set" & vbCrLf)
    strBuilder.Append("end Property ")
    strBuilder.Append(vbCrLf)
    strBuilder.Append(vbCrLf)
Next
strBuilder.Append(vbCrLf)

strBuilder.Append("# end Region " & vbCrLf) 'Properties region ends

strBuilder.Append(vbCrLf & "end class") ' Class building Ends
strFunctionText.Append(strBuilder.ToString)
strBuilder = Nothing

The class generated will have a corresponding property for each field of the selected table. Simple modification is required to iterate dgColumns DataGrid for generating the class code that will have properties only for fields that are selected in the DataGrid.

Generating Stored Procedures

When inserting a row, value should be passed for each of the columns that do not allow null values. Therefore, in the DataGrid dgColumns, all rows with "Allow Null" column value "NO" are selected and can not be unselected. If new columns are required, they can be selected (simply use control/shift key combinations to select one or more rows).

For generating "Update" stored procedure, primary keys are used in the where clause and other selected columns are treated as the columns that need to be updated, and corresponding parameters are also generated. In dgColumns primary key column, rows can not be unselected but other columns can be selected.

VB
'Adding where condition using Primary keys
'by default all the primary keys are used in where condition
strBld.Append(Space(7) & "WHERE" & vbCrLf)

For IntJ = 0 To dsColumns.Tables("Pkeys").Rows.Count - 2
    strColumnName = _
      dsColumns.Tables("Pkeys").Rows(IntJ).Item("COLUMN_NAME")
    strBld.Append(Space(10) & strColumnName _
      & " = " & "@" & strColumnName _
      & " and " & vbCrLf)
Next
strColumnName = _
  dsColumns.Tables("Pkeys").Rows(IntJ).Item("COLUMN_NAME")
strBld.Append(Space(10) & strColumnName _
  & " = " & "@" & strColumnName & vbCrLf)

For generating Delete stored procedure, all the rows selected in dgColumns are used in the where clause. In this case also, primary key column rows are pre-selected and can not be unselected, but more rows can be selected.

VB
For intI = 0 To intGridRowCount - 1
    If dgDetails.IsSelected(intI) Then
        If intI <> 0 Then strBld.Append(" and " & vbCrLf)
        strColumnName = dgDetails.Item(intI, 1) 
        strBld.Append(Space(10) & strColumnName _
          & " = " & "@" & strColumnName)
    End If
Next

Generating Function Call

DataGrid displays the parameters required to execute a stored procedure if stored procedures are populated in the combobox by choosing the appropriate radio button. When "Generate Function" button is clicked, the VB.NET function code is generated which will have the code to prepare the parameters, except the line which is required to supply value to the prepared parameter.

The steps that are followed in generating the function code are as follows:

  1. declaring the SQL Parameter object array.
  2. start of Try block.
  3. preparing each parameter.
  4. calling ExecuteDataset function of SQLHelper class of Microsoft Application Blocks.
  5. implement Finally block to destroy all the objects.
  6. closing Try block.
VB
Private Sub GenerateFunction()
    Dim strBuilder As New StringBuilder
    Dim strFunctionName As String
    Dim i As Integer

    btnSaveToFile.Enabled = True
    strBuilder.Append("Public function ")
    strFunctionName = InputBox("Enter Function Name", "Function Name")

    'Return type assumed to be Dataset
    strBuilder.Append(strFunctionName & "() _
      as System.Data.Dataset") 'Modify this statement to add return type
    strBuilder.Append(vbCrLf)
    'Declaring Dataset varaibale
    strBuilder.Append("Dim dsResults as System.Data.DataSet" & vbCrLf)
    'Delacring Parameter length based on the number of parameters required
    strBuilder.Append("Dim SQLParam(") ' Creates SQLParameter object array
    i = dsColumns.Tables("Procedure").Rows.Count - 1
    If i > 1 Then
        strBuilder.Append(i - 1 & ") as SQLParameter")
    Else
        strBuilder.Append(0 & ") as SQLParameter")
    End If
    strBuilder.Append(vbCrLf)

    strBuilder.Append("Try") ' Try block starts
    strBuilder.Append(vbCrLf & vbCrLf)
    For i = 1 To dsColumns.Tables("Procedure").Rows.Count - 1
        'strBuilder.Append("ErrMsg =" & Chr(34) & " _
          Preparing Parameter " & i & Chr(34))
        strBuilder.Append(vbCrLf)
        strBuilder.Append("SQLParam(" & _
          (i - 1).ToString & _
          ")=new System.Data.SqlClient.SqlParameter" _
          & vbCrLf)
        strBuilder.Append("with SQLParam(" & _
          (i - 1).ToString & ")" & vbCrLf)
          'Begin of WITH block
        strBuilder.Append(".ParameterName=" & _
          Chr(34) & _
          dsColumns.Tables("Procedure").Rows(i).Item("COLUMN_NAME") _
          & Chr(34) & vbCrLf)
        strBuilder.Append(".DbType=" & _
         GetSQLDataType(dsColumns.Tables("Procedure").Rows(i).Item("TYPE_NAME"))_
          & vbCrLf)
        If dsColumns.Tables("Procedure").Rows(i).Item("COLUMN_TYPE")_
                = 1 Then 'Input parameter
            strBuilder.Append(".Direction = _
              ParameterDirection.Input" & vbCrLf)
        ElseIf _
         dsColumns.Tables("Procedure").Rows(i).Item("COLUMN_TYPE")_
          = 2 Then ' Output parameter
            strBuilder.Append(".Direction = ParameterDirection.Output" & vbCrLf)
        End If

        If Not _
         IsDBNull(dsColumns.Tables("Procedure").Rows(i).Item("CHAR_OCTET_LENGTH"))_
          Then
            'This will be null for all numeric data types
            strBuilder.Append(".Size = " & _
             dsColumns.Tables("Procedure").Rows(i).Item("PRECISION")_
              & vbCrLf)
        End If

        strBuilder.Append("'.Value = " & _
          "'Uncomment this after" & _
          " providing value for this parameter" & vbCrLf)

        strBuilder.Append("End with" & vbCrLf)
        ' End of With block

        strBuilder.Append(vbCrLf & vbCrLf)

    Next
    'Write ExecuteDataset statment
    'Replace this statement if required with any other
    'For example you may not be using 
    'MS Data Access Blocks, or ExecuteScalar etc...
    strBuilder.Append("'calling ExecuteDataset Method " & vbCrLf)
    strBuilder.Append("SqlHelper.ExecuteDataset" & _
      "(m_ConnectionString, CommandType.StoredProcedure,"_
       & Chr(34))
    strBuilder.Append(cmbDetails.SelectedValue.ToString()_
       & Chr(34) & ",")
    strBuilder.Append("SQLParam)")
    strBuilder.Append(vbCrLf & vbCrLf)
    'Catch block
    strBuilder.Append("Catch ex As Exception")
    strBuilder.Append(vbCrLf & vbCrLf)
    '
    'Implement you own method of exception handling
    strBuilder.Append("'Your own method of exception handling" & vbCrLf)
    'like sending an email to the concerned
    '
    strBuilder.Append(vbCrLf & vbCrLf)
    strBuilder.Append("Finally")
    strBuilder.Append(vbCrLf & vbCrLf)
    'Destroying SQL Parameter Objects
    strBuilder.Append("Dim i As Integer" & vbCrLf)
    strBuilder.Append("For i = 0 To SQLParam.Length - 1" & vbCrLf)
    strBuilder.Append("SQLParam(i) = Nothing" & vbCrLf)
    strBuilder.Append("Next" & vbCrLf)
    strBuilder.Append(vbCrLf)
    strBuilder.Append("end try" & vbCrLf) ' End of Try block
    strBuilder.Append(vbCrLf)

    'Return Statement
    strBuilder.Append("Return dsResults")
    ' Normally a dataset, change this statement if required

    strBuilder.Append(vbCrLf & vbCrLf)

    strBuilder.Append("End function") 'End of function

    strFunctionText.Append(strBuilder.ToString)

    strBuilder = Nothing
End Sub

Only the skeleton code related exception handling will be generated as different techniques are used in different projects; for example, the exception details could be emailed to the concerned or recorded in the database etc. Therefore, modify this code to add lines between "Catch" and "End Try" statements.

A note of caution: as mentioned earlier, in the generated code, the statement assigning value to SQL Parameter will be commented out. Below is the code which generates a commented VB.NET code line that needs to be uncommented, and proper value for the parameter is to be assigned by the user before using the generated function in a class module.

VB
strBuilder.Append("'.Value = " & _
  "'Uncomment this after providing value for this parameter" & vbCrLf)

Click "Save to File" button to save the generated class, stored procedure, or function to a file.

Points of Interest

Writing functions or procedures to call stored procedures could be annoying as parameter names and order should be exactly the same as that declared in the stored procedure. And while writing the stored procedures to manipulate database tables, parameters are required for each of the "not null" columns. Using this tool relieves that pain.

Also, generating entity class and declaring properties for each table field could be tiring. I find this tool very handy in generating most of the required code quickly with very little or no modifications required before using it.

History

Posted for the first time.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionThanks Pin
kzelda6-Dec-11 22:21
kzelda6-Dec-11 22:21 
GeneralJust what I needed - Pin
Bob Chase20-Jun-10 15:20
Bob Chase20-Jun-10 15:20 
GeneralDoesnt enumerate MSDE servers Pin
arekkusu8224-Aug-07 11:24
arekkusu8224-Aug-07 11:24 
GeneralTry TierDeveloper Code generation tool Pin
Iqbal M Khan20-Jun-07 2:22
Iqbal M Khan20-Jun-07 2:22 
GeneralThank you for Your Good Job Pin
aichau14029-Jan-07 17:47
aichau14029-Jan-07 17:47 
QuestionCan We show ERD using application Pin
shahajigole7-Nov-06 19:28
shahajigole7-Nov-06 19:28 
GeneralSuperb Job !!!! Pin
ubercoder31-May-06 10:50
ubercoder31-May-06 10:50 
GeneralRe: Superb Job !!!! Pin
padierna24-Aug-06 10:52
padierna24-Aug-06 10:52 
GeneralExcellent piece of work Pin
Bob Housedorf18-Apr-06 5:38
Bob Housedorf18-Apr-06 5:38 
GeneralQuality of code Pin
blonkm26-Oct-05 16:26
blonkm26-Oct-05 16:26 
Generalplease contact Pin
Jag HK23-Mar-05 2:42
Jag HK23-Mar-05 2:42 
GeneralRe: please contact Pin
Raj Settipalli28-Mar-05 12:04
Raj Settipalli28-Mar-05 12:04 
GeneralDownload not available Pin
AxelM22-Feb-05 21:16
AxelM22-Feb-05 21:16 
GeneralRe: Download not available Pin
Smitha Nishant23-Feb-05 1:34
protectorSmitha Nishant23-Feb-05 1:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.