C# .NET: nested try catch block not handling exception properly

Question

To demonstrate Exception Handling, I have intentionally put the wrong table name and null instance to SqlDataReader dr. The Inner try block catches the wrong table reference exception SqlClient.SqlException but the outer try block fails to catch the NullReferenceException on dr.hasRows.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Exp_Handling
{
    internal class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn;
            SqlCommand cmd;
            //SqlDataReader dr;
            try
            {
                conn = new SqlConnection(@"data source=ITEM-S132712\MSSQLSERVER2019;initial catalog=Hotel;integrated security=true");
                cmd = new SqlCommand("Select * from Room_Entry1", conn);

                conn.Open();
                SqlDataReader dr = null;
                try
                {
                    dr = cmd.ExecuteReader();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("****" + ex.Message);
                }
                if (dr.HasRows)
                {
                    Console.WriteLine("DATA FOUND");
                    while (dr.HasRows)
                    {
                        Console.WriteLine(dr.GetString(0));
                    }
                }
            }
            catch (System.NullReferenceException nullExp)
            {

                Console.WriteLine("##try1###" + nullExp.Message);
                Console.WriteLine("Instance is Null for DataReader");
            }
            catch (Exception out_Ex)
            {
                Console.WriteLine("##try2###" + out_Ex.Message);
            }

            Console.ReadKey();
            }
        }
    }

Demonstration from Reference https://www.mssqltips.com/sqlservertip/6055/net-exception-handling-for-database-calls-to-sql-server-with-try-catch-and-finally/

Charlieface · Answer

There are so many issues with this code it's hard to know where to start:

Catching NullReference is ridiculous: you won't get one, unless the ExecuteReader failed and you caught the SqlException, in which case you should just move the catch (SqlException to the outside and then you can remove the catch (NullReferenceException.
conn cmd and dr need using.
while (dr.HasRows) makes no sense, it should probably be while (dr.Read())
Don't use SELECT *, only select the columns you need.
Don't use GetString(0), instead use the column name.
Don't hardcode the connection string, instead store it in a settings file

static void Main(string[] args)
{
    try
    {
        using (var conn = new SqlConnection(YourConnString))
        using (var cmd = new SqlCommand("Select SomeColumn from Room_Entry1", conn))
        {
            conn.Open();
            using (var dr = cmd.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    Console.WriteLine("DATA FOUND");
                    while (dr.HasRows)
                    {
                        Console.WriteLine(dr["SomeColumn"]);
                    }
                }
            }
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine("****" + ex.Message);
    }
    catch (Exception out_Ex)
    {
        Console.WriteLine("##try2###" + out_Ex.Message);
    }

    Console.ReadKey();
}

C# .NET: nested try catch block not handling exception properly

1 Answers