<Documents Title Here>

Transcription

1 !?.NET Oracle -.NET with ODP.NET - oo4o ODP.NET Creation Date: Aug. 3, 2004 Last Update: Sep 28, 2004 Version: 1.0

2 oo4o ODP.NET Document Control Internal Use Only Author Hiroshi Ota Change Logs Date Author Version Change Log Aug. 3, 2004 Hiroshi Ota Created. Reviewers Name Position Approvals <Approver 1> <Approver 2> Distribution Copy No. Name Location!?.NET Oracle oo4o ODP.NET 2

3 oo4o ODP.NET!?.NET Oracle Microsoft Visual Studio.NET Oracle10g.NET!?.NET Oracle Oracle Data Provider for.net Visual Basic.NET oo4o(oracle Objects for OLE) ODP.NET Visual Basic( ) oo4o.net!?.net Oracle.NET!?.NET Oracle Web ASP.NET 3. oo4o(oracle Objects for OLE) ODP.NET!?.NET Oracle oo4o ODP.NET 7 1. ODP.NET 2. oo4o ODP.NET 3. oo4o ODP.NET PL/SQL ADO!?.NET Oracle oo4o ODP.NET 3

4 oo4o ODP.NET!?.NET Oracle OS Microsoft Windows 2000 Professional + SP4 RDBMS Oracle Database 10g Standard Edition for Windows OS Microsoft Windows 2003 Enterprise AP Microsoft Internet Information Services 6.0 OS Microsoft Windows 2000 Professional + SP4 Microsoft Visual Studio.NET 2003 Microsoft Visual Studio 6.0!?.NET Oracle oo4o ODP.NET 4

5 oo4o ODP.NET ODP.NET Oracle Data Provider for.net ODP.NET Microsoft.NET.NET Oracle OLE DB.NET ODBC.NET ODP.NET ODP.NET Oracle PL/SQL Oracle LOB REF CURSOR DATE Unicode Microsoft Transaction Server XML DB TAF ODP.NET.NET Framework API.NET Oracle Objects for OLE( oo4o).net Runtime Callable Wrapper( RCW) RCW COM RCW!?.NET Oracle oo4o ODP.NET 5

6 oo4o ODP.NET ODP.NET oo4o ODP.NET oo4o Oracle COM Oracle RCW Oracle ODP.NET oo4o.net oo4o Visual Basic.NET VB.NET Visual C#.NET Visual C++.NET oo4o ODP.NET oo4o ODP.NET Oracle ADO.NET ODP.NET ADO.NET DataAdapter DataSet Oracle Command SQL Oracle oo4o oo4o ODP.NET 3. oo4o ODP.NET Oracle SQL Oracle!?.NET Oracle oo4o ODP.NET 6

7 oo4o ODP.NET SQL Oracle SQL oo4o ODP.NET 2 Visual Basic ( C/S). NET ASP.NET XML WEB Windows Web C/S C/S!?.NET Oracle oo4o ODP.NET 7

8 oo4o ODP.NET ADO.NET ADO.NET ODP.NET oo4o OraSession CreateDatabasePool OraSessionClass OracleInProcServer Set OraSession = CreateObject("OracleInProcServer.XOraSession") Database OraDatabase OraSession.CreateDatabasePool 2, 40, 200, "ORCL", "SCOTT/TIGER", 0 ID OraDatabase Set OraDatabase = OraSession.GetDatabaseFromPool(100).1 oo4o ODP.NET Oracle User ID/Password/Data Source Imports Oracle.DataAccess.Client Imports Oracle.DataAccess.Types Public Class fmmainmenu Inherits System.Windows.Forms.Form Private Sub DbConnect() Dim conn As New OracleConnection conn.connectionstring = "Data Source=orcl;User id=scott;password=tiger" conn.open() End Sub.2 ODP.NET!?.NET Oracle oo4o ODP.NET 8

9 oo4o ODP.NET ODP.NET OracleConnection ConnectionString Dim conn As OracleConnection = New OracleConnection conn.connectionstring = "User Id=SCOTT;Password=TIGER; Data Source=ORCL; & _ conn.open() Pooling=True;Min Pool Size=2;Max Pool Size=50".3 ODP.NET ConnectionString ConnectionString Data Source!?.NET Oracle oo4o ODP.NET 9

