5.20 Query Data Using Runtime Objects to Oracle Database

423
The prototype of the procedure SelectFacultyCourse() is declared in line 3. Two
arguments are used for this procedure: input parameter FacultyName, which is indicated
as an input by using the keyword IN followed by the data type of VARCHAR2. The
output parameter is a cursor named FacultyCourse followed by the keyword OUT. Each
PL - SQL statement must end with a semicolon, and this rule also applies to the END
statement.

Figure 5.175
Opened Create Package window.

Figure 5.176
Name page of the Package window.
c05.indd 423c05.indd 423 2/11/2010 2:59:14 PM2/11/2010 2:59:14 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
424
Chapter 5 Data Selection Query with Visual C#.NET
Click on the Finish button to complete this step. You can click on the Compile button
to compile this specifi cation block if you like. Next we need to create the body block of
this package. Click on the Body tab to open the Body page, which is shown in Figure
5.179 .
Click on the Edit button to begin to create our body part. Enter the PL - SQL codes
into this body shown in Figure 5.180 .
The procedure prototype is redeclared 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 proce-
Figure 5.177
Opened Specifi cation page.
Figure 5.178

above. The queried results are assigned to the cursor variable FacultyCourse.
Now let ’ s compile our package by clicking on the Compile button. A successful
compiling information
PL/SQL code successfully compiled (22:20:06)
will be displayed if this package is bug free, which is shown in Figure 5.181 .
The development of our Oracle package is complete, and now let ’ s go to the Visual
Studio.NET to call this package to perform our course query for our Course form.
5.20.3.9 Query Data Using Oracle Package for Course Form
Open the Course form window and double - click on the Select button to open its Click
method and enter the codes shown in Figure 5.182 into this method.
Let ’ s take a look at this piece of code to see how it works.
A. The package query string is declared, and this string contains both the package ’ s name
(Faculty_Course) and the stored procedure ’ s name (Select FacultyCourse). All query
strings for the Oracle database package must follow this style. The package ’ s name and
the procedure ’ s name defi ned in this string must be identical with those names we used
when we created this package in the Object Browser in Oracle Database 10g XE. Otherwise
your calling to this package would fail.
Figure 5.181
Compiled coding for the body part of the package.
c05.indd 426c05.indd 426 2/11/2010 2:59:26 PM2/11/2010 2:59:26 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.20 Query Data Using Runtime Objects to Oracle Database

427
private void cmdSelect_Click(object sender, EventArgs e)
{
string cmdString = "Faculty_Course.SelectFacultyCourse";
OracleParameter paramFacultyName = new OracleParameter();
OracleParameter paramFacultyCourse = new OracleParameter();

{
oraDataReader = oraCommand.ExecuteReader();
if (oraDataReader.HasRows == true)
FillCourseReader(oraDataReader);
else
MessageBox.Show("No matched course found!");
oraDataReader.Close();
}
else //LINQ to DataSe
t Method is selected...
{
CourseDataAdapter.SelectCommand = oraCommand;
CourseDataAdapter.Fill(ds, "Course");
var courseinfo = (from ci in ds.Tables["Course"].AsEnumerable()
select ci);
CourseList.Items.Clear();
foreach (var cRow in courseinfo)
{
CourseList.Items.Add(cRow.Field<string>("course_id"));
}
ds.Clear();
}
CourseList.SelectedIndex = 0;
}
A
B
C
D
E
F

