You can also use XML to pass in a parameter list into a stored procedure:
1) In Visual Studio:
Create a new Tableadapter and create a Typed Dataset to get a single record:
SELECT * FROM myTable WHERE (ID = @ID)
2) In SQL Server Manager:
Create a stored procedure with the same select fields as your typed dataset:
CREATE PROCEDURE [dbo].[usrsp_GetIds]
@paramList xml = NULL
AS
SET NOCOUNT ON;
/*
Create a temp table to hold paramaters list.
Parse XML string and insert each value into table.
Param list contains: List of ID's
*/
DECLARE @tblParams AS TABLE (ID INT)
INSERT INTO @tblParams(ID)
SELECT
XmlValues.ID.value('.', 'INT')
FROM
@paramList.nodes('/params/value') AS XmlValues(ID)
/*
Select records that match ID's in param list:
*/
SELECT * FROM myTable
WHERE
ID IN (
SELECT ID FROM @tblParams
)
3) In Visual Studio:
Add a New Query to your Tableadapter, select the stored procedure created above usrsp_GetIds and name it FillBy_Ids. This creates the command:
TableAdapter.FillBy_Ids(@paramList)
4) In Visual Studio:
In your .net code create a utility function to convert an array of strings to XML:
''' <summary>
''' Converts an array of strings to XML values.
''' </summary>
''' <remarks>Used to pass parameter values to the data store.</remarks>
Public Shared Function ConvertToXML(xmlRootName As String, values() As String) As String
'Note: XML values must be HTML encoded.
Dim sb As New StringBuilder
sb.AppendFormat("<{0}>", HttpUtility.HtmlEncode(xmlRootName))
For Each value As String In values
sb.AppendLine()
sb.Append(vbTab)
sb.AppendFormat("<value>{0}</value>", HttpUtility.HtmlEncode(value))
Next
sb.AppendLine()
sb.AppendFormat("</{0}>", xmlRootName)
Return sb.ToString
End Function
Usage Example:
Fill your data table using the strongly typed functions by passing a list of strings as a parameter:
'Create a list of record IDs to retrieve:
Dim ids as New List(of String)
ids.Add(1)
ids.Add(2)
ids.Add(3)
'Convert the list of IDs to an XML string:
Dim paramsXml As String = ConvertToXML("params", ids.ToArray)
'Get the records using standard DataTable & TableAdapter methods:
Using myDT As New MyDataTable
Using myTA As New MyTableAdapter
myTA.FillBy_Ids(myDT, paramsXml)
For Each row In myDT
'do stuff:
Next
End Using
End Using
