• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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.
0
lobos
Asked:
lobos
  • 3
  • 2
2 Solutions
 
ozymandiasCommented:
If this is a web page leave it open.
It all happens in the scape of a single http request so there's no point closing and reopening the connection.
0
 
ozymandiasCommented:
scope*
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
redpipeCommented:
From MSDN: "To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block."

using (OleDbConnection cn = new OleDbConnection(connectionString))
{
    using (OleDbCommand cm = new OleDbCommand(commandString, cn))
    {
        cn.Open();
        cm.ExecuteNonQuery();
    }
}
0
 
redpipeCommented:
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
0
 
ozymandiasCommented:
happy for points to go to redpipe,
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now