Wednesday, July 05, 2006

Q: How could I quickly pass array from business layer (e.g. ASP.NET 2.0) to MS SQL Server 2005?
SQL Server - No Arrays? No Problem!

1) Prepare XML string like this:
<data>
 <meta>
  <key>Key1</key>
  <value>Value1</value>
 </meta>
 <meta>
  <key>Key2</key>
  <value>Value2</value>
 </meta>
</data>
2) Pass it to Stored Procedure:
command.Parameters.Add("@Tags", SqlDbType.Xml).Value = NameValueToXml(post.Tags)
3) Stored Procedure:
CREATE PROCEDURE SavePost
(
...
@Tags XML
)
AS
...
INSERT INTO Xxx
SELECT ItemData.row.value('key[1]', 'varchar(200)'),
ItemData.row.value('value[1]', 'varchar(200)')
FROM @Tags.nodes('/data/meta') ItemData(row)

No comments:

Followers

About Me

My photo
Email me: blog@postjobfree.com