C＃和SQL Server 2008：如何从数据库处理null

I am selecting max(id) from database which are grouped by date. Sometimes there might be situation where date does not have any id. That time it rising error so how to handle it n assign to int variable.

我从数据库中选择max(id),按日期分组。有时可能会出现日期没有任何ID的情况。那个时候它上升错误所以如何处理它n赋值给int变量。

SqlCommand maxid = new SqlCommand("select max(block) from blocks_allocation where date='" + DR.ItemArray.GetValue(7).ToString() + "'", con);

SqlDataReader maxrd = maxid.ExecuteReader();

if (maxrd.Read())
   block_no = int.Parse(maxrd["block"].ToString()) + 1;

maxrd.Close();

2 个解决方案

#1

SqlCommand returns DBNull.Value if the value is null, so if you have a query that could return null values you need to test against DBNull.Value first, like this:

如果值为null,则SqlCommand返回DBNull.Value,因此如果您有一个可以返回空值的查询,则需要首先测试DBNull.Value,如下所示:

var date = DR.ItemArray.GetValue(7).ToString();
const string sql = "SELECT MAX(block) FROM blocks_allocation WHERE date = @date";
using (var cmd = new SqlCommand(sql, con))
{
    cmd.Parameters.AddWithValue("@date", date);
    var maxBlock = cmd.ExecuteScalar();
    block_no = maxBlock == DBNull.Value ? null : (int?) maxBlock;
}

(This assumes that block_no is a nullable int). I've also changed a few other things:

(这假设block_no是可以为空的int)。我还改变了一些其他的东西:

If q query returns a single value you can use ExecuteScalar instead of Read etc...

如果q查询返回单个值,则可以使用ExecuteScalar而不是Read等...

You should use using blocks instead of manually closing / disposing of objects.

您应该使用块而不是手动关闭/处置对象。

You shouldn't build dynamic SQL as it can lead to SQL Injection - I've modified the query to use a parametrized query instead.

您不应该构建动态SQL,因为它可以导致SQL注入 - 我已经修改了查询以使用参数化查询。

I've used the inline-if syntax to set block_no, but you can also use a standard if should you prefer:

我使用了inline-if语法来设置block_no,但如果您愿意,也可以使用标准:

if (maxBlock == DBNull.Value)
{
    block_no = null;
}
else
{
    block_no = (int?) maxBlock;
}

#2

-1

Check whether the max(id) is null, if it so then return 1 using ISNULL()

检查max(id)是否为null,如果是,则使用ISNULL()返回1

select isnull(max(block),1) from blocks_allocation

#1