4.19 Build a Sample Oracle Database Project -OracleSelectRTObject

1
4.19 Build a Sample Oracle Database Project
-OracleSelectRTObject

• For convenience, in this section we install the
  Oracle Database 10g Express Edition in our local
  computer. It would be no difference whether the
  Oracle server is installed in the local or a
  remote computer for this sample project. The
  Oracle database used in this sample project is
  Oracle Database 10g Express Edition that was
  developed in Chapter 2.
• 4.19.1 Install the Oracle Database 10g Express
  Edition
•  Oracle Database 10g Express Edition (Oracle
  Database XE) is an entry-level, small footprint
  starter database with the following advantages
• Free to download and install on your local
  computer or remote computers
• Free to develop deploy data-driven applications
• Free to distribute (including ISVs)

2
4.19.1 Install the Oracle Database 10g Express
Edition

• Oracle Database XE is built using the same code
  base as Oracle Database 10g Release 2 product
  line - Standard Edition One, Standard Edition,
  and Enterprise Edition, and is available on
  32-bit Windows and Linux.
• Although there are limitations existed for the
  Oracle Database 10g XE, such as up to 4 GB upper
  bound of the user data and the single instance
  only on any server, it is still an ideal and
  convenient tool to develop professional and
  leading edge data-driven applications
• The Oracle Database 10g XE can be easily upgraded
  to Standard Edition One, Standard Edition and
  Enterprise Edition
• Any application developed for Oracle Database XE
  will run completely unchanged with Oracle
  Database 10g Standard Edition One, Standard
  Edition, or Enterprise Edition, and the
  application development investment is guaranteed.
  
• With Oracle Database XE, ISVs have the industrys
  leading database technology to power their
  applications. Distributing Oracle Database XE in
  their applications or products without additional
  costs.
• Oracle Database XE can be freely distributed as a
  standalone database or as part of a third-party
  application or product.

3
4.19.1 Install the Oracle Database 10g Express
Edition -2

• For most applications, you only need to download
  and install the Oracle Database XE Server
  component, since it provides both an Oracle
  database and tools for managing this database. It
  also includes the Client component of Oracle
  Database XE, so that you can connect to the
  database from the same computer on which you
  installed the Server, and then administer the
  database and develop Visual Studio.NET
  applications.

4
4.19.2 Configure the Oracle Database Connection
String

• There are different ways to build a connection
  string for the Oracle database connection. One
  way is to
• Use the database alias defined in the
  tnsnames.ora file. This file is created
  automatically after you install the Oracle
  database 10g XE. During the installation process,
  you will be prompted to enter your username and
  password. Normally the username is SYSTEM or
  SYS, which is defined by the Oracle system.
• Select your password.
• Remember, you need these two pieces of
  information to access your database each time as
  you want to create, edit and manipulate your
  database in the future.
• In order to use the database alias defined in the
  tnsnames.ora file, first you need to check the
  content of this definition. This file should be
  located at the folder C\oraclexe\app\oracle\produ
  ct\10.2.0\server\NETWORK\ADMIN after the Oracle
  Database 10g XE is installed. Use NotePad to open
  it.

5
4.19.2 Configure the Oracle Database Connection
String -2

• Close this file and now lets create our
  connection string for the Oracle database 10g XE
  using the database alias XE.
• The connection string can be defined as
•  
• Dim oraString As String "Data SourceXE" _
• 
  "User IDsystem" _
• 
  "Passwordreback"
• where the password reback is the password we
  used when we installed the Oracle Database 10g XE
  in our computer.
• Another way to create the connection string is to
  copy the top block from the tnsnames.ora file and
  paste it as the value of the Data Source
  parameter, which is
•  
• Dim oraString As String "Data
  Source(DESCRIPTION" _
• "(ADDRESS(PROTOCOLTCP)(HOSTsusan)(PORT1
  521))" _
• "(CONNECT_DATA(SERVERDEDICATED)(SERVICE_N
  AME XE)" _
• "User IDsystemPasswordreback
• where the HOST susan means that susan is the
  computers name.

6
4.19.3 Query Data Using Runtime Objects for the
LogIn Form

