5.19 Query Data Using Runtime Objects to SQL Server Database

373
C. The FindName() function is executed to get the student ’ s photo fi le based on the student ’ s
name. The returned student ’ s image fi le is assigned to the local string variable strName.
D. The user - defi ned method BuildCommand() is called to initialize the fi rst Command object
with the correct Connection, CommandType, and CommandText properties. In order to
execute our stored procedure, the properties should be set as follows:

•
CommandType = CommandType. StoredProcedure

•
CommandText = “ dbo.StudentInfo ”
Figure 5.129
Testing result for our second stored procedure.
One point you need to note is that if you are using SQL Server Management Studio
Express to build your database, in some situations, you cannot connect the server to open the
database if you are performing some tasks with the Server Explorer such as creating stored
procedures because your server has been connected and the database is open when you create
stored procedures. An error message would be displayed if you try to do that since this version
only allows one instance of the server to be connected at a time. You have to disconnect that
connection fi rst by rebooting your computer.
The content of the CommandText must be equal to the name of the stored procedure we
developed above.
E. The unique input parameter to the stored procedure dbo.StudentInfo is StudentName,
which will be selected by the user from the student name combobox (ComboName.Text)

MessageBox
.Show("
No matched student found!");
BuildCommand(ref sqlCmdStudentCourse, strStudentCourse);
sqlCmdStudentCourse.Parameters.Add("@StudentID", SqlDbType.Char).Value = txtID.Text;
StudentCourseDataAdapter.SelectCommand = sqlCmdStudentCourse;
StudentCourseDataAdapter.Fill(sqlStudentCourseTable);
if (sqlStudentCourseTable.Rows.Count > 0)
FillCourseList(sqlStudentCourseTable);
else
MessageBox.Show("No matched course_id found!");

}
else //DataReader Method is selected
{
sqlStudentReader = sqlCmdStudent.ExecuteReader();
if (sqlStudentReader.HasRows == true)
FillStudentReader(sqlStudentReader);
else
MessageBox.Show("No matched student found!");
BuildCommand(ref sqlCmdStudentCourse, strStudentCourse);
sqlCmdStudentCourse.Parameters.Add("@StudentID", SqlDbType.Char).Value = txtID.Text;
StudentCourseDataAdapter.SelectCommand = sqlCmdStudentCourse;
sqlStudentCourseReader = sqlCmdStudentCourse.ExecuteReader();
if (sqlStudentCourseReader.HasRows == true)
FillCourseReader(sqlStudentCourseReader);
else
MessageBox.Show("No matched course_id found!");
sqlStudentReader.Close();
sqlStudentCourseReader.Close();

Coding for the Select button Click method.
c05.indd 374c05.indd 374 2/11/2010 2:58:55 PM2/11/2010 2:58:55 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.19 Query Data Using Runtime Objects to SQL Server Database

375
that is the property of the Command class by using the Add() method before the stored
procedure can be executed. The initialized Command object sqlCmdStudent is assigned
to the SelectCommand property of the DataAdapter to make it ready to be used in the
next step.
F. If the user selected the DataAdapter Method, the Fill() method of the DataAdapter is
called to fi ll the Student table, which actually calls our fi rst stored procedure to fi ll the
Student table.
G. If this calling is successful, the Count property should be greater than 0, which means
that at least one row is fi lled into the Student table, and the user - defi ned method
FillStudentTextBox() is called to fi ll six textboxes in the Student form with six pieces of
retrieved columns from the stored procedure. Otherwise, an error message is displayed if
this fi ll has failed.
H. The user - defi ned method BuildCommand() is called again to initialize our second
Command object sqlCmdStudentCourse. The values to be assigned to the properties of
the Command object are:

•
CommandType = CommandType. StoredProcedure

•
CommandText = “ dbo.StudentCourseInfo ”
The content of the CommandText must be equal to the name of the stored procedure we
developed above.


Chapter 5 Data Selection Query with Visual C#.NET
P. The ExecuteReader() method is called to run our second stored procedure to read out all
courses taken by the selected student, and assign them to the StudentCourse DataReader
object.
Q. If the method ExecuteReader() runs successfully, the HasRows property of the DataReader
object should be true, the user - defi ned method FillCourseReader() is executed to fi ll all
courses (course_id) into the CourseList listbox. Otherwise if this method has failed, an
error message is displayed.
R. Two student DataReader objects are released before we can exit this method.
S. The cleaning job is performed to release all other data objects used in this method.
The code for the method FindName() is identical to what we developed for the
same method in Section 5.18.5.2 . Refer to Figure 5.99 to get detailed information for this
coding.
In order to pick up the correct student ’ s image fi le from this subroutine, note that
you must store all students ’ image fi les in the folder in which your Visual C# 2008 pro-
ject ’ s executable fi le is located. In our application, this folder is C:\Book6\Chapter 5\
SQLSelectRTObject\bin\Debug. If you place those students ’ image fi les in another folder,
you must provide a full name, which includes the drive name, path, and the image fi le
name, for that student ’ s image fi le to be accessed, and assign it to the returning string
variable strName in this method. The coding for the BuildCommand() method is shown
in Figure 5.131 .
This coding is straightforward and easy to be understood. First, a new LogInForm
instance is created, and the getLogInForm() method is called to pick up the global con-
nection object and assign it to the Connection property of the Command object. Then
the Command object is initialized by assigning the related properties such as the
CommandType and CommandText to it. The point is that the value assigned to the
CommandType must be StoredProcedure, and the value assigned to the CommandText
must be equal to the name of the stored procedure we developed in the last section.
The codes for the methods FillStudentTextBox(), MapStudentTextBox(), and

Figure 5.133 .
In the method FillStudentReader(), fi rst textbox object array is initialized, and then
the MapStudentTextBox() method is executed to set up a correct relationship between
each element in the textbox array and each associated textbox control in the Student
form. A while and a for loop are utilized to pick up each fetched column and assign them
to the associated textbox control. The point is the difference between the system method
GetString() and GetValue() that belong to the DataReader class. The former can be used
to get only string data stored in the database, but the latter can be used to get any kind
of data stored in the database. The issue is that another system method, ToString(), must
be attached to this GetValue() to convert the data from any other data type to the string
and assign it to the Text property of each TextBox control. Therefore, the latter is more
popular and more powerful in a data - driven application. You can try to compare these
two methods yourself if you like.
private void FillStudentTextBox(DataTable StudentTable)
{
int pos1 =0;
for (int pos2 = 0; pos2 <= 6; pos2++) //Initialize the textbox array
StudentTextBox[pos2] = new TextBox();
MapStudentTextBox(StudentTextBox);
foreach (DataRow row in StudentTable.Rows)
{
foreach (DataColumn column in StudentTable.Columns)
{
StudentTextBox[pos1].Text = row[column].ToString();
pos1++;
}
}
}
private void MapStudentTextBox(Object[] sTextBox)
{

project. Build the project and click on the Start Debugging button to run our project,
enter user name and password, and select the Student Information item to open the
Student form window, which is shown in Figure 5.134 .
Select Ashly Jade from the Student Name combobox and click on the Select button.
All information related to this student and the courses are displayed in the six textboxes
and the CourseList box, which is shown in Figure 5.134 .
Our calling to two stored procedures are very successful! Some readers may fi nd that
these two stored procedures are relatively simple, and each procedure only contains one
SQL statement. Let ’ s dig a little deeper and develop some sophisticated stored proce-
dures and try to call them from our Visual C# project. Next we will develop a stored
procedure that contains more SQL statements.
5.19.2.7.4 Query Data Using More Complicated Stored Procedures We want to
get all courses (all course_ids) taken by the selected student based on student name from
the StudentCourse table. To do that, we must fi rst go to the Student table to obtain the
associated student_id based on the student name since there is no student name column
available in the StudentCourse table. Then we can go to the StudentCourse table to pick
up all course_id based on the selected student_id. We need to perform two queries to
complete this data - retrieving operation. Now we try to combine these two queries into a
private void FillStudentReader(SqlDataReader StudentReader)
{
int intIndex = 0;
for (int pos2 = 0; pos2 <= 6; pos2++) //Initialize the textbox array
StudentTextBox[pos2] = new TextBox();
MapStudentTextBox(StudentTextBox);
while (StudentReader.Read())
{
for (intIndex = 0; intIndex <= StudentReader.FieldCount - 1; intIndex++)
StudentTextBox[intIndex].Text = StudentReader.GetValue(intIndex).ToString();
}
}

Figure 5.134
Running status of the Student form.

Figure 5.135
New stored procedure: StudentCourseINTO.
c05.indd 379c05.indd 379 2/11/2010 2:58:56 PM2/11/2010 2:58:56 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
380
Chapter 5 Data Selection Query with Visual C#.NET
Let ’ s give a detailed discussion for this piece of coding.
A. The stored procedure is named dbo.StudentCourseINTO.
B. The input parameter is the student name, @stdName, which is a varying - char variable
with the maximum characters of 50. All parameters, no matter input or output, must be
declared inside the braces.
C. The local variable @stdID is used to hold the returned query result from the fi rst SQL
statement that retrieves the student_id.
D. T h e fi rst SQL statement is executed to get the student_id from the Student table based
on the input parameter @stdName. A SET command must be used to assign the returned
result from the fi rst SQL query to the local variable (or intermediate variable) @stdID.
The fi rst SQL statement must be covered by the parenthesis to indicate that this whole
query will be returned as a single data.
E. The second SQL statement is executed, and this query is used to retrieve all courses
(course_id) taken by the selected student from the StudentCourse table based on the
student_id (@stdID) obtained from the fi rst query.
F. Finally the queried result, all courses or course_id, is returned.
Go to File|Save StoredProcedure1 to save this stored procedure.
Now let ’ s test our stored procedure in the Server Explorer window. Right - click on
our new created stored procedure StudentCourseINTO and select the Execute item from
the pop - up menu. On the opened dialog box, enter the student ’ s name, Erica Johnson;
then click on the OK button to run the procedure. The running result is shown in Figure

methods without including the header and ender of those methods.
Now let ’ s develop the codes for our Select button Click method. Most codes are
identical to those we developed for the Student form, such as the methods FindName()
and FillCourseReader(). Open the Select button Click method by double - clicking on the
Select button from the Designer window, and enter the code shown in Figure 5.137 into
this method. Let ’ s discuss this piece of code step by step to see how it works.
A. The name of our stored procedure, “ dbo.StudentCourseINTO ” , must be declared fi rst,
and this name must be identical with the name we used when we created the stored pro-
cedure in the Server Explorer window.
B. All data components, including the Command, DataAdapter, DataTable, and DataReader
objects, are declared here since we need to use them for this data query operation.
C. The method FindName() is called to get and display the matched student image fi le, and
the returned name of the image fi le is stored in the local string variable strName.
D. The Command object is initialized with suitable properties by executing the user - defi ned
method BuildCommand(). The CommandType property must be StoredProcedure to
indicate that this query is to execute a stored procedure. The CommandText property
must be equal to the name of our stored procedure, “ dbo.StudentCourseINTO ” , which is
stored in a string variable strStudentCourse.
E. The input parameter to the stored procedure is the student name, which is obtained from
the student combobox, and it should be added into the Parameters collection property of
the Command object. You need to note that the nominal name @stdName must be identi-
cal with the input parameter name we defi ned in the parameter braces in our stored
procedure dbo.StudentCourseINTO. The real parameter is entered by the user as the
project runs. The fi nished Command object is assigned to the SelectCommand property
of the DataAdapter, which will be used later to fetch the desired course_id from the
StudentCourse table.
c05.indd 381c05.indd 381 2/11/2010 2:58:59 PM2/11/2010 2:58:59 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
382


StudentCourseDataAdapter.SelectCommand = sqlCmdStudentCourse;
if (ComboMethod.Text == "DataAdapter Method")
{
StudentCourseDataAdapter.Fill(sqlStudentCourseTable);
if (sqlStudentCourseTable.Rows.Count > 0)
FillCourseList(sqlStudentCourseTable);
else
MessageBox.Show("No matched course_id found!");
}
else //DataReader Method is selected
{
sqlStudentCourseReader = sqlCmdStudentCourse.ExecuteReader();
if (sqlStudentCourseReader.HasRows == true)
FillCourseReader(sqlStudentCourseReader);
else
MessageBox.Show("No matched course_id found!"
);
sqlStudentCourseReader.Close();
}
sqlStudentCourseTable.Dispose();
StudentCourseDataAdapter.Dispose();
sqlCmdStudentCourse.Dispose();
}
A
B
C
D
E
F
G

need to disconnect the connection you set up before using the Server Explorer by reboot-
ing the computer, and then reopen the project to run it. A possible solution to this
problem is that you can copy the completed database fi le CSE_DEPT.mdf into another
folder in your computer; in our case, it is C:\database folder, and connect your project to
this database fi le using the Server Explorer. You can use the same database fi le CSE_
DEPT.mdf that is located at the default SQL Server 2005 database folder, C:\Program
Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\Data, as your target database and
connect it to your project using the codes. The prerequisite to use this possible solution
is that two database fi les must be identical, and both fi les are fi nal versions without any
further modifi cation.
private void FillCourseList(DataTable StudentCourseTable)
{
CourseList.Items.Clear();
foreach (DataRow row in StudentCourseTable.Rows)
{
CourseList.Items.Add(row[0]); //the 1st column is course_id - strStudentCourse
}
}
private void FillCourseReader(SqlDataReader StudentCourseReader)
{
int pos = 0;
CourseList.Items.Clear();
while (StudentCourseReader.Read())
{
for (pos = 0; pos <= StudentCourseReader.FieldCount - 1; pos++)
CourseList.Items.Add(StudentCourseReader.GetValue(pos).ToString());
}
}
SQLSelectRTObject.SPForm FillCourseList()


First, let ’ s create our main stored procedure — dbo.StudentAndCourse. Enter the
codes shown in Figure 5.140 into our main stored procedure.
Figure 5.139
Running status of calling stored procedure.
c05.indd 384c05.indd 384 2/11/2010 2:58:59 PM2/11/2010 2:58:59 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.19 Query Data Using Runtime Objects to SQL Server Database

385
The functionality of each line of the coding is:
A. The name of the main stored procedure is declared fi rst, which is dbo.StudentAndCourse.
This name must be identical with the name of the stored procedure used in our Visual C#
2008 project later.
B. The input parameter @StudentName is declared here.
C. The local variable @StudentID is used as an output parameter for the child stored proce-
dure that will return this parameter, student_id, to our main procedure.
D. Call the child stored procedure to execute it using the command EXEC. This calling passes
two parameters to the child stored procedure: the input parameter to the child procedure
@StudentName and the output parameter @StudentID. The latter must be indicated with
the keyword OUTPUT. Later in the child stored procedure, you must also declare this
parameter as an output parameter using the keyword OUTPUT to match its defi nition
defi ned in this main stored procedure.
E. After the child stored procedure is executed, it returns the student_id. Now we can
perform our main query to obtain all courses taken by the selected student from the
StudentCourse table based on the student_id returned by the child stored procedure.
F. The retrieved courses are returned to the calling procedure developed in Visual C# 2008.
Click on the File | Save StoredProcedure1 item to save the main stored procedure.
Second, let ’ s create our child stored procedure. Right - click on the Stored Procedures
folder and select the Add New Stored Procedure item from the pop - up menu. On the

stored procedure ’ s name declared in the cmdSelect_Click() method and the nominal
input parameter name to the stored procedure. Change the name of the stored procedure
from “ dbo.StudentCourseINTO ” to “ dbo.StudentAndCourse ” (refer to line A in Figure
5.137 ), and change the nominal parameter ’ s name from “ @stdName ” to “ @StudentName ”
(refer to line E in Figure 5.137 ). Then you can run this project to get the same result,
which is shown in Figure 5.139 , as we got from the last project.
At this point, we fi nished developing the data - driven project using the general real -
time object for the SQL Server database. A complete project named SQLSelectRTObject
can be found in the folder DBProjects\Chapter 5 located at the accompanying ftp site
(see Chapter 1 ).
Now let ’ s go to the next part in this chapter — develop a data - driven application using
the LINQ to SQL with the SQL Server database.
5.19.3 Query Data Using LINQ to SQL Technique
As we discussed in Chapter 4 , LINQ to SQL is an application programming interface
(API) that allows users to easily and conveniently access the SQL Server database from
the Standard Query Operators (SQO) related to the LINQ. To use this API, you must
Figure 5.141
Child stored procedure.
c05.indd 386c05.indd 386 2/11/2010 2:59:02 PM2/11/2010 2:59:02 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

5.19 Query Data Using Runtime Objects to SQL Server Database

387
fi rst set up a mapping relationship between your relational database and a group of
objects that are instantiated from entity classes. The LINQ to SQL or the Standard Query
Operators will interface to these entity classes to perform the real database operations.
In other words, each entity class can be mapped or is equivalent to a physical data table
in the database, and each entity class ’ s property can be mapped or is equivalent to a data
column in that table. Once this mapping relationship has been set up, one can use the

represented by graphic icons into the GUI. Obviously, the second method or tool is more
convenient and easier compared with the fi rst one.
In this section, we will develop a new sample Visual C# 2008 project named
SQLSelectRTObjectLINQ and use it to illustrate how to perform the data query using
the LINQ to SQL method step by step. Now let ’ s create a new Visual C# project in our
default folder C:\Book6\Chapter 5. For your convenience, a blank project with fi ve form
windows has been created, and you can directly use this blank project to develop your
codes to perform the LINQ to SQL data query. This project, SQLSelectRTObjectLINQ,
is located at the folder DBProjects\Chapter 5 at the accompanying ftp site (see Chapter
1 ), and you can copy and paste it into your folder to use it.
5.19.3.1 Create Entity Classes and Connect DataContext to Database
We have provided a very detailed discussion about the entity classes and DataContext
object as well as how to use the Object Relational Designer to create and add these
components to a data - driven project to perform LINQ to SQL queries in Section 4.6.1 .
Before we can continue to go to the next step, refer to that section to get a clear picture
of how to create and use these components.
5.19.3.2 Query Data Using LINQ to SQL for LogIn Form
When we fi nished reviewing for Section 4.6.1 in Chapter 4 , we can continue to complete
our sample project. First, we need to create a fi eld variable cse_dept based on our derived
DataContext class CSE_DEPTDataContext. As we discussed in the last section, this
object is used to connect to our sample database. Four overloaded constructors are avail-
able for this DataContext class, but in this application we will use the simplest one to
simplify our coding process. Open the code window of the LogInForm and enter the
codes shown in Figure 5.143 into this code section.
Let ’ s look at this piece of code to see how it works.
A. The namespace System.Data.Linq is added into this code section since we need to use all
data components related to LINQ to SQL, and this namespace contains all of them.
B. A new instance of our derived class CSE_DEPTDataContext is created with the fi rst
constructor. A trick here is that no connection string is included in this connection object.
Yes, but where is the connection string? How can we connect to our database without

using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace SQLSelectRTObjectLINQ
{
public partial class LogInForm : Form
{
public CSE_DEPTDataContext cse_dept = new CSE_DEPTDataContext();
public LogInForm()
{
InitializeComponent();
}
}
A
B
SQLSelectRTObjectLINQ.LogInForm LogInForm()

Figure 5.143
Coding for the creation of the fi eld variable.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="MSSQLSelectRTObjectLINQ.Properties.Settings.CSE_DEPTConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=&quot;C:\Program Files

many faculty_ids and students_ids can exist in the LogIn table, but only a single or unique
faculty_id can be in the Faculty table and a unique student_id in the Student table. In
other words, the LogIn class is in the many (child) side of a one - to - many relationship.
Therefore, generally, the member variable of this kind of entity class is named LogIn s ,
and an “ s ” is attached to the name of the related entity class.
D. The foreach loop is utilized to pick up each column from the selected data row log, which
is obtained from the loginfo we get from the LINQ query. Then, assign two columns, log.
user_name and log.pass_word, to our two local string variables, username and password.
private void cmdLogIn_Click(object sender, EventArgs e)
{
string username = string.Empty;
string password = string.Empty;
SelectionForm selForm = new SelectionForm();
IQueryable<LogIn> loginfo = from lg in cse_dept.LogIns
where lg.user_name == txtUserName.Text &&
lg.pass_word == txtPassWord.Text
select lg;
foreach (LogIn log in loginfo)
{
username = (string)log.user_name;
password = (string)log.pass_word;
}
if (txtUserName.Text == string.Empty || txtPassWord.Text == string.Empty)
MessageBox.Show("Enter a valid username/password");
else if (username == txtUserName.Text && password == txtPassWord.Text)
{
MessageBox.Show("The LogIn is successful!");
selForm.Show();
this.Hide();
}

the LogIn table, a successful message is displayed and our next form, SelectionForm, is
displayed and the current form is removed from the screen. The point is that this successful
message is only for the testing purpose and it should be commented out during the normal
running process of this project.
G. If no matched username and password can be found, an error message is displayed to
indicate this situation.
It looks like this query is quite simple, and all columns in the LogIn table have been
in there even if we did not explicitly perform any query to that table. The reason behind
this simple query is that no login information is actually retrieved until all columns in the
LogIn class are referenced, and this is called deferred loading. This terminology is used
to describe the type of loading in which columns are not actually loaded from the database
until they are required or referenced.
The code for the Cancel button Click method is easy and it is shown in Figure 5.146 .
The coding for the user - defi ned method getLogInForm() is also shown in this fi gure. This
is all the coding developments for the LogIn form. Before we can test this coding, we
prefer to fi nish the coding for the next form, SelectionForm.
5.19.3.3 Coding for Selection Form
The coding for this form is basically identical with the same form we did for the last
SQLSelectRTObject project, and the only difference is that the database connection
object we used in this project is an instance of the derived class CSE_DEPTDataContext.
Another point is that we do not need to add the namespace System.Data.Linq to the
namespace declaration section in this form since the LINQ to SQL will not be used for
this form.
private void cmdCancel_Click(object sender, EventArgs e)
{
cse_dept.Connection.Close();
Application.Exit();
}
public LogInForm getLogInForm()
{

public partial class SelectionForm : Form
{
FacultyForm facultyForm = new FacultyForm();
CourseForm courseForm = new CourseForm();
StudentForm studentForm = new StudentForm();
public SelectionForm()
{
InitializeComponent();
this.ComboSelection.Items.Add("Faculty Information");
this.ComboSelection.Items.Add("Course Information");
this.ComboSelection.Items.Add("Student Information");
this.ComboSelection.SelectedIndex = 0;
}
private void cmdOK_Click(object sender, EventArgs e)
{
if (this.ComboSelection.Text == "Faculty Information")
facultyForm.Show();
else if (this.ComboSelection.Text == "Course Information")
courseForm.Show();
else
if (this.ComboSelection.Text == "Student Information")
studentForm.Show();
else
MessageBox.Show("Invalid Selection!");
}
private void cmdExit_Click(object sender, EventArgs e)
{
LogInForm logForm = new LogInForm();

logForm = logForm.getLogInForm();