Introduction
ADO.NET SqlConnecion object has InfoMessage event that would get raised when any informational message or warning 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 you the information about the database, stored procedure and line numbers where the error occurs.
Benefits
The main advantage of using this event in your applications is really interesting and let me narrate few circumstances where it could help you out.
1. Assume that you are performing a series of Execute operations (ExecuteNonQuery or ExecuteReader) using SqlCommand objects that are common to a single SqlConnection object without using transactions. Problem is that when any one of the command doesn’t get executed for some reason, you 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 you perform multiple Sql command executions with-in a transaction; and still wants to override the transactional behaviour. This is the most unfavourable situation one wants to implement in their applications.
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 your application would wait for warnings and errors with severity levels from 11 or more from Sql Server Database.
In the following example, you could see how this is handled. It uses two tables’ product and productdetails in the sales database.
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 you 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 is not inserted.
If you want to include transactional capability to this code, you 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, you may have to look into 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 is 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 for as the database changes are concerned, it is similar to the one you saw in the non-transactional execution.
Conclusion
I was running and testing this program in Visual Studio 2008 Beta2. The inclusion of FireInfoMessageEventOnUserErrors property in the 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 final product comes out, I hope the strange behaviour is modified and new improvements be made. More importantly, developers are looking for a much cleaner way to override Sql transactions.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|