>>And I've posted this answer about a gazillion times over the last several years. <g>
ya, ya, ya - I know. And I appreciate your patience. Believe it or not, I really am learning :)

So I dusted off my DataFactory class that I wrote the last time we talked about this. I finished it off, and I have a question. It works in that I can Select, Insert, Update and Delete records, but here's the problem:

I have a generic private method in the class called _GetDataApadter (Catch code omitted for brevity)

protected IDbDataAdapter _GetDataAdapter(string sQuery)
{
    IDbDataAdapter oAdapter = null;

    try
    {
        switch (_Provider)
        {
            // Sql Data Provider
            case ProviderType.SQL:
                oAdapter = new SqlDataAdapter(sQuery, _ConnectionString);
                SqlCommandBuilder oSqlBuilder = new SqlCommandBuilder((SqlDataAdapter)oAdapter);
                break;

            // OleDb Data Provider
            case ProviderType.OleDb:
                oAdapter = new OleDbDataAdapter(sQuery, _ConnectionString);
                OleDbCommandBuilder oOleDbBuilder = new OleDbCommandBuilder((OleDbDataAdapter)oAdapter);
                break;

            // ODBC Data Provider
            case ProviderType.ODBC:
                oAdapter = new OdbcDataAdapter(sQuery, _ConnectionString);
                OdbcCommandBuilder oOdbcBuilder = new OdbcCommandBuilder((OdbcDataAdapter)oAdapter);
                break;
        }
    }
    catch (SqlException e)
    {
    }
    return oAdapter;
}

The problem is that because I'm using the IDbDataAdapter interface, the Fill method on the adapter doesn't support a TableName value. Now I could simply work by table number, but in the case of a DS with multiple tables, I would be in trouble if a table was inserted somewhere in the tables collection.

I welcome your expert opinion on this one!

Thanks Bonnie!





>Hi Kevin,
>
>>I posted a messages a while back about this, but I can't see to find it now.
>
>And I've posted this answer about a gazillion times over the last several years. <g>
>
>Our DataAccess layer has two base classes: MyConnection and MyDataAccess. All the methods are coded to the various DataAccess Interfaces (the IDb interface objects). DataSets are used simply for transporting data back and forth. I know that there are plenty of camps who advocate Biz objects for this, but I'm a believer of Typed DataSets for transporting my data between front-end and back-end.
>
>Probably a slightly better design than what I've shown here, would be to use a Factory Pattern ... but the example below was refactored from more tightly-coupled-to-SQL code, and at the time was easier done the way I did it. I'll leave that as an exercise for the reader! ;-)
>
>The MyConnection class basically has a GetConnection() method that finds the Connection String from the app's config info
>and returns an instantiated Connection. It returns it as an interface, the IDbConnection.
>
>The MyDataAccess class, simplified, is something like this:
>
>

>public class MyDataAccess
>{
>	#region Declarations
>
>	private IDbConnection oConnection;
>	private IDbCommand oCommand;
>	private IDbDataAdapter oAdapter;
>	private IDataParameterCollection oParms;
>	public string ConnectionString = "";
>	protected string ErrorMessage = "";
>
>	#endregion
>
>	#region Constructor/Destructor
>
>	public MyDataAccess()
>	{
>		MyConnection o = new MyConnection();
>		this.oConnection = o.GetConnection();
>		this.ConnectionString = o.ConnectionString;
>
>		this.oCommand = this.GetIDbCommand();
>		this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
>		this.oCommand.Connection = this.oConnection;
>		this.oCommand.CommandType = CommandType.StoredProcedure;
>	}
>
>	#endregion
>
>	#region Get/Use IDb Interface Objects
>
>	protected IDbCommand GetIDbCommand()
>	{
>		return new SqlCommand();
>	}
>	protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
>	{
>		return new SqlDataAdapter((SqlCommand)command);
>	}
>	protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
>	{
>		return new SqlParameter(ParmName, ParmValue);
>	}
>	protected void DeriveParameters(IDbCommand command)
>	{
>		SqlCommandBuilder.DeriveParameters((SqlCommand)command);
>	}
>	#endregion
>
>	// Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.
>	// It's these various methods that get used in the classes that are sub-classed from this "base" class.
>	// Here's just a few of them:
>
>	protected void ClearParameters()
>	{
>		this.oCommand.Parameters.Clear();
>		this.ErrorMessage = "";
>	}
>
>	protected void AddParms(string ParmName, object ParmValue)
>	{
>		try
>		{
>			if (ParmName.StartsWith(this.AtSign) == false)
>				ParmName = this.AtSign + ParmName;
>
>			if (ParmValue != DBNull.Value)
>				this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
>		}
>		catch (Exception ex)
>		{
>			this.ErrorMessage += ex.Message;
>		}
>	}
>	protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
>	{
>		this.AddParms(ParmName, ParmValue);
>		this.SetParmDirection(ParmName, direction);
>	}
>	protected bool FillData(DataSet ds, string StoredProcName)
>	{
>		try
>		{
>			this.oCommand.CommandText = StoredProcName;
>			this.oAdapter.Fill(ds);
>		}
>		catch (Exception ex)
>		{
>			this.ErrorMessage += ex.Message;
>			this.CloseConnection();
>			return false;
>		}
>		return true;
>	}
>
>}
>

>
>In this way, your DataAccess sub-classes are totally "disassociated" from knowing what your back-end data is (could be SqlServer, as the above class is, but if you also have an Oracle customer, you have another base class exactly the same as the above class, but it uses Oracle-specific stuff instead and you use the appropriate DLL depending on your which database your customer has ... however, you're still programming to the interface (the IDB Interface objects), so your sub-classes, which are in different projects (and thus different DLLs) don't care and don't need to be changed.
>
>So, an example of a sub-class might be this:
>
>

>public class PersonsAccess : MyDataAccess
>{
>	#region Public Get Methods
>
>	public DataSet GetPersonsOnReport(ref string Message, string ReportNumber, string Supplement)
>	{
>		PersonDataSet ds = new PersonDataSet();
>
>		this.ClearParameters();
>		this.AddParms("ReportNumber", ReportNumber);
>		this.AddParms("Supplement",   Supplement);
>		this.FillData(ds, "csp_Persons_Get");
>
>		return ds;
>	}
>
>	// more methods
>
>	#endregion
>}
>

>
>
>~~Bonnie
>
>
>
>
>>
>>I want to create a generic data access class. I may not know which provider I'm going to use. Moreover, I will probably want to specify which type of database I'm targeting, such as 'MSSQL', 'MYSQL', 'MSACCESS' or even 'MSVFP'.
>>
>>What's involved in doing this?
>>
>>Thanks