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 Log Nov. 30, 2005 Hiroshi Ota Created. Reviewers Name Position Approvals <Approver 1> <Approver 2> Distribution Copy No. Name Location!?.NET Oracle GrapeCity (FlexGrid) 2
GrapeCity Microsoft Visual Studio.NET VB.NET Oracle Tips FlexGrid FlexGrid.NET Oracle Oracle Data Provider for.net ODP.NET!?.NET Oracle.NET Oracle GrapeCity 1. ActiveReports 2. InputMan 3. SPREAD 4. FlexGrid GrapeCity FlexGrid 3 1. FlexGrid 2. 3. FlexGrid ODP.NET GrapeCity OS Microsoft Windows 2000 Professional + SP4 RDBMS Oracle Database 10g Release 2 Standard Edition for Windows!?.NET Oracle GrapeCity (FlexGrid) 3
OS Microsoft Windows 2000 Professional + SP4 Microsoft Visual Studio.NET 2003 GrapeCity FlexGrid for.net 2.0J!?.NET Oracle GrapeCity (FlexGrid) 4
FlexGrid FlexGrid Excel 100.NET Framework ADO.NET ADO.NET ODP.NET FlexGrid URL.NET http://www.grapecity.com/japan/support/database/dotnet_productlist.htm FlexGrid 2006 1 FlexGrid for.net 2.0J 2006 7 FlexGrid 2006 2 24 FlexGrid for.net 3.0J!?.NET Oracle GrapeCity (FlexGrid) 5
http://otndnld.oracle.co.jp/easy/dotnet/flex_grid/sample.zip sample.zip CONFIG WindowsForm CONFIG CONFIG first_copy_the_photos_to_c_temp C: Temp WindowsForm VB.NET Windows Oracle Oracle sample.zip CONFIG gen_flexgrid.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_flexgrid system :!?.NET Oracle GrapeCity (FlexGrid) 6
1 1 SQL> gen_flexgrid.sql system Oracle FlexGrid FlexGrid sample.zip WindowsForm VS.NET FlexGridTest.sln VS.NET FlexGridTest.sln 1 FlexGridTest Windows Oracle App.Config!?.NET Oracle GrapeCity (FlexGrid) 7
XML <appsettings> connectionstring data source tnsnames.ora <add key="connectionstring" value="user id=flexgrid;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_ cd/index.htm VS.NET (D) (S)!?.NET Oracle GrapeCity (FlexGrid) 8
FlexGrid Windows FlexGrid ODP.NET FlexGrid ODP.NET FlexGrid ODP.NET Oracle DataSet FlexGrid Oracle DataSet DataSet FlexGrid FlexGrid Windows ASP.NET WEB FlexGrid DataSet Oracle DataSet 1. Oracle Oracle Oracle Oracle Oracle Developer Tools for Visual Studio.NET ODT ODT Oracle Oracle Oracle (A)!?.NET Oracle GrapeCity (FlexGrid) 9
Oracle < > flexgrid oracle tnsnames.ora (T)!?.NET Oracle GrapeCity (FlexGrid) 10
OK Oracle 2. DataAdapter Oracle DataAdapter DataSet DataAdapter ODT!?.NET Oracle GrapeCity (FlexGrid) 11
(Y) DataAdapter DataAdapter Connection Oracle DataAdapter SelectCommand CommandText SQL 3. DataSet DataAdapter DataSet DataAdapter DataSet!?.NET Oracle GrapeCity (FlexGrid) 12
DataSet ODT DataSet XML xsd ODT DataAdapter DataSet OracleDataAdapter1.Fill( 11. ) DataAdapter Fill DataSet DataSet ODT DataSet FlexGrid!?.NET Oracle GrapeCity (FlexGrid) 13
DataSet FlexGrid FlexGrid Windows FlexGrid DataMember DataSource 11 VS.NET (D) (S)!?.NET Oracle GrapeCity (FlexGrid) 14
DataSet FlexGrid BLOB BLOB FlexGrid BLOB FlexGrid BLOB FlexGrid Windows BLOB Windows Windows BLOB FlexGrid ' OwnerDrawCell Private Sub fg_ownerdrawcell(byval sender As Object, _ ByVal e As C1.Win.C1FlexGrid.OwnerDrawCellEventArgs) Handles fg.ownerdrawcell If e.row >= fg.rows.fixed Then ' "Photo" If fg.cols(e.col).name = " " Then ' ID Dim userid As Integer = fg.getdata(e.row, 1) ' e.image = LoadImage(userID)!?.NET Oracle GrapeCity (FlexGrid) 15
' Text Null If Not (e.image Is Nothing) Then e.text = Nothing End If End If End If End Sub Function LoadImage(ByVal userid As Integer) As Image If IsDBNull(userID) Then Return Nothing End If ' Dim img As Image = Nothing Dim cmdimg As New OracleCommand Dim rdrimg As OracleDataReader ' Connection Open If Not blobtbloracleconnection1.state = ConnectionState.Open Then blobtbloracleconnection1.open() End If cmdimg.connection = blobtbloracleconnection1 cmdimg.commandtext = "select from BLOB_TBL where =:userid" cmdimg.parameters.add(new OracleParameter("userid", userid)) rdrimg = cmdimg.executereader If rdrimg.read Then Dim blob As OracleBlob = rdrimg.getoracleblob(0) Dim stmpic As New MemoryStream(blob.Value) 'MemoryStream 0 Nothing If Not stmpic.length > 0 Then rdrimg.close() blobtbloracleconnection1.close()!?.net Oracle GrapeCity (FlexGrid) 16
Return Nothing End If img = Image.FromStream(stmPic) End If rdrimg.close() blobtbloracleconnection1.close() Return img End Function.1 frmblob.vb - Sub LoadImage FlexGrid OwnerDrawCell BLOB REF CURSOR PL/SQL PL/SQL FlexGrid PL/SQL PL/SQL PL/SQL SQL gen_flexgrid.sql!?.net Oracle GrapeCity (FlexGrid) 17
CREATE OR REPLACE PACKAGE FLEXGRID.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 FLEXGRID.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; / FlexGrid!?.NET Oracle GrapeCity (FlexGrid) 18
FlexGrid Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Cnn.ConnectionString = ConfigurationSettings.AppSettings("connectionString") Cnn.Open() ' cmd = New OracleCommand("REFCUR_TEST.open_cv", Cnn) cmd.commandtype = CommandType.StoredProcedure 'REF CURSOR cmd.parameters.add("emp_cv", OracleDbType.RefCursor, ParameterDirection.Output) cmd.parameters.add("dept_cv", OracleDbType.RefCursor, ParameterDirection.Output) 'DataSet da = New OracleDataAdapter(cmd) da.fill(dslist, "data") 'FlexGrid fg.datasource = dslist.tables(0) Cnn.Close() Cnn.Dispose() End Sub Private Sub ButtonUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonUser.Click fg.datasource = dslist.tables(0) End Sub Private Sub ButtonComp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonComp.Click!?.NET Oracle GrapeCity (FlexGrid) 19
fg.datasource = dslist.tables(1) End Sub.2 frmrefcursor.vb - Sub Form1_Load 1 Oracle FlexGrid!?.NET Oracle GrapeCity (FlexGrid) 20