SQL CLR - 检索数据/更新结果

Question

作为业务逻辑的一部分，我必须从 SQL 表中检索 2000 多行。通过一些复杂的、程序性的、业务逻辑来运行这些行（是的，它必须是程序性的，并且我迄今为止研究的所有运行求和技术都不会降低效率）。然后我必须更新数据库并返回结果。

使用 SQLDataReader - 数据从 SQL 中流出，并进入我的逻辑 - 效果很好。

逻辑迭代数据，并进行必要的更改。

现在保存更改 - 我真的不想通过将参数填充到 UPDATE 语句来发送结果。如果我在 SQL 中获得结果，我会将临时表与基表连接起来，并在单个 TSQL Update 语句中执行所有更新。我的“表”位于 SQL CLR 中，而基表位于 SQL 端。

我已经阅读了有关将结果序列化为 XML 并使用 varchar 变量将其全部传输的技术 - 但我使用的是 SQL Server 2008 R2。

我研究过 UDT 和表值参数 - 但您不能使用它们在 SQL CLR 和 SQL 之间传输数据。

如果 SQL CLR 应该与复杂的业务逻辑一起使用 - 是否有发送数据的最佳实践方法？我还没有找到直接解决或讨论这个问题的例子。

Answer 1

为了一次性更新所有列，在对数据库的一次调用中的各种记录中，您可以使用 XML。
下面是您可以使用的查询示例。

if OBJECT_ID('sample1') is not null
drop table sample1
go
create table sample1(id int identity(1,1), firstname varchar(100), lastname varchar(100))
go

declare @xmldata xml
set @xmldata  = 
N'<sample>
        <data>
            <firstname>Jay</firstname>
            <lastname>Keni</lastname>
        </data>
        <data>
            <firstname>Lid</firstname>
            <lastname>kurtis</lastname>
        </data>
        <data>
            <firstname>Lea</firstname>
            <lastname>Cortez</lastname>
        </data>
</sample>'


declare @xml_hndl int
exec sp_xml_preparedocument @xml_hndl OUTPUT, @xmldata

insert sample1(firstname, lastname)
select up.firstname, up.lastname
FROM OPENXML(@xml_hndl, '/sample/data',2)
WITH  (firstname varchar(100),lastname varchar(100))up

EXEC sp_xml_removedocument @xml_hndl

select * from sample1

您可以使用任何您想要的连接操作进行更新、插入、删除，此代码适用于从 SQL Server 2005 到 Denali。