你也可以使用XML将参数列表传递给存储过程:
1)在Visual Studio中:
创建一个新的Tableadapter并创建一个Typed Dataset以获取单个记录:
SELECT * FROM myTable WHERE (ID = @ID)
2) 在SQL Server Manager中:
创建一个存储过程,其选择的字段与您键入的数据集相同:
CREATE PROCEDURE [dbo].[usrsp_GetIds]
@paramList xml = NULL
AS
SET NOCOUNT ON;
DECLARE @tblParams AS TABLE (ID INT)
INSERT INTO @tblParams(ID)
SELECT
XmlValues.ID.value('.', 'INT')
FROM
@paramList.nodes('/params/value') AS XmlValues(ID)
SELECT * FROM myTable
WHERE
ID IN (
SELECT ID FROM @tblParams
)
3) 在Visual Studio中:
向Tableadapter添加一个新查询,选择上面创建的存储过程usrsp_GetIds并将其命名为FillBy_Ids。这将创建以下命令:
TableAdapter.FillBy_Ids(@paramList)
4) 在Visual Studio中:
在您的.NET代码中创建一个实用函数,将字符串数组转换为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
使用示例:
通过将字符串列表作为参数传递,使用强类型函数填充数据表:
'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