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