Practical Database Programming With Visual C#.NET- P7 - Pdf 68

5.18 Query Data Using Runtime Objects to Microsoft Access 2007 Database

323
faculty. Five label controls bound to the associated columns in the Faculty table are
updated with the queried information, and the selected faculty image is also displayed in
the PhotoBox control, which is shown in Figure 5.89 . You can try to select the different
method by clicking on the drop - down arrow from the Method combobox. Yes, the project
works ﬁ ne with all three methods without any problem at all!
private string ShowFaculty(string fName)
{
string strName;
switch (fName)
{
case "Black Anderson":
strName = "Anderson.jpg";
break;
case "Ying Bai":
strName = "Bai.jpg";
break;
case "Satish Bhalla":
strName = "Satish.jpg";
break;
case "Steve Johnson":
strName = "Johnson.jpg";
break;
case "Jenney King":
strName = "King.jpg";
break;
case "Alice Brown":
strName = "Brown.jpg";

this.Hide();
}
AccessSelectRTObject.FacultyForm cmdBack_Click()

Figure 5.88
Coding for the cmdBack button Click method.
c05.indd 323c05.indd 323 2/11/2010 2:58:31 PM2/11/2010 2:58:31 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
324
Chapter 5 Data Selection Query with Visual C#.NET
Our next job is to do the coding for the Course form.
5.18.4 Query Data Using Runtime Objects for Course Form
Three data query methods will be used for the data query on this form: DataAdapter,
DataReader, and LINQ method. As we did for the FacultyForm, we also need to use the
OleDb data provider to perform the data query in this CourseForm. Thus, ﬁ rst we need
to add one more namespace, System.Data.OleDb, into the namespace section on the code
window of this form. Open the code window and add
using System.Data.OleDb;
to the namespace part of this form.
Next we need to create a class - level textbox array, CourseTextBox[6], and a class
level DataSet object ds. The textbox array is used to temporarily save ﬁ ve columns in the
Course data table, and we need this array when we retrieve and assign columns to the
associated textbox controls on the CourseForm window as the project runs. The ds
DataSet is used for the LINQ method since there is no direct relationship between the
LINQ and Access database, and we need this DataSet to perform a LINQ to DataSet
operation to do the data query (refer to Figure 5.90 ).
Now we need to do the coding for the constructor of the CourseForm to do some
initialization tasks. Enter the codes into the constructor, and your ﬁ nished coding should
match that shown in Figure 5.90 .
Let ’ s see how this piece of code works.

combobox in the Course form, and then we can make the second query to the Course
table to pickup all course_id based on the faculty_id we obtained from the ﬁ rst query.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace AccessSelectRTObject
{
public partial class CourseForm : Form
{
private TextBox[] CourseTextBox = new TextBox[6];
DataSet ds = new DataSet();
public CourseForm()
{
InitializeComponent();
ComboName.Items.Add("Ying Bai");
ComboName.Items.Add("Satish Bhalla");
ComboName.Items.Add("Black Anderson");
ComboName.Items.Add("Steve Johnson");
ComboName.Items.Add("Jenney King");
ComboName.Items.Add("Alice Brown");
ComboName.Items.Add("Debby Angles");
ComboName.Items.Add("Jeff Henry");
ComboName.SelectedIndex = 0;
ComboMethod.Items.Add("DataAdapter Method");

as the detailed information for selected course_id. The faculty_id is used as the criterion
to query the desired course information for the selected faculty.
B. The necessary instances and data components are also created at this part to aid with the
data query task. Two sets of objects, which include DataAdapters, Commands, and
DataTables, are declared, and one set is for the Faculty table and the other set is for the
Course table. The DataReader object is used for the data querying using the DataReader
method, and the DataRow object is used to reserve the returned row from the Faculty
table. The string variable strFacultyID is used to hold the queried faculty_id from the
Faculty table.
C. Then the ﬁ rst Command object, accCmdFaculty, is initialized by assigning it with the
Connection instance, Command type, and the query string. The dynamic parameter
Param1 is obtained from the Faculty Name combobox in which the faculty name will be
selected by the user as the project runs.
D. The completed Command object accCmdFaculty is assigned to the SelectCommand prop-
erty of the FacultyDataAdapter, which is ready to make query by using the Fill() method.
E. The Fill() method is executed to ﬁ ll the faculty data table named accFacultyTable, and it
is used to ﬁ nd the faculty_id that is matched to the selected faculty name from the
ComboName in the CourseForm from the Faculty table.
F. By checking the Count property, we can conﬁ rm whether this Fill is successful or not.
If the value of this property is greater than 0, which means that at least one row has
been selected and ﬁ lled into the Faculty table, the returned ﬁ rst row or the only row,
accFacultyTable.Rows[0], is assigned to the DataRow object rowFaculty. Then the ﬁ rst
column in that row, rowFaculty[0], which is the matched faculty_id, is converted to string
and saved to the strFacultyID variable that will be used later.
G. An error message will be displayed if this Fill() has failed.
H. Next the course Command object accCmdCourse is initialized and the dynamic parameter
@Param2 is replaced by the real parameter faculty_id obtained from the ﬁ rst query we did
above.
I. As we did for the Faculty form, the user can make a choice among three query methods:
DataAdapter, DataReader, and LINQ to DataSet. If the user selects the DataAdapter

FacultyDataAdapter.Fill(accFacultyTable);
if (accFacultyTable.Rows.Count > 0)
{ rowFaculty = accFacultyTable.Rows[0]; strFacultyID = rowFaculty[0].ToString(); }
Else
{ MessageBox.Show("No matched faculty_id found!"); }
accCmdCourse.Connection = logForm.accConnection;
accCmdCourse.CommandType = CommandType.Text;
accCmdCourse.CommandText = strCourse;
accCmdCourse.Parameters.Add("@Param2", OleDbType.Char).Value = strFacultyID;
if (ComboMethod.Text == "DataAdapter Method")
{
CourseDataAdapter.SelectCommand = accCmdCourse;
CourseDataAdapter.Fill(accCourseTable);
if (accCourseTable.Rows.Count > 0) { FillCourseTable(accCourseTable); }
else { MessageBox.S
how("No matched course found!"); }
accFacultyTable.Dispose(); accCourseTable.Dispose(); CourseDataAdapter.Dispose();
}
else if (ComboMethod.Text == "DataReader Method")
{
accDataReader = accCmdCourse.ExecuteReader();
if (accDataReader.HasRows == true) { FillCourseReader(accDataReader); }
else { MessageBox.Show("No matched course found!"); }
accDataReader.Close(); accDataReader.Dispose();
}
else //LINQ to DataSet Method is selected
{
CourseList.Items.Clear();
CourseDataAdapter.SelectCommand = accCmdCourse;
CourseDataAdapter.Fill(ds, "Course");

T
U
V
W
AccessSelectRTObject.CourseForm cmdSelect_Click()

Figure 5.91
Coding for the cmdSelect button Click method.
c05.indd 327c05.indd 327 2/11/2010 2:58:33 PM2/11/2010 2:58:33 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
328
Chapter 5 Data Selection Query with Visual C#.NET
J. If this ﬁ ll is successful, the Count property of the Course table should be greater than 0,
which means that the table is ﬁ lled by at least one row. The user - deﬁ ned method
FillCourseTable() will be called with the ﬁ lled table as the argument to ﬁ ll the CourseList
box control with the course_id on the Course form.
K. Otherwise, if this Count is equal to 0, which means that no row or record has been ﬁ lled
into the Course table. An error message will be displayed for this situation.
L. Some necessary cleaning jobs are performed to release all objects we used for this data
query.
M. If the user selected the DataReader method, the ExecuteReader() method is called to
perform a reading - only operation to the Course table.
N. If the HasRows property of the DataReader is True, which means that the DataReader
did receive some data, the user - deﬁ ned method FillCourseReader() is executed with the
DataReader as the argument to ﬁ ll the CourseList box control with the course_id on the
Course form window.
O. An error message will be displayed if the HasRows property is false.
P. The DataReader object is released after it is used up.
Q. If the user selects the LINQ to DataSet method, ﬁ rst we need to clean up the CourseList
control to make it ready to be ﬁ lled with course_id.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.18 Query Data Using Runtime Objects to Microsoft Access 2007 Database

329
SelectedIndexChanged() method to display detailed information for the selected
course_id in ﬁ ve textboxes. Without this default course_id selected, no detailed course
information can be displayed as the Course List_SelectedIndexChanged() method is exe-
cuted at the ﬁ rst time.
Now let ’ s take a look at the codes of two user - deﬁ ned methods, FillCourseTable()
and FillCourseReader(). These two methods are used to ﬁ ll the CourseList box control
on the Course form by using the queried data obtained either from the DataAdapter or
from the DataReader. The detailed codes for these two methods are shown in Figure
5.92 .
Let ’ s see how this piece of code works.
A. Before we can ﬁ ll the CourseList box, a cleaning job is needed. This cleaning is very
important and necessary, otherwise multiple duplicated course_id would be displayed in
this listbox if you forget to clean it up ﬁ rst.
B. A foreach loop is used to scan all rows of the ﬁ lled Course table. Recall that we queried
six columns from the Course table in the database and ﬁ lled them to this Course table in
the DataSet starting with the ﬁ rst column that is course_id (refer to query string strCourse
deﬁ ned in the cmdSelect button Click method; see Figure 5.91 ). Now we need to pick up
the ﬁ rst column — course_id (column index = 0) — for each returned row from the Course
table. Then add each course_id into the CourseList control to display them by using the
Add() method.
C. For the FillCourseReader() method, a local string variable strCourse is created, and this
variable can be considered as an intermediate variable that is used to temporarily hold
the queried column from the Course table.
D. We also need to clean up the CourseList box before it can be ﬁ lled.
E. A while loop is utilized to retrieve each ﬁ rst column ’ s data [GetString(0)] whose column

c05.indd 329c05.indd 329 2/11/2010 2:58:33 PM2/11/2010 2:58:33 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
330
Chapter 5 Data Selection Query with Visual C#.NET
Next we need to take care of the coding for the CourseList_SelectedIndexChanged()
method. The functionality of this method is to display the detailed course information
related to the selected course_id from the CourseList box, which includes the course
name, classroom, schedule, credit, and enrollment in 5 textbox controls on the Course
form. This method can be triggered as the user clicked on a course_id from the CourseList
box.
Open the Course form window by clicking on the View Designer button from the
Solution Explorer window, and then double - click on the Courselist box to open its
CourseList_SelectedIndexChanged() method. Enter the codes shown in Figure 5.93
into this method. The code segment in this part is very similar to the one we did for the
cmdSelect button Click method.
Let ’ s see how this piece of code works.
A. The query string is deﬁ ned with 6 data columns that contain the detailed course informa-
tion. Note that the ﬁ rst column is the course name with a column index of 0, and the cri-
terion for the WHERE clause is course_id. This is because we want to retrieve all course
information related to the selected course_id and display those pieces of information in
the 5 textbox controls.
B. The data components and objects used in this method are declared and created here, which
include CourseDataAdapter, Command, DataTable, DataReader, and an instance of
the LogInForm class. The purpose of creating this new instance is to get the Connection
object from the LogInForm object since we created our database connection object in that
class.
C. The Command object is initialized with the Connection object, CommandType, and
CommandText properties.
D. The dynamic parameter @Param1 is replaced by the real parameter, CourseList.
SelectedItem, which will be selected by the user from the CourseList box as the project

OleDbCommand accCommand = new OleDbCommand();
DataTable accDataTable = new DataTable();
OleDbDataReader accDataReader;
LogInForm logForm = new LogInForm();
logForm = logForm.getLogInForm();
accCommand.Connection = logForm.accConnection;
accCommand.CommandType = CommandType.Text;
accCommand.CommandText = cmdString;
accCommand.Parameters.Add("@Param1", OleDbType.Char).Value = CourseList.SelectedItem;
if (ComboMethod.Text == "DataAdapter Method")
{
CourseDataAdapter.SelectCommand = accCommand;
CourseDataAdapter.Fill(accDataTable);
if (accDataTable.Rows.Count > 0)
FillCourseTextBox(accDataTable);
else
MessageBox.Show("No matched course information found!");
accDataTable.Dispose();
CourseDataAdapter.Dispose();
}
else if (ComboMethod.Text == "DataReader Method")
{
accDataReader = accCommand.ExecuteReader();
if (accDataReader.HasRows == true)
FillCourseReaderTextBox(accDataReader);
else
MessageBox.Show("No matched course information found!");
accDataReader.Close();
accDataReader.Dispose();
}

J
K
L
M
N
O
P
Q
AccessSelectRTObject.CourseForm CourseList_SelectedIndexChanged()
I

Figure 5.93
Coding for the CourseList SelectedIndexChanged method.
c05.indd 331c05.indd 331 2/11/2010 2:58:34 PM2/11/2010 2:58:34 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
332
Chapter 5 Data Selection Query with Visual C#.NET
M. If the user selects the LINQ to DataSet method, ﬁ rst we need to create a new DataSet
object dc and build it by executing the Fill() method. The SelectCommand property of the
CourseDataAdapter should have been initialized with the accCommand object we built in
step C .
N. A typical LINQ query structure is created and executed to retrieve back the detailed
course information related to course_id. The cinfo is a Visual C# 2008 implicitly typed
local variable with a data type var. The Visual C# 2008 will be able to automatically convert
this var to any suitable data type, in this case, it is a collection, when it sees it. An iteration
variable c is used to iterate over the result of this query from the Course table. Then a
similar SQL SELECT statement is executed with the WHERE clause. The ﬁ rst key point
for this structure is the operator AsEnumerable(). Since different database systems use
different collections and query operators, those collections must be converted to a type of
IEnumerable< T > in order to use the LINQ technique because all data operations in LINQ

the CourseList_SelectedIndexChabnged() method.
D. A double foreach loop is utilized to retrieve all columns and all rows from the Course
DataTable; that is, the outer loop is only executed one time since we only query one record
(one row) based on the selected course_id from the Course data table. The inner loop is
c05.indd 332c05.indd 332 2/11/2010 2:58:34 PM2/11/2010 2:58:34 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.18 Query Data Using Runtime Objects to Microsoft Access 2007 Database

333
executed six times to pick up six pieces of course - related information that contains the
course name, classroom, credit, schedule, enrollment, and course_id. Then the retrieved
information is assigned to each textbox control in the textbox array, which will be displayed
in that textbox control in the CourseForm later. The course_id is an exception since we
do not need to display it in this application.
E. For the method MapCourseTable(), it assigns each textbox control to the matched partner
in the textbox array to set up a correct relationship between them.
F. In the FillCourseReaderTextBox() method, a loop counter intIndex is ﬁ rst created, and it
is used for the loop of initialization of the textbox object array and the loop of retrieving
data from the DataReader later.
G. This loop is used to initialize the textbox object array.
H. The MapCourseTable() method is executed to set up a correct relationship between the
textbox controls and the textbox array.
I. A while and a for loop are used to pick up all ﬁ ve piece of course - related information from
the DataReader one by one. The Read() method is used as the while loop condition. A
private void FillCourseTextBox(DataTable CourseTable)
{
int pos1 = 0;
for (int pos2 = 0; pos2 <= 5; pos2++) //Initialize the object array
CourseTextBox[pos2] = new TextBox();

A
B
C
D
E
F
G
H
I
AccessSelectRTObject.CourseForm FillCourseTextBox()

Figure 5.94
Coding for three methods.
c05.indd 333c05.indd 333 2/11/2010 2:58:34 PM2/11/2010 2:58:34 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
334

Chapter 5 Data Selection Query with Visual C#.NET
returned True means that a valid data is read out from the DataReader, and a returned
False means that no valid data has been read out from the DataReader; in other words,
no more data is available and all data has been read out. The for loop uses the FieldCount - 1
as the termination condition since the index of the ﬁ rst data ﬁ eld is 0, not 1, in the
DataReader object. Each read - out data is converted to a string and assigned to the associ-
ated textbox control in the textbox object array.
The last coding is for the cmdBack button Click method. This coding is very simple
and it is shown in Figure 5.95 . The CourseForm window will disappear from the screen
when this button is clicked on by the user.
Now let ’ s test our project by clicking on the Start button. Enter the username and
password as we did before, and select the Course Information from the Selection form
window to open the Course form window, which is shown in Figure 5.96 .

information is displayed in ﬁ ve textboxes as one clicks on the Select button.
The coding for this form is a little special since two data tables are utilized for this
form: Student and StudentCourse. The ﬁ rst table contains the student ’ s general infor-
mation and the second one contains all courses taken by the student. Therefore two
DataAdapters are needed for this application. Also two different data queries are
needed to query data from two tables. The ﬁ rst one is used to retrieve the student ’ s
general information from the Student table, and the second is to pick up all course
information (course_id) for the courses taken by the student from the StudentCourse
table.
In order to save space, only two query methods — DataAdapter and LINQ to DataSet
methods — are provided in this section. For the DataReader query method, we like to
leave it as homework to the students.
The coding job is divided into two parts with two major methods: the constructor of
the Student class and the cmdSelect_Click method. The ﬁ rst one is used to initialize the
Student form and display all students ’ names on the combobox control, which can be
selected by the user to review the related information for the selected student. The
second one is to execute the data queries to pick up the selected student ’ s general and
course information and display them in the associated textbox controls and the ListBox
control.
5.18.5.1 Coding for Constructor of Student Form
As we did before for the LogIn, Faculty, and Course forms, add the OleDb namespace,
System.Data.OleDb, into this code window since we need to use it in this data query.
The coding for this constructor is shown in Figure 5.97 . Let ’ s take a look at this piece
of code to see how it works.
A. A new DataSet instance and a TextBox array StudentTextBox[] is created here. The
DataSet instance is used for the LINQ to DataSet method, and the textbox object array
is used to set up a mapping relationship between the 6 textboxes in the Student form and
6 query columns in the query string strStudent, student_id, gpa, credits, major, schoolYear,
and email. The reason we deﬁ ned the size of this array as 7 is that we need the seventh
column, student_name, when we query data using the LINQ to DataSet method later.

second query string is for the StudentCourse table. The queried columns are course_id
and student_id, and the query criterion is the student_id. Similarly, the repeated query for
the column student_id is for the requirement of the LINQ to DataSet method.
B. All data objects are created here, which include the Student and the StudentCourse
DataAdapters, Commands, and DataTables. The string variable strName is used to hold
the returned name of the student ’ s image ﬁ le from the calling of the function FindName().
C. The FindName() function is called to get and display the appropriate student ’ s image based
on the student name. If no matched image can be found, an error message is displayed.
D. The method BuildCommand() is called to build the Student Command object with the
Connection object and the student query string as the arguments. You will ﬁ nd that the
data type of the ﬁ rst argument, accCmdStudent, is a reference type (ref), which is equiva-
public partial class StudentForm : Form
{
DataSet ds = new DataSet();
private TextBox[] StudentTextBox = new TextBox[7]; //We query 7 columns from the Student table
public StudentForm()
{
InitializeComponent();
ComboName.Items.Add("Erica Johnson");
ComboName.Items.Add("Ashly Jade");
ComboName.Items.Add("Holes Smith");
ComboName.Items.Add("Andrew Woods");
ComboName.Items.Add("Blue Valley");
ComboName.SelectedIndex = 0;
ComboMethod.Items.Add("DataAdapter Method");
ComboMethod.Items.Add("LINQ & DataSet Method");
ComboMethod.SelectedIndex = 0;
A
B
C

strName = FindName(ComboName.Text);
if (strName == "No Match")
MessageBox.Show("No Matched Student’s Image Found!");
BuildCommand(ref accCmdStudent, strStudent);
accCmdStudent.Parameters.Add("@Param1", OleDbType.Char).Value = ComboName.Text;
StudentDataAdapter.SelectCommand = accCmdStudent;
if (ComboMethod.Text == "DataAdapter Method")
{
StudentDataAdapter.Fill(accStudentTable);
if (accStudentTable.Rows.Count > 0)
FillStudentTextBox(accStudentTable);
else
MessageBox.Show("No matched student found!");
BuildCommand(ref accCmdStudentCourse, strStudentCourse);
accCmdStudentCourse.Parameters.Add("@Param2", OleDbType.Char).Value = txtID.Text;
StudentCourseDataAdapter.SelectCommand = accCmdStudentCourse;
StudentCourseDataAdapter.Fill(accStudentCourseTable);
if (accStudentCourseTable.Rows.Count > 0)
FillCourseList(accStudentCourseTable);
else
MessageBox.Show("No matched course_id found!");

}
else //LINQ to DataSet Method
{
StudentDataAdapter.Fill(ds, "Student");
LINQStudent(ds);
BuildCommand(ref accCmdStudentCourse, strStudentCourse);
accCmdStudentCourse.Parameters.Add("@Param2", OleDbType.Char).Value = txtID.Text;
StudentCourseDataAdapter.SelectCommand = accCmdStudentCourse;

Figure 5.98
Coding for the Student Select button Click method.
c05.indd 337c05.indd 337 2/11/2010 2:58:36 PM2/11/2010 2:58:36 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
338
Chapter 5 Data Selection Query with Visual C#.NET
object accCmdStudent is assigned to the SelectCommand property of the Student
DataAdapter.
F. If the DataAdapter method is selected, the Fill() method is called to ﬁ ll the Student
table.
G. By checking the Count property, we can inspect whether this ﬁ ll is successful or not. If this
property is greater than 0, which means that at least one row has been ﬁ lled into the
Student data table and the ﬁ ll is successful, the user - deﬁ ned method FillStudentTextBox()
is called with the ﬁ lled Student table as the argument to ﬁ ll six textboxes in the
Student form with the detailed student ’ s information such as student_id, gpa, credits,
major, schoolYear, and email.
H. Otherwise, an error message is displayed.
I. To make the second query to the StudentCourse table to ﬁ nd all courses taken by the
selected student, the BuildCommand() is called again to initialize and build the
StudentCourse Command object. The dynamic parameter @Param2 is replaced by
the real student_id, which was obtained from the ﬁ rst query and stored in the textbox
txtID. The completed StudentCourse Command object, accCmdStudentCourse, is assigned
to the SelectCommand property of the StudentCourse DataAdapter.
J. The Fill() method is called to ﬁ ll the StudentCourse data table.
K. If the Count property of the DataRow object in the StudentCourse table is greater than
0, which means that the ﬁ ll is successful, another user - deﬁ ned method FillCourseList() is
executed to ﬁ ll all courses (that is, course_id) stored in the ﬁ lled StudentCourse table into
the CourseList box in the Student form.
L. Otherwise if the Count property is 0, which means that this ﬁ ll has failed, an error message
is displayed.

• LINQStudent()
• LINQStudentCourse()
First let ’ s handle the coding for the FindName() method. This method is similar to
the one we developed in the Faculty form, and the coding for this method is shown in
Figure 5.99 .
A switch case structure is used to select the desired student ’ s image ﬁ le based on the
input student ’ s name, and the selected student ’ s image is displayed in a PictureBox in the
Student form using the FromFile() system method. Note the location in which the student
image ﬁ les are located. You can save those image ﬁ les in any folder on your computer
or a server, but you must provide the full name for the selected image and assign it to
the strName variable to be returned. The so - called full name includes the machine name,
driver name, and folder name as well as the image ﬁ le name. An easy way to save these
image ﬁ les is to save them in the folder in which your Visual C# project executable ﬁ le
is located. For instance, in this application our Visual C# project executable ﬁ le is located
in the folder C:\Chapter 5\MSAccessSelectRTObject\bin\Debug. When save all student ’ s
private string FindName(string sName)
{
string strName;
switch (sName)
{
case "Erica Johnson":
strName = "Erica.jpg";
break;
case "Ashly Jade":
strName = "Ashly.jpg";
break;
case "Holes Smith":
strName = "Holes.jpg";
break;
case "Andrew Woods":

class such as the Connection string, Command type, and Command text are assigned to
the Command object. Note that the data type of the ﬁ rst argument — cmdObj — is a refer-
ence (ref), as we mentioned above in step D . A reference in Visual C# 2008 is equivalent
to a memory address or a pointer in C++, and the argument cmdObj is called passing by
reference. When an argument is passing in this mode, the object cmdObj will work as
both an input and an output argument, and they will be stored at the same address when
this method is completed. We can use this built cmdObj as a returned object even if it is
an argument without needing to return this cmdObj object from this method.
For some other user - deﬁ ned methods used in this form, such as FillStudentTextBox(),
FillCourseList(), and MapStudentTextBox(), the coding for them is similar to that we
developed in the Course form. For your convenience, we list them here again with some
simple explanations. The coding for the FillStudentTextBox() method is shown in Figure
5.101 .
private void BuildCommand(ref OleDbCommand cmdObj, string cmdString)
{
LogInForm logForm = new LogInForm();
logForm = logForm.getLogInForm();
cmdObj.Connection = logForm.accConnection;
cmdObj.CommandType = CommandType.Text;
cmdObj.CommandText = cmdString;
}
AccessSelectRTObject.StudentForm
BuildCommand()

Figure 5.100
The BuildCommand method.
private void FillStudentTextBox(DataTable StudentTable)
{
int pos1 =0;
for (int pos2 = 0; pos2 <= 6; pos2++) //Initialize the textbox array

pick up data from any DataTable, one must use the row and column objects as the index
to access each row or column of DataTable instead of using an integer. The local integer
variable pos1 works as an index for the StudentTextBox array.
The coding for the MapStudentTextBox() method is shown in Figure 5.102 . The
purpose of this coding is to set up a correct relationship between each textbox control in
the StudentTextBox array and each column data in our ﬁ rst query string — strStudent.
Each textbox control in the StudentTextBox array is related to an associated textbox
control in the Student form such as student_id, gpa, credits, major, schoolYear, and email.
Since the distribution order of those textboxes in the StudentTextBox array may be dif-
ferent with the order of those column data in our ﬁ rst query, a correct order relationship
can be set up by executing this method.
The coding for the FillCourseList() method is shown in Figure 5.103 . The function
of this method is to ﬁ ll the CourseList box with all courses taken by the selected student,
and those queried courses are stored in the StudentCourse table, which are obtained by
executing the second query to the StudentCourse table based on the student_id. In order
private void MapStudentTextBox(Object[] sTextBox)
{
}
sTextBox[0] = txtID; //The order must be identical with the
sTextBox[1] = txtGPA; //order in the query string - strStudent
sTextBox[2] = txtCredits;
sTextBox[3] = txtMajor;
sTextBox[4] = txtSchoolYear;
sTextBox[5] = txtEmail;
AccessSelectRTObject.StudentForm MapStudentTextBox()

Figure 5.102
Coding for the MapStudentTextBox method.
private void FillCourseList(DataTable StudentCourseTable)
{

structure is the operator AsEnumerable(). Since different database systems use different
collections and query operators, those collections must be converted to a type of
IEnumerable < T > in order to use the LINQ technique because all data operations in LINQ
use a Standard Query Operator method that can perform complex data queries on an
IEnumerable < T > sequence. A compiling error would be encountered without this opera-
tor. The second key point is that you have to use the explicit cast (string) to convert the
ComboName. Text to the string object and then assign it to the ﬁ eld of student_name as
the criterion for this query.
B. The foreach loop is utilized to pick up each column from the selected data row sRow,
which is obtained from the studentinfo we get from the LINQ query. Then, assign each
column to the associated textbox control in the StudentForm window to display them.
Since we are using a nontyped DataSet, we must indicate each column clearly with the
ﬁ eld < string > and the column ’ s name as the position for each of them.
The coding for the LINQStudentCourse() method is shown in Figure 5.105 . Let ’ s see
how this piece of code works.
private void LINQStudent(DataSet dSet)
{
var studentinfo = (from si in dSet.Tables["Student"].AsEnumerable()
where si.Field<string>("student_name") == (string)ComboName.Text
select si);
foreach (var sRow in studentinfo)
{
this.txtID.Text = sRow.Field<string>("student_id");
this.txtSchoolYear.Text = sRow.Field<string>("schoolYear");
this.txtGPA.Text = sRow.Field<string>("gpa");
this.txtCredits.Text = sRow.Field<int>("credits").ToString();
this.txtMajor.Text = sRow.Field<string>("major");
this.txtEmail.Text = sRow.Field<string>("email");
}
}

Nhờ tải bản gốc

Tài liệu, ebook tham khảo khác

Practical Database Programming With Visual C#.NET- P7 - Pdf 68

Tài liệu, ebook tham khảo khác

Học thêm