Class structure

[Guest]

I am writing a class which connects to a database, then runs a stored
procedure and returns the results in a dataset.
Now I am quiet new to C#, and I was wondering is it better to create my
database connection within a constructor (of which I have 3) or create the
connection out side the constructors in a separate function which I call in
each constructor.

 

[Drebin]

Do a search on MSDN for "Data Application Blocks" - they created a class
that wraps every way you'd make a data call (T-SQL, stored proc, stored proc
with params, etc) and returns datasets or datareaders.. I started with this,
and tweaked it according to our needs here - but it's a GREAT place to
start.

 

[Nicholas Paldino [.NET/C# MVP]]

MicroMoth,

You should be creating your connections as you need them, and tearing
them down as quickly as possible. If within the course of a method call
(generally), you are going to perform a number of database operations, then
I would say to hold onto the connection in the context of the method call.
Also, I would use the using statement to make sure that they are disposed of
properly:

// Use a database connection.
using (SqlConnection conn = new SqlConnection("connection string"))
{
// Perform your operations.
}

Don't worry about the overhead involved in opening/closing the
connections. The provider implements connection pooling, which will take
care of that.

Hope this helps.

 

[Guest]

I use a MySQL library in my C# Projects at work, and the best way I've found
to do what you're asking is to create an object that maintains a persistent
availability to the database (that is, a property that has all of the
properties and settings specified) and make it where it is accessible by any
method within the class. You should only open this connection when you need
it, write/read from it, then close it within a finally{} block of a method.

MicroMoth,

You should be creating your connections as you need them, and tearing
them down as quickly as possible. If within the course of a method call
(generally), you are going to perform a number of database operations, then
I would say to hold onto the connection in the context of the method call.
Also, I would use the using statement to make sure that they are disposed of
properly:

// Use a database connection.
using (SqlConnection conn = new SqlConnection("connection string"))
{
// Perform your operations.
}

Don't worry about the overhead involved in opening/closing the
connections. The provider implements connection pooling, which will take
care of that.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am writing a class which connects to a database, then runs a stored
procedure and returns the results in a dataset.
Now I am quiet new to C#, and I was wondering is it better to create my
database connection within a constructor (of which I have 3) or create the
connection out side the constructors in a separate function which I call
in
each constructor.

 

[Drebin]

Although some may argue that you now have to maintain "state" of that
object, you would need to check to see if it has the connection information,
and see if it's a valid connection, etc.. just seems more straight-forward
and predictable to build-up and tear-down a connection whenever it's needed?
Just a thought..

I use a MySQL library in my C# Projects at work, and the best way I've found
to do what you're asking is to create an object that maintains a persistent
availability to the database (that is, a property that has all of the
properties and settings specified) and make it where it is accessible by any
method within the class. You should only open this connection when you need
it, write/read from it, then close it within a finally{} block of a method.

MicroMoth,

You should be creating your connections as you need them, and tearing
them down as quickly as possible. If within the course of a method call
(generally), you are going to perform a number of database operations, then
I would say to hold onto the connection in the context of the method call.
Also, I would use the using statement to make sure that they are disposed of
properly:

// Use a database connection.
using (SqlConnection conn = new SqlConnection("connection string"))
{
// Perform your operations.
}

Don't worry about the overhead involved in opening/closing the
connections. The provider implements connection pooling, which will take
care of that.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am writing a class which connects to a database, then runs a stored
procedure and returns the results in a dataset.
Now I am quiet new to C#, and I was wondering is it better to create my
database connection within a constructor (of which I have 3) or create the
connection out side the constructors in a separate function which I call
in
each constructor.