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."); } } }
Database ADO.net
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.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
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
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
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
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
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(); } }