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

SQL Server 2008 中的表值参数 - VB.NET

Andy_L_J
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.31/5 (8投票s)

2009年10月9日

CPOL

6分钟阅读

viewsIcon

68777

downloadIcon

1523

使用表值参数从 VB.NET 在 SQL Server 2008 中执行插入操作。

IndRecipeTest_DB

引言

您是否遇到过以下场景：需要将数据列表输入到 SQL Server 数据库中的表中？例如，食谱、员工时间列表或任何重复数据？

您是否必须费力地使用数组或列表以及 UDF 来处理批量插入中的数据？例如，在上面的图片中，为了处理表中的所有行，需要从前端调用 7 个 Insert 语句。

我一直在寻找一个更好的解决方案，一个能够将工作从客户端转移（并省去多次服务器连接）并将繁重的工作转移到服务器的解决方案，因为服务器才是它真正应该存在的地方。

尽管网上有很多关于 SQL Server 2008 中表值参数的文章，但大多数都集中在服务器端 T-SQL 脚本上，而忽略了必须使用数据库存储过程的应用程序开发人员。对于那些技能水平较低的人来说，这种将服务器端代码与客户端代码结合的示例可以成为一个很好的学习工具，并带来许多“灵光一现”的时刻。

鸣谢 Stephen Forté 的博客提供的灵感：www.stephenforte.net。

背景

IndRecipeTest_DB

为了创建一些背景，并避免输入过多的 T_SQL，我附上了这个简单的数据库图表，它解释了所需表的底层结构。这是一个相当著名的模式。创建示例数据库的脚本可以在页面顶部找到。这项工作是一个更大解决方案的一部分，该解决方案涉及六个项目和几十个类，如果发布所有代码甚至完整的解决方案，那将太多了，因为其中可能存在秘密，而且，你知道我们接下来会怎么对你吗？但是，我附上了一个简单而粗糙的示例应用程序，以尝试演示这些概念。

服务器端

服务器端包含一个由少量表、几个存储过程和所有重要的表值参数（用户定义类型）组成的小型数据库。

表值用户定义类型是我们的首要任务

  • TVP_BOM

然后，我们创建所需的表

  • MaterialType
  • MaterialCategory
  • Material
  • FormulaBOM

最后，一个存储过程

  • usp_FormulaBOMInsert

实际上，有很多存储过程，但这是使用表值参数（它是用户定义类型）的那个。

所以，首先，我们需要创建一个用户定义类型来保存我们的表

CREATE TYPE TVP_BOM AS TABLE(
  Mat_ID Int,
  PPH Numeric(18,7)
)
GO

为了更好地说明，我们将插入 Material 和 FormulaBOM 表的表结构

CREATE TABLE Material(
 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,        -- Primary Key
 -- Foreign Key Reference: MaterialCategory
 MatCat_ID INT NOT NULL FOREIGN KEY REFERENCES MaterialCategory(ID), 
 Name VARCHAR(50) NOT NULL,       -- MaterialName
 Price Money NOT NULL Default(2.5)
)
GO

CREATE TABLE FormulaBOM(
  ID Int Identity(1,1) Not Null Primary Key,    -- Primary Key
  -- Foreign Key Reference (Material_ID: Formula)
  Formula_ID Int Not Null Foreign Key References Material(ID), 
  -- Foreign Key Reference (Material_ID: Raw Material)
  Material_ID Int Not Null Foreign Key References Material(ID), 
  PPH Decimal(18,7) NOT NULL Check(PPH > 0)    -- Parts Per Hundred
)
GO

请注意，我们的用户定义类型 TVP_BOM 的结构如何与我们的 FormulaBOM 表的 Mat_ID 和 PPH 字段匹配。这使得向 FormulaBOM 表进行插入操作变得更容易。

另请注意，FormulaBOM 表有两个字段引用 Material 表；Formula_ID 引用 MaterialType 类型的 Material，而 Material_ID 引用 MaterialType 类型的原材料 Material。

现在我们将创建一个存储过程来在后台完成繁重的工作。事实上，使用 TVP 使这个特定的查询非常简单，正如您将看到的

