System.Xml.Linq.XElement cannot be used as a parameter value at Dapper

[dalvirsaini]

I am using dapper dot net for making Db calls.

now one of my sp having input parameter as Db type XML

Now i am executing sp like

var param = new DynamicParameters();
param.Add("@TagXML", string.IsNullOrEmpty(student.TagXML)?null:XElement.Parse(student.TagXML));
param.Add("@CustomTagXML", string.IsNullOrEmpty(student.CustomTagXML) ? null : XElement.Parse(student.CustomTagXML));
param.Add("@Return", strRet, direction: ParameterDirection.Output);
param.Add("@RetunErrMsg", strRetMsg, direction: ParameterDirection.Output);
using (var connection = new SqlConnection(ConnectionString))
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }
    connection.Execute("My Sp here", param, commandType: CommandType.StoredProcedure);
}

This code giving me error:

System.NotSupportedException: The member TagXML of type System.Xml.Linq.XElement cannot be used as a parameter value
   at Dapper.SqlMapper.LookupDbType(Type type, String name, Boolean demand, ITypeHandler& handler)

Why this error coming? How I can use XML type of inputs in StoredProcedure.?

[mgravell]

Good question. You can probably pass a string instead. I agree it would be
nice if we supported at least one of XmlDocument / XDocument (or element),
but that probably requires some code changes. Shouldn't be anything huge,
though.

On Thu, 24 Dec 2015 12:01 dalvirsaini [email protected] wrote:

I am using dapper dot net for making Db calls

now one of my sp having input parameter as Db type XML

Now i am executing sp like

var param = new DynamicParameters();
paramAdd("@TagXML",
stringIsNullOrEmpty(studentTagXML)?null:XElementParse(studentTagXML));
paramAdd("@CustomTagXML", stringIsNullOrEmpty(studentCustomTagXML) ? null
: XElementParse(studentCustomTagXML));
paramAdd("@return https://github.com/Return", strRet, direction:
ParameterDirectionOutput);
paramAdd("@RetunErrMsg", strRetMsg, direction: ParameterDirectionOutput);
using (var connection = new SqlConnection(ConnectionString))
{
if (connectionState != ConnectionStateOpen)
{
connectionOpen();
}
connectionExecute("My Sp here", param, commandType:
CommandTypeStoredProcedure);
}

This code giving me error:

SystemNotSupportedException: The member TagXML of type
SystemXmlLinqXElement cannot be used as a parameter value
at DapperSqlMapperLookupDbType(Type type, String name, Boolean demand,
ITypeHandler& handler)
Why this error coming? How I can use XML type of inputs in StoredProcedure?

—
Reply to this email directly or view it on GitHub
#427.

[dalvirsaini]

Thanks for reply.

By passing xml as string now exception changes to :

System.InvalidOperationException: String[29]: the Size property has an invalid size of 0.
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)

So in which version I can expect that XElement will be supported. Any time estimation?

Thanks

[NickCraver]

I haven't looked at this yet, but if it means a new dependency in CoreCLR world - I'm not totally sure it's worth having in the base package. It may not be an problem at all, just noting the potential issue here.

[mgravell]

Note: both XDocument and XmlDocument are in 23509, but yeah, I kinda agree
with you. But at the same time: our dependency tree on core-clr is also
long enough that it may be a moot point to get excited about
On 24 Dec 2015 9:24 p.m., "Nick Craver" [email protected] wrote:

I haven't looked at this yet, but if it means a new dependency in CoreCLR
world - I'm not totally sure it's worth having in the base package. It may
not be an problem at all, just noting the potential issue here.

—
Reply to this email directly or view it on GitHub
#427 (comment)
.

[jez9999]

Just like to add my 2 cents: I think it would be nice if the XML data type in SQL Server could be deserialized by Dapper into an XmlDocument or XDocument field in a .NET POCO, as well as a string (which seems to be all that is currently supported). It would be more semantically correct.