Can Dapper return values ​​from an SQL function?

Question

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

I call it this way:

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

Does sql function support?

Answer 1

Dapper must support it. Are you sure your function is in the correct database?

Here is an example of fast VB.NET.

  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 is the function that I created on the same db that I use in my connection.

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

Answer 2

Try the following:

 result = _connection.Query<dynamic>("SELECT dbo.functionName(" + fnParam + ")", commandType: CommandType.Text);