Handle Null column values in ASP.Net SqlDataReader using C# and VB.Net

Hii Kiith,

Specify Column name in SqlDataReader instead of GetString method for reading the column values.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" />
        <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" />
        <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" />
        <asp:BoundField DataField="Address" HeaderText="Address" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if(!this.IsPostBack)
    {
        this.BindGridView();
    }
}

private void BindGridView()
{
    gvCustomers.DataSource = OnGet();
    gvCustomers.DataBind();
}

public List<InsurerInfo> OnGet()
{
    List<InsurerInfo> listInsurers = new List<InsurerInfo>();
    try
    {
        string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT TOP 15 CustomerId, CompanyName, ContactTitle, Address FROM Customers ORDER BY CustomerId DESC", con))
            {
                con.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        InsurerInfo insurerInfo = new InsurerInfo();
                        insurerInfo.CustomerID = reader["CustomerId"].ToString();
                        insurerInfo.CompanyName = reader["CompanyName"].ToString();
                        insurerInfo.ContactTitle = reader["ContactTitle"].ToString();
                        insurerInfo.Address = reader["Address"].ToString();
                        listInsurers.Add(insurerInfo);
                    }
                }
                con.Close();
            }
        }
    }
    catch (Exception ex)
    {

    }

    return listInsurers;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGridView()
    End If
End Sub

Private Sub BindGridView()
    gvCustomers.DataSource = OnGet()
    gvCustomers.DataBind()
End Sub

Public Function OnGet() As List(Of InsurerInfo)
    Dim listInsurers As List(Of InsurerInfo) = New List(Of InsurerInfo)()

    Try
        Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
        Using con As SqlConnection = New SqlConnection(constring)
            Using cmd As SqlCommand = New SqlCommand("SELECT TOP 15 CustomerId, CompanyName, ContactTitle, Address FROM Customers ORDER BY CustomerId DESC", con)
                con.Open()
                Using reader As SqlDataReader = cmd.ExecuteReader()
                    While reader.Read()
                        Dim insurerInfo As InsurerInfo = New InsurerInfo()
                        insurerInfo.CustomerID = reader("CustomerId").ToString()
                        insurerInfo.CompanyName = reader("CompanyName").ToString()
                        insurerInfo.ContactTitle = reader("ContactTitle").ToString()
                        insurerInfo.Address = reader("Address").ToString()
                        listInsurers.Add(insurerInfo)
                    End While
                End Using

                con.Close()
            End Using
        End Using

    Catch ex As Exception
    End Try

    Return listInsurers
End Function

Class InsurerInfo

C#

public class InsurerInfo
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
}

VB.Net

Public Class InsurerInfo
    Public Property CustomerID As String
    Public Property CompanyName As String
    Public Property ContactTitle As String
    Public Property Address As String
End

Screenshot