如何在 .NET 中使用 SQL 用户定义函数？

How to use SQL user defined functions in .NET?

提问by Shimmy Weitzhandler

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.

现在我想在我的 .NET C# 或 VB.NET 代码中运行它。

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):

我使用实体框架，我尝试用函数映射来映射它，但没有成功。我不在乎用简单的 DbCommand 来做，问题是我没有得到任何结果（该函数存在于实体类中）：

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.

有什么解决办法吗？欢迎使用 C# 或 VB.NET 中的帖子。

回答by Jonathan Rupp

It sounds like the rightway 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.

在这种情况下，听起来正确的方法是使用实​​体框架的功能来定义 .NET 函数并将其映射到您的 UDF，但我想我明白为什么您在使用 ADO 时没有得到预期的结果.NET 来做到这一点——你告诉它你正在调用一个存储过程，但你实际上是在调用一个函数。

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

回答by Daniel Neel

This is very similar to the above answer, but the below code allows you to call a UDF with any number of parameters and any return type. This might be useful as a more general solution. This also hasn't been tested thoroughly...I think it will have some problems with varchars.

这与上面的答案非常相似，但是下面的代码允许您使用任意数量的参数和任意返回类型调用 UDF。作为更通用的解决方案，这可能很有用。这也没有经过彻底的测试......我认为它会与 varchars 有一些问题。

public class MyDBAccess
{
    private SqlConnection sqlConnection = new SqlConnection("databaseconnectionstring");

    public int GetUserIdByUsername(string username)
    {
        int userID = CallUDF<int>("dbo.fn_GetUserId_Username", new SqlParameter("@Username", username));
        return userID;
    }

    internal static T1 CallUDF<T1>(string strUDFName, params SqlParameter[] aspParameters)
    {
        using (SqlConnection scnConnection = sqlConnection)
        using (SqlCommand scmdCommand = new SqlCommand(strUDFName, scnConnection))
        {
            scmdCommand.CommandType = CommandType.StoredProcedure;

            scmdCommand.Parameters.Add("@ReturnValue", TypeToSqlDbType<T1>()).Direction = ParameterDirection.ReturnValue;
            scmdCommand.Parameters.AddRange(aspParameters);

            scmdCommand.ExecuteScalar();

            return (T1)scmdCommand.Parameters["@ReturnValue"].Value;
        }
    }

    private SqlDbType TypeToSqlDbType<T1>()
    {
        if (typeof(T1) == typeof(bool))
        {
            return SqlDbType.Bit;
        }
        else if (typeof(T1) == typeof(int))
        {
            return SqlDbType.Int;
        }
        //
        // ... add more types here
        //
        else
        {
            throw new ArgumentException("No mapping from type T1 to a SQL data type defined.");
        }
    }
}