Using InfoMessage Event of SqlConnection Object






2.75/5 (4 votes)
This article explores the usage of 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:
- Assume that we are performing a series of
Execute
operations (ExecuteNonQuery
orExecuteReader
) usingSqlCommand
objects that are common to a singleSqlConnection
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. - 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:
- This example does not use transaction objects and it just performs two insert operations one by one.
- Because the
FireInfoMessageEventOnUserErrors
property is set totrue
, due to the error (invalid type of data passed in theinsert
statement) in the first SQL statement, second SQL Statement only gets executed. As a result, product row is not inserted; productdetails row is inserted. - On the other hand, if the
FireInfoMessageEventOnUserErrors
property is set tofalse
, due to the error (invalid type of data passed in theinsert
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.
- When the
FireInfoMessageEventOnUserErrors
property is set tofalse
, due to the error (invalid type of data passed in theinsert
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. - On the other hand, when the
FireInfoMessageEventOnUserErrors
property is set totrue
, due to the error (invalid type of data passed in theinsert
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 henceInvalidOperationException
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 thecatch
block forInvalidOperationException
, 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
- 8th June, 2009: Initial post