none
How to construct a DataAdapter? RRS feed

  • Question

  • i have a DbConnection (you don't know what it is connected to, it could be Excel, Access, XML, DB2, Oracle, FoxPro, text file, sql server 6.5, etc).

     

     

    When i wanted to create an appropriate DbCommand for this DbConnection i call:

    DbCommand command = connection.CreateCommand();

     

     

    Now i need to create a DataAdapter, but there is no connection.CreateAdapter() method. The constructor is protected, DbDataAdapter is abstract, and the remaining descendants are database engine specific.

     

    Lutz's Reflector says the only places it's exposed is the protected constructor, and the protected CloneInternals() method.

     

    So how do i construct a DataAdapter that matches my connection?

     

    Tuesday, August 19, 2008 2:04 PM

Answers

  •  Daniel Rieck wrote:

    Use the CreateDataAdpater method of your DbProviderFactory.

     

    The DbConnection.DbProviderFactory property is protected - would have been nice.

     

    The best i was able to come up with was to check if the connection object is a known type, and construct a data adapter to match it:

     

     

    Code Snippet

    public static DbDataReader ExecuteReaderClient(DbConnection conn, String cmdText)

    {

       DbDataAdapter adapter; //we can't construct an adapter directly

       //So let's run around the block 3 times, before potentially crashing

     

       if (conn is System.Data.Odbc.OdbcConnection)

       {

          adapter = new System.Data.Odbc.OdbcDataAdapter();

       }

       else if (conn is System.Data.OleDb.OleDbConnection)

       {

          adapter = new System.Data.OleDb.OleDbDataAdapter();

       }

       else if (conn is System.Data.SqlClient.SqlConnection)

       {

          adapter = new System.Data.SqlClient.SqlDataAdapter();

       }

       else

       {

          throw new Exception("Can't find matching DataAdapter for DbConnection");

       }

     

       DbCommand command = conn.CreateCommand();

       command.CommandText = cmdText;

     

       adapter.SelectCommand = command;

       DataSet dataset = new DataSet();

       try

       {

          adapter.Fill(dataset);

     

          return dataset.CreateDataReader();

       }

       finally

       {

          dataset.Dispose();

       }

    }

     

     

     

     

     

    Tuesday, August 19, 2008 2:50 PM

All replies

  • Use the CreateDataAdpater method of your DbProviderFactory.

    Tuesday, August 19, 2008 2:11 PM
  •  Daniel Rieck wrote:

    Use the CreateDataAdpater method of your DbProviderFactory.

     

    The DbConnection.DbProviderFactory property is protected - would have been nice.

     

    The best i was able to come up with was to check if the connection object is a known type, and construct a data adapter to match it:

     

     

    Code Snippet

    public static DbDataReader ExecuteReaderClient(DbConnection conn, String cmdText)

    {

       DbDataAdapter adapter; //we can't construct an adapter directly

       //So let's run around the block 3 times, before potentially crashing

     

       if (conn is System.Data.Odbc.OdbcConnection)

       {

          adapter = new System.Data.Odbc.OdbcDataAdapter();

       }

       else if (conn is System.Data.OleDb.OleDbConnection)

       {

          adapter = new System.Data.OleDb.OleDbDataAdapter();

       }

       else if (conn is System.Data.SqlClient.SqlConnection)

       {

          adapter = new System.Data.SqlClient.SqlDataAdapter();

       }

       else

       {

          throw new Exception("Can't find matching DataAdapter for DbConnection");

       }

     

       DbCommand command = conn.CreateCommand();

       command.CommandText = cmdText;

     

       adapter.SelectCommand = command;

       DataSet dataset = new DataSet();

       try

       {

          adapter.Fill(dataset);

     

          return dataset.CreateDataReader();

       }

       finally

       {

          dataset.Dispose();

       }

    }

     

     

     

     

     

    Tuesday, August 19, 2008 2:50 PM
  • > The DbConnection.DbProviderFactory property is protected - would have been nice.

     

    But where does your DbConnection come from? The regular way when working with the abstract Db* classes is DbProviderFactories -> DbProviderFactory -> DbConnection. So, at one point you should have an instance of the DbProviderFactory, and you can pass that as an argument to your method.

     

    By the way, why do you fill a DataSet first? You can execute a reader directly from the select command: http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.executereader.aspx

    Tuesday, August 19, 2008 3:59 PM
  •  Daniel Rieck wrote:

    So, at one point you should have an instance of the DbProviderFactory, and you can pass that as an argument to your method.

     

    The method signature you (and i) are presented with gives you (and i) a DbConnection. You (and i) can make no further assumptions about the available of whatever in whatever.

     

    By the way, why do you fill a DataSet first? You can execute a reader directly from the select command.

     

    Because that uses a server-side cursor, i require a client-side cursor.

    Wednesday, August 20, 2008 1:43 PM
  • I realize this is very late in the day, but I had the same issue and found the following solution:

    Assume cnn is a DbConnection then

                    Assembly asm = Assembly.GetAssembly(type: cnn.GetType());
                    Type typDataAdapter = asm.GetTypes().Where(t => t.Namespace != null && t.Namespace.Equals(cnn.GetType().Namespace) && t.Name.Contains("DataAdapter")).Single();
                    DbDataAdapter  da = (DbDataAdapter)asm.CreateInstance(typeName: typDataAdapter.FullName);


    John Carroll

    Wednesday, April 17, 2013 2:43 PM