One important point is that the second parameter is an output parameter and its data type
is cursor, and the transmission direction of this parameter is output. So the Direction
property of this Parameter object must be clearly indicated by assigning the Output to it.
Otherwise, the procedure calling may encounter some error and this error is hard to debug.
E. The Command object is initialized by assigning the associated property, such as
the Connection, CommandType, and CommandText. The CommandType must be
StoredProcedure and the CommandText should be the query string we declared at the
beginning of this method (step A ).
F. Two initialized Parameter objects are added into the Command object, that is, they are
added into the Parameters collection property of the Command object.
G. If the user selected the DataAdapter method, the initialized Command object is assigned
to the SelectCommand property of the DataAdapter, and the Fill() method is executed to
fi ll the Course table. Basically, only at this moment, the Oracle package we developed is
called and two queries are executed. The returned columns should be stored in the Course
data table if this fi ll is successful.
H. If the Count property of the Course table is greater than 0, which means that at least one
row is fi lled into the table, the user - defi ned method FillCourseTable() is called to fi ll the
queried course_id into the CourseList box in the Course form window. Otherwise, an error
message is displayed to indicate that this fi ll has failed.
I. Some cleaning jobs are performed to release some data objects used for this query.
J. If the user selected the DataReader method, the ExecuteReader() method is executed to
invoke the DataReader to retrieve required columns and store the returned results into the
DataReader. If the property HasRows is true, which means that DataReader did read back
some rows, the user - defi ned method FillCourseReader() is called to fi ll the CourseList box
in the Course form window with the read rows. Otherwise, an error message is displayed.
K. Another cleaning job is performed to release the DataReader used for this query.
L. If the user selected the LINQ to DataSet method, the initialized Command object is
assigned to the SelectCommand property of the DataAdapter, and the Fill() method is
executed to initialize the DataSet with the stored procedure. This operation will not only
fi ll the Course table but also the Faculty table since there are two query operations in the

Without this line, multiple duplicated course_ids would be added and displayed in the
CourseList box since multiple duplicated query results are fi lled into the DataSet, that is,
into the Course table.
Q. This statement is very important and it is used to select the fi rst course_id in the CourseList
box as the default course as the Course form is opened. More important, this command
can work as a trigger event to trigger the CourseList box ’ s SelectedIndexChanged method
to display the detailed information related to that default course_id. The coding for that
method is our next job.
The coding for the FillCourseTable() and the Back button Click method have nothing
to do with any object used in this project. Thus, no coding modifi cation is needed. The
user - defi ned method FillCourseReader() needs only one small modifi cation, which is to
change the nominal argument ’ s type to OracleDataReader since now we are using an
Oracle data provider. The detailed explanations for methods FillCourseTable() and
FillCourseReader() can be found in Figure 5.92 . For your convenience, we list this piece
of code with some explanations again, which is shown in Figure 5.183 .
Let ’ s see how this piece of code works.
A. Before we can fi ll the CourseList box, a cleaning job is needed. This cleaning is very
important. Otherwise multiple repeat course_ids would be added and displayed in this
listbox if you forget to clean it up fi rst.
B. A foreach loop is used to scan all rows of the fi lled Course table. Recall that we fi lled 6
columns of data from the Course table in the database to this Course table in the DataTable
object, starting with the fi rst column — course_id — and the second column — course. Now
we need to pick up the fi rst column — course_id (column index = 0) — for each returned
row or record of the Course table. Then the Add() method of the ListBox control is used
to add each retrieved course_id into the CourseList box.
C. For the FillCourseReader() method, the data type of the passed argument is Oracle
DataReader since we are using an Oracle database as our data source.
D. A local string variable strCourse is created, and this variable can be considered as an
intermediate variable used to temporarily hold the queried data from the Course table.
c05.indd 429c05.indd 429 2/11/2010 2:59:28 PM2/11/2010 2:59:28 PM

C. The Command object is initialized by assigning it with associated properties such as the
Connection, CommandType, and CommandText.
private void FillCourseTable(DataTable CourseTable)
{
CourseList.Items.Clear();
foreach (DataRow row in CourseTable.Rows)
{
CourseList.Items.Add(row[0]); //the 1st column is course_id - cmdString
}
}
private void FillCourseReader(OracleDataReader CourseReader)
{
string strCourse = string.Empty;
CourseList.Items.Clear();
while (CourseReader.Read())
{
strCourse = CourseReader.GetString(0); //the 1st column is course_id
CourseList.Items.Add(strCourse);
}
}
A
B
C
D
E
F
OracleSelectRTObject.CourseForm FillCourseTable()

