Understanding LINQ and Relational Databases in C#: A Guide for Microsoft Visual C# 2012

1. Chapter 15: Using LINQ to Access Data in C# Programs

2. Understanding Relational Database Fundamentals Microsoft Visual C# 2012, Fifth Edition • Database • Holds a file or, more frequently, a group of files that an organization needs to support its applications • In a database, the files often are called tables because you can arrange their contents in rows and columns • Primary key (or key) • A value that uniquely identifies a record

3. Understanding Relational Database Fundamentals (cont’d.) Microsoft Visual C# 2012, Fifth Edition

4. Understanding Relational Database Fundamentals (cont’d.) Microsoft Visual C# 2012, Fifth Edition • Database management software • Also called a database management system (DBMS) • Allows you to: • Create table descriptions • Identify keys • Add, delete, and update records within a table • Arrange records within a table so they are sorted by different fields • Write questions that select specific records from a table for viewing • Write questions that combine information from multiple tables • Create reports that allow users to easily interpret your data • Keep data secure by employing sophisticated security measures

5. Understanding Relational Database Fundamentals (cont’d.) Microsoft Visual C# 2012, Fifth Edition • Database management software (cont’d.) • Establishes and maintains relationships between the columns in tables • A group of database tables from which you can make these connections is a relational database

6. Creating Databases and Table Descriptions (cont’d.) Microsoft Visual C# 2012, Fifth Edition

7. Identifying Primary Keys Microsoft Visual C# 2012, Fifth Edition • The primary key in a table is the column that makes each record different from all others • Each primary key must be unique • Typical examples of primary keys include: • A student ID number in a table that contains college student information • A part number in a table that contains inventory items • A Social Security number in a table that contains employee information

8. Creating SQL Queries Microsoft Visual C# 2012, Fifth Edition • Query • Simply a request using syntax that the database software can understand • Structured Query Language (or SQL) • The most common language that database administrators use to access data in their tables • The basic form of the SQL command that retrieves selected records from a table • SELECT-FROM-WHERE • Example: SELECT custId, lastName FROM tblCustomer WHERE state = "WI"

9. Creating an Access Database Microsoft Visual C# 2012, Fifth Edition • Microsoft Office Access • A relational database that is part of some versions of the Microsoft Office system

10. Creating an Access Database (cont’d.) Microsoft Visual C# 2012, Fifth Edition

11. Creating an Access Database (cont’d.) Microsoft Visual C# 2012, Fifth Edition

12. Understanding LINQ Microsoft Visual C# 2012, Fifth Edition • LINQ (Language INtegrated Query) Project • Provides queries that can be used in C# and Visual Basic • In older versions of C#, you could access database data by passing an SQL string to a database object • OleDbCommand is a built-in type used to access databases • LINQ provides a set of general-purpose standard operators that allow queries to be constructed using syntax that is easy to understand

13. Understanding LINQ (cont’d.) Microsoft Visual C# 2012, Fifth Edition • The operators defined in LINQ can be used to query arrays, enumerable classes, XML, relational databases, and other sources • Some keywords in the LINQ vocabulary: • select indicates what to select • from indicates the collection or sequence from which data will be drawn • where indicates conditions for selecting records

14. Retrieving Data from an Access Database in C# Microsoft Visual C# 2012, Fifth Edition • Save time and reduce the chance for error by using the built-in tools of the Visual Studio IDE • Tasks for adding a database table to a Windows Form project: • Drag a DataGridView object from the IDE’s Toolbox onto a Form • Next, browse for the project data source and answer a few questions • Make a few choices and browse for the database file to add to the project

15. Retrieving Data from an Access Database in C# (cont’d.) Microsoft Visual C# 2012, Fifth Edition

16. Retrieving Data from an Access Database in C# (cont’d.) Microsoft Visual C# 2012, Fifth Edition

17. Retrieving Data from an Access Database in C# (cont’d.) Microsoft Visual C# 2012, Fifth Edition

18. Retrieving Data from an Access Database in C# (cont’d.) Microsoft Visual C# 2012, Fifth Edition

19. Using LINQ Queries with an Access Database Table Microsoft Visual C# 2012, Fifth Edition You are not required to use the data grid to view database records You can use a LINQ query to select a collection of data from a table when specific criteria are met

20. Using LINQ Queries with an Access Database Table (cont’d.) Microsoft Visual C# 2012, Fifth Edition

21. Using LINQ Queries with an Access Database Table (cont’d.) Microsoft Visual C# 2012, Fifth Edition

22. Using LINQ Queries with an Access Database Table (cont’d.) Microsoft Visual C# 2012, Fifth Edition

23. Using LINQ Queries with an Access Database Table (cont’d.) Microsoft Visual C# 2012, Fifth Edition