ado.net中where子句中的空值参数 [英] Null value parameters in where clause in ado.net
问题描述
我必须如何在Ado.net中的带参数的where子句中设置带有可能为空值的Sql命令。
How do I have to set up an Sql command with possible null values in the where clause with parameters in Ado.net.
Sql语句:
Select * from ViewSessionTarget where AgentId = @Parameter
Ado.net代码
using (SqlConnection connection = new SqlConnection(@"my connection string"))
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
var parameter = command.Parameters.AddWithValue("@Parameter", DBNull.Value);
parameter.DbType = DbType.Int64;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Debug.Write(reader["SessionId"]);
}
}
结果集将始终包含0个元素,因为在我的where子句我有一个空值。
因此,等于(=)将不起作用,我必须使用 is。
The Resultset will always have 0 elements, because in my where clause I have a null value. So the equal (=) will not work and I have to use "is".
但是当我将sql更改为此时:
But when I change my sql to this:
Select * from ViewSessionTarget where AgentId is @Parameter
我收到一个SqlException:'@Parameter'附近的语法不正确。
I receive an SqlException: "Incorrect syntax near '@Parameter'."
推荐答案
像这样写sql查询...
You can write you sql query something like this...
Select * from ViewSessionTarget where AgentId = @Parameter OR @Parameter IS NULL
或者您可以创建一个小程序,也可以为您提供更好的性能,例如....
Or you can create a little procedure which may also give you better performance, something like ....
CREATE PROCEDURE dbo.myProc
@Parameter INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = N'Select * from ViewSessionTarget where 1 = 1 '
+ CASE WHEN @Parameter IS NOT NULL THEN
N'AND AgentId = @Parameter ' ELSE N' ' END
EXECUTE sp_executesql @Sql
,N'@Parameter INT '
,@Parameter
END
这篇关于ado.net中where子句中的空值参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!