Database connection advice

[Guest]

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.dbOpen();
// do SOMETHING with the dbConn variable.
// some code that does something with the database using dbConn
variable.
// Done. Now close our database connection
clsDatabase.dbClose(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 dlEmployeeDirectory_EditCommand(object source,
DataListCommandEventArgs e)
{
// Start DataList editing mode
dlEmployeeDirectory.EditItemIndex = e.Item.ItemIndex;

// Refresh data
getEmployeeDirectoryData(clsDatabase.dbGetConnection());
}

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 dlEmployeeDirectory_EditCommand(object source,
DataListCommandEventArgs e)
{
// Start DataList editing mode
dlEmployeeDirectory.EditItemIndex = e.Item.ItemIndex;
// Open our database connection
SqlConnection dbConn = new clsDatabase.dbOpen();
// Refresh data
getEmployeeDirectoryData(clsDatabase.dbGetConnection());
clsDatabase.dbClose(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, DataListCommandEventArgs 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(dbConn);
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]

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.dbOpen())
{
//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

 

[Guest]

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