Where to put DataAdapters

[Quercus]

I'm relatively new to database programming and I'm working on a WinForms application that will manipulate data in a single database. The database has about 18 different tables, and the application will have many different forms, each dealing with different aspects of the data.

 

My question is how to deal with the connections and data adapters, and possibly even the datasets. Should I have the main application establish the connection and data adapters for all my tables, and then let each form access these objects or should I let each form handle its own connection / adapter for only the tables it needs?

 

Thanks

[Robby]

This is my point of view http://www.xtremedotnettalk.com/showthread.php?threadid=78714

[Quercus]

I like the class idea. But part of my question still remains: would I create one instance of the class with a global scope that would handle all DB access for all forms, or would each form create a new instance of the class.

 

Presumably, each class would have its own connection, multiple data adapters, datasets, etc. If I create a new instance for each form, then I will have a lot of connections going. Wouldn't this add to overhead and slow things down? I would think that a single uber-object, accessbile to my whole application, would be more efficient. Please correct me if I'm wrong.

[Robby]

The business class would inherit the data class, all the Sql statements reside in the business class. Let's say you have a method in the data layer that returns a dataset, you open a connection/dataAdapter then fill the dataset close the connection, return the dataset to the business layer which in turn returns the dataset to the original caller (let's say a WinForm).

 

Also, if you have multiple connection strings, let's say one for dev, one for production and another for unit testing, you can create a public enum for each environment, so that the business class can choose (in the myBase constructor) which connection string to use. Since this enum selection would take place in one place only, saving you search/replace in your code when it's time to deploy.

[Quercus]

I'm sorry, Robby, but you've lost me. At first, I thought you were talking about creating just one class. Now it seems like you're talking about several. What's the difference between the data class and the business class? Where are you suggesting the connections and data adapters go? Back to my original question, if I have one database that is being used from several different locations in an application, do I have just one connection to the DB that is shared throughout my program, or should I create - instantiate - a new connection every time I need it. I appreciate your help but please back up a step. Thanks.

[Robby]

Data class will return Datasets, Scalars, ExecuteNonQueries, etc.... (This class does not know about the database, tables nor its' contents) It is generic enough to be ported over to any application, Web or Desktop, your PC or your friends' PC.

 

Business class will contain Sql statements, calculations and logic specific to your project. This class will Inherit the Data class.

 

 

The DataAdapters, connections are all in the Data class, they are created and destroyed as needed, nothing is global in my scenario.

[Quercus]

Okay, so if I understand your scenario correctly, you're talking about something like this:

 

There is an abstract data class that would contain connections, data adapters, command objects etc that provide the basic machinary for connecting to the database. A set of properties to hold settings like what DB to connect to, connection strings, SQL statements, etc., that will all be set at run time by consumers of this class.

 

I would then create a couple of additional business classes that would inherit the data class, and would fine-tune the operations of the data class as appropriate for my project. For example, a set of different methods, each of which returns a different dataset representing a different table in the underlying DB.

 

I would then instantiate a boat load (that's a technical term :D) of these business object whereever I might need them. A specific dataset might be retrieved by something like:

 

// Inside of a WinForms somewhere...

MyBusinessClass mbc = new MyBusinessClass();
// This will create a new instance of my business class

DataSet authorTable = mbc.GetAuthors();
// Inside the GetAuthors method, a SQL statement and
// connection string are built and passed to a GetDataset
// method of the data class, which will create a connection
// and data adapter, retrieve the data set and pass it
// along. The connection and data adapter are destroyed
// and the dataset is returned here.

Is that about right?

[Robby]

You hit the nail on the head.

[Rick_Fla]

I love this forum, don't even have to ask a question but still gain the answer. Thanks for the explanation on the data and business classes, I ahve been trying to grab a hold of this for some time and this post did the job.

[bri189a]

Robby, interestings..I'm on those lines with my way, but not quite the same.... my DataClass is always just the connection object and the basics. My buisness class has the data adapters rather than the data class. I always have more to learn, so I'm listening to what I'm doing wrong. Since my data adapters have differant parameters for each table it connects to, the stored procedure is differant, I've never seen much of point to put it in a class... DataClass.AddAdapter(string AdapterCommandText, Parameter [] parameters)... I mean I just don't see where I'd be saving the time. For instance I got something I'm working on now that uses15 differant tables, all related too each other at some point (very carefully trimed down to exclude unessary, unneed and duplicated data), and I'd love to make this simpler... I find it terribly bloated...15 differant tables equates to 60 differant stored procedures, one for each select, add, update, and delete query of each table, plus a few specialized queries for those JOINS we all love. Each of these queries I have to add the parameters for, anywhere from 6 to 21 of them, then because I find the results more expected I just don't put the parameter name, but also the type, length, and source column. My buisness class so far is 1200 lines... I'm expecting it to be 2400 lines when I'm done (I'm halfway through)... if there was a way I could shorten that up, I'd love to know how, but with so many tables to work with... how can you really do that?

[Robby]

Of course there are times when you need to put (let's say)DataRelations in the business class, something like this couldn't be in a black box.