使用Dapper从T-SQL存储过程返回XML字符串 [英] Using Dapper to return XML string from T-SQL stored procedures
问题描述
我正在一个项目中,将大型VB6应用程序转换为.NET。我决定创建一个项目,为现有的VB6 ADO代码提供外观,在这里我使用令人惊叹的Dapper扩展方法来处理VB6 ADO函数用来完成的所有数据库代码。
I am working on a project to convert a large VB6 application to .NET. I decided to create a project to provide a facade to the existing VB6 ADO code, where I am using the amazing Dapper extension methods to handle all the database code that the VB6 ADO functions used to do.
我在新项目中必须支持的功能之一是能够(通过FOR XML)从T-SQL存储过程中获取XML字符串结果。 Dapper不支持返回我可以看到的XML。因此,我实现了ADO.NET ExecuteXmlReader
方法来提供此返回值。我的项目还使用Dapper DynamicParameters
捕获存储过程所需的所有输入/输出参数。
One of the features I have to support in my new project is the ability to get the XML string results from T-SQL stored procedures (via the FOR XML). Dapper doesn't have support to return this XML that I can see. So, I implemented the ADO.NET ExecuteXmlReader
method to provide this return. My project is also using Dapper DynamicParameters
to capture all the in/out parameters required for the stored procedures.
我不知道怎么做,是如何将 DynamicParameters
转换为 SqlCommand.SqlParameterCollection
,这样我就可以将这些参数填充到 ExecuteXmlReader $ c $的
SqlCommand
对象中c>方法。对于这种情况,我也必须支持输出参数。
What I don't see how to do, is how to convert the DynamicParameters
to the SqlCommand.SqlParameterCollection
so that I can populate these parameters into the SqlCommand
object for the ExecuteXmlReader
method. I have to support output parameters also for this scenario.
我可以遍历 DynamicParameters
,但是那只能得到参数名称和值。我还需要方向,类型,大小,比例和精度。 Dapper有一个 DynamicParameters.ReplaceLiterals
方法,该方法采用 IDbCommand
对象替换SQL字符串中的文字。我希望它有一种也可以填写参数的方法。
I can iterate over the DynamicParameters
, but that only gets me the parameter name, and value. I also need the direction, type, size, scale, and precision. Dapper has a DynamicParameters.ReplaceLiterals
method that takes an IDbCommand
object for replacing literals in SQL string. I wish it had a method to also fill in the parameters.
我在这里错过了明显的东西吗?如果我调用Dapper Execute
方法,则可以将 DyanmicParameters
直接传递给此方法。我只是看不到如何将它们传递给 SqlCommand
对象。
Am I missing something obvious here? If I call the Dapper Execute
method, I can pass the DyanmicParameters
directly into this method. I'm just not seeing how to pass them to the SqlCommand
object.
推荐答案
经过测试,我发现Dapper确实支持从存储过程中提取XML。
After testing, I see that Dapper does indeed support pulling the XML from stored procedures.
var result = conn.Query<string>(@"select * from <someTable> for xml auto");
这将返回一个字符串数组,每个元素最多包含2,033个字符,您可以简单地加入
This will return an array of string with each element containing up to 2,033 characters, which you can simple join to have your result as a single string.
var fullResult = string.Join("", result);
或
var fullResult = string.Concat(result);
或全部一步:
var result = string.Concat(conn.Query<string>(
@"select * from <someTable> for xml auto", buffered: false));
因此,我不需要自己实现ExcuteXmlReader方法,现在我可以让Dapper处理参数通常。
So, there is no need for me to implement ExcuteXmlReader method myself, and now I can let Dapper handle the parameters normally.
这篇关于使用Dapper从T-SQL存储过程返回XML字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!