CREATE PROCEDURE usp_FormulaBOMInsert(
  @Form_ID Int,
  @BOM AS TVP_BOM READONLY    -- MUST BE READONLY AND NOT for OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON
  
  BEGIN TRAN
  
    -- If a recipe exists then delete it
    IF EXISTS(SELECT TOP 1 (m.ID)
                FROM FormulaBOM bom
                  JOIN Material m
                    ON bom.Formula_ID = m.ID  
                WHERE  m.ID = @Form_ID)
      DELETE FormulaBOM
        WHERE ID = @Form_ID
   
    -- Add the new recipe to the Table
    -- The bit where we use the TVP (User_Defined Type)
    INSERT INTO FormulaBOM
      SELECT @Form_ID, Mat_ID, PPH
        FROM @BOM

    SELECT bom.ID, f.Name As Formula, m.Name As Material, PPH 
    FROM FormulaBOM bom
      JOIN Material f
        ON bom.Formula_ID = f.ID
      JOIN Material m
        ON bom.Material_ID = m.ID
    WHERE Formula_ID = @Form_ID
    
  COMMIT

  RETURN
  
END
GO

好的。现在我们有了表、用户定义类型和后端连接的存储过程，准备发挥其魔力。需要注意的重要一点是，表值参数必须声明为 READONLY，并且不能将其用作 OUTPUT 参数。（您需要通过添加 MaterialType 表和 MaterialCategory 表来充实数据库结构，并插入一些相关数据，然后就可以开始了。）

客户端

现在我们来到客户端代码。我不会详细介绍如何连接 N 层架构等......但这个概念非常简单。

假设我们有一个类库，其中包含映射业务对象的各种类。对于我们的示例，我们假设有一个与材料及其类别等相关的 Recipe 类......我们将创建一个函数来处理将 FormulaBOM (Recipe) 添加到数据库。对于页面顶部图片中的情况，看起来像一个伪工业化学配方......

Public Class Recipe
  Inherits Generic.List(Of BOMItem)
  ...

  Public Sub AddNew(ByVal formID As Integer, dt As DataTable)
    
    If formID <> 0

      ' Set the required parameters, par and pc are helper
      ' classes that pass data to the datalayer 
      Dim par As New DbParameter("@FormulaID", SqlDbType.nVarChar, formID)
      Dim pc As New ParCollection()
      pc.Add(par)

      ' Note: SqlDbType.Structured: Me.ToBOMTable returns System.DataTable
      ' Note: SqlDBType.Structured
      par = New DbParameter("@BOM", SqlDbType.Structured, Me.ToBOMTable)
      pc.Add(par)

      Dim dtRet As DataTable = DAL.GetDataTable("usp_FormulaBOMInsert", pc, "Recipe")
      
      If dtRet.Rows.Count > 0
        For Each dr As DataRow In dtRet.Rows
          
         ' Create a New BOMItem object (helper class) to hold the extracted data
          Dim bom As New BOMItem(dr("Formula_ID", _
              New Material(True, Convert.ToInt32(dr("Material_ID"))), _
                           dr("PPH"))

         ' Add the BOMItem object to the Collection
          Me.Add(bom)
          
        Next

      End If

    End If

  End Sub

  ...
  ' Other Important methods and functions
  ...

现在，从您的代码中执行 FormulaBOM 插入操作非常简单

...
' Create a Formula Object (Remember how you created this class
' when you were writing the Business Layer? :) )

Dim curFormula = New Formula
curFormula.Name = "1/57 White"
curFormula.MatCategory = "Rigid PVC"
curFormula.AddNew()            ' Load Data into the Object

With curForm.Recipe

  For Each dr As DataGridViewRow In dgvRecipe.Rows
    Dim bom As New BOMItem(curForm.Id, New Material(True, dr.Cells("Chemical").ToString), _
                           dr.Cells("PPH"))

    Me.Add(obj)

  Next
  
End With

curFormula.Recipe.AddNew(curFormula.Id)

非常非常简单的代码

好的，在您询问之前，这里是访问此功能（从示例项目）的真正简单方法。您仍然需要数据库内容，但表插入代码可以像这样简单

Private Sub InsertFormulaBOM(ByVal formID As Integer)

    If dgvRecipe.DataSource IsNot Nothing Then

        ' Set up a BOM Table to pass to the database
        ' BOM Table must match TVP_BOM UDT
        SetBOM()

        Try
            conn.Open()     ' Open connection
            cmd = New SqlCommand("usp_FormulaBOMInsert", conn)  ' Set Command
            cmd.CommandType = CommandType.StoredProcedure

            ' Set parameters
            With cmd
                ' Formula ID parameter
                .Parameters.AddWithValue("@Form_ID", formID)
                .Parameters(0).SqlDbType = SqlDbType.VarChar
                ' BOM Table parameter
                .Parameters.AddWithValue("@BOM", ds.Tables("BOM"))
                .Parameters(1).SqlDbType = SqlDbType.Structured     ' Note Structured

            End With

            cmd.ExecuteNonQuery()

            conn.Close()
            cmd.Dispose()

            GetMaterialsByCategory(cboMatCat.Text)

        Catch ex As Exception
            MsgBox("Ooops!  - " & ex.ToString, _
                   MsgBoxStyle.OkOnly, "ERROR")
        Finally
            conn.Close()
            cmd.Dispose()
        End Try
    End If
End Sub

虽然这很简单，但您仍然需要一种方法来提供 Formula_ID 参数并获取 DataTable 数据。这就是 OOP 概念的优雅之处，因为这些任务可以在您的对象中匿名处理......所包含的示例项目是此功能的一个非常简单的示例。它可以使用 LINQ 或数据绑定来完成，但其中涉及一些基本的手动数据访问和 DataGridView 处理技术，这可能对某些人感兴趣。我把它留下了，以便您可以扩展和尝试这个概念。

如何使用随附文件

下载 IndRecipeTest 脚本并在 SQL Server Management Studio 中打开它。运行脚本以创建一个名为 IndRecipeTest.mdf 的示例数据库。

接下来，打开示例 VB 项目并设置与您刚刚创建的数据库的连接字符串。

获取连接字符串的简单方法

  • 点击“视图”
  • 选择“服务器资源管理器”
  • 右键单击“数据连接”
  • 选择“添加连接”

将出现一个对话框

  • 提供服务器名称
  • 选择：附加数据库文件
  • 浏览到文件位置，选择，然后单击“确定”
  • 测试连接

如果一切顺利，将出现一个表示连接成功的对话框。数据库将添加到“数据连接”下的“服务器资源管理器”树中。

单击 IndRecipeTest 数据库，然后从“属性”窗口中，将 ConnectionString 属性复制到剪贴板。

ConnectionString.JPG

从“程序”菜单中打开“项目属性”页面。选择“设置”并将连接字符串粘贴到值字段中。

从类型下拉菜单中选择 (ConnectionString)，并给字符串一个名称（connStr 想到）。保存这些属性后，一个新的 app.config XML 文件将添加到您的项目中。现在可以使用 My.Settings 构造访问此处保存的数据，并且您可以在代码中的任何位置引用它。

Dim conn As New SqlConnection(My.Settings.connStr)

按 F5 运行程序。将出现以下窗口

ScreenShotDataEntry.JPG

创建新材料

  • 从“材料类型”组合框中选择“原材料”。
  • 选择一个材料类别。
  • 从“材料”组合框中输入或选择一个材料/配方。如果选择了配方，可以通过单击“显示配方”按钮显示配方。

如果所选配方没有配方，您可以通过选择“化学品”列表中的行并单击“添加”按钮来构建新配方。提供 PPH（每百份）值并单击“确定”。注意，PPH 必须是数字。示例中省略了数据验证。

完成配方创建后，单击“新建”将数据保存到数据库。此时会调用 SQL 存储过程。

通过从组合框中选择配方并单击“显示配方”来检查您的输入。（请注意，DataGridView 已很好地格式化和排序数据。）

关注点

有关更多信息，您可以查看 Stephen Forté 的网站（链接在页面顶部），或者 Robin_Roy 在 CP 上发表的这篇非常有用的文章：表值参数。

历史

  • 版本 1。
© . All rights reserved.