Oracle Lite Tutorial

Size: px

Start display at page:

Download "Oracle Lite Tutorial"

かげたつとくやす
6 years ago
Views:

1 GrapeCity -.NET with GrapeCity - SPREAD Creation Date: Nov. 30, 2005 Last Update: Nov. 30, 2005 Version: 1.0

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

3 GrapeCity Microsoft Visual Studio.NET VB.NET Oracle Tips SPREAD SPREAD Oracle.NET Oracle Oracle Data Provider for.net( ODP.NET)!?.NET Oracle.NET GrapeCity 1. ActiveReports 2. InputMan 3. SPREAD 4. FlexGrid GrapeCity SPREAD 3 1. SPREAD SPREAD ODP.NET GrapeCity OS Microsoft Windows 2000 Professional + SP4 RDBMS Oracle Database 10g Release 2 Standard Edition for Windows!?.NET Oracle 3

4 OS Microsoft Windows 2000 Professional + SP4 Microsoft Visual Studio.NET 2003 GrapeCity SPREAD for.net Windows Forms Edition SPREAD for.net 2.0J Web Forms Edition!?.NET Oracle 4

5 SPREAD SPREAD IME SPREAD ADO.NET.NET Oracle Oracle Data Provider for.net( ODP.NET) SPREAD IDE SPREAD SPREAD SPREAD URL.NET Oracle 5

6 SPREAD SPREAD for.net Windows Forms Edition SPREAD for.net 2.0J Web Forms Edition (sample.zip) CONFIG WebForms WindowsForm CONFIG WebForms ASP.NET WindowsForm VB.NET Windows Oracle Oracle sample.zip CONFIG gen_spread.sql SQL*PLUS C: >sqlplus /nolog SQL*Plus: Release Production on :43: Copyright (c) 1982, 2005, Oracle. All rights reserved. Oracle 6

7 system : 1 1 SQL> gen_spread.sql system Oracle IIS Internet Information Services(IIS) ( ) sample.zip WebForms Internet Information Services(IIS) sample.zip C: SPREAD!?.NET Oracle 7

8 WebForms ASP.NET WEB IIS 1. Windows IIS WebForms [ ] WEB!?.NET Oracle 8

9 SpreadTestWeb OK IIS SPREAD GrapeCity SPREAD sample.zip SPREAD VS.NET SpreadTest.sln VS.NET SpreadTest.sln!?.NET Oracle 9

10 SpreadTestWeb ASP.NET WEB SpreadTestWin Windows Oracle SpreadTestWeb Web.config SpreadTestWin App.config!?.NET Oracle 10

XML <appsettings> connectionstring data source tnsnames.ora <add key="connectionstring" value="user id=spread;password=oracle;data source=orcl10gr2" /> tnsnames.

11 XML <appsettings> connectionstring data source tnsnames.ora <add key="connectionstring" value="user id=spread;password=oracle;data source=orcl10gr2" /> tnsnames.ora Oracle Database Net Services 10g av/portal_3.htm ASP.NET WEB VS.NET SpreadTestWeb A VS.NET (D) (S)!?.NET Oracle 11

12 SPREAD WEB Windows VS.NET SpreadTestWin A VS.NET (D) (S)!?.NET Oracle 12

13 SPREAD Windows SPREAD ODP.NET SPREAD ODP.NET SPREAD ODP.NET Oracle DataSet SPREAD Oracle DataSet DataSet SPREAD SPREAD Windows ASP.NET WEB SPREAD Windows ASP.NET WEB DataSet Oracle DataSet 1. Oracle Oracle Oracle Oracle Oracle Developer Tools for Visual Studio.NET( ODT) ODT Oracle!?.NET Oracle 13

14 Oracle Oracle (A) Oracle < > spread oracle tnsnames.ora (T)!?.NET Oracle 14

15 OK Oracle 2. DataAdapter Oracle DataAdapter DataSet DataAdapter ODT!?.NET Oracle 15

16 (Y) DataAdapter DataAdapter Connection Oracle DataAdapter SselectCommand CommandText SQL 3. DataSet DataAdapter DataSet DataAdapter DataSet!?.NET Oracle 16

17 DataSet ODT DataSet XML xsd ODT DataAdapter DataSet OracleDataAdapter1.Fill( 11. ) DataAdapter Fill DataSet DataSet ODT DataSet SPREAD!?.NET Oracle 17

18 DataSet SPREAD SPREAD Windows SPREAD DataMember DataSource 11 VS (D) (S)!?.NET Oracle 18

