C# 2008 for Programmers: Databases and LINQ to SQL
- 21.1 Introduction
- 21.2 Relational Databases
- 21.3 Relational Database Overview: Books Database
- 21.4 SQL
- 21.5 LINQ to SQL
- 21.6 LINQ to SQL: Extracting Information from a Database
- 21.7 More Complex LINQ Queries and Data Binding
- 21.8 Retrieving Data from Multiple Tables with LINQ
- 21.9 Creating a Master/Detail View Application
- 21.10 Programming with LINQ to SQL: Address-Book Case Study
- 21.11 Wrap-Up
- 21.12 Tools and Web Resources
This chapter is from the book
This chapter is from the book
This chapter is from the book
It is a capital mistake to theorize before one has data.
—Arthur Conan Doyle
Now go, write it before them in a table, and note it in a book, that it may be for the time to come for ever and ever.
—Isaiah 30:8
Get your facts first, and then you can distort them as much as you please.
—Mark Twain
I like two kinds of men: domestic and foreign.
—Mae West
OBJECTIVES
In this chapter you’ll learn:
- The relational database model.
- To write basic database queries in SQL.
- To use LINQ to SQL to retrieve and manipulate data from a database.
- To add data sources to projects.
- To use the Object Relational Designer to create LINQ to SQL classes.
- To use the IDE’s drag-and-drop capabilities to display database tables in applications.
- To use data binding to move data seamlessly between GUI controls and databases.
- To create Master/Detail views.
Outline
|
21.1 |
Introduction |
|
21.2 |
Relational Databases |
|
21.3 |
Relational Database Overview: Books Database |
|
21.4 |
SQL |
|
21.4.1 Basic SELECT Query |
|
|
21.4.2 WHERE Clause |
|
|
21.4.3 ORDER BY Clause |
|
|
21.4.4 Retrieving Data from Multiple Tables: INNER JOIN |
|
|
21.4.5 INSERT Statement |
|
|
21.4.6 UPDATE Statement |
|
|
21.4.7 DELETE Statement |
|
|
21.5 |
LINQ to SQL |
|
21.6 |
LINQ to SQL: Extracting Information from a Database |
|
21.6.1 Creating LINQ to SQL Classes |
|
|
21.6.2 Creating Data Bindings |
|
|
21.7 |
More Complex LINQ Queries and Data Binding |
|
21.8 |
Retrieving Data from Multiple Tables with LINQ |
|
21.9 |
Creating a Master/Detail View Application |
|
21.10 |
Programming with LINQ to SQL: Address-Book Case Study |
|
21.11 |
Wrap-Up |
|
21.12 |
Tools and Web Resources |
21.1 Introduction
A database is an organized collection of data. Many strategies exist for organizing data to facilitate easy access and manipulation. A database management system (DBMS) provides mechanisms for efficiently storing, organizing, retrieving and modifying data for many users. Database management systems allow access to and storage of data independently of its internal representation—this allows the internal representation to be structured to maximize efficiency while the external representation maximizes ease of use.
Today’s most popular DBMSs manage relational databases, which organize data simply as tables with rows and columns. A language called Structured Query Language (SQL)—pronounced “sequel,” or as its individual letters—is the international standard language used almost universally with relational databases to perform queries (i.e., to request information that satisfies given criteria) and to manipulate data in a database. In this book, we pronounce SQL as “sequel.”
Some popular proprietary database management systems are Microsoft SQL Server, Oracle, Sybase and IBM DB2. PostgreSQL and MySQL are open-source DBMSs that can be downloaded and used freely by anyone. You may also be familiar with Microsoft Access—a relational database system that is part of Microsoft Office. In this chapter, we use Microsoft SQL Server 2005 Express. The latest version can be downloaded from www.microsoft.com/express/sql. As this book was sent to publication, SQL Server 2008 Express was released. You can use either the 2005 or the 2008 version with this chapter.
Chapter 9 introduced LINQ to Objects, which allows you to manipulate data stored in arrays and collections using a syntax similar to SQL. LINQ to SQL allows you to manipulate relational data stored in a SQL Server database. LINQ to SQL provides the expressiveness of SQL with the additional benefits of the C# compiler’s type checking and the IDE’s IntelliSense.
This chapter introduces general concepts of relational databases and SQL, then explores LINQ to SQL and the IDE’s tools for working with databases. In the next two chapters, you’ll see other practical database and LINQ to SQL applications. Chapter 22, ASP.NET 3.5 and ASP.NET AJAX, presents a web-based bookstore case study that retrieves user and book information from a database. Chapter 23, Windows Communication Foundation (WCF) Web Services, uses a database to store airline reservation data for a web service (i.e., a class that allows its methods to be called by methods on other machines via common data formats and protocols).