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

Question

I would like to know which values ​​are null in a datatable in C #, which is returned from the ExecuteDataTable class of the 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 did not find a way to do this. Please let me know if there is a way. Thank you in advance.

Answer 1

Like the David M method, you can also use DataRow.IsNull :

if (dr.IsNull(dc))

Answer 2

You need to DBNull.Value:

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

Answer 3

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

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(")");
            }