Using InfoMessage Event of SqlConnection Object

Introduction

This article explores the usage of InfoMessage event of SqlConnection and how it is useful in real-world applications. It also examines a small ambiguity when it is used along with transactions.

ADO.NET SqlConnecion object has InfoMessage event that would get raised when any informational message or warning is returned by the SQL Server Database. To be precise, it is raised for errors with severity levels less than 10 and those with severity levels 11 or above causes an exception to be thrown. This event has SqlInfoMessageEventArgs object as an argument that contains Error property. This Error object is a collection of errors with error number and text besides giving us the information about the database, stored procedure and line numbers where the error occurs.

InfoMessage event works in synch with the SqlConnection’s FireInfoMessageEventOnUserErrors property which takes a Boolean value, true or false. If this property is set to true, InfoMessage event is handled and our application would wait for warnings and errors with severity levels from 11 or more from SQL Server Database.

The main advantage of using this event in our applications is really interesting and let me narrate a few circumstances where it could help us out:

1. Assume that we are performing a series of Execute operations (ExecuteNonQuery or ExecuteReader) using SqlCommand objects that are common to a single SqlConnection object without using transactions. The problem is that when any one of the commands doesn't get executed for some reason, we are forced to abandon the rest of the operations by simply notifying the user of the nature of Exception being thrown out. 
2. Assume that we perform multiple SQL command executions within a transaction; InfoMessage event can still be raised and helps us to override the transactional behaviour. This is the most unfavourable situation one wants to implement in his/her applications. Overriding the transactional behavior would surely dis-integrate the ACID properties of a transaction.

Let us examine both the scenarios one by one.

Scenario 1

The following example uses two tables’ product and productdetails in the sales database. The code below does not use transactions:

    SqlConnection conn = new SqlConnection
		(@"Data Source=.\SQLEXPRESS;AttachDbFilename=
		D:\Users\bala\Documents\sales.mdf;Integrated Security=True;
		Connect Timeout=30;User Instance=True");
    conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
    conn.FireInfoMessageEventOnUserErrors = true;
    conn.Open();

    try
    {
        //  SQL insert for product row 
        string myDataCmds = "insert into product (productid, prodname, desc) 
        	values ('abc', 'Driver Kit', 'Kit useful for driver functions')";
        SqlCommand comm = new SqlCommand(myDataCmds, conn);
        int rows = comm.ExecuteNonQuery();
        if (rows > 0)
            MessageBox.Show("A row inserted into product table");
        else
            MessageBox.Show("Product not inserted in the product table");
        myDataCmds = "insert into productdetail (productid, model, manufacturer) 
        	values (101, 'ModelA', 'Simpson Co.')";
        comm = new SqlCommand(myDataCmds, conn);
        rows = comm.ExecuteNonQuery();
        if (rows > 0)
            MessageBox.Show("A row inserted into productdetail table");
        else
            MessageBox.Show("Detail not inserted in the productdetail table");
    }
    catch (Exception e)
    {
        MessageBox.Show("Exception thrown: " + e.Message);
    }
            
    conn.Close();
}

static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    MessageBox.Show("InfoMessage Handled Error Level-" + 
	e.Errors[0].Class.ToString() + ":" + e.Message);           
}

There are few things we need to notice here in the above code:

1. This example does not use transaction objects and it just performs two insert operations one by one. 
2. Because the FireInfoMessageEventOnUserErrors property is set to true, due to the error (invalid type of data passed in the insert statement) in the first SQL statement, second SQL Statement only gets executed. As a result, product row is not inserted; productdetails row is inserted. 
3. On the other hand, if the FireInfoMessageEventOnUserErrors property is set to false, due to the error (invalid type of data passed in the insert statement) in the first SQL statement, an exception is thrown stopping the further proceedings of the program. As a result, both the product row and productdetails row are not inserted.

Scenario 2

To include transactional capability in the above example, we may have to add commit and rollback operations in the code as below. As expected, there are changes in the program results too.

    SqlConnection conn = new SqlConnection
	(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Users\bala\Documents\sales.mdf;
	Integrated Security=True;Connect Timeout=30;User Instance=True");
    conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
    conn.FireInfoMessageEventOnUserErrors = true;
    conn.Open();
    SqlTransaction tran = conn.BeginTransaction();

    try
    {
        //  SQL insert for product row 
        string myDataCmds = "insert into product (productid, prodname, desc) 
        	values ('abc', 'Driver Kit', 'Kit useful for driver functions')";
        SqlCommand comm = new SqlCommand(myDataCmds, conn);
        comm.Transaction = tran;
        int rows = comm.ExecuteNonQuery();
        if (rows > 0)
            MessageBox.Show("A row inserted into product table");
        else
            MessageBox.Show("Product not inserted in the product table");
        myDataCmds = "insert into productdetail (productid, model, manufacturer) 
        	values (101, 'ModelA', 'Simpson Co.')";
        comm = new SqlCommand(myDataCmds, conn);
        comm.Transaction = tran;
        rows = comm.ExecuteNonQuery();
        if (rows > 0)
            MessageBox.Show("A row inserted into productdetail table");
        else
            MessageBox.Show("Detail not inserted in the productdetail table");
        tran.Commit();
    }
    catch (InvalidOperationException ioe)
    {
        MessageBox.Show("Exception thrown but transaction performed partially! ");
    }
    catch (Exception e)
    {
        MessageBox.Show("Exception thrown: " + e.Message);
        tran.Rollback();
    }
            
    conn.Close();
}

static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    MessageBox.Show("InfoMessage Handled Error Level-" + 
    	e.Errors[0].Class.ToString() + ":" + e.Message);
}

In the transactional scenario, we may have to look into the following behaviour of the code. 

1. When the FireInfoMessageEventOnUserErrors property is set to false, due to the error (invalid type of data passed in the insert statement) in the first SQL statement, an exception is thrown stopping the further proceedings of the program. As a result, both the product row and productdetails row are not inserted. 
2. On the other hand, when the FireInfoMessageEventOnUserErrors property is set to true, due to the error (invalid type of data passed in the insert statement) in the first SQL statement, second SQL Statement gets executed overriding the default nature of transaction. It tries to rollback the committed change which is not allowed and hence InvalidOperationException is thrown. But as far as the database changes are concerned, it is similar to the one we saw in the non-transactional execution. If we remove the catch block for InvalidOperationException, the application hangs and needs to be stopped manually.

Conclusion

This program is tested and run in the recently released version of Visual Studio 2008. The inclusion of FireInfoMessageEventOnUserErrors property in SqlConnection is really useful for performing multiple SQL commands in non-transactional mode. In a transactional mode, it behaves abnormally forcing the developers to include an additional Exception check; When the next version of VS comes out, I hope the strange behaviour is modified and new improvements be made. More importantly, developers are looking for a much cleaner and better way to override SQL transactions and perform partial transactions.

History

• 8^th June, 2009: Initial post