Solved

use of connections to database

Posted on 2006-11-08
7
166 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

939 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

11 Experts available now in Live!

Get 1:1 Help Now