Figure 5.183
Coding for the FillCourseTable and FillCourseReader methods.

item from the Selection form window to open the Course form. Select the desired faculty
private void CourseList_SelectedIndexChanged(object sender, EventArgs e)
{
string cmdString = "SELECT course, credit, classroom, schedule, enrollment, course_id FROM Course ";
cmdString += "WHERE course_id =:courseid";
OracleDataAdapter CourseDataAdapter = new OracleDataAdapter();
OracleCommand oraCommand = new OracleCommand();
DataTable oraDataTable = new DataTable();
LogInForm logForm = new LogInForm();
logForm = logForm.getLogInForm();
oraCommand.Connection = logForm.oraConnection;
oraCommand.CommandType = CommandType.Text;
oraCommand.CommandText = cmdString;
oraCommand.Parameters.Add("courseid", OracleType.Char).Value = CourseList.SelectedItem;
CourseDataAdapter.SelectCommand = oraCommand;
CourseDataAdapter.Fill(oraDataTable);
if (oraDataTable.Rows.Count > 0)
FillCourseTextBox(oraDataTable);
else
MessageBox.Show("No matched course information found!");
oraDataTable.Dispose();
oraCommand.Dispose();
CourseDataAdapter.Dispose();
}
A
B
C
D
E
F

many powerful tools and wizards provided by Visual Studio.NET 2008 and ADO.NET
to simplify the coding process, and most of codes are autogenerated by the .NET
Framework 3.5 and Visual C#.NET 2008 as the user uses those tools and wizards to
perform data operations such as adding new data source, making data binding, and con-
Figure 5.185
Running status of the Course form.
c05.indd 432c05.indd 432 2/11/2010 2:59:28 PM2/11/2010 2:59:28 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.21 Chapter Summary

433
necting to the selected data source. The shortcoming of this method is that a lot of coding
jobs are performed by the system behind the screen. Thus it is diffi cult to give users a
clear picture of what is really happening behind those tools and wizards. Most codes are
generated by the system automatically in the specifi c locations. Thus it is not easy to
translate and execute those codes in other platforms.
The second method, the runtime objects, 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 project in other platforms. This method provides a clear view for the users and enables
users to have a global and detail picture of how to control the direction of the project
with the coding according to the users ’ idea and feeling. The shortcoming of this method
is that a lot of coding may make the project complicated and it is hard to be accepted by
the beginners.
The third method, LINQ to DataSet and LINQ to SQL, is an up - to - date method,
and this technique was released with the Microsoft Visual Studio.NET 2008. The coding
process can be signifi cantly simplifi ed, and the query effi ciency can be greatly improved
by using this technique. These advantages can be found by comparing the codes we
developed in some projects in this chapter.

• Use the OleDbConnection, SqlConnection, and OracleConnection class to dynamically
connect to Microsoft Access 2007, 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 fi ll
a DataSet and a DataTable object with three kinds of databases.
• Use the OleDbDataReader, SqlDataReader, and OracleDataReader class to dynamically
read and process data with three kinds of databases.
• Use LINQ to DataSet and LINQ to SQL to signifi cantly simplify the query process and
improve the query effi ciency.
• 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 the SQL stored procedure to perform the data query from Visual C#.NET.
• Use the SQL nested stored procedure to perform the data query from Visual C#.NET.
• Use the Object Browser in Oracle Database 10g XE to create, debug, and test stored pro-
cedures and packages.
• Use the Oracle stored procedures and packages to perform the data query from Visual
C#.NET.
In Chapter 6 , we will discuss the data - inserting technique with three kinds of data-
bases. Three methods are introduced in two parts: Part I: Using the Design Tools and
Wizards provided by Visual Studio.NET 2008 to develop data inserting query, and Part
II: Using the runtime objects, LINQ to DataSet and LINQ to SQL to perform the data
inserting job for three databases.
HOMEWORK
I. True/False Selections
____ 1. Data Provider – dependent objects are Connection, Command, TableAdapter, and
DataReader.