10 oo4o ODP.NET "User Id=scott;Password=tiger;Data Source=orcl" "User Id=scott;Password=tiger;Data Source=ORCL".4 ConnectionString OTN Oracle Data Provider for.net Oracle ODP.NET oo4o tnsname.ora Oracle Data Source tnsnames.ora ORCL = (DESCRIPTION = ) (ADDRESS_LIST = ) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(port = 1521)) (CONNECT_DATA = ) (SERVICE_NAME = orcl).5 TNSNAMES.ORA tnsnames.ora Web IIS ODP.NET tnsnames.ora Dim conn As New OracleConnection Dim sb As New System.Text.StringBuilder!?.NET Oracle oo4o ODP.NET 10

11 oo4o ODP.NET sb.append("user Id=scott; Password=tiger;") sb.append("data Source=(DESCRIPTION = (ADDRESS_LIST = ") sb.append("(address = (PROTOCOL = TCP)(HOST = localhost)") sb.append("(port = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)") sb.append("(service_name = ORCL)));") conn.connectionstring = sb.tostring conn.open() conn.close().6 tnsname.ora ConnectionString User Id / Windows DBA Privilege SYSDBA SYSOPER Oracle Dim cnn As New OracleConnection Windows DBA Privilege SYSDBA cnn.connectionstring = "User Id=/;Data Source=orcl;DBA Privilege=SYSDBA" cnn.open() MsgBox("Connect OK!!") cnn.close().7 Oracle Dim cnn As New OracleConnection cnn.connectionstring = _ "User Id=scott;Password=tiger;Data Source=ora10g" Try cnn.open() Catch!?.NET Oracle oo4o ODP.NET 11

12 oo4o ODP.NET cnn.openwithnewpassword("panther") End Try.8 ODP.NET ADO.NET ADO.NET Oracle oo4o oo4o ODP.NET oo4o ODP.NET oo4o ODP.NET!?.NET Oracle oo4o ODP.NET 12

13 oo4o ODP.NET oo4o emp Dim OraDynaset As OraDynaset OraDynaset Set OraDynaset = _ OraDatabase.CreateDynaset("SELECT empno,ename FROM emp", ORADYN_READONLY) Do Until OraDynaset.EOF Debug.Print OraDynaset.Fields("empno").Value + " / " + _ OraDynaset.Fields("ename").Value OraDynaset.MoveNext Loop.9 oo4o ODP.NET Dim conn As New OracleConnection conn.connectionstring = _ "User ID=scott;Password=tiger;Data Source=orcl" conn.open() Dim cmd As New OracleCommand CommandText SQL cmd.connection = conn cmd.commandtext = "select empno, ename from emp" OracleCommand ExecuteReader OracleDataReader Dim rdr As OracleDataReader rdr = cmd.executereader!?.net Oracle oo4o ODP.NET 13

14 oo4o ODP.NET Do While rdr.read Debug.WriteLine(CStr(rdr("empno")) + " / " + _ Loop rdr("ename")) Close rdr.close() cmd.close().10 ODP.NET ( ) oo4o ODP.NET oo4o SQL OraDynaset ODP.NET OracleCommand SQL OracleDataReader Dim OraSession As Object Dim OraDatabase As Object Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase("orcl", "scott/tiger", &H2&) SQL OraDatabase.ExecuteSQL "update emp set ename='scott' where empno=6".11 oo4o ( ) conn.open() Dim cmd As New OracleCommand cmd.connection = conn SQL cmd.commandtext = "update emp set ename='bob' where empno=6" cmd.commandtype = CommandType.Text cmd.executenonquery().12 ODP.NET ODP.NET OracleCommand OracleCommand SQL!?.NET Oracle oo4o ODP.NET 14

