Database connection advice

[R Reyes]

Hi, I'm trying to modularize my database connections a little better and get
more out of my project with less code.

First check out this common dbOpen() function inside class clsDatabase. I
removed the try/catch part as it is not important for my question:

// This function opens a connection to the database.
public static SqlConnection dbOpen()
{
// Create a SqlConnection object and pass in our connection string
SqlConnection dbConn = new SqlConnection(@ "some connection string");
// Open our database connection
dbConn.Open();
}

Currently I call this function like so:
// Open our database connection
SqlConnection dbConn = new clsDatabase.dbO pen();
// do SOMETHING with the dbConn variable.
// some code that does something with the database using dbConn
variable.
// Done. Now close our database connection
clsDatabase.dbC lose(dbConn);

Now, I am stuck in an EventHandler (another part of this same page) where I
am trying to refresh a DataList with its values that looks like this:
protected void dlEmployeeDirec tory_EditComman d(object source,
DataListCommand EventArgs e)
{
// Start DataList editing mode
dlEmployeeDirec tory.EditItemIn dex = e.Item.ItemInde x;

// Refresh data
getEmployeeDire ctoryData(clsDa tabase.dbGetCon nection());
}

My question is, how can I use the database connection I was just working
with within this particular function? Do I have to create a new connection
using dbOpen and dbClose as I did in the code segment before this one? Note
that the only lines I added to the following "revised" code are dbOpen and
dbClose:
protected void dlEmployeeDirec tory_EditComman d(object source,
DataListCommand EventArgs e)
{
// Start DataList editing mode
dlEmployeeDirec tory.EditItemIn dex = e.Item.ItemInde x;
// Open our database connection
SqlConnection dbConn = new clsDatabase.dbO pen();
// Refresh data
getEmployeeDire ctoryData(clsDa tabase.dbGetCon nection());
clsDatabase.dbC lose(dbConn);
}

I'm trying to figure out a way to just open the connection once and always
be able to refer to it or pass it around instead of putting dbOpen and
dbClose in functions all over the page. I have no idea how to get the db
connection either within EventHandlers because they already come premade with
"(object source, DataListCommand EventArgs e) as the parameters to be passed
in. So I can't pass in the db connection and I don't know the code to just
"get" the last connection I was just working with.

Normaly I would just pass the database connection around from function to
function like so:

doSomething(dbC onn);
doSomethingElse (dbConn);

....but I can't even do that here because this is an EventHandler with
pregiven parameters.

Sorry, kinda new at C# still...any questions or comments are appreciated.
Thanks!

[David Browne]

"R Reyes" <RR****@discuss ions.microsoft. com> wrote in message
news:47******** *************** ***********@mic rosoft.com...

Hi, I'm trying to modularize my database connections a little better and
get
more out of my project with less code.

First check out this common dbOpen() function inside class clsDatabase. I
removed the try/catch part as it is not important for my question:

// This function opens a connection to the database.
public static SqlConnection dbOpen()
{
// Create a SqlConnection object and pass in our connection string
SqlConnection dbConn = new SqlConnection(@ "some connection
string");
// Open our database connection
dbConn.Open();
}

Ok that's good. Now anyplace you need a connection do this

using (SqlConnection con = clsDatabase.dbO pen())
{
//use the connection

}

The using block will close the connection at the end of the block.

David

[Cor Ligthert [MVP]]

R,

Depends what you do, if you use DataAdapters and Dataset, than you don't
even have to open and close your connections. The DataAdapter does that
intrensic.

(If you than do more fill action in one method, than it is a little bit
slower than with opens and closes around the fills)

I hope this helps,

Cor

[R Reyes]

Ok, I will try these out. Thanks for the quick replies you two! :)

"Cor Ligthert [MVP]" wrote:

R,

Depends what you do, if you use DataAdapters and Dataset, than you don't
even have to open and close your connections. The DataAdapter does that
intrensic.

(If you than do more fill action in one method, than it is a little bit
slower than with opens and closes around the fills)

I hope this helps,

Cor