Practical Database Programming With Visual C#.NET- P9

Tham khảo tài liệu practical database programming with visual c#.net- p9, 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- P9 5.20 Query Data Using Runtime Objects to Oracle Database 423Figure 5.175 Opened Create Package window.Figure 5.176 Name page of the Package window. The prototype of the procedure SelectFacultyCourse() is declared in line 3. Twoarguments are used for this procedure: input parameter FacultyName, which is indicatedas an input by using the keyword IN followed by the data type of VARCHAR2. Theoutput parameter is a cursor named FacultyCourse followed by the keyword OUT. EachPL-SQL statement must end with a semicolon, and this rule also applies to the ENDstatement.424 Chapter 5 Data Selection Query with Visual C#.NETFigure 5.177 Opened Specification page.Figure 5.178 Coding for the Specification page. Click on the Finish button to complete this step. You can click on the Compile buttonto compile this specification block if you like. Next we need to create the body block ofthis package. Click on the Body tab to open the Body page, which is shown in Figure5.179. Click on the Edit button to begin to create our body part. Enter the PL-SQL codesinto this body shown in Figure 5.180. The procedure prototype is redeclared in line 2. But an IS operator is attached at theend of this prototype and it is used to replace the AS operator to indicate that this proce- 5.20 Query Data Using Runtime Objects to Oracle Database 425Figure 5.179 Opened Body page of the package.Figure 5.180 Coding for the Body part of the package.dure needs to use a local variable facultyId, and this variable will work as an intermediatevariable to hold the returned faculty_id from the first query, which is located at line 6. Starting from BEGIN, our real SQL statements are included in lines 6 and 7. Thefirst query is to get the faculty_id from the Faculty table based on the input parameterFacultyName, which is the first argument of this procedure. A SELECT … INTO state-ment is utilized to temporarily store the returned faculty_id into the intermediate variablefacultyId.426 Chapter 5 Data Selection Query with Visual C#.NETFigure 5.181 Compiled coding for the body part of the package. The OPEN FacultyCourse FOR command is used to assign the returned data columnsfrom the following query to the cursor variable FacultyCourse. Recall that we used a SETcommand to perform this assignment functionality in the SQL Server stored procedurein Section 5.19.2.7.4. Starting from lines 9 and 10, the second query is declared, and it isto get all course_id and courses taught by the selected faculty from the Course table basedon the intermediate variable’s value, faculty_id, which is obtained from the first queryabove. The queried results are assigned to the cursor variable FacultyCourse. Now let’s compile our package by clicking on the Compile button. A successfulcompiling information PL/SQL code successfully compiled (22:20:06)will be displayed if this package is bug free, which is shown in Figure 5.181. The development of our Oracle package is complete, and now let’s go to the VisualStudio.NET to call this package to perform our course query for our Course form.5.20.3.9 Query Data Using Oracle Package for Course FormOpen the Course form window and double-click on the Select button to open its Clickmethod and enter the codes shown in Figure 5.182 into this method. Let’s take a look at this piece of code to see how it works. A. The package query string is declared, and this string contains both the package’s name (Faculty_Course) and the stored procedure’s name (Select FacultyCourse). All query strings for the Oracle database package must follow this style. The package’s name and the procedure’s name defined in this string must be identical with those names we used when we created this package in the Object Browser in Oracle Database 10g XE. Otherwise your calling to this package would fail. 5.20 Query Data Using Runtime Objects to Oracle Database 427 OracleSelectRTObject.CourseForm cmdSelect_Click() private void cmdSelect_Click(object sender, EventArgs e) {A string cmdString = Faculty_Course.SelectFacultyCourse;B OracleParameter paramFacultyName = new OracleParameter(); OracleParameter paramFacultyCourse = new OracleParameter(); OracleDataAdapter CourseDataAdapter = new OracleDataAdapter(); OracleCommand oraCommand = new OracleCommand(); DataTable oraDataTable = new DataTable(); OracleDataReader oraDataReader; LogInForm logForm = new LogInForm(); logForm = logForm.getLogInForm();C paramFacultyName.ParameterName = FacultyName; ...