Csharp/C Sharp/Database ADO.net/OleDbDataReader

Execute complicated SQL query

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class ExtractInfo {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the Sales database");
  OleDbCommand cmd = con.CreateCommand();
  
  cmd.rumandText = "SELECT DISTINCT CustomerName "
           + "FROM Customer, Item, Orders, OrderItem "
           + "WHERE Customer.CustomerID = Orders.CustomerID "
           + "AND Orders.OrderNumber = OrderItem.OrderNumber "
           + "AND OrderItem.ItemNumber = Item.ItemNumber "
           + "AND Description = "computer"";
  OleDbDataReader reader = cmd.ExecuteReader();
  while(reader.Read())
    Console.WriteLine(reader.GetString(0));
  reader.Close();

  con.Close();
}

}

      </source>

Format Data from OleDbDataReader

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; class OleDbProvider{

  static void Main(string[] args)  {
     String connString = "provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
   
   String sql = "select firstname, lastname from Employee";
     OleDbConnection conn = null;
     OleDbDataReader reader = null;
     try {
        conn = new OleDbConnection(connString);
        conn.Open();
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        reader = cmd.ExecuteReader();
        Console.WriteLine("Querying database {0} with query {1}\n"
                          , conn.Database
                          , cmd.rumandText);
        Console.WriteLine("First Name\tLast Name\n");
        while(reader.Read()){
           Console.WriteLine(
              "{0} | {1}" 
              , reader["FirstName"].ToString().PadLeft(10) 
              , reader[1].ToString().PadLeft(10)
              );
        }
     } catch (Exception e) {
        Console.WriteLine("Error: " + e);
     } finally {
        reader.Close();
        conn.Close();
     }
  }

}

      </source>

Map database column name to OleDbDataReader

<source lang="csharp">

using System; using System.Data.OleDb; public class ReadFromOleDb {

 [STAThread]
 static void Main(string[] args)
 {
   String sConn = "provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
   
   String sSQL = "select id, firstname, lastname from Employee";
   OleDbConnection oConn = new OleDbConnection(sConn);
   oConn.Open();
   OleDbCommand oCmd = new OleDbCommand(sSQL, oConn);
   OleDbDataReader oReader = oCmd.ExecuteReader();
   int idxID = oReader.GetOrdinal("id");
   int idxFirstName = oReader.GetOrdinal("firstname");
   int idxLastName = oReader.GetOrdinal("lastname");
   while(oReader.Read()) {
     Console.WriteLine("{0} {1} {2}",
       oReader.GetValue(idxID),
       oReader.GetValue(idxFirstName),
       oReader.GetValue(idxLastName));
   }
 }

}

      </source>

Query more columns and read result set

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class ExtractInfo {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the Sales database");
  OleDbCommand cmd = con.CreateCommand();
  cmd.rumandText = "SELECT First_Name, Last_Name FROM Employee ORDER BY First_Name"; 
  OleDbDataReader reader = cmd.ExecuteReader();
  Console.WriteLine("First_Name\t\tLast_Name");  
  while(reader.Read()) 
    Console.WriteLine("{0}\t{1}",reader.GetString(0), reader.GetString(1));
  reader.Close();
  
  con.Close();
}

}

      </source>

Read result set containing all columns

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class ExtractInfo {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the Sales database");
  OleDbCommand cmd = con.CreateCommand();
  
  cmd.rumandText = "SELECT * FROM Employee";
  OleDbDataReader reader = cmd.ExecuteReader();
  Console.WriteLine();
  Console.WriteLine("ID\tFirstName\tLastName\tSalary");  
  while (reader.Read())
    Console.WriteLine("{0}\t\t{1}\t\t{2}\t{3}",reader.GetInt32(0),reader.GetString(1),reader.GetString(2), reader.GetDecimal(3));
  reader.Close();

  con.Close();
}

}

      </source>

Read value using OleDbDataReader

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class Prepare {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the database");
  OleDbCommand cmd2 = con.CreateCommand();
  cmd2.rumandText = "SELECT First_name FROM Employee "
                                 + "WHERE ID > ?"; 
  OleDbParameter p3 = new OleDbParameter();
  cmd2.Parameters.Add(p3);
  p3.Value = new Decimal(0.0);
  OleDbDataReader reader = cmd2.ExecuteReader();
  while(reader.Read()) 
    Console.WriteLine("{0}", reader.GetString(0));
  reader.Close();

  con.Close();
}

}

      </source>

Reference data in OleDbDataReader by column name

<source lang="csharp">

   using System;
   using System.Data.OleDb;
 class MapOleDbReader
 {
   static void Main(string[] args)
   {
       String sConn = "provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
       String sSQL = "select id, firstname, lastname from Employee";
     OleDbConnection oConn = new OleDbConnection(sConn);
     oConn.Open();
     OleDbCommand oCmd = new OleDbCommand(sSQL, oConn);
     OleDbDataReader oReader = oCmd.ExecuteReader();
     while(oReader.Read()) 
     {
       Console.WriteLine("{0} {1} {2}",
         oReader["id"],
         oReader["firstname"],
         oReader["lastname"]);
     }
   }
 }

      </source>