ADO.NET – Get the notification events from Managed Providers

• Download demo project - 31.4 Kb

Introduction

Like ADO, ADO.NET provides the mechanism you can use to get the events raised by the managed providers when certain actions are taking place or have taken place. This is a cool feature that in some cases becomes very handy when you want to change the action based on the rules that database access layer is to follow. E.g. the application changes value of a column in a table. The application may not be aware of all the rules that it is supposed to follow to make a judgment whether the values it is changing to are legal or not. Then you can set up an event notification function for that DataTable such that you receive notification when a column is being changed for that table. Then you can check the values that are being changed and depending on the business rules you can either let it go or change the values in that event notification so that valid value gets entered into the database. This is just one example how the event handlers can help you control the events and changes that happen to flow of data. ADO.NET support two kinds of Managed providers. One based on OLE DB and the other for SQL Server. For these two types of managed providers you will see two name spaces in the .NET framework, System.Data.ADO and System.Data.SQL. As the name suggests, System.Data.SQL is exclusively for SQL Server 7.0 and higher.

In this article I will be using the general OLE DB based provider System.Data.ADO namespace. The first thing every database application does is open the connection with database. You will be using ADOConnection object to make this connection. While this connection is being established, the server may come across some problems. For all these problems, server sends events containing information about warnings and errors. You can event handler to your connection object. ADOConnection object supports InfoMessage event to receive these notifications. The messages are passed back in ADOInfoMessageEventArgs object. This event supports following properties.

• ErrorCode
• Errors
• Message
• Source

Look at the reference documentation to get details about these properties. But in short I can tell you that these properties can give you complete description about the error like complete error text, source of error, etc.

Here is the example on how you add event handler to your ADOConnection object. First you add the event handler to connection object.

String strConnection = "Provider= SQLOLEDB.1;
      Data Source=localhost; uid=sa; pwd=; Initial Catalog=northwind";

m_ADOConnection = new ADOConnection (strConnection);

m_ADOConnection.InfoMessage += new 
      ADOInfoMessageEventHandler (OnInfoMessageFromConnection);

m_ADOConnection.StateChange += 
     new StateChangeEventHandler (OnStateChange);

And then you need to provide the event handler function for the events. E.g. in this case OnInfoMessageFromConnection & OnStateChange.

protected static void OnInfoMessageFromConnection 
            (object sender, ADOInfoMessageEventArgs args)
{
    Trace.Write ("Info Message Recieved From Server");
    Trace.Write (args.Message);
    Trace.Write ("End Of Message From Server");
}
 
protected static void OnStateChange(object sender, StateChangeEventArgs e)
{
    PrintEventArgs(e);
}

After you have established connection, then next step is retrieving the data from the data store and then manipulating it. The information is accessed in DataTable objects. And then you make changes to the information and submit the changes to data source. DataTable object supports the following events that you can intercept.

• ColumnChanging
• PropertyChanged
• RowChanged
• RowChanging
• RowDeleted
• RowDeleting

The names of the events are very self-explanatory. So depending on your needs you can event handlers for these events. Here is the example on how you can do it.

public static void SetEventHandlers (DataTable table)
{
   table.ColumnChanging += new 
         DataColumnChangeEventHandler (ColumnChangingEvtHandler);
   table.RowChanging += new 
         DataRowChangeEventHandler   RowChangingEvtHandler);
   table.RowChanged += new 
         DataRowChangeEventHandler (RowChangedEvtHandler);
   table.RowDeleting += new 
         DataRowChangeEventHandler (RowDeletingEvtHandler);
   table.RowDeleted += new 
         DataRowChangeEventHandler (RowDeletedEvtHandler);
}
 
public static void RowChangingEvtHandler 
       (object sender, DataRowChangeEventArgs args)
{
    Trace.Write ("Row Changing: " + args.Row.ToString ());
}

public static void RowChangedEvtHandler 
       (object sender, DataRowChangeEventArgs args)
{
    Trace.Write ("Row Changed: " + args.Row.ToString ());
}
 
public static void RowDeletingEvtHandler 
       (object sender, DataRowChangeEventArgs args)
{
      Trace.Write ("Row Deleting: " + args.Row.ToString ());
}
 
public static void RowDeletedEvtHandler 
       (object sender, DataRowChangeEventArgs args)
{
      Trace.Write ("Row Deleted: " + args.Row.ToString ());
}
 
public static void ColumnChangingEvtHandler 
      (object sender, DataColumnChangeEventArgs args)
{
      Trace.Write ("Column Changing: " + args.Column.ColumnName);
}

Most of these events are just providing information except ColumnChanging where you can change the values that are being set.

DataSet object supports the following events.

• MergeFailed
• PropertyChanged

Similar to the way I added events for DataTable, you can add the events for this object. MergeFailed event occurs when the two tables fail to merge because of some constraints. PropertyChanged event occurs when a property value changes.

DataRelation class the represents parent/child relationship between tables has only one event, PropertyChanged. This event occurs whenever a property value of the DataRelation changes.