I created a scalar function in the DB
我在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):
我使用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;
}
Is there any solution? Posts in either C# or VB.NET will be welcommed.
有什么解决方案吗? C#或VB.NET中的帖子都会受到欢迎。
2 个解决方案
#1
15
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.
在这种情况下,听起来正确的方法是使用实体框架的功能来定义.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
}
}
#2
3
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会有一些问题。
internal static T1 CallUDF<T1>(string strUDFName, params SqlParameter[] aspParameters)
{
using (SqlConnection scnConnection = GetConnection())
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 static SqlDbType TypeToSqlDbType<T1>()
{
if (typeof(T1) == typeof(bool))
{
return SqlDbType.Bit;
}
.
.
.
else
{
throw new ArgumentException("No mapping from type T1 to a SQL data type defined.");
}
}
#1
15
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.
在这种情况下,听起来正确的方法是使用实体框架的功能来定义.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
}
}
#2
3
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会有一些问题。
internal static T1 CallUDF<T1>(string strUDFName, params SqlParameter[] aspParameters)
{
using (SqlConnection scnConnection = GetConnection())
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 static SqlDbType TypeToSqlDbType<T1>()
{
if (typeof(T1) == typeof(bool))
{
return SqlDbType.Bit;
}
.
.
.
else
{
throw new ArgumentException("No mapping from type T1 to a SQL data type defined.");
}
}