C# 24-Hour Trainer (2015)

Section VII

Specialized Topics

Lesson 37

LINQ to SQL

Lesson 36 provided an introduction to LINQ to Objects. This lesson gives a brief introduction to another of the LINQ family of technologies: LINQ to SQL.

LINQ to SQL lets you use queries similar to those provided by LINQ to Objects to manipulate SQL Server databases. It uses a set of classes to represent database objects such as tables and records. The classes provide intuitive methods for adding, modifying, deleting, and otherwise manipulating the records.

In this lesson you learn the basics of LINQ to SQL. You learn how to make LINQ objects representing a SQL Server database and how to add records to the database. You also learn how to perform queries similar to those described in Lesson 36 to filter and sort data taken from the database.

Note that the programs and techniques described in this lesson demonstrate only very simple uses of LINQ to SQL. For more information, search the web. Microsoft's “LINQ to SQL” page at msdn.microsoft.com/library/bb386976.aspx provides a good starting point for learning more about LINQ to SQL.

Connecting to the Database

The first step in creating a LINQ to SQL program is connecting to the database. Create a Windows Forms application as usual. Then open the Server Explorer shown in Figure 37.1. (Use the View menu's Server Explorer command if you can't find it.)

Figure 37.1

NOTE

To run most of the programs described in this lesson, you need to have SQL Server installed on your computer. The Visual Studio installation software comes with SQL Server (at least the versions I've seen) or you can download the free SQL Server Express edition at www.microsoft.com/express/Database. It's a fairly busy page so the download link can be difficult to see. You can find it by searching for “Express.”

NOTE

Unfortunately there isn't room in this book to say too much about SQL Server and how to use it. You'll have to rely on the web or get a book about SQL Server to do much with it.

To make using the examples described in this lesson a bit easier, this lesson's download includes a program named Build Customer Database. This program connects to your SQL Server instance, deletes the database named CustomerDatabase if it exists, and creates a new CustomerDatabase containing a few records for the examples described in this lesson to use.

Obviously don't run this program if you already have a database named CustomerDatabase that you want to preserve because that database will be destroyed!

Click the Connect to Database button (third from the left at the top in Figure 37.1) to display the Add Connection dialog shown in Figure 37.2.

Figure 37.2

Initially the Add Connection dialog may have some type of database other than SQL Server selected. The dialog shown in Figure 37.2 is ready to connect to a Microsoft Access database. To switch to SQL Server, click the Change button to display the dialog shown inFigure 37.3.

Figure 37.3

Select the Microsoft SQL Server entry and click OK. When you return to the Add Connection dialog, it should look like Figure 37.4.

Figure 37.6

Enter your server name in the indicated textbox. If you're running SQL Server Express Edition, follow the server's name with \SQLEXPRESS, as shown in the figure. For example, my computer is named Quarkbeast and I'm running SQL Server Express Edition so I entered QUARKBEAST\SQLEXPRESS in Figure 37.4.

Enter the name of the database on the server (I entered CustomerDatabase in Figure 37.4) and click OK.

NOTE

In the dialog shown in Figure 37.4, if you enter the name of a database that doesn't exist on the server, Visual Studio tells you that the database doesn't exist and asks if you want to create it. If you click Yes, you can use the Server Explorer to build the database. The Server Explorer doesn't provide as many features as a database management tool such as SQL Server Management Studio, but it's handy if you don't have easy access to those tools.

If you go back to Figure 37.1, you can see the Server Explorer with the quarkbeast\sqlexpress server's CustomerDatabase expanded to show its single table, Customers, and its columns.

NOTE

You can use similar steps to connect to other kinds of databases such as Oracle, MySQL, or Microsoft Access databases. Only the details needed to connect to the database in the Add Connection dialog are different. For example, Figure 37.2 shows the details needed for a Microsoft Access database, and Figure 37.4 shows the details needed for a SQL Server database.

Note that LINQ to SQL is intended to work with SQL Server and making it work with other types of databases takes some extra work. The section “Using LINQ to SQL with Access” later in this lesson explains how to use LINQ to SQL classes with Access databases, but there's no guarantee that the same techniques will work with every kind of database or that those techniques will keep working in later versions of Visual Studio.

Making LINQ to SQL Classes

After you make a database connection, you're ready to build LINQ to SQL classes that you can use to manipulate the database.

Open the Project menu and select Add New Item. In the Add New Item dialog, select the LINQ to SQL Classes template. If you have trouble finding it, you can narrow your search by looking in the Data template category on the left. Enter a descriptive name for the new file such as CustomerClasses and click Add.

At this point Visual Studio creates a .dbml file to manage the new LINQ to SQL classes. It opens that file in the Object Relational Designer shown in Figure 37.5, although initially the designer is blank.

Figure 37.5

In Server Explorer, expand your database until you find its tables and drag the tables that you want to manage onto the designer surface. In Figure 37.5, I dragged the Customers table onto the surface so the designer created a class to represent the table. Each instance of the class will represent a row in the table.

The designer represents the table's fields as properties. If you look closely at Figure 37.5, you can see that the table's primary key fields FirstName and LastName have little key symbols on the left.

If you click a field in the designer, the Properties window shows the field's properties. Figure 37.6 shows the properties for the table's FirstName field.

Figure 37.6

A few important properties include:

· Name—The name of the field in the class

· Nullable—Indicates whether the field can hold null values

· Server Data Type—The data type of the field in the database

· Source—The name of the field in the database

If you make changes and save them, Visual Studio automatically generates a CustomerClassesDataContext class to represent the database. (CustomerClasses is the name I gave the new LINQ to SQL file.) This object has a Customers property that represents the database's Customers table.

Visual Studio also creates a Customer class to represent the records in the table.

You can look at these classes (although don't modify them) in the file CustomerClasses.designer.cs by double-clicking the CustomerClassesDataContext entry in Solution Explorer.

NOTE

Note that this is an extremely simple example. Most real databases contain multiple tables. In that case, you can use the entity-relationship designer to model the relationships between the tables.

To add a relationship, right-click the designer's surface, open the Add menu, and select Association. Select the parent and child classes from the dropdown lists and then select the fields that match up in the two classes.

For example, an Orders table might hold an OrderId field that you can use to find corresponding OrderItems records that make up the order. In that case, the parent class would be Orders, the child class would be OrderItems, and the fields that match up would be the OrderId fields in both classes.

After you build the association, the designer displays an arrow to represent the one-to-many relationship between the two classes (one Order may hold many OrderItems).

Writing Code

Now that you've built the LINQ to SQL classes, you can use them to manipulate the database. For example, the Make Customer Data example program shown in Figure 37.7 (and available as part of this lesson's code download) uses LINQ to SQL classes to add new records to a database.

Figure 37.7

The following code shows how the Make Customer Data example program adds a new record to the database:

// Add a new Customers record.

private void addButton_Click(object sender, EventArgs e)

{

// Get the database.

using (CustomerClassesDataContext db =

new CustomerClassesDataContext())

{

// Make a new Customer object.

Customer cust = new Customer();

cust.FirstName = firstNameTextBox.Text;

cust.LastName = lastNameTextBox.Text;

cust.Balance = decimal.Parse(balanceTextBox.Text);

cust.DueDate = DateTime.Parse(dueDateTextBox.Text);

// Add it to the table.

db.Customers.InsertOnSubmit(cust);

// Submit the changes.

db.SubmitChanges();

}

// Prepare to add the next customer.

firstNameTextBox.Clear();

lastNameTextBox.Clear();

balanceTextBox.Clear();

dueDateTextBox.Clear();

firstNameTextBox.Focus();

}

The code starts with what is probably its least obvious step: creating a new instance of the CustomerClassesDataContext. This object represents the database and provides access to its tables. It provides a Dispose method so the program creates it in a using block to callDispose automatically.

Next the code creates a new Customer object to represent a new row in the Customers table. The code initializes this object's properties.

The program then calls the Customers table's InsertOnSubmit method, passing it the new Customer object. The following statement calls the database object's SubmitChanges method to send any pending changes (in this case, the new Customer) to the database.

The code finishes by clearing its TextBoxes so the form is ready for you to enter another customer's data.

Using LINQ Queries

The Make Customer Data program described in the previous section uses LINQ to SQL classes to manage the database but it doesn't actually use LINQ queries.

You can use LINQ queries with these classes much as you can use them with lists, arrays, and classes that you build yourself in code. For example, you can use a query to select particular records from a table.

The following code shows how the Find Customers program described in Lesson 36 displayed customers with negative account balances:

// Display customers with negative balances.

var negativeQuery =

from Customer cust in customers

where cust.Balance < 0

//orderby cust.Balance ascending, cust.FirstName

select cust;

negativeListBox.DataSource = negativeQuery.ToArray();

The following code shows how the Find Customers program available in this lesson displays the same customers from the Customers table:

// Display customers with negative balances.

var negativeQuery =

from Customer cust in db.Customers

where cust.Balance < 0

//orderby cust.Balance ascending, cust.FirstName

select String.Format("{0} {1}\t{2:C}\t{3:d}",

cust.FirstName, cust.LastName,

cust.Balance, cust.DueDate);

negativeListBox.DataSource = negativeQuery.ToArray();

These two queries differ in two ways. First, the second query ranges over items in the db.Customers LINQ to SQL object instead of a customers array created by the program's code.

The second difference is that the new version's select clause doesn't select Customer objects. Instead it concatenates certain fields taken from those objects. I made this change because the Customer class generated by LINQ to SQL doesn't override its ToString method to display a nice representation of the object as the earlier version of the class did in Lesson 36. The new version builds strings that the ListBox can display directly.

Understanding Nullable Fields

Although LINQ to Objects and LINQ to SQL queries work mostly in the same way, some important differences exist behind the scenes.

One difference that you are likely to run into immediately is that values provided by LINQ to SQL classes are often nullable. A nullable type is a data type that can hold the special value null in addition to whatever other values it normally holds. The value nullrepresents “no value.”

For example, a nullable int can hold an integer or it can hold the special value null, which means it doesn't contain any real integer value.

NOTE

Only value types (such as structures, enumerated types, ints, and doubles) can be nullable because reference types can already hold the value null. The only surprising case is string, which looks a lot like a value type but is really a reference type.

NOTE

You can declare your own nullable variables by following their data types with a question mark. For example, the following code declares a nullable integer variable named numCourses and assigns it the initial value null:

int? numCourses = null;

Databases often have fields that are allowed to have no value and the LINQ to SQL classes represent them as nullable properties. In the database's Customers table, the Balance and DueDate fields are not required so the Customer LINQ to SQL class makes its Balance andDueDate fields nullable. That means when the program's C# code looks at those fields, they may not contain any value.

To decide whether a field contains a value, you can compare it to null or use its HasValue property. Once you know that the value exists, you can use its Value property to get the value.

For example, the following code checks whether a Customer's Balance field is null and, if the value exists, displays it:

if (cust.Balance != null)

{

// There is a Balance. Display it.

MessageBox.Show("Balance: " + cust.Balance.Value.ToString());

}

Understanding Query Execution

Although LINQ to SQL looks a lot like LINQ to Objects in your C# code, behind the scenes there is a huge difference in the way the two kinds of queries are executed.

The C# compiler converts a LINQ to Objects query into a series of method calls to do all of the work. The code does nothing that you couldn't do yourself in C# code, so it works more or less the way you would expect C# code to work.

In contrast, the compiler converts a LINQ to SQL query into code that can execute within the database. Instead of executing code within your program, it sends commands to the database to make it do all of the work. With a bit of effort, you could come up with similar database commands yourself and make your program execute them on the database, but LINQ to SQL does that for you.

Why should LINQ to SQL handle this differently?

Suppose you want to find a customer with a particular name in a customers array that holds 100,000 objects. LINQ to Objects can zip through the array fairly quickly and find the right customer with little problem.

Now suppose you want to find the same customer in a database containing 100,000 records. To perform that search in C# code, the program would need to fetch 100,000 records from the database. Moving that much data from the database into the program would take quite a bit of time and memory. In contrast, the database itself has great tools for finding specific records, particularly if the table uses the Name field as an index. In that case, the database may need to perform only a few disk accesses to search through its index structure for the right customer, a much more efficient operation than moving 100,000 records into the program and then searching them sequentially.

For many reasonably simple queries, the translation from LINQ query syntax into something the database can understand works and there's no problem. Sometimes, however, your query code doesn't translate easily into database-speak and the database can't execute it.

For example, the following code shows how the Find Customer program described in Lesson 36 displayed customers who owe more than $50 and who are more than 30 days overdue. (In this code the variable today holds the current date and is used to simplify the code.)

// Display customers who owe at least $50

// and are overdue at least 30 days.

var overdueQuery =

from Customer cust in customers

where (cust.Balance <= -50) &&

(today.Subtract(cust.DueDate).TotalDays > 30)

select cust;

overdueListBox.DataSource = overdueQuery.ToList<Customer>();

Unfortunately, SQL Server doesn't have a function that subtracts one date from another, so this query doesn't translate perfectly into database commands and at run time the program throws the following exception:

Method 'System.TimeSpan Subtract(System.DateTime)' has no supported

translation to SQL.

One solution is to rewrite the query in terms that the database can understand. The following code shows a query that LINQ to SQL can translate successfully:

// Display customers who owe at least $50

// and are overdue at least 30 days.

var overdueQuery =

from Customer cust in db.Customers

where (cust.Balance == null || cust.Balance.Value < -50) &&

(cust.DueDate == null || today > cust.DueDate.Value.AddDays(30))

//orderby cust.Balance ascending, cust.FirstName

select String.Format("{0} {1}\t{2:C}\t{3:d}",

cust.FirstName, cust.LastName,

cust.Balance, cust.DueDate);

overdueListBox.DataSource = overdueQuery.ToArray();

Although the translation to database code doesn't know how to subtract dates, it does know how to add days to a date so this query uses that capability. It selects records where the customer:

· Has no balance or has a balance less than $50, and

· Has no due date or today's date is greater than the due date plus 30 days

Using LINQ to SQL with Access

LINQ to SQL is intended to let you use objects to manage SQL Server databases, but with a little extra work you can also use it to manage other kinds of databases.

NOTE

Why would you want to use LINQ to SQL to manage other kinds of databases? One reason is that the LINQ to SQL classes are convenient. They allow you to use fairly intuitive objects to manipulate the data.

Another reason for using LINQ to SQL classes with other databases is that it lets me give you examples in Microsoft Access databases. Though SQL Server is generally more powerful, you cannot use it without installing SQL Server (at least the Express Edition). The .NET Framework includes all of the classes you need to interact with an Access database so you can connect to one and use it without installing anything else.

To get started, create a normal Windows Forms project, open the Project menu, and select Add New Item. Select the LINQ to SQL Classes template as before, give the file a good name, and click Add.

If you were working with SQL Server, you would then drag tables from the Server Explorer onto the design surface to define the classes. Visual Studio won't let you drag tables from other kinds of databases onto the entity-relationship designer, but you can build the classes manually.

Click the Toolbox link on the Object Relational Designer to open the Toolbox shown in Figure 37.8 and use the tools it holds to build the classes.

Figure 37.8

To get the model to work, you need to set a few properties correctly. For a table class, set the Source property to the name of the table in the database. For example, if you want to represent the Customers table's records with Customer objects, then create a Customer class and set its Source property to Customers.

After you create a class, right-click it and select AddProperty to give the class properties. For each property, set:

· Primary Key—True if the field is part of the table's primary key

· Nullable—True if the database field allows nulls

· Server Data Type—The field's data type in the database (for example, VARCHAR(50) NOT NULL)

· Source—The name of the field in the database (probably the same as the property's name in the class)

· Type—The property's type in the class (for example, string)

After you build the model, Visual Studio generates the classes you need. Now you just need to add code to use them.

If you are using a Microsoft Access database, start by adding the following using directive at the top of the file:

using System.Data.OleDb;

The connection object that you need to open the database is defined in this namespace. (For other kinds of databases, you may need to use different database objects in other namespaces.)

Next build a database connection. The Linq To Access example program that is available in this lesson's download uses the following code to build its connection:

// Get the database's location.

string filename = Path.GetFullPath(

Application.StartupPath + @"\..\..\CustomerData.mdb");

// Connect to the database.

using (OleDbConnection conn = new OleDbConnection(

"Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + filename))

{

This program assumes the CustomerData.mdb database is located two directory levels above where the program is executing. This is true if the program is running from its bin\Debug directory and the database is stored with the code.

The program gets the location of the database file. It creates a new OleDbConnection object, passing its constructor a connect string that includes the location of the database file. (Connect strings for different kinds of databases hold different fields. If you're using some other kind of database, you'll need to build an appropriate connect string.)

Having connected to the database, the program should create an instance of the LINQ to SQL database class, passing its constructor the database connection. The Linq To Access example program uses the following code:

// Get the database.

using (CustomerClassesDataContext db =

new CustomerClassesDataContext(conn))

{

From this point on, the code is the same as it is for working with SQL Server. The only complication is that not all databases are created equal. Different databases may provide different features and the automatically generated database code may not work properly for all databases.

The Linq To Access example program executes the same queries as the Find Customers example and has no trouble until the final query, which adds 30 days to the customer's due date. Access cannot understand the automatically generated code for that query and throws an exception.

In this case, you can fix the query by subtracting 30 days from the current date and seeing if the result is after the customer's due date, as shown in the following code:

DateTime todayMinus30 = today.Subtract(new TimeSpan(30, 0, 0, 0));

var overdueQuery =

from Customer cust in db.Customers

where (cust.Balance == null || cust.Balance.Value < -50) &&

(cust.DueDate == null || todayMinus30 > cust.DueDate.Value)

//orderby cust.Balance ascending, cust.FirstName

select String.Format("{0} {1}\t{2:C}\t{3:d}",

cust.FirstName, cust.LastName,

cust.Balance, cust.DueDate);

overdueListBox.DataSource = overdueQuery.ToArray();

Try It

In this Try It, you extend the Find Customers program to find customers that are missing data. You add a new ListBox to display customers that are missing first name, last name, balance, or due date values.

Lesson Requirements

In this lesson, you:

· Copy the Find Customers program available in this lesson's download. Add a new ListBox to hold customers with missing data.

· Use a LINQ to SQL query to display customers that have missing values.

NOTE

You can download the code and resources for this lesson from the website at www.wrox.com/go/csharp24hourtrainer2e.

Hints

· Remember that a blank string (a string with no characters) is not the same as a null value. You don't need to check the FirstName and LastName fields for null values, but you should check them for blank values.

Step-by-Step

· Copy the Find Customers program available in this lesson's download. Add a new ListBox to hold customers with missing data.

1. This is straightforward.

· Use a LINQ to SQL query to display customers that have missing values.

1. You can use code similar to the following:

2. // List customers with missing data.

3. var missingDataQuery =

4. from Customer cust in db.Customers

5. where (cust.FirstName == "" ||

6. cust.LastName == "" ||

7. cust.Balance == null ||

8. cust.DueDate == null)

9. select String.Format("{0} {1}\t{2:C}\t{3:d}",

10. cust.FirstName, cust.LastName,

11. cust.Balance, cust.DueDate);

missingDataListBox.DataSource = missingDataQuery.ToArray();

Exercises

For these exercises, use the customer database built by the Build Customer Database program. (If you don't want to install SQL Server, you can use the Access database CustomerData.mdb included in the Linq To Access example program in this lesson's download.)

1. Build the user interface shown in Figure 37.9. Make the First and Last Name TextBoxes read-only. (Don't worry about the data yet. Just build the user interface.)

Figure 37.9

2. Copy the program you built for Exercise 1 and make it display the list of customers. To do that:

a. Add LINQ to SQL classes to the program.

b. Override the Customer class's ToString method so it displays the customer's name. Instead of modifying the automatically generated Customer class, however, add a new class named Customer. Modify the class definition as follows:

c. // Add a ToString override to Customer.

d. public partial class Customer

e. {

f. public override string ToString()

g. {

h. return FirstName + " " + LastName;

i. }

}

The partial keyword indicates that this class is part of a class that may have pieces elsewhere. In this case, it means the ToString method should be added to the Customer class built by LINQ to SQL so you don't need to modify the automatically generated code.

j. Declare a field named Db with your DataContext class's type.

k. Write a LoadData method that queries the database and sets the ListBox's DataSource property to the result.

l. In the form's Load event handler, initialize the Db variable and call LoadData.

3. Copy the program you built for Exercise 2 and make it display the currently selected customer's properties. To do that:

a. Write a ShowSelectedCustomer method. It should get the ListBox's SelectedItem property as a Customer object. It should then display the object's properties in the TextBoxes.

b. In the ListBox's SelectedIndexChanged event handler, call ShowSelectedCustomer.

4. Copy the program you built for Exercise 3 and make it update the Customer objects when the user modifies the balance or due date. To do that, give the TextBoxes TextChanged event handlers. They should get the current Customer object, parse the value in the TextBox, and save the value in the Customer object. Use a try catch statement to protect against invalid data and, if a value is invalid, store null in the object.

5. Copy the program you built for Exercise 4 and finish it by making the Save and Cancel buttons work. To do that:

a. Make the Save button call Db.SubmitChanges. That saves any changes pending in the DataContext back to the SQL Server database.

b. Make the Cancel button execute the following statement to cancel any changes pending in the DataContext:

c. Db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues,

customerListBox.Items);

Then make the button's event handler call ShowSelectedCustomer to redisplay the currently selected customer.

NOTE

Please select the videos for Lesson 37 online at www.wrox.com/go/csharp24hourtrainer2evideos.