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.

About the Author xix

Preface xxi

Acknowledgment xxiii

About the Companion Website xxiv

Chapter 1 Introduction 1

1.1 Outstanding Features About This Book 2

1.2 This Book is For 2

1.3 What This Book Covers 2

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 8

Chapter 2 Introduction to Databases 9
Ying Bai and Satish Bhalla

2.1 What are Databases and Database Programs? 10

2.1.1 File Processing System 10

2.1.2 Integrated Databases 11

2.2 Develop a Database 12

2.3 Sample Database 13

2.3.1 Relational Data Model 13

2.3.2 Entity-Relationship Model (ER) 17

2.4 Identifying Keys 18

2.5 Define Relationships 18

2.6 ER Notation 22

2.7 Data Normalization 23

2.7.1 First Normal Form (1NF) 23

2.7.2 Second Normal Form (2NF) 24

2.7.3 Third Normal Form (3NF) 26

2.8 Database Components in Some Popular Databases 28

2.8.1 Microsoft Access Databases 28

2.8.2 SQL Server Databases 29

2.8.3 Oracle Databases 32

2.9 Create Microsoft SQL Server 2017 Express Sample Database 35

2.9.1 Create the LogIn Table 36

2.9.2 Create the Faculty Table 37

2.9.3 Create Other Tables 39

2.9.4 Create Relationships Among Tables 45

2.9.4.1 Create Relationship Between the LogIn and the Faculty Tables 46

2.9.4.2 Create Relationship Between the LogIn and the Student Tables 49

2.9.4.3 Create Relationship Between the Faculty and the Course Tables 50

2.9.4.4 Create Relationship Between the Student and the StudentCourse Tables 50

2.9.4.5 Create Relationship Between the Course and the StudentCourse Tables 51

2.9.5 Store Images to the SQL Server 2017 Express Database 53

2.10 Chapter Summary 61

Homework 63

Chapter 3 Introduction to ADO.NET 67

3.1 The ADO and ADO.NET 67

3.2 Overview of the ADO.NET 69

3.3 The Architecture of the ADO.NET 70

3.4 The Components of ADO.NET 71

3.4.1 The Data Provider 72

3.4.1.1 The ODBC Data Provider 73

3.4.1.2 The OLEDB Data Provider 73

3.4.1.3 The SQL Server Data Provider 74

3.4.1.4 The Oracle Data Provider 74

3.4.2 The Connection Class 74

3.4.2.1 The Open() Method of the Connection Class 77

3.4.2.2 The Close() Method of the Connection Class 77

3.4.2.3 The Dispose() Method of the Connection Class 78

3.4.3 The Command and the Parameter Classes 78

3.4.3.1 The Properties of the Command Class 79

3.4.3.2 The Constructors and Properties of the Parameter Class 79

3.4.3.3 Parameter Mapping 80

3.4.3.4 The Methods of the ParameterCollection Class 82

3.4.3.5 The Constructor of the Command Class 83

3.4.3.6 The Methods of the Command Class 84

3.4.4 The DataAdapter Class 87

3.4.4.1 The Constructor of the DataAdapter Class 87

3.4.4.2 The Properties of the DataAdapter Class 87

3.4.4.3 The Methods of the DataAdapter Class 88

3.4.4.4 The Events of the DataAdapter Class 88

3.4.5 The DataReader Class 90

3.4.6 The DataSet Component 92

3.4.6.1 The DataSet Constructor 94

3.4.6.2 The DataSet Properties 94

3.4.6.3 The DataSet Methods 94

3.4.6.4 The DataSet Events 94

3.4.7 The DataTable Component 97

3.4.7.1 The DataTable Constructor 98

3.4.7.2 The DataTable Properties 98

3.4.7.3 The DataTable Methods 99

3.4.7.4 The DataTable Events 100

3.4.8 ADO.NET Entity Framework 102

3.4.8.1 Advantages of Using the Entity Framework 6 104

3.4.8.2 The ADO.NET 4.3 Entity Data Model 106

3.4.8.3 Using Entity Framework 6 Entity Data Model Wizard 110

