ADO.NET connected mode vs disconnected mode

---

ADO.NET can work on either connected mode or disconnected mode.

Connected mode implies the database connection is open, for example, if you use DataReader, the database connection state keeps on open;

If you use DataSet or DataAdapter, the database connection status is cloesed, it is ADO.NET disconnected mode; When DataAdapter Fill() data to DataSet, the connection state is closed then.

An example using connected mode:

            SqlConnection sqlCon = new SqlConnection();
            SqlCommand sqlCommand = new SqlCommand();

            sqlCon.ConnectionString = ".....";

            try
            {
                sqlCon.Open();
                sqlCommand.CommandText = "Insert Into Employee(EmployeeID, Name....)";
                sqlCommand.Connection = sqlCon;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //exception handle here
            }
            finally
            {
                sqlCon.Close();
            }

An example using disconnected mode:

For disconnected mode, we should use DataAdapter and DataSet;

            SqlConnection sqlCon = new SqlConnection();
            SqlDataAdapter sa = null;


            try
            {
                sqlCon.ConnectionString = ".....";
                sqlCon.Open();

                sa = new SqlDataAdapter("Select * from Employee", sqlCon);

                DataSet ds = new DataSet();
                sa.Fill(ds);

            }
            catch (Exception ex)
            {
                //exception handle here
            }
            finally
            {
                sqlCon.Close();
            }