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.
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.