How to use Try Catch Block to Catch Exceptions

How to use Try Catch Block to Catch Exceptions

When performing an ADO.NET operation you can sometimes get some exception like:

1. Database connection error – due to wrong connection string.
2. Operation timeout error – due to some network error.

You can catch these exceptions, of type SqlException, thrown during an ADO.NET operation by using the Try Catch block.

Take for example I update the Delete action method from the above code to include the try catch block like shown below:

[HttpPost]
public IActionResult Delete(int id)
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = $"Delete From Inventory Where Id='{id}'";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                ViewBag.Result = "Operation got error:" + ex.Message;
            }
            connection.Close();
        }
    }

    return RedirectToAction("Index");
}

Notice I kept the ExecuteNonQuery() code inside the try block, so that if there happens to be any exception then I can catch it in the catch statement.

Inside the catch block I added the exception message, with a string to a ViewBag variable, so that I can show this message on the View.

ViewBag.Result = "Operation got error:" + ex.Message;

Database Transaction with SqlTransaction class

A Database Transaction is a set of operations that should either all succeed or all fail as a collective unit. Transactions are necessary to ensure that the data in the database is safe, valid and consistent.

A classic example involves transferring money in a bank from one account to another. Suppose there are 2 people named – Trump and Putin.

Putin transfers $100 from his bank account to Trump’s account. So in this transaction there are 2 operations:

  • 1. $100 is deducted from Putin’s account.
  • 2. $100 is added to Trump’s account.

Both of these operations should complete successfully in order for the transaction being successful. Because it will be unwise if the money is deducted from 1st account but is not added to the 2nd account.

If any part of the transaction fails, the entire transaction is rolled back to the original state. On the other hand, if all steps succeed, the transaction is committed.

SqlTransaction class

ADO.NET supports transaction from its SqlTransaction class located under the System.Data.SqlClient namespace.

The key members of the SqlTransaction class are:

  • Commit() – the method commits the database transaction.
  • Rollback() – this method rollbacks the transaction to the previous state.

Implementation of SqlTransaction

Let’s implement a small transaction using the SqlTransaction class. In your database create a new table called Account which contains 3 columns:

1. ‘Id’ which is primary key and Identity
2. ‘Name’ which contains the name of the person.
3. ‘Money’ which contains the total money the person has

You can create this table by running the following below script on the New Query window.

CREATE TABLE [dbo].[Account]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY (1, 1), 
    [Name] VARCHAR(50) NOT NULL, 
    [Money] MONEY NOT NULL
)

The New Query window can be opened by right clicking the Database and select New Query.

Also add 2 records to this table, first one with name ‘Putin’ and money ‘100’, and for second put name as ‘Trump’ with money as ‘100’. See the below image:

account table

View

Create a View called TransferMoney inside the ‘Views ►Home’ folder with the following code:

@model string

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ADO - Transfer Money</title>
    <link rel="stylesheet" asp-href-include="lib/bootstrap/dist/css/*.min.css" />
</head>
<body>
    <div class="container-fluid">
        <h1>Transfer $100 from Putin to Trump</h1>
        @if (Model != null)
        {
            <h2 class="alert alert-danger">@Model</h2>
        }
        <form method="post">
            <div class="form-group">
                <label>Throw Excepton</label>
                <select name="throwEx" class="form-control">
                    <option>false</option>
                    <option>true</option>
                </select>
            </div>
            <div class="text-center panel-body">
                <button type="submit" class="btn btn-sm btn-primary">Transfer</button>
            </div>
        </form>
    </div>
</body>
</html>

The View has a model of type string and it is used to show the result of the transaction inside an ‘h2’ tag:

@if (Model != null)
{
    <h2 class="alert alert-danger">@Model</h2><div class="note">Action</div>
}

I have a select control inside the form which has its name as ‘throwEx’. Through this control I will have the option to ‘raise an error or not’ during the transaction (see the action method code to understand it).

<select name="throwEx" class="form-control">
    <option>false</option>
    <option>true</option>
</select>

Action

Now go the Home Controller and add a new action method called TransferMoney with the following code:

[HttpPost]
public IActionResult TransferMoney(bool throwEx)
{
    string result = "";

    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        var cmdRemove = new SqlCommand($"Update Account Set Money=Money-100 Where Name='Putin'", connection);
        var cmdAdd = new SqlCommand($"Update Account Set Money=Money+100 Where Name='Trump'", connection);

        connection.Open();

        // We will get this from the connection object.
        SqlTransaction tx = null;
        try
        {
            tx = connection.BeginTransaction();

            // Enlist the commands into this transaction.
            cmdRemove.Transaction = tx;
            cmdAdd.Transaction = tx;

            // Execute the commands.
            cmdRemove.ExecuteNonQuery();
            cmdAdd.ExecuteNonQuery();

            // Simulate error.
            if (throwEx)
            {
                throw new Exception("Sorry! Database error! Transaction failed");
            }

            // Commit it!
            tx.Commit();

            result = "Success";
        }
        catch (Exception ex)
        {
            // Any error will roll back transaction. Using the new conditional access operator to check for null.
            tx?.Rollback();
            result = ex.Message;
        }
        finally
        {
            connection.Close();
        }
    }

    return View((object)result);
}

Explanation: Notice it has the parameter – bool throwEx which gives it the value of the select control.

Since the transaction will have 2 operations therefore I create 2 SqlCommand objects – one which deletes $100 from Putin’s account:

var cmdRemove = new SqlCommand($"Update Account Set Money=Money-100 Where Name='Putin'", connection);

And the other which adds $100 to the Trump’s accont:

var cmdAdd = new SqlCommand($"Update Account Set Money=Money+100 Where Name='Trump'", connection);

Next, inside the try block I starts the database transaction by the code – connection.BeginTransaction(); which gives an SqlTransaction object.

I also set this SqlTransaction object to the Transaction properties of the two SqlCommand objects defined earlier:

cmdRemove.Transaction = tx;
cmdAdd.Transaction = tx;

Now I execute both these 2 operations like:

cmdRemove.ExecuteNonQuery();
cmdAdd.ExecuteNonQuery();

And if both of them completes successfully then I commit the whole transaction like:

tx.Commit();

I also check the value of the ‘throwEx’ variable (which contains the select control value). If it is true then I throw an exception:

if (throwEx)
{
    throw new Exception("Sorry! Database error! Transaction failed");
}

And if the exception happens then the catch block is called. Here I Rollback() the transaction, and set the model variable’s value to the exception message:

tx?.Rollback();
result = ex.Message;

Test the Transaction feature

Run your project and go to the URL – ‘http://localhost:61126/Home/TransferMoney’. Select true in the select control and press the button. You will see transaction failed message, as shown by the below image:

transaction failed

Now refresh the Account table and you will see the no money has transferred from Putin’s account to Trump’s.

This happens because an exception was raised before the .Commit() method was called.

Now select ‘false’ in the select control and press the button. This time you will see the ‘Success’ message.

transaction success

Now refresh the ‘Account’ table and you will notice the money has transferred from Putin’s account to that of Trump’s.

account table updated

You can download the source code using the below link:

Download

Conclusion

In this tutorial you learned to use the Try Catch feature to get rid of database errors when working with ADO.NET. I hope you liked it, please share this tutorial with your friends.

Up next – SqlBulkCopy class of ADO.NET

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.