CODEWITHSUNDEEP
c#ado.net
Sha Le
Sha Le

Reputation: 231

Close DataSet's underlying connection explicitly?

I am using DataSet to retrieve data from the Microsoft SQL Server. Do I need to explicitly close the connection (or the underlying SqlDataAdapter automatically closes the connection)?

I always use DataReader (with using), but first time using DataSet -- that's why wondering about best practice. Thanks in advance.

Upvotes: 5

Views: 8227

Answers (5)

abatishchev
abatishchev

Reputation: 100278

The best practice is to call Dispose() for all ADO.NET members implemented IDisposable: connection, command, adapter, table, set, reader, etc:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
    connection.Open();
    using (DataSet ds = new DataSet())
    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
    {
        adapter.Fill(ds);
    }
}

Upvotes: 1

user240141
user240141

Reputation:

Just for making things clear i am following conventional beginners way of interacting with db.

public DataSet GetData()
{
    SqlDataReader reader;
    string connstr = your conn string;
    SqlConnection conn = new SqlConnection(connstr);
    DataTable st = new DataTable();
    DataSet ds = new DataSet();
    try
    {                   
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "Your select query";
        cmd.Connection = conn;
        conn.Open();

        reader = cmd.ExecuteReader();
        dt.Load(reader);
        ds.Tables.Add(dt);
    }
    catch (Exception ex)
    {
        // your exception handling 
    }
    finally
    {
        reader.Close();
        reader.Dispose();
        conn.Close();
        conn.Dispose();
    }    
    return ds;
}

Upvotes: 0

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

A DataSet is a disconnected 'view' on the database. That is, you load the data from the database in a DataSet (actually, in a DataTable, which can be put in a DataSet), and you can close the Connection that you've used to populate the DataTable or DataSet.

You can continue to work with the data that is in the dataset. It does not require an open connection to the DB.

In fact, you should close a DB-connection as soon as you don't need any DB access soon. Connections to databases should be short-lived.

Upvotes: 4

Seattle Leonard
Seattle Leonard

Reputation: 6776

I always think it is a good idea to keep track of my connections, no matter wich way I'm connecting to a database.

You said that you always use a datareader, but now you are using a data set. I'm assuming that means you are using a DataAdapter to go with your DataSet. If that is the case, and you are using MSSQL, then the SqlDataAdapter will open and close the connection for you, but like I said, I like to keep track of this myself, especially since you may use SqlCommand.ExecuteScalar (even if you are using a DataAdapter most of the time) at some point, and the SqlCommand will not manage your connection state for you.

SqlDataAdapter doc: http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx

Upvotes: 0

Jean-Christophe Fortin
Jean-Christophe Fortin

Reputation: 748

Using statement clean up unmanaged resources before the object is garbage collected. The connection, is an unmanaged resources so it should be close even if your are with a DataSet.

Upvotes: 0

Related Questions