如何在 C# 中调用 SQL 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11796530/
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
How can I call a SQL function in C#?
提问by Rika
I have created a function in SQL, now I need to use that function in my C# application.
我已经在 SQL 中创建了一个函数，现在我需要在我的 C# 应用程序中使用该函数。
I tried using something like this, but it seems I'm doing it wrong since I'm getting:
我尝试使用这样的东西，但似乎我做错了，因为我得到：
Must declare the scalar value '@2064734117'
...when I give 2064734117as the first parameter and 1as the second parameter. Here is the code I'm talking about:
...当我给出2064734117第一个参数和1第二个参数时。这是我正在谈论的代码：
SqlConnection con = new SqlConnection(clsDb.connectionString);
string query = string.Format("select Function1(@{0},@{1}) ",
int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString()),1);
con.Open();
SqlCommand cmd = new SqlCommand(query,con);
SqlDataAdapter READER = new SqlDataAdapter();
READER.SelectCommand = cmd;
DataTable table = new DataTable();
READER.Fill(table);
radGridView1.DataSource = table;
con.Close();
And my function takes two integer parameters and returns a table. I checked it in Visual Studio and it worked, but I couldn't get it to work in my application.
我的函数接受两个整数参数并返回一个表。我在 Visual Studio 中检查它并且它工作，但我无法让它在我的应用程序中工作。
And this is my function declaration:
这是我的函数声明：
ALTER FUNCTION dbo.Function1
(
/*
@parameter1 int = 5,
@parameter2 datatype
*/
@ID int,
@clsTypeID int
)
RETURNS TABLE/* @table_variable TABLE (column1 datatype, column2 datatype) */
AS
/*BEGIN */
/* INSERT INTO @table_variable
SELECT ... FROM ... */
RETURN SELECT * FROM tblCLASS2
WHERE STNID = @ID AND CLASSTYPEID = @clsTypeID
/*END */
/*GO*/
采纳答案by Mithrandir
Your SQL is a bit off, it should be:
你的 SQL 有点不对，应该是：
string query = string.Format("select * from dbo.Function1({0},{1});", int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString()),1);
You might want to use SqlParameter-objects to prevent sql injections:
您可能希望使用 SqlParameter-objects 来防止 sql 注入：
string query = "select * from dbo.Function1(@pa1,@par2);";
cmd.Parameters.Add("@par1", SqlDbType.Int).Value = int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString());
cmd.Parameters.Add("@par2", SqlDbType.Int).Value = 1;
回答by jdb1a1
You can do something like this:
你可以这样做：
myConn.Open();
//generating the new command for our database
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT OBJECTID_1, NDNT as theAddress, MIN(ABS(x - " + double.Parse(x.ToString()) + ") + ABS(y - " + double.Parse(y.ToString()) +")) from dbo.DWH_OUTPUT GROUP BY OBJECTID_1,NDNT HAVING (MIN(ABS(x - " + double.Parse(x.ToString()) + ") + ABS(y - " + double.Parse(y.ToString()) + ")) = (Select MIN(ABS(a.x - " + double.Parse(x.ToString()) + ") + ABS(a.y - " + double.Parse(y.ToString()) + ")) from dbo.DWH_OUTPUT a ) )";
cmd.Connection = myConn;
//getting some more ado.net objects
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds, @"Addresses");
if (ds.Tables[0].Rows.Count > 0)
{
theAddress = ds.Tables[0].Rows[0][@"theAddress"] + @" (proximity address)";
}
myConn.Close();
Note how in this example, you set the SqlCommand's CommandType to CommandType.Text. Specify your command parameters (i.e. the select function in your code snippet), and then populate the dataset with the Fillmethod. Then you can pluck out the values from the rows as you ordinarily would with standard ado.net.
请注意在此示例中如何将 SqlCommand 的 CommandType 设置为CommandType.Text. 指定您的命令参数（即代码片段中的 select 函数），然后使用该Fill方法填充数据集。然后，您可以像通常使用标准 ado.net 一样从行中提取值。
If you need to call a stored proc, have a look at this:
如果您需要调用存储过程，请查看以下内容：
回答by Marc Gravell
At a glance, the first thing I can see is that you aren't specifying the object owner / schema; that is required for functions, so it should be select dbo.Function1(...
乍一看，我能看到的第一件事是您没有指定对象所有者/架构；这是函数所必需的，所以应该是select dbo.Function1(...
Second: look at what your call to string.Formatgenerates; that is generating @1and @nfor nanother integer, but that is nota valid parameter name. Which is handy, because
第二：看看你的调用string.Format产生了什么；正在生成@1，并@n为n另一整数，但是这不是一个有效的参数名称。这很方便，因为
Third: you didn't add any parameters
第三：你没有添加任何参数
Fourth: for a table UDF (rather than a scalar UDF), you must select * from dbo.Function1(..., not just select dbo.Function1(...
第四：对于表 UDF（而不是标量 UDF），您必须select * from dbo.Function1(...，而不仅仅是select dbo.Function1(...
回答by Munawar
You need fully qualified name of function with owner/schema name A working sample available at following link:
您需要具有所有者/架构名称的函数的完全限定名称 以下链接提供的工作示例：