3.5 Chapter Summary 118

Homework 120

Chapter 4 Introduction to Language Integrated Query (LINQ) 123

4.1 Overview of Language Integrated Query 123

4.1.1 Some Special Interfaces Used in LINQ 124

4.1.1.1 The IEnumerable and IEnumerable(Of T) Interfaces 124

4.1.1.2 The IQueryable and IQueryable(Of T) Interfaces 125

4.1.2 Standard Query Operators 126

4.1.3 Deferred Standard Query Operators 127

4.1.4 Non-Deferred Standard Query Operators 131

4.2 Introduction to LINQ Query 135

4.3 The Architecture and Components of LINQ 137

4.3.1 Overview of LINQ to Objects 138

4.3.2 Overview of LINQ to DataSet 139

4.3.3 Overview of LINQ to SQL 139

4.3.4 Overview of LINQ to Entities 140

4.3.5 Overview of LINQ to XML 140

4.4 LINQ to Objects 141

4.4.1 LINQ and ArrayList 142

4.4.2 LINQ and Strings 143

4.4.2.1 Query a String to Determine the Number of Numeric Digits 144

4.4.2.2 Sort Lines of Structured Text By any Field in the Line 145

4.4.3 LINQ and File Directories 147

4.4.3.1 Query the Contents of Files in a Folder 148

4.4.4 LINQ and Reflection 150

4.5 LINQ to DataSet 152

4.5.1 Operations to DataSet Objects 152

4.5.1.1 Query Expression Syntax 153

4.5.1.2 Method-Based Query Syntax 154

4.5.1.3 Query the Single Table 157

4.5.1.4 Query the Cross Tables 159

4.5.1.5 Query Typed DataSet 162

4.5.2 Operations to DataRow Objects Using the Extension Methods 165

4.5.3 Operations to DataTable Objects 169

4.6 LINQ to SQL 170

4.6.1 LINQ to SQL Entity Classes and DataContext Class 171

4.6.1.1 Add LINQ to Data Reference 171

4.6.1.2 Add LINQ To SQL Tools 171

4.6.2 LINQ to SQL Database Operations 175

4.6.2.1 Data Selection Query 175

4.6.2.2 Data Insertion Query 177

4.6.2.3 Data Updating Query 178

4.6.2.4 Data Deletion Query 179

4.6.3 LINQ to SQL Implementations 182

4.7 LINQ to Entities 182

4.7.1 The Object Services Component 183

4.7.2 The ObjectContext Component 183

4.7.3 The ObjectQuery Component 184

4.7.4 LINQ to Entities Flow of Execution 184

4.7.5 Implementation of LINQ to Entities 186

4.8 LINQ to XML 187

4.8.1 LINQ to XML Class Hierarchy 187

4.8.2 Manipulate XML Elements 188

4.8.2.1 Creating XML from Scratch 188

4.8.2.2 Insert XML 190

4.8.2.3 Update XML 191

4.8.2.4 Delete XML 192

4.8.3 Manipulate XML Attributes 192

4.8.3.1 Add XML Attributes 192

4.8.3.2 Get XML Attributes 193

4.8.3.3 Delete XML Attributes 193

4.8.4 Query XML with LINQ to XML 194

4.8.4.1 Standard Query Operators and XML 194

4.8.4.2 XML Query Extensions 195

4.8.4.3 Using Query Expressions with XML 196

4.8.4.4 Using XPath and XSLT with LINQ to XML 196

4.8.4.5 Mixing XML and Other Data Models 197

4.9 Visual Basic.NET Language Enhancement for LINQ 199

4.9.1 Lambda Expressions 199

4.9.2 Extension Methods 201

4.9.3 Implicitly Typed Local Variables 205

4.9.4 Query Expressions 206

4.10 Chapter Summary 208

Homework 209

Chapter 5 Data Selection Query with Visual Basic.NET 215

Part I Data Query with Visual Studio.NET Design Tools and Wizards 216

5.1 A Completed Sample Database Application Example 216

5.2 Visual Studio.NET Design Tools and Wizards 219

