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.

David M · Accepted Answer · 2010-01-11 11:25:08Z

15

You need DBNull.Value:

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

answered Jan 11, 2010 at 11:25

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Hamilton · Accepted Answer · 2010-01-11 11:28:54Z

6

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

if (dr.IsNull(dc))

answered Jan 11, 2010 at 11:28

Matt Hamilton

205k61 gold badges395 silver badges321 bronze badges

1 Comment

Codesleuth Over a year ago

I would use this as it has slightly less IL, and slightly more efficient (a good idea when working with data).

Thunder · Accepted Answer · 2010-01-11 11:42:35Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related