Can Dapper return values from a SQL function?

Question

I have a SQL function that returns an INT, when I try to call it via dapper I always get no results back.

I'm calling it like this:

var result = _connection.Query<int>("functionname", new {Parm = 123}, commandType: CommandType.StoredProcedure);

Does dapper support sql functions?

Alex · Accepted Answer · 2012-03-26 11:35:51Z

16

Dapper should support it. Are you sure, your function is in the right database?

Here's a quick VB.NET example.

   Using conn = IDbConnectionFactory.CreateFromProvider("System.Data.SqlClient", CONNECTION_STRING)
                Dim sqlCommand As String = "SELECT dbo.fx_SumTwoValues(@valueOne,@valueTwo) As SumOfTwoValues"
                conn.Open()
                Dim result = (conn.Query(Of Integer)(sqlCommand, New With {.valueOne = 1, .valueTwo = 2})).First()
                Console.WriteLine(result.ToString)
            End Using

And here's the function I created on the same db I'm using in my connection.

CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
END
GO

answered Mar 26, 2012 at 11:35

Alex

8,1561 gold badge45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ilivewithian Over a year ago

The bit I was doing wrong was calling functionname and it should have been select dbo.functionname as columnname

Alex Over a year ago

@hellboy Any exception thrown by the ODP.NET (Or whatever you use) driver is propagated to the caller.

Ahmad Aghazadeh · Accepted Answer · 2018-07-17 07:49:34Z

9

Can use this:

result = _connection.Query<dynamic>("SELECT dbo.functionName(@Parm)", new {Parm = 123},commandType: CommandType.Text);

answered Jul 17, 2018 at 7:49

Ahmad Aghazadeh

17.2k13 gold badges105 silver badges100 bronze badges

Collectives™ on Stack Overflow

Can Dapper return values from a SQL function?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related