5.2.1 Data Components in the Toolbox Window 220

5.2.1.1 The DataSet 220

5.2.1.2 DataGridView 221

5.2.1.3 BindingSource 222

5.2.1.4 BindingNavigator 222

5.2.1.5 TableAdapter 223

5.2.1.6 TableAdapter Manager 223

5.2.2 Data Source Window 223

5.2.2.1 Add New Data Sources 224

5.2.2.2 Data Source Configuration Wizard 224

5.2.2.3 DataSet Designer 228

5.3 Query Data from SQL Server Database Using Design Tools and Wizards 231

5.3.1 Application User Interface 231

5.3.1.1 The LogIn Form 232

5.3.1.2 The Selection Form 232

5.3.1.3 The Faculty Form 232

5.3.1.4 The Course Form 234

5.3.1.5 The Student Form 234

5.4 Use Visual Studio Wizards and Design Tools to Query and Display Data 236

5.4.1 Query and Display Data using the DataGridView and Detail Controls 236

5.4.1.1 View the Entire Table 238

5.4.1.2 View Each Record or the Specified Columns with Detail View 241

5.4.2 Use DataSet Designer to Edit the Structure of the DataSet 243

5.4.3 Bind Data to the Associated Controls in LogIn Form 245

5.4.4 Develop Codes to Query Data Using the Fill() Method 249

5.4.5 Use Return a Single Value to Query Data for LogIn Form 251

5.4.6 Develop the Codes for the Selection Form 254

5.4.7 Query Data from the Faculty Table for the Faculty Form 256

5.4.8 Develop Codes to Query Data from the Faculty Table 258

5.4.8.1 Develop Codes to Query Data Using the TableAdapter Method 258

5.4.8.2 Develop Codes to Query Data Using the LINQ to DataSet Method 261

5.4.9 Query Data from the Course Table for the Course Form 262

5.4.9.1 Build the Course Queries Using the Query Builder 263

5.4.9.2 Bind Data Columns to the Associated Controls in the Course Form 265

5.4.9.3 Develop Codes to Query Data for the Course Form 267

Part II Data Query with Runtime Objects 271

5.5 Introduction to Runtime Objects 272

5.5.1 Procedure of Building a Data-Driven Application Using Runtime Object 274

5.6 Query Data from SQL Server Database Using Runtime Object 274

5.6.1 Access to SQL Server Database 274

5.6.2 Declare Global Variables and Runtime Objects 276

5.6.3 Query Data Using Runtime Objects for the LogIn Form 278

5.6.3.1 Connect to the Data Source with the Runtime Object 278

5.6.3.2 Coding for Method 1: Using the TableAdapter to Query Data 279

5.6.3.3 Coding for Method 2: Using the DataReader to Query Data 281

5.6.4 The Coding for the Selection Form 283

5.6.5 Query Data Using Runtime Objects for the Faculty Form 284

5.6.5.1 Using Three Query Methods to Retrieve Images from SQL Server Database 290

5.6.6 Query Data Using Runtime Objects for the Course Form 290

5.6.6.1 Retrieve Data from Multiple Tables Using Tables JOINS 293

5.6.7 Query Data Using Runtime Objects for the Student Form 301

5.6.7.1 Query Student Data Using Stored Procedures 302

5.6.7.2 Query Data Using Stored Procedures for Student Form 306

5.6.7.3 Query Data Using More Complicated Stored Procedures 315

5.7 Chapter Summary 320

Homework 321

Chapter 6 Data Inserting with Visual Basic.NET 327

Part I Insert Data with Visual Basic.NET Design Tools and Wizards 328

6.1 Insert Data Into a Database 328

6.1.1 Insert New Records into a Database Using the TableAdapter.Insert Method 329

6.1.2 Insert New Records into a Database Using the TableAdapter.Update Method 329

6.2 Insert Data into the SQL Server Database Using a Sample Project InsertWizard 330

6.2.1 Create InsertWizard Project Based on the SelectWizard Project 330

6.2.2 Application User Interfaces 331

6.2.3 Validate Data...