Solved

use of connections to database

Posted on 2006-11-08
7
165 Views
Last Modified: 2010-04-16
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
Comment
Question by:lobos
  • 3
  • 2
7 Comments
 
LVL 15

Assisted Solution

by:ozymandias
ozymandias earned 25 total points
ID: 17900980
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
 
LVL 15

Expert Comment

by:ozymandias
ID: 17900983
scope*
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17901212
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Accepted Solution

by:
redpipe earned 25 total points
ID: 17905979
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
 
LVL 8

Expert Comment

by:redpipe
ID: 18084235
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
 
LVL 15

Expert Comment

by:ozymandias
ID: 18084369
happy for points to go to redpipe,
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now