Why does a connection have to be open for a nonquery but not to fill a dataset?
SqlConnection cw_con = new SqlConnection("Server=Server;Database=Database;User=User;password=password");
SqlCommand cmd = new SqlCommand("SELECT * FROM Example WHERE value = value");
cmd.Connection = cw_con;

//Create DataSet
DataSet cw_ds = new DataSet("cw_ds");
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
//Execute Command and Fill DataSet
da.Fill(cw_ds);
cw_ds.Clear();

SqlConnection cw_con = new SqlConnection("Server=Server;Database=Database;User=User;password=password");
cw_con.Open();
SqlCommand cmd = new SqlCommand("UPDATE example SET value = value WHERE value = value");
cmd.Connection = cw_con;
cmd.ExecuteNonQuery();
cw_con.Close();

SqlCommand InsertCmd = new ("Insert statement", cw_con)

cw_con.Open();

foreach (var newitem in collection)
{
   --Set Parameters
   UpdateCmd.ExecuteNonQuery();
}

cw_con.Close();