呼叫使用ADO .NET SQL函数 [英] Call SQL Function using ADO .NET

问题描述

我要调用SQL Server中创建函数，该函数接收两个参数并返回一个整数。当我打电话StoreProcedure我用下面的code：

I want call function created in SQL Server, which receive two parameters and returns an integer. When I call StoreProcedure I use the following code:

    sqlcmd.CommandType = CommandType.StoredProcedure
    sqlcmd.CommandText = "PROCEDURE_NAME"

    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param1", Utilities.NothingToDBNull(user)))
    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param2", Utilities.NothingToDBNull(password)))
    da = New SqlClient.SqlDataAdapter()
    da.SelectCommand = sqlcmd
    table = New DataTable()
    da.Fill(table)

在这种情况下，我必须通过存储过程返回的表。如果我想使用的功能，返回标值，而不是存储过程？

In this case i have a table returned by Stored Procedure. What changes if I want use Function that returns scalar value instead of Store Procedure?

推荐答案

您不能直接调用该函数，只有 StoredProcedure的，文本（查询的），和 TableDirect 是允许的。既然你已经暴露与存储过程，为什么不创建一个具有功能上的程序？

You can't call that function directly, only StoredProcedure, Text (query), and TableDirect are allowed. Since you are already exposed with stored procedure, why not create a procedure that has the function on it?

在你的 C＃ code，您可以使用的ExecuteScalar 你的命令对象的

In your C# code, you can use the ExecuteScalar of your command object

sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.CommandText = "PROCEDURE_NAME"
sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param1", Utilities.NothingToDBNull(user)))
sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param2", Utilities.NothingToDBNull(password)))
Dim obj as Object = sqlcmd.ExecuteScalar() 
' obj hold now the value from the stored procedure.

您的存储过程应该是现在这个样子，

Your Stored Procedure should look like this now,

CREATE PROCEDURE PROCEDURE_NAME
    @param1 VARCHAR(15),
    @param2 VARCHAR(15)
AS
BEGIN
    SELECT function_name(@param1, @param2)
    FROM...
    WHERE....
END

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