15 oo4o ODP.NET oo4o OraDynaset Set OraDatabase = OraSession.OpenDatabase( _ "orcl", "scott/tiger", dboption.oradb_default) ssql = "SELECT * FROM MstCustomer Where CustomerId='" + TextCustomerId.Text + "'" Set OraDynaset = _ OraDatabase.CreateDynaset(sSql, ORADYN_DEFAULT) OraDynaset.Edit OraDynaset("CustomerID") = TextCustomerId.Text OraDynaset("CustomerNAME") = TextCustomerName.Text OraDynaset("EmployeeNAME") = TextEmployeeName.Text OraDynaset("kana") = TextKana.Text OraDynaset("job") = TextJob.Text OraDynaset("postcode") = TextPostCode.Text OraDynaset("prefectures") = ComboPrefectures.Text OraDynaset("address") = TextAddress.Text OraDynaset("tel") = TextTel.Text OraDynaset("fax") = TextFax.Text OraDynaset("note") = TextNote.Text OraDynaset.Update.13 oo4o ( ) ODP.NET OracleDataReader ODP.NET ODP.NET!?.NET Oracle oo4o ODP.NET 15

16 oo4o ODP.NET OracleDataAdapter DataSet oo4o OracleDataAdapter DataSet ODP.NET Dim cnn As New OracleConnection Dim cmd As New OracleCommand Dim dslist As New DataSet Dim icnt As Integer cnn.connectionstring = _ "user id=scott;password=tiger;data source=orcl" cmd.connection = cnn DataAdapter DataSet cmd.commandtext = "Select * from emp" Dim adp As New OracleDataAdapter(cmd) adp.fill(dslist, "EmpList") DataSet With dslist.tables("emplist") For icnt = 0 To.Rows.Count - 1 Next Debug.WriteLine(CStr(.Rows(iCnt).Item("empno")) + " / " + _.Rows(iCnt).Item("ename"))!?.NET Oracle oo4o ODP.NET 16

17 oo4o ODP.NET End With.14 ODP.NET ( ) DataSet OracleDataAdapter Fill DataSet Oracle OracleDataAdapter Update.14 ODP.NET ( ) OracleDataAdapter Fill Update DataSet Oracle SQL DataSet OracleDataAdapter Update Oracle Update OracleDataAdapter UpdateCommand, DeleteCommand, InsertCommand SQL!?.NET Oracle oo4o ODP.NET 17

18 oo4o ODP.NET OracleDataAdapter UpdateCommand DeleteCommand InsertCommand SQL OracleCommandBuilder SQL Dim CustRow As DataRow If dscustomer.tables("mstcustomer").rows.count = 0 Then CustRow = dscustomer.tables("mstcustomer").newrow() Else CustRow = dscustomer.tables("mstcustomer").rows(0) End If CustRow.Item("CustomerId") = TextBoxID.Text CustRow.Item("CustomerNAME") = TextBoxCompanyName.Text If CustRow.RowState = DataRowState.Detached Then _ dscustomer.tables("mstcustomer").rows.add(custrow) da.update(dscustomer.tables("mstcustomer")).15 ODP.NET ( ) Dim strcustname As String = _ dscustomer.tables("mstcustomer").rows(0).item( CustomerName ).16 Dim strcustname As String = dscustomer.mstcustomer(0).customername.17 xsd xsd Oracle Developer Tools for Visual Studio.NET ODT Oracle Technology Network( OTN).NET Developer Center!?.NET Oracle oo4o ODP.NET 18

19 oo4o ODP.NET ODT xsd 1. xsd Visual Studio.NET 2. xsd Oracle xsd!?.net Oracle oo4o ODP.NET 19

20 oo4o ODP.NET xsd 3. (T) xsd OK Oracle OracleDataAdapter OracleDataAdapter!?.NET Oracle oo4o ODP.NET 20

21 oo4o ODP.NET DataSet!?.NET Oracle oo4o ODP.NET 21

