Dapper with SQL Server output bigint parameter returns int32

Question

I have the following C# code and a SQL Server stored procedure.

private static long GetTestBigintOutput(SqlConnection dbConn) 
{
    var sqlParams = new DynamicParameters();
    sqlParams.Add("@input_param", 1);
    sqlParams.Add("@output_param", SqlDbType.BigInt, direction: ParameterDirection.Output);

    dbConn.Execute("stp_zzz_test_output_bigint", sqlParams, commandType: CommandType.StoredProcedure);

    var output = sqlParams.Get<long>("@output_param");

    return output;
}

static void Main(string[] args) 
{
    Console.WriteLine("Hello World!");

    try 
    {
        var output = GetTestBigintOutput(GetDbConnection());
        Console.WriteLine($"Output = {output}");
    }
    catch (Exception ex) 
    {
        Console.WriteLine(ex.Message);                
    }

    Console.ReadKey();
}

Stored procedure:

CREATE PROCEDURE stp_zzz_test_output_bigint
    (@input_param bigint,
     @output_param bigint OUTPUT)
AS
BEGIN
    SET @output_param = @input_param  
END

The output of this simple console app is

Hello World!
Unable to cast object of type 'System.Int32' to type 'System.Int64'.

Is this to be expected? My other stored procedures return values that are tied to BIGINT columns, but the actual returned value will fit in an Int32. However I can't be sure beforehand what the actual returned value will be and this will change once the program is running.

I know I can change the

var output = sqlParams.Get<long>("@output_param");

to

var output = sqlParams.Get<dynamic>("@output_param");

However I feel that this is a kind of cheating.

Any thoughts?

regards Paul

Try changing `var output = sqlParams.Get("@output_param");` to `var output = sqlParams.Get("@output_param");` Refer [this link] (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings) for mapping of datatypes between SQL and C# — Hemant Halwai, Aug 07 '20 at 09:08
Try to define the **input parameter** to also be of `BigInt` type: `sqlParams.Add("@input_param", SqlDbType.BigInt, 1);` — marc_s, Aug 07 '20 at 09:10
@HemantHalwai, `long` is just a language specific alias for `System.Int64`. They are the very same type. — Aluan Haddad, Aug 07 '20 at 09:16

score 3 · Accepted Answer · edited Aug 07 '20 at 10:43

3

I don't think the Add method of DynamicParameters accepts SqlDbType as the dbType parameter. It should be DbType instead, so you can try to change following:

sqlParams.Add("@output_param", SqlDbType.BigInt, direction: ParameterDirection.Output);

to that:

sqlParams.Add("@output_param", dbType: DbType.Int64, direction: ParameterDirection.Output);

edited Aug 07 '20 at 10:43

Amit Joshi

15,448
21
77
141

answered Aug 07 '20 at 09:27

Jens

410
3
13

beat me to it :) but yes, this ^^^ – Marc Gravell Aug 07 '20 at 09:30
tried changig it to sqlParams.Add("@output_param", DbType.Int64, direction: ParameterDirection.Output); and var output = sqlParams.Get("@output_param"); Still the same result – Paul Sjoerdsma Aug 07 '20 at 10:45
1

Try to be explicit where you want to pass your arguments by specifying a name. See https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/named-and-optional-arguments. Most likely you passed `DbType` at the wrong position. – Jens Aug 07 '20 at 11:07
Thanks, using a named param did the trick.. Once you see it, it is easy.. – Paul Sjoerdsma Aug 07 '20 at 11:20

Dapper with SQL Server output bigint parameter returns int32

1 Answers1