SQL Server 将datatable作为参数传递给存储过程以执行更新

pnwntuvh  于 2022-12-17  发布在  其他
关注(0)|答案(1)|浏览(171)

我需要将整个表传递给存储过程以运行更新查询。
这是我的代码:

CREATE TYPE [dbo].[UploadData] AS TABLE
(
    [CODE] [varchar](4) NULL,
    [SERIALNUMBER] [varchar](20) NULL,
    [PRODUCTCODE] [varchar](20) NULL,
    [THRESHOLD] [int] NULL  
)

这是我的存储过程:

CREATE PROCEDURE sp_UpdateExchangeThreshold
    @TableType dbo.UploadData READONLY 

   UPDATE [dbo].[ExchangeData_20221213] 
   SET t.Threshold = tbl.THRESHOLD
   FROM [dbo].[ExchangeData_20221213] t
   INNER JOIN @TableType AS tbl ON t.ProductCode = tbl.PRODUCTCODE 
                                AND t.SerialNumber = tbl.SERIALNUMBER

在我的VB.net代码中,我有这样的代码:

Using sqlCommand As SqlCommand = New SqlCommand()
    sqlCommand.Connection = connection
    sqlCommand.CommandText = "sp_UpdateExchangeThreshold"
    sqlCommand.CommandTimeout = 180
    sqlCommand.CommandType = CommandType.StoredProcedure

    Dim parameterList As SqlParameter = New SqlParameter("@TableType", SqlDbType.Structured)
    parameterList.Value = dataTable
    parameterList.Direction = ParameterDirection.Input
    sqlCommand.Parameters.Add(parameterList)

    sqlCommand.ExecuteNonQuery()
End Using

我无法更新记录。我做错了吗?请帮助

dy1byipe

dy1byipe1#

不幸的是,一个.net ado数据表不是一个sql server表类型。它们彼此没有任何关系。你可以在Oracle,mysql或任何基于服务器的系统中使用数据表。你甚至可以在access数据库中使用数据表。
因此,datatable对象是平台无关的(中立的),因此不能作为sql表类型传递。
然而,你并不一定要把一张table递回去。真正的问题是你想用这张table做什么?
是否要编辑、更新或将该表中的行插入回数据库?
如果是,那么使用表适配器(这就是它们的用途)。
他们不仅会为您做所有的肮脏的工作,而且该表可以有更新,删除,编辑和插入/添加。
并且使用一个命令,您可以一次发送整个表+所有编辑/更新/删除/添加。
像这样说:

Dim rstHotels As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Dim strSQL As String = "SELECT * FROM tblHotelsD"
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            ' delete first row
            rstData.Rows(0).Delete()    ' delete first row

            rstData.Rows(2).Item("City") = "Zoo" ' edit/modify the 3rd row

            Dim OneNewRow As DataRow = rstData.NewRow

            OneNewRow("FirstName") = "First Name"
            OneNewRow("LastName") = "last Name"
            OneNewRow("HotelName") = "My Hotel Name"
            OneNewRow("Active") = True

            rstData.Rows.Add(OneNewRow)

            ' now, lets send edits/delete/inserts back to database.
            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daU As New SqlCommandBuilder(da)

            da.Update(rstData)

        End Using
    End Using

现在,我已经按照上面的方法加载了表并进行了更新,但是它们可以分开进行。

编辑:将数据表发送到SQL Server,但不发送所有行。

' so, lets "send" to the sql database some data
    Dim rstData2 As New DataTable
    rstData2.Columns.Add("HotelName", GetType(String))
    rstData2.Columns.Add("City")     ' FYI -- defualt is string 
    rstData2.Columns.Add("Active", GetType(Boolean))

    ' lets create 5 test rows of data
    For i = 1 To 5
        Dim MyNewRow As DataRow = rstData2.NewRow
        MyNewRow("HotelName") = "Hotel #" & i
        MyNewRow("City") = "City #" & i
        MyNewRow("Active") = True   ' you MUST include columnes that require deffault value
        rstData2.Rows.Add(MyNewRow)
    Next

    ' now send in one shot to database
    Using conn As New SqlConnection(My.Settings.TEST4)
        Dim strSQL As String = "SELECT ID, HotelName, City, Active FROM tblHotelsD"
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daU As New SqlCommandBuilder(da)
            da.Update(rstData2)
        End Using
    End Using

相关问题