Home > Articles > Programming > Windows Programming

C# 2008 for Programmers: Databases and LINQ to SQL

  • Print
  • + Share This
This chapter introduces relational databases, SQL, LINQ to SQL and the IDE’s visual programming tools for working with databases.
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).

  • + Share This
  • 🔖 Save To Your Account