How to find if a value is NULL in SQL Server using c#

Question

I would like to know which values are null in datatable in c# that is returned from ExecuteDataTable of SqlHelper class.

string select = "select * from testTable";
string val="";

DataTable  dt=dbcon.ExecuteDataTable(select);
foreach (DataRow dr in dt.Rows)
{
   foreach (DataColumn  dc in dt.Columns )
   {
       if(dr[dc].Equals (null))
       {
          val ="null";
       } 
       else  
       {
          val = dr[dc].ToString();
       }
   }
}

But unfortunately I didn't find any way to do it. Please let me know if there is a way. Thank you in advance.

Answer 1

You need DBNull.Value:

if (dr[dc] == DBNull.Value)

Answer 2

As well as David M's method, you can also use DataRow.IsNull:

if (dr.IsNull(dc))

Answer 3

if (dr[dc] == DBNull.Value) worked ! Here is why I needed it .It generates the insert script for given table name.

String tableName= "mytable";

           string select = "select * from "+tableName ;
           DataTable  dt=dbcon.ExecuteDataTable(select );
           StringBuilder sb = new StringBuilder();
           string pk="";

           sb.AppendFormat ( "select Name from sys.columns where Object_ID = Object_ID('{0}') and is_identity=1",tableName );
           try
           {
               pk = dbcon.ExecuteScalar(sb.ToString()).ToString();
           }
           catch
           { }
           sb.Remove(0, sb.Length);
            foreach (DataRow dr in dt.Rows  )
            {
                sb.Append("Insert INTO " + tableName + " VALUES( ");
                foreach (DataColumn  dc in dt.Columns )
                {
                    if (dc.ColumnName != pk)
                    {
                        string val = dr[dc].ToString();

                        if (dr[dc] == DBNull.Value)
                        {
                            sb.AppendFormat("{0} , ", "null");
                        }
                        else
                        {
                            sb.AppendFormat("'{0}' , ", dr[dc].ToString());
                        }
                    }
                }
                sb.Remove(sb.Length - 2, 2);
                sb.AppendLine(")");
            }