65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL 和 VB.NET 代码生成器

Raj Settipalli
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.72/5 (31投票s)

2005年2月22日

5分钟阅读

viewsIcon

223325

downloadIcon

10410

一篇关于生成 SQL 存储过程和 VB.NET 函数调用的文章。

Sample Image

引言

我遇到过许多项目，它们要么生成 VB/C# 代码，要么生成 SQL 代码。这个应用程序可以同时生成表的 SQL 存储过程和调用这些存储过程的 VB.NET 函数模块。这是一个非常有用的工具，可以生成所需的代码，尤其是在将 ASP/VB 代码转换为 ASP.NET/VB.NET 时。此外，该应用程序还提供了选择字段以生成存储过程的选项。

背景

我在一个使用 Microsoft Application Blocks for .NET 访问 SQL Server 2000 数据库的项目中开发了这部分核心代码。我们有封装数据访问的类，以及为订单、客户等每个业务对象设计的实体类。

我们的想法是创建一个工具，用于辅助生成用于操作数据库的存储过程、函数和实体。例如，为了创建一个订单，我们需要在 *order* 表中插入一行，并在 *order detail* 表中插入一个或多行。可能还涉及其他表，例如特殊说明、送货地址（可能不止一个送货点）。为此，将有一个或多个插入行的存储过程，这些存储过程通过 VB.NET 数据访问类执行。这个类将封装添加、删除、修改订单所需的所有功能（数据访问逻辑），这无非是在 Order 和 OrderDetails（以及其他）表中插入、删除和更新行。用于插入订单的 VB.NET 函数将接收一个“Order”对象作为参数，并准备调用存储过程（例如，usp_InsertOrder）所需的 SQL 命令参数，以便将行插入 Order、OrderDetails 和其他表中。

使用代码

在第一步中，用户会看到一个网络中可用 SQL 服务器的列表，这些服务器通过调用 NetServerEnum API 填充到 ComboBox 中。此 API 可用于获取其他服务器（如域控制器等）的列表。我已经修改了代码以适应目的，并创建了一个新类，以便它可以在任何其他项目中复用。

当在提供服务器登录用户名和密码后单击“连接”按钮时，将显示所选服务器中可用的数据库。根据需要显示的内容选择“表”或“存储过程”（单选按钮）。当选择“表”选项时，您可以选择创建实体类、插入、删除或更新存储过程。当选择“表”选项时，DataGrid dgColumns 将用表字段填充；当选择“存储过程”选项时，将用存储过程参数填充。

生成实体类

要创建实体类，将使用所选表的所有列。每列都将以“_”为前缀，成为私有变量，并创建一个公共属性。代码非常简单，只需遍历表行并为每个表行生成属性。

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

生成的类将为所选表的每个字段都有一个对应的属性。只需进行简单的修改，就可以迭代 dgColumns DataGrid 来生成类代码，该代码将仅为 DataGrid 中选定的字段提供属性。

生成存储过程

插入行时，必须为每个不允许 null 值的列提供值。因此，在 DataGrid dgColumns 中，所有“允许 Null”列值为“否”的行都将默认选中，并且无法取消选中。如果需要新列，可以选择它们（只需使用 Ctrl/Shift 键组合来选择一行或多行）。

在生成“更新”存储过程时，主键将用在 where 子句中，其他选定列将被视为需要更新的列，并生成相应的参数。在 dgColumns 的主键列中，行无法取消选中，但其他列可以选择。

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

在生成删除存储过程时，dgColumns 中选定的所有行都将用在 where 子句中。在这种情况下，主键列行也预先选中且无法取消选中，但可以选中更多行。

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

生成函数调用

如果通过选择适当的单选按钮将存储过程填充到组合框中，DataGrid 将显示执行存储过程所需的参数。单击“生成函数”按钮时，将生成 VB.NET 函数代码，其中包含准备参数的代码，但不包括为准备好的参数提供值的代码行。

生成函数代码遵循以下步骤：

  1. 声明 SQL 参数对象数组。
  2. 启动 Try 块。
  3. 准备每个参数。
  4. 调用 Microsoft Application Blocks 的 SQLHelper 类的 ExecuteDataset 函数。
  5. 实现 Finally 块以销毁所有对象。
  6. 关闭 Try 块。
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

仅会生成与异常处理相关的骨架代码，因为不同项目使用不同的技术；例如，异常详细信息可以发送到相关人员的电子邮件或记录在数据库中等。因此，请修改此代码以在“Catch”和“End Try”语句之间添加代码。

温馨提示：如前所述，在生成的代码中，为 SQL 参数赋值的语句将被注释掉。下面是生成注释掉的 VB.NET 代码行，需要取消注释，并在使用生成的函数之前由用户为参数分配适当的值。

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

单击“保存到文件”按钮将生成的类、存储过程或函数保存到文件中。

关注点

编写调用存储过程的函数或过程可能很烦人，因为参数名称和顺序必须与存储过程中声明的完全相同。而在编写操作数据库表的存储过程时，每个“not null”列都需要参数。使用此工具可以减轻这种痛苦。

此外，生成实体类并为每个表字段声明属性也可能很累人。我发现这个工具非常方便，可以快速生成大部分所需的代码，且在实际使用前需要很少甚至无需修改。

历史

首次发布。

© . All rights reserved.