Csharp/CSharp Tutorial/ADO.Net/OleDbCommand

Connect Table to OleDbCommand using CommandType.TableDirect

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class MainClass
{
  static void Main(string[] args)
    {
    string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
    OleDbConnection conn = new OleDbConnection(ConnectionString);
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.rumandText = "Customers";
    cmd.rumandType = CommandType.TableDirect;
       
    conn.Open();
    OleDbDataReader reader = cmd.ExecuteReader();
    Console.WriteLine("Customer Id, Contact Name, Company Name");
        
    while (reader.Read())
    {
      Console.Write(reader["CustomerID"].ToString());
      Console.Write(", "+ reader["ContactName"].ToString());
      Console.WriteLine(", "+ reader["CompanyName"].ToString());
    }
    
    reader.Close();
    conn.Close();                  
    }
}

Customer Id, Contact Name, Company Name
ALFKI, Maria Anders, Alfreds Futterkiste
ANATR, Ana Trujillo, Ana Trujillo Emparedados y helados
ANTON, Antonio Moreno, Antonio Moreno Taquer�a
AROUT, Thomas Hardy, Around the Horn
BERGS, Christina Berglund, Berglunds snabbk?p
BLAUS, Hanna Moos, Blauer See Delikatessen
WOLZA, Zbyszek Piestrzeniewicz, Wolski  Zajazd
...
...

Execute Non Query

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class MainClass
{
  static void Main(string[] args)
    {
        string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
        OleDbConnection conn = new OleDbConnection(ConnectionString);
    conn.Open();
    OleDbCommand cmd = new OleDbCommand("Customers", conn);

    cmd.rumandText = @"DELETE * FROM Customers";
    cmd.ExecuteNonQuery();
          
        conn.Close();                  
    }
}

Executing a Parameterized Query with OleDbCommand

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString = "Provider=SQLOLEDB; Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string oledbSelect = "SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > ?";
            OleDbConnection oledbConnection = new OleDbConnection(oledbConnectString);
            OleDbCommand oledbCommand = new OleDbCommand(oledbSelect, oledbConnection);
            oledbCommand.Parameters.Add("@TotalDue", OleDbType.Currency);
            oledbCommand.Parameters["@TotalDue"].Value = 200000;
            OleDbDataAdapter oledbDa = new OleDbDataAdapter(oledbCommand);
            DataTable oledbDt = new DataTable( );
            oledbDa.Fill(oledbDt);
            foreach (DataRow row in oledbDt.Rows){
                Console.WriteLine(row["SalesOrderID"]);
                Console.WriteLine(row["OrderDate"]);
                Console.WriteLine(row["TotalDue"]);
            }
        }
    }

OleDbCommand: Execute Scalar

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class MainClass
{
    static void Main(string[] args)
    {
        string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
        OleDbConnection conn = new OleDbConnection(ConnectionString);
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.rumandText = "SELECT Count(*) FROM Customers";
        cmd.Connection = conn;
        int counter = (int)cmd.ExecuteScalar();
        
        Console.WriteLine("Total rows returned are :" + counter.ToString());
               
        conn.Close();                  
    }
}

Total rows returned are :91

Use OLE DB Data Provider

using System;
using System.Data;
using System.Data.OleDb;
class MainClass
{
   static void Main(string[] args)
   {
      string connString = "provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
      string sql = @"select * 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 );
         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();
      }
   }
}