Holy crud. The launch is approaching, and I haven't reviewed the features and samples in the 101 * 4 demos in the SampleQueries application.
If I want to have any hope of finishing, I need to do this quite a bit more quickly.
Next on the list of 101+ Linq to SQL samples are ones that show that you can use SQL statements in a LINQ query:
var products = db.ExecuteQuery<Product>(
"SELECT [Product List].ProductID, [Product List].ProductName " +
"FROM Products AS [Product List] " +
"WHERE [Product List].Discontinued = 0 " +
"ORDER BY [Product List].ProductName;"
);
I'm not a fan of this syntax, except as a transitional strategy for moving applications to Linq. The query is a text string. None of the parameters are typed, and if you goofed, and typed db.ExecuteQuery<Customer> instead of db.ExecuteQuery<Product>, you'd have a runtime exception on your hands. That's not fun. That's what Linq is supposed to help you avoid: you should be able to work with types the compiler understands, not strings.
You can also execute commands directly:
db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");
That has the same problem. The command is text, not code. It's a string, there's no security, no intelligence, no type checking.
There are other interesting strategies that you can use to migrate code from existing applications. The next two samples show how you can use the database connection from an existing application in Linq.
SqlConnection nwindConn = new
SqlConnection(connString);
nwindConn.Open();
// ... other ADO.NET database access code ... //
// Use pre-existing ADO.NET connection to create DataContext:
Northwind interop_db = new
Northwind(nwindConn) { Log = db.Log };
var orders =
from o in interop_db.Orders
where o.Freight > 500.00M
select o;
nwindConn.Close();
Notice that this sample opens a new connection, and queries against that connection. You specify the data connection as part of the data source in your query. That's all it is. If you construct the data context object using an existing connection, that's the connection you'll be working with.
The next sample shows how you can use old style (it feels funny calling .NET 2.0 old-style, but it is) database transactions to wrap your Linq enabled commands:
var q =
from p in db.Products
where p.ProductID == 3
select p;
// Create a standard ADO.NET connection:
SqlConnection nwindConn = new
SqlConnection(connString);
nwindConn.Open();
// Use pre-existing ADO.NET connection to create DataContext:
Northwind interop_db = new
Northwind(nwindConn) { Log = db.Log };
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();
// Share pre-existing ADO.NET transaction:
//interop_db.LocalTransaction = nwindTxn;
interop_db.Transaction = nwindTxn;
Product prod1 = interop_db.Products.First(p => p.ProductID == 4);
prod1.UnitsInStock -= 3;
interop_db.SubmitChanges();
nwindTxn.Commit();
}
catch (Exception e) {
// If there is a transaction error, all changes are rolled back,
// including any changes made directly through the ADO.NET connection
Console.WriteLine(e.Message);
Console.WriteLine("Error submitting changes... all changes rolled back.");
}
nwindConn.Close();
It's just a matter of executing the commands you want to execute within the confines of a SqlTransaction. What I think is really cool is that this code demonstrates that you can mix Linq commands with old style commands.
This post shows you some of the ways that you can mix existing DB access code with Linq queries. That will make it easier for you to migrate your existing application to Linq and support the codebase during the transition. That's going to be important for many customers.