• Open Visual Studio.NET 2005 and create a new
  Windows-Based project named OracleSelectRTObject.
  
• Delete the default form Form1.
• Add five form windows from the last project.
  Refer to section 4.18.2 to add those five forms.
• Modify the coding for the file Application.Designe
  r.vb. Make sure that the LogIn form is the start
  form in this project by checking the
  ProjectOracleSelectRTObject Properties window.
• Open the Oracle Client namespace in which the
  Oracle Data Provider and associated classes are
  located, and make this namespace as a reference
  for our project.
• Right-click on the OracleSelectRTObject from the
  Solution Explorer window and select the Add
  Reference item from the popup menu.
• Scroll down the list until you find the item
  System.Data.OracleClient, click it to select it
  and click the OK to add this reference to our
  project.

7
4.19.3.1 Declare the Runtime Objects

• All components related to the Oracle Data
  Provider supplied by ADO.NET are located at the
  namespace System.Data.OracleClient. To access the
  Oracle database, you need to use this Data
  Provider. You must first declare this namespace
  at the top line of your code window to allow
  Visual Basic.NET 2005 to know that you want to
  use this specified Data Provider.
• Open the LogIn form and enter the following codes
  into the Code Window that is shown in Figure
  4-135.
• A new instance of the OracleConnection class is
  created with the accessing mode of Public, which
  means that we want to use this connection object
  for our whole project.

8
4.19.3.2 Connect to the Data Source with the
Runtime Object

• Since the connection job is the first thing you
  need to do before you can make any data query,
  you need to do the connection job in the
  LogInForm_Load() event procedure, to allow the
  connection to be made first as your project runs.
• To simplify the coding in this part, change all
  prefixes sql, which is preceded in front of
  each Data Provider-dependent objects such as
  sqlConnection and sqlExceptionErr, to ora to
  get a new group of Oracle-related objects such as
  oraConnection and oraExceptionErr.

9
4.19.3.3 Coding Method 1 Using the DataAdapter
to Query Data
10
4.19.3.4 Coding Method 2 Using the DataReader
to Query Data
11
4.19.4 The Coding for the Selection form

• Most coding in this form is identical with the
  coding of the Selection form in the last project.
  The only difference is the coding for the Exit
  command button.
• In this project we used an Oracle database so the
  connection object should be preceded by a prefix
  ora. When the Exit button is clicked, we need
  to check whether the connection object has been
  closed and released. Since the connection object
  is created in the LogIn class, the connection
  object should be preceded by the class name
  LogIn.
• The only modification you need to do is to change
  the prefix sql to ora for the connection
  instance, as shown in Figure 4-139.

12
4.19.5 Query Data Using Runtime Objects for the
Faculty Form

• First lets take a look at the coding for the
  FacultyForm_Load() event procedure. The
  differences between this coding with the coding
  in the last project are
• Change the namespace to System.Data.OracleClient
• The size of the object array FacultyLabel is
  reduced to 5
• The prefix of the connection object is changed to
  ora since an Oracle Data Provider is utilized
  in the project.

13
The Coding for the Select Button Event Procedure
14
The Coding Modifications to Three Subroutines

• For three subroutines, FillFacultyTable(),
  FillFacultyReader() and MapFacultyTable(), only a
  little modifications are made.
• For the first two subroutines, the first
  modification is to change the upper bound index
  from 6 to 4 since we reduced the queried columns
  from 7 to 5. Then change the nominal arguments
  type from SqlDataReader to OracleDataReader for
  the FillFacultyReader() subroutine. Figures 4-142
  (A) and 4-143 (A) and (B) show these
  modifications.

15
The Coding Modifications to Three Subroutines -
2

• The modification made for the subroutine
  MapFacultyTable() is to shift the order index of
  the Label object array since the size of that
  array is reduced from 7 to 5 (the first two
  columns are not used).
• The coding for the subroutine ShowFaculty() and
  the Back button event procedure is identical to
  those coding we did for the last project.

16
4.19.6 Query Data Using Runtime Objects for the
Course Form

