CODEWITHSUNDEEP
c#sql-serversql-server-2008
Pramod Niralakeri
Pramod Niralakeri

Reputation: 53

C# and SQL Server 2008 : how to handle null from database

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.

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();

Upvotes: 0

Views: 113

Answers (2)

Mukund Thakkar
Mukund Thakkar

Reputation: 1305

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

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

Upvotes: -1

Justin
Justin

Reputation: 86789

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:

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:

  • If q query returns a single value you can use ExecuteScalar instead of Read etc...
  • 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.

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

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

Upvotes: 2

Related Questions