Best Practices for implementing db conn's from asp.net business ob

[Guest]

I am in a slight predicament trying to determine the most efficient and
effective way to connect/disconnect from a database within a business object
(c# dll). I'm also keeping in mind the concept of connecting late and
disconnecting early.

Background:
- multi-tier application (code-behind uses properties and methods of the
business object, the business object handles the data layer)

For instance (in an ASPX code-behind file):

MyClass MyObject = new MyClass();
MyObject.Property1 = x;
MyObject.DoSomething();

When should the MyClass class connect and disconnect from the database?
Right now I am looking at 3 avenues of approach:
1. Connect in the constructor, then disconnect in Dispose()
2. Connect and Disconnect from within DoSomething()
3. Implement additional methods called Connect() and Disconnect()

I like number 2 except that, what happens when I get into a looping
situation where I repetitively call DoSomething(). Number 1 is good, except
I have to explicitly call Dispose() and of course implement IDisposable. I
really hate number 3 because it seem like unnecessary steps performed by the
consumer of the business object.

I'm looking for answers in the realms of what the best answer is from a
solution design perspective... keeping in mind: performance, scalability,
maintainability, and all that other fun stuff.

 

[zq]

Hi, Nate

First method could be problematic because of the database's per seat
licensing (if any) - if you occupy too many connections at once, next
"unlicensed" connection will be waiting inside an endless loop until a
license gets available.

Second method would be problematic if you call DoSomething too often in a
short period of time - if you have a for or a while loop and inside you call
DoSomething you'll have a big overhead of connecting-disconnecting for no
reason.

Third method gives you the most control.

 

[Michael]

I'd actually go for number 2. The reason being that your probably using
connection pooling and as the connection is being created on the server
the connection string will be continually the same so the connection
you previously created will be reused. The creation of the connection
is the expensive operation. This then provides you with your scenario
where the connection is open for the minimum amount of time which is
good for scalability.

 

[Guest]

Hi Nate,
I would not explicitly worry about when to connect/disconnect from the DB
in your public interface of your object, that is really a secondary part
depending on the behaviour of your object. You are going to need to be able
to accomplish the following scenarios with your objects:

1. Create a new object (db connection may be required or not)
2. Fetch an existing object from the DB
3. Update an object (this may involve insertion / update as well as delete)

Scenario 1
For scenario 1 you probably won't need to connect to the database at all
(unless you need to fetch a unique id from the DB or something else) so you
can just say:
MyObject x = new MyObject();

there would be no database involvement at this point, there is no need to
open a connection to the DB, when you want to update the contents of the
object into the DB then you will open a connection.


Scenario 2
If you want to create an object that represents data in the DB then you will
connect to the DB fetch the data and put it in the object and disconnect,
again there is no need to keep a connection open in the object, opening and
closing connections is fast since they will be pooled anyway, only the first
open of a connection will take a performance hit. To fetch data into your
object you could say:

MyObject x = MyObject.Fetch(1234);

The static Fetch method knows how to connect to the db and populate an
instance of MyObject and return it to the user.


Scenario 3
Once you have updated your objects data then you will call an updte method
i.e.
MyObject x = new MyObject();
.... do some stuff
x.Update();

The update call will connect and then disconnect from the DB, calling the
appropriate insert,update or delete method depending if your object isDirty,
isNew or isDeleted. Your update method should also check to see if the
object isDirty, it would only save if the object isDirty in order to increase
performance, no point saving if you don't have to.


For your scenario 2 where you have the DoSomething method being called
multiple times and it is affecting the DB, I would say you want to avoid this
case, possibly by moving the logic you are using in the DB into the object so
that it is not necessary to update directly inside the method, hopefully your
business objects have enough knowledge that they can be used without having
to hit the db until an update time.

Hope that helps.
Mark Dawson
http://www.markdawson.org

 

[Michael S]

I am in a slight predicament trying to determine the most efficient and
effective way to connect/disconnect from a database within a business
object
(c# dll). I'm also keeping in mind the concept of connecting late and
disconnecting early.

Background:
- multi-tier application (code-behind uses properties and methods of the
business object, the business object handles the data layer)

For instance (in an ASPX code-behind file):

MyClass MyObject = new MyClass();
MyObject.Property1 = x;
MyObject.DoSomething();

When should the MyClass class connect and disconnect from the database?
Right now I am looking at 3 avenues of approach:
1. Connect in the constructor, then disconnect in Dispose()
2. Connect and Disconnect from within DoSomething()
3. Implement additional methods called Connect() and Disconnect()

I like number 2 except that, what happens when I get into a looping
situation where I repetitively call DoSomething(). Number 1 is good,
except
I have to explicitly call Dispose() and of course implement IDisposable.
I
really hate number 3 because it seem like unnecessary steps performed by
the
consumer of the business object.

I'm looking for answers in the realms of what the best answer is from a
solution design perspective... keeping in mind: performance, scalability,
maintainability, and all that other fun stuff.

My 2 cent answer is: None of the above.

As OLEDB takes care of connection pooling for you you should not keep a
connection alive in code. Connect, run some sql and then disconnect.

I typically add a class called DataConnection with one single static method
..Create() that looks kinda like this:

public static SqlConnnection Create()
{
SqlConnection s = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
s.open();
return s;
}

Then in my code that runs sql i fetch a connection with the using
statement...

using (SqlConnection conn = DataConnection.Create())
{
SqlCommand blaha...
MyDataSet fubar...
...
....
} //At this point the connection goes back to the pool and other objects
will be collected by the GC at a later time. .Dispose() is a great thing
indeed.

The using keyword is a great language feature in C#. Use it!

Happy Coding
- Michael S

 

[Guest]

Nate,
In an ASP.NET application, best practices database access policy is to open
the connection just before you are about to use it, and close the connection
immediately after you are finished. There are exceptions to this
best-practices methodology, but they are rare.
Connection pooling will take care of the rest, provided your app uses the
same connection string.
Peter

 

[Guest]

Looks like the consensus was #2,... Thank you all for replying.