• The Query Method selection box allows users to
  use either the DataAdapter method or the
  DataReader method to make data query from the
  Course table.
• First lets do the coding for the
  CourseForm_Load() event procedure.

17
4.19.7 The Stored Procedures in Oracle Database
Environment

• Oracle also provides many methods to create
  stored procedures. For example, one can use the
  Object Browser page or SQL Commands page in the
  Oracle Database 10g Express Edition to create
  stored procedures.
• In Oracle database, if a stored procedure needs
  to return data such that a stored procedure needs
  to execute an SELECT statement, that stored
  procedure must be embedded into a package.
• The package in Oracle is a class and it can
  contain variables, functions and procedures.
• The stored procedures that dont need to return
  any data (by executing the INSERT, UPDATE and
  DELETE statements) can be considered as a pure
  stored procedure.
• The stored procedures that need to return data
  (by executing the SELECT statement) must be
  embedded into the package and therefore a package
  should be used.

18
4.19.7.1 The Syntax of Creating a Stored
Procedure in Oracle

• The syntax of creating a stored procedure in the
  Oracle is
• The keyword REPLACE is used for the modified
  stored procedures. Followed the procedures name,
  all input or output parameters are declared
  inside the braces. After the keyword AS, the
  stored procedures body is displayed.
• An example of a stored procedure in Oracle is

19
4.19.7.2 The Syntax of Creating a Package in
Oracle

• To create stored procedure that returns data, one
  need to embedded the stored procedure into a
  package. The syntax of creating a package is
• An example of a package in Oracle is

20
4.19.8 Create the Faculty_Course Package for the
Course Form

• Open the Oracle Database 10g XE home page by
  going to StartAll ProgramsOracle Database 10g
  Express EditionGo To Database Home Page items.
  Click the Object Browser and select
  CreatePackage item to open the Create Package
  window.
• Each package has two parts The definition or
  specification part and the body part. Lets
  create the specification part by checking the
  Specification radio button and click Next button
  to open the Name page.
• Enter the package name Faculty_Course into the
  name box and click the Next button to go to the
  specification page.
• Remove the default function prototype, and change
  the default procedure name from the test to our
  procedure name SelectFacultyCourse.
• In line 2, we defined the returned data type as a
  CURSOR_TYPE by using
• TYPE CURSOR_TYPE IS REF CURSOR 
• since you must use a cursor to return a
  group of data and IS operator is equivalent to an
  equal operator.

21
4.19.8 Create the Faculty_Course Package for the
Course Form -2

• Your finished coding for the specification page
  should match one that is shown in Figure 4-150.
• You can click the Compile button to compile this
  specification block if you like. Click the Finish
  button to complete this step.
• Next we need to create the body block of this
  package. Click the Body tab to open the Body
  page.

22
4.19.8 Create the Faculty_Course Package for the
Course Form -3

• Click the Edit button to begin to create our body
  part. Enter the following PL-SQL codes into this
  body, which is shown in Figure 4-152.
• The procedure prototype is re-declared in line 2.
  But an IS operator is attached at the end of this
  prototype and it is used to replace the AS
  operator to indicate that this procedure needs to
  use a local variable facultyId, and this variable
  will work as an intermediate variable to hold the
  returned faculty_id from the first query that is
  located at line 6.

23
4.19.8 Create the Faculty_Course Package for the
Course Form -4

• Starting from the BEGIN, our real SQL statements
  are included in lines 6 and 7. The first query is
  to get the faculty_id from the Faculty table
  based on the input parameter FacultyName, which
  is the first argument of this procedure. An
  SELECTINTO statement is utilized to temporarily
  store the returned faculty_id into the
  intermediate variable facultyId.
• The OPEN FacultyCourse FOR command is used to
  assign the returned data columns from the second
  query to the cursor variable FacultyCourse.
  Recall that we used a SET command to perform this
  assignment functionality in the SQL Server stored
  procedure in section 4.18.9.
• Starting from lines 9 and 10, the second query is
  declared, and it is to get all course_id and
  courses taught by the selected faculty from the
  Course table based on the intermediate variables
  value, faculty_id, which is obtained from the
  first query above. The queried results are
  assigned to the cursor variable FacultyCourse.
