Csharp/C Sharp/Database ADO.net/OleDbCommand

Between certain dates

<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 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();
}

}

      </source>

Create table and populate data

<source lang="csharp"> 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();
}

}

      </source>

Delete database records through OleDbCommand and verify the results

<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 ="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();
}

}

      </source>

Execute aggregate function: sum

<source lang="csharp"> 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();
}

}

      </source>

Get column average

<source lang="csharp"> 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();
}

}

      </source>

Get count value from a select query

<source lang="csharp"> 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();
}

}

      </source>

Get max column value

<source lang="csharp"> 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();
}

}

      </source>

How to execute a TableDirect command

   <source lang="csharp">

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();
 }

}

      </source>
   
  

Pass parameter to OleDbCommand

<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 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();
}

}

      </source>