Is a second class factory the only way to get an IDbDataAdapter reference?

[bulwark_jrm]

Hi,

I try to make my code as technology independent as possible via
interfaces.

For example, for my data access, the only code specifically referring
to the System.Data.SqlClient namespace is a classfactory which returns
an IDbConnection reference. From there, I can do a CreateCommand() to
get the IDbCommand reference, and ExecuteReader() to get an
IDataReader.

But ... I'm having difficulty finding a way to get an IDbDataAdapter
referernce.

Is the only way to do this to create another classfactory method to
return an IDbDataAdapter referernce???

FYI: VS.NET2003 right now, access to VS.NET2005 as well.

Thanks in advance.

Regards,
John

PS-Here is a bit of code to show specifically what I'm talking about.
---------------------------------
using System;
using System.Data;
using System.Data.Common;

namespace DbDataAdapterTest
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
try
{
string cnstring =
"Server=web-server2;Database=FreshStartTMSWebService;Trusted_Connection=True;";
string sql = "select * from tms_ws_o.vw_CustomerDocuments
where [invoice number]='AT110253994'";

// get connection interface based on the database
IDbConnection cn = DBUtilityClassFactory.GetConnection( cnstring);

// get command
IDbCommand cmd = cn.CreateCommand();
cmd.CommandText = sql;

// I want to do something like
// IDbDataAdapter da = cn.CreateDataAdapter();
// but it doesn't seem to exist
//
// is the only way to get a data adapter to have a second
classfactory?
IDbDataAdapter da = DBUtilityClassFactory.GetDataAdapter( cmd);
DataSet ds = new DataSet();
da.Fill( ds);

// do something interesting
ds.WriteXml( @"c:\TestRecordI.xml");
Console.Out.WriteLine( "Dataset written to file.");
}
catch( Exception ex)
{
Console.Out.WriteLine( "!! Exception thrown : {0}", ex.Message);
}
}
}

class DBUtilityClassFactory
{
public static IDbConnection GetConnection( string connectionString)
{
// for now only access to SQLServer
return new System.Data.SqlClient.SqlConnection( connectionString);
}

public static IDbDataAdapter GetDataAdapter( IDbCommand cmd)
{
// for now only access to SQLServer
return new System.Data.SqlClient.SqlDataAdapter(
(System.Data.SqlClient.SqlCommand) cmd);
}
}
}

---------------------------------

 

[David Browne]

Hi,

I try to make my code as technology independent as possible via
interfaces.

For example, for my data access, the only code specifically referring
to the System.Data.SqlClient namespace is a classfactory which returns
an IDbConnection reference. From there, I can do a CreateCommand() to
get the IDbCommand reference, and ExecuteReader() to get an
IDataReader.

But ... I'm having difficulty finding a way to get an IDbDataAdapter
referernce.

Is the only way to do this to create another classfactory method to
return an IDbDataAdapter referernce???
. . .

In .NET 1.1 you had to write your own factory methods for all this stuff.
In 2.0 you need fewer of them. You may want more convenience methods too,
like a

IDbCommand CreateCommand(IDbConnection con, string SQL)

or

IDataReader ExecuteReader(IDbConnection con, string SQL)

And check this out for managing connection lifetime and automatically
passing a DbConnection down the call stack:

A ConnectionScope class
http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

Using this you can manage connection lifetime at the same scope you manage
transaction scope, and every method called for the duration of the
transaction can have access to the same connection, without having to pass
the connection or transaction objects around.

David