Bob Beauchemin Senior Staff Instructor DevelopMentor Alex Keh Senior Product Manager Oracle Corporation

Size: px

Start display at page:

Download "Bob Beauchemin Senior Staff Instructor DevelopMentor Alex Keh Senior Product Manager Oracle Corporation"

ひでかたなせ
7 years ago
Views:

2 Bob Beauchemin Senior Staff Instructor DevelopMentor Alex Keh Senior Product Manager Oracle Corporation

3 Oracle Data Provider for.net (ODP.NET)

4 Oracle.NET ODP.NET ODP.NET

5 Oracle.NET ASP.NET VB.NET C# C++.NET Any.NET Client OLE DB.NET Oracle OLE DB ODBC.NET Oracle ODBC ODP.NET Oracle

6 .NET OLE DB.NET Oracle Provider for OLE DB ODBC.NET Oracle ODBC driver ODP.NET ADO.NET ADO/Oracle OLE DB ODP.NET ADO

7 ODP.NET OLE DB.NET ODBC.NET ODP.NET Oracle Oracle OLE DB.NET ODBC.NET ODP.NET OLE DB.NET and ODBC.NET

8 A N N O U N C E M E N T Oracle Data Provider for.net OTN

9 Oracle.NET ODP.NET ODP.NET

10 ODP.NET PL/SQL Oracle LOB REF BFILE N DATE TIMESTAMP LONG RAW.NET Oracle Services for MTS ( )

11 ODP.NET Min, Max, Timeout, Lifetime, Increment, Decrement Unicode Oracle

12 ODP.NET DataReader XML DB XMLType CLOB XML PL/SQL XML DB API

13 Oracle.NET ODP.NET ODP.NET

14 ODP.NET Oracle9i Release 2 Oracle8 Oracle8i Oracle9i.NET

15 ODP.NET Disconnected Layer Connected Layer (ODP.NET) Data Layer DataSet DataAdapter Command Builder DataReader Command Transaction Connection Oracle

16 .NET Data Providers Connected Data providers ADO.NET Data providers Connection Data providers Command Resultsets DataReader

17 Data Provider DataSet DataAdapter DataSet Select/Update CommandBuilder SELECT UPDATE Specialty classes ParameterCollection Command Transaction class = local transactions Exceptions Errors DB

18 ADO.NET IDbConnection (OracleConnection) IDbCommand (OracleCommand) IDbTransaction (OracleTransaction) IDataReader, IDataRecord (OracleDataReader) OracleTransaction.Save OracleCommand.AddRowid

19 IDbConnection = common connection behavior / / ODP.NET OLE DB ODP.NET Oracle -OS - SYSDBA/SYSOPER - closed disposed

20 OLE DB ODBC No control over pool, timeout value only ODP.NET COM+ Pool

21 // build up a connection string // vanilla parms String sconn = "user id=scott;password=tiger;data source=orcl"; // add connection pooling and auto enlistment sconn += ";pooling=true;connection timeout=30;enlist=true"; // specify pool parms sconn += ";min pool size=2;max pool size=50"; // open the connection OracleConnection conn = new OracleConnection(sConn);

22 Command Command ExecuteNonQuery ExecuteScalar ExecuteReader DataReader ODP.NET SelectForUpdate RowID resultset FetchSize ChunkSize LONG/LONG RAW

23 ParameterCollection ODP.NET ODP.NET Output ODP.NET output ODP.NET

24 int[] myarraydeptno = new int[3]{1, 2, 3}; OracleConnection conn = new OracleConnection(connectStr); OracleCommand cmd = new OracleCommand( "delete dept where deptno = :1", conn); // Bind with an array of 3 items cmd.arraybindcount = 3; OracleParameter param1 = new OracleParameter(); param1.oracledbtype = OracleDbType.Int32; param1.value = myarraydeptno; cmd.parameters.add(param1); conn.open(); cmd.executenonquery();

