public class ADOnet
 {
  private OleDbConnection odORAConn;
  private OleDbConnection odAccessConn;
  private SqlConnection sqlConn;
  public bool InitConnectionString()
  {
   //with an Oracle database
   odORAConn=new OleDbConnection("Provider=MSDAORA.1;User ID=scott;password=tiger;database=ora");
   //with Access data source
   odAccessConn=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\myDatabase.mdb; User ID=MyUserID; Password=MyPassword");
   //with SqlServer database
   sqlConn=new SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30");
   /*
    Persist Security Info=False: userid and password are used to open the connection, and then discarded
    * /
   /*Integrated Security=SSPI: you do not need to supply User ID and password
    Conditions to use:
       - SQL Server must be running on the same computer as IIS.
    - All application users must be on the same domain and their credentials are available to the Web server.
    - In IIS to turn off anonymous access and turn on Windows authentication.
    - Make sure in application config file you have
      <authentication mode="Windows" />
      <identity impersonate="true"/>

    */
   return true;
  }
 }

public bool doCommand()
{
   SqlConnection myConnection=new SqlConnection();
   myConnection.ConnectionString = "server=mySqlServer;
database=myDatabase;user id=myUserId;password=MyPassword";
   string SQLText="Select COUNT(EmployeeID) FROM EMPLOYEE";
   SqlCommand myCommand=myConnection.CreateCommand();
   /*
   OR
   myCommand=new SqlCommand();
   myCommand.Connection=myConnection;
    */
   myCommand.CommandText=SQLText;
   /*
    No need to set Command type the default is CommandType.Text
    in case of Stored procedure use: myCommand.CommandType=CommandType.StoredProcedure;
    */
   
   myConnection.Open();
   try
   {
      //return the first column in the first row
      int Employees=Convert.ToInt32(myCommand.ExecuteScalar());
   }
   catch(Exception ex)
   {
     //Log exception
     //Log(ex);
   }
   finally
   {
      myConnection.Close();
    //or
    //myConnection.Dispose();
   }
   return true;
 }

public bool doDataReader()
{
   SqlConnection myConnection=new SqlConnection();
   myConnection.ConnectionString = "server=mySqlServer; database=myDatabase; user id=myUserId; password=MyPassword";
   string SQLAllText="Select * FROM EMPLOYEE";
   SqlCommand myCommand=myConnection.CreateCommand();
   /*
   OR
   myCommand=new SqlCommand();
   myCommand.Connection=myConnection;
    */
   myCommand.CommandText=SQLAllText;
   /*
    No need to set Command type the default is CommandType.Text
    in case of Stored procedure use: myCommand.CommandType=CommandType.StoredProcedure;
    */
   
   myConnection.Open();
   try
   {
    //introducing Datareader
    SqlDataReader myReader = myCommand.ExecuteReader();
    ArrayList aEmployees=new ArrayList();
    while (myReader.Read())
    {
     
     //retrieve column values
     //we can retrieve a column value using its index
     string Empl_id = myReader[0].ToString();
     //Retrieving column value by using data column name
     string FirstName = myReader["FirstName"].ToString();
     string LastName = myReader["LastName"].ToString();

     aEmployees.Add(new Employee(Empl_id,FirstName,LastName));
     
    }
    //the connection can only be close after reading the data
    myConnection.Close();
   }
   catch(Exception ex)
   {
    //Log exception
    //Log(ex);
   }
   finally
   {
    myConnection.Close();
    //or
    //myConnection.Dispose();
   }
   return true;
}

public bool doDataAdapter()
{
   SqlConnection myConnection=new SqlConnection();
   myConnection.ConnectionString = "server=mySqlServer; database=myDatabase; user id=myUserId; password=MyPassword";
   string SQLAllText="Select * FROM EMPLOYEE";
   
   SqlCommand myCmdEmployee=myConnection.CreateCommand();
  
   myCmdEmployee.CommandText=SQLAllText;
   
   
   myConnection.Open();
   try
   {
    
    //introducing data adapter
    SqlDataAdapter daEmployee = new SqlDataAdapter(myCmdEmployee);
    DataSet dsEmployee = new DataSet();
    //lets fills our dataset with rows from the query
    daEmployee.Fill(dsEmployee);
    //We can close the database connection and work disconnected
    //this one of the major advantages of DataAdapter
    myConnection.Close();
    //Extracting the XML document from our dataset
    string EmployeeDocument=dsEmployee.GetXml();
    //looping through dataset tables
    ArrayList aEmployees=new ArrayList();
    foreach.(DataRow row in dsEmployee.Tables[0].Rows)
    {
     //retrieve column values
     //we can retrieve a column value using its index
     string Empl_id = row.ItemArray[0].ToString();
     //Retrieving column value by using data column name
     string FirstName = row["FirstName"].ToString();
     string LastName = row["LastName"].ToString();

     aEmployees.Add(new Employee(Empl_id,FirstName,LastName));

    }
   }
   catch(Exception ex)
   {
    //Log exception
    //Log(ex);
   }
   finally
   {
    myConnection.Close();
    //or
    //myConnection.Dispose();
   }
   return true;
}