Debugging ADO.NET Datatables

Do you ever had the famous error message :

“Failed to enable constraints. One or more rows  contain values violating non-null, unique, or foreign-key  constraints.”

when using ADO.NET Datasets ?

Tthis is an incredibly useful message isn’t it ?

So what can you do to spare you some headaches ? Retrieve a more useful message sure !

You have to know that a Datatable object have a “HasError” property, this property allows you to know if the datatable has one ore more rows in error.

When HasError is true, you can retrieve the rows havin an error with the DataTable’s “GetErrors()” method wich return an array with all datarow having an error.

The “in errror” rows can inform you about errors with the “RowError” property.

Here is a simple helper class which can be used to debug a single DataTable or an entire DataSet:

using System;
using System.Data;

public class DataSetErrorRevealer
{

   /// <summary>
   /// Get errors on rows of a DataTable
   /// </summary>
   /// <param name="errorDataTable">The DataTable to be checked</param>
   public static void RevealTableErrors(DataTable errorDataTable)
   {
      if (errorDataTable == null)
      {
         Console.WriteLine("The provided DataTable object is null");
         return;
      }

      if (errorDataTable.HasErrors)
      {
         DataRow[] rowsInError = errorDataTable.GetErrors();
         if (rowsInError.Length <= 0)
         {
            Console.WriteLine("There is no problem with table {0}", errorDataTable.TableName);
         }
         else
         {
            foreach (DataRow errorRow in rowsInError)
            {
               Console.WriteLine(errorRow.RowError);
            }
         }
      }
   }

   /// <summary>
   /// Get errors on row for all tables of a DataSet
   /// </summary>
   /// <param name="errorDataSet">The Dataset to be checked</param>
   public static void RevealDataSetErrors(DataSet errorDataSet)
   {
         if (errorDataSet == null)
         {
            Console.WriteLine("The provided DataSet is null");
            return;
         }

         foreach (DataTable errorTable in errorDataSet.Tables)
         {
            RevealTableErrors(errorTable);
         }
      }
   }

Use it this way :


   TestDataSet dataset = new TestDataSet();
   try
   {
      using (SqlConnection sqlCon = new SqlConnection("MyConnectionString"))
      {
         SqlCommand sqlComm = sqlCon.CreateCommand();
         sqlComm.CommandType = CommandType.StoredProcedure;
         sqlComm.CommandText = "Select Stored procedure";
         SqlDataAdapter sqlDa = new SqlDataAdapter(sqlComm);
         sqlDa.Fill(dataset);
      }
   }
   catch (Exception ex)
   {
   // Will provide the error message :
   // "Failed to enable constraints. One or more rows
   // contain values violating non-null, unique,
   // or foreign-key  constraints."
   Console.WriteLine("Error : {0}", ex);

   // will provide a more helpful message
   DataSetErrorRevealer.RevealDataSetErrors(dataset);
   }

If you prefer, you can use an extension method :


using System.Data;
using System;

public static class DataSetExtensions
{
   public static void RevealErrors(this DataSet errorDataSet)
   {
      if (errorDataSet == null)
      {
         Console.WriteLine("The provided DataSet is null");
         return;
      }

      foreach (DataTable errorTable in errorDataSet.Tables)
      {
         errorTable.RevealErrors();
      }
   }

   public static void RevealErrors(this DataTable errorDataTable)
   {
      if (errorDataTable == null)
      {
         Console.WriteLine("The provided DataTable object is null");
         return;
      }

      if (errorDataTable.HasErrors)
      {
         DataRow[] rowsInError = errorDataTable.GetErrors();
         if (rowsInError.Length <= 0)
         {
            Console.WriteLine("There is no problem with table {0}", errorDataTable.TableName);
         }
         else
         {
            foreach (DataRow errorRow in rowsInError)
            {
               Console.WriteLine(errorRow.RowError);
            }
         }
      }
   }
}

use :


catch (Exception ex)
{
   // will provide an helpful message
   dataset.RevealErrors();
}

Hope this helps !

using System;
using System.Data;public class DataSetErrorRevealer
{

/// <summary>
/// Get errors on rows of a DataTable
/// </summary>
/// <param name=”errorDataTable”>The DataTable to be checked</param>
public static void RevealTableErrors(DataTable errorDataTable)
{
if (errorDataTable == null)
{
Console.WriteLine(“The provided DataTable object is null”);
return;
}

if (errorDataTable.HasErrors)
{
DataRow[] rowsInError = errorDataTable.GetErrors();
if (rowsInError.Length <= 0)
{
Console.WriteLine(“There is no problem with table {0}”, errorDataTable.TableName);
}
else
{
foreach (DataRow errorRow in rowsInError)
{
Console.WriteLine(errorRow.RowError);
}
}
}
}

/// <summary>
/// Get errors on row for all tables of a DataSet
/// </summary>
/// <param name=”errorDataSet”>The Dataset to be checked</param>
public static void RevealDataSetErrors(DataSet errorDataSet)
{
if (errorDataSet == null)
{
Console.WriteLine(“The provided DataSet is null”);
return;
}

foreach (DataTable errorTable in errorDataSet.Tables)
{
RevealTableErrors(errorTable);
}
}
}

Incoming search terms:

  • bridgeghq
  • datatable geterrors
  • datatable haserrors
  • debugging You may use these HTML tags and attributes -captcha

About Sam Beauvois

Application Developer, .NET enthusiast since 2004, I'm interested in technology watch, usability, code quality, patterns & practices, UX, ...

2 comments

  1. Laurent says:

    Very useful for me, thanks.
    Laurent

  2. [...] This post was mentioned on Twitter by Samuel Beauvois. Samuel Beauvois said: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints http://bit.ly/8XgBRt [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>