ASP.NET DBNull Value
DBNull is a special class in .NET that represents an uninitialized variant or a nonexistent database column. It is important to note that DBNull is distinct from the number zero or an empty string value. DBNull is designed to specifically indicate the absence of a value.
DBNull
In .NET, DBNull is implemented as a singleton class, meaning that only one instance of this class can exist throughout the runtime of an application. The DBNull.Value member serves as the sole instance of the DBNull object. It can be used to represent a null value when working with databases or handling data that may contain missing or undefined values.
With DBNull.Value, developers are now able to discriminate the system between actual values and no values in their code. It's a very useful tool in this respect since it introduces one consistent and standardized approach to the null value handling especially when interacting with databases and data sources that may have null values represented in some columns or fields.
In many situations while reading data from DataSource, we have seen the error message like the following :
Conversion from type 'DBNull' to type '' is not valid
This message is getting because the ASP.NET program unable to handle DBNull value. In these cases you can determine whether a value retrieved from a database field is a DBNull value by passing the value of that field to the DBNull.Value.Equals method. Here is an example of how DBNull.Value can be used:
VB.Net
If IsDBNull(ds.Tables(0).Rows(i).Item(0)) Then
Label1.Text = "DBNULL exist in the field "
End If
C#
if (ds.Tables[0].Rows[0].ItemArray[0] == System.DBNull.Value)
{
Label1.Text = "DBNULL exist in the field ";
}
In the above code snippet, the retrieved value is compared with DBNull.Value to determine if it is null or has an actual value. If the value is equal to DBNull.Value, it indicates that the database column contained a null value. Otherwise, the value is printed to the Label1.
The following ASP.NET program is checking wether the retrieved values is DBNull.
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Full Source | C#
using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
DataSet ds = new DataSet();
string sql = "select count(*) from tablename";
try
{
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(ds);
connection.Close();
if (ds.Tables[0].Rows[0].ItemArray[0] == System.DBNull.Value)
{
Label1.Text = "DBNULL exist in the field ";
}
else
{
Label1.Text = ds.Tables[0].Rows[0][0].ToString();
}
}
catch (Exception ex)
{
Label1.Text = "Error in execution " + ex.ToString();
}
}
}
Full Source | VB.NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString As String
Dim connection As SqlConnection
Dim ds As New DataSet
Dim i As Integer
connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
Dim sql As String = "select count(*) from publishers"
Try
connection.Open()
Dim adapter As New SqlDataAdapter(sql, connection)
adapter.Fill(ds)
connection.Close()
If IsDBNull(ds.Tables(0).Rows(i).Item(0)) Then
Label1.Text = "DBNULL exist in the field "
Else
Label1.Text = CInt(ds.Tables(0).Rows(i).Item(0))
End If
Catch ex As Exception
Label1.Text = "Error in execution " & ex.ToString
End Try
End Sub
End Class
Conclusion
By understanding the purpose and usage of DBNull, developers can effectively handle null values and uninitialized variants in their applications, ensuring proper data integrity and accurate processing of database-related operations.