reading

Cover
About the Author
Preface
Acknowledgment
About the Companion Website
Chapter 1: Introduction
1. 1.1 Outstanding Features About This Book
2. 1.2 This Book Is For
3. 1.3 What This Book Covers
4. 1.4 How This Book Is Organized and How to Use This Book
5. 1.5 How to Use Source Codes and Sample Database
6. 1.6 Instructors and Customers Supports
Chapter 2: Introduction to Databases
1. 2.1 What Are Databases and Database Programs?
2. 2.2 Develop a Database
3. 2.3 Sample Database
4. 2.4 Identifying Keys
5. 2.5 Define Relationships
6. 2.6 ER Notation
7. 2.7 Data Normalization
8. 2.8 Database Components in Some Popular Databases
9. 2.9 Create Microsoft SQL Server 2017 Express Sample Database
10. 2.10 Chapter Summary
11. Homework
Chapter 3: Introduction to ADO.NET
1. 3.1 The ADO and ADO.NET
2. 3.2 Overview of the ADO.NET
3. 3.3 The Architecture of the ADO.NET
4. 3.4 The Components of ADO.NET
5. 3.5 Chapter Summary
6. Homework
Chapter 4: Introduction to Language Integrated Query (LINQ)
1. 4.1 Overview of Language Integrated Query
2. 4.2 Introduction to LINQ Query
3. 4.3 The Architecture and Components of LINQ
4. 4.4 LINQ to Objects
5. 4.4.3 LINQ and File Directories
6. 4.5 LINQ to DataSet
7. 4.6 LINQ to SQL
8. 4.7 LINQ to Entities
9. 4.7.3 The ObjectQuery Component
10. 4.7.4 LINQ to Entities Flow of Execution
11. 4.7.5 Implementation of LINQ to Entities
12. 4.8 LINQ to XML
13. 4.9 Visual Basic.NET Language Enhancement for LINQ
14. 4.10 Chapter Summary
15. Homework
Chapter 5: Data Selection Query with Visual Basic.NET
1. PART IData Query with Visual Studio.NET Design Tools and Wizards
2. 5.1 A Completed Sample Database Application Example
3. 5.2 Visual Studio.NET Design Tools and Wizards
4. 5.3 Query Data from SQL Server Database Using Design Tools and Wizards
5. 5.4 Use Visual Studio Wizards and Design Tools to Query and Display Data
6. PART II Data Query with Runtime Objects
7. 5.5 Introduction to Runtime Objects
8. 5.6 Query Data from SQL Server Database Using Runtime Object
9. 5.7 Chapter Summary
10. Homework
Chapter 6: Data Inserting with Visual Basic.NET
1. PART IInsert Data with Visual Basic.NET Design Tools and Wizards
2. 6.1 Insert Data Into a Database
3. 6.2 Insert Data into the SQL Server Database Using a Sample Project InsertWizard
4. PART IIData Insertion with Runtime Objects
5. 6.3 The General Run Time Objects Method
6. 6.4 Insert Data into the SQL Server Database Using the Run Time Object Method
7. 6.5 Insert Data into the Database Using Stored Procedures
8. 6.6 Insert Data into the Database Using the LINQ To SQL Method
9. 6.7 Chapter Summary
10. Homework
Chapter 7: Data Updating and Deleting with Visual Basic.NET
1. PART I Data Updating and Deleting with Visual Studio.NET Design Tools and Wizards
2. 7.1 Update or Delete Data Against Databases
3. 7.2 Update and Delete Data For Microsoft SQL Server Database
4. PART II Data Updating and Deleting with Runtime Objects
5. 7.3 The Run Time Objects Method
6. 7.4 Update and Delete Data for SQL Server Database Using the Run Time Objects
7. 7.5 Update and Delete Data against SQL Server Database Using Stored Procedures
8. 7.6 Chapter Summary
9. Homework
Chapter 8: Accessing Data in ASP.NET
1. 8.1 What is .NET Framework?
2. 8.2 What is ASP.NET?
3. 8.3 Develop ASP.NET Web Application to Select Data from SQL Server Databases
4. 8.4 Develop ASP.NET Web Application to Insert Data Into SQL Server Databases
5. 8.5 Develop Web Applications to Update and Delete Data in SQL Server Databases
6. 8.6 Develop ASP.NET Web Applications with LINQ to SQL Query
7. 8.7 Chapter Summary
8. Homework
Chapter 9: ASP.NET Web Services
1. 9.1 What Are Web Services and Their Components?
2. 9.2 Procedures to Build a Web Service
3. 9.3 Build ASP.NET Web Service Project to Access SQL Server Database
4. 9.4 Build ASP.NET Web Service Project to Insert Data Into SQL Server Database
5. 9.5 Build ASP.NET Web Service to Update and Delete Data for SQL Server Database
6. 9.6 Build Windows‐Based Web Service Clients to Consume the Web Services
7. 9.7 Build Web‐Based Web Service Clients to Consume the Web Services
8. 9.8 Chapter Summary
9. Homework
Appendix A: Appendix AInstall and Configure SQL Server 2017 Express DatabaseInstall and Configure SQL Server 2017 Express Database
1. A.1 Install SQL Server 2017 Express Database
2. A.2 Install SQL Server 2017 Management Studio
3. A.3 Configure and Setup SQL Server 2017 Express Connection Parameters
Appendix B: Appendix BDownload and Install DevExpress .NET UI ControlsDownload and Install DevExpress .NET UI Controls
Appendix C: Appendix CDownload & Install FrontPage Server Extension for Windows 10Download & Install FrontPage Server Extension for Windows 10
Appendix D: How to Use Sample Database
1. D.1 Use Microsoft SQL Server 2017 Express Database File
Index
End User License Agreement