DataSet SPREAD BLOB BLOB SPREAD BLOB SPREAD BLOB SPREAD Windows ASP.NET WEB BLOB Windows Windows BLOB SPREAD ' SPREAD Private Sub ShowphotoInSpread(ByVal row As Integer) ' If Not IsDBNull(ds.BlobTbl.

19 DataSet SPREAD BLOB BLOB SPREAD BLOB SPREAD BLOB SPREAD Windows ASP.NET WEB BLOB Windows Windows BLOB SPREAD ' SPREAD Private Sub ShowphotoInSpread(ByVal row As Integer) ' If Not IsDBNull(ds.BlobTbl.Rows(row).Item(3)) Then 'DataSet Byte Dim imgdata As Byte() = CType(ds.BlobTbl.Rows(row).Item(3), Byte()) ' Dim stmpic As New MemoryStream(imgData) Dim imgtype As New FarPoint.Win.Spread.CellType.GeneralCellType imgtype.backgroundimage = _ New FarPoint.Win.Picture(Image.FromStream(stmPic)) FpSpread1.Sheets(0).Cells(row, 3).CellType = imgtype!?.net Oracle 19

End If End Sub.1 frmblob.vb - Sub ShowphotoInSpread SPREAD BackgoundImage SPREAD ASP.NET WEB BLOB ASP.NET WEB ' Spread Private Sub ShowphotoInSpread(ByVal row As Integer) ' If Not IsDBNull(ds.BlobTbl.

20 End If End Sub.1 frmblob.vb - Sub ShowphotoInSpread SPREAD BackgoundImage SPREAD ASP.NET WEB BLOB ASP.NET WEB ' Spread Private Sub ShowphotoInSpread(ByVal row As Integer) ' If Not IsDBNull(ds.BlobTbl.Rows(row).Item(3)) Then 'DataSet Byte Dim imgdata As Byte() = CType(ds.BlobTbl.Rows(row).Item(3), Byte()) ' Dim strusercode As String = get_user_code_by_row(row) Dim imgfilephypath As String = Server.MapPath(".") & " " & _ strusercode & ".gif" Dim imgfilelogpath As String = strusercode & ".gif" Dim imgfile As New System.IO.FileStream(imgfilePhyPath, IO.FileMode.Create) imgfile.write(imgdata, 0, imgdata.length) imgfile.close()!?.net Oracle 20

' FpSpread1.ActiveSheetView.Cells(row, 3).CellType = _ New FarPoint.Web.Spread.ImageCellType(imgfileLogPath) FpSpread1.ActiveSheetView.Cells(row, 3).Text = "" End If End Sub.

21 ' FpSpread1.ActiveSheetView.Cells(row, 3).CellType = _ New FarPoint.Web.Spread.ImageCellType(imgfileLogPath) FpSpread1.ActiveSheetView.Cells(row, 3).Text = "" End If End Sub.2 blobtest.aspx.vb - Sub ShowphotoInSpread ASP.NET WEB BLOB SPREAD BLOB SPREAD ImageCellType URL BLOB BFILE Windows SPREAD ASP.NET WEB SPREAD (REF CURSOR) PL/SQL PL/SQL SPREAD!?.NET Oracle 21

22 PL/SQL PL/SQL PL/SQL SQL gen_spread.sql CREATE OR REPLACE PACKAGE SPREAD.REFCUR_TEST AS TYPE UserCurTyp IS REF CURSOR RETURN %ROWTYPE; CURSOR c1 IS SELECT * FROM ; TYPE CompCurTyp IS REF CURSOR RETURN c1%rowtype; PROCEDURE open_cv(user_cv IN OUT UserCurTyp, comp_cv IN OUT CompCurTyp); END; / CREATE OR REPLACE PACKAGE BODY SPREAD.REFCUR_TEST AS PROCEDURE open_cv(user_cv IN OUT UserCurTyp, comp_cv IN OUT CompCurTyp) IS BEGIN OPEN user_cv FOR SELECT * FROM ; OPEN comp_cv FOR SELECT * FROM ; END open_cv; END; / SPREAD SPREAD SPREAD Sheets!?.NET Oracle 22

23 (A) SPREAD Imports Oracle.DataAccess.Client Imports Oracle.DataAccess.Types Imports System.Configuration Public Class fmrefcursor Inherits System.Windows.Forms.Form #Region " Windows " #End Region!?.NET Oracle 23

24 Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Dim conn As New OracleConnection( _ ConfigurationSettings.AppSettings("connectionString")) ' Dim cmd As New OracleCommand("REFCUR_TEST.open_cv", conn) cmd.commandtype = CommandType.StoredProcedure 'REF CURSOR cmd.parameters.add("user_cv", _ OracleDbType.RefCursor, ParameterDirection.Output) cmd.parameters.add("comp_cv", _ OracleDbType.RefCursor, ParameterDirection.Output) 'DataSet Dim ds As New DataSet Dim da As New OracleDataAdapter(cmd) da.fill(ds, "data") ' With FpSpread1.Sheets(0).DataSource = ds.datamember = "data" End With With FpSpread1.Sheets(1).DataSource = ds.datamember = "data1" End With conn.close() conn.dispose()!?.net Oracle 24

25 End Sub End Class.3 frmrefcursor.vb - Sub Form1_Load SPREAD Oracle SPREAD!?.NET Oracle 25

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