Practical Database Programming With Visual C#.NET- P8

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ả
Nội dung trích xuất từ tài liệu:
Practical Database Programming With Visual C#.NET- P8 5.19 Query Data Using Runtime Objects to SQL Server Database 373Figure 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.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 Students 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. 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 FillStudentTe ...