c# DataSet close connection explicitly?

Question

Hi:

Am using DataSet to retrieve data from the MS 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.

Answer 1

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.

Answer 2

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;
    }

Answer 3

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