SQL Server Database Programming with Visual Basic.Net: Concepts, Designs and Implementations

ISBN-13: 9781119608509 / Angielski / Miękka / 2020 / 688 str.

Ying Bai

Spis treści

About the Author xixPreface xxiAcknowledgment xxiiiAbout the Companion Website xxivChapter 1 Introduction 11.1 Outstanding Features About This Book 21.2 This Book is For 21.3 What This Book Covers 21.4 How This Book is Organized and How to Use This Book 51.5 How to Use Source Codes and Sample Database 61.6 Instructors and Customers Supports 8Chapter 2 Introduction to Databases 9Ying Bai and Satish Bhalla2.1 What are Databases and Database Programs? 102.1.1 File Processing System 102.1.2 Integrated Databases 112.2 Develop a Database 122.3 Sample Database 132.3.1 Relational Data Model 132.3.2 Entity-Relationship Model (ER) 172.4 Identifying Keys 182.5 Define Relationships 182.6 ER Notation 222.7 Data Normalization 232.7.1 First Normal Form (1NF) 232.7.2 Second Normal Form (2NF) 242.7.3 Third Normal Form (3NF) 262.8 Database Components in Some Popular Databases 282.8.1 Microsoft Access Databases 282.8.2 SQL Server Databases 292.8.3 Oracle Databases 322.9 Create Microsoft SQL Server 2017 Express Sample Database 352.9.1 Create the LogIn Table 362.9.2 Create the Faculty Table 372.9.3 Create Other Tables 392.9.4 Create Relationships Among Tables 452.9.4.1 Create Relationship Between the LogIn and the Faculty Tables 462.9.4.2 Create Relationship Between the LogIn and the Student Tables 492.9.4.3 Create Relationship Between the Faculty and the Course Tables 502.9.4.4 Create Relationship Between the Student and the StudentCourse Tables 502.9.4.5 Create Relationship Between the Course and the StudentCourse Tables 512.9.5 Store Images to the SQL Server 2017 Express Database 532.10 Chapter Summary 61Homework 63Chapter 3 Introduction to ADO.NET 673.1 The ADO and ADO.NET 673.2 Overview of the ADO.NET 693.3 The Architecture of the ADO.NET 703.4 The Components of ADO.NET 713.4.1 The Data Provider 723.4.1.1 The ODBC Data Provider 733.4.1.2 The OLEDB Data Provider 733.4.1.3 The SQL Server Data Provider 743.4.1.4 The Oracle Data Provider 743.4.2 The Connection Class 743.4.2.1 The Open() Method of the Connection Class 773.4.2.2 The Close() Method of the Connection Class 773.4.2.3 The Dispose() Method of the Connection Class 783.4.3 The Command and the Parameter Classes 783.4.3.1 The Properties of the Command Class 793.4.3.2 The Constructors and Properties of the Parameter Class 793.4.3.3 Parameter Mapping 803.4.3.4 The Methods of the ParameterCollection Class 823.4.3.5 The Constructor of the Command Class 833.4.3.6 The Methods of the Command Class 843.4.4 The DataAdapter Class 873.4.4.1 The Constructor of the DataAdapter Class 873.4.4.2 The Properties of the DataAdapter Class 873.4.4.3 The Methods of the DataAdapter Class 883.4.4.4 The Events of the DataAdapter Class 883.4.5 The DataReader Class 903.4.6 The DataSet Component 923.4.6.1 The DataSet Constructor 943.4.6.2 The DataSet Properties 943.4.6.3 The DataSet Methods 943.4.6.4 The DataSet Events 943.4.7 The DataTable Component 973.4.7.1 The DataTable Constructor 983.4.7.2 The DataTable Properties 983.4.7.3 The DataTable Methods 993.4.7.4 The DataTable Events 1003.4.8 ADO.NET Entity Framework 1023.4.8.1 Advantages of Using the Entity Framework 6 1043.4.8.2 The ADO.NET 4.3 Entity Data Model 1063.4.8.3 Using Entity Framework 6 Entity Data Model Wizard 1103.5 Chapter Summary 118Homework 120Chapter 4 Introduction to Language Integrated Query (LINQ) 1234.1 Overview of Language Integrated Query 1234.1.1 Some Special Interfaces Used in LINQ 1244.1.1.1 The IEnumerable and IEnumerable(Of T) Interfaces 1244.1.1.2 The IQueryable and IQueryable(Of T) Interfaces 1254.1.2 Standard Query Operators 1264.1.3 Deferred Standard Query Operators 1274.1.4 Non-Deferred Standard Query Operators 1314.2 Introduction to LINQ Query 1354.3 The Architecture and Components of LINQ 1374.3.1 Overview of LINQ to Objects 1384.3.2 Overview of LINQ to DataSet 1394.3.3 Overview of LINQ to SQL 1394.3.4 Overview of LINQ to Entities 1404.3.5 Overview of LINQ to XML 1404.4 LINQ to Objects 1414.4.1 LINQ and ArrayList 1424.4.2 LINQ and Strings 1434.4.2.1 Query a String to Determine the Number of Numeric Digits 1444.4.2.2 Sort Lines of Structured Text By any Field in the Line 1454.4.3 LINQ and File Directories 1474.4.3.1 Query the Contents of Files in a Folder 1484.4.4 LINQ and Reflection 1504.5 LINQ to DataSet 1524.5.1 Operations to DataSet Objects 1524.5.1.1 Query Expression Syntax 1534.5.1.2 Method-Based Query Syntax 1544.5.1.3 Query the Single Table 1574.5.1.4 Query the Cross Tables 1594.5.1.5 Query Typed DataSet 1624.5.2 Operations to DataRow Objects Using the Extension Methods 1654.5.3 Operations to DataTable Objects 1694.6 LINQ to SQL 1704.6.1 LINQ to SQL Entity Classes and DataContext Class 1714.6.1.1 Add LINQ to Data Reference 1714.6.1.2 Add LINQ To SQL Tools 1714.6.2 LINQ to SQL Database Operations 1754.6.2.1 Data Selection Query 1754.6.2.2 Data Insertion Query 1774.6.2.3 Data Updating Query 1784.6.2.4 Data Deletion Query 1794.6.3 LINQ to SQL Implementations 1824.7 LINQ to Entities 1824.7.1 The Object Services Component 1834.7.2 The ObjectContext Component 1834.7.3 The ObjectQuery Component 1844.7.4 LINQ to Entities Flow of Execution 1844.7.5 Implementation of LINQ to Entities 1864.8 LINQ to XML 1874.8.1 LINQ to XML Class Hierarchy 1874.8.2 Manipulate XML Elements 1884.8.2.1 Creating XML from Scratch 1884.8.2.2 Insert XML 1904.8.2.3 Update XML 1914.8.2.4 Delete XML 1924.8.3 Manipulate XML Attributes 1924.8.3.1 Add XML Attributes 1924.8.3.2 Get XML Attributes 1934.8.3.3 Delete XML Attributes 1934.8.4 Query XML with LINQ to XML 1944.8.4.1 Standard Query Operators and XML 1944.8.4.2 XML Query Extensions 1954.8.4.3 Using Query Expressions with XML 1964.8.4.4 Using XPath and XSLT with LINQ to XML 1964.8.4.5 Mixing XML and Other Data Models 1974.9 Visual Basic.NET Language Enhancement for LINQ 1994.9.1 Lambda Expressions 1994.9.2 Extension Methods 2014.9.3 Implicitly Typed Local Variables 2054.9.4 Query Expressions 2064.10 Chapter Summary 208Homework 209Chapter 5 Data Selection Query with Visual Basic.NET 215Part I Data Query with Visual Studio.NET Design Tools and Wizards 2165.1 A Completed Sample Database Application Example 2165.2 Visual Studio.NET Design Tools and Wizards 2195.2.1 Data Components in the Toolbox Window 2205.2.1.1 The DataSet 2205.2.1.2 DataGridView 2215.2.1.3 BindingSource 2225.2.1.4 BindingNavigator 2225.2.1.5 TableAdapter 2235.2.1.6 TableAdapter Manager 2235.2.2 Data Source Window 2235.2.2.1 Add New Data Sources 2245.2.2.2 Data Source Configuration Wizard 2245.2.2.3 DataSet Designer 2285.3 Query Data from SQL Server Database Using Design Tools and Wizards 2315.3.1 Application User Interface 2315.3.1.1 The LogIn Form 2325.3.1.2 The Selection Form 2325.3.1.3 The Faculty Form 2325.3.1.4 The Course Form 2345.3.1.5 The Student Form 2345.4 Use Visual Studio Wizards and Design Tools to Query and Display Data 2365.4.1 Query and Display Data using the DataGridView and Detail Controls 2365.4.1.1 View the Entire Table 2385.4.1.2 View Each Record or the Specified Columns with Detail View 2415.4.2 Use DataSet Designer to Edit the Structure of the DataSet 2435.4.3 Bind Data to the Associated Controls in LogIn Form 2455.4.4 Develop Codes to Query Data Using the Fill() Method 2495.4.5 Use Return a Single Value to Query Data for LogIn Form 2515.4.6 Develop the Codes for the Selection Form 2545.4.7 Query Data from the Faculty Table for the Faculty Form 2565.4.8 Develop Codes to Query Data from the Faculty Table 2585.4.8.1 Develop Codes to Query Data Using the TableAdapter Method 2585.4.8.2 Develop Codes to Query Data Using the LINQ to DataSet Method 2615.4.9 Query Data from the Course Table for the Course Form 2625.4.9.1 Build the Course Queries Using the Query Builder 2635.4.9.2 Bind Data Columns to the Associated Controls in the Course Form 2655.4.9.3 Develop Codes to Query Data for the Course Form 267Part II Data Query with Runtime Objects 2715.5 Introduction to Runtime Objects 2725.5.1 Procedure of Building a Data-Driven Application Using Runtime Object 2745.6 Query Data from SQL Server Database Using Runtime Object 2745.6.1 Access to SQL Server Database 2745.6.2 Declare Global Variables and Runtime Objects 2765.6.3 Query Data Using Runtime Objects for the LogIn Form 2785.6.3.1 Connect to the Data Source with the Runtime Object 2785.6.3.2 Coding for Method 1: Using the TableAdapter to Query Data 2795.6.3.3 Coding for Method 2: Using the DataReader to Query Data 2815.6.4 The Coding for the Selection Form 2835.6.5 Query Data Using Runtime Objects for the Faculty Form 2845.6.5.1 Using Three Query Methods to Retrieve Images from SQL Server Database 2905.6.6 Query Data Using Runtime Objects for the Course Form 2905.6.6.1 Retrieve Data from Multiple Tables Using Tables JOINS 2935.6.7 Query Data Using Runtime Objects for the Student Form 3015.6.7.1 Query Student Data Using Stored Procedures 3025.6.7.2 Query Data Using Stored Procedures for Student Form 3065.6.7.3 Query Data Using More Complicated Stored Procedures 3155.7 Chapter Summary 320Homework 321Chapter 6 Data Inserting with Visual Basic.NET 327Part I Insert Data with Visual Basic.NET Design Tools and Wizards 3286.1 Insert Data Into a Database 3286.1.1 Insert New Records into a Database Using the TableAdapter.Insert Method 3296.1.2 Insert New Records into a Database Using the TableAdapter.Update Method 3296.2 Insert Data into the SQL Server Database Using a Sample Project InsertWizard 3306.2.1 Create InsertWizard Project Based on the SelectWizard Project 3306.2.2 Application User Interfaces 3316.2.3 Validate Data Before the Data Insertion 3316.2.3.1 Visual Basic Collection and .NET Framework Collection Classes 3316.2.3.2 Validate Data Using the Generic Collection 3326.2.4 Initialization Coding Process for the Data Insertion 3356.2.5 Build the Insert Query 3366.2.5.1 Configure the TableAdapter and Build the Data Inserting Query 3366.2.6 Develop Codes to Insert Data Using the TableAdapter.Insert Method 3376.2.7 Develop Codes to Insert Data Using the TableAdapter.Update Method 3416.2.8 Insert Data into the Database Using the Stored Procedures 3456.2.8.1 Create the Stored Procedure Using the TableAdapter Query Configuration Wizard 3466.2.8.2 Modify the Codes to Perform the Data Insertion Using the Stored Procedure 346Part II Data Insertion with Runtime Objects 3506.3 The General Run Time Objects Method 3516.4 Insert Data into the SQL Server Database Using the Run Time Object Method 3526.4.1 Insert Data into the Faculty Table for the SQL Server Database 3536.4.1.1 Validate Data Before the Data Insertion 3536.4.1.2 Insert Data into the Faculty Table 3556.4.1.3 Validate Data After the Data Insertion 3576.5 Insert Data into the Database Using Stored Procedures 3606.5.1 Insert Data into the SQL Server Database Using Stored Procedures 3606.5.1.1 Develop Stored Procedures in SQL Server Database 3616.5.1.2 Develop Codes to Call Stored Procedures to Insert Data into the Course Table 3636.6 Insert Data into the Database Using the LINQ To SQL Method 3686.6.1 Insert Data Into the SQL Server Database Using the LINQ to SQL Queries 3696.7 Chapter Summary 369Homework 370Chapter 7 Data Updating and Deleting with Visual Basic.NET 377Part I Data Updating and Deleting with Visual Studio.NET Design Tools and Wizards 3787.1 Update or Delete Data Against Databases 3787.1.1 Updating and Deleting Data from Related Tables in a DataSet 3797.1.2 Update or Delete Data Against Database Using TableAdapter DBDirect Methods - TableAdapter.Update and TableAdapter.Delete 3797.1.3 Update or Delete Data Against Database Using TableAdapter.Update Method 3807.2 Update and Delete Data For Microsoft SQL Server Database 3817.2.1 Create a New Project Based on the InsertWizard Project 3817.2.2 Application User Interfaces 3827.2.3 Validate Data Before the Data Updating and Deleting 3827.2.4 Build the Update and Delete Queries 3827.2.4.1 Configure the TableAdapter and Build the Data Updating Query 3837.2.4.2 Build the Data Deleting Query 3847.2.5 Develop Codes to Update Data Using the TableAdapter DBDirect Method 3857.2.5.1 Modifications of the Codes 3857.2.5.2 Creations of the Codes 3857.2.6 Develop Codes to Update Data Using the TableAdapter.Update Method 3877.2.7 Develop Codes to Delete Data Using the TableAdapter DBDirect Method 3887.2.8 Develop Codes to Delete Data Using the TableAdapter.Update Method 3907.2.9 Validate the Data After the Data Updating and Deleting 391Part II Data Updating and Deleting with Runtime Objects 3957.3 The Run Time Objects Method 3957.4 Update and Delete Data for SQL Server Database Using the Run Time Objects 3967.4.1 Update Data Against the Faculty Table in the SQL Server Database 3977.4.1.1 Develop Codes to Update the Faculty Data 3977.4.1.2 Validate the Data Updating 3997.4.2 Delete Data from the Faculty Table in the SQL Server Database 3997.4.2.1 Develop Codes to Delete Data 3997.4.2.2 Validate the Data Deleting 4017.5 Update and Delete Data against SQL Server Database Using Stored Procedures 4047.5.1 Modify an Existing Project to Create Our New Project 4057.5.2 Create the Codes to Update and Delete Data from the Course Table 4057.5.2.1 Develop Two Stored Procedures in the SQL Server Database 4077.5.2.2 Call the Stored Procedures to Perform the Data Updating and Deleting 4097.5.3 Update and Delete Data against Databases Using the LINQ to SQL Query 4127.5.3.1 Update and Delete Data Using LINQ to SQL Query for Student Table 4137.5.3.2 Create a New Object of the DataContext Class for Student Form 4147.5.3.3 Develop the Codes for the Select Button Click Event Procedure 4157.5.3.4 Develop the Codes for the Insert Button Click Event Procedure 4167.5.3.5 Develop the Codes for the Update Button Click Event Procedure 4197.5.3.6 Develop the Codes for the Delete Button Click Event Procedure 4197.5.3.7 Run the Project to Test Data Updating and Deleting Actions for Student Table 4217.6 Chapter Summary 423Homework 423Chapter 8 Accessing Data in ASP.NET 4298.1 What is .NET Framework? 4308.2 What is ASP.NET? 4318.2.1 ASP.NET Web Application File Structure 4338.2.2 ASP.NET Execution Model 4338.2.3 What is Really Happened When a Web Application is Executed? 4348.2.4 The Requirements to Test and Run the Web Project 4358.3 Develop ASP.NET Web Application to Select Data from SQL Server Databases 4358.3.1 Create the User Interface - LogIn Form 4368.3.2 Develop the Codes to Access and Select Data from the Database 4388.3.3 Validate the Data in the Client Side 4428.3.4 Create the Second User Interface - Selection Page 4438.3.5 Develop the Codes to Open the Other Page 4448.3.6 Modify the Codes in the LogIn Page to Transfer to the Selection Page 4468.3.7 Create the Third User Interface - Faculty Page 4478.3.8 Develop the Codes to Select the Desired Faculty Information 4488.3.8.1 Develop the Codes for the Page_Load Event Procedure 4498.3.8.2 Develop the Codes for the Select Button Click Event Procedure 4508.3.8.3 Develop the Codes for Other Procedures 4528.3.9 Create the Fourth User Interface - Course Page 4548.3.9.1 The AutoPostBack Property of the List Box Control 4578.3.10 Develop the Codes to Select the Desired Course Information 4578.3.10.1 Coding for the Course Page Loading and Ending Event Procedures 4588.3.10.2 Coding for the Select Button's Click Event Procedure 4598.3.10.3 Coding for the SelectedIndexChanged Event Procedure of the CourseList Box 4618.3.10.4 Coding for Other User Defined Subroutine Procedures 4638.4 Develop ASP.NET Web Application to Insert Data Into SQL Server Databases 4658.4.1 Develop the Codes to Perform the Data Insertion Function 4668.4.2 Develop the Codes for the Insert Button Click Event Procedure 4668.4.3 Validate the Data Insertion 4738.5 Develop Web Applications to Update and Delete Data in SQL Server Databases 4738.5.1 Modify the Codes for the Faculty Page 4748.5.2 Develop the Codes for the Update Button Click Event Procedure 4758.5.3 Develop the Codes for the Delete Button Click Event Procedure 4798.5.3.1 Relationships Between Five Tables in Our Sample Database 4808.5.3.2 Data Deleting Sequence 4818.5.3.3 Use the Cascade Deleting Option to Simplify the Data Deleting 4818.5.3.4 Create the Stored Procedure to Perform the Data Deleting 4838.5.3.5 Develop the Codes to Call the Stored Procedure to Perform the Data Deleting 4868.6 Develop ASP.NET Web Applications with LINQ to SQL Query 4898.6.1 Create a New Object of the DataContext Class 4918.6.2 Develop the Codes for the Data Selection Query 4928.6.3 Develop the Codes for the Data Insertion Query 4938.6.4 Develop the Codes for the Data Updating and Deleting Queries 4968.7 Chapter Summary 500Homework 500Chapter 9 ASP.NET Web Services 5059.1 What are Web Services and Their Components? 5069.2 Procedures to Build a Web Service 5089.2.1 The Structure of a Typical Web Service Project 5089.2.2 The Real Considerations When Building a Web Service Project 5099.2.3 Introduction to Windows Communication Foundation (WCF) 5099.2.3.1 What is the WCF? 5109.2.3.2 WCF Data Services 5109.2.3.3 WCF Services 5119.2.3.4 WCF Clients 5119.2.3.5 WCF Hosting 5129.2.3.6 WCF Visual Studio Templates 5129.2.4 Procedures to Build an ASP.NET Web Service 5139.3 Build ASP.NET Web Service Project to Access SQL Server Database 5149.3.1 Files and Items Created in the New Web Service Project 5159.3.2 A Feeling of the Hello World Web Service Project As it Runs 5189.3.3 Modify the Default Namespace 5209.3.4 Create a Base Class to Handle Error Checking for Our Web Service 5229.3.5 Create a Customer Returned Class to Hold All Retrieved Data 5229.3.6 Add Web Methods into Our Web Service Class 5249.3.7 Develop the Codes for Web Methods to Perform the Web Services 5249.3.7.1 Web Service Connection Strings 5249.3.7.2 Modify the Existing HelloWorld Web Method 5279.3.7.3 Develop the Codes to Perform the Database Queries 5289.3.7.4 Develop the Codes for Subroutines Used in the Web Method 5309.3.8 Develop the Stored Procedure to Perform the Data Query 5339.3.8.1 Develop the Stored Procedure WebSelectFacultySP 5339.3.8.2 Add Another Web Method to Call the Stored Procedure 5349.3.9 Use DataSet as the Returning Object for the Web Method 5369.3.10 Build Windows-based Web Service Clients to Consume the Web Services 5389.3.10.1 Create a Web Service Proxy Class 5399.3.10.2 Develop the Graphic User Interface for the Windows-based Client Project 5419.3.10.3 Develop the Code to Consume the Web Service 5419.3.11 Build Web-based Web Service Clients to Consume the Web Service 5489.3.11.1 Create a New Web Site Project and Add an Existing Web Page 5489.3.11.2 Add a Web Service Reference and Modify the Web Form Window 5499.3.11.3 Modify the Designer and Codes for the Related Event Procedures 5509.3.12 Deploy the Completed Web Service to Production Servers 5559.3.12.1 Publish the Desired Web Service 5579.4 Build ASP.NET Web Service Project to Insert Data Into SQL Server Database 5599.4.1 Create a New Web Service Project WebServiceSQLInsert 5599.4.2 Develop Four Web Service Methods 5609.4.2.1 Develop Codes for the First Web Method SetSQLInsertSP 5619.4.2.2 Develop Codes for User Defined Functions and Subroutine Procedures 5639.4.2.3 Develop the Second Web Method GetSQLInsert 5659.4.2.4 Develop the Third Web Method SQLInsertDataSet 5689.4.2.5 Develop the Fourth Web Method GetSQLInsertCourse 5729.4.3 Build Windows-based Web Service Clients to Consume the Web Services 5789.4.3.1 Create a Windows-Based Consume Project and a Web Service Proxy Class 5789.4.3.2 Develop the Graphic User Interface for the Client Project 5799.4.3.3 Develop the Code to Consume the Web Service 5819.4.4 Build Web-based Web Service Clients to Consume the Web Services 5949.4.4.1 Create a New Web Site Project and Add an Existing Web Page 5949.4.4.2 Add a Web Service Reference and Modify the Web Form Window 5959.4.4.3 Modify the Codes for the Related Event Procedures 5969.5 Build ASP.NET Web Service to Update and Delete Data for SQL Server Database 6069.5.1 Modify the Default Namespace and Add Database Connection String 6079.5.2 Create Our Customer-Built Base and Returned Classes 6089.5.3 Create a Web Method to Call Stored Procedure to Update Student Records 6099.5.4 Create a Web Method to Call Stored Procedure to Delete Student Records 6119.5.5 Develop Two Stored Procedures WebUpdateStudentSP and WebDeleteStudentSP 6139.5.5.1 Develop the Stored Procedure WebUpdateStudentSP 6139.5.5.2 Develop the Stored Procedure WebDeleteStudentSP 6169.6 Build Windows-Based Web Service Clients to Consume the Web Services 6189.6.1 Modify the Student Form Window 6189.6.2 Add a New Web Reference to Our Client Project 6199.6.3 Build the Codes to the Update Button Click Event Procedure 6209.6.4 Build the Codes to the Delete Button Click Event Procedure 6219.7 Build Web-Based Web Service Clients to Consume the Web Services 6249.7.1 Create a New Web Site Application Project and Add an Existing Web Page 6259.7.2 Add a Web Service Reference and Modify the Web Form Window 6259.7.3 Modify the Codes Inside the Back Button Click Event Procedure 6269.7.4 Add the Codes to the Update Button Click Event Procedure 6269.7.5 Develop Codes for the Delete Button Click Event Procedure 6289.8 Chapter Summary 631Homework 632Appendix A: Install and Configure SQL Server 2017 Express Database 637Appendix B: Download and Install DevExpress .NET UI Controls 649Appendix C: Download & Install FrontPage Server Extension for Windows 10 651Appendix D: How to Use Sample Database 655Index 657

O autorze

Ying Bai, PhD, is a Professor in the Department of Computer Science and Engineering at Johnson C. Smith University (JCSU). He has published multiple books with Wiley-IEEE Press including Practical Microcontroller Engineering with ARM Technology, Practical Database Programming with Visual C#.NET and Practical Database Programming with Java.