lobos
asked on
use of connections to database
I have a page where I am doing 2 seperate queries to an access database. My question is should I close the connection after the first query and the re-open it.....or do I just leave it open, then close at the end.
What is the best and efficient way to do this?
// Connect to the Access database using a query to get all the data from the table.
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM table1);
// Create and fill a DataSet.
DataSet ds = new DataSet();
myAdapter.Fill(ds);
connection.Close(); ////////////////////the magic line, should I have this here or not?
// Bind MyRepeater to the DataSet.
MyRepeater.DataSource = ds;
MyRepeater.DataBind();
//Use a variable to hold the SQL statement.
string selectString = "SELECT * FROM [table2]";
//Open the connection.
connection.Open();
//Create an OleDbCommand object.
OleDbCommand cmd = new OleDbCommand(selectString, connection );
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
this.field1.Text = reader["field1"].ToString( );
this.field2.Text = reader["field2"].ToString( );
}
//Close the reader and the related connection.
reader.Close();
connection.Close();
I look forward in hearing thoughts on this.
What is the best and efficient way to do this?
// Connect to the Access database using a query to get all the data from the table.
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM table1);
// Create and fill a DataSet.
DataSet ds = new DataSet();
myAdapter.Fill(ds);
connection.Close(); ////////////////////the magic line, should I have this here or not?
// Bind MyRepeater to the DataSet.
MyRepeater.DataSource = ds;
MyRepeater.DataBind();
//Use a variable to hold the SQL statement.
string selectString = "SELECT * FROM [table2]";
//Open the connection.
connection.Open();
//Create an OleDbCommand object.
OleDbCommand cmd = new OleDbCommand(selectString,
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
this.field1.Text = reader["field1"].ToString(
this.field2.Text = reader["field2"].ToString(
}
//Close the reader and the related connection.
reader.Close();
connection.Close();
I look forward in hearing thoughts on this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
scope*
Erm, where do you open it in the first place ? If you are using a DataAdapter then the DataAdapter itself takes care of opening and closing the connection when required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ozymandias has answered the question on when a connection should be kept open and when to close it, and I have proposed the "best and efficient way" to do it
happy for points to go to redpipe,