Oracle database, LINQ to Oracle must be used.
___ 17. To assign a dynamic parameter in a SELECT statement in the SQL Server database, the
keyword LIKE must be used as the assignment operator.
___ 18. Two popular tools to create Oracle Packages are the Object Browser page and the SQL
Command page in Oracle Database 10g XE.
___ 19. Two popular ways to query data from any database are using the Fill() method, which
belongs to the TableAdapter class, or calling the ExecuteReader method, which belongs to
the Command class.
___ 20. A DataTable can be considered as a collection of DataRowCollection and DataColumn
Collection, and the latter contains DataRow and DataColumn objects.
II. Multiple Choices
1. To connect a database dynamically, one needs to use the _____.
a. Data Source
b. TableAdapter
c. Runtime object
d. Tools and Wizards
2. Four popular data providers are ________.
a. ODBC, DB2, JDBC, and SQL
b. SQL, ODBC, DB2, and Oracle
c. ODBC, OLEDB, SQL, and Oracle
d. Oracle, OLEDB, SQL, and DB2
3. To modify the DataSet, one needs to use the ______ Wizard.
a. DataSet confi guration
b. DataSet edit
c. TableAdapter confi guration
d. Query Builder
c05.indd 435c05.indd 435 2/11/2010 2:59:29 PM2/11/2010 2:59:29 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
436
Chapter 5 Data Selection Query with Visual C#.NET

b. Protected, globally
c. Public, locally
d. Public, globally
10. To ____ data between the DataSet and the database, the ___ object should be used.
a. Bind, BindingSource
b. Add, TableAdapter
c. Move, TableAdapter
d. Remove, DataReader
c05.indd 436c05.indd 436 2/11/2010 2:59:29 PM2/11/2010 2:59:29 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Homework

437
11. The keyword _______ will be displayed before the procedure ’ s name if one modifi ed an SQL
Server stored procedure.
a. CREATE
b. CREATE OR REPLACE
c. REPLACE
d. ALTER
12. To perform a runtime data query to the Oracle database, one needs to use ________.
a. OleDb Data Provider
b. Oracle Data Provider
c. Both (a) and (b)
d. None of them
13. To query data from any database using the runtime object method, two popular methods are
______ and ________.
a. DataSet, TableAdapter
b. TableAdapter, Fill
c. DataReader, ExecuteReader

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
438
Chapter 5 Data Selection Query with Visual C#.NET
based on the student_id retrieved from the fi rst stored procedure. Compile this package after it
is created to confi rm that it works.
5 . Try to use the OleDb data provider to replace either the SQL Server or the Oracle data provider
for the SQLSelectRTObject or the OracleSelectRTObject project to perform the similar data
query jobs for the Faculty form.
6 . Use LINQ to SQL to perform the data query to Student and StudentCourse tables for the
Student form in SQLSelectRTObject project. For your reference, a sample project can be found
in the folder DBProjects\Chapter 5 located at the accompanying ftp site (see Chapter 1 ). In that
project, the DataReader method is used to perform the data query for the Student form.
7 . Develop the data query for the Student form to retrieve data from both Student and
StudentCourse tables using Oracle Database 10g XE. Use LINQ to DataSet to perform this
query. The desired way is to use an Oracle Package to build this query.
c05.indd 438c05.indd 438 2/11/2010 2:59:29 PM2/11/2010 2:59:29 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter
6
Data Inserting with Visual C #. NET
Practical Database Programming With Visual C#.NET, by Ying Bai
Copyright © 2010 the Institute of Electrical and Electronics Engineers, Inc.
439
We spent a lot of time discussing and explaining data query in the last chapter by using
two different methods. In this chapter, we will concentrate on inserting data into the
DataSet and the database. Inserting data into the DataSet, or inserting data into the data
tables embedded in the DataSet, is totally different from inserting data into the database,
or inserting data into the data tables in the database. The former only inserts data into
the mapping of the data table in the DataSet, and this has nothing to do with the real
data tables in the database. In other words, the data inserted into the mapped data tables

