How to use SQL user defined functions in .NET?

.net, oledbcommand, sql, user-defined-functions

Solution

It sounds like the right way in this case is to use the functionality of the entity framework to define a .NET function and map that to your UDF, but I think I see why you don't get the result you expect when you use ADO.NET to do it -- you're telling it you're calling a stored procedure, but you're really calling a function.

Try this:

public int GetUserIdByUsername(string username)
{
    EntityConnection connection = (EntityConnection)Connection;            
    DbCommand com = connection.StoreConnection.CreateCommand();
    com.CommandText = "select dbo.fn_GetUserId_Username(@Username)";
    com.CommandType = CommandType.Text;
    com.Parameters.Add(new SqlParameter("@Username", username));
    if (com.Connection.State == ConnectionState.Closed) com.Connection.Open();
    try
    {
        var result = com.ExecuteScalar(); // should properly get your value
        return (int)result;
    }
    catch (Exception e)
    {
        // either put some exception-handling code here or remove the catch 
        //   block and let the exception bubble out 
    }
}

Problem

I created a scalar function in the DB ``` SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_GetUserId_Username] ( @Username varchar(32) ) RETURNS int AS BEGIN DECLARE @UserId int SELECT @UserId = UserId FROM [User] WHERE Username = @Username RETURN @UserId END ``` Now I want to run it within my .NET C# or VB.NET code. I use Entity Framework, I tried to map it with function mapping and I did not success. i don't care to do it with simple DbCommand, the problem is that I get no results (the function exists in the Entities class): ``` public int GetUserIdByUsername(string username) { EntityConnection connection = (EntityConnection)Connection; DbCommand com = connection.StoreConnection.CreateCommand(); com.CommandText = "fn_GetUserId_Username"; com.CommandType = CommandType.StoredProcedure; com.Parameters.Add(new SqlParameter("Username", username)); if (com.Connection.State == ConnectionState.Closed) com.Connection.Open(); try { var result = com.ExecuteScalar(); //always null } catch (Exception e) { } return result; } ``` Is there any solution? Posts in either C# or VB.NET will be welcommed.

Original source