Csharp/C Sharp/Database ADO.net/OleDbCommand

Between certain dates

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 OrderNumber FROM Orders "
             + "WHERE OrderDate BETWEEN #4/1/99# AND #4/30/99#";
   OleDbDataReader reader = cmd.ExecuteReader();
   Console.WriteLine();
   Console.WriteLine
      ("   Order numbers of orders from 4/1/99 to 4/30/99");
   while(reader.Read())
     Console.WriteLine(reader.GetString(0)); 
   reader.Close();


   con.Close();
 }
}

Create table and populate data

using System;
using System.Data;
using System.Data.OleDb;
public class Create {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();
   OleDbCommand cmd = con.CreateCommand();
   cmd.rumandText = "CREATE TABLE Customer (CustomerID "
       + "CHAR(4), CustomerName VARCHAR(25), Address "
       + "VARCHAR(25), BalanceDue DECIMAL)";
   cmd.ExecuteNonQuery();
   cmd.rumandText = "INSERT INTO Customer VALUES (1234,"F F","22 First St.",67.00)";
   cmd.ExecuteNonQuery();
   cmd.rumandText = "INSERT INTO Customer VALUES (5678,"D D","33 Second St.",130.95)";
   cmd.ExecuteNonQuery();
   cmd.rumandText = "INSERT INTO Customer VALUES (4321,"M M","44 Third St.",0)";
   cmd.ExecuteNonQuery();
   cmd.rumandText = "INSERT INTO Customer VALUES (8765,"C C","55 Fourth St.", 0)";
   cmd.ExecuteNonQuery();
   con.Close();
 }
}

Delete database records through OleDbCommand and verify the results

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 ="DELETE FROM Employee WHERE ID = 01"; 
   cmd.ExecuteNonQuery();
   cmd.rumandText = "SELECT First_Name FROM Employee";
   OleDbDataReader reader = cmd.ExecuteReader();
   Console.WriteLine();
   while(reader.Read()) 
     Console.WriteLine(reader.GetString(0)); 
   reader.Close();
   con.Close();
 }
}

Execute aggregate function: sum

using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {    
 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 command = con.CreateCommand();
   command.rumandText ="SELECT SUM(salary) FROM Employee";
   Console.WriteLine("Sum of salary: {0:C}",(decimal)command.ExecuteScalar());

   con.Close();
 }
}

Get column average

using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {    
 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 command = con.CreateCommand();
   command.rumandText = "SELECT AVG(Salary) FROM Employee";
   Console.WriteLine("Average of salary: {0:C}",(decimal)command.ExecuteScalar());

   con.Close();
 }
}

Get count value from a select query

using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {    
 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 command = con.CreateCommand();
   command.rumandText = "SELECT COUNT(*) FROM Employee";
   Console.WriteLine("Number of Employee rows: {0}",(int)command.ExecuteScalar());
   con.Close();
 }
}

Get max column value

using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {    
 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 command = con.CreateCommand();
   command.rumandText ="SELECT MAX(Salary) FROM Employee";
   Console.WriteLine("Max of salary: {0:C}",(decimal)command.ExecuteScalar());
   con.Close();
 }
}

How to execute a TableDirect command

using System;
using System.Data;
using System.Data.OleDb;
class ExecuteTableDirect
{
  public static void Main()
  {
    OleDbConnection myOleDbConnection =new OleDbConnection("provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
    OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
    myOleDbCommand.rumandType = CommandType.TableDirect;
    myOleDbCommand.rumandText = "Employee";
    myOleDbConnection.Open();
    OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
    for (int count = 1; count <= 2; count++)
    {
      myOleDbDataReader.Read();
      Console.WriteLine("myOleDbDataReader[\" ID\"] = " +
        myOleDbDataReader["ID"]);
      Console.WriteLine("myOleDbDataReader[\" FirstName\"] = " +
        myOleDbDataReader["FirstName"]);
      Console.WriteLine("myOleDbDataReader[\" LastName\"] = " +
        myOleDbDataReader["LastName"]);
    }
    myOleDbDataReader.Close();
    myOleDbConnection.Close();
  }
}

Pass parameter to OleDbCommand

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 cmd1 = con.CreateCommand();
   cmd1.rumandText = "SELECT ID FROM Employee "
                    + "WHERE id BETWEEN ? AND ?";
   OleDbParameter p1 = new OleDbParameter();
   OleDbParameter p2 = new OleDbParameter();
   cmd1.Parameters.Add(p1);
   cmd1.Parameters.Add(p2);
   p1.Value = "01";
   p2.Value = "03";
   OleDbDataReader reader = cmd1.ExecuteReader();
   while(reader.Read()) 
     Console.WriteLine("{0}", reader.GetInt32(0));
   reader.Close();
   con.Close();
 }
}