Command.Parameters ["@ name"].Value正确的语法 [英] Command.Parameters["@name"].Value Correct syntax

问题描述

我正在尝试使用查询来使用一个-三个参数从表中选择所有内容；

I am attempting to use a query to select everything from a table using one - three parameters;

SELECT * FROM PlantAreaCodes 
WHERE (@AreaCode IS NULL OR AreaCode = @AreaCode) 
AND (@AreaName IS NULL OR AreaName LIKE @AreaName) 
AND (@Comments IS NULL OR Comments LIKE @Comments);

这是我的查询的C#代码:

Here is the c# code for my query:

mySqlCommand = mySqlConnect.CreateCommand();

mySqlCommand.CommandText = "SELECT * FROM PlantAreaCodes WHERE (@AreaCode IS NULL OR AreaCode = @AreaCode) AND (@AreaName IS NULL OR AreaName LIKE @AreaName) AND (@Comments IS NULL OR Comments LIKE @Comments);";

mySqlCommand.Parameters.Add("@AreaCode", MySqlDbType.Int32);
mySqlCommand.Parameters.Add("@AreaName", MySqlDbType.VarChar);
mySqlCommand.Parameters.Add("@Comments", MySqlDbType.Text);

mySqlCommand.Parameters["@AreaCode"].Value = (string.IsNullOrEmpty(Convert.ToString(PModel.AreaCode)) ? (object)DBNull.Value : PModel.AreaCode);
mySqlCommand.Parameters["@AreaName"].Value = (string.IsNullOrEmpty(PModel.AreaName) ? (object)DBNull.Value : PModel.AreaName);
mySqlCommand.Parameters["@Comments"].Value = (string.IsNullOrEmpty(PModel.Comments) ? (object)DBNull.Value : PModel.Comments);

mySqlReader = mySqlCommand.ExecuteReader();

此查询允许用户搜索一个，两个，三个或没有任何字段而不会引发任何错误，但是它仅适用于可为null的类型.由于PModel.AreaCode是int，因此refref，搜索使用0而不是null.即.

This query allows for the user to search for one, two, three or no fields without throwing any errors, however it only works with nullable types. Therefpre since PModel.AreaCode is an int, the search uses 0 instead of null. ie.

SELECT * FROM PlantAreaCodes 
WHERE (NULL IS NULL OR AreaCode = NULL) 
AND (NULL IS NULL OR AreaName LIKE NULL) 
AND (NULL IS NULL OR Comments LIKE NULL);

由于AreaCode = 0是有效的查询，因此不会返回任何字段.

Which will return no fields because AreaCode = 0 is a valid query.

我尝试通过声明int? AreaCode = null使AreaCode为可空，但这不起作用.这将返回所有字段，而不仅仅是一个字段，就像我使用默认的null:

I have tried making AreaCode nullable by declaring int? AreaCode = null, but this doesn't work. This returns all fields, instead of just one, same as if I used the default null:

SELECT * FROM PlantAreaCodes 
WHERE (110 IS NULL OR AreaCode = 110) 
AND (NULL IS NULL OR AreaName LIKE NULL) 
AND (NULL IS NULL OR Comments LIKE NULL);

但是此查询返回一个字段:

However this query returns one field:

SELECT * FROM PlantAreaCodes 
WHERE (110 IS NULL OR AreaCode = 110) 
AND ('%General%' IS NULL OR AreaName LIKE '%General%') 
AND (NULL IS NULL OR Comments LIKE NULL);

这将返回名称中包含单词"General"的所有字段:

This returns all fields containing the word 'General' in the Name:

SELECT * FROM PlantAreaCodes 
WHERE (NULL IS NULL OR AreaCode = NULL) 
AND ('%General%' IS NULL OR AreaName LIKE '%General%') 
AND (NULL IS NULL OR Comments LIKE NULL);

如何在此查询中使用非空类型?

How can I use non-nullable types in this query?

推荐答案

好，这样我就可以了.主要问题是这需要将null与null进行比较，但是int不能为null，因为它是值类型.

Ok so I got this working. The main issue was that this needs to compare null with null, however an int can't be null as it is a value type.

所以我不得不对我的代码进行一些调整，并通过变通办法解决了这个问题

So I had to do some tweaking with my code and I got this working with the workaround

int? AreaCode = null;

这使我可以更改代码:

mySqlCommand.Parameters["@AreaCode"].Value = (PModel.AreaCode.HasValue ? PModel.AreaCode.Value : object)DBNull.Value );

对于所有不知道这里发生了什么的人:

For all those who aren't aware of what's happening here:

运算符?为值类型赋予没有值的能力，因此它可以= null.

The operator ? gives a Value Type the ability to have no value, so it can = null.

即.

int n = 0;
int? n = null;

bool b = false;
bool? b = null;

这篇关于Command.Parameters ["@ name"].Value正确的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！