如何使用SQL用户定义的函数在.NET？ [英] How to use SQL user defined functions in .NET?

问题描述

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

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

现在我想在我的.NET C＃或VB.NET code运行它。

Now I want to run it within my .NET C# or VB.NET code.

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

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

有没有什么解决办法吗？ 帖子在C＃或者VB.NET将welcommed。

Is there any solution? Posts in either C# or VB.NET will be welcommed.

推荐答案

这听起来像的右键的方式在这种情况下是使用实体框架的功能来定义一个.NET函数和地图这给你的UDF，但是我想我明白为什么你没有得到你所期望的结果，当您使用ADO.NET来做到这一点 - 你告诉它你调用存储过程，但你实际上调用的功能。

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.

试试这个：

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

这篇关于如何使用SQL用户定义的函数在.NET？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！