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)
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)
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)
strBuilder.Append(vbCrLf & "end class")
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.
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:
- declaring the SQL Parameter object array.
- start of
Try
block.
- preparing each parameter.
- calling
ExecuteDataset
function of SQLHelper
class of Microsoft Application Blocks.
- implement
Finally
block to destroy all the objects.
- 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")
strBuilder.Append(strFunctionName & "() _
as System.Data.Dataset")
strBuilder.Append(vbCrLf)
strBuilder.Append("Dim dsResults as System.Data.DataSet" & vbCrLf)
strBuilder.Append("Dim SQLParam(")
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")
strBuilder.Append(vbCrLf & vbCrLf)
For i = 1 To dsColumns.Tables("Procedure").Rows.Count - 1
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("
"'Uncomment this after" & _
" providing value for this parameter" & vbCrLf)
strBuilder.Append("End with" & vbCrLf)
strBuilder.Append(vbCrLf & vbCrLf)
Next
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)
strBuilder.Append("Catch ex As Exception")
strBuilder.Append(vbCrLf & vbCrLf)
strBuilder.Append("'Your own method of exception handling" & vbCrLf)
strBuilder.Append(vbCrLf & vbCrLf)
strBuilder.Append("Finally")
strBuilder.Append(vbCrLf & vbCrLf)
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)
strBuilder.Append(vbCrLf)
strBuilder.Append("Return dsResults")
strBuilder.Append(vbCrLf & vbCrLf)
strBuilder.Append("End 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.