How to catch specific MSSQL exception in .NET?

This one is pretty interesting. Not quite sure how useful, but it is good to know it. For the following example you will need one SQL table. I have a table called products. It has a column ProductName (NVARCHAR(10)), which we will use so I can show you how to catch specific SqlException and pass over all other SqlExceptions (actually re-throw them). As you may or may not know the Sql namespace has one exception which is generic and is thrown no matter what caused the exception inside the SqlServer, .NET will always throw SqlException. So how can we distinct the different Sql Errors? Let’s make a short demo so I can show you. First of all, as I said, we will have a table (in my case Products). The ProductName column is set to be

unique, so if a product with the same name already exist in .NET we will have SqlException, but in Sql the things are different. There we will have something like:



Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'p'. Cannot insert duplicate key in object 'dbo.products'.



Let’s say we want to catch only the “Unique violation” exception and leave all other exceptions propagate.



Let’s setup a very small form to illustrate this. We will have two labels, one text box and one button. When clicking on the button the text in the text box will be added in the Products table. If there is already product with the same name we will display the second label.

Simple isn’t it?



Here is the form:



<asp:Label ID="lblProductNameCaption" runat="server" Text="Product name:" />

<br />

<asp:TextBox ID="txtProductName"

         runat="server"

Width="259px">

asp:TextBox>

<br />

<asp:Label ID="lblError"

runat="server"

        Visible="false"

        ForeColor="red"

        Font-Size="x-small">

asp:Label>

<br />

<asp:Button ID="btnSaveProduct"

        runat="server"

        OnClick="btnSaveProduct_Click"

        Text="Save"

Width="79px" />



In order to distinct the unique constraint violation we need to compare it to something. Fortunately SqlException type transports the ErrorNumber from the SqlServer straight to .NET, so we can use it.

We need to find out the number for that particular exception. In a previous post I wrote where can you obtain information about all errors in Sql Server.

Okay, we know the error number of Unique Constraint Violation.

Let’s write some code to handle it:



const int uniqueConstraintViolaton = 2627;



This constant you may define as global so you can access it across your code.



The magic comes in the btnSaveProduct_Click:



SqlConnection con = new SqlConnection("connection_string");

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "Insert into products " +

"(productname)" +

             "values " +

             "('" + txtProductName.Text + "');";

try

{

cmd.ExecuteScalar();

}

catch (SqlException sqlException)

{

if (sqlException.Number == uniqueConstraintViolaton)

    {

     this.lblError.Text = "Product already exist.";

        this.lblError.Visible = true;

     }

     else

     {

     // Pass over ...

        throw sqlException;

     }

}



What we did? We caught SqlException in the catch block.

We tested if it is the Sql error we are expecting. If it was - we performed some actions to inform the user.

If it wasn’t - we re-thrown, so if there is some exception handling code - it could receive it and handle it as expected.



When you type in a name of a product which already exist in the database you should see the label showing, informing you that there is a product with that name.

To test if other exceptions are propagating, you may type a name with more than 10 symbols (I set my ProductName column to be NVARCHAR(10)). You should see exception which informs you that the data would be truncated. Your code will not handle this, but pass it over.



Literature which may be useful: Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2005 For Dummies, Pro ADO.NET 2.0, Programming Microsoft® ADO.NET 2.0 Core Reference

How to get all Microsoft SQL Server errors

This can be achieved by using the sys.sysmessages system view. You may want to specify language to prevent errors localized in all available languages to be returned. English should be with 1033 id. Here is a query which will return all the errors for English: select * from sys.sysmessages where msglangid=1033 Literature which may be useful: Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2005 For Dummies

>

Enumerators - the correct way to parse an enumerator.

First of all - what are enumerators? You can think for the enumerators as for some kind of constants. They are used to check objects against. Let's say you will have the following dropdown: <asp:DropDownList ID="ddlGender" runat="server"> <asp:ListItem Value="1">Maleasp:ListItem> <asp:ListItem Value="2">Femaleasp:ListItem> <asp:ListItem Value="3">Won't shareasp:ListItem> asp:DropDownList> As you know the genders won't ever change, for code convenience, you may want to define an enumerator to hold them, so you can check against: enum EnumUserGender { Male = 1, Female = 2, Unknown

= 3

}



This way, the Male element of the enumerator will have a value of 1, Female will have a value of 2 and Unknown will have a value of 3.

To check a value against enumerator you may do the following:



int Gender = 1;

if (Gender == EnumUserGender.Male)

{



}



In this case the if will evaluate to true, as the Gender variable has a value of 1.



Now, let's return to our case with the asp:DropDownList, we want to be able to know which asp:ListItem was selected by the user. We don't want to convert the value to int first, but to receive it as a EnumUserGender variable. Let's do this in the SelectedIndex SelectedIndexChanged asp:DropDownList:



protected void ddlGender_SelectedIndexChanged(object sender, EventArgs e)

{

EnumUserGender genderSelected = (EnumUserGender)Enum.Parse(typeof(EnumUserGender), ddlGender.SelectedValue, true);

switch (genderSelected)

{

case EnumUserGender.Male:

Response.Write("Check our cars section!");

break;

case EnumUserGender.Female:

Response.Write("Check our make-up section");

break;

case EnumUserGender.Unknown:

Response.Write("If you want you can check either our cars section or our make-up section");

break;

default:

Response.Write("Sorry, there was an" +

"error, please go back and try again," +

" the error was logged and will be reviewed by our team.");

break;

}

}

}



What we did was to actually Parse the Enum , by using its base class method Parse. It recieves 2 parameters + 1 optional:



Parameter 1 is of type Type, this is the type to which the enumerator will be parsed / converted. In our case we use the typeof, function, passing our enum type as a paramter.

Parameter 2 is of type string, this is the value to be parsed.

Parameter 3 is of type bool, and indicates whether the value passed as a string should match case sensitive. If set to true, you will have insensitive Parse, so "mALe" will be parsed correctly.