List of Tables

Chapter 2
1. Table 2.1 LogIn table.
2. Table 2.2 Faculty table.
3. Table 2.3 Course table.
4. Table 2.4 Student table.
5. Table 2.5 StudentCourse table.
6. Table 2.6 Faculty table.
7. Table 2.7 Course (Partial data shown). Faculty (Partial data shown).
8. Table 2.8 Unnormalized Faculty table with repeating groups.
9. Table 2.9 Normalized Faculty table.
10. Table 2.10 The old Course table.
11. Table 2.11 The new Course table.
12. Table 2.12 The new Instructor table.
13. Table 2.13 The data in the LogIn table.
14. Table 2.14 The data in the Faculty table.
15. Table 2.15 The data in the Course table.
16. Table 2.16 The data in the Student table.
17. Table 2.17 The data in the StudentCourse table.
18. Table 2.18 The image files in the Faculty table.
19. Table 2.19 The image files in the Student table.
Chapter 3
1. Table 3.1 Namespaces for different Data Providers, DataSet and DataTable.
2. Table 3.2 The compatibility between the OLEDB and OLEDB.NET.
3. Table 3.3 The Connection classes and databases.
4. Table 3.4 The data types and the associated Data Provider.
5. Table 3.5 The different parameter mappings.
6. Table 3.6 Methods of the Command class.
7. Table 3.7 The constructors of the DataAdapter class.
8. Table 3.8 The public properties of the DataAdapter class.
9. Table 3.9 The public methods of the DataAdapter class.
10. Table 3.10 The events of the DataAdapter class.
11. Table 3.11 Popular properties of the SqlDataReader class.
12. Table 3.12 Popular methods of the SqlDataReader class.
13. Table 3.13 Popular Exceptions of the DataReader class.
14. Table 3.14 Popular constructors of the DataSet class.
15. Table 3.15 Public properties of the DataSet class.
16. Table 3.16 Public methods of the DataSet class.
17. Table 3.17 Public events of the DataSet class.
18. Table 3.18 Three popular constructors of the DataTable class.
19. Table 3.19 The popular properties of the DataTable class.
20. Table 3.20 The popular methods of the DataTable class.
21. Table 3.21 The public events of the DataTable class.
22. Table 3.22 The completed FacultyTable.
Chapter 4
1. Table 4.1 Most Often Used Standard Query Operators.
2. Table 4.2 LINQ Related Namespaces.
3. Table 4.3 Deleted record from the Faculty table.
4. Table 4.4 Deleted record from the LogIn table.
5. Table 4.5 Deleted records from the Course table.
Chapter 5
1. Table 5.1 Relationship between the Form and Data Table.
2. Table 5.2 Relationship between each form and data table.
3. Table 5.3 Controls on the LogIn form.
4. Table 5.4 Controls on the Selection form.
5. Table 5.5 Controls on the Faculty form.
6. Table 5.6 Controls on the Course form.
7. Table 5.7 Controls on the Student form.
8. Table 5.8 A part of Faculty and Course data table.
Chapter 6
1. Table 6.1 TableAdapter DBDirect Methods.
Chapter 7
1. Table 7.1 TableAdapter DBDirect Methods.
2. Table 7.2 Variations of Tabledapter.Update() method.
3. Table 7.3 The data to be recovered in the Faculty table.
4. Table 7.4 The data to be recovered in the LogIn table.
5. Table 7.5 The data to be recovered in the Course table.
6. Table 7.6 The data to be recovered in the StudentCourse table.
7. Table 7.7 The data to be recovered in the Course table.
8. Table 7.8 The data to be recovered in the StudentCourse table.
9. Table 7.9 The courses to be inserted into the Course table.
10. Table 7.10 The courses to be inserted into the StudentCourse table.
Chapter 8
1. Table 8.1 Controls for the LogIn form.
2. Table 8.2 Validation Controls.
3. Table 8.3 Controls on the Selection form.
4. Table 8.4 Controls on the Faculty form.
5. Table 8.5 Controls on the Course form.
6. Table 8.6 The original data for the faculty member Ying Bai in the Faculty ta...
7. Table 8.7 The total number of rows affected or deleted.
8. Table 8.8 The data to be added into the Faculty table.
9. Table 8.9 The data to be added into the LogIn table.
10. Table 8.10 The data to be added into the Course table.
11. Table 8.11 The data to be added into the StudentCourse table.
Chapter 9
1. Table 9.1 The Web Service Namespaces.
2. Table 9.2 The first course record to be inserted.
3. Table 9.3 The second course record to be inserted.
4. Table 9.4 The input parameters to the stored procedure.
5. Table 9.5 The recovered record for the original student.
6. Table 9.6 The deleted records for student Toney Black in all tables.
7. Table 9.7 Deleted data in LogIn table.
8. Table 9.8 Deleted data in StudentCourse table.
9. Table 9.9 Deleted data in Student table.
10. Table 9.10 The updated record for the student Tom Erica.
11. Table 9.11 Deleted data in Student table.
12. Table 9.12 Deleted data in LogIn table.
13. Table 9.13 Deleted data in StudentCourse table.

List of Illustrations

