In my talk on using LINQ to SQL in Web Apps, I wanted to use an ASP.NET GridView control to edit, not just display, data. The "web" sample that came along with the May CTP has a very complex LinqDataSource class that is probably a map for something that will be part of Orcas (note Scott Guthrie's post where he says that yes, there will be a LINQDataSource). So at the point where you decide you want to connect to a datasource and the options currently are Object, SQL, XML, there may be LINQ as another option there. However, I didn't want to use their sample class as it is a LOT of code to display in a session.
Instead I created a simple ObjectDataSource that I can use with a GridView. Currently it supports selects, paging, updates (just a few choice fields to get the picture across) and deletes. Since a gridview doesn't inherently support inserting (though you can easily find workarounds for that) I didn't bother with inserts for now. More than anything, it is a proof of concept, that it can be done.
I began by looking over the Creating an ObjectDataSource Control Source MSDN topic and the attached example.
I'm using a basic LINQ class created from the Northwind database and created the ObjectDataSource for Suppliers.
The key methods I created were a select, a select that supports paging, an update and a delete method.
The selects return a generic BindingList of type Supplier. Supplier is a class created in the NorthwindDataContext class. My simplistic query just returns a collection of supplier objects. Although I have the LINQ attributes on these methods, it is still necessary to explicitly bind to the methods when you create the ObjectDataSource control on the web page. The first method is used when paging is not supported in the GridView. The second method is overloaded with the parameters expected when the GridView supports paging. These values are automatically passed in.
_
Public Function GetAllSuppliers() As System.ComponentModel.BindingList(Of Supplier)
Dim q = From s In db.Suppliers _
Select s
Return q.ToBindingList(Of LinqSQLFirstTest.Supplier)()
End Function
_
Public Function GetAllSuppliers(ByVal maximumRows As Int32, _
ByVal startRowIndex As Int32) As System.ComponentModel.BindingList(Of Supplier)
Dim q = (From s In db.Suppliers _
Select s).Skip(startRowIndex).Take(maximumRows)
Return q.ToBindingList(Of LinqSQLFirstTest.Supplier)()
End Function
Next I have an update method. By default, LINQ objects use Optimistic Concurrency on all fields. I turned that off for the Home Page field, but left the rest in tact. That means I need to have the original values of the supplier object in order to do the update against the database. My update method is trimmeed down, as it only will update the CompanyName and Address field. Therefore my parameters are each property of Supplier and then an old_* version of each property. The “old_“ prefix is set in a property of the object datasource. You can set the prefix to be whatever you want, but just make sure the signature matches.
The interesting thing that is happening here is the “Attach“ method. It enables you to update LINQ objects on a different DataContext than they were created on - in other words: disconnected data. I create a new datacontext and a new supplier, then I populate the supplier object with the ApplySupplierValues method. Next, I Attach the supplier to the DataContext. This becomes the starting point for the supplier's state. Anything that changes after this will be tracked and pushed to the database with SubmitChanges. I think apply the new values to some of the Supplier properties and call Submit changes.
_
Public Sub UpdateSupplier(ByVal Address As String, ByVal ContactTitle As String, ByVal Phone As String, ByVal SupplierID As Int32, ByVal HomePage As String, ByVal Fax As String, ByVal Country As String, ByVal PostalCode As String, ByVal City As String, ByVal CompanyName As String, ByVal Region As String, ByVal ContactName As String, _
ByVal old_Address As String, ByVal old_ContactTitle As String, ByVal old_Phone As String, ByVal old_SupplierID As Int32, ByVal old_HomePage As String, ByVal old_Fax As String, ByVal old_Country As String, ByVal old_PostalCode As String, ByVal old_City As String, ByVal old_CompanyName As String, ByVal old_Region As String, ByVal old_ContactName As String)
Dim db2 = New LinqSQLFirstTest.northwindDataContext
Dim su As New Supplier
ApplySupplierValues(su, old_Address, old_ContactTitle, old_Phone, old_SupplierID, old_HomePage, old_Fax, old_Country, old_PostalCode, old_City, old_CompanyName, old_Region, old_ContactName)
db2.Suppliers.Attach(su)
su.CompanyName = CompanyName
su.Address = Address
db2.SubmitChanges()
End Sub
Public Sub ApplySupplierValues(ByRef supp As Supplier, ByVal Address As String, ByVal ContactTitle As String, ByVal Phone As String, ByVal SupplierID As Int32, ByVal HomePage As String, ByVal Fax As String, ByVal Country As String, ByVal PostalCode As String, ByVal City As String, ByVal CompanyName As String, ByVal Region As String, ByVal ContactName As String)
With supp
.Address = Address
.CompanyName = CompanyName
.City = City
.ContactName = ContactName
.ContactTitle = ContactTitle
.Country = Country
.Fax = Fax
.HomePage = HomePage
.Phone = Phone
.PostalCode = PostalCode
.Region = Region
.SupplierID = SupplierID
End With
End Sub
Now for a Delete method. Again, to satisfy the default optimistic concurrency, I must deal with all of the original values of the supplier. The ObjectDataSource will, by default, pass in all of the current values and all of the old values. I modified the DeleteParameters of the ObjectDataSource Control to only pass in SupplierID and frankly, that's coming over as 0 still. This is because I am still learning how to create ObjectDataSource classes and has nothing to do with LINQ. So, remember, this is just a proof of concept and at this stage it's working and I need to fine tune it still, but didn't want to keep it a secret until I found the time to work it out. :-) I'm not even using the SupplierID but creating a supplier object from the old_ * properties. Then I Remove that object from the datacontext and call SubmitChanges. Remember that constraints will be checked so you will want some exception handling for that case or even test for it in advance, though that would mean an extra hit to the db which I'd rather not do ...therefore I go with the exception handling. Or, of course you can protect users at the business layer for this. But I digress. THese are not LINQ problems, but typical data access issues. In the case of a default NorthwindDataContext, you won't be able to delete suppliers with products.
_
Public Sub DeleteSupplier(ByVal SupplierID As Int32, ByVal old_Address As String, ByVal old_ContactTitle As String, ByVal old_Phone As String, ByVal old_SupplierID As Int32, ByVal old_HomePage As String, ByVal old_Fax As String, ByVal old_Country As String, ByVal old_PostalCode As String, ByVal old_City As String, ByVal old_CompanyName As String, ByVal old_Region As String, ByVal old_ContactName As String)
Dim db2 = New LinqSQLFirstTest.northwindDataContext
Dim su As New Supplier
ApplySupplierValues(su, old_Address, old_ContactTitle, old_Phone, old_SupplierID, old_HomePage, old_Fax, old_Country, old_PostalCode, old_City, old_CompanyName, old_Region, old_ContactName)
db2.Suppliers.Remove(su)
Try
db2.SubmitChanges()
Catch ex As Exception
'exceptions are currenlty system.exception
'test for:
'ex.Message.Contains("REFERENCE constraint")
Throw ex
End Try
End Sub
This of course is not a complete ObjectDataSource class, but you can use these parts to plug into a more typical one (e.g. the example in the MSDN topic linked to above).
With this in hand, I created a GridView on a web page and created and ObjectDataSource control that points to my class. I mapped the Update, Select and Delete commands to my methods, turned on paging, and did all of the other typical things necessary for using an ObjectDataSource with a GridView.
But once that was done, I was able to run the page, edit and update the rows and delete rows. All with LINQ for SQL.
Hopefully with all of my copying and pasting from a VPC, to the host computer then over to another computer, all of the code is still in tact!