Practical Database Programming With Visual C#.NET- P8

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
74
lượt xem
13
download

Practical Database Programming With Visual C#.NET- P8

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'practical database programming with visual c#.net- p8', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Practical Database Programming With Visual C#.NET- P8

  1. 5.19 Query Data Using Runtime Objects to SQL Server Database 373 Figure 5.129 Testing result for our second stored procedure. C. The FindName() function is executed to get the student’s photo file based on the student’s name. The returned student’s image file is assigned to the local string variable strName. D. The user-defined method BuildCommand() is called to initialize the first 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” 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) as the project runs. This dynamic parameter must be added into the Parameters collection 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 first by rebooting your computer.
  2. 374 Chapter 5 Data Selection Query with Visual C#.NET SQLSelectRTObject.StudentForm cmdSelect_Click() private void cmdSelect_Click(object sender, EventArgs e) { A string strStudent = "dbo.StudentInfo"; string strStudentCourse = "dbo.StudentCourseInfo"; B SqlDataAdapter StudentDataAdapter = new SqlDataAdapter(); SqlDataAdapter StudentCourseDataAdapter = new SqlDataAdapter(); SqlCommand sqlCmdStudent = new SqlCommand(); SqlCommand sqlCmdStudentCourse = new SqlCommand(); DataTable sqlStudentTable = new DataTable(); DataTable sqlStudentCourseTable = new DataTable(); SqlDataReader sqlStudentReader, sqlStudentCourseReader; string strName = string.Empty; C strName = FindName(ComboName.Text); if (strName == "No Match") MessageBox.Show("No Matched Student's Image Found!"); D BuildCommand(ref sqlCmdStudent, strStudent); E sqlCmdStudent.Parameters.Add("@StudentName", SqlDbType.Char).Value = ComboName.Text; StudentDataAdapter.SelectCommand = sqlCmdStudent; F if (ComboMethod.Text == "DataAdapter Method") { StudentDataAdapter.Fill(sqlStudentTable); G if (sqlStudentTable.Rows.Count > 0) FillStudentTextBox(sqlStudentTable); else MessageBox.Show("No matched student found!"); H BuildCommand(ref sqlCmdStudentCourse, strStudentCourse); I sqlCmdStudentCourse.Parameters.Add("@StudentID", SqlDbType.Char).Value = txtID.Text; StudentCourseDataAdapter.SelectCommand = sqlCmdStudentCourse; J StudentCourseDataAdapter.Fill(sqlStudentCourseTable); K if (sqlStudentCourseTable.Rows.Count > 0) FillCourseList(sqlStudentCourseTable); else MessageBox.Show("No matched course_id found!"); } else //DataReader Method is selected { L sqlStudentReader = sqlCmdStudent.ExecuteReader(); M if (sqlStudentReader.HasRows == true) FillStudentReader(sqlStudentReader); N else MessageBox.Show("No matched student found!"); O BuildCommand(ref sqlCmdStudentCourse, strStudentCourse); sqlCmdStudentCourse.Parameters.Add("@StudentID", SqlDbType.Char).Value = txtID.Text; StudentCourseDataAdapter.SelectCommand = sqlCmdStudentCourse; P sqlStudentCourseReader = sqlCmdStudentCourse.ExecuteReader(); Q if (sqlStudentCourseReader.HasRows == true) FillCourseReader(sqlStudentCourseReader); else MessageBox.Show("No matched course_id found!"); R sqlStudentReader.Close(); sqlStudentCourseReader.Close(); } S sqlStudentTable.Dispose(); sqlStudentCourseTable.Dispose(); StudentDataAdapter.Dispose(); StudentCourseDataAdapter.Dispose(); sqlCmdStudent.Dispose(); sqlCmdStudentCourse.Dispose(); } Figure 5.130 Coding for the Select button Click method.
  3. 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 fill the Student table, which actually calls our first stored procedure to fill 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 filled into the Student table, and the user-defined method FillStudentTextBox() is called to fill six textboxes in the Student form with six pieces of retrieved columns from the stored procedure. Otherwise, an error message is displayed if this fill has failed. H. The user-defined 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. I. The unique input parameter to the stored procedure dbo.StudentCourseInfo is the StudentID, which is obtained from the calling of the first stored procedure and stored in the student ID textbox txtID. This dynamic parameter must be added into the Parameters collection that is the property of the Command class by using the Add() method before the stored procedure can be executed. The initialized Command object sqlCmdStudentCourse is assigned to the SelectCommand property of the another DataAdapter to make it ready to be used in the next step. J. The Fill() method of the DataAdapter is called to fill the StudentCourse table, which calls our second stored procedure to fill the StudentCourse table. K. If this calling is successful, the Count property should be greater than 0, which means that at least one row is filled into the StudentCourse table, and the user-defined method FillCourseList() is called to fill the CourseList box in the Student form with all courses (course_id) retrieved from the stored procedure. Otherwise, an error message is displayed if this fill has failed. L. If the user selects the DataReader method, the ExecuteReader() method is called to retrieve back all information related to the selected student and assign it to the sqlStudentReader object. M. If this method is executed successfully, which means that at least one row of data is read out and assigned to the DataReader, the HasRows property should be true, and the user- defined method FillStudentReader() is executed to fill the related information to six textboxes in the Student form. N. Otherwise, an error message is displayed to indicate this situation. O. The BuildCommand() method is called to build the StudentCourse Command object with our second stored procedure. The unique input parameter to this stored procedure is the StudentID that is added to the Parameters collection that is a property of the Command object. The finished Command object is assigned to the SelectCommand property of the StudentCourse DataAdapter.
  4. 376 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-defined method FillCourseReader() is executed to fill 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 file from this subroutine, note that you must store all students’ image files in the folder in which your Visual C# 2008 pro- ject’s executable file is located. In our application, this folder is C:\Book6\Chapter 5\ SQLSelectRTObject\bin\Debug. If you place those students’ image files in another folder, you must provide a full name, which includes the drive name, path, and the image file name, for that student’s image file 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 FillCourseList() are shown in Figure 5.132. In total we need six pieces of information for the selected student, so a six-dimension textbox array StudentTextBox[5] is used (the index is based on 0). In the method FillStudentTextBox(), a double foreach loop is used to retrieve each queried column from the returned row. The outer loop is only executed one time since we have only retrieved back one data row from the Student table. Each fetched column is assigned to the associated textbox to be displayed in the Student form. The method SQLSelectRTObject.StudentForm BuildCommand() private void BuildCommand(ref SqlCommand cmdObj, string cmdString) { LogInForm logForm = new LogInForm(); logForm = logForm.getLogInForm(); cmdObj.Connection = logForm.sqlConnection; cmdObj.CommandType = CommandType.StoredProcedure; cmdObj.CommandText = cmdString; } Figure 5.131 Coding for the BuildCommand method.
  5. 5.19 Query Data Using Runtime Objects to SQL Server Database 377 SQLSelectRTObject.StudentForm FillStudentTextBox() private void FillStudentTextBox(DataTable StudentTable) { int pos1 =0; for (int pos2 = 0; pos2
  6. 378 Chapter 5 Data Selection Query with Visual C#.NET SQLSelectRTObject.StudentForm FillStudentReader() private void FillStudentReader(SqlDataReader StudentReader) { int intIndex = 0; for (int pos2 = 0; pos2
  7. 5.19 Query Data Using Runtime Objects to SQL Server Database 379 Figure 5.134 Running status of the Student form. Figure 5.135 New stored procedure: StudentCourseINTO. single stored procedure to simplify our data-querying operation. First let’s create our stored procedure. Open Visual Studio.NET and open the Server Explorer window; click on the plus symbol icon that is next to the CSE_DEPT database folder to connect to our database if this database was added into the Server Explorer before. Otherwise you need to right- click on the Data Connections folder to add and connect to our database. Right-click on the Stored Procedures folder and select the Add New Stored Procedure item to open the Add Procedure dialog box, and then enter the codes shown in Figure 5.135 into this new procedure.
  8. 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 first SQL statement that retrieves the student_id. D. The first 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 first SQL query to the local variable (or intermediate variable) @stdID. The first 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 first 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 5.136. Figure 5.136 Running result of the stored procedure.
  9. 5.19 Query Data Using Runtime Objects to SQL Server Database 381 Next we need to develop a Visual C# project to call this stored procedure to test the functionality of the stored procedure. To save time and space, we added a new form window into this project and named it SPForm. Open our project SQLSelectRTObject and select Project | Add Windows Form item, enter SP Form.cs into the Name: box and click on the Add button to add this new form into our project. Enter “SPForm” into the name property as the name for this form. Enlarge the size of this SP Form by dragging the border of the form window, and then open the Student form window. We need to copy all controls on the Student form to our new SP form. On the opened Student form, select Edit|Select All and Edit|Copy items, and then open the SP form and select Edit|Paste to paste all controls we copied from the Student form. To save time, next we need to copy most codes from the Student code window to our new SP form code window. But first you need to add the SQL Server-related namespace System.Data.SqlClient to the namespace declaration section on this SP form code window. The only difference is the codes for the Select button Click method, cmdSelect_Click(). Don’t copy this piece of code since we need to develop new codes to test our stored procedure later. To copy all other codes, open the code window of the Student form, select those codes, copy, and then paste them to our new SP form code window. One point you need to note is that when you copy the codes for two methods, such as the constructor of the SP Form and the Back button Click, you must first open the constructor or the Back method in the SP form, and only copy the body of those 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 first, 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 file, and the returned name of the image file is stored in the local string variable strName. D. The Command object is initialized with suitable properties by executing the user-defined 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 defined in the parameter braces in our stored procedure dbo.StudentCourseINTO. The real parameter is entered by the user as the project runs. The finished 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.
  10. 382 Chapter 5 Data Selection Query with Visual C#.NET SQLSelectRTObject.SPForm cmdSelect_Click() private void cmdSelect_Click(object sender, EventArgs e) { A string strStudentCourse = "dbo.StudentCourseINTO"; B SqlDataAdapter StudentCourseDataAdapter = new SqlDataAdapter(); SqlCommand sqlCmdStudentCourse = new SqlCommand(); DataTable sqlStudentCourseTable = new DataTable(); SqlDataReader sqlStudentCourseReader; string strName = string.Empty; C strName = FindName(ComboName.Text); if (strName == "No Match") MessageBox.Show("No Matched Student's Image Found!"); D BuildCommand(ref sqlCmdStudentCourse, strStudentCourse); E sqlCmdStudentCourse.Parameters.Add("@stdName", SqlDbType.Char).Value = ComboName.Text; StudentCourseDataAdapter.SelectCommand = sqlCmdStudentCourse; if (ComboMethod.Text == "DataAdapter Method") { F StudentCourseDataAdapter.Fill(sqlStudentCourseTable); G if (sqlStudentCourseTable.Rows.Count > 0) FillCourseList(sqlStudentCourseTable); else MessageBox.Show("No matched course_id found!"); } else //DataReader Method is selected { H sqlStudentCourseReader = sqlCmdStudentCourse.ExecuteReader(); I if (sqlStudentCourseReader.HasRows == true) FillCourseReader(sqlStudentCourseReader); else MessageBox.Show("No matched course_id found!"); sqlStudentCourseReader.Close(); } J sqlStudentCourseTable.Dispose(); StudentCourseDataAdapter.Dispose(); sqlCmdStudentCourse.Dispose(); } Figure 5.137 Coding for the Select button Click method. F. If the user selected the DataAdapter Method, the Fill() method is called to fill the StudentCourse table with the desired course_id. G. If the Count property is greater than 0, this fill is successful. The user-defined method FillCourseList() is called to fill the fetched course_id into the CourseList listbox. Otherwise an error message is displayed to indicate this situation. H. If the user selected the DataReader Method, the ExecuteReader() method is executed to invoke the DataReader to call our stored procedure. I. If this call is successful, the queried result should be stored in the DataReader with certain rows. The user-defined method FillCourseReader() is executed to fill the returned course_id into the CourseList box in that situation. Otherwise an error message is displayed if this call is failed. J. The cleaning job is performed to release all objects used in this data query operation. Most user-defined methods, such as BuildCommand() and FindName(), are identical with those methods in the Student form without any modification. For your convenience, we show the other two user-defined methods FillCourseList() and FillCourseReader() again in Figure 5.138.
  11. 5.19 Query Data Using Runtime Objects to SQL Server Database 383 SQLSelectRTObject.SPForm FillCourseList() 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
  12. 384 Chapter 5 Data Selection Query with Visual C#.NET Figure 5.139 Running status of calling stored procedure. As the project runs, enter the suitable username and password, and then select the SP Information from the Selection form to open the SP Form window. Select a student name from the student combobox and click on the Select button. All courses taken by the selected student will be displayed in the CourseList box, which is shown in Figure 5.139. The testing result for our stored procedure is very good. Is that correct? The answer is yes, but we may do a little more to develop a more sophisticated stored procedure to meet our special requirement. What is our special requirement? We want to develop some nested stored procedures and call the nested stored procedure from our main or parent stored procedure. Sounds complicated? Yes, but we try to make this complicated issue simple with the following example. 5.19.2.7.5 Query Data Using Nested Stored Procedures The so-called nested stored procedure is very similar to subroutines or subqueries, which means that a main stored procedure can be considered as a parent stored procedure, and a substored pro- cedure can be considered as a child stored procedure. The parent stored procedure can call the child stored procedure as it likes. In this section, we try to create two stored procedures; one is a main procedure and the other is a child stored procedure. The main stored procedure, StudentAndCourse, is used to get all courses taken by the selected student from the StudentCourse table based on the student_id, and the child stored pro- cedure, StudentInfoID, is used to get the student_id from the Student table based on the input student name. Now open the Server Explorer window and connect to our database CSE_DEPT. After the database is connected, right-click on the Stored Procedures folder and select the Add New Stored Procedure item to open the Add Procedure dialog box. First, let’s create our main stored procedure—dbo.StudentAndCourse. Enter the codes shown in Figure 5.140 into our main stored procedure.
  13. 5.19 Query Data Using Runtime Objects to SQL Server Database 385 Figure 5.140 Main stored procedure. The functionality of each line of the coding is: A. The name of the main stored procedure is declared first, 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 definition defined 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 opened dialog box, enter the codes shown in Figure 5.141 into this new stored procedure. The functionality for each coding line is explained below: A. The name of the child stored procedure is declared first, which is dbo.StudentInfoID. This name must be identical with the name used by our main stored procedure when this child procedure is called. B. Two parameters are declared here: The first one, @sName, is the input, and the second, @sID, is the output parameter. The default type of parameter is INPUT, so the keyword OUTPUT must be attached for the second parameter since it is an output and will be returned to the main stored procedure.
  14. 386 Chapter 5 Data Selection Query with Visual C#.NET Figure 5.141 Child stored procedure. C. The SQL statement is executed to get the desired student_id from the Student table based on the input student name. The returned student_id will be assigned to the output param- eter @sID by using the SET command. D. The output parameter @sID is returned to the main stored procedure. Click on the File|Save StoredProcedure1 item to save our child stored procedure. To test both main and child stored procedure in the Server Explorer window, right- click on the main stored procedure StudentAndCourse item, and then select Execute item to open the Run Stored Procedure dialog. Enter a student name, such as Erica Johnson, and click on the OK button to run both stored procedures. The running result is shown in Figure 5.142. Our nested stored pro- cedures work fine! To call this nested stored procedure, we need to develop a Visual C# 2008 project. In order to save time and space, you can use the coding we developed for the SPForm window in the last section. All coding is the same and the only modifications are 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 finished 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
  15. 5.19 Query Data Using Runtime Objects to SQL Server Database 387 Figure 5.142 Running result of the nested stored procedure. first 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 LINQ to SQL to access and manipulate data against the databases. After entity classes are created and the mapping relationships between each physical table and each entity class has been built, the conversion for data operations between the entity class and the real data table is needed. The class DataContext will function in this role. Basically, the DataContext is a connection class that is used to establish a connec- tion between your project and your database. In addition to this connection role, the DataContext also provides the conversion function to convert or interpret operations of the Standard Query Operators for the entity classes to the SQL statements that can be run in real databases. The procedure to use LINQ to SQL to perform data actions against SQL Server database can be described in the sequence listed below: 1. Add the System.Data.Linq.dll assembly into the project that will use LINQ to SQL by adding the reference System.Data.Linq. 2. Create an entity class for each data table by using one of two popular tools: SQLMetal or Object Relational Designer. 3. Add a connection to the selected database using the DataContext class or the derived class from the DataContext class. 4. Use LINQ to SQL to access the database to perform desired data actions.
  16. 388 Chapter 5 Data Selection Query with Visual C#.NET The difference between the SQLMetal and the Object Relational Designer is that the former is a console-based application but the latter is a window-based application. This means that the SQLMetal provides a DOS-like template, and the operations are performed by entering single commands into a black-white window. The Object Relational Designer provides a graphic user interface (GUI) and allows users to drag-place tables represented by graphic icons into the GUI. Obviously, the second method or tool is more convenient and easier compared with the first 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 five 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 finished reviewing for Section 4.6.1 in Chapter 4, we can continue to complete our sample project. First, we need to create a field 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 first 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 connection string? However, the connection string is in there. Where? Let’s see. Open the application configuration file named app.config located at the Solution Explorer window by double-clicking on it. A sample file is shown in Figure 5.144. It can be found from this XML file that the connection string is already in there under the ConnectionString tag, which includes the location of the sample database file CSE_ DEPT.mdf. In this application, it is the default folder of the SQL Server 2005 database file, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.
  17. 5.19 Query Data Using Runtime Objects to SQL Server Database 389 SQLSelectRTObjectLINQ.LogInForm LogInForm() using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; A using System.Data.Linq; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace SQLSelectRTObjectLINQ { public partial class LogInForm : Form { B public CSE_DEPTDataContext cse_dept = new CSE_DEPTDataContext(); public LogInForm() { InitializeComponent(); } } Figure 5.143 Coding for the creation of the field variable. Figure 5.144 Sample coding for the app.config file. When you create a new instance of the derived DataContext class to connect to the sample database, the system can automatically locate this connection string from this configuration file and use it to do this connection even we did not clearly indicate this connection string. The instance cse_dept has finished the connection when you create it, and save this connection in this instance when it is created. Now we can use this connec- tion object to perform the data actions against the database. Now let’s develop the codes for the login process. Since we only deal with this login process using the LINQ to SQL, we only need one LogIn button in this LogInForm window. Delete one button from this form window and rename the left button as cmdLogIn and change its Text property to LogIn. Double-click on the LogIn button from the form window to open its Click method, and enter the codes shown in Figure 5.145 into this method. Let’s take a closer look at this piece of code to see how it works. A. Two local-level string variables, username and password, are created, and these two vari- ables are used to hold the returned queried data from the LogIn table.
  18. 390 Chapter 5 Data Selection Query with Visual C#.NET SQLSelectRTObjectLINQ.LogInForm cmdLogIn_Click() private void cmdLogIn_Click(object sender, EventArgs e) { A string username = string.Empty; string password = string.Empty; B SelectionForm selForm = new SelectionForm(); C IQueryable loginfo = from lg in cse_dept.LogIns where lg.user_name == txtUserName.Text && lg.pass_word == txtPassWord.Text select lg; D foreach (LogIn log in loginfo) { username = (string)log.user_name; password = (string)log.pass_word; } E if (txtUserName.Text == string.Empty || txtPassWord.Text == string.Empty) MessageBox.Show("Enter a valid username/password"); F else if (username == txtUserName.Text && password == txtPassWord.Text) { MessageBox.Show("The LogIn is successful!"); selForm.Show(); this.Hide(); } G else MessageBox.Show("The LogIn is failed!"); } Figure 5.145 Codes for the LogIn button Click method. B. An instance of the next form class SelectionForm, selForm, is created and this form will be displayed if the login process is completed successfully. C. As we discussed in the last section when we built the entity classes for this project, all five entity classes are related or bounded by using the associations (primary-foreign keys). Also recall that in Chapter 4 we discussed that most normal LINQ queries are performed on arrays or collections that apply the IEnumerable or IEnumerable interfaces, but in LINQ to SQL queries, they are performed on entity classes that apply the IQueryable interface. This means that besides the Standard Query Operators, LINQ to SQL queries have additional query operators available since IQueryable applies IEnumerable. Here querying an associated entity class in LINQ to SQL is used with the IQueryable interface, and the is the type of member variable of the related entity class. In this case, it is our LogIn table. An iteration variable lg is used to iterate over the result of this query from the LogIn table. Then a similar SQL SELECT statement is executed with the WHERE clause. Two criteria are used for this query, user_name and pass_word, and these two criteria are connected with a logic AND operator. Note the member variable of our entity class LogIn, which is named LogIns in this query. The relationships or the associa- tions between the LogIn, Faculty, and Student tables are many-to-one, which means that 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 LogIns, 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.
  19. 5.19 Query Data Using Runtime Objects to SQL Server Database 391 The purpose of this assignment operation is to avoid the possible overhead cycles when identifying the validity of the username and password entered by the user. In other words, we prefer to do this validation outside of this foreach loop. You can try to do it inside this loop but definitely you would encounter some bugs. Since we are using a typed data table and database, we can directly access each column by using its name without using the field and the column’s name as the position for each of them. E. An error message will be displayed if any of input box is empty to remind user to enter valid username and password. F. If both username and password are correct and matched to both columns queried from 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-defined method getLogInForm() is also shown in this figure. This is all the coding developments for the LogIn form. Before we can test this coding, we prefer to finish 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. SQLSelectRTObjectLINQ.LogInForm cmdCancel_Click() private void cmdCancel_Click(object sender, EventArgs e) { A cse_dept.Connection.Close(); B Application.Exit(); } public LogInForm getLogInForm() { return this; } Figure 5.146 Codes for the Cancel button Click method.
  20. 392 Chapter 5 Data Selection Query with Visual C#.NET SQLSelectRTObjectLINQ.SelectionForm SelectionForm() using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace SQLSelectRTObjectLINQ { 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(); A if (logForm.cse_dept.Connection.State == ConnectionState.Open) logForm.cse_dept.Connection.Close(); B logForm.Close(); courseForm.Close(); facultyForm.Close(); studentForm.Close(); Application.Exit(); } } } Figure 5.147 Codes for the SelectionForm. Open the SelectionForm and enter the codes shown in Figure 5.147 into the code window of this form. Most of code is identical with the same form we did for the SQLSelectRTObject project except for the following codes: A. When we click on the Exit button, first we need to check whether our connection object is still active or open. If it is open, we need to close this connection before we can exit the project. Otherwise a bug would be created in this project if you exit the project without closing the database connection.
Đồng bộ tài khoản