I asked the following question in another forum, and got a response that I thought was good, but still need some guidance.

My Question
-------------------------------------------------------
I'm new to C# and to Object Oriented programming. I have some experience using ASP and limited experience with VBA and VB 6.0.

In an effort to learn I'm toying around with building an Iventory Management application. For the purposes of this question, lets say I have two classes, an Item Class and an Inventory Class, each which will be used to manupulate data in an Access database.

What is the best way to structure the calls to the database for Inserts, updates and deletions (where should they physically be within my class structure)?

Will all my data access be contained in one class, or should they reside in the other classes (Item and Inventory, etc.). Any help or guidance or examples would be apprecieted.
-------------------------------------------------------------------

The response:

************************************************** ******************
There are as many different ways to do what your asking as there are programmers...

But here is how I do it...

I have a web project in a solution that consumes object defined in a Class Library project. In my class library project I have 2 folders. One called BLL, for Business Logic Layer. And one called DAL, for Data Access Layer.

In my BLL folder, I would put the 2 classes you talked about, Item and Inventory.

In the DAL folder I would have 2 additional classes. One called DataSource, and one called Configuration. Each of these contains static member functions and properties to facilitate talking to the database, and getting configuration information.

I use the Configuration class to get things like connection strings, email servers, administrator email address, and other configuration information so that it isn't compiled in the application. You can store this kind of thing in the web.config, or the app.exe.config file then I use the Configuration class to go get them.

The DataSource class in my app would have several static properties.

One called ItemAdapter, initialized with this code...

public static SqlDataAdapter ItemAdapter{
get{
SqlDataAdapter Adapter = new SqlDataAdapter(
"SELECT * FROM MyItemTable",
new SqlConnection(Configuration.DatabaseConnectionStri ng));

SqlCommandBuilder Builder = new SqlCommandBuilder(Adapter);
Adapter.InsertCommand = Builder.GetInsertCommand();
Adapter.UpdateCommand = Builder.GetUpdateCommand();
Adapter.DeleteCommand = Builder.GetDeleteCommand();

return Adapter;
}
}

Then, my DataSource class would have the following property

public static DataSet ItemData
{
get{
DataSet AlloSet = new DataSet("ItemData");

DataSource.ItemAdapter.Fill(AlloSet, "ItemData");

return AlloSet;
}
}

Then you create similar properties for your Inventory class.

These give you the basis of the most important ADO.NET objects, and a decent way to access them.

Then, my Item and Inventory classes would each have a Constructor that accepts an ID and would go talk to the DataSource class to get a DataRow object and populate itself. They also would have Insert, Update, and Delete Functions that would also go talk to the DataSource class and perform those pieces of functionality.

You could then implement caching in ASP.NET or use Global variables in Windows forms to minimize the number of database calls, and the number of times you have to construct your ADO.NET objects.

Also, ADO DataSets are not the fastest way of getting data out of a database. If performance is a big deal, then you might want to use SqlDataReader's. They are the fastest way of getting data out of a database. Like this...

public static SqlDataReader ItemReader
{
get{
return DataSource.ItemAdapter.SelectCommand.ExecuteReader ();
}
}

************************************************** *******************
I'm trying to use the example above and add a neew record to the Item table described in my original question. I've done some fooling around, and some looking but the examples I've seen don't fit into this mold..

Can anyone PLEASE point me int the right direction?

Thanks

cmb
AKA Beerman...The great American Homebrewer