22 oo4o ODP.NET PL/SQL oo4o ODP.NET PL/SQL SQL PL/SQL CREATE OR REPLACE PACKAGE SCOTT.pkg_ref AS CURSOR c1 IS SELECT ename FROM emp; TYPE empcur IS REF CURSOR RETURN c1%rowtype; PROCEDURE GetEmpData( END; indeptno IN NUMBER, EmpCursor in out empcur ); CREATE OR REPLACE PACKAGE BODY SCOTT.pkg_ref AS PROCEDURE GetEmpData(indeptno IN NUMBER, EmpCursor in out empcur ) IS BEGIN OPEN EmpCursor FOR SELECT ename FROM emp WHERE deptno=indeptno; END GetEmpData; END pkg_ref;.18 PL/SQL GetEmpData emp deptno Ref Cursor oo4o PL/SQL GetEmpData Dim ssql As String Set OraDatabase = OraSession.OpenDatabase( _ "orcl", "scott/tiger", dboption.oradb_default) deptno OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT OraDatabase.Parameters("DEPTNO").serverType = ORATYPE_NUMBER GetEmpData ssql = "Begin pkg_ref.getempdata(:deptno, :EmpCursor); end;" Ref Cursor Set OraDynaset = OraDatabase.CreatePlsqlDynaset(sSql, "EmpCursor", 0&)!?.NET Oracle oo4o ODP.NET 22

23 oo4o ODP.NET Do While Not OraDynaset.EOF Debug.Print OraDynaset("ename") OraDynaset.MoveNext Loop.19 oo4o Ref Cursor ODP.NET Dim ssql As String Dim conn As New OracleConnection("User Id=Scott;Password=Tiger;Data Source=orcl") conn.open() GetEmpData SQL OracleCommand Dim cmd As New OracleCommand("pkg_ref.GetEmpData", conn) cmd.commandtype = CommandType.StoredProcedure deptno cmd.parameters.add("deptno", 10) cmd.parameters.add("empcursor", OracleDbType.RefCursor, ParameterDirection.Output) GetEmpData cmd.executenonquery() Ref Cursor Dim dr1 As OracleDataReader = _ CType(cmd.Parameters(1).Value, OracleRefCursor).GetDataReader Do While dr1.read Debug.WriteLine(dr1("ename")) Loop.20 ODP.NET Ref Cursor Ref Cursor Dim conn As New OracleConnection("User Id=Scott;Password=Tiger;Data Source=orcl") GetEmpData SQL OracleCommand Dim cmd As New OracleCommand("pkg_ref.GetEmpData", conn) cmd.commandtype = CommandType.StoredProcedure!?.NET Oracle oo4o ODP.NET 23

24 oo4o ODP.NET Ref Cursor cmd.parameters.add("deptno", 10) cmd.parameters.add("empcursor", OracleDbType.RefCursor, ParameterDirection.Output) ' GetEmpData DataSet Dim dsdata As New DataSet Dim da As New OracleDataAdapter(cmd) da.fill(dsdata, "data") 'Grid DataGridEmp.SetDataBinding(dsData, "data").21 ODP.NET Ref Cursor Ref Cursor CREATE OR REPLACE PACKAGE SCOTT.pkg_ref2 AS CURSOR c1 IS SELECT * FROM emp; CURSOR c2 IS SELECT * FROM dept; TYPE empcur IS REF CURSOR RETURN c1%rowtype; TYPE deptcur IS REF CURSOR RETURN c2%rowtype; PROCEDURE GetEmpDeptData( ); END; EmpCursor in out empcur, DeptCursor in out deptcur CREATE OR REPLACE PACKAGE BODY SCOTT.pkg_ref2 AS PROCEDURE GetEmpDeptData( EmpCursor in out empcur, DeptCursor in out deptcur) IS BEGIN OPEN EmpCursor FOR SELECT * FROM emp; OPEN DeptCursor FOR SELECT * FROM dept; END GetEmpDeptData; END pkg_ref2;.22 Ref Cursor!?.NET Oracle oo4o ODP.NET 24

