CODEWITHSUNDEEP
c#.net.net-4.0ado.netbiginteger
Patrick Desjardins
Patrick Desjardins

Reputation: 140973

Ado.Net and BigInteger result to an invalid cast exception to Int64

I have a simple SQL query to execute in Ado.Net. It has one parameter, a 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:

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.

What is wrong?

Upvotes: 1

Views: 1694

Answers (2)

CodeCowboyOrg
CodeCowboyOrg

Reputation: 3043

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.

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;    
}

Upvotes: 1

usr
usr

Reputation: 171226

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.

Upvotes: 3

Related Questions