What is the correct way to treat the primary key exception?

I need to enter some data fetched from the Hattrick website (online football manager) into a database, using C#. The database structure is the following:

MatchID (which is also primary key)
Rating1
Rating2
...
Rating16

Because the way the data are retrieved, if I enter into the database a MatchID which is already present, I am 100% that the other fields, Ratingx will have the same value, so this exception can be safely ignored, so as not to bother the user.

To treat this exception, I used the following code:

string AddMatchCommand = "Insert into Games values (@Match, @Ratings1, @Ratings2, @Ratings3, @Ratings4, @Ratings5, @Ratings6, @Ratings7, @Ratings8, @Ratings9, @Ratings10, @Ratings11, @Ratings12, @Ratings13, @Ratings14, @Ratings15, @Ratings16)";
SqlConnection MyConn = new SqlConnection(CreateTableConnectionString);
SqlCommand command = new SqlCommand(AddMatchCommand, MyConn);
command.Parameters.AddWithValue("@Match", MatchIDToInsert.ToString(CultureInfo.InvariantCulture));
command.Parameters.AddWithValue("@Ratings1", RatingsToInsert[0].ToString(CultureInfo.InvariantCulture));
command.Parameters.AddWithValue("@Ratings2", RatingsToInsert[1].ToString(CultureInfo.InvariantCulture));
//...
command.Parameters.AddWithValue("@Ratings16", RatingsToInsert[15].ToString(CultureInfo.InvariantCulture));
MyConn.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException S)
{
if (S.Number != 2627) //2627 is the ID for the exception I want to ignore
{
MessageBox.Show(S.Message);
}
}
MyConn.Close();

The code above does its job. However, is there a better way to handle this situation? I am pretty sure that I may find a bug or two down the line, but I cannot prove this.

Hi,

You can try something like this.

try
{
// Some DB access
}
catch (Exception ex)
{
HandleException(ex);
}
public virtual void HandleException(Exception exception)
{
if (exception is DbUpdateConcurrencyException concurrencyEx)
{
// A custom exception of yours for concurrency issues
throw new ConcurrencyException();
}
else if (exception is DbUpdateException dbUpdateEx)
{
if (dbUpdateEx.InnerException != null
&& dbUpdateEx.InnerException.InnerException != null)
{
if (dbUpdateEx.InnerException.InnerException is SqlException sqlException)
{
switch (sqlException.Number)
{
case 2627: // Unique constraint error
case 547: // Constraint check violation
case 2601: // Duplicated key row error
// Constraint violation exception
// A custom exception of yours for concurrency issues
throw new ConcurrencyException();
default:
// A custom exception of yours for other DB issues
throw new DatabaseAccessException(
dbUpdateEx.Message, dbUpdateEx.InnerException);
}
}
throw new DatabaseAccessException(dbUpdateEx.Message, dbUpdateEx.InnerException);
}
}
// If we're here then no exception has been thrown
// So add another piece of code below for other exceptions not yet handled...
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Bhoir7y ago
Accepted answer
Hi,

You can try something like this.
try
{
  // Some DB access
}
catch (Exception ex)
{
  HandleException(ex);
}

public virtual void HandleException(Exception exception)
{
  if (exception is DbUpdateConcurrencyException concurrencyEx)
  {
    // A custom exception of yours for concurrency issues
    throw new ConcurrencyException();
  }
  else if (exception is DbUpdateException dbUpdateEx)
  {
    if (dbUpdateEx.InnerException != null
            && dbUpdateEx.InnerException.InnerException != null)
    {
      if (dbUpdateEx.InnerException.InnerException is SqlException sqlException)
      {
        switch (sqlException.Number)
        {
          case 2627:  // Unique constraint error
          case 547:   // Constraint check violation
          case 2601:  // Duplicated key row error
                      // Constraint violation exception
            // A custom exception of yours for concurrency issues
            throw new ConcurrencyException();
          default:
            // A custom exception of yours for other DB issues
            throw new DatabaseAccessException(
              dbUpdateEx.Message, dbUpdateEx.InnerException);
        }
      }

      throw new DatabaseAccessException(dbUpdateEx.Message, dbUpdateEx.InnerException);
    }
  }

  // If we're here then no exception has been thrown
  // So add another piece of code below for other exceptions not yet handled...
}
+1
Guest User7y ago
Thank you!
0