0

I have added unique constraint to datatable like this

DataTable dtemp
private void TempTable() 
{
 dtemp = new DataTable("Temp");
 dtemp.Columns.Add(new DataColumn("Table", typeof(int)));
 dtemp.Columns.Add(new DataColumn("Capacity", typeof(int)));
 UniqueConstraint TableUnique = new UniqueConstraint(new DataColumn[] { dtemp.Columns["Table"] });
 dtemp.Constraints.Add(TableUnique);
}

When I try to add same record, instead of going to catch block, it simply exit the program abruptly and gives unique constraint unhandled exception in the visual studio.

private void GetTable()
    {
        try
        { 
            dtemp.Rows.Add(mTable.TableID,mTable.Capacity);
        }
        catch(SqlException ee)
        {
            if (ee.Number == 2627)
            {
                MessageBox.Show("Sorry, This Table Is Already Reserved!");
                if (Con.State == ConnectionState.Open) { Con.Close(); }
                return;
            }
            MessageBox.Show("Exception: " + ee.Message);
        }

Can anyone please tell me how to handle this exception?

Richard Schneider
  • 34,944
  • 9
  • 57
  • 73
Rohith Nayak
  • 55
  • 1
  • 1
  • 8
  • I would avoid using exceptions in this scenario. This is really a bad pattern. It is especially painful to debug when you have all exceptions turned on. A better practice is to check if the record exists, then add if it doesn't. – tsells Feb 05 '15 at 05:56
  • Another question - why are you using a data table for this? It looks like a Dictionary<> would suit your needs and would be much faster. Dictionary(). – tsells Feb 05 '15 at 05:58

2 Answers2

1

Since you have define your constraint in the local DataTable, you're trying to catch the wrong type of exception. Instead of SqlException, a ConstraintException is thrown.

An immediate fix would be, to change the type of exception in your try clause:

try
{ 
    dtemp.Rows.Add(mTable.TableID,mTable.Capacity); 
} 
catch (ConstraintException ee) 
{
    // handle the exception here 
}

If you want to check your constraint in the database, then that's where you want to define it instead of in the DataTable. In this case, SqlException will get thrown, but only when you send the data to the database, not when you add a row to the DataTable.

Damir Arh
  • 17,637
  • 2
  • 45
  • 83
  • I would avoid using exceptions in this scenario. This is really a bad pattern. It is especially painful to debug when you have all exceptions turned on. A better practice is to check if the record exists, then add if it doesn't. – tsells Feb 05 '15 at 06:01
  • tsells can you please share the code to check if the record exists or not before adding – Rohith Nayak Feb 05 '15 at 06:20
  • @RohithJNayak Check [this article](https://msdn.microsoft.com/en-us/library/y06xa2h1.aspx) for finding a row in `DataTable`. – Damir Arh Feb 05 '15 at 06:58
0

This is not a SQLException. Change your handling to be more specific around the exception. If you can't resolve that - change it to a regular Exception. 

private void GetTable()
    {
        try
        { 
            dtemp.Rows.Add(mTable.TableID,mTable.Capacity);
        }
        catch(Exception ee)
        {                
            MessageBox.Show("Exception: " + ee.Message);
        }

I would avoid using exceptions in this scenario. This is really a bad pattern. It is especially painful to debug when you have all exceptions turned on. A better practice is to check if the record exists, then add if it doesn't.

Think of exceptions as edge cases for unexpected events - not regular coding practices.

tsells
  • 2,751
  • 1
  • 18
  • 20