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);
}
}
}
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.