ADO.NET and LINQ to SQL

ADO.NET and LINQ to SQL

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Microsoft Visual Studio 2010/.NET Framework 4.0

LINQ to SQL

ADO.NET and LINQ to SQL

LINQ to SQL is part of the ADO.NET family of technologies. It is based on services provided by the ADO.NET provider model. You can therefore mix LINQ to SQL code with existing ADO.NET applications and migrate current ADO.NET solutions to LINQ to SQL. The following illustration provides a high-level view of the relationship.

Connections

You can supply an existing ADO.NET connection when you create a LINQ to SQL DataContext. All operations against the DataContext (including queries) use this provided connection. If the connection is already open, LINQ to SQL leaves it as is when you are finished with it.

Visual Basic

Dim conString = "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\northwind.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim northwindCon = New SqlConnection(conString)
northwindCon.Open()

Dim db = New Northwnd("...")
Dim northwindTransaction = northwindCon.BeginTransaction()

Try
    Dim cmd = New SqlCommand( _
            "UPDATE Products SET QuantityPerUnit = 'single item' " & _
            "WHERE ProductID = 3")
    cmd.Connection = northwindCon
    cmd.Transaction = northwindTransaction
    cmd.ExecuteNonQuery()

    db.Transaction = northwindTransaction

    Dim prod1 = (From prod In db.Products _
  Where prod.ProductID = 4).First
    Dim prod2 = (From prod In db.Products _
  Where prod.ProductID = 5).First
    prod1.UnitsInStock -= 3
    prod2.UnitsInStock -= 5

    db.SubmitChanges()

    northwindTransaction.Commit()

Catch e As Exception

    Console.WriteLine(e.Message)
    Console.WriteLine("Error submitting changes... " & _
"all changes rolled back.")
End Try

northwindCon.Close()

string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\northwind.mdf;
    Integrated Security=True; Connect Timeout=30; User Instance=True";
SqlConnection nwindConn = new SqlConnection(connString);
nwindConn.Open();

Northwnd interop_db = new Northwnd(nwindConn);

SqlTransaction nwindTxn = nwindConn.BeginTransaction();

try
{
    SqlCommand cmd = new SqlCommand(
        "UPDATE Products SET QuantityPerUnit = 'single item' WHERE ProductID = 3");
    cmd.Connection = nwindConn;
    cmd.Transaction = nwindTxn;
    cmd.ExecuteNonQuery();

    interop_db.Transaction = nwindTxn;

    Product prod1 = interop_db.Products
        .First(p => p.ProductID == 4);
    Product prod2 = interop_db.Products
        .First(p => p.ProductID == 5);
    prod1.UnitsInStock -= 3;
    prod2.UnitsInStock -= 5;

    interop_db.SubmitChanges();

    nwindTxn.Commit();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    Console.WriteLine("Error submitting changes... all changes rolled back.");
}

nwindConn.Close();

You can always access the connection and close it yourself by using the Connection property, as in the following code:

Visual Basic

db.Connection.Close()

db.Connection.Close();

Transactions

You can supply your DataContext with your own database transaction when your application has already initiated the transaction and you want your DataContext to be involved.

The preferred method of doing transactions with the .NET Framework is to use the TransactionScope object. By using this approach, you can make distributed transactions that work across databases and other memory-resident resource managers. Transaction scopes require few resources to start. They promote themselves to distributed transactions only when there are multiple connections within the scope of the transaction.

Visual Basic

Using ts As New TransactionScope()
    db.SubmitChanges()
    ts.Complete()
End Using

using (TransactionScope ts = new TransactionScope())
{
    db.SubmitChanges();
    ts.Complete();
}

You cannot use this approach for all databases. For example, the SqlClient connection cannot promote system transactions when it works against a SQL Server 2000 server. Instead, it automatically enlists to a full, distributed transaction whenever it sees a transaction scope being used.

Direct SQL Commands

At times you can encounter situations where the ability of the DataContext to query or submit changes is insufficient for the specialized task you want to perform. In these circumstances you can use the ExecuteQuery method to issue SQL commands to the database and convert the query results to objects.

For example, assume that the data for the Customer class is spread over two tables (customer1 and customer2). The following query returns a sequence of Customer objects:

Visual Basic

    Dim results As IEnumerable(Of Customer) = _
db.ExecuteQuery(Of Customer)( _
"SELECT [c1].custID as CustomerID," & _
    "[c2].custName as ContactName" & _
    "FROM customer1 AS [c1], customer2 as [c2]" & _
    "WHERE [c1].custid = [c2].custid")

            IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
    @"select c1.custid as CustomerID, c2.custName as ContactName
        from customer1 as c1, customer2 as c2
        where c1.custid = c2.custid"
);

As long as the column names in the tabular results match column properties of your entity class, LINQ to SQL creates your objects out of any SQL query.

Parameters

The ExecuteQuery method accepts parameters. The following code executes a parameterized query:

Visual Basic

    Dim results As IEnumerable(Of Customer) = _
db.ExecuteQuery(Of Customer)( _
    "SELECT contactname FROM customers WHERE city = {0}, 'London'")
End Sub

            IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
    "select contactname from customers where city = {0}",
    "London"
);

Note:
Parameters are expressed in the query text by using the same curly notation used by Console.WriteLine() and String.Format(). String.Format() takes the query string you provide and substitutes the curly-braced parameters with generated parameter names such as @p0, @p1 …, @p(n).

Tasks

How to: Reuse a Connection Between an ADO.NET Command and a DataContext (LINQ to SQL)

Other Resources

Background Information (LINQ to SQL)