Thanks. This is vanilla dotnet - no framework. I started using c# way back when it was new and then focused purely on VFP again for work. Now I'm trying to relearn what I once knew :o) What I did in the past though with C# didn't include much data except for play. It was purely a communications module so I'm basically starting (again) from scratch. I took Kevin's class (dotnet for vfp developers) a couple of months ago and then of course got so busy with VFP work required stuff that I didn't get to use it yet. Now I will though so I'm starting with some simple data access stuff (display sqlserver data on a form) but I'd rather separate the process in a manner that is at least close to our current logical separation and keep the data access separate from the business logic, et al.

Oh, I forgot to mention, I'm using VS2005 for now but very soon I will be using 2008.



>Hey Tracy,
>
>Well, I'll be around this weekend, off-and-on, if you need help (unless, of course, it's MM-type questions ... I know nothing <g>).
>
>Most importantly though ... HAVE FUN!! =0)
>
>~~Bonnie
>
>
>
>
>>Hey Bonnie,
>>
>>I'm going to be playing around in C# this weekend doing a test form to access data in sqlserver and thought I'd give your ideas in your example below a try. Thanks for posting this! I haven't looked at C# since Kevin's training and before that it was about a year and a half ago! Time to put my learning hat on... :o)
>>
>>Tracy
>>
>>
>>>John,
>>>
>>>This isn't exactly what you asked for, but here goes ...
>>>
>>>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.
>>>
>>>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 = "";
>>>
>>>	#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.
>>>

>>>
>>>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 am trying to get a handle on a good design for a data access layer. This topic has come up in the past and has really been too broad of a question to be easily answered in a single post. So, what I would like to do is break the problem down into smaller parts that should be able to be answered quickly and succinctly.
>>>>
>>>>The first question I have is what classes/objects need to be created for a decent data access layer. I realize that I will probably get a different answer from each person that responds, that's OK.
>>>>
>>>>In it's most simplistic form I see data access like this:
>>>>
>>>>

>>>>DataSource
>>>>  Connection
>>>>    DataSet
>>>>      DataTable
>>>>

>>>>
>>>>So that gives us at least 3 basic objects:
>>>>
>>>>

>>>>Connection
>>>>DataSet
>>>>DataTable
>>>>

>>>>
>>>>So... the question is:
>>>>
>>>>What other objects do you see as being required for data access???
>>>>
>>>>I don't need them explained at this point, just a list with descriptive names would be good. Something Like:
>>>>
>>>>

>>>>SQLWrapper
>>>>DBProvider
>>>>DataAccessLayer
>>>>

>>>>
>>>>I'm sure I will have questions on what you list off, but that can wait for now.
>>>>
>>>>If we come up with something good, maybe I can put it all together and post it as single item in the end.