SQL Server throws a custom exception, which is captured by the C # program and processed accordingly,

SQL Server throws a custom exception, which is captured by the C # program and processed accordingly,

 

Recently, I have been searching for custom exceptions and capturing and processing them with the C # program. I tried many methods but I have not succeeded. I finally found a good method today. So I will repost and share it with you.

From: http://www.cnblogs.com/scottckt/archive/2007/12/07/986847.html

In the stored procedures of SQL Server, custom exceptions are sometimes thrown according to the requirements of the business logic, captured by the C # program and processed accordingly. SQL Server throws a custom exception and is simple, just like this: RAISERROR ('rais Error1 ', 16, 1) WITH NOWAIT, but it is very particular about the error levels, otherwise, the catch Block in C # may not be captured.

The method for SQL Servr to throw a custom exception is generally written as follows: RAISERROR ('rais error1', 16, 1) WITH NOWAIT

The number 16 indicates the error level: the error level ranges from 0 to 25, 19 to 25 indicates the major error level.

Note: A value smaller than 0 is interpreted as 0, and a value greater than 25 is interpreted as 25.

Any user can specify the error level between 0 and 18.

Errors ranging from 19 to 25 can only be specified by members of the sysadmin role using the with log option.

Errors of levels 19 to 25 are recorded in the error log and Application Log.

Errors of 20 to 25 levels are considered fatal. In case of a fatal level error, the client connection will terminate after receiving the message. The catch Block in C # can capture exceptions with error levels 11 to 19.

Errors of 0 to 10 levels are not caught at all. Exceptions of 20 to 25 levels are considered fatal and will be disconnected from the database. Therefore, the C # cath block can accept this exception, but the exception content is not a real exception content. It may be something like this: "A transmission-level error occurs when receiving results from the server ".

Public void GetSqlError ()

{

Try

{

String connString = "Data Source = CCM02 // SQLEXPRESS; Initial Catalog = Northwind; User ID = sa; Password = sh2_123 ";

String SQL = "select * from Products ";

String raisError = "RAISERROR ('rais Error1 ', 16, 1) with nowait ";

SQL = SQL + ";" + raisError;

SqlConnection con = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, con );

Con. Open ();

Cmd. ExecuteNonQuery ();

Con. Close ();

}

Catch (Exception ex)

{

RtbValue. Text = ex. Message. ToString ();

}

}

Result:

RtbValue. Text: "Rais Error1"