GrapeCity -.NET with GrapeCity - SPREAD 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 Log Nov. 30, 2005 Hiroshi Ota Created. Reviewers Name Position Approvals <Approver 1> <Approver 2> Distribution Copy No. Name Location!?.NET Oracle 2
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 2. 3. SPREAD ODP.NET GrapeCity OS Microsoft Windows 2000 Professional + SP4 RDBMS Oracle Database 10g Release 2 Standard Edition for Windows!?.NET Oracle 3
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
SPREAD SPREAD IME SPREAD ADO.NET.NET Oracle Oracle Data Provider for.net( ODP.NET) SPREAD IDE SPREAD SPREAD SPREAD URL.NET http://www.grapecity.com/japan/support/database/dotnet_productlist.htm!?.net Oracle 5
SPREAD 2006 1 SPREAD for.net Windows Forms Edition SPREAD for.net 2.0J Web Forms Edition http://otndnld.oracle.co.jp/easy/dotnet/spread/sample.zip (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 10.2.0.1.0 - Production on 2 2 10:43:52 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> @gen_spread!?.net Oracle 6
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
WebForms ASP.NET WEB IIS 1. Windows IIS WebForms [ ] WEB!?.NET Oracle 8
SpreadTestWeb OK IIS SPREAD GrapeCity SPREAD sample.zip SPREAD VS.NET SpreadTest.sln VS.NET SpreadTest.sln!?.NET Oracle 9
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.ora Oracle Database Net Services 10g 2 10.2 http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_v8/n av/portal_3.htm ASP.NET WEB VS.NET SpreadTestWeb A VS.NET (D) (S)!?.NET Oracle 11
SPREAD WEB Windows VS.NET SpreadTestWin A VS.NET (D) (S)!?.NET Oracle 12
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
Oracle Oracle (A) Oracle < > spread oracle tnsnames.ora (T)!?.NET Oracle 14
OK Oracle 2. DataAdapter Oracle DataAdapter DataSet DataAdapter ODT!?.NET Oracle 15
(Y) DataAdapter DataAdapter Connection Oracle DataAdapter SselectCommand CommandText SQL 3. DataSet DataAdapter DataSet DataAdapter DataSet!?.NET Oracle 16
DataSet ODT DataSet XML xsd ODT DataAdapter DataSet OracleDataAdapter1.Fill( 11. ) DataAdapter Fill DataSet DataSet ODT DataSet SPREAD!?.NET Oracle 17
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.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.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.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
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
(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
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
End Sub End Class.3 frmrefcursor.vb - Sub Form1_Load SPREAD Oracle SPREAD!?.NET Oracle 25