none
RowUpdating when working with the ADO.NET DBProviderFactories? RRS feed

  • Question

  • Hello,

    I use the DBProviderFactories in VS2008, .NET 3.5 with e.g. CreateFactory, CreateConnection, DbConnection, DbDataAdapter, etc. instead of OleDbConnection, OleDbDataAdapter to achieve provider independance. This works absolutely great.

    But : there is no way to catch RowUpdating and RowUpdated events while using DbDataAdapter objects, though it's feasible using SqlDataAdapter and OleDbDataAdapter. I already searched the forums and this was the same at times of VS2005. Is there a solution out there?

    Thanks to anyone having a solution to my problem.

    Kai
    Wednesday, May 13, 2009 4:54 AM

Answers

  • Hi Kai,

    This is a really interesting problem! Making a general solution is much more tricky than it seems due to the way that the type system works for C# events. Here is a solution that works (based on the code sample here ), but is not very general, since you would need to create a pair of methods for adding/removing for each handler function that you want to add to each event. If anybody can come up with a better solution to this, I'd be really interested to see it!

    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.Odbc;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    
    namespace ConsoleApplication14
    {
    
        class Program
        {
            private static void AddRowUpdatingHandler(DbDataAdapter adapter)
            {
                if (adapter is SqlDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating += OnRowUpdating;
                }
                else if (adapter is OleDbDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating += OnRowUpdating;
                }
                else if (adapter is OdbcDataAdapter)
                {
                    (adapter as OdbcDataAdapter).RowUpdating += OnRowUpdating;
                }
                else
                {
                    throw new ArgumentException("Unknown type of adapter");
                }
            }
    
            private static void RemoveRowUpdatingHandler(DbDataAdapter adapter)
            {
                if (adapter is SqlDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating -= OnRowUpdating;
                }
                else if (adapter is OleDbDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating -= OnRowUpdating;
                }
                else if (adapter is OdbcDataAdapter)
                {
                    (adapter as OdbcDataAdapter).RowUpdating -= OnRowUpdating;
                }
                else
                {
                    throw new ArgumentException("Unknown type of adapter");
                }
            }
    
            private static void OnRowUpdating(object sender, RowUpdatingEventArgs e)
            {
                PrintEventArgs(e);
            }
    
            public static int Main()
            {
                const string connectionString = "<<<< put connection string here >>>>";
                const string queryString = "SELECT * FROM Products";
    
                // create DataAdapter
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
    
                using (DbDataAdapter adapter = factory.CreateDataAdapter())
                {
                    adapter.SelectCommand = factory.CreateCommand();
                    adapter.SelectCommand.Connection = factory.CreateConnection();
                    adapter.SelectCommand.Connection.ConnectionString = connectionString;
                    adapter.SelectCommand.Connection.Open();
                    adapter.SelectCommand.CommandText = queryString;
    
                    DbCommandBuilder builder = factory.CreateCommandBuilder();
                    builder.DataAdapter = adapter;
    
                    // Create and fill DataSet (select only first 5 rows)
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, 0, 5, "Table");
    
                    // Modify DataSet
                    DataTable table = dataSet.Tables["Table"];
                    table.Rows[0][1] = "asdf";
    
                    // add handlers
                    AddRowUpdatingHandler(adapter);
    
                    // update, this operation fires two events 
                    // (RowUpdating/RowUpdated) per changed row 
                    adapter.Update(dataSet, "Table");
    
                    // remove handlers
                    RemoveRowUpdatingHandler(adapter);
    
                    // Modify DataSet again
                    table.Rows[0][1] = "qwerty";
    
                    // update again, this operation fires two events 
                    // (RowUpdating/RowUpdated) per changed row 
                    adapter.Update(dataSet, "Table");
                }
                return 0;
            }
    
            private static void PrintEventArgs(RowUpdatingEventArgs args)
            {
                Console.WriteLine("OnRowUpdating");
                Console.WriteLine("  event args: (" +
                    " command=" + args.Command +
                    " commandType=" + args.StatementType +
                    " status=" + args.Status + ")");
            }
    
            private static void PrintEventArgs(RowUpdatedEventArgs args)
            {
                Console.WriteLine("OnRowUpdated");
                Console.WriteLine("  event args: (" +
                    " command=" + args.Command +
                    " commandType=" + args.StatementType +
                    " recordsAffected=" + args.RecordsAffected +
                    " status=" + args.Status + ")");
            }
    
        }
    }
    

    Cheers,
    Jared
    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, June 11, 2009 8:20 PM
  • Here is a generic way to do it for all adapters that support RowUpdating event on their DataAdapter implementation:

    using System;
    using System.Reflection;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    
    namespace DataSamples
    {
        public class RowUpdatingEventSample
        {
            public static void ad_RowUpdating(object sender, RowUpdatingEventArgs e)
            {
                Console.WriteLine("in RowUpdating: status {0}", e.Status);
            }
    
            public static void Main()
            {
                using(SqlConnection con = new SqlConnection("Data Source=NNDEV\\KATEXPRESS;Initial Catalog=TestDB;Integrated Security=true"))
                {
                    con.Open();
                    SqlDataAdapter ad = new SqlDataAdapter("SELECT * from test", con);
                    SqlCommandBuilder builder = new SqlCommandBuilder(ad); // auto-create UPDATE/INSERT/DELETE and other commands
    
                    DataTable table = new DataTable();
                    ad.Fill(table);
    
                    RegisterForRowUpdatingEvent(ad, ad_RowUpdating);
    
                    table.Rows[0][0] = Environment.TickCount; // first column is key of type int
                    ad.Update(table);
                }
            }
    
    
            delegate void DbRowUpdatingEventHandler(object sender, RowUpdatingEventArgs e);
    
            /// <summary>
            /// this method registers RowUpdating event handler for data adapters that support this event
            /// </summary>
            static void RegisterForRowUpdatingEvent(DbDataAdapter ad, DbRowUpdatingEventHandler handler)
            {
                EventInfo rowUpdatingEvent = ad.GetType().GetEvent("RowUpdating");
                if (rowUpdatingEvent == null)
                    throw new InvalidOperationException(ad.GetType().Name + " does not support RowUpdating event");
    
                // reconstruct the delegate from the exact type required by the adapter event
                // otherwise, the event will not accept it
                Delegate d = Delegate.CreateDelegate(rowUpdatingEvent.EventHandlerType, handler.Target, handler.Method, true);
                
                rowUpdatingEvent.AddEventHandler(ad, d);
            }
        }
    }
    

    Similarly, you can create the 'event remove' version by using RemoveEventHandler method on the rowUpdatingEvent event info

    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, June 11, 2009 11:47 PM
    Moderator

