using System; using System.Data; using System.Data.OleDb; class OleDbConnectionOracle { public static void Main() { string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER"; OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); myOleDbCommand.CommandText = "SELECT empno, ename, sal FROM emp WHERE empno = 7369"; myOleDbConnection.Open(); OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader(); myOleDbDataReader.Read(); Console.WriteLine("myOleDbDataReader[" empno"] = " + myOleDbDataReader["empno"]); Console.WriteLine("myOleDbDataReader[" ename"] = " + myOleDbDataReader["ename"]); Console.WriteLine("myOleDbDataReader[" sal"] = " + myOleDbDataReader["sal"]); myOleDbDataReader.Close(); myOleDbConnection.Close(); } }
Database ADO.net
How to use an OleDbConnection object to connect to an Oracle database
using System; using System.Data; using System.Data.OleDb; class OleDbConnectionOracle { public static void Main() { string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER"; OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); myOleDbCommand.CommandText = "SELECT empno, ename, sal FROM emp WHERE empno = 7369"; myOleDbConnection.Open(); OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader(); myOleDbDataReader.Read(); Console.WriteLine("myOleDbDataReader[" empno"] = " + myOleDbDataReader["empno"]); Console.WriteLine("myOleDbDataReader[" ename"] = " + myOleDbDataReader["ename"]); Console.WriteLine("myOleDbDataReader[" sal"] = " + myOleDbDataReader["sal"]); myOleDbDataReader.Close(); myOleDbConnection.Close(); } }
Committed insert
using System; using System.Data; using System.Data.OleDb; public class Transact { 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 cmd = con.CreateCommand(); OleDbTransaction trans = con.BeginTransaction(); cmd.Transaction = trans; cmd.CommandText ="INSERT INTO Employee VALUES (12,'CD','wwe',10)"; cmd.ExecuteNonQuery(); trans.Commit(); cmd.CommandText = "SELECT First_name FROM Employee"; OleDbDataReader reader = cmd.ExecuteReader(); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); con.Close(); } }
OleDbTransaction rollback
using System; using System.Data; using System.Data.OleDb; public class Transact { 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 cmd = con.CreateCommand(); OleDbTransaction trans = con.BeginTransaction(); cmd.Transaction = trans; cmd.CommandText ="INSERT INTO Employee VALUES (09,'qwe','ewq',123)"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT First_name FROM Employee"; OleDbDataReader reader = cmd.ExecuteReader(); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); trans.Rollback(); cmd.CommandText = "SELECT First_name FROM Employee"; reader = cmd.ExecuteReader(); Console.WriteLine("Rolled back insert and update -- table unchanged"); Console.WriteLine("First_name"); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); con.Close(); } }
Use OleDbTransaction
using System; using System.Data; using System.Data.OleDb; public class Transact { 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 cmd = con.CreateCommand(); OleDbTransaction trans = con.BeginTransaction(); cmd.Transaction = trans; cmd.CommandText = "UPDATE Employee SET Salary = 1342 " + "WHERE ID > 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT First_name FROM Employee"; OleDbDataReader reader = cmd.ExecuteReader(); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); con.Close(); } }
Illustrates transaction processing
using System; using System.Data; using System.Data.OleDb; public class Transact { 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 cmd = con.CreateCommand(); OleDbTransaction trans = con.BeginTransaction(); cmd.Transaction = trans; cmd.CommandText ="INSERT INTO Employee VALUES (09,'qwe','ewq',123)"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT First_name FROM Employee"; OleDbDataReader reader = cmd.ExecuteReader(); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); con.Close(); } }
Read query result from OleDbReader
using System; using System.Data.OleDb; public class ReadFromOleDb { [STAThread] static void Main(string[] args) { String sConn = "provider=sqloledb;server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; String sSQL = "select id, firstname, lastname from Employee"; OleDbConnection oConn = new OleDbConnection(sConn); oConn.Open(); OleDbCommand oCmd = new OleDbCommand(sSQL, oConn); OleDbDataReader oReader = oCmd.ExecuteReader(); int idxID = oReader.GetOrdinal("id"); int idxFirstName = oReader.GetOrdinal("firstname"); int idxLastName = oReader.GetOrdinal("lastname"); while(oReader.Read()) { Console.WriteLine("{0} {1} {2}", oReader.GetValue(idxID), oReader.GetValue(idxFirstName), oReader.GetValue(idxLastName)); } } }