How can i show an error message, in a message box in C#, of SQL exception(unique identifier) in 3-tier application?

Question

I am designing a windows form 3-tier application using C# and SQL. I have designed user registration page which is connected with the database using stored procedures. I have put textboxes to take input from user. I have used unique constraint for my user name column. Since I am using 3-tier application architecture so i have placed my register_user function in BLL and calling it from button in UI. When i typed repeated user name in text box it throws an exception(as expected) of unique constraint, but what i wish is that this message should be shown in a windows box and my application should not stop working. I have tried to use try catch in register_user function but it was of no use. Similarly I have also tried to use try catch in my UI(register button) but again failed. I am posting my code here: Thanks

//BLL

    public void register_user(string First_Name,string Last_Name,string User_Name,string Password,string Date_Of_Birth,string Security_Question,string Security_Answer)
    {
        try
        {
            DAL obj = new DAL();
            obj.OpenConnection();
            obj.LoadSpParameters("UR", First_Name, Last_Name, User_Name, Password, Date_Of_Birth, Security_Question, Security_Answer);
            obj.ExecuteQuery();
            obj.UnLoadSpParameters();
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            DAL obj2 = new DAL();
            obj2.CloseConnection();
        }   
       }

// UI

    private void register_button_Click(object sender, EventArgs e)
    {

        if (first_name_text.Text == "" || last_name_text.Text == "" || user_name_text.Text == "" || password_text.Text == "" || confirm_password_text.Text == "" || answer_txt.Text == "")
        {

            alert1_label.Show();
            error1_label.Show();
            error1_label.Text = "You cannot left mandatory fields empty";
        }

        if (password_text.Text.Length <= 8)
        {

            alert1_label.Show();
            error1_label.Show();
            error1_label.Text = "Password must be greater than 8 characters";
            password_text.Clear();
            confirm_password_text.Clear();
            return;


        }
        if (first_name_text.Text.Any(Char.IsDigit) || first_name_text.Text.Any(Char.IsPunctuation) || first_name_text.Text.Any(Char.IsSeparator) || first_name_text.Text.Any(Char.IsSymbol))
        {
            alert1_label.Show();
            error1_label.Show();
            error1_label.Text = "Numbers and Special Characters are not allowed in first name";
            first_name_text.Clear();
            return;
        }
        if (last_name_text.Text.Any(Char.IsDigit) || last_name_text.Text.Any(Char.IsPunctuation) || last_name_text.Text.Any(Char.IsSeparator) || last_name_text.Text.Any(Char.IsSymbol))
        {
            alert1_label.Show();
            error1_label.Show();
            error1_label.Text = "Numbers and Special Characters are not allowed in last name";
            last_name_text.Clear();
            return;
        }
        if (!user_name_text.Text.Any(Char.IsLetter))
        {
            alert1_label.Show();
            error1_label.Show();
            error1_label.Text = "User Name must contain atleast one alphabet, and must be atleast 4 characters long.";
            user_name_text.Clear();
            return;
        }
        if (user_name_text.Text.Length <= 3)
        {
            alert1_label.Show();
            error1_label.Show();
            error1_label.Text = "User Name must contain atleast one alphabet, and must be atleast 4 characters long.";
            user_name_text.Clear();
            return;
        }

        else
        {
           try
           {
            error1_label.Hide();
            alert1_label.Hide();
            BLL obj = new BLL();
            obj.register_user(first_name_text.Text, last_name_text.Text, user_name_text.Text, password_text.Text, date_of_birth_text.Text, security_question_text.SelectedItem.ToString(), answer_txt.Text);

                MessageBox.Show("Registration Successful");
                UP frm = new UP();
                frm.Text = "Welcome" + " " + first_name_text.Text;
                this.Dispose();
                frm.Show();
            }
            catch(SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

it failed with an exception error but i want this error to be shown in message box. — Anus Kaleem, May 19 '14 at 12:13

score 3 · Accepted Answer · answered May 19 '14 at 13:29

One of the problems in your BLL is the Closing of a connection that is not open. In your register_user method you have a finally block that creates a new DAL() object and then you call CloseConnection.

Without seeing that implementation I suspect you wanted to close the DAL object you created in the try block. If that is the case one possible fix is to lift the obj variable outside the try block so it becomes available in the try and the finally block.

Remember that variables have blockscope in C#.

public void register_user(string First_Name,string Last_Name,string User_Name,string Password,string Date_Of_Birth,string Security_Question,string Security_Answer)
{ 
    DAL obj = null; // initialize to a value
    try
    {
        obj = new DAL();
        obj.OpenConnection();
        obj.LoadSpParameters("UR", First_Name, Last_Name, User_Name, Password, Date_Of_Birth, Security_Question, Security_Answer);
        obj.ExecuteQuery();
        obj.UnLoadSpParameters();
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        if (obj!=null)  // check for null in case the constructor did throw an exception
        {
          obj = new DAL();
          obj.CloseConnection();
        } 
        else
        {
            Debug.Assert(obj!=null, "DAL not intialized");
        }
    }   
   }

Beyond the scope of this answer you have to revisit why and when you catch exceptions. As a general rule you only catch an exception if you know how to handle that. In your current code examples both the BLL and UI layer handle exceptions. If done properly a BLl layer should not have the responsibility to show errors to the user. It should hand-off errors to the caller, in this case the UI layer and that caller can decide what to do.

If you take that advice in the above sample the MessageBox.Show should be removed and maybe replaced by logging and/or an Error collection.

score 0 · Answer 2 · edited May 28 '14 at 07:19

0

It is good practice to catch specific exceptions like you do, "catch (SqlException ex)", but for sake of debugging, try to replace "catch (SqlException ex)" with something like the statement below.

catch (Exception exception)
{
    System.Diagnostics.Debug.WriteLine(exception.Message);
    throw;
}

Do this on both client and business layer, then place a breakpoint, debug and see what happens.

My guess is that something happens in the first catch statement that generates another type of exception, that is not catched on the client.

edited May 28 '14 at 07:19

Incredible

3,495
8
49
77

answered May 19 '14 at 13:25

Poxet

111
1
5

For sake of debugging it would be even easier if no exceptions would be caught. Another possibilty is to break on a first chance wexception: http://stackoverflow.com/questions/8218249/how-do-i-enable-visual-studio-2010-to-break-when-a-first-chance-exception-happen – rene May 19 '14 at 13:38

score 0 · Answer 3 · answered May 19 '14 at 13:31

0

Change your catch:

catch (SqlException ex)
{
   MessageBox.Show(ex.ToString());
}

to:

catch (Exception ex)
{
   MessageBox.Show(ex.ToString());
}

This way it catches any errors within the try-block, not just "SqlException"-type of errors. Your current error could be something else than "SqlException".

answered May 19 '14 at 13:31

W0lfw00ds

2,018
14
23

If it is for sure, why the error isn't shown in the MessageBox? You should try it, at least – W0lfw00ds May 19 '14 at 14:22
actually i have worked on your suggestion already but i failed. – Anus Kaleem May 19 '14 at 15:44
What i have observed is that when i traced it using break point it first throw an exception then when i force it to continue it shows me that exception in message box. Now i have simply un checked the thrown box in exceptions window from debug menu. With this approach i am directly seeing an exception in message box. – Anus Kaleem May 19 '14 at 16:19

score 0 · Answer 4 · answered May 19 '14 at 13:33

Your error is probably occurring because the UI is not accessible from that point in code.

It violates design principles of n-tier to have your data layer generate UI components. In n-tier you want to decide early in the design at what layer exceptions are handled and how messages are transferred because retrofitting can be difficult.

Common schemes:

All exceptions are bubbled up to the UI layer, where they are handled appropriately. The only catches in data or business layer would be for errors that can be handled correctly within the tier, or to catch, log, and rethrow.
Have custom return types that allow messages to be returned from business layers. This can be a success boolean return from all BL methods and some sort of global error repository ("One or more errors have occurred, here is the error log" type messaging) or something like a result type or complex result object with exception included and possibly other info about transaction state, etc.

Same issue was confusing me alot, i was using 2nd scheme as my very first option but i was confused in what to return at failure. but now it is resolved :) — Anus Kaleem, May 19 '14 at 13:55

How can i show an error message, in a message box in C#, of SQL exception(unique identifier) in 3-tier application?

4 Answers4