All replies

  • Hi Kai,

    This is a really interesting problem! Making a general solution is much more tricky than it seems due to the way that the type system works for C# events. Here is a solution that works (based on the code sample here ), but is not very general, since you would need to create a pair of methods for adding/removing for each handler function that you want to add to each event. If anybody can come up with a better solution to this, I'd be really interested to see it!

    using System;
    using System.Data;
    using System.Data.Common;
    using System.Data.Odbc;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    
    namespace ConsoleApplication14
    {
    
        class Program
        {
            private static void AddRowUpdatingHandler(DbDataAdapter adapter)
            {
                if (adapter is SqlDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating += OnRowUpdating;
                }
                else if (adapter is OleDbDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating += OnRowUpdating;
                }
                else if (adapter is OdbcDataAdapter)
                {
                    (adapter as OdbcDataAdapter).RowUpdating += OnRowUpdating;
                }
                else
                {
                    throw new ArgumentException("Unknown type of adapter");
                }
            }
    
            private static void RemoveRowUpdatingHandler(DbDataAdapter adapter)
            {
                if (adapter is SqlDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating -= OnRowUpdating;
                }
                else if (adapter is OleDbDataAdapter)
                {
                    (adapter as SqlDataAdapter).RowUpdating -= OnRowUpdating;
                }
                else if (adapter is OdbcDataAdapter)
                {
                    (adapter as OdbcDataAdapter).RowUpdating -= OnRowUpdating;
                }
                else
                {
                    throw new ArgumentException("Unknown type of adapter");
                }
            }
    
            private static void OnRowUpdating(object sender, RowUpdatingEventArgs e)
            {
                PrintEventArgs(e);
            }
    
            public static int Main()
            {
                const string connectionString = "<<<< put connection string here >>>>";
                const string queryString = "SELECT * FROM Products";
    
                // create DataAdapter
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
    
                using (DbDataAdapter adapter = factory.CreateDataAdapter())
                {
                    adapter.SelectCommand = factory.CreateCommand();
                    adapter.SelectCommand.Connection = factory.CreateConnection();
                    adapter.SelectCommand.Connection.ConnectionString = connectionString;
                    adapter.SelectCommand.Connection.Open();
                    adapter.SelectCommand.CommandText = queryString;
    
                    DbCommandBuilder builder = factory.CreateCommandBuilder();
                    builder.DataAdapter = adapter;
    
                    // Create and fill DataSet (select only first 5 rows)
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, 0, 5, "Table");
    
                    // Modify DataSet
                    DataTable table = dataSet.Tables["Table"];
                    table.Rows[0][1] = "asdf";
    
                    // add handlers
                    AddRowUpdatingHandler(adapter);
    
                    // update, this operation fires two events 
                    // (RowUpdating/RowUpdated) per changed row 
                    adapter.Update(dataSet, "Table");
    
                    // remove handlers
                    RemoveRowUpdatingHandler(adapter);
    
                    // Modify DataSet again
                    table.Rows[0][1] = "qwerty";
    
                    // update again, this operation fires two events 
                    // (RowUpdating/RowUpdated) per changed row 
                    adapter.Update(dataSet, "Table");
                }
                return 0;
            }
    
            private static void PrintEventArgs(RowUpdatingEventArgs args)
            {
                Console.WriteLine("OnRowUpdating");
                Console.WriteLine("  event args: (" +
                    " command=" + args.Command +
                    " commandType=" + args.StatementType +
                    " status=" + args.Status + ")");
            }
    
            private static void PrintEventArgs(RowUpdatedEventArgs args)
            {
                Console.WriteLine("OnRowUpdated");
                Console.WriteLine("  event args: (" +
                    " command=" + args.Command +
                    " commandType=" + args.StatementType +
                    " recordsAffected=" + args.RecordsAffected +
                    " status=" + args.Status + ")");
            }
    
        }
    }
    

    Cheers,
    Jared
    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, June 11, 2009 8:20 PM
  • Here is a generic way to do it for all adapters that support RowUpdating event on their DataAdapter implementation:

    using System;
    using System.Reflection;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    
    namespace DataSamples
    {
        public class RowUpdatingEventSample
        {
            public static void ad_RowUpdating(object sender, RowUpdatingEventArgs e)
            {
                Console.WriteLine("in RowUpdating: status {0}", e.Status);
            }
    
            public static void Main()
            {
                using(SqlConnection con = new SqlConnection("Data Source=NNDEV\\KATEXPRESS;Initial Catalog=TestDB;Integrated Security=true"))
                {
                    con.Open();
                    SqlDataAdapter ad = new SqlDataAdapter("SELECT * from test", con);
                    SqlCommandBuilder builder = new SqlCommandBuilder(ad); // auto-create UPDATE/INSERT/DELETE and other commands
    
                    DataTable table = new DataTable();
                    ad.Fill(table);
    
                    RegisterForRowUpdatingEvent(ad, ad_RowUpdating);
    
                    table.Rows[0][0] = Environment.TickCount; // first column is key of type int
                    ad.Update(table);
                }
            }
    
    
            delegate void DbRowUpdatingEventHandler(object sender, RowUpdatingEventArgs e);
    
            /// <summary>
            /// this method registers RowUpdating event handler for data adapters that support this event
            /// </summary>
            static void RegisterForRowUpdatingEvent(DbDataAdapter ad, DbRowUpdatingEventHandler handler)
            {
                EventInfo rowUpdatingEvent = ad.GetType().GetEvent("RowUpdating");
                if (rowUpdatingEvent == null)
                    throw new InvalidOperationException(ad.GetType().Name + " does not support RowUpdating event");
    
                // reconstruct the delegate from the exact type required by the adapter event
                // otherwise, the event will not accept it
                Delegate d = Delegate.CreateDelegate(rowUpdatingEvent.EventHandlerType, handler.Target, handler.Method, true);
                
                rowUpdatingEvent.AddEventHandler(ad, d);
            }
        }
    }
    

    Similarly, you can create the 'event remove' version by using RemoveEventHandler method on the rowUpdatingEvent event info

    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, June 11, 2009 11:47 PM
    Moderator