Как использовать пользовательские функции SQL в .NET?

Я создал скалярную функцию в БД

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.


.net sql user-defined-functions oledbcommand
person Shimmy Weitzhandler    schedule 29.06.2009    source источник

Ответы (2)


arrow_upward
18
arrow_downward

Похоже, что правильный способ в этом случае — использовать функциональные возможности инфраструктуры сущностей для определения функции .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 
    }
}
person Jonathan Rupp    schedule 29.06.2009
comment
Результат: SqlException: 'fn_GetUserId_Username' не является распознаваемым именем функции. - person Shimmy Weitzhandler; 29.06.2009
comment
@Shimmy: укажите имя владельца, dbo --› выберите dbo.fn_GetUserId_Username(@Username) - person dance2die; 29.06.2009
comment
Изменено, чтобы добавить .dbo, как предложил Сунг Мейстер. - person Jonathan Rupp; 29.06.2009
comment
@Jonathan Rupp выдает мне эту ошибку ::: Имя «Соединение» не существует в текущем контексте .. как я могу это исправить? плз - person Hammam Muhareb; 17.12.2012
comment
@HammamMuhareb - вам нужно использовать любую переменную / свойство, в которой есть ваше соединение - оно может отличаться от «Соединения». - person Jonathan Rupp; 18.12.2012

arrow_upward
3
arrow_downward

Это очень похоже на приведенный выше ответ, но приведенный ниже код позволяет вызывать 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.");
        }
    }
}
person Daniel Neel    schedule 28.01.2015
comment
Правильно и блестяще! Спасибо. - person Carl Krig; 03.12.2017
comment
Отличное решение, если вы хотите вызвать общую функцию, используйте int userID = CallUDF‹int›(dbo.fn_GetUserId_Username, new SqlParameter(@Username, username)); - person David Sopko; 15.07.2019