Passing an array of parameters to a stored procedure
Solution 1:
If you are using Sql Server 2008 or better, you can use something called a Table-Valued Parameter (TVP) instead of serializing & deserializing your list data every time you want to pass it to a stored procedure.
Let's start by creating a simple schema to serve as our playground:
CREATE DATABASE [TestbedDb]
GO
USE [TestbedDb]
GO
/* First, setup the sample program's account & credentials*/
CREATE LOGIN [testbedUser] WITH PASSWORD=N'µ×?
?S[°¿Q¥½q?_Ĭ¼Ð)3õļ%dv', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
CREATE USER [testbedUser] FOR LOGIN [testbedUser] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', N'testbedUser'
GO
/* Now setup the schema */
CREATE TABLE dbo.Table1 ( t1Id INT NOT NULL PRIMARY KEY );
GO
INSERT INTO dbo.Table1 (t1Id)
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);
GO
With our schema and sample data in place, we are now ready to create our TVP stored procedure:
CREATE TYPE T1Ids AS Table (
t1Id INT
);
GO
CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY )
AS
BEGIN
SET NOCOUNT ON;
SELECT Table1.t1Id FROM dbo.Table1 AS Table1
JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id;
END
GO
With both our schema and API in place, we can call the TVP stored procedure from our program like so:
// Curry the TVP data
DataTable t1Ids = new DataTable( );
t1Ids.Columns.Add( "t1Id",
typeof( int ) );
int[] listOfIdsToFind = new[] {1, 5, 9};
foreach ( int id in listOfIdsToFind )
{
t1Ids.Rows.Add( id );
}
// Prepare the connection details
SqlConnection testbedConnection =
new SqlConnection(
@"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5" );
try
{
testbedConnection.Open( );
// Prepare a call to the stored procedure
SqlCommand findMatchingRowsInTable1 = new SqlCommand( "dbo.FindMatchingRowsInTable1",
testbedConnection );
findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure;
// Curry up the TVP parameter
SqlParameter sqlParameter = new SqlParameter( "Table1Ids",
t1Ids );
findMatchingRowsInTable1.Parameters.Add( sqlParameter );
// Execute the stored procedure
SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader( );
while ( sqlDataReader.Read( ) )
{
Console.WriteLine( "Matching t1ID: {0}",
sqlDataReader[ "t1Id" ] );
}
}
catch ( Exception e )
{
Console.WriteLine( e.ToString( ) );
}
/* Output:
* Matching t1ID: 1
* Matching t1ID: 5
* Matching t1ID: 9
*/
There is probably a less painful way to do this using a more abstract API, such as Entity Framework. However, I do not have the time to see for myself at this time.
Solution 2:
Use a stored procedure:
EDIT: A complement for serialize List (or anything else):
List<string> testList = new List<int>();
testList.Add(1);
testList.Add(2);
testList.Add(3);
XmlSerializer xs = new XmlSerializer(typeof(List<int>));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);
string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());
The result (ready to use with XML parameter):
<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>1</int>
<int>2</int>
<int>3</int>
</ArrayOfInt>
ORIGINAL POST:
Passing XML as parameter:
<ids>
<id>1</id>
<id>2</id>
</ids>
CREATE PROCEDURE [dbo].[DeleteAllData]
(
@XMLDoc XML
)
AS
BEGIN
DECLARE @handle INT
EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc
DELETE FROM
YOURTABLE
WHERE
YOUR_ID_COLUMN NOT IN (
SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.')
)
EXEC sp_xml_removedocument @handle