呼叫使用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屋!