25 oo4o ODP.NET PL/SQL GetEmpDeptData emp dept oo4o Ref Cursor Dim ssql As String Dim OraSession As New OraSessionClass Dim OraDatabase As OraDatabase Set OraDatabase = OraSession.OpenDatabase( _ "orcl", "scott/tiger", dboption.oradb_default) OraDatabase.Parameters.Add "EmpCursor", 0, ORAPARM_OUTPUT OraDatabase.Parameters("EmpCursor").serverType = ORATYPE_CURSOR OraDatabase.Parameters.Add "DeptCursor", 0, ORAPARM_OUTPUT OraDatabase.Parameters("DeptCursor").serverType = ORATYPE_CURSOR ssql = "Begin pkg_ref2.getempdeptdata(:empcursor,:deptcursor); end;" Set OraSqlStmt = OraDatabase.CreateSql(sSql, ORASQL_FAILEXEC) Set EmpDynaset = OraDatabase.Parameters("EmpCursor").Value Set DeptDynaset = OraDatabase.Parameters("DeptCursor").Value Do While Not EmpDynaset.EOF Debug.Print EmpDynaset("ename") Loop EmpDynaset.MoveNext Do While Not DeptDynaset.EOF Debug.Print DeptDynaset("dname") Loop DeptDynaset.MoveNext OraDatabase.Parameters.Remove ("EmpCursor") OraDatabase.Parameters.Remove ("DeptCursor").23 oo4o Ref Cursor!?.NET Oracle oo4o ODP.NET 25

26 oo4o ODP.NET ODP.NET Ref Cursor Dim conn As New OracleConnection("User Id=Scott;Password=Tiger;Data Source=orcl") Dim cmd As New OracleCommand("pkg_ref2.GetEmpDeptData", conn) cmd.commandtype = CommandType.StoredProcedure 'REF CURSOR cmd.parameters.add("empcursor", OracleDbType.RefCursor, ParameterDirection.Output) cmd.parameters.add("deptcursor", OracleDbType.RefCursor, ParameterDirection.Output) cmd.executenonquery() 'SQL Ref Cursor Dim dr1 As OracleDataReader = _ CType(cmd.Parameters(0).Value, OracleRefCursor).GetDataReader Dim dr2 As OracleDataReader = _ CType(cmd.Parameters(1).Value, OracleRefCursor).GetDataReader 'RefCursor Do While dr1.read Debug.WriteLine(dr1("ename")) Loop Do While dr2.read Debug.WriteLine(dr2("dname")) Loop.24 ODP Ref Cursor ( ) Ref Cursor Dim conn As New OracleConnection("User Id=Scott;Password=Tiger;Data Source=orcl") Dim cmd As New OracleCommand("pkg_ref2.GetEmpDeptData", conn) cmd.commandtype = CommandType.StoredProcedure 'REF CURSOR cmd.parameters.add("empcursor", OracleDbType.RefCursor, ParameterDirection.Output) cmd.parameters.add("deptcursor", OracleDbType.RefCursor, ParameterDirection.Output) 'SQL Ref Cursor Dim dsdata As New DataSet!?.NET Oracle oo4o ODP.NET 26

27 oo4o ODP.NET Dim da As New OracleDataAdapter(cmd) da.fill(dsdata, "data") 'Grid DataGridEmp.SetDataBinding(dsData, "data") DataGridDept.SetDataBinding(dsData, "data1").25 ODP.NET Ref Cursor ( ) oo4o ODP.NET oo4o '--- Dim OraSession As New OraSessionClass Dim OraDatabase As OraDatabase Set OraDatabase = OraSession.OpenDatabase( _ "orcl", "scott/tiger", dboption.oradb_default) ' OraSession.BeginTrans OraDatabase.ExecuteSQL "insert into emp(empno,ename) values(6,'michel')" OraSession.CommitTrans.26 oo4o oo4o OraSession ODP.NET OracleConnection OracleConnection BeginTransaction '--- cnn.open() Dim cmd As New OracleCommand cmd.connection = cnn ' Dim txn As OracleTransaction = cnn.begintransaction() cmd.commandtext = "insert into emp(empno,ename) values(6,'michel')" cmd.commandtype = CommandType.Text cmd.executenonquery() txn.commit()!?.net Oracle oo4o ODP.NET 27

