General Database Question - Tables in Classes - Appended

[Jim Stools]

Forget the re-post I had my clock set 12 hours earlier

Hopefully this will make some sense.. I have a database that has around 50
tables - I thought about putting each table in a class and the data
connection in a class then I could manage the (tables) classes with one data
connection. The business logic would open the database class then whatever
table class is needed, then close the tables and the connection. -- I have
may
doubts whether this would work.

Another way would be to open a data connection in each class. Since
mutiple tables could be open at the same time I would have one data
connection instead of multiple data connections at the same time. If I were
to open one connection for each (table) class, lets say 30 and I had 10 hits
on the web page at the same time, (30 * 10) I could have a possible 300 data
connections (with tables) open at one time. Any rough ideas would be greatly
appreciated.

Finally, just put all the tables and connection into one class.

class1 (open & close database connection)
class2 (table1)
class3 (table 2)
class4 (table 3)
class5 (business logic)
class6 (business logic)

class5: (most tables are needed) (business logic)
-- instances class1 (open)
-- instances class2
-- instances class3
-- instances class4
-- close all classes (tables and connection)

class6: (only 2 tables are needed) (business logic)
-- instances class1 (open)
-- instances class2
-- instances class3
-- close all classes (tables and connection)

Another way:
-- business logic class
-- class2 (open data connection & table)
-- class3 (open data connection & table)
-- class3 (open data connection & table)
-- close classes

Finally way:
-- business logic class
-- class2 (open data connection & all tables)
-- close class

[Jim Stools]

Date was wrong --- Sorry!
"Jim Stools" <ji*********@yahoo.com> wrote in message
news:44*********************@news.twtelecom.net...

Forget the re-post I had my clock set 12 hours earlier

Hopefully this will make some sense.. I have a database that has around 50
tables - I thought about putting each table in a class and the data
connection in a class then I could manage the (tables) classes with one
data
connection. The business logic would open the database class then whatever
table class is needed, then close the tables and the connection. -- I have
may
doubts whether this would work.

Another way would be to open a data connection in each class. Since
mutiple tables could be open at the same time I would have one data
connection instead of multiple data connections at the same time. If I
were
to open one connection for each (table) class, lets say 30 and I had 10
hits
on the web page at the same time, (30 * 10) I could have a possible 300
data
connections (with tables) open at one time. Any rough ideas would be
greatly
appreciated.

Finally, just put all the tables and connection into one class.

class1 (open & close database connection)
class2 (table1)
class3 (table 2)
class4 (table 3)
class5 (business logic)
class6 (business logic)

class5: (most tables are needed) (business logic)
-- instances class1 (open)
-- instances class2
-- instances class3
-- instances class4
-- close all classes (tables and connection)

class6: (only 2 tables are needed) (business logic)
-- instances class1 (open)
-- instances class2
-- instances class3
-- close all classes (tables and connection)

Another way:
-- business logic class
-- class2 (open data connection & table)
-- class3 (open data connection & table)
-- class3 (open data connection & table)
-- close classes

Finally way:
-- business logic class
-- class2 (open data connection & all tables)
-- close class

[Greg Young]

Connection pooling will handle this for you ...

Cheers,

Greg Young
MVP - C#
http://geekswithblogs.net/gyoung

"Jim Stools" <ji*********@yahoo.com> wrote in message
news:44**********************@news.twtelecom.net.. .

Date was wrong --- Sorry!
"Jim Stools" <ji*********@yahoo.com> wrote in message
news:44*********************@news.twtelecom.net...

Forget the re-post I had my clock set 12 hours earlier

Hopefully this will make some sense.. I have a database that has around
50
tables - I thought about putting each table in a class and the data
connection in a class then I could manage the (tables) classes with one
data
connection. The business logic would open the database class then
whatever
table class is needed, then close the tables and the connection. -- I
have may
doubts whether this would work.

Another way would be to open a data connection in each class. Since
mutiple tables could be open at the same time I would have one data
connection instead of multiple data connections at the same time. If I
were
to open one connection for each (table) class, lets say 30 and I had 10
hits
on the web page at the same time, (30 * 10) I could have a possible 300
data
connections (with tables) open at one time. Any rough ideas would be
greatly
appreciated.

Finally, just put all the tables and connection into one class.

class1 (open & close database connection)
class2 (table1)
class3 (table 2)
class4 (table 3)
class5 (business logic)
class6 (business logic)

class5: (most tables are needed) (business logic)
-- instances class1 (open)
-- instances class2
-- instances class3
-- instances class4
-- close all classes (tables and connection)

class6: (only 2 tables are needed) (business logic)
-- instances class1 (open)
-- instances class2
-- instances class3
-- close all classes (tables and connection)

Another way:
-- business logic class
-- class2 (open data connection & table)
-- class3 (open data connection & table)
-- class3 (open data connection & table)
-- close classes

Finally way:
-- business logic class
-- class2 (open data connection & all tables)
-- close class

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,
"Jim Stools" <ji*********@yahoo.com> wrote in message
news:44*********************@news.twtelecom.net...

Hopefully this will make some sense.. I have a database that has around 50
tables - I thought about putting each table in a class
There are tools for this, see codesmith for example.
and the data
connection in a class then I could manage the (tables) classes with one
data
connection. The business logic would open the database class then whatever
table class is needed, then close the tables and the connection. -- I have
may
doubts whether this would work.
What you may do is create a class that will handle all access to the DB,
this class export methods that accept a SqlCommand (or ICommand) and execute
it in the DB.
Each class that represent a DB table create its own command and use the
DataAccess class to interact with the DB
Another way would be to open a data connection in each class.

Not a good idea IMHO , you better concentrate all the DB connectivity in one
class. this class should be stateless though. Each time a new request arrive
a connection is created & destroyed.

[Jim Stools]

Thanks for the info..

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:us**************@TK2MSFTNGP05.phx.gbl...

Hi,
"Jim Stools" <ji*********@yahoo.com> wrote in message
news:44*********************@news.twtelecom.net...

Hopefully this will make some sense.. I have a database that has around
50
tables - I thought about putting each table in a class

There are tools for this, see codesmith for example.

and the data
connection in a class then I could manage the (tables) classes with one
data
connection. The business logic would open the database class then
whatever
table class is needed, then close the tables and the connection. -- I
have may
doubts whether this would work.

What you may do is create a class that will handle all access to the DB,
this class export methods that accept a SqlCommand (or ICommand) and
execute it in the DB.
Each class that represent a DB table create its own command and use the
DataAccess class to interact with the DB

Another way would be to open a data connection in each class.

Not a good idea IMHO , you better concentrate all the DB connectivity in
one class. this class should be stateless though. Each time a new request
arrive a connection is created & destroyed.