index > Windows Forms Data Controls and Databinding > Customize sql server exception

Customize sql server exception

hi. my database throws an exception when a user tries to delete rows that are referenced with foreign key constraint and it's fine. It throws something like this:

The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_FK00". The conflict occurred in database "restaurant", table "dbo.Users", column 'RoleID'.

the thing is I want to customize that message to a common exception message. how to achieve it?

Thank you.

Emil

emilh Thursday, July 16, 2009 12:13 PM

Catch and re-throw. Note: You can't re-throw as SqlException because it has no accessible constructors.

            try
            {
                // SQL code here.
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547) // The ___ statement conflicted with the ___ constraint ___. The conflict occurred in database ___, table ___.
                {
                    throw new ApplicationException("This update conflicts with other data in the database.");
                }
                else
                    throw; // Re-throw unchanged.
            }

Marked As Answer byemilh Friday, July 17, 2009 9:47 AM

BinaryCoder Friday, July 17, 2009 12:38 AM

Catch and re-throw. Note: You can't re-throw as SqlException because it has no accessible constructors.

            try
            {
                // SQL code here.
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547) // The ___ statement conflicted with the ___ constraint ___. The conflict occurred in database ___, table ___.
                {
                    throw new ApplicationException("This update conflicts with other data in the database.");
                }
                else
                    throw; // Re-throw unchanged.
            }

Marked As Answer byemilh Friday, July 17, 2009 9:47 AM

BinaryCoder Friday, July 17, 2009 12:38 AM

Thank you for reply. But here is another problem raised after changes you gave:

Roles class calls DataClass.DeleteRole

DataClass.DeleteRole:

        public void DeleteRole(int id)
        {
            try
            {
                command.Parameters.Add(new SqlParameter("@ID", id));
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "RolesDelete";
                conn.Open();
                command.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                //this line is looping
                throw new Exception(ex.Message);
            }
        }

------
RolesClass which calls DataClass.DeleteRole:

        private void deleteBut_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Are you sure you want to delete this item?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                try
                {
                    DataClass dc = new DataClass();
                    dc.DeleteRole(int.Parse(this.grid.CurrentRow.Cells["ID"].Value.ToString()));
                    dc.RefreshRoles();
                }
                catch (SqlException ex)
                {
                    if(ex.Number == 547)
                        MessageBox.Show("Constraints error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

catch block in DataClass.DeleteRole is looping and not passed to the calling class - RolesClass to be handled... I hope i could describe the issue...

Emil

emilh Friday, July 17, 2009 4:47 AM

I don't understand how this could happen with just the code you posted. Is this a stack overflow? Can you get a stack trace?

BinaryCoder Saturday, July 18, 2009 2:59 AM

I think I would have structured that class a little differently, but I agree with BC that the code you posted doesn't look like it should get into a loop. Here's another suggestion:

DataClass.DeleteRole:

public bool DeleteRole(int id, ref string Message)
{
	bool IsOK = true;
	try
	{
		command.Parameters.Add(new SqlParameter("@ID", id));
		command.CommandType = CommandType.StoredProcedure;
		command.CommandText = "RolesDelete";
		conn.Open();
		command.ExecuteNonQuery();
		conn.Close();
	}
	catch (SqlException ex)
	{
		IsOK = false;
		if(ex.Number == 547)
			Message = "Constraints error";
		else
			Message = ex.Message;
	}
	return IsOK;
}

RolesClass which calls DataClass.DeleteRole

private void deleteBut_Click(object sender, EventArgs e)
{
	if (MessageBox.Show("Are you sure you want to delete this item?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
	{
		DataClass dc = new DataClass();
		string Message = "";
		if (dc.DeleteRole(int.Parse(this.grid.CurrentRow.Cells["ID"].Value.ToString()), ref Message))
			dc.RefreshRoles();
		else
			MessageBox.Show(Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
	}
}

The point of this refactoring is that now, the Form doesn't know anything about where/how the data is being processed (previously, you had it catching a SqlException ... I don't believe that the Form should know anything at all about the database). This refactored codeis a little cleaner, I think.

~~Bonnie Berent [C# MVP]

BonnieB Sunday, July 19, 2009 4:12 PM

I have been using OleDB driver for SQL server. When I changed it to Sql Native Client, it worked. I have never used OleDb for SQL before and that caused an error... additionally, I think using SQL driver for SQL Server is much more good practice, isn't it?

Thank you all anyway!

Emil

emilh Tuesday, July 21, 2009 10:30 AM

You can use google to search for other answers

Custom Search

More Threads

• Binding datasource in multiple combobox problem

• Dynamic Connection String

• Windows Form DataGridView

• DataGridView empty after update of database

• C# Datagridviewcomboboxcell value is not valid, when using dataset/datatable binding through bindingsource

• How to present 3 arrays in a Listbox with tabs in between.

• using datagridview in .net 1.1

• C# connection to Access Database

• allow nulls in databinding with DateTime datatype and typed dataset

• Setting values as a list in a combo box