To successfully complete this chapter, you need to understand topics such as funda-
mentals of databases, which were introduced in Chapter 2 , ADO.NET, which was dis-
cussed in Chapter 3 , and introduction to LINQ, which was presented in Chapter 4 . Also
a sample database CSE_DEPT that was developed in Chapter 2 will be used throughout
this chapter.
In order to save time and avoid repetition, we will use a sample project named
SampleWizards developed in the last chapter to demonstrate data insertion. Recall that
some command buttons on the different form windows in that project have not been
coded, such as Insert, Update, and Delete, and those buttons, or the event methods
related to those buttons, will be developed and coded in this chapter. We only concentrate
on the coding for the Insert button in this chapter.
PART I DATA INSERTING WITH VISUAL STUDIO.NET
DESIGN TOOLS AND WIZARDS
In this part, we discuss inserting data into the database using the Visual Studio.NET
design tools and wizards. We develop two methods to perform this data insertion: First,
we use the TableAdapter DBDirect method, TableAdapter.Insert(), to directly insert
data into the database. Second, we show readers how to insert data into the database by
fi rst adding new records into the DataSet, and then updating new records from the
DataSet to the database using the TableAdapter.Update() method. Both methods utilize
the TableAdapter ’ s direct and indirect methods to complete the data insertion. The
database we use is the Microsoft Access 2007 database, CSE_DEPT.accdb, which was
developed in Chapter 2 and is located in the folder Database\Access located at the
accompanying site: ftp://ftp.wiley.com/public/sci_tech_med/practical_database . Of course,
you can try to use any other databases such as Microsoft SQL Server 2005 or Oracle
Database 10 g XE. The only issue is that you need to select and connect to the correct
database when you use the Data Source window to set up your data source for your
Visual C#.NET data - driven applications.
6.1 INSERT NEW DATA INTO A DATABASE
Generally, there are many different ways to insert new data into the database in Visual
C#.NET. Normally, however, three methods are widely utilized:

method 3 to complete these data manipulations, which means that both methods need to
execute the Command object; more precisely, the ExecuteNonQuery() method of the
Command object fi nishes those data operations against the database.
Because methods 1 and 2 are relatively simple, in this part we will concentrate
on inserting data into the database using the TableAdapter methods. First, we discuss
how to insert new records directly into the database using the TableAdapter.Insert()
method. Second, we show readers how to insert new records into the DataSet and
then into a database using the TableAdapter.Update() method. Method 3 will be
discussed in Part II since it contains more complicated coding related to the runtime
objects method.
6.1.1 Insert New Records into a Database Using
T able A dapter . I nsert Method
When you use the TableAdapter DBDirect method to perform data manipulations
to a database, the main query must provide enough information for the DBDirect
methods to be created correctly. The so - called main query is the default or original
query method such as Fill() and GetData() when you fi rst open any TableAdapter by
using the TableAdapter Confi guration Wizard. Enough information means that the
data table must contain completed defi nitions. For example, if a TableAdapter is
confi gured to query data from a table that does not have a primary key column defi ned,
it does not generate DBDirect methods. Table 6.1 lists three TableAdapter DBDirect
methods.
It can be found from Table 6.1 that the TableAdapter.Update() method has two
functionalities: One is to directly make all changes in the database based on the param-
eters contained in the Update() method, and another job is to update all changes made
in the DataSet to the database based on the associated properties of the TableAdapter,
such as the InsertCommand, UpdateCommand, and DeleteCommand.
c06.indd 441c06.indd 441 2/11/2010 11:55:55 AM2/11/2010 11:55:55 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
442
Chapter 6 Data Inserting with Visual C#.NET

to update that record. The TableAdapter.Update
method is also used to reconcile changes in a data set
back to the database by taking a DataSet, DataTable,
DataRow, or array of DataRows as method
parameters.
TableAdapter.Delete Deletes existing records from the database based on the
original column values passed as method parameters.
c06.indd 442c06.indd 442 2/11/2010 11:55:55 AM2/11/2010 11:55:55 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.