As if this wasn't a bad enough idea the first time around... anyway, those of you with an XQuery phobia might say, "Kent, your last post, it scared me. Can you show me how do this with SQLCLR instead?" Sure!
Our database is very much designed as it was before, but I've added a couple of additional stored procedures (in bold):
use master
go
drop database procexample
go
create database procexample
go
use procexample
go
create table dbo.tableA
(pkid tinyint identity(1,1) primary key
,descr varchar(50) not null)
go
create procedure dbo.tableA_Insert
(@descr varchar(50))
as
begin
set nocount on
declare @i table (pkid tinyint,descr varchar(50))
insert into dbo.tableA(descr)
output inserted.* into @i
values (@descr)
select top 1 pkid from @i
end
go
create table dbo.tableB
(
pkid tinyint identity(1,1) primary key
,pkid_tableA tinyint not null
constraint fk_tableB_tableA_PKID
foreign key references dbo.tableA(pkid)
,descr varchar(50) not null
)
go
create procedure dbo.tableB_Insert
(@pkid_tableA tinyint,@descr varchar(50))
as
begin
set nocount on
insert into dbo.tableB(pkid_tableA,descr)
values (@pkid_tableA,@descr)
end
go
The SQLCLR bits look like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Transactions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static SqlInt32 XmlLoader(SqlXml doc)
{
SqlConnection conn = new SqlConnection("context connection=true;");
SqlCommand cmdA = new SqlCommand("dbo.tableA_Insert",conn);
cmdA.Parameters.AddWithValue("@descr", null);
cmdA.CommandType = CommandType.StoredProcedure;
SqlCommand cmdB = new SqlCommand("dbo.tableB_Insert", conn);
cmdB.Parameters.AddWithValue("@descr", null);
cmdB.Parameters.AddWithValue("@pkid_tableA", null);
cmdB.CommandType = CommandType.StoredProcedure;
byte lastTableAID = 0;
int rc = -1;
XmlReader xr = doc.CreateReader();
using (TransactionScope ts = new TransactionScope())
{
try
{
conn.Open();
while (xr.Read())
{
switch (xr.Name)
{
case "tableA":
cmdA.Parameters[0].Value = xr.ReadString();
lastTableAID = (byte)(cmdA.ExecuteScalar());
while (xr.Name == "tableB")
{
cmdB.Parameters[0].Value = xr.ReadString();
cmdB.Parameters[1].Value = lastTableAID;
cmdB.ExecuteNonQuery();
xr.Read();
}
break;
default:
throw new ArgumentException();
}
}
ts.Complete();
rc = 0;
}
catch (Exception ex)
{
rc = -1;
throw ex;
}
}
return (rc);
}
};
One can test it thusly:
declare @x xml
set @x = 'BeerWaterMaltHopsYeastWineWaterGrapesYeast'
exec dbo.XmlLoader @x
This variant has one advantage over the XQuery-based example in that it can accept multiple object graphs in SqlXml document.