Chapter 1
1. Figure 1.1 Two study levels in the book.
2. Figure 1.2 Book related materials on Web site.
Chapter 2
1. Figure 2.1 Records and fields in a table.
2. Figure 2.2 One to one relationship in the LogIn and the Student tables.
3. Figure 2.3 One‐to‐many relationship between Faculty and Course tables.
4. Figure 2.4 Many‐to‐Many relationship between Student and Course tables.
5. Figure 2.5 Relationships in CSE_DEPT database.
6. Figure 2.6 Relationships are illustrated using MS Access in the CSE_DEPT dat...
7. Figure 2.7 Commonly used symbols for ER notation.
8. Figure 2.8 Converting Faulty table into 2NF by decomposing the old table in ...
9. Figure 2.9 Microsoft Access database illustration.
10. Figure 2.10 SQL Server database structure.
11. Figure 2.11 Syntax for creating a stored procedure in Oracle.
12. Figure 2.12 Connect to the SQL Server 2017 Express database.
13. Figure 2.13 The opened server management studio.
14. Figure 2.14 The new table window.
15. Figure 2.15 The finished LogIn table.
16. Figure 2.16 The design view of the Faculty table.
17. Figure 2.17 The completed Faculty table.
18. Figure 2.18 The completed Course table.
19. Figure 2.19 The completed Student table.
20. Figure 2.20 The completed StudentCourse table.
21. Figure 2.21 The opened Foreign Key Relationships dialog box.
22. Figure 2.22 The opened Tables and Columns dialog box.
23. Figure 2.23 The finished Tables and Columns dialog box.
24. Figure 2.24 The finished Foreign Key Relationships dialog.
25. Figure 2.25 The completed Tables and Columns dialog.
26. Figure 2.26 The finished Tables and Columns dialog.
27. Figure 2.27 The finished Tables and Columns dialog.
28. Figure 2.28 The finished Tables and Columns dialog.
29. Figure 2.29 Relationships among tables.
30. Figure 2.30 Create a blank solution SQL Image Solution.
31. Figure 2.31 Add a new project SQL Image Project into the solution.
32. Figure 2.32 The new added project SQL Image Project.
33. Figure 2.33 The database connection page.
34. Figure 2.34 The opened Add Connection wizard.
35. Figure 2.35 The connected database and dataset CSE_DEPTDataSet.
36. Figure 2.36 Drag and place three columns in Details format on Faculty table....
37. Figure 2.37 Drag and place three columns in GridView format on Faculty table...
38. Figure 2.38 The completed Form for adding faculty images.
39. Figure 2.39 The modified fimage column.
40. Figure 2.40 The finished Student table in the Data Source Configuration Wiza...
41. Figure 2.41 The completed Form for adding student images.
Chapter 3
1. Figure 3.1 ADO.NET LINQ Techniques.
2. Figure 3.2 A typical architecture of the ADO.NET.
3. Figure 3.3 An alternative architecture of the ADO.NET.
4. Figure 3.4 An example code of the opening a connection.
5. Figure 3.5 An example code for the cleanup of resources.
6. Figure 3.6 Four constructors of the Parameter class.
7. Figure 3.7 An example of initializing the property of a Parameter object.
8. Figure 3.8 Two methods to add Parameter objects.
9. Figure 3.9 Three popular protocols of the constructor of the Command class....
10. Figure 3.10 An example of creating a SqlCommand object.
11. Figure 3.11 An example of code running of ExecuteReader method.
12. Figure 3.12 A sample of code using the ExecuteScalar method.
13. Figure 3.13 An example of code using the ExecuteNonQuery method.
14. Figure 3.14 An example of using the SqlDataAdapter to fill the DataSet.
15. Figure 3.15 An example code of using the SqlDataReader object.
16. Figure 3.16 A global representation of the DataSet and other data objects.
17. Figure 3.17 An example of using the DataSet.
18. Figure 3.18 An example of adding a DataTable into a DataSet.
19. Figure 3.19 An example of creating a new table and adding data into the tabl...
20. Figure 3.20 The mapping relationship between three layers.
21. Figure 3.21 Entity Framework architecture.
22. Figure 3.22 An example of the ADO.NET 4.3 Entity Data Model Designer.
23. Figure 3.23 The new created project EDModel Project.
24. Figure 3.24 The EDModelForm window.
25. Figure 3.25 Displayed all available Entity Framework tools.
26. Figure 3.26 The finished Add New Item Wizard.
27. Figure 3.27 The Connection properties wizard.
28. Figure 3.28 The Designer view of the Entity Data Model EDModel.
29. Figure 3.29 An example of Mapping Details – Faculty entity.
30. Figure 3.30 The codes for the cmdShow_Click event procedure.
31. Figure 3.31 The running result of the project EDModel.
Chapter 4
1. Figure 4.1 A coding example of using LINQ to Object query.
2. Figure 4.2 A modification of the coding example of using LINQ to Object quer...
3. Figure 4.3 A coding example of using LINQ to SQL query.
4. Figure 4.4 An example code for the operator AsEnumerable.
5. Figure 4.5 An example code for the operator Cast.
6. Figure 4.6 An example code for the operator Join.
7. Figure 4.7 An example code for the operator OfType.
8. Figure 4.8 An example code for the operator OrderBy.
9. Figure 4.9 An example code for the operator Select.
10. Figure 4.10 An example code for the operator Where.
11. Figure 4.11 An example code for the operator ElementAt.
12. Figure 4.12 An example code for the operator First.
13. Figure 4.13 An example of code for the operator Last.
14. Figure 4.14 An example of code for the operator Single.
15. Figure 4.15 An example of code for the operator ToArray.
16. Figure 4.16 An example of code for the operator ToList.
17. Figure 4.17 An example code for the LINQ query.
18. Figure 4.18 The running process of a LINQ query.
19. Figure 4.19 A typical query expression of LINQ query.
20. Figure 4.20 A typical LINQ architecture.
21. Figure 4.21 The relationship between LINQ to DataSet and ADO.NET 2.0.
22. Figure 4.22 Relationship between LINQ to Entities, the Entity Framework and ...
23. Figure 4.23 The codes for the example project NonGenericLINQ.
24. Figure 4.24 The codes for the example project QueryStringLINQ.
25. Figure 4.25 The content of the sample text file scores.csv.
26. Figure 4.26 The codes for the example project SortLinesLINQ.
27. Figure 4.27 The running result of the project SortLinesLINQ.
28. Figure 4.28 The codes for the example project QueryContentsLINQ.
29. Figure 4.29 The codes for the example project QueryReflectionLINQ.
30. Figure 4.30 The example codes for the query expression syntax.
31. Figure 4.31 The example codes for the query expression in implicit typing of...
32. Figure 4.32 The codes for the example project QueryMethodSyntax.
33. Figure 4.33 The codes for the example project DataSetSingleTableLINQ.
34. Figure 4.34 The codes for the example project DataSetCrossTableLINQ.
35. Figure 4.35 The opened Add New Item dialog box.
36. Figure 4.36 The Add Connection dialog and the Server Explorer window.
37. Figure 4.37 The codes for the example project TypedDataSetLINQ.
38. Figure 4.38 The codes for the example project DataRowFieldLINQ.
39. Figure 4.39 The codes for the example project DataRowSetFieldLINQ.
40. Figure 4.40 Select the LINQ to SQL Tools.
41. Figure 4.41 The Add New item dialog.
42. Figure 4.42 The opened Object Relational Designer.
43. Figure 4.43 The finished Designer.
44. Figure 4.44 The codes for the console Main method.
45. Figure 4.45 The codes for the LINQSelect subroutine.
46. Figure 4.46 The codes for the LINQInsert() subroutine procedure.
47. Figure 4.47 The codes for the LINQUpdate() subroutine procedure.
48. Figure 4.48 The codes for the LINQDelete() subroutine procedure.
49. Figure 4.49 An example of expression used in LINQ to Entities.
50. Figure 4.50 An example of executing the query.
51. Figure 4.51 A simplified structure of LINQ to Entities.
52. Figure 4.52 The LINQ to XML class hierarchy.
53. Figure 4.53 A sample XML file created using LINQ to XML.
54. Figure 4.54 A sample functional construction.
55. Figure 4.55 A sample query using LINQ to XML.
56. Figure 4.56 Some sample codes of using LINQ to XML to insert XML.
57. Figure 4.57 Some sample codes of using LINQ to XML to update XML.
58. Figure 4.58 Some sample codes of using LINQ to XML to delete XML.
59. Figure 4.59 A sample XML attributes.
60. Figure 4.60 A sample code to create an XAttribut.
61. Figure 4.61 A sample code to get and delete an XAttribut.
62. Figure 4.62 A sample code to create an XElement.
63. Figure 4.63 A sample code to perform the query to an XElement.
64. Figure 4.64 A sample code to perform the query using query expressions with ...
65. Figure 4.65 A sample code to perform the query using mixing XML.
66. Figure 4.66 A sample XML.
67. Figure 4.67 A piece of sample codes to read and update database.
68. Figure 4.68 A piece of sample codes for lambda expressions.
69. Figure 4.69 An example of defining class and instance method.
70. Figure 4.70 An example of calling class and instance method.
71. Figure 4.71 The running result of the execution of the extension method.
72. Figure 4.72 Declare the class method ToUpper() to extension method.
73. Figure 4.73 Declare an anonymous type variable.
74. Figure 4.74 Declare an anonymous type variable using implicitly typed local ...
75. Figure 4.75 An example of using the object initializer.
76. Figure 4.76 A typical syntax of query expression.
77. Figure 4.77 A real example of query expression.
78. Figure 4.78 A LINQ to Object query.
Chapter 5
1. Figure 5.1 The LogIn form.
2. Figure 5.2 The Selection form.
3. Figure 5.3 The Faculty form.
4. Figure 5.4 The Course form.
5. Figure 5.5 The Data components in Toolbox window.
6. Figure 5.6 The relationship between data components.
7. Figure 5.7 The Data Sources window.
8. Figure 5.8 The Data Source Configuration Wizard.
9. Figure 5.9 Choose a Database Model in the Data Source Configuration Wizard....
10. Figure 5.10 The Change Data Source wizard.
11. Figure 5.11 The Add Connection and Change Data Source wizards.
12. Figure 5.12 Choose Your Database Objects wizard.
13. Figure 5.13 The added data source.
14. Figure 5.14 A sample DataSet Designer.
15. Figure 5.15 The opened Preview Data wizard.
16. Figure 5.16 An example of the Preview Data for Faculty table.
17. Figure 5.17 The finished project window.
18. Figure 5.18 The completed Selection form.
19. Figure 5.19 The finished Faculty form.
20. Figure 5.20 The completed Course form.
21. Figure 5.21 The completed Student form.
22. Figure 5.22 The DataGridView tool.
23. Figure 5.23 The entire table view for the Faculty table.
24. Figure 5.24 The Detail view for specified columns.
25. Figure 5.25 The running status of the Detail view for each record.
26. Figure 5.26 The Query Build window.
27. Figure 5.27 The Choose Methods to Generate wizard.
28. Figure 5.28 The result of the TableAdapter Query Configuration Wizard.
29. Figure 5.29 The DataBindings property.
30. Figure 5.30 The created BindingSource object LogInBindingSource.
31. Figure 5.31 The codes of the LogIn button event procedure.
32. Figure 5.32 The running status of the LogIn Form.
33. Figure 5.33 The warning message.
34. Figure 5.34 The finished Query Builder.
35. Figure 5.35 The codes for the cmdPW button event procedure.
36. Figure 5.36 The running status of the LogIn form.
37. Figure 5.37 The Selection Form.
38. Figure 5.38 The coddes for the Selection Form_Load event procedure.
39. Figure 5.39 The codes for the OK button event procedure.
40. Figure 5.40 The codes for the Exit button click event procedure.
41. Figure 5.41 The expansion for Faculty table data binding.
42. Figure 5.42 An example of the Query Builder.
43. Figure 5.43 The codes for the FacultyForm_Load event procedure.
44. Figure 5.44 The codes for the Select button event procedure.
45. Figure 5.45 The codes for the Back button.
46. Figure 5.46 The codes for the LINQ to DataSet subroutine.
47. Figure 5.47 The running status of the Faculty form window.
48. Figure 5.48 The finished query for the faculty_id.
49. Figure 5.49 The Query Builder.
50. Figure 5.50 The expansion of the data source.
51. Figure 5.51 The codes for the CourseForm_Load event procedure.
52. Figure 5.52 The codes for the Select button click event procedure.
53. Figure 5.53 The codes for the LINQ to DataSet subroutine.
54. Figure 5.54 The running status of the Course form.
55. Figure 5.55 The relationships between Course ListBox, Course table and TextB...
56. Figure 5.56 Classes provided by ADO.NET.
57. Figure 5.57 The declaration of the namespace for the SQL Server Data Provide...
58. Figure 5.58 The modified LogIn form window.
59. Figure 5.59 The codes for the database connection.
60. Figure 5.60 The codes for the TabLogIn button event procedure.
61. Figure 5.61 The codes for the ReadLogIn button event procedure.
62. Figure 5.62 The codes for the Cancel button event procedure.
63. Figure 5.63 The codes for the SelectionForm_Load event procedure.
64. Figure 5.64 The codes for the OK Click button’s event procedure.
65. Figure 5.65 The codes for the Exit Click button’s event procedure.
66. Figure 5.66 The codes for the FacultyForm_Load() event procedure.
67. Figure 5.67 The modified codes for the Select button event procedure.
68. Figure 5.68 The codes for the user defined subroutine procedure FillFacultyT...
69. Figure 5.69 The codes for the user defined subroutine procedure MapFacultyTa...
70. Figure 5.70 The codes for the subroutine procedure FillFacultyReader.
71. Figure 5.71 The codes for the subroutine procedure ShowFaculty().
72. Figure 5.72 Three methods to retrieve images from SQL Server database.
73. Figure 5.73 The modified codes for the subroutine FillFacultyTable().
74. Figure 5.74 The modified codes for the subroutine FillFacultyReader().
75. Figure 5.75 The codes for the CourseForm_Load() event procedure.
76. Figure 5.76 The modified codes for the Select button event procedure.
77. Figure 5.77 The codes for two user defined subroutine procedures.
78. Figure 5.78 The codes for the CourseList_SelectedIndexChanged procedure.
79. Figure 5.79 The codes for two user defined subroutine procedures.
80. Figure 5.80 The codes for the subroutine MapCourseTable.
81. Figure 5.81 The codes for the Back button Click event procedure.
82. Figure 5.82 The Student Form window.
83. Figure 5.83 The codes for the Form_Load event procedure.
84. Figure 5.84 The opened SQL Server Connect wizard.
85. Figure 5.85 The prototype of a SQL Server stored procedure.
86. Figure 5.86 A sample SQL Server stored procedure.
87. Figure 5.87 An example of calling the stored procedure.
88. Figure 5.88 The first stored procedure – dbo.StudentInfo.
89. Figure 5.89 The second stored procedure – dbo.StudentCourseInfo.
90. Figure 5.90 The popup menu and EXECUTE dialog box.
91. Figure 5.91 The testing result of our first stored procedure.
92. Figure 5.92 The testing result for our second stored procedure.
93. Figure 5.93 The codes for the Select button Click event procedure.
94. Figure 5.94 The codes for the subroutine BuildCommand().
95. Figure 5.95 The codes for the subroutine FillStudent().
96. Figure 5.96 The codes for the subroutine FillCourseList().
97. Figure 5.97 The codes for the subroutine MapStudentTextBox().
98. Figure 5.98 The running status of the Student form.
99. Figure 5.99 The new stored procedure – StudentCourseINTO.
100. Figure 5.100 The running result of the stored procedure.
101. Figure 5.101 The codes for the Select button event procedure.
102. Figure 5.102 The running status of calling stored procedure dbo.StudentCours...
Chapter 6
1. Figure 6.1 The form‐level collection object.
2. Figure 6.2 The codes for the Insert button event procedure.
3. Figure 6.3 The codes for the subroutine CreateFacultyCollection.
4. Figure 6.4 The codes for the subroutine RemoveFacultyCollection.
5. Figure 6.5 The modified codes for the Form_Load event procedure.
6. Figure 6.6 The opened Query Builder wizard.
7. Figure 6.7 The modified codes for the Insert button event procedure.
8. Figure 6.8 The codes for the txtID TextChanged event procedure.
9. Figure 6.9 Detailed codes for the user defined function getFacultyImage().
10. Figure 6.10 The codes for the second data insertion method.
11. Figure 6.11 The codes for the user defined function InsertFacultyRow.
12. Figure 6.12 The running status of inserting a new faculty.
13. Figure 6.13 The confirmation result of the new inserted faculty member.
14. Figure 6.14 The opened Query Builder wizard.
15. Figure 6.15 The confirmation wizard.
16. Figure 6.16 The code modification to the Form_Load event procedure.
17. Figure 6.17 The code developments for the Insert button’s Click event proced...
18. Figure 6.18 The codes for the subroutine CleanInsert().
19. Figure 6.19 The running result of calling the stored procedure.
20. Figure 6.20 The codes for the user defined subroutine InitFacultyInfo().
21. Figure 6.21 The codes for the function CheckFacultyInfo().
22. Figure 6.22 The first piece of codes for the Insert button’s Click event pro...
23. Figure 6.23 The second piece of codes for the Insert button’s event procedur...
24. Figure 6.24 The codes for the user‐defined subroutine InsertParameters().
25. Figure 6.25 The codes for the user‐defined subroutine CleanInsert().
26. Figure 6.26 The codes for the user defined function getFacultyImage().
27. Figure 6.27 The new inserted faculty record in the Faculty Form window.
28. Figure 6.28 The new inserted faculty member Mhamed Jones.
29. Figure 6.29 The codes for the stored procedure dbo.InsertFacultyCourse.
30. Figure 6.30 The Run Stored Procedure wizard.
31. Figure 6.31 The running result of the stored procedure.
32. Figure 6.32 The codes for user defined subroutine and function.
33. Figure 6.33 The first coding part for the Insert button’s event procedure.
34. Figure 6.34 The modifications to the Insert button’s Click event procedure....
35. Figure 6.35 The codes for the user defined subroutine InsertParameters().
36. Figure 6.36 The running status of the Course Form window.
37. Figure 6.37 The data validation process.
38. Figure 6.38 The codes to call the SQL Server stored procedure.
Chapter 7
1. Figure 7.1 The Query Builder for the Update query.
2. Figure 7.2 The Query Builder for the Delete query.
3. Figure 7.3 The modified codes for the Form_Load event procedure.
4. Figure 7.4 The codes for the Update command button’s Click event procedure....
5. Figure 7.5 The codes for the second data updating method.
6. Figure 7.6 The codes for the user‐defined function UPFacultyRow().
7. Figure 7.7 The codes for the Delete button’s Click event procedure.
8. Figure 7.8 The codes for the second data deleting method.
9. Figure 7.9 The running status of the Updated Faculty Form window.
10. Figure 7.10 The updated faculty record.
11. Figure 7.11 The relationships among tables.
12. Figure 7.12 The codes for the data updating operation.
13. Figure 7.13 The codes for the data‐deleting query.
14. Figure 7.14 The updated faculty information window.
15. Figure 7.15 Modified codes for the cmdSelect button’s Click event procedure....
16. Figure 7.16 Modified codes for the subroutine ShowFaculty().
17. Figure 7.17 The codes for the Update button’s event procedure.
18. Figure 7.18 The codes for the user defined procedure UpdateParameters().
19. Figure 7.19 The modified codes to call the stored procedure.
20. Figure 7.20 The created stored procedure dbo.UpdateFacultySP.
21. Figure 7.21 The finished information wizard.
22. Figure 7.22 The updated Faculty table.
23. Figure 7.23 The created data deleting stored procedure.
24. Figure 7.24 The recovered course CSE‐438.
25. Figure 7.25 Initialization codes for the Form_Load event procedure of the St...
26. Figure 7.26 The codes for the Select button Click event procedure.
27. Figure 7.27 The codes for the Insert button Click event procedure.
28. Figure 7.28 The retrieved new inserted student record.
29. Figure 7.29 Three new courses are added into the CourseList box.
30. Figure 7.30 The codes for the Update button Click event procedure.
31. Figure 7.31 The codes for the Delete button Click event procedure.
Chapter 8
1. Figure 8.1 The .NET Framework model.
2. Figure 8.2 The structure of an ASP.NET Web application.
3. Figure 8.3 ASP.NET Web application file structure.
4. Figure 8.4 The opened Template wizard.
5. Figure 8.5 The finished LogIn Web form.
6. Figure 8.6 The codes for the Page_Load event procedure.
7. Figure 8.7 The codes for the LogIn button’s Click event procedure.
8. Figure 8.8 The codes for the Cancel button’s event procedure.
9. Figure 8.9 Adding the data validation –RequiredFieldValidator.
10. Figure 8.10 The finished Selection page.
11. Figure 8.11 The codes for the Page_Load event procedure of the Selection pag...
12. Figure 8.12 The codes for the Select button’s event procedure.
13. Figure 8.13 The codes for the Exit button’s Click event procedure.
14. Figure 8.14 The running status of the second page – Selection page.
15. Figure 8.15 The finished Faculty page.
16. Figure 8.16 The codes for the Page_Load event procedure.
17. Figure 8.17 The codes for the Select button’s Click event procedure.
18. Figure 8.18 The codes for the subroutine FillFacultyReader.
19. Figure 8.19 The codes for the subroutine MapFacultyTable.
20. Figure 8.20 The codes for the Back button’s Click event procedure.
21. Figure 8.21 The running status of the Faculty page.
22. Figure 8.22 The finished Course Web page.
23. Figure 8.23 The codes for the Page_Load event procedure.
24. Figure 8.24 The codes for the Select button’s Click event procedure.
25. Figure 8.25 The codes for the subroutine FillCourseReader().
26. Figure 8.26 The codes for the SelectedIndexChanged event procedure.
27. Figure 8.27 The codes for the subroutine FillCourseReaderTextBox().
28. Figure 8.28 The codes for the subroutine MapCourseTable().
29. Figure 8.29 The running status of the Course page.
30. Figure 8.30 The detailed course information.
31. Figure 8.31 The codes for the Insert button’s Click event procedure.
32. Figure 8.32 The codes for the subroutine InsertParameters().
33. Figure 8.33 The codes for the subroutine GetFacultyImage().
34. Figure 8.34 The codes for the subroutine CleanInsert().
35. Figure 8.35 The completed codes for the Insert button’s Click event procedur...
36. Figure 8.36 The codes for the function CheckFaculty().
37. Figure 8.37 The running status of the Faculty page.
38. Figure 8.38 The data validation process.
39. Figure 8.39 The modified codes for the Select button’s event procedure.
40. Figure 8.40 The codes for the Update button’s click event procedure.
41. Figure 8.41 The codes for the subroutine UpdateParameters().
42. Figure 8.42 The data updating process.
43. Figure 8.43 The relationships between five tables.
44. Figure 8.44 The Foreign Key Relationship wizard.
45. Figure 8.45 The stored procedure dbo.DeleteFaculty.
46. Figure 8.46 The finished Run Stored Procedure wizard.
47. Figure 8.47 The running result of the stored procedure.
48. Figure 8.48 The codes for the Delete button’s Click event procedure.
49. Figure 8.49 The codes for the subroutine CleanFaculty().
50. Figure 8.50 Initialization codes for the Faculty Web page.
51. Figure 8.51 The codes for the Exit button Click event procedure.
52. Figure 8.52 The codes for the Select button Click event procedure.
53. Figure 8.53 The codes for the Insert button Click event procedure.
54. Figure 8.54 The codes for the function CheckFaculty().
55. Figure 8.55 The codes for the subroutine GetFacultyImage().
56. Figure 8.56 The codes for the subroutine MapFacultyTable.
57. Figure 8.57 The codes for the Update button Click event procedure.
58. Figure 8.58 The codes for the Delete button Click event procedure.
59. Figure 8.59 The testing status of the data insertion action.
60. Figure 8.60 The codes for the Page_Load() event procedure.
Chapter 9
1. Figure 9.1 A typical process of a Web service.
2. Figure 9.2 The Service interface and contract.
3. Figure 9.3 The codes in the client side to call the operation GetData() in t...
4. Figure 9.4 Create a new WCF Web Service project.
5. Figure 9.5 New created items for a WCF Web service project.
6. Figure 9.6 The modified Web service project.
7. Figure 9.7 The default codes for the code‐behind page WebServiceSQLSelect.vb...
8. Figure 9.8 The running status of the default Web service project.
9. Figure 9.9 The test method page.
10. Figure 9.10 The running status of the default method.
11. Figure 9.11 The modified main service page.
12. Figure 9.12 The running status of the modified Web service project.
13. Figure 9.13 The class member data.
14. Figure 9.14 The member data for the class SQLSelectResult.
15. Figure 9.15 The modified codes to test the connection string.
16. Figure 9.16 The modified Web method – GetSQLSelect().
17. Figure 9.17 The modified codes for the Web method.
18. Figure 9.18 The codes for the subroutine FillFacultyReader().
19. Figure 9.19 The codes for the subroutine ReportError().
20. Figure 9.20 The running status of the Web service.
21. Figure 9.21 The running status of our Web method.
22. Figure 9.22 The running result of our Web service project.
23. Figure 9.23 The stored procedure dbo.WebSelectFacultySP.
24. Figure 9.24 The running result of the stored procedure.
25. Figure 9.25 The modified Web method – GetSQLSelectSP().
26. Figure 9.26 The modified Web method – GetSQLSelectDataSet().
27. Figure 9.27 Three Web methods in built‐in Web interface window.
28. Figure 9.28 The running result of the Web method GetSQLSelectDataSet().
29. Figure 9.29 The Add Web Reference wizard.
30. Figure 9.30 The codes for the Form_Load event procedure.
31. Figure 9.31 The codes for the Select button click event procedure.
32. Figure 9.32 The codes for two user defined subroutines.
33. Figure 9.33 The codes for the subroutine FillFacultyDataSet().
34. Figure 9.34 The running status of our Web service project WebServiceSQLSelec...
35. Figure 9.35 The running status of our client project.
36. Figure 9.36 Add a Web reference.
37. Figure 9.37 The modified Faculty.aspx page.
38. Figure 9.38 The modified Page_Load event procedure.
39. Figure 9.39 The modified codes for the Select button’s click event procedure...
40. Figure 9.40 The modified codes for the subroutine ProcessObject().
41. Figure 9.41 The modified codes for the subroutine FillFacultyObject().
42. Figure 9.42 The modified codes for the subroutine FillFacultyDataSet().
43. Figure 9.43 The modified codes for the Back button event procedure.
44. Figure 9.44 The running status of our Web‐based client project.
45. Figure 9.45 The completed Add Website wizard.
46. Figure 9.46 The Customer Profile setup wizard.
47. Figure 9.47 The Web site Publishing result.
48. Figure 9.48 Modifications to the first Web method.
49. Figure 9.49 Detailed codes for the user defined function and subroutine.
50. Figure 9.50 The running status of the built‐in Web interface.
51. Figure 9.51 The input parameter interface.
52. Figure 9.52 The running result of the first Web method.
53. Figure 9.53 The codes for our second Web method GetSQLInsert().
54. Figure 9.54 The codes for the subroutine FillCourseReader().
55. Figure 9.55 The running status of our Web service project.
56. Figure 9.56 The input parameter wizard for the Web method GetSQLInsert().
57. Figure 9.57 The running result of our Web method GetSQLInsert().
58. Figure 9.58 The codes for the Web method SQLInsertDataSet().
59. Figure 9.59 The finished input parameter wizard.
60. Figure 9.60 The running result of the third Web method.
61. Figure 9.61 The codes for the stored procedure WebSelectCourseSP().
62. Figure 9.62 The running result of the stored procedure WebSelectCourseSP()....
63. Figure 9.63 The codes for the Web method GetSQLInsertCourse().
64. Figure 9.64 The codes for the subroutine FillCourseDetail().
65. Figure 9.65 The running result of our Web method GetSQLInsertCourse().
66. Figure 9.66 The finished Add Web Reference wizard.
67. Figure 9.67 The finished graphic user interface.
68. Figure 9.68 The codes of the Form_Load and Back button event procedures.
69. Figure 9.69 The codes for the Insert button Click event procedure.
70. Figure 9.70 The codes for the user defined function CheckCourse().
71. Figure 9.71 The codes for the Select button Click event procedure.
72. Figure 9.72 The codes for the subroutines ProcessObject() and FillCourseList...
73. Figure 9.73 The codes for the subroutine FillCourseDataSet().
74. Figure 9.74 The running result of the data validation.
75. Figure 9.75 The codes for the SelectedIndexChanged event procedure.
76. Figure 9.76 The running status of getting the detailed course information.
77. Figure 9.77 The finished Add Web Reference wizard.
78. Figure 9.78 The modified Course page window.
79. Figure 9.79 The modified Page_Load event procedure.
80. Figure 9.80 The codes for the Insert button event procedure.
81. Figure 9.81 The detailed codes for the user defined function CheckCourse()....
82. Figure 9.82 The codes for the Select button event procedure.
83. Figure 9.83 The codes for subroutines ProcessObject() and FillCourseListBox(...
84. Figure 9.84 The codes for the subroutine FillCourseDataSet().
85. Figure 9.85 The modified codes for the SelectedIndexChanged event procedure....
86. Figure 9.86 The codes for the subroutine FillCourseDetail().
87. Figure 9.87 The running status of inserting new course records.
88. Figure 9.88 The running status of the data validation process.
89. Figure 9.89 The running status of getting the detailed course information.
90. Figure 9.90 The modified namespace.
91. Figure 9.91 The member data for the class SQLResult.
92. Figure 9.92 The codes for the Web method SQLUpdateSP().
93. Figure 9.93 The codes for the Web method SQLDeleteSP().
94. Figure 9.94 Detailed codes for the stored procedure WebUpdateStudentSP().
95. Figure 9.95 The input parameters to stored procedure WebUpdateStudentSP().
96. Figure 9.96 The running result of the stored procedure WebUpdateStudentSP()....
97. Figure 9.97 The codes for the stored procedure WebDeleteCourseSP().
98. Figure 9.98 The Execute Stored Procedure wizard.
99. Figure 9.99 The modified Student Form window.
100. Figure 9.100 The opened Add Web Reference wizard.
101. Figure 9.101 Detailed codes for the Update button’s click event procedure.
102. Figure 9.102 The codes for the Delete button’s click event procedure.
103. Figure 9.103 The running status of the student updating process.
104. Figure 9.104 The finished Add Web Reference wizard.
105. Figure 9.105 The codes for the Update button click event procedure.
106. Figure 9.106 The codes for the Delete button click event procedure.
107. Figure 9.107 Running result of updating a student record.
Appendix A
1. Figure A.1 The opened download page.
2. Figure A.2 The Installation Option wizard.
3. Figure A.3 The SQL Server media download target location page.
4. Figure A.4 The SQL Server Installation Center wizard.
5. Figure A.5 The Setup wizard.
6. Figure A.6 The intermediate checking results page.
7. Figure A.7 The Database Engine Configuration page.
8. Figure A.8 The installation process.
9. Figure A.9 The installation is completed.
10. Figure A.10 Opened Web page to install SSMS 18.0.
11. Figure A.11 The installation processing page.
12. Figure A.12 Check the port number used by the SQL Server.
13. Figure A.13 The opened SQL Server Configuration Manager wizard.
14. Figure A.14 The opened New Inbound Rule Wizard.
15. Figure A.15 The opened Protocol and Ports wizard.
16. Figure A.16 The opened Name wizard.
17. Figure A.17 The completed Inbound Rules addition.
18. Figure A.18 The opened System wizard on the Control Panel.
19. Figure A.19 The started SQL Server 2017 Express.
20. Figure A.20 The connected SQL Server 2017 Express database.
Appendix B
1. Figure B.1 The opened site for WindowsUI component.
2. Figure B.2 Select WinForms Controls to install the WindowsUI component.
3. Figure B.3 The downloading and installation process starts.
Appendix C
1. Figure C.1 FrontPage Server Extensions installation.
2. Figure C.2 FrontPage Server Extensions installation additional features.
3. Figure C.3 FrontPage Server Extensions install button.
4. Figure C.4 FrontPage Server Extensions installation in progress.
5. Figure C.5 FrontPage Server Extensions installation complete.

Jón Atli Benediktsson	David Alan Grier	Elya B. Joffe
Xiaoou Li	Peter Lian	Andreas Molisch
Saeid Nahavandi	Jeffrey Reed	Diomidis Spinellis
Sarah Spurgeon	Ahmet Murat Tekalp

SQL Server Database Programming with Visual Basic.NET

Concepts, Designs and Implementations

About the Author