C＃SQL客户端中的序数值 [英] Ordinal values in C# SQL client

问题描述

我有一个函数

 GetStudents 

，它接受两个参数。我想使用序数值来进行选择但我收到错误。如果我设法做到这一点，那么我将尝试添加where where子句，条件变化，这就是我需要使用序数值的原因。

 using（SqlCommand cd = db.CreateCommand（））
 {
 cd.CommandType = CommandType.Text; 
 cd.CommandText =SELECT ClassName FROM GetStudents（？，？）; 
 cd.Parameters.AddWithValue（null，age）; 
 cd.Parameters.AddWithValue（null，monthBday）; 
} 

我的尝试：

< pre> using（SqlCommand cd = db.CreateCommand（））
 {
 cd.CommandType = CommandType.Text; 
 cd.CommandText =SELECT ClassName FROM GetStudents（？，？）; 
 cd.Parameters.AddWithValue（null，age）; 
 cd.Parameters.AddWithValue（null，monthBday）; 
} 

解决方案

SQL使用命名参数。对于函数调用，参数名称不需要与函数中声明的名称匹配：

 cd.CommandText =   SELECT ClassName FROM GetStudents（@ p0，@ p1）; 
 cd.Parameters.AddWithValue（  @ p0，年龄）; 
 cd.Parameters.AddWithValue（  @ p1，monthBday）; 

我认为你的做法完全错了。您可以使用一些SQL日期数学代码计算它们的年龄。除此之外，您的GetStudents功能甚至不应该存在。无论函数中的任何查询都可以放入调用该函数的查询中。在查询中调用函数在性能方面非常昂贵，并且应尽可能避免。因此，假设您想要特定年龄的所有学生的班级名称：

  SELECT  [ClassName] 
  FROM 学生
  WHERE   @ age  = DATEDIFF（年， CONVERT （ DATE ，GETDATE （））， CONVERT （ DATE ，[DateOfBirth]））

I have a function

GetStudents

, that accepts two parameters. I want to use ordinal values to make the select but I am getting an error. If I manage to do this then I will try to add a where in clause that conditions vary that is why I need to use ordinal values.

using (SqlCommand cd = db.CreateCommand())
                    {
                        cd.CommandType = CommandType.Text;
                        cd.CommandText = "SELECT ClassName FROM GetStudents(?,?)";
                        cd.Parameters.AddWithValue(null, age);
                        cd.Parameters.AddWithValue(null, monthBday);
}

What I have tried:

<pre>using (SqlCommand cd = db.CreateCommand())
                    {
                        cd.CommandType = CommandType.Text;
                        cd.CommandText = "SELECT ClassName FROM GetStudents(?,?)";
                        cd.Parameters.AddWithValue(null, age);
                        cd.Parameters.AddWithValue(null, monthBday);
}

解决方案

SQL uses named parameters. For a function call, the parameter names don't need to match the names declared in the function:

cd.CommandText = "SELECT ClassName FROM GetStudents(@p0,@p1)";
cd.Parameters.AddWithValue("@p0", age);
cd.Parameters.AddWithValue("@p1", monthBday);

I think your approach is all wrong. You can calculate their age with some SQL date math code. Beyond that, your GetStudents function should not even exist. Whatever query is in the function can be put into the query that's calling the function. Calling a function in a query is very expensive in terms of performance, and should be avoided if possible. So, assuming you want the classname for all students of a certain age:

SELECT [ClassName] 
FROM Students 
WHERE @age = DATEDIFF(YEAR, CONVERT(DATE,GETDATE()), CONVERT(DATE,[DateOfBirth]))

这篇关于C＃SQL客户端中的序数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！