Per diverse esigenze, spesso mi è capitato di dover inviare una lista di valori ad una Stored Procedure.
Per raggiungere questo obiettivo è possibile escogitare un workaround mediante la funzione TSQL nodes(XQuery) sui tipi di dato XML.
Vediamo due esempi per comprendere al meglio la soluzione proposta.
Esempio 1: Come inviare ad una Stored Procedure una lista di ID.
1 – Costruisco un XML che contiene la lista degli ID, come di seguito:
<MyIDList> <ID>1</ID> <ID>2</ID> </MyIDList>
In C# posso costruire l’XML nel modo seguente:
XmlDocument xmlDoc = new XmlDocument(); XmlElement xElem; //add root node XmlElement xRoot = xmlDoc.CreateElement("MyIDList"); xmlDoc.AppendChild(xRoot); //Add ID 1 xElem = xmlDoc.CreateElement("ID"); xElem.InnerText = "1"; xRoot.AppendChild(xElem); //Add ID 2 xElem = xmlDoc.CreateElement("ID"); xElem.InnerText = "2"; xRoot.AppendChild(xElem);
2 – Invio alla Stored Procedure l’XML costruito.
In C# creo un DbParam come di seguito:
//@MyIDListXML DbParameter dbParam = dbCommand.CreateParameter(); dbParam.ParameterName = "@MyIDListXML"; dbParam.DbType = DbType.Xml; dbParam.Value = xmlDoc.InnerXml; //Nota passo l'InnerXml dbCommand.Parameters.Add(dbParam);
3 – Recupero dal parametro @MyIDListXML la lista di ID in formato relazionale per poter effettuare qualsiasi tipo di operazione (JOIN, WHERE, etc.)
SELECT T.Item.value('.' , 'BIGINT') AS ID FROM @MyIDListXML.nodes('//MyIDList/ID') AS T(Item)
Il risultato sarà una tabella con due valori, come mostrato in figura:
Esempio 2: Come inviare una tabella ad una Stored Procedure.
1 – Costruisco un XML che rappresenta la tabella da inviare:
<MyList> <item attributo1="value11" attributo2="value12" /> <item attributo1="value21" attributo2="value22" /> </MyList>
In C# posso contruire l’XML nel modo seguente:
XmlDocument xmlDoc = new XmlDocument(); XmlElement xElem; //add root node XmlElement xRoot = xmlDoc.CreateElement("MyList"); xmlDoc.AppendChild(xRoot); //add item 1 xElem = xmlDoc.CreateElement("item"); xElem.SetAttribute("attributo1", "value11"); xElem.SetAttribute("attributo2", "value12"); xRoot.AppendChild(xElem); //add item 2 xElem = xmlDoc.CreateElement("item"); xElem.SetAttribute("attributo1", "value21"); xElem.SetAttribute("attributo2", "value22"); xRoot.AppendChild(xElem);
2 – Invio alla Stored Procedure l’XML costruito.
In C# creo un DbParameter come di seguito:
//@MyListXML DbParameter dbParam = dbCommand.CreateParameter(); dbParam.ParameterName = "@MyListXML"; dbParam.DbType = DbType.Xml; dbParam.Value = xmlDoc.InnerXml; //Nota passo l'InnerXml dbCommand.Parameters.Add(dbParam);
3 – Recupero dal parametro @MyListXML la lista di ID in formato relazionale per poter effettuare qualsiasi tipo di operazione (JOIN, WHERE, etc.)
SELECT T.Item.value('@attributo1' , 'varchar(10)') AS Attributo1, T.Item.value('@attributo2' , 'varchar(10)') AS Attributo2 FROM @MyListXML.nodes('//MyList/item') AS T(Item)
Il risultato sarà una tabella con i valori come mostrato in figura:
E’ possibile inviare qualsiasi struttura XML e lavorare lato SQL Engine con la funzione nodes(XQuery).