• Click the Compile button to compile our package.
  A successful compiling information should be
  displayed if no error.

24
4.19.9 Query Data Using the Oracle Package for
the Course Form
25
Modification to Subroutines and Event Procedures

• The subroutine FillCourseTable() and the Back
  button event procedure have nothing to do with
  any object used in this project, so no coding
  modification is needed. The subroutine
  FillCourseReader() needs only one small
  modification, which is to change the nominal
  arguments type to OracleDataReader since now we
  are using an Oracle data provider.

26
The Coding for the CourseList_SelectedIndexChanged
Event Procedure
27
4.20 Chapter Summary

• The main topic of this chapter is to develop
  professional data-driven applications in Visual
  Basic.NET 2005 by using two methods.
• Using Design Tools and Wizards to develop data
  driven applications
• Using Runtime Object to develop data driven
  applications
• The first method is simple, and it is easy to be
  understood and learned by those students who are
  beginner to Visual Basic.NET and databases. This
  method utilizes a lot of powerful tools and
  wizards provided by Visual Basic.NET 2005 and
  ADO.NET to simplify the coding process, and most
  of codes are auto-generated by the .NET Framework
  and Visual Basic.NET 2005.
• The runtime objects are utilized in the second
  method. This method allows users to dynamically
  create all data-related objects and perform the
  associated data operations after the project
  runs. Because all objects are generated by the
  coding, it is very easy to translate and execute
  this kind of projects in other platforms.

28
4.20 Chapter Summary - 2

• By finishing the Part I in this chapter, you
  should be able to
• Use Design Tools and Wizards to develop the
  simple but powerful data-driven applications to
  perform data query to Microsoft Access, SQL
  Server 2005 and Oracle databases.
• Use OleDbConnection, SqlConnection or
  OracleConnection class to connect to Microsoft
  Access, SQL Server 2005 Express and Oracle 10g XE
  databases.
• Perform data binding to a DataGridView using two
  methods.
• Use OleDbCommand, SqlCommand and OracleCommand
  class to execute the data query with dynamic
  parameters to three kinds of databases.
• Use the OleDbDataAdapter to fill a DataSet and a
  DataTable object with three kinds of databases.
• Use the OleDbDataReader class to query and
  process data with three kinds of databases.
• Set properties for the OleDbCommand objects to
  construct a desired query string for three kinds
  of databases.

29
4.20 Chapter Summary - 3

• By finishing the Part II in this chapter, you
  should be able to
• Use the Runtime Objects to develop the
  professional data-driven applications to perform
  data query to Microsoft Access, SQL Server 2005
  and Oracle databases.
• Use the OleDbConnection, SqlConnection and
  OracleConnection class to dynamically connect to
  Microsoft Access, SQL Server 2005 Express and
  Oracle 10g XE databases.
• Use the OleDbCommand, SqlCommand and
  OracleCommand class to dynamically execute the
  data query with dynamic parameters to three kinds
  of databases.
• Use the OleDbDataAdapter, SqlDataAdapter and
  OracleDataAdapter to dynamically fill a DataSet
  and a DataTable object with three kinds of
  databases.
• Use the OleDbDataReader, SqlDataReader and
  OracleDataReader class to dynamically query and
  process data with three kinds of databases.

30
4.20 Chapter Summary - 4

• By finishing the Part II in this chapter, you
  should be able to
• Set properties for the OleDbCommand, SqlCommand
  and OracleCommand objects dynamically to
  construct a desired query string for three kinds
  of databases.
• Use the Server Explorer to create, debug and test
  stored procedures in Visual Studio.NET
  environment.
• Use SQL stored procedure to perform the data
  query from Visual Basic.NET.
• Use the SQL nested stored procedure to perform
  the data query from Visual Basic.NET.
• Use Object Browser in Oracle Database 10g XE to
  create, debug and test stored procedures and
  packages.
• Use the Oracle stored procedures and packages to
  perform the data query from Visual Basic.NET.

31
 Homework

• True/False Questions
• Multiple Choices
• Exercises