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.
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.
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()
You can always access the connection and close it yourself by using the Connection property, as in the following code:
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.
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:
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:
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).