How to handle database null values

Within the scope of ADO.NET, a powerful framework for data access, the classes are carefully organized into two distinct components known as the Data Providers and the DataSet. The DataSet, undoubtedly a vital element, assumes the role of a memory-based representation of data, functioning independently from the primary data source. It serves as a comprehensive and self-contained structure, offering an array of capabilities for storing and manipulating information with utmost efficiency and reliability.

In many situations while reading data from Dataset, we have seen the error message like this,

Conversion from type 'DBNull' to type '<DataType>' is not valid

DBNull class

A significant player within this framework is the DBNull class, which plays a crucial role in handling the representation of non-existent values. It is worth noting that the DBNull type has been carefully designed as a singleton class, ensuring the presence of only a singular DBNull object across the entire system. This approach guarantees consistency and coherence in dealing with non-existent values in ADO.NET.

if (ds.Tables[0].Rows[i].ItemArray[0] == System.DBNull.Value) { MessageBox.Show("DBNULL exist in the field "); } else { MessageBox.Show(ds.Tables[0].Rows[i][0].ToString()); }

The above source code checking whether the dataset value is a DBNULL or not.

Full Source
using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection connection = null; SqlCommand command = null; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; string sql = null; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" sql = "your sql select statements here" connection = new SqlConnection(connetionString); connection.Open(); command = new SqlCommand(sql, connection); adapter.SelectCommand = command; adapter.Fill(ds); connection.Close(); try { if (ds.Tables[0].Rows[i].ItemArray[0] == System.DBNull.Value) { MessageBox.Show("DBNULL exist in the field "); } else { MessageBox.Show(ds.Tables[0].Rows[i][0].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }

Conclusion

The DBNull.Value member assumes a key position in this scheme, as it serves as a standardized reference point for all non-existent values encountered within the ADO.NET framework. By employing DBNull.Value, developers can rely on a universal and standardized representation when dealing with missing or null values, enhancing clarity and facilitating seamless communication within the system.






Related Topics
  1. C# ADO.NET Architecture
  2. Advantages of ADO.Net over ADO
  3. Connected and Disconnected Data Access Architecture in Ado.Net
  4. C# ADO.NET Connection String
  1. C# ADO.NET Data Providers

Related Links
  1. How to handle single quotes in sql