Vb Net Catch Exception and Log but Continue

By: | Updated: 2019-05-24 | Comments | Related: More > Application Development

Problem

In many of my previous Application Development tips, among other, we've talked about calling SQL Server via queries, functions, stored procedures and more, from within our .NET code. Besides the implementation of the calls, another important aspect, is exception handling. With the proper exception handling mechanisms, you can report back to your application's users, what is the exact issue in case a database call fails, as well as fully control your application's flow. Moreover, this can help the user take possible corrective actions that will help the application to work properly.

In this tip, we will talk about setting up the proper exception handling mechanism, for .NET applications that make calls to SQL Server.

Solution

In order to better understand proper exception handling in .NET when it comes to SQL Server database calls, we will be using a simple query call to SQL Server from within our .NET app (C#).

For the above database call, we will be intentionally making a problematic call, in order to see what errors are being reported in our application with and without explicit exception handling, as well as see how the flow of the program is affected.

Sample Database

In order to be able to better understand the different concepts, we will need a sample database. This database is called "SampleDB" and I have created it on a test SQL Server named instance on my local machine. The name of the instance is "SQL2K17".

Here's a screenshot of the SQL Server instance, as it can be seen in SSMS:

This is a sample database that will be used for this tip examples.

Also, here's the database diagram of the tables in the "SampleDB" database which will be used for this tip's example:

Database Diagram - This is the database diagram for the SampleDB database, illustrating the two tables that will be used in this tip example.

As you can see, there is a table named "employees" and another table named "location".

The "employees" table has a foreign key (locationID) pointing to the "location" table and more specifically, to the "id" column.

Sample Data

The sample data in the "location" table are:

Sample Data - This is a screenshot of the sample data in the location table.

The sample data in the "employees" table are:

Sample Data - This is a screenshot of the sample data in the employees table.

The Query Call

Now, let's create a simple Visual C# Console App (.NET Framework), name it "QueryCall" and make a simple query call to the database.

Creating a Visual C# Console App .NET Framework

The query to execute is the below:

SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr FROM employees1 e INNER JOIN location l on e.locationID=l.id;

As you can see in the above code, even though the correct table names in our sample database is "employees" and "location", instead of calling "employees", I'm calling the table "employees1", which does not exist, in order to get an error. So, the initial code for our console application is the below:

After running the program, this is what we get as an error:

Program output without exception handling

As you can see, the program's execution failed and returned an unhandled exception and therefore, it displayed the stack trace, which is the default behavior in such cases. That means that there is not a proper exception handling mechanism in the code, in order to handle any issues that may have to do with the interaction between our C# app and SQL Server.

Moreover, since an unhandled exception took place, the program's execution was terminated (application crash) without being able to proceed with the execution of the rest of the program's logic, that is executing the code line:

Console.WriteLine("...program execution after database calls...");

Further Analysis – The Need for Exception Handling

If we further analyze the source code, we can see that the actual code that executes the query is:

dr = cmd.ExecuteReader();

If we check MS Docs about the SqlCommand.ExecuteReader method, we can see that it provides comprehensive information about the supported exception types.

So, from the MS Docs article, we can see that the above method supports the below exception types:

InvalidCastException
SqlException
InvalidOperationException
IOException

This helps us decide how to make use of a more precise exception mechanism. In this case, we will make use of the "SqlException" type. To this end, the approach to be followed regarding exception handling for our app is the following:

Use an exception handling mechanism for the SqlCommand.ExecuteReader method, using the exception type " SqlException " (inner code exception handling)
Use an exception handling mechanism for the "using" code block, using the exception type " Exception " (outer code exception handling)

By using the above exception handling approach, not only we will be handling any unexpected issues that may have to do with the database calls, but also, we will be allowing our program to continue its execution, if of course, this is the intended behavior. More precisely, we will be able to fully control the program's flow, even in the event of an exception.

Implementing Exception Handling

To this end, based on the above analysis, we need to add two exception handling blocks in our code.

The first block which will be handling any exceptions for the SqlCommand.ExecuteReader method will be:

try {            //execute the SQLCommand            dr = cmd.ExecuteReader(); } catch (SqlException ex) {     Console.WriteLine("Inner Exception: "            + ex.Message);     Console.WriteLine();     Console.WriteLine("Query Executed: "            + query);     Console.WriteLine();     dr.Close(); } finally {     ;            //here you can add any code you want to be executed            //regardless if an exception is thrown or not            }

In the above exception handling code, you can note the syntax which uses the keywords try…catch…finally.

The "try" keyword encapsulates the code to be executed and whenever an exception is thrown, it passes the execution to the "catch" code block. Therefore, in the "catch" code block, you can include the handling code you want to be executed in the event of an exception. Last, the "finally" keyword/code block, is always executed, regardless if an exception is thrown or not.

The outer exception handling block will encapsulate the entire "using…" block in order to catch any other exceptions besides the SqlException block.

This code block will look like this:

try {            //sql connection object            using (SqlConnection conn = new SqlConnection(connString))     {          //… application code …     } } catch (Exception ex) {     Console.WriteLine("Outer Exception: "            + ex.Message); } finally {     Console.WriteLine();     Console.WriteLine("...program execution after database calls..."); }

With the above two exception handling blocks, we will be able to fully manage any exception in our code and control the application's flow in those cases as well.

So, based on all the above, our application's entire source code changes as per below:

And this is the new output of our program's execution:

Program output when using exception handling

Now, as you can see, our program's code fully manages any exception, as well it is more user-friendly, because instead of displaying an unhandled exception along with displaying its stack trace, it now displays more meaningful error messages to the user.

Discussion

It is natural during a software application's lifecycle, the application to sometimes experience errors due to either invalid user input or unexpected computing environment issues. For example, a database server might not be accepting connections via TCP/IP, a user might not be having access to a database object, etc.

If you don't handle these exceptions in your code, then your application will not have a controlled behavior, meaning that some things might run and some not.

With proper exception handling, you can fully control any unexpected issues, inform the user accordingly and in general, smoothly control your application's flow in such cases and thus avoid application freezes and crashes.

Proper exception handling is always a must in any application, not only database apps.

Especially when developing in .NET, make sure you follow the Best Practices for Exceptions guide, as well as, always find the proper exception type for the operations you perform in your app. Moreover, always include an outer Exception try…catch…finally block in order to catch and handle any exceptions that might have not being caught by your inner exception handling blocks.

Next Steps

Check my tip: Querying SQL Server Tables from .NET
Check my tip: Working with SQL Server Functions and .NET
Check my tip: Working with SQL Server Stored Procedures and .NET
Check the MS Docs article: Best Practices for Exceptions
Check the MS Docs article: Exception Class
Check the MS Docs article: SqlException Class

Ribush Spaided01