Section 5.3. Create, Read, Update, and Delete with LINQ


5.3. Create, Read, Update, and Delete with LINQ

Most of the discussion thus far has focused on data querying, and not data modification. Don't be mistaken, LINQ provides full support for read/write data access, commonly referred to as CRUD.

Interestingly, while data is read using an SQL-like query language, data modification is approached using more traditional, object-oriented mechanisms. For example, to schedule the doctor mbl for call on November 30, 2006 in our SchedulingDocs database, we do two things. First, we add a new row to the object representing the Calls table:

 db.Calls.Add( new Databases.Calls{Initials="mbl",                                   DateOfCall=new DateTime(2006, 11, 30} ); 

Second, we flush the change back to the database:

 db.SubmitChanges(); 

The first step makes a local, in-memory change only; the second step is what triggers the underlying SQL (or stored procedure) to update the database. LINQ to SQL will automatically generate the appropriate SQL for inserts, updates, and deletes, or interoperate with your custom stored procedures.

To delete a call, we find the corresponding object, remove it from the table, and update the database:

 var del = from c in db.Calls           where c.Initials == "mbl" && c.DateOfCall == new DateTime(2006, 11, 30)           select c; foreach (var c in del)   db.Calls.Remove(c); db.SubmitChanges(); 

Since there is at most one doctor on call for any given day, we know the above query will return exactly one record. In this case we can use the standard query operator Single, passing a lambda expression for the search criteria:

 var call = db.Calls.Single( c => c.Initials == "mbl" &&                                  c.DateOfCall == new DateTime(2006, 11, 30) ); db.Calls.Remove(call); db.SubmitChanges(); 

If it's possible that the search may fail, use the query operator SingleOrDefault, and check the query result for null.

Finally, to update existing data, the approach is (1) query to find the corresponding objects, (2) update those objects, and (3) flush the changes. For example, if the doctor ay's pager number changes to 53301, we update the database as follows:

 var ay = db.Doctors.Single( d => d.Initials == "ay" ); ay.PagerNumber = 53301; db.SubmitChanges(); 

The same logic applies to other LINQ scenarios, such as XML documents and DataSets. For example, with the typed DataSet SchedulingDocs (see Figure 2), scheduling a doctor on call is simply a matter of adding a new row to the Calls table:

 ds.Calls.AddCallsRow( "mbl", new DateTime(2006, 11, 30) ); 

Much like the database objects, DataSets are a local, in-memory collection of objects. To persist your changes, an updated DataSet must be written to some durable medium, such as the file system or a database:

 dbConn.Open(); callsAdapter.Update( ds.Calls ); dbConn.Close(); 

Here we re-open the connection, update the database to match, and close the connectionthe equivalent of db.SubmitChanges(). The key difference is that in the case of LINQ to SQL, the SQLMetal tool generates the necessary code to update the underlying database. In the case of DataSets and XML documents (and other flavors of LINQ), it's typically our responsibility to load the data, and consequently to persist it back.



LINQ[c] The Future of Data Access in C# 3. 0
LINQ[c] The Future of Data Access in C# 3. 0
ISBN: N/A
EAN: N/A
Year: 2006
Pages: 25

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net