Modify values on-the-fly during SqlAdapter.Fill( )

Posted by Timothy on Stack Overflow See other posts from Stack Overflow or by Timothy
Published on 2010-05-05T04:24:04Z Indexed on 2010/05/05 4:28 UTC
Read the original article Hit count: 264

Filed under:
|
|

What would the proper way be to modify values on the fly as they are loaded into a DataTable by SqlAdapter.Fill()?

I have globalized my application's log messages. An integer indicating the event type and serialized data relevant to the event is stored in the database as show below. When I display the logged events through a DataGridView control to the user, I interpolate the data to a formatting string.

event_type event_timestamp     event_details
============================================
3          2010-05-04 20:49:58 jsmith
1          2010-05-04 20:50:42 jsmith
...

I am currently iterating through the DataTable's rows to format the messages.

public class LogDataTable : DataTable
{
    public LogDataTable()
    {
        Locale = CultureInfo.CurrentCulture;
        Columns.AddRange(new DataColumn[] {
            new DataColumn("event_type", typeof(Int32)),
            new DataColumn("event_timestamp", typeof(DateTime)),
            new DataColumn("event_details", typeof(String))});
    }
}

...
using (SqlDataAdapter adapter = new SqlDataAdapter(...)) 
{
    adapter.SelectCommand.Parameters.AddRange(new Object[] {
       ...
    });
    adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
    switch ((LogEventType)row["event_type"])
    {
       case LogEventType.Create:
           row["event_details"] = String.Format(Resources.Strings.LogEventCreateMsg, row["event_details"];
           break;
       case LogEventType.Create:
           row["event_details"] = String.Format(Resources.Strings.LogEventCreateMsg, row["event_details"];
           break;
       ...

The end result as displayed would resemble:

Type   Date and Time       Details
====================================================================
[icon] 2010-05-04 20:49:58 Failed login attempt with username jsmith
[icon] 2010-05-04 20:50:42 Successful login with username jsmith
...

It seems wasteful to iterate the result set twice-- once as the table is filled by the adapter, and again to perform the replacements. I would really like to do the replacement on-the-fly in my LogDataTable class as it is being populated.

I have tried overriding an OnRowChanging method in LogDataTable, which throws an InRowChangingEventException.

protected override void OnRowChanging(DataRowChangeEventArgs e)
{
    base.OnRowChanging(e);
    switch ((LogEventType)row["event_type"])
    ...

I have tried overriding an OnRowChanged method, which throws a StackOverflowException (I assume changing it re-triggers the method ad infinitum?).

I have tried overriding an OnTableNewRow method, which does not throw an exception but appears not to be invoked (I assume only when a user adds a row in the view, which I've prevented).

I'd greatly appreciate any assistance anyone can give me.

© Stack Overflow or respective owner

Related posts about c#

Related posts about datatable