与Dapper一起使用Microsoft.SqlServer.Types时发生RuntimeBinderInternalCompilerException

问题描述

使用目标平台设置为以下其中之一的Sql Server Data Tools项目：

Using a Sql Server Data Tools project whose target platform is set to one of:

• SQL Server 2008


• SQL Server 2012


• SQL Server 2014

并部署到（localdb ）\Projects或（localdb）\ProjectsV12

And deploying to (localdb)\Projects or (localdb)\ProjectsV12

调用一个存储过程，该存储过程返回Geometry，Geography或HierachyId类型，例如：

Calling a stored procedure that returns a Geometry, Geography or HierachyId type such as:

CREATE PROCEDURE [dbo].[SelectSqlGeometry]
    @x Geometry
AS
    SELECT @x as y
RETURN 0

以下调用代码：

var result = Connection.Query("dbo.SelectSqlGeometry", new { x = geometry }, commandType: CommandType.StoredProcedure).First();
bool isSame = ((bool)geometry.STEquals(result.y));

导致STEquals行上出现以下异常。

results in the following exception on the STEquals line.

推荐答案

尽管根本原因不是Dapper，但有一个潜在的异常正在被吞噬。

Although the root cause is not Dapper, there is an underlying exception that is being swallowed.

使用ADO.Net代码，例如：

Using ADO.Net code like:

var geometry = Util.CreateSqlGeometry();
SqlDataReader reader=null;
SqlCommand cmd=null;
try
{
    cmd = new SqlCommand("dbo.SelectSqlGeometry", (SqlConnection)Connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@x", geometry) { UdtTypeName = "Geometry" });
    cmd.ExecuteNonQuery();
    reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        var y = (SqlGeometry)reader.GetValue(0);
        var same = geometry.STEquals(y);
    }
    reader.Dispose();
    reader = null;
}
finally
{
    if (reader != null)
    {
        if (!reader.IsClosed) try { cmd.Cancel(); }
            catch {}
        reader.Dispose();
    }
    if (cmd != null) cmd.Dispose();
    Connection.Close();
}

在 reader.GetValue

The following exception is thrown at reader.GetValue

根本异常的原因是已知的重大更改在SQL Server 2012中使用。请参阅以下MSDN文档的 SQL CLR数据类型部分

The cause of the underlying exception is a known breaking change in SQL Server 2012. See the SQL CLR Data Types section of the following MSDN documentation

对我有用的解决方法是在app.config或web.config中创建以下bindingRedirect。

The resolution, that has worked for me is to create the following bindingRedirect in the app.config or web.config.

<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.Types"
                      publicKeyToken="89845dcd8080cc91"
                      culture="neutral" />
    <bindingRedirect oldVersion="10.0.0.0"
                     newVersion="11.0.0.0"/>
  </dependentAssembly>
</assemblyBinding>
</runtime>

或者，使用.NET 4.5，您可以更改连接字符串以包括 SQL Server 2012值类型系统版本属性，以强制SqlClient加载程序集的11.0版。

Alternately, with .NET 4.5 you can change your connection string to include a value of "SQL Server 2012" for the "Type System Version" attribute to force SqlClient to load version 11.0 of the assembly.

另一种解决方法是代码，例如：

Another workaround is code like:

var geo = SqlGeography.Deserialize(rdr.GetSqlBytes(0));

但是，我不认为这是Dapper的选择。

However, I don't believe this is an option with Dapper.

这篇关于与Dapper一起使用Microsoft.SqlServer.Types时发生RuntimeBinderInternalCompilerException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！