How to: Connect to a Database

The DataContext is the main conduit by which you connect to a database, retrieve objects from it, and submit changes back to it. You use the DataContext just as you would use an ADO.NET SqlConnection. In fact, the DataContext is initialized with a connection or connection string that you supply. For more information, see DataContext Methods (O/R Designer).

The purpose of the DataContext is to translate your requests for objects into SQL queries to be made against the database, and then to assemble objects out of the results. The DataContext enables Language-Integrated Query (LINQ) by implementing the same operator pattern as the Standard Query Operators, such as Where and Select.

Example 1

In the following example, the DataContext is used to connect to the Northwind sample database and to retrieve rows of customers whose city is London.

// Do not use System.Data.SqlClient for new development; use Microsoft.Data.SqlClient.
// LINQ to SQL's built-in provider requires System.Data.SqlClient.
// DataContext takes a connection string.
DataContext db = new DataContext(@"c:\Northwind.mdf");

// Get a typed table to run queries.
Table<Customer> Customers = db.GetTable<Customer>();

// Query for customers from London.
var query =
    from cust in Customers
    where cust.City == "London"
    select cust;

foreach (var cust in query)
    Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);

' Do not use System.Data.SqlClient for new development; use Microsoft.Data.SqlClient.
' LINQ to SQL's built-in provider requires System.Data.SqlClient.
' DataContext takes a connection string.
Dim db As New DataContext("…\Northwind.mdf")

' Get a typed table to run queries.
Dim Customers As Table(Of Customer) = db.GetTable(Of Customer)()

' Query for customer from London.
Dim Query = _
    From cust In Customers _
    Where cust.City = "London" _
    Select cust

For Each cust In Query
    Console.WriteLine("id=" & cust.CustomerID & _
        ", City=" & cust.City)
Next

Each database table is represented as a Table collection available by way of the GetTable method, by using the entity class to identify it.

Example 2

Best practice is to declare a strongly typed DataContext instead of relying on the basic DataContext class and the GetTable method. A strongly typed DataContext declares all Table collections as members of the context, as in the following example.

// Do not use System.Data.SqlClient for new development; use Microsoft.Data.SqlClient.
// LINQ to SQL's built-in provider requires System.Data.SqlClient.
public partial class Northwind : DataContext
{
    public Table<Customer> Customers;
    public Table<Order> Orders;
    public Northwind(string connection) : base(connection) { }
}

' Do not use System.Data.SqlClient for new development; use Microsoft.Data.SqlClient.
' LINQ to SQL's built-in provider requires System.Data.SqlClient.
Partial Public Class Northwind
    Inherits DataContext
    Public Customers As Table(Of Customer)
    Public Orders As Table(Of Order)
    Public Sub New(ByVal connection As String)
        MyBase.New(connection)
    End Sub
End Class

You can then express the query for customers from London more simply as:

// Do not use System.Data.SqlClient for new development; use Microsoft.Data.SqlClient.
// LINQ to SQL's built-in provider requires System.Data.SqlClient.
Northwnd db = new Northwnd(@"c:\Northwnd.mdf");
var query =
    from cust in db.Customers
    where cust.City == "London"
    select cust;
foreach (var cust in query)
    Console.WriteLine("id = {0}, City = {1}", cust.CustomerID,
        cust.City);

' Do not use System.Data.SqlClient for new development; use Microsoft.Data.SqlClient.
' LINQ to SQL's built-in provider requires System.Data.SqlClient.
Dim db As New Northwind("...\Northwnd.mdf")

Dim query = _
    From cust In db.Customers _
    Where cust.City = "London" _
    Select cust

For Each cust In query
    Console.WriteLine("id=" & cust.CustomerID & _
        ", City=" & cust.City)
Next

See also

• Communicating with the Database