Database ADO.net

OleDbConnection connection: ConnectionString, Database, DataSource, ServerVersion and State

Posted on by coder 
   


using System;
using System.Data;
using System.Data.OleDb;

class AccessConnect {
   static void Main() 
   {
      string connString = @"provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;";

      OleDbConnection conn = new OleDbConnection(connString);

      try 
      {
         conn.Open();
         Console.WriteLine("Connection opened.");

         Console.WriteLine("Connection Properties:");   
         Console.WriteLine("	Connection String: {0}", conn.ConnectionString);    
         Console.WriteLine("	Database: {0}",conn.Database);   
         Console.WriteLine("	DataSource: {0}",conn.DataSource);
         Console.WriteLine("	ServerVersion: {0}", conn.ServerVersion);
         Console.WriteLine("	State: {0}", conn.State);
      } catch (OleDbException e) {
         Console.WriteLine("Error: " + e);  
      } finally  {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}

Pass parameter to OleDbCommand

Posted on by coder 


   

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

Create table and populate data

Posted on by coder 
   

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.CommandText = "CREATE TABLE Customer (CustomerID "
       + "CHAR(4), CustomerName VARCHAR(25), Address "
       + "VARCHAR(25), BalanceDue DECIMAL)";
   cmd.ExecuteNonQuery();
   cmd.CommandText = "INSERT INTO Customer VALUES (1234,'F F','22 First St.',67.00)";
   cmd.ExecuteNonQuery();
   cmd.CommandText = "INSERT INTO Customer VALUES (5678,'D D','33 Second St.',130.95)";
   cmd.ExecuteNonQuery();
   cmd.CommandText = "INSERT INTO Customer VALUES (4321,'M M','44 Third St.',0)";
   cmd.ExecuteNonQuery();
   cmd.CommandText = "INSERT INTO Customer VALUES (8765,'C C','55 Fourth St.', 0)";
   cmd.ExecuteNonQuery();

   con.Close();
 }
}

Delete database records through OleDbCommand and verify the results

Posted on by coder 
   


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

   con.Close();
 }
}

Between certain dates

Posted on by coder 
   


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

Get count value from a select query

Posted on by coder 


   


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.CommandText = "SELECT COUNT(*) FROM Employee";
   Console.WriteLine("Number of Employee rows: {0}",(int)command.ExecuteScalar());
   con.Close();
 }
}

Get max column value

Posted on by coder 


   

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.CommandText ="SELECT MAX(Salary) FROM Employee";
   Console.WriteLine("Max of salary: {0:C}",(decimal)command.ExecuteScalar());
   con.Close();
 }
}