Open E-School

Male
Friday, February 24, 1984

Importance of exception handling with ADO.net ( Database programming using C# )

Category: Programming Date: Thursday, July 16, 2015

Database programming involves 4 very important steps

  1. Creating the connection with the database
  2. Opening the connection
  3. Executing the SQL statements
  4. Closing the connection

Out of these 4 steps the most important step is the last one – closing the connection. This is because if all the database connections are left open very soon no user will be able to perform any operation on the database. It is because of the extreme importance of this step it becomes very critical that you use exception handling while using ADO.net for database programming using C#.

Possible exceptions which can occur during database interaction

Below we have listed some of the exceptions which you will come across very frequently when using ADO.net code. The bad thing about all these error is that the C# compiler is not able to find these errors and they always show up during runtime. This makes it every more important to use exception handling with ADO.net

1)    Not able to connect to database

Exception handling in ADO.net using C#
 
While working with ADO.net code you will come across the above error very frequently. The cause of this error is a wrong connection string. When you get into this error you should check your connection string and make sure that you have all the required information to connect to the database. If your connection string is correct then you should make sure that your SQL Server is in running state.

2)    Wrong SQL statement

If you write wrong SQL statement and ask ADO.net to execute it then you will again see a runtime exception. This is because the C# compiler cannot compile the SQL statements and find out the error.

3)    Connection lost while processing

It might happen that your application was in the process of reading or writing to the database and the connection is lost in between. This will generate a runtime exception.

How to use exception handling with ADO.net code

To use exception handling with ADO.net code you should keep the following things in mind

1)    Create all your variables outside the try-catch block. This way they will be accessible both in the try and the catch blocks if you need them.

2)    Perform all your ADO.net operations inside the try block. This means that all the 4 steps we mentioned above should be inside the try block.

3)    Always close your database connection inside the finally block. Finally is the best place to close your db connection because not matter what happens the finally block will execute for sure. This will guarantee that any connection that opens will get closed in all circumstances.

Sample code

// Create all variables outside the try-catch block  SqlConnection connection = null;  SqlCommand command = null;    // Write all your code inside the try block  try  {      connection = new SqlConnection();      connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;        connection.Open();        command = new SqlCommand();      command.Connection = connection;        // Insert statement      command.CommandText = string.Format("Insert into branches(branchcode,branchname) values('{0}','{1}')", branch.BranchCode, branch.BranchName);        int rowsAffected = command.ExecuteNonQuery();  }  catch (Exception ex)  {      // Write your exception handling code here.      // If you are not sure what to do when an exception      // happens that just leave this block empty for now.  }  finally  {      // Always close your connection inside the finally block.      if (connection.State == System.Data.ConnectionState.Open)          connection.Close();  }

Read the comments in the code to understand it better. Please note how we are checking the state of the connection in the finally block before closing it. Only if the connection is in open state we will close it. Even if there is a return statement inside the try block the finally block will be called before sending the control back to the calling function.

Rate this article and help us improve

Please Login to rate
Overall ratings: 0 | Rating: out of 5
Previous Article Next Article

Quick Links

E-Magazines

@

Total Followers
Study Group Created
Study Group Joined
Following Teacher
Following Organization
Blog Articles Added
Questions Asked
Questions Answered
Jobs Posted
Total Members in Group
Questions asked by members
Tasks added in this Group

Please wait..

Ok

Login to Open ESchool OR Create your account    Login   SignUp