Export (0) Print
Expand All
1 out of 2 rated this helpful - Rate this topic

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.

LINQ to SQL and ADO.NET

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.

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:

db.Connection.Close(); 

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.

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.

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:

            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.

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

            IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
    "select contactname from customers where city = {0}",
    "London"
);
Note 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).

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.