直接从 C# 调用 SQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3013192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling SQL Functions directly from C#
提问by Sk93
I have a requirement to run a query against a database that will return either a zero or one (Checking for existance of specific criteria). The Tech specs I've been given for review state that I should be creating a stored procedure, that will return a single row, with a single column called "result" that will contain a bit value of 0 or 1. However, I'm not sure that a stored procedure would be the best approach, but am a little unsure so thought I'd ask for you opinions. The two options I can think of are:
我需要对将返回零或一的数据库运行查询（检查特定条件的存在）。我接受的技术规范指出我应该创建一个存储过程，该过程将返回一行，其中有一列名为“结果”的列将包含 0 或 1 的位值。但是，我我不确定存储过程是否是最好的方法，但我有点不确定所以想我会征求你的意见。我能想到的两个选项是：
1: Create a SQL scalar-valued function that performs the query and returns a bit. This could then be called directly from within the .Net client application using a "TEXT" SqlCommand object, and it would return a bool from the "ExecuteScalar()" method.
1：创建一个执行查询并返回位的SQL标量值函数。然后可以使用“TEXT”SqlCommand 对象直接从 .Net 客户端应用程序中调用它，并且它会从“ExecuteScalar()”方法返回一个 bool。
2: Create a stored procedure as described in the tech specs. This would then be called from the .Net Client app in the normal manner, and would return a DataTable with a single row and single column, that contains the bit value.
2：按照技术规范中的描述创建存储过程。然后，这将以正常方式从 .Net 客户端应用程序调用，并返回一个包含位值的单行单列数据表。
To me, option one seems the best. However, something in the back of my head is saying this isn't such a good idea.
对我来说，选项一似乎是最好的。然而，我脑子里的东西在说这不是一个好主意。
Please could you give your opinions and help relieve my concerns? :)
请您发表您的意见并帮助减轻我的担忧？:)
Cheers, Ian
干杯，伊恩
采纳答案by TcKs
The calling scalar-valued function is absolutely correct solution.
调用标量值函数是绝对正确的解决方案。
回答by codingbadger
Execute the Stored Procedure using the ExecuteScalar()method. You can then cast the result of this to a boolean.
使用ExecuteScalar()方法执行存储过程。然后，您可以将其结果转换为布尔值。
e.g
例如
SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("Execute dbo.usp_MyStoredProc", con);
return (Boolean)com.ExecuteScalar();
回答by rasika
This works for me and is based on this answer https://stackoverflow.com/a/3232556/1591831using a SqlDataAdapter (note that you do not need to use one) and ExecuteScalar (can use ExecuteNonQuery as shown here):
这对我有用，并且基于这个答案https://stackoverflow.com/a/3232556/1591831使用 SqlDataAdapter（请注意，您不需要使用一个）和 ExecuteScalar（可以使用 ExecuteNonQuery，如下所示）：
bool res = false;
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
using (SqlCommand comm = new SqlCommand("dbo.MyFunction", conn))
{
comm.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@MyParam", SqlDbType.Int);
// You can call the return value parameter anything, .e.g. "@Result".
SqlParameter p2 = new SqlParameter("@Result", SqlDbType.Bit);
p1.Direction = ParameterDirection.Input;
p2.Direction = ParameterDirection.ReturnValue;
p1.Value = myParamVal;
comm.Parameters.Add(p1);
comm.Parameters.Add(p2);
conn.Open();
comm.ExecuteNonQuery();
if (p2.Value != DBNull.Value)
res = (bool)p2.Value;
}
}
return res;
回答by deostroll
I suppose it depends on the logic the corresponding db function (sp/udf) has to execute.
我想这取决于相应的 db 函数（sp/udf）必须执行的逻辑。
If for e.g. we are interested in the number of times the particular db function has executed we'd definitely need to do some data manipulation and updates on various tables. Hence we'd have to go for stored procs here. If its a simple retrieval a udf will do.
例如，如果我们对特定 db 函数执行的次数感兴趣，我们肯定需要对各种表进行一些数据操作和更新。因此，我们必须在这里进行存储过程。如果它是一个简单的检索 udf 就可以了。
回答by John Gabriel Rodriguez
i use this sql scalar function
我使用这个 sql 标量函数
CREATE FUNCTION DAYSADDNOWK(@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
SET @addDate = DATEADD(d, @numDays, @addDate)
IF DATENAME(DW, @addDate) = 'sunday' SET @addDate = DATEADD(d, 1, @addDate)
IF DATENAME(DW, @addDate) = 'saturday' SET @addDate = DATEADD(d, 2, @addDate)
RETURN CAST(@addDate AS DATETIME)
END
GO
then this is my c# code
那么这是我的 C# 代码
using (SqlCommand cmd3 = new SqlCommand("SELECT dbo.DAYSADDNOWK", ClassV.con))
ClassV.con.Open();
SqlCommand brecord = ClassV.con.CreateCommand();
brecord.CommandText = "INSERT INTO TblBorrowRecords VALUES ('" + DGStudents.CurrentRow.Cells[1].Value.ToString() + "','" + DGStudents.CurrentRow.Cells[2].Value.ToString() + "','" + DGStudents.CurrentRow.Cells[4].Value.ToString() + "','" + DGStudents.CurrentRow.Cells[3].Value.ToString() + "','" + DG.CurrentRow.Cells[4].Value.ToString() + "','" + DG.CurrentRow.Cells[5].Value.ToString() + "','" + DG.CurrentRow.Cells[6].Value.ToString() + "','" +System.DateTime.Now.Date.ToShortDateString() + "' , dbo.DAYSADDNOWK(GETDATE(),5) ,null , '" + ClassV.lname.ToString() + ", " + ClassV.fname.ToString() + " " + ClassV.mname.ToString() + "', null, 'Good',null)";
var DAYSADDNOWK = brecord.ExecuteScalar();
my C# code skips the function
我的 C# 代码跳过了该函数
回答by kamahl
solving it with a stored procedure is better in the long run because it′s more flexible and adaptable
从长远来看，使用存储过程解决它更好，因为它更灵活和适应性强
