Using Dapper to return XML string from T-SQL stored procedures

Question

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.

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.

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.

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.

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.

Morrolan Morrolan · Accepted Answer · 2017-04-13T13:19:55

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");

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);

or

var fullResult = string.Concat(result);

or, all in one step:

var result = string.Concat(conn.Query<string>(
    @"select * from <someTable> for xml auto", buffered: false));

So, there is no need for me to implement ExcuteXmlReader method myself, and now I can let Dapper handle the parameters normally.

Using Dapper to return XML string from T-SQL stored procedures

1 Answers