SQL and VB.NET Code Generator

• Download demo project - 18.1 Kb
• Download source - 16.5 Kb

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.

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.

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

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.

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.

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.