ADO.net - what are the constaints on database connectivity for use of DataSet and DataAdapter?

Question

Hi,

Re ADO.net, I get the concept generally of DataSet and DataAdapter. What's not clear if I may ask is:

Q1 - What constraints are there on whether the database connection need to remain open or not? Can I close the connection but still work with DataSet? If so what needs to be in place to perform an update? Does the DataAdapter need to retain an active database connection?

Q2 - More generally if I wanted to, during the course of a user using a WinForms application was to have the user:

1. Get initial data available in database (e.g. readin DataSet using DataAdapter), and then at some point of time later
2. Add a new row to the database, and have it appear in the actual database (ie saved)
3. Add another record (but just keep in memory/dataset)
4. Some time later then SAVE the new row added from [3]

What would be the DataSet / DataAdapter / Connection things that one would need to do here? Would you have the database connection closed in-between all steps under the guidelines of minimizing keeping the database open?

Thanks

Answer 1

Q1 no you do not need to keep the database connection open. To perform an update you open the connection again and call dataadapter.update()

q2 pretty much the same as q1, until you open the connection again and call dataadapter.update() the changes will be in the local dataset only.

1. Open connection
2. Get data
3. Close connection
4. manipulate/add data
5. Open connection
6. dataadapter.update()
7. close connection

There is of course I'm sure exceptions in very specific cases.

Side note, if using c# I prefer to use the USING construct which will call dispose on the unmanaged objects and close connections.

EDIT

Updating data with data adapters http://msdn.microsoft.com/en-us/library/33y2221y.aspx

A very simple example

  {
        SqlDataAdapter ad;
        SqlConnection con;
        SqlCommand cm_insert;
        SqlCommand cm_select;
        DataSet employees;
        employees = new DataSet();
        ad = new SqlDataAdapter();
        cm_select = new SqlCommand("select * from employees");
        cm_insert = new SqlCommand("insert into employees values(@employeename) ");
        cm_insert.Parameters.Add("@employeename", SqlDbType.VarChar, 50, "employeename");
        ad.InsertCommand = cm_insert;
        ad.SelectCommand = cm_select;





        using (con = new SqlConnection(@"data source=csl066\sqlexpress;initial catalog=junk;Integrated Security=SSPI;persist security info=False;packet size=4096"))
        {

            cm_select.Connection = con;
            con.Open();
            ad.Fill(employees);

        }
        //Do other work, collect data, sometime later in the app..
        using (con = new SqlConnection(@"data source=csl066\sqlexpress;initial catalog=junk;Integrated Security=SSPI;persist security info=False;packet size=4096"))
        {
            cm_insert.Connection = con;
            con.Open();
            employees.Tables[0].Rows.Add(new string[] { "Allen" });
            ad.Update(employees);
        }

    }