Although using SQL-like syntax with your in-memory collections is the more interesting and unusual use of LINQ, it’s natural to use the SQL-like syntax with SQL databases. With LINQ, instead of using the DataAdapter
and DataSet
classes you learned about in Chapter 20, you can treat the tables in a SQL database as classes, and work with the data directly, as though the tables were objects created in your code.
Create a new console application to see how this works. To use the LINQ data features, you’ll need to add a reference to the System.Data.Linq
namespace, which is something you haven’t done before, but it’s simple. Right-click on the References folder of your project in the Solution Explorer. You’ll see the Add Reference dialog shown in Figure 21-1.
Figure 21-1. You’ll need to add a reference to the System.Data.Linq namespace before you can use LINQ with a SQL database.
Now that you have the reference, you need to add some using
statements to take advantage of them in your program:
using System.Data; using System.Data.Linq; using System.Data.Linq.Mapping;
As we mentioned earlier, when you’re using LINQ and SQL, you can treat the database tables as classes, and the columns as members. It just requires a bit of extra work on your part. You’ll retrieve some simple information from Northwind’s Employees
table in this example.
If you did the examples in Chapter 20, you should already have the Northwind database installed and attached on your machine. If not, turn back to Chapter 20 for detailed instructions.
Create the following class in your application:
[Table(Name = "Employees")] public class Employee { [Column] public int EmployeeID { get; set; } [Column] public string FirstName { get; set; } [Column] public string LastName { get; set; } }
You’ve probably noticed the extra code in square brackets that’s unfamiliar to you. These are called attributes, and they’re used in a lot of different places in C# to provide extra modifiers to your classes. In this case, you’re using the Table
attribute to indicate that this class is drawn from a table, specifically the Employees
table in the associated database. Each of the public properties in the class has a Column
attribute to indicate that the property is associated with a column in the table.
As you probably remember from Chapter 20, you always need a data context before you can work with a database. With LINQ, creating the data context is much easier. Add the following line to Main( )
:
DataContext db = new DataContext("Data Source = .\SQLExpress;" + "Initial Catalog=Northwind;Integrated Security=True");
Notice that the DataContext
object’s constructor takes a string as its parameter—the same connection string that you used to connect to the database in Chapter 20.
The DataContext
object has an important method, GetTable( )
, which is how you retrieve the data table from the database and assign it to a generic Table
collection. The collection holds the type of objects that you defined earlier in the application. So, add this Table<Employee>
declaration to your application (after the DataContext
line):
Table<Employee> employees = db.GetTable<Employee>( );
That’s all you need to retrieve the data. Now, though, you’ll need a query. For this example, you’ll simply retrieve all the employee rows in the table. That’s where the LINQ syntax you saw earlier in this chapter comes in. You don’t need to create a query string; just use the LINQ syntax directly:
var dbQuery = from emp in employees select emp;
Although dbQuery
is declared using the var
keyword, it returns an IEnumerable
collection, which means that you can iterate over it with a foreach
loop, just like you would any other collection. Add this code to output some of the data:
foreach (var employee in dbQuery) { Console.WriteLine("{0} {1} {2}", employee.EmployeeID, employee.FirstName, employee.LastName); }
Simple, right? No more worrying about DataSet
or DataRow
objects. Example 21-6 shows the complete code for this example.
Example 21-6. You can access a SQL database with LINQ by simply treating the tables and columns in the database as though they were objects in your code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Linq; using System.Data.Linq.Mapping; namespace Example_21_6_ _ _ _LINQ_and_SQL { [Table(Name = "Employees")] public class Employee { [Column] public int EmployeeID { get; set; } [Column] public string FirstName { get; set; } [Column] public string LastName { get; set; } } class Program { static void Main( ) { DataContext db = new DataContext("Data Source = .\SQLExpress; Initial Catalog=Northwind;Integrated Security=True"); Table<Employee> employees = db.GetTable<Employee>( ); var dbQuery = from emp in employees select emp; foreach (var employee in dbQuery) { Console.WriteLine("{0} {1} {2}", employee.EmployeeID, employee.FirstName, employee.LastName); } } } }
1 Nancy Davolio 2 Andrew Fuller 3 Janet Leverling 4 Margaret Peacock 5 Steven Buchanan 6 Michael Suyama 7 Robert King 8 Laura Callahan 9 Anne Dodsworth