28 oo4o ODP.NET.27 ODP.NET ODP.NET SavePoint Dim cnn As New OracleConnection cnn.connectionstring = "user id=scott;password=tiger;data source=orcl" cnn.open() ' Dim txn As OracleTransaction = cnn.begintransaction() Dim strsql1 As String = "INSERT INTO emp (empno, ename) VALUES (1,'Employee1')" Dim mycmd As New OracleCommand(strSQL1, cnn) Dim res As Integer = mycmd.executenonquery() SavePoint a txn.save("a") Dim strsql2 As String = "INSERT INTO emp (empno, ename) VALUES (2,'Employee2')" Dim mycmd2 As New OracleCommand(strSQL2, cnn) Dim res2 As Integer = mycmd2.executenonquery() SavePoint b txn.save("b") SavePoint a txn.rollback("a") txn.commit().28 ODP.NET (SavePoint ) COM+ oo4o COM COM COM!?.NET Oracle oo4o ODP.NET 28

29 oo4o ODP.NET ODP.NET COM+ ODP.NET COM+ Oracle Services for Microsoft Transaction Server(OraMTS) COM+ Microsoft COM+ Microsoft MSDN.NET COM+ XML WEB COM+!?.NET Oracle!?.NET Oracle oo4o ODP.NET 29

30 oo4o ODP.NET ADO Visual Basic 6.0( VB) Oracle oo4o ADO VB ADO Oracle.NET ADO ODP.NET ADO RecordSet Movenext Do Until RecordSet.Eof RecordSet.Movenext Loop.29 ADO RecordSet ODP.NET OracleDataReader ADO RecordSet ReadOnly, FowardOnly. NET ADO ODP.NET oo4o ODP.NET OracleDataAdapter DataSet DataSet!?.NET Oracle oo4o ODP.NET 30

31 oo4o ODP.NET ADO ODP.NET ADO.NET Visual Studio.NET Visual Basic 6.0 Visual Basic.NET Vbupgrade.exe Visual Basic 6.0 Visual Studio.NET Visual Basic 6.0 Visual Basic.NET Visual Basic 6.0 Oracle Visual Besic 6.0 DataGrid emp!?.net Oracle oo4o ODP.NET 31

32 oo4o ODP.NET Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Set conn = New ADODB.Connection conn.cursorlocation = aduseclient conn.connectionstring = _ "Provider=OraOLEDB.Oracle.1;User ID=scott;Password=tiger;Data Source=orcl;" conn.open Set rs = conn.execute("select * FROM emp",, adcmdtext) Set Me.DataGrid1.DataSource = rs.30.net VB Visual Studio.NET VB.NET Dim conn As ADODB.Connection Dim rs As ADODB.Recordset conn = New ADODB.Connection conn.cursorlocation = ADODB.CursorLocationEnum.adUseClient conn.connectionstring = _ "Provider=OraOLEDB.Oracle.1;User ID=scott;Password=tiger;Data Source=orcl;"!?.NET Oracle oo4o ODP.NET 32

33 oo4o ODP.NET conn.open() rs = conn.execute("select * FROM emp",, ADODB.CommandTypeEnum.adCmdText) Me.DataGrid1.DataSource = rs.31.net ADO.NET ADO ADO DataGrid Visual Basic 6.0 Visual Basic.NET ADO Visual Basic 6.0.NET Framework ADO.NET.NET ODP.NET Dim conn As New OracleConnection( _ "User Id=Scott;Password=Tiger;Data Source=orcl") Dim cmd As New OracleCommand("Select * from emp", conn) Dim adp As New OracleDataAdapter(cmd) Dim ds As New DataSet adp.fill(ds, "emplist") DataGrid1.SetDataBinding(ds, "emplist").32 ADO ODP.NET!?.NET Oracle oo4o ODP.NET 33

34 oo4o ODP.NET Visual Basic 6.0 DataGrid Visual Studio.NET DataGrid DataGrid!?.NET Oracle oo4o ODP.NET 34

35 oo4o ODP.NET Copyright 2005 Oracle Corporation Japan. All Rights Reserved. Oracle Oracle Corporation Oracle Corporation!?.NET Oracle oo4o ODP.NET 35