RSS Feed http://www.damba.net
Flexible T-SQL: Bending T-SQL to Get Around Everyday Programming Obstacles Part III
This blog post continues the presentation that will be given at the June 12, 2009 Macon .NET User’s group
A common problem when dealing with stored procedures in SQL Server concerns the passing of variable arrays. The developer has a stored procedure that requires an array of integers, but there’s no straightforward was to get the array to the stored procedure as there’s no such thing as a array variable in SQL Server 2000 and 2005. However, there is a workaround: pass XML from .NET to SQL Server.
There are two parts: preparing the parameter array in .NET, and processing the array in T-SQL.
Let’s handle the first part. We’re going to perform the following steps using VB.NET to prepare the XML parameter array:
1. Create a dataset in .NET
2. Add a datatable to the dataset
3. Apply ColumnMapping to the dataset
4. Use the getXML method of the dataset to get the XML
So, for Step #1, we’re going to create a new dataset and call id “FormIds”. It’s very important to name the new dataset, otherwise your XML root tag will be “<NewDataSet></NewDataSet>”.
****************** BEGIN VB.NET CODE SNIPPET **********************
Dim dtForm As New DataTable ‘The table that holds the array of FormIds
Dim ds As New DataSet("FormIds") ‘The Dataset that generates the XML
Dim dr As DataRow ‘A temporary row that adds each FormId to the table
Dim dt As DataTable ‘A result table that holds the result of the stored procedure called with the parameter array
Dim sql As String ‘the name of the stored procedure that needs the parameter array
Dim parameters As Hashtable ‘the parameters passed to the stored procedure
‘This table is our array and is arbitrarily called “datarow”
dtForm.TableName = "datarow"
‘the name of our variable
dtForm.Columns.Add("FormId")
‘additional columns can be added, each additional column will become an attribute within the xml
‘add variables to our array table
For formId as integer = 0 to 5
dr = dtForm.NewRow
dr("FormId") = formId
dtForm.Rows.Add(dr)
Next
‘add our array table to our dataset
ds.Tables.Add(dtForm)
‘add ColumnMapping so that when the dataset is converted to XML, each column is an attribute
‘example XML row = <datarow FormId="0" />
For iLoop As Integer = 0 To ds.Tables.Count - 1
For Each col As DataColumn In ds.Tables(iLoop).Columns
col.ColumnMapping = System.Data.MappingType.Attribute
Next
Next
‘get XML from our dataset
sqlXML = ds.GetXml()
‘Here’s how the XML generated by the ds.GetXML will look:
‘<FormIds>
‘ <datarow FormId="0" />
‘ <datarow FormId="1" />
‘ <datarow FormId="2" />
‘ <datarow FormId="3" />
‘ <datarow FormId="4" />
‘ <datarow FormId="5" />
‘</FormIds>
‘FormIds is the name of the dataset, datarow is the name of the table, and FormId is the name of the column.
‘Call the Stored Procedure (see separate note about the GetDataTable function
sql = "GetFormsByFormXML"
parameters = New Hashtable
parameters.Add("@xml", sqlXML)
dt = GetDataTable(sql, parameters)
‘Side note – this is a helper function to easily add parameters to a SQL query
Public Shared Function GetDataTable(ByVal Sql As String, ByVal Parameters As Hashtable) As DataTable
Dim oConnStr As String
Dim param() As SqlClient.SqlParameter
Dim ds As New DataSet
Dim tableName As String
Dim dt As DataTable
Dim parameterValue As String
Dim keyCollection As ICollection
tableName = Sql.Substring(3, Sql.Length - 3)
‘stub
oConnStr = GetConnectionString()
param = SqlHelperParameterCache.GetSpParameterSet(oConnStr, Sql, True)
keyCollection = Parameters.Keys
For Each keyname As String In keyCollection
parameterValue = Convert.ToString(Parameters(keyname))
For Each parm As SqlClient.SqlParameter In param
Select Case parm.ParameterName.ToLower
Case keyname.ToLower
parm.Value = parameterValue
End Select
Next
Next
SqlHelper.FillDataset(oConnStr, CommandType.StoredProcedure, Sql, ds, New String() {tableName}, param)
dt = ds.Tables(tableName)
ds.Tables.Remove(dt.TableName)
Return dt
End Function
****************** END VB.NET CODE SNIPPET **********************
Now for the the stored procedure that will receive the XML. We’re going to use OpenXML to parse the XML into a table variable, and then link that table variable with a query to yield the results we were looking for. To use OpenXML, perform the following steps:
1. Declare a table variable to hold the array that results from the XML
2. Declare a dochandler variable as an integer
3. Call sp_xml_preparedocument on the XML
4. Select the columns from the XML and place them into the table variable
5. Call sp_xml_removedocument on the XML to destroy it.
For this example, it is presumed that there is a table in SQL server called [Form] that has two columns, [FormId] and [Description].
****************** BEGIN T-SQL CODE SNIPPET **********************
-- Note: this is a snipped of code that would become the stored procedure GetFormsByFormXML referenced by the VB.NET code
declare @xml xml -- the incoming XML from VB.NET
-- note: in SQL Server 2000, this variable will have to be either TEXT
-- or a varchar large enough to hold all possible xml values
--example XML
set @xml = '<FormIds>
<datarow FormId="0" />
<datarow FormId="1" />
<datarow FormId="2" />
<datarow FormId="3" />
<datarow FormId="4" />
<datarow FormId="5" />
</FormIds>'
declare @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
declare @FormIds table(
FormId char(36)
)
insert into @FormIds
Select FormId
FROM OPENXML(@DocHandle, '/FormIds/datarow',1)
WITH (FormId char(36))
exec sp_xml_removedocument @DocHandle
select f.FormId
, f.Description
from form f
inner join @FormIds fi
on fpd.formid = fi.formid
****************** END T-SQL CODE SNIPPET **********************
And that concludes our example. We have created an array in VB.NET, passed that array to T-SQL, and successfully used that array to filter our result set. This procedure should help solve some of the common coding obstacles that we run into on a regular basis.
6ca56739-8d62-4a15-91e1-4f0a2f3e8d6d|0|.0