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

Question

我在数据库中创建了一个标量函数

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

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

我使用Entity Framework，尝试使用函数映射进行映射，但没有成功。 我不介意使用简单的DbCommand来实现，问题是我没有收到结果（该函数存在于Entities类中）：

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

是否有任何解决方案？ 欢迎使用C#或VB.NET发布帖子。

- Shimmy Weitzhandler

Answer 1

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

尝试这个：

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

- Jonathan Rupp

---

SqlException：'fn_GetUserId_Username' 不是一个可识别的函数名。 - Shimmy Weitzhandler

@Shimmy：请指定所有者名称，将 "dbo" 改为 "select dbo.fn_GetUserId_Username(@Username)"。 - dance2die

根据Sung Meister的建议，已进行修改以添加“.dbo”。 - Jonathan Rupp

@Jonathan Rupp，它给了我这个错误：The name 'Connection' does not exist in the current context（当前上下文中不存在名称“Connection”）.. 我该如何解决？请帮忙。 - Hammam Muhareb

@HammamMuhareb - 你需要使用包含连接的任何变量/属性 - 它可能与“Connection”不同。 - Jonathan Rupp

Answer 2

这与上面的答案非常相似，但下面的代码允许您调用具有任意数量参数和任何返回类型的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.");
        }
    }
}

- Daniel Neel

---

恰当而出色！谢谢。 - Carl Krig

如果您想调用通用函数，则可以使用以下解决方案 int userID = CallUDF<int>("dbo.fn_GetUserId_Username", new SqlParameter("@Username", username)); - David Sopko