Sql Data Reader: Wish I knew this earlier!

 

Dealing with DataReader Null Values

Wish I'd known this years ago...  when parsing through a SQL DataReader, you can effectively use reader.IsDBNull(ordinal) to determine whether the value in the database is null.  What you do from there depends on your context.  The example below shows different ways to implement based on data type.

     private void CopyTopicMaterials(int fromTopicId, int toProjectId, int toTopicId, int modifiedBy)  
     {  
       SqlConnection dbConn = new SqlConnection(dbConnString);  
       string sql = "select * from tblTopicMaterials where TopicId = @TopicId";  
       SqlCommand cmd = new SqlCommand(sql, dbConn);  
       cmd.Parameters.AddWithValue("@TopicId", fromTopicId);  
       dbConn.Open();  
       SqlDataReader reader = cmd.ExecuteReader();  
       while (reader.Read())  
       {  
         TopicMaterial m = new TopicMaterial();  
         m.TopicId = toTopicId;  
         m.ProjectId = toProjectId;  
         m.Sequence = reader["Sequence"] as byte? ?? 0;  
         m.FindNumber = reader.IsDBNull(reader.GetOrdinal("FindNumber")) ? "" : reader["FindNumber"].ToString();  
         m.MaterialMasterId = reader["MaterialMasterId"] as int? ?? 0;  
         m.Quantity = reader.IsDBNull(reader.GetOrdinal("Quantity")) ? "" : reader["Quantity"].ToString();  
         m.MaterialUnitId = reader["MaterialUnitId"] as int? ?? 0;  
         m.Notes = reader.IsDBNull(reader.GetOrdinal("Notes")) ? "" : reader["Notes"].ToString();  
         m.ModifiedBy = modifiedBy;  
         m.DateModified = DateTime.UtcNow;  
         m.RecordStatus = reader.IsDBNull(reader.GetOrdinal("RecordStatus")) ? "" : reader["RecordStatus"].ToString();  
         CopyMaterial(m);  
       }  
       dbConn.Close();  
       reader.Close();  
     }