Pass datatable as a parameter to a SQL Server stored proc from VB.net

Please suggest how to do this. Than开发者_运维问答ks.

---

PrivateFunction GetDataFromDb(ByVal lcSQL AsString, ByVal loCommandType As CommandType, _
    ByVal lcTableName AsString, ByValParamArray loParameters() As SqlParameter) As DataSet

    Dim loResult As DataSet
    Dim loConnection As SqlConnection
    Dim loCommand As SqlCommand
    Dim loAdapter As SqlDataAdapter
    Dim i As Int32
    Dim loParameter As SqlParameter

    Try

        'Create and open connection to the Northwind database
        loConnection = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=(local);Connect Timeout=30")
        loConnection.Open()

        'Prepare command and to select data from the database
        loCommand = New SqlCommand(lcSQL, loConnection)
        loCommand.CommandType = loCommandType

        IfNot loParameters IsNothingThen
            ForEach loParameter In loParameters
                loCommand.Parameters.Add(loParameter)
            Next
        EndIf

        loAdapter = New SqlDataAdapter(loCommand)

        loResult = New DataSet
        loAdapter.Fill(loResult, lcTableName)

        'Return list of the customers as a DataSet
        Return loResult

    Catch ex As Exception
        Throw ex
    Finally

        'Clean resources
        IfNot loAdapter IsNothingThen
            loAdapter.Dispose()
            loAdapter = Nothing
        EndIf

        IfNot loCommand IsNothingThen
            loCommand.Dispose()
            loCommand = Nothing
        EndIf

        IfNot loConnection IsNothingThen

            If loConnection.State = ConnectionState.Open Then
                loConnection.Close()
            EndIf

            loConnection.Dispose()
            loConnection = Nothing

        EndIf
    EndTry

EndFunction

Found at: http://support.microsoft.com/kb/555266

---

Pass it in as XML datatype, i just did this a couple months ago. so i will re edit when i find some code that handles it.

Private Function AddToList(dtData As DataTable) As List(Of [Integer])
Dim ListOfInt As New List(Of Integer)()
For Each row As DataRow In dtData.Rows
    For Each Col As DataColumn In dtData.Columns
        ListOfInt.Add(row(Col).ToString())
    Next
Next
Return ListOfInt
End Function
Private Function DataToXML() As XDocument
        Dim DataDoc As XDocument = <?xml version='1.0'?>
                                   <Root>
                                       <%= RenderKeys(SelectedDataValues) %>
                                   </Root>
        Return DataDoc
End Function    
Private Function RenderKeys(ByVal keys As List(Of Integer)) As Collection(Of XElement)
        Dim ElementCollection As New Collection(Of XElement)
        For Each Key As Integer In keys
            Dim XKey As XElement = <Key ID=<%= Key %>/>
            ElementCollection.Add(XKey)
        Next
        Return ElementCollection
End Function

This just takes a list of IDs from a table that was pulled from SQL Server and add its each id to a list (of integer) Then in your sproc just add an xml data type for the data coming in.