25 PL/SQL REF Output CREATE OR REPLACE PACKAGE MyPackage AS TYPE empcur IS REF CURSOR; TYPE jobcur IS REF CURSOR; PROCEDURE GetEmpRecords( p_cursor OUT empcur, j_cursor OUT jobcur, indeptno IN NUMBER, p_errorcode OUT NUMBER); END Employees3; / CREATE OR REPLACE PACKAGE BODY MyPackage AS PROCEDURE GetEmpRecords( p_cursor OUT empcur, j_cursor OUT jobcur, indeptno IN NUMBER, p_errorcode OUT NUMBER) IS BEGIN p_errorcode := 0; OPEN p_cursor FOR SELECT * FROM emp WHERE deptno = indeptno; OPEN j_cursor FOR SELECT * FROM dept; EXCEPTION WHEN OTHERS THEN p_errorcode:= SQLCODE; END GetEmpRecords; END Employees3; /

26 Output OracleConnection conn = new OracleConnection( "data source=zmv43;user id=scott;password=tiger"); OracleCommand cmd = new OracleCommand( "Employees3.GetEmpRecords", conn); cmd.commandtype = CommandType.StoredProcedure; conn.open(); cmd.parameters.add("p_cursor", OracleDbType.RefCursor); cmd.parameters.add("j_cursor", OracleDbType.RefCursor); cmd.parameters.add("indeptno", OracleDbType.Decimal); cmd.parameters.add("p_errorcode", OracleDbType.Int32); cmd.parameters[0].direction = ParameterDirection.Output; cmd.parameters[1].direction = ParameterDirection.Output; cmd.parameters[2].value = 10; cmd.parameters[3].direction = ParameterDirection.Output; int i = cmd.executenonquery(); Console.WriteLine(cmd.Parameters[3].Value); // WORKS! OracleDataReader rdr1 = ((OracleRefCursor)cmd.Parameters[0].Value).GetDataReader(); OracleDataReader rdr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader(); while ((rdr1.read()) && (rdr2.read())) //... read both ref cursors at same time

27 DataReaders DataReaders resultsets SELECT REF DataReader.Read STRONG Oracle.NET

28 public interface IDataRecord { // some methods ommitted bool GetBoolean(int i) byte GetByte(int i) int GetBytes(int i, int fieldoffset, byte[] buffer, int bufferoffset, int length) char GetChar(int i) int GetChars(int i, int fieldoffset, char[] buffer, int bufferoffset, int length) IDataReader GetData(int i) DateTime GetDateTime(int i) decimal GetDecimal(int i) double GetDouble(int i) float GetFloat(int i) Guid GetGuid(int i) short GetInt16(int i) int GetInt32(int i) long GetInt64(int i) string GetString(int i) }

29 OracleDataReader rdr; // populate data reader... then for (int i=0;i < rdr.fieldcount; i++) { // column name String name = rdr.getname(i); } // get name and value by ordinal Console.WriteLine("column {0}: {1}", rdr.getname(i), rdr[i]); // get ordinal and value by name Console.WriteLine("column {0}: {1}", rdr.getordinal(name), rdr[name]);

30 DataReader REF OracleConnection conn = new OracleConnection( "data source=zmv43;user id=scott;password=tiger"); OracleCommand cmd = new OracleCommand( "MyPackage.GetEmpRecords", conn); cmd.commandtype = CommandType.StoredProcedure; conn.open(); cmd.parameters.add("p_cursor", OracleDbType.RefCursor); cmd.parameters.add("j_cursor", OracleDbType.RefCursor); cmd.parameters.add("indeptno", OracleDbType.Decimal); cmd.parameters[0].direction = ParameterDirection.Output; cmd.parameters[1].direction = ParameterDirection.Output; cmd.parameters[2].value = 10; OracleDataReader rdr = cmd.executereader(); bool more = true; while (more == true) { while (rdr.read()) {Console.WriteLine("reading..."); } more = rdr.nextresult(); if (more == true) Console.WriteLine("next result"); }

31 BLOB CLOB GetBytes GetChars Called with null buffer returns CLOB and BLOB length Works with LONG, LONG RAW, and BFILE as well ODP.NET

32 Oracle BLOB // Create a file and open a binary writer over it FileStream fs = new FileStream("c: mypic.bmp", FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fs); OracleConnection conn = new OracleConnection( "user id=scott;password=tiger;data source=orcl"); string cmdtext = "update employees " + "set Photo=:image where EmployeeId=1"; OracleCommand cmd = new OracleCommand(cmdtext, conn); cmd.parameters.add(":image", OracleDbType.Blob); cmd.parameters["image"].value = br.readbytes((int)br.basestream.length); conn.open(); int i = cmd.executenonquery(); conn.close();

33 XMLDB XML create or replace procedure getempsasxml( theclob OUT CLOB) is ctx dbms_xmlgen.ctxhandle; begin ctx := dbms_xmlgen.newcontext('select * from emp'); theclob := dbms_xmlgen.getxml(ctx); dbms_xmlgen.closecontext(ctx); end; /

34 XMLType OracleConnection conn = new OracleConnection( "data source=zmv43;user id=scott;password=tiger"); OracleCommand cmd = new OracleCommand("getempsasxml", conn); cmd.commandtype = CommandType.StoredProcedure; cmd.parameters.add("theclob", OracleDbType.Clob); cmd.parameters[0].direction = ParameterDirection.Output; conn.open(); int i = cmd.executenonquery(); OracleClob l = (OracleClob)cmd.Parameters[0].Value; byte[] thelob = new Byte[l.Length]; l.read(thelob, 0, (int)l.length); MemoryStream ms = new MemoryStream(thelob); StreamReader sr = new StreamReader(ms); Console.WriteLine(sr.ReadToEnd());

35 Data providers OleDbError Oracle OracleError

36 OracleException OracleConnection conn = new OracleConnection( "user id=scott;password=tiger;data source=orcl"); OracleCommand cmd = new OracleCommand("ins_sel_ins", conn); cmd.commandtype = CommandType.StoredProcedure; try { conn.open(); int rows = cmd.executenonquery(); } catch (OracleException oe) { Console.WriteLine("Procedure {0} failed", oe.procedure); for (int i=0;i<oe.errors.count;i++) Console.WriteLine(oe.Errors[i].Message); } finally { if (conn.state == ConnectionState.Open) conn.close(); }

37 BiginTransaction Command OracleTransaction OracleTransaction

38 System.EnterpriseServices MSDTC COM+ ServicedComponent COM+ ComEmulateAttribute COM+ ODP.NET Oracle Services for MTS

39 EnterpriseServices // require a distributed transaction for this class [Transaction(TransactionOption.Required, Isolation=TransactionIsolationLevel.ReadCommitted)] public class DoTx : ServicedComponent { // only no-arg constructors public DoTx() { } // autocomplete true is the default [AutoComplete(false)] public void DoDatabase() { OracleConnection conn = null; OracleDataReader rdr; try { conn = new OracleConnection( "user id=scott;password=tiger;data source=orcl"; conn.open(); OracleCommand cmd = new OracleCommand( "select * from emp", conn); rdr = cmd.executereader();

40 nterpriseservices (2) } } while (rdr.read()) { // do something with those rows } rdr.close(); cmd.commandtext = "delete emp where empno = 99999"; cmd.executenonquery(); // if I get here I vote to commit ContextUtil.SetComplete(); } catch (Exception e) { ContextUtil.SetAbort(); // rethrow the exception throw(e); } finally { // this closes the command and datareader if (conn.state!= ConnectionState.Closed)) conn.close(); }

41 Oracle.NET ODP.NET ODP.NET

42 Dell Computer PC Dell eordermanagement ODP.NET REF LOB XML DB ODP.NET XML

43 ComponentOne Visual Studio.NET ComponentOne Data Objects for.net ODP.NET Oracle

44 DataTrak EDC Electronic Data Capture Central Admin and DATAUnifyer ODP.NET XML You can tell that ODP.NET was designed by people who built applications before, it is easy to use, provides me access to all the features of the database I always wanted to take advantage of and it is absolutely intuitive. -- Mario Jugel, Senior Software Engineer

45 ODP.NET Oracle on Windows Book: Oracle9i for Windows 2000 Tips and Techniques Or you can order on Amazon.com

46 Q U E S T I O N S A N S W E R S

47 Oracle Corporation Using the Oracle Data Provider for.net ppt

Oracle Lite Tutorial

Oracle Lite Tutorial GrapeCity -.NET with GrapeCity - FlexGrid Creation Date: Nov. 30, 2005 Last Update: Nov. 30, 2005 Version: 1.0 Document Control Internal Use Only Author Hiroshi Ota Change Logs Date Author Version Change