Ado.Net和BigInteger导致Int64的无效强制转换异常

I have a simple SQL query to execute in Ado.Net. It has one parameter, a BigInteger.

我有一个简单的SQL查询要在Ado.Net中执行。它有一个参数，一个BigInteger。

public int MyMethod(BigInteger executionId)
{
    int status = -1;
    using (var connection = new SqlConnection(this.ConnectionString))
    {
        connection.Open();
        var command = new SqlCommand("select ... where execution_id=@execution_id", connection) { CommandType = CommandType.Text };
        var parameterExecutionId = new SqlParameter("@execution_id", executionId) { SqlDbType = SqlDbType.BigInt };
        command.Parameters.Add(parameterExecutionId);
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                status = Convert.ToInt32(reader["status"]);
            }
        }
        connection.Close();
    }
    return status;    
}

When I am running this code I have on the ExecuteReader this exception:

当我运行此代码时，我在ExecuteReader上遇到此异常：

An exception of type 'System.InvalidCastException' 
occurred in System.Data.dll but was not handled in user code

Additional information: Failed to convert parameter 
value from a BigInteger to a Int64.

The database does have the execution_id column set to bigint.

数据库确实将execution_id列设置为bigint。

What is wrong?

哪里不对？

2 个解决方案

#1

BigInt does not correspond to BigInteger. It corresponds to long/Int64. You should probably remove all BigInteger usages from your business code and use long. ADO.NET does not understand BigInteger.

BigInt与BigInteger不对应。它对应于long / Int64。您应该从业务代码中删除所有BigInteger用法并使用long。 ADO.NET不了解BigInteger。

#2

Your BigInteger is a complex type in System.Numerics, this does not map to SQL BigInt. As a general rule, usually when mapping C# types to SQL DB types its a base type such as long, int, int64, string... in this case your secondary error message suggest Int64. If you decide keep BigInteger as the parameter you pass in then you should Cast that to a long inside your Method. You can also specify the SQL Type in the instantiation of SqlParameter.

您的BigInteger是System.Numerics中的复杂类型，它不映射到SQL BigInt。作为一般规则，通常在将C＃类型映射到SQL DB类型时，其基本类型（如long，int，int64，string ...）在这种情况下，您的辅助错误消息建议使用Int64。如果您决定将BigInteger保留为传入的参数，那么您应该将其转换为Method内部的长参数。您还可以在SqlParameter的实例化中指定SQL类型。

public int MyMethod(BigInteger executionId)
{
    int status = -1;
    using (var connection = new SqlConnection(this.ConnectionString))
    {
        connection.Open();
        var command = new SqlCommand("select ... where execution_id=@execution_id", connection)     { CommandType = CommandType.Text };

        //BigInteger to Int64 conversion long and Int64 is the same type
        long execId = (long) executionId;

        var parameterExecutionId = new SqlParameter("@execution_id", SqlDbType.BigInt, execId);
        command.Parameters.Add(parameterExecutionId);
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                status = Convert.ToInt32(reader["status"]);
            }
        }
        connection.Close();
    }
    return status;    
}