To pass a list of Integers from .NET C# to a Stored Procedure in your MS SQL Server, take a look on the following article:
Serializer
First of all, you need to implement a simple and reliable Object-to-XML Serializer:
public string serializeObjectToXML<T>(T o) { return SerializeToXMLString<T>(o, Encoding.Unicode, true); } public string SerializeToXMLString<T>(T XMLObj, Encoding encoding, bool removeNamespace) { MemoryStream memStrm = new MemoryStream(); SerializeToXMLString(XMLObj, memStrm, encoding, removeNamespace); memStrm.Position = 0; var sr = new StreamReader(memStrm); return sr.ReadToEnd(); } public void SerializeToXMLString<T>(T XMLObj, Stream s, Encoding encoding, bool removeNamespace) { try { XmlSerializer xmlSerializer = new XmlSerializer(typeof(T)); XmlTextWriter xmlSink = new XmlTextWriter(s, encoding); xmlSink.Formatting = Formatting.Indented; if (removeNamespace) { XmlSerializerNamespaces xs = new XmlSerializerNamespaces(); xs.Add("", ""); xmlSerializer.Serialize(xmlSink, XMLObj, xs); } else xmlSerializer.Serialize(xmlSink, XMLObj); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); if (ex.InnerException != null) Console.WriteLine(ex.InnerException); } } private static string GetXMLIntList(List<int> JobList) { Serializer s = new Serializer(); return s.serializeObjectToXML(JobList); }
Create list of Integers
Next step: create a list of Integers and serialize it to an XML-String.
List<int> ListOfInteger = new List<int>() { 1, 2, 3, 4, 5, 100, 101 }; string xml_ListOfInteger = GetXMLIntList(ListOfInteger);
The content of xml_ListOfInteger is now:
1 2 3 4 5 100 101
Create Stored Procedure
Create your Stored Procedure and declare the XML document as a parameter. The following example returns the list directly.
CREATE PROCEDURE [dbo].[GetValues] @xml nvarchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @idoc int; DECLARE @doc nvarchar(max) SET @doc = CONVERT(nvarchar(max),@xml); EXEC sp_xml_preparedocument @idoc OUTPUT, @doc SELECT Value FROM OPENXML(@idoc, 'ArrayOfInt/int') WITH ( Value int 'text()' ) END