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

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

@return

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.

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

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.

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

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.

Support xml types by default (XmlDocument, XDocument, XElement) - fix D…

Navigation Menu

Repository navigation

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

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Activity

mgravell commented on Dec 24, 2015

dalvirsaini commented on Dec 24, 2015

NickCraver commented on Dec 24, 2015

mgravell commented on Dec 24, 2015

jez9999 commented on Mar 1, 2016

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions