Beth Massi - Sharing the goodness that is VB : Article, Winforms

Using TableAdapters to Insert Related Data into an MS Access Database

Beth Massi — Fri, 15 May 2009 03:48:00 GMT

I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships against SQL server. It’s pretty straightforward and Visual Studio generates all the code for you to properly insert, update and delete your data. However if you’re using MS Access then there’s one thing that Visual Studio doesn’t do because it’s not supported when using Access.

How Parent-Child Inserts Work

When Visual Studio generates the insert commands on a SQL-Server TableAdapter it looks to see if the table’s primary keys are auto-generated (identity columns) and if so, Visual Studio will write an additional statement to retrieve the key using the SCOPE_IDENTITY functionality of SQL Server. When in the DataSet designer, if you look at the insert statement in the properties window for the SQLTableAdapter you will see two statements separated by a semi-colon:

INSERT INTO [dbo].[Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = SCOPE_IDENTITY())

SQL Server supports batch statements through ADO.NET commands so this will populate the primary key back in the DataRow inside the DataSet as each row is inserted into the database. If you are enforcing foreign key constraints with a parent-child relation set up on two DataTables and you set the Update Rule to Cascade then any foreign key references will also be updated in the children. Because the TableAdapterManager will save the children after their parent records, when the child saves to the database it will contain the correct parent key which must already exist in the database before a child can be inserted in order to maintain referential integrity in the database.

Unfortunately Access doesn’t support batch statements. If you look at what is generated for Access you will only see one statement (also the OLEDB provider does not support named parameters hence the question mark placeholders):

INSERT INTO `Products` (`ProductName`, `SupplierID`, `CategoryID`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `ReorderLevel`, `Discontinued`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

So if you are doing inserts, especially for related parent-child data, you need a way to intercept the DataRow and set the primary key right after the row is inserted into the database and before any children are inserted. There’s an excellent article by Bill Vaughn (VB MVP) that explains this issue as well as a KB Article that shows how to solve it using the DataAdapter. These were written before Visual Studio had the concept of TableAdapters (which were added in VS 2008) so let’s see how we could use this technique to enhance our TableAdapters via partial classes.

Setting up the Parent-Child DataSet

The first step is to make sure you set up the tables in your Access database to use the AutoNumber feature for the primary keys on the rows. Here I’m using Access 2007 against the Northwind Access database. AutoNumber is used for both the primary keys on the Products and Categories tables:

Next you need to make sure you set up the relationship on the DataSet properly so that the primary key on the parent will cascade to the foreign key on the child. Set the relation in the DataSet designer to "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade. Just right-click on the relation and select "Edit Relation" in the DataSet designer:

Loading and Editing the Parent-Child DataSet

You now are telling the DataSet to enforce the foreign key relationship which means that you must have a parent for every child. This means you have to load the data in parent then child order. You also have to be careful with your queries. You have to make sure that every row in the child DataTable will have a corresponding parent row in the parent DataTable. This also means that you have to make sure to call EndEdit on any new parent BindingSource before any children can be added.

For example, from the Data Sources window drag the Categories parent table as details and the related child Products table as a DataGridView on the form and Visual Studio will generate the code to load and save our data.

Head over to the code behind and make sure that the parent is filled first before the child. Also make sure that EndEdit is called on the CategoriesBindingSource before a new product can be inserted into the DataGridView. EndEdit will flush the data row being edited by the controls into the DataTable. In this example I just am calling EndEdit on the CategoriesBindingSource when the user selects the grid.

Public Class Form1

    Private Sub CategoriesBindingNavigatorSaveItem_Click() _
            Handles CategoriesBindingNavigatorSaveItem.Click
        Me.Validate()
        'Call EndEdit on all BindingSources! 
        Me.CategoriesBindingSource.EndEdit()
        Me.ProductsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ProductsDataSet)
    End Sub

    Private Sub Form1_Load() Handles MyBase.Load
        'Load parent before child!
        Me.CategoriesTableAdapter.Fill(Me.ProductsDataSet.Categories)
        Me.ProductsTableAdapter.Fill(Me.ProductsDataSet.Products)
    End Sub

    Private Sub ProductsDataGridView_Enter() Handles ProductsDataGridView.Enter
        'You must commit the parent row to the DataTable before adding child rows 
        Me.CategoriesBindingSource.EndEdit()
    End Sub

End Class

Note that anytime you call EndEdit and flush the data to the DataTable, the row must not fail any constraints either (i.e. if NULLs aren’t being allowed then you have to set those values). One way to handle this is to add code to set default values in the TableNewRow handler on the DataTable.

Enhancing the TableAdapter Partial Classes

Now for the good stuff. Like I mentioned in the beginning, you need a way to set the primary key on the parent right after the row is inserted into the database and before any children are inserted. Now that we have keys cascading we just need to write code to handle the RowUpdated event on the DataAdapter inside the TableAdapter partial class. TableAdapters are generated classes that Visual Studio creates for us from the DataSet designer. These classes are declared as Partial Classes so that means we can add code to the same class even if it’s in a separate file. Right-click on the TableAdapter class in the DataSet Designer and select View Code and the partial class file that you can edit will be created for you.

Namespace ProductsDataSetTableAdapters

    Partial Public Class CategoriesTableAdapter

    End Class

    Partial Public Class ProductsTableAdapter

    End Class
End Namespace

In these classes we can handle the RowUpdated event on the private variable _adapter which gives us access to the ADO.NET DataAdapter that is executing the updates to our rows. The way we retrieve the primary key is by executing the statement SELECT @@IDENTITY which tells Access to send back the last primary key it used on the connection. Because you have to add this handler to all your TableAdapters that are working against MS Access, to make things more manageable you can create a class with a Shared (static) method to handle setting the key and then call that from the handlers.

Imports System.Data.OleDb

Public Class AccessIDHelper
    ''' <summary>
    ''' Retrieves the primary key autonumber values from Access
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, _
                                    ByVal e As OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue AndAlso _
           e.StatementType = StatementType.Insert Then
            ' If this is an INSERT operation...
            Dim pk = e.Row.Table.PrimaryKey
            ' and a primary key column exists...
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)
                ' Execute the post-update query to fetch new @@Identity
                e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                e.Row.AcceptChanges()
            End If
        End If
    End Sub
End Class

Namespace ProductsDataSetTableAdapters

    Partial Public Class CategoriesTableAdapter

        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class

    Partial Public Class ProductsTableAdapter

        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class
End Namespace

So that’s how you can get the primary keys into the data rows and have them properly cascaded to the child rows. So now when the children are updated they will have the correct foreign key and the parent will exist in the database. I hope this helps clear up how to work with Access and Visual Studio.

I’ve posted this example on CodeGallery so have a look.

Enjoy!

Tally Rows in a DataSet that Match a Condition

Beth Massi — Tue, 28 Apr 2009 05:24:21 GMT

Today I got a question that comes up often in data application programming about how to count rows in a DataSet that matched a condition. The DataSet may be bound to a DataGridView or other list control and it’s tempting to start looking at the control to see if you can coax it into returning what you need but usually there is a much better way.

For instance, say we have a table in our database called “Inbox” that has varchar fields Subject, From, and Status and we’d like to tally all the rows where the Status = “Unread”. Suppose we’ve also created a typed DataSet that contains this Inbox table. You create a data-bound Windows Form with a DataGridView on it by dragging the table from the Data sources window onto the form (like I showed in this video). When you do this, Visual Studio generates code that hooks up your DataGridView’s DataSource property to a BindingSource object which in turn has it’s DataSource set to your DataSet. This is a good thing. The BindingSource is a simple controller that provides currency between your DataSet and the DataGidView UI. (In WPF this is similar to the CollectionView object). You write code against the BindingSource instead so that it doesn’t matter what kind of control is being used to display the data.

BindingSource to the Rescue

The BindingSource in this case is actually working with a DataView, not the actual DataTable, which may seem confusing. This is needed for related data binding to work (see this video and this one). Since the BindingSource manages the currency (current row position) that is being displayed by the controls, in this example the BindingSource.List will return the DataView and the BindingSource.Current property will always return the DataRowView. You access your typed DataRow by casting the DataRowView.Row property. So to get a count of items in the DataView we could just simply ask the BindingSource for a count of it’s rows no matter what kind of control is being used for the display:

Dim count = Me.InboxBindingSource.Count

Or we could grab the DataView and ask for its count:

Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count = dv.Count

Once you have the DataView you can apply more filtering on it directly or we can loop through it to tally the rows where Status = “Unread”.

Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count = 0
For Each drv As DataRowView In dv
    Dim inboxRow = CType(drv.Row, EmailDataSet.InboxRow)
    If inboxRow.Status = "Unread" Then
        count += 1
    End If
Next

But the neat thing about using the BindingSource is that you always know the current row being displayed. So if we want to tally the rows based on a condition in the current row we go through the BindingSource. You can easily write the code that gets the current row as your typed data row by using a code snippet. Right-click in the editor, select Data – LINQ, XML, Designer, ADO.NET > Designer Features and ADO.NET > Converts BindingSource.Current to a specific row in a DataTable:

This code returns a tally of rows in the current view that have the same status as the selected row. Instead of looping through the data manually with For Each, this code sets the Filter property of the DataView instead:

Dim currentRow As EmailDataSet.InboxRow
currentRow = CType(CType(Me.InboxBindingSource.Current, DataRowView).Row, EmailDataSet.InboxRow)

Dim dv = CType(Me.InboxBindingSource.List, DataView)
dv.RowFilter = "Status ='" & currentRow.Status & "'"
Dim count = dv.Count

This technique causes any bound controls to update to the new filter. To remove the filter, set the Filter property to Nothing or the empty string. Although this code may work for a lot of scenarios, we may have a situation where we do not want to affect any of the bound controls by changing the filter. And even though this is a simple condition it would be nice not to have to manually write the loop ourselves. This is a perfect place to use LINQ instead.

LINQ to the Rescue

If we want to tally the entire DataTable for rows matching a condition we can use LINQ to DataSets with Visual Basic’s Aggregate clause. This doesn’t involve a BindingSource at all but be aware that the DataView that a BindingSource may be displaying data from may be filtered. In the case of this example the DataView contains the same rows as the DataTable (no filter) so we can write a simple Aggregate query to count the rows:

Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _
                       Where row.Status = "Unread" _
                       Into UnreadCount = Count()

This will return an integer indicating the number of rows in the entire DataTable that matched the Where clause. But what if we do want to use the BindingSource and take into account any filters that are being applied to the DataView in which controls are bound? In this case we can still use an Aggregate LINQ query it will just be against the DataView so we will need to cast a few things:

Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count As Integer = Aggregate row In dv.Cast(Of DataRowView).AsQueryable _
                       Where CType(row.Row, EmailDataSet.InboxRow).Status = "Unread" _
                       Into UnreadCount = Count()

Now what would be really nice is to automatically tally this information from the DataSet and display it in a label on the Form anytime the user made any changes to the rows. We can do this easily by handling the BindingSource’s ListChanged event and putting our Aggregate LINQ query in there:

Private Sub InboxBindingSource_ListChanged() Handles InboxBindingSource.ListChanged
    Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _
                           Where row.Status = "Unread" _
                           Into UnreadCount = Count()

    Me.lblStatus.Text = count.ToString() & " unread email."
End Sub

Now this will give us a “live” running tally and we’re not bound to the UI controls at all. Nice. For more information on Aggregate queries and DataSets check out this video.

More resources:

Enjoy!

Editing Data from Two Tables in a Single DataGridView

Beth Massi — Thu, 16 Oct 2008 04:31:00 GMT

I've had a lot of questions lately on how to display data from two separate tables in the database into a single DataGridView for editing. It sure would be nice if all our data was in a single table, but in reality most of the time it's not. Basically the problem is that we want one single table (entity) representation on the client even though we have two physical tables in the database holding the information... thus we need to "split" the data in our entity on the client side into two or more physical tables on the server. There are many ways you can do this depending on the relations in the database and also depending on what your client-side data source happens to be. I'll present a simple, common database table scenario and then attempt to explain how we can work with it using DataTables, LINQ to SQL classes, and then an Entity Data Model --- three different approaches to working with data in Visual Studio.

So let's take a very simple example. In my database I have two tables with a one-to-one relationship, Customer and CustomerContactInfo, one storing basic information about a customer and another that stores contact information:

Entity Splitting DataTables

If we're using DataSets in our application already we probably want to represent this as a single Customer client-side DataTable. To do this, we need to specify some stored procedures in our database for Select, Insert, Update, and Delete so that when ADO.NET retrieves our data or sends back the updated, inserted and deleted rows to the database, it calls our stored procedures that do the work of splitting the data into the proper physical tables. You can easily map DataTables in the DataSet designer to stored procedures. In our example these stored procedures are going to be very simple:

CREATE PROCEDURE [dbo].[GetCustomers] AS
SELECT  cust.CustomerID, 
        cust.Title, 
        cust.FirstName, 
        cust.MiddleName, 
        cust.LastName, 
        cust.Suffix, 
        cust.CompanyName, 
        cust.SalesPerson, 
        contact.EmailAddress, 
        contact.Phone
FROM    [dbo].[Customer] AS cust
JOIN    [dbo].[CustomerContactInfo] 
        AS contact ON cust.CustomerID = contact.CustomerID

CREATE PROCEDURE [dbo].[DeleteCustomer](
    @CustomerID [int]
) AS
BEGIN

    DELETE   [dbo].[CustomerContactInfo]
    WHERE    [CustomerID] = @CustomerID
    
    DELETE   [dbo].[Customer]
    WHERE    [CustomerID] = @CustomerID
    
END

CREATE PROCEDURE [dbo].[UpdateCustomer](
    @CustomerID [int],
    @Title [nvarchar](8),
    @FirstName [nvarchar](50),
    @MiddleName [nvarchar](50),
    @LastName [nvarchar](50),
    @Suffix [nvarchar](10),
    @CompanyName [nvarchar](128),
    @SalesPerson [nvarchar](256),
    @EmailAddress [nvarchar](50),
    @Phone [nvarchar](25)
) AS
BEGIN

    UPDATE  [dbo].[Customer]
    SET     [Title] = @Title,
            [FirstName] = @FirstName,
            [MiddleName] = @MiddleName,
            [LastName] = @LastName,
            [Suffix] = @Suffix,
            [CompanyName] = @CompanyName,
            [SalesPerson] = @SalesPerson
    WHERE   [CustomerID] = @CustomerID

    UPDATE  [dbo].[CustomerContactInfo]
    SET     [EmailAddress] = @EmailAddress,
            [Phone] = @Phone
    WHERE   [CustomerID] = @CustomerID
    
END

CREATE PROCEDURE [dbo].[InsertCustomer](
    @Title [nvarchar](8),
    @FirstName [nvarchar](50),
    @MiddleName [nvarchar](50),
    @LastName [nvarchar](50),
    @Suffix [nvarchar](10),
    @CompanyName [nvarchar](128),
    @SalesPerson [nvarchar](256),
    @EmailAddress [nvarchar](50),
    @Phone [nvarchar](25),
    @CustomerID int OUTPUT
) AS
BEGIN

    INSERT INTO [dbo].[Customer]
    VALUES (
        @Title,
        @FirstName,
        @MiddleName,
        @LastName,
        @Suffix,
        @CompanyName,
        @SalesPerson )
    
    -- Get back the customer ID 
    SELECT @customerID = CustomerID
    FROM [dbo].[Customer]
    WHERE @@ROWCOUNT > 0 AND [CustomerID] = scope_identity()

    INSERT INTO [dbo].[CustomerContactInfo]
    VALUES (
        @customerID,
        @EmailAddress,
        @Phone)
    
END

Now that we have that set up, open up the Data Source Window in Visual Studio and add a new data source, select the Database and then select just the GetCustomers stored procedure:

Click Finish and then open up the DataSet designer by double-clicking on the CustomerDataset.xsd in the Solution Explorer. You'll notice that the name of the DataTable is GetCustomers so change that to just "Customer". Next we need to configure the DataTable so that it will use our stored procedures so right click on the DataTable and select "Configure...". The TableAdapter Configuration Wizard should open and this will allow you to map your stored procedures to the Update, Insert and Delete commands. By default the fields will match up by name so you shouldn't have to do anything but drop down the combo boxes and select the right procedures for each action.

To test this out you can drag the Customer table from the Data Sources window on to a Windows Form as a DataGrid and immediately run it.

You will see the data from both tables displayed in the grid and they will be editable. As you update, insert and delete rows here, the DataSet will keep track of these changes for you. So when it is time to save the data via a call to the TableAdapterManager.UpdateAll method, the proper stored procedure will be called for each row that was modified.

Entity Splitting LINQ to SQL Classes

So with a Dataset, the DataTables do not have to map one-to-one with your database tables, but you need to specify how the data should be saved back to the database via stored procedures. This is also true if using LINQ to SQL classes. In that case though, you drag methods onto the method pane first and then map them to the class in the designer by right-clicking on the class and selecting "Configure Behavior...".

However with LINQ to SQL classes you can't map the Select behavior unfortunately. What you do instead is map the result of the GetCustomers method to a result type of Customer (the class we created on the design surface). Then when accessing the customers data you need to remember to call the GetCustomers method and not access the Customers directly in the DataContext, otherwise you'll get a SQLException that it cant find the additional columns when retrieving the data (in our case EmailAddress and Phone).

Private db As New MyDataContext

Private SubForm1_Load() Handles MyBase.Load     
    'This will properly populate a collection of our     
    ' Customer entities.    
    Me.CustomerBindingSource.DataSource = db.GetCustomers
End Sub

To save the data in a connected state like this we can simply call SubmitChanges on the DataContext. The LINQ to SQL DataContext tracks the state of each modification, deletion or insertion into the collection of Customers and will call the corresponding stored procedures that we configured.

Private Sub CustomerBindingNavigatorSaveItem_Click() _
   Handles CustomerBindingNavigatorSaveItem.Click
   Try
       db.SubmitChanges()

       MsgBox("saved")
   Catch ex As Exception
       MsgBox(ex.ToString)
   End Try
End Sub

(Note: In order to get get drag-drop data binding in Windows Forms to work with LINQ to SQL objects you need to select Data --> "Add New Data Source" on the main menu, then select Object (not database). Then select the Customer object and Finish. This will populate your Data Sources window so you can drag the grid onto a Windows form.)

Entity Splitting Using the Entity Data Model

This scenario becomes pretty straight-forward and automatic using the Entity Framework (EF) and you're not required to write any stored procedures to get it to work. This is because EF provides more complex mappings out of the box than LINQ to SQL or DataSets. And EF separates your database schema from your object model by providing a mapping layer. If you have Visual Studio 2008 Service Pack 1 you can create what's called an Entity Data Model which provides an ObjectContext, similar in theory to the LINQ to SQL DataContext above, but it provides many more mapping features.

When you add a new Entity Data Model to your project you can choose to generate it from the database or you can create an empty model. For this example I'll choose to generate it from the database and I'll select just the Customer and CustomerContactInfo tables and no stored procedures this time. (Note that I can still map stored procs to the update, insert and delete behaviors if I need to though.)

This looks just like our database model except you can see the navigation properties displayed that let us navigate from one entity to related ones. Notice the association is also shown as one-to-(none or)one. To set up the mapping so that it will automatically split the entity for us first we need to adjust the Customer by adding the EmailAddress and Phone. You can select the EmailAddress and Phone properties of the CustomerContactInfo and cut then paste them into the Customer. Then you can delete the CustomerContactInfo class from the design surface.

Now select the Customer and look at the Mapping Details window. Below the column mappings you will see <Add a Table or View>. Drop that down and select CustomerContactInfo and it will automatically map the columns in that table to the properties that we added.

Save the model and rebuild the project. Now you can add Customer as a data source for drag-drop Winforms data binding the same way you do for LINQ to SQL classes or your own objects. And the code for loading and saving of the Customers is similar to the LINQ to SQL code above except loading the Customers is much more intuitive. And the ObjectContext tracks changes for you and generates the proper insert, update, and delete statements automatically without you having to define stored procedures to do the splitting.

Private db As New MyDatabaseEntities

Private Sub Form1_Load() Handles MyBase.Load

    Me.CustomerBindingSource.DataSource = db.Customer
End Sub

Private Sub CustomerBindingNavigatorSaveItem_Click() _
    Handles CustomerBindingNavigatorSaveItem.Click

    Try
        db.SaveChanges()

        MsgBox("saved")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

This is just the tip of the iceberg using the Entity Data Model. I'd recommend reading the documentation in the MSDN library, visiting the forums and the FAQ, as well as visiting Julie Lerman's site (she's been living EF since the early Betas). I'm just learning EF myself but as you can see it allows you to model more complex data scenarios.

Enjoy!

LINQ to SQL N-Tier Smart Client - Part 3 Database Transactions

Beth Massi — Wed, 16 Apr 2008 22:03:41 GMT

In my previous posts this week I showed how to build a simple distributed application with a Windows client, a WCF hosted middle-tier and a data access layer that used LINQ to SQL:

LINQ to SQL N-Tier Smart Client - Part 1 Building the Middle-Tier

LINQ to SQL N-Tier Smart Client - Part 2 Building the Client

After sleeping on the design I realized that there's a scenario that we may want to handle. When we built the connected client-server version of the application (using the connected DataContext), because the DataContext is tracking all our changes (updates/inserts/and deletes) when we call SubmitChanges these updates are all processed in one single database transaction.

This may or may not be required for your application and in the case of Orders/OrderDetails it's okay to allow the updates and inserts and then the deletes to be processed in separate transactions. However what if we were working with drug interactions in a medical application or other data that needs to provide this level of integrity?

It's easy to make these modifications to our n-tier application we built. All we need to do is attach ALL the changes that we want processed in a single database transaction to one instance of the DataContext. To do this first we need to modify our service to accept all our changes. This can end up putting more data on the wire which we discussed in Part 1 so you need to evaluate your scenarios carefully. In our case I'm only pulling up open orders for a particular customer ID so the data set is relatively small.

First add the following interface on our WCF service:

<ServiceContract()> _
Public Interface IOMSService
.
.
<OperationContract()> _
Function SaveAllOrders(ByRef orders As OrderList, _
                       ByVal deletedOrders As IEnumerable(Of Order), _
                       ByVal deletedDetails As IEnumerable(Of OrderDetail)) As Boolean

End Interface

Next add the implementation to the OMSDataManager class in the data access layer to go ahead and attach all the changes to a single DataContext and submit all the changes at once. Note that the validation is performed exactly as before (when SubmitChanges is called).

Public Shared Function SaveAllOrders(ByRef orders As IEnumerable(Of Order), _
                                     ByVal deletedOrders As IEnumerable(Of Order), _
                                     ByVal deletedDetails As IEnumerable(Of OrderDetail)) As Boolean

    Dim hasOrders = (orders IsNot Nothing AndAlso orders.Count > 0)
    Dim hasDeletedOrders = (deletedOrders IsNot Nothing AndAlso deletedOrders.Count > 0)
    Dim hasDeletedDetails = (deletedDetails IsNot Nothing AndAlso deletedDetails.Count > 0)

    If (Not hasOrders) AndAlso (Not hasDeletedOrders) AndAlso (Not hasDeletedDetails) Then
        Return False 'nothing at all to save
    End If

    Dim db As New OMSDataContext

    For Each o In orders
        'Insert/update orders and details
        If o.OrderID = 0 Then
            db.Orders.InsertOnSubmit(o)
        Else
            db.Orders.Attach(o, o.IsDirty)
        End If

        For Each d In o.OrderDetails
            If d.IsDirty Then
                If d.OrderDetailID = 0 Then
                    db.OrderDetails.InsertOnSubmit(d)
                Else
                    db.OrderDetails.Attach(d, True)
                End If
            End If
        Next
    Next

    If hasDeletedOrders Then
        'Delete orders and related details
        db.Orders.AttachAll(deletedOrders, False)
        db.Orders.DeleteAllOnSubmit(deletedOrders)



        For Each o In deletedOrders
             For Each detail In o.OrderDetails
                db.OrderDetails.DeleteOnSubmit(detail)
            Next
        Next
    End If

    If hasDeletedDetails Then
        'Now delete the order details that were passed in
        ' (these order parents were not deleted, just the details)
        db.OrderDetails.AttachAll(deletedDetails, False)
        db.OrderDetails.DeleteAllOnSubmit(deletedDetails)
    End If

    Try
        'There's one database transaction for all records that are attached.
        'Since we attached all updates/inserts/deletes 
        ' they will all be processed in one transaction.
        db.SubmitChanges(ConflictMode.ContinueOnConflict)

        'Reset the IsDirty flag
        For Each o In orders
            o.IsDirty = False
            For Each d In o.OrderDetails
                d.IsDirty = False
            Next
        Next
    Catch ex As ChangeConflictException
        'TODO: Conflict Handling
        Throw
        Return False
    End Try

    Return True
End Function

We can then modify our form to call this new operation. On the client form I just added a new method called SaveAll. Note that the same simple change tracking is being used.

Private Sub SaveAll()
    'Push any pending edits on the BindingSources to the BindingList
    Me.Validate()
    Me.OrderBindingSource.EndEdit()
    Me.OrderDetailsBindingSource.EndEdit()
    Dim saved = False

    'Only save changes if there are some and they are valid
    If Me.HasChanges AndAlso Me.ValidateOrders() Then

        Dim saveOrders = Me.Orders.ToArray()
        Dim delOrders = Me.DeletedOrders.ToArray()
        Dim delDetails = Me.DeletedDetails.ToArray()

        Try
            If saveOrders.Count > 0 OrElse delOrders.Count > 0 OrElse delDetails.Count > 0 Then
                'Update/insert orders/details
                If proxy.SaveAllOrders(saveOrders, delOrders, delDetails) Then
                    Me.DeletedDetails.Clear()
                    Me.DeletedOrders.Clear()
                    saved = True
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        'Merges added keys and any validation errors
        Me.MergeOrdersList(saveOrders)

    End If

    If Me.HasErrors Then
        'Display any errors if there are any
        Me.DisplayErrors()
        MsgBox("Please correct the errors on this form.")
    Else
        If saved Then
            MsgBox("Your data was saved.")
        Else
            MsgBox("Your data was not saved.")
        End If
    End If
End Sub

So now when we make updates, inserts and deletes to our Orders and OrderDetails then we can save them all in a single database transaction.

I've uploaded the latest version of the application onto Code Gallery with the modifications.

Enjoy!

LINQ to SQL N-Tier Smart Client - Part 2 Building the Client

Beth Massi — Mon, 14 Apr 2008 21:03:00 GMT

In my last post we built the service and data access layer for our LINQ to SQL N-Tier application. In this post we'll walk through building a very simple Windows client form that works with our middle-tier.

Adding the Service Reference

Now that we have our middle-tier built it's time to add the service reference to the client project. Sine we have both .NET on the server and the client I'm going to use type sharing so that we can reuse the business objects (LINQ to SQL classes) on both ends. If you recall we we already added a project reference on the client to the OMSDataLayer project that defines these types.

Once you add that project reference we can add the service reference by right-clicking on the client and selecting "Add Service Reference" which opens up the Visual Studio 2008 Add Service Reference dialog. Hit the Discover button and it will pick up the OMSService in our solution. Click on the "Advanced" button and you'll notice some interesting settings here that I should mention.

Note here that the default is to "Reuse types in all referenced assemblies". This means that since we added the project reference to our LINQ to SQL business objects first, when the service proxy is generated it will not create new classes on the client, instead it will reference our business object types directly. Although this can make versioning more of a challenge it drastically cuts down the amount of code we have to write to maintain our business rules because now they are shared. However note that rules we call from the client cannot access the database directly. Our application here does not have any rules like that but it's something you may need to code for in your scenarios.

The other interesting settings I'll mention are the Collection type and Dictionary collection type settings since we're passing these types from our service. You can set these types to serialize differently if you need to. For instance, you can set the collection type to a BindingList if you are going to use all the collections from this service in typical data binding scenarios. Since this setting is for the entire service and we're only going to need a BindingList for just our GetOrdersByCustomerID result, I'm opting to keep the default Array type instead.

Loading the Data

Now we're ready to build our n-tier master-detail (one-to-many) form. Create a new form and then add a new data source (Menu, Data --> Add New Data Source) and select Object. Then expand the OMSDataLayer and choose the Order object and then do it again for Product.

Now we can build the master-detail form like I showed in this post (see the "Data Sources and Data Binding the Form" section) but this time against the objects in the shared assembly. The other main difference is that we don't need the Customer object because we're going to limit our data to just one customer.

Now we're ready to create an instance of our service reference and load the Orders from the middle-tier. Since the list will deserialize as an array, I'm going to place them into a BindingList that the form will manage. This will give us automatic add/delete support to the collection and a better data binding experience. I'm also going to set up a couple lists to track deletes of Order and OrderDetails. In a real application typically you create your own subclass of the BindingList and have it track these things but I'm trying to keep this example simple. We'll also load the products just like we did before but this time in our query we call the service instead.

Public Class NtierMasterDetailForm

    Dim customerID As Integer = 1 'should come from a search form

    Dim proxy As New OMSServiceReference.OMSServiceClient

    Dim Orders As New BindingList(Of Order)
    Dim DeletedOrders As New List(Of Order)
    Dim DeletedDetails As New List(Of OrderDetail)

    Private Sub Form1_Load() Handles MyBase.Load

        'Load the orders from our service
        Dim orderList = proxy.GetOrdersByCustomerID(customerID)

        For Each o In orderList
            Me.Orders.Add(o)
        Next

        Me.OrderBindingSource.DataSource = Me.Orders

        Dim emptyProduct As Product() = _
                {New Product With {.Name = "<Select a product>", .ProductID = 0}}

        Me.ProductBindingSource.DataSource = (From Empty In emptyProduct).Union( _
                                              From Product In proxy.GetProductList _
                                              Order By Product.Name)
    End Sub

Tracking Changes on the Objects

Now let's see how we're going to track all the changes made to the Orders and OrderDetails. First let's take another look at our BaseBusiness class. This is the class that we created in this post when we implemented our validation. When we built the middle-tier I mentioned that we needed to add this property but it's the client that needs to set it. Here's a look at the modifications we need to make to the BaseBusiness object including adding the DataMember attribute to the new IsDirty property as well as on the ValidationErrors dictionary.

<DataContract()> _
Public Class BaseBusiness
    Implements IDataErrorInfo

    Private m_isDirty As Boolean
    <DataMember()> _
    Public Property IsDirty() As Boolean
        Get
            Return m_isDirty
        End Get
        Set(ByVal value As Boolean)
            m_isDirty = value
        End Set
    End Property

    'This dictionary contains a list of our validation errors for each field
    Private m_validationErrors As New Dictionary(Of String, String)

    <DataMember()> _
    Public Property ValidationErrors() As Dictionary(Of String, String)
        Get
            Return m_validationErrors
        End Get
        Set(ByVal value As Dictionary(Of String, String))
            m_validationErrors = value
        End Set
    End Property
.
.
.

Since LINQ to SQL classes implement IPropertyNotifyChanged we can handle this event to set the IsDirty flag. The easiest way to set this flag is to tell the business objects themselves to do it. In order to hook up this event handler again when the objects are deserialized from the WCF service we can attribute a method with the OnDeserializedAttribute and add an event handler to the PropertyChanged event on all our business objects.

Partial Class Order
    Inherits BaseBusiness

    <OnDeserialized()> _
    Private Sub OnDeserialized(ByVal context As StreamingContext)
        AddHandler Me.PropertyChanged, AddressOf MyPropertyChanged
    End Sub

    Private Sub MyPropertyChanged(ByVal sender As Object, 
                                  ByVal e As System.ComponentModel.PropertyChangedEventArgs) _
                                  Handles Me.PropertyChanged
        If e.PropertyName <> "Customer" Then
            Me.IsDirty = True
        End If
    End Sub
.
.
.

The trick in the handler is to set the IsDirty flag only if the entity reference (the parent reference) property is not being set because we want to only set this flag if the user is making changes, not when the collection reference is set by the system.

Tracking adds is really easy because when an object is added to the collection it will be sent to the middle-tier and we can use the primary keys to determine if the Order or OrderDetail is new. For instance, if the OrderID on the Order is equal to zero (OrderID = 0) then we know we have a new object in the collection.

Deletes are a bit trickier because when you delete an object from the collection it's gone. If you are implementing a custom BindingList then you can just override the RemoveItem method but in our simple form we're just going to add the Order or OrderDetail being deleted to our Deleted* lists when the delete buttons are clicked on the form.

Private Sub OrderNavigatorDeleteItem_Click() Handles BindingNavigatorDeleteItem.Click
    'Track deletes of orders
    If Me.OrderBindingSource.Position > -1 Then
        Dim order As Order = CType(Me.OrderBindingSource.Current, Order)
        If order.OrderID > 0 Then
            'Greater than 0 indicates that the object came from the database.
            'If it's = 0 then we know the object was added here then deleted 
            '  and we don't need to track that.
            Me.DeletedOrders.Add(order)
        End If
    End If
End Sub

Private Sub DetailNavigatorDeleteItem_Click() Handles DetailNavigatorDeleteItem.Click
    'Track deletes of details
    If Me.OrderDetailsBindingSource.Position > -1 Then
        Dim detail As OrderDetail = CType(Me.OrderDetailsBindingSource.Current, OrderDetail)
        If detail.OrderDetailID > 0 Then
            Me.DeletedDetails.Add(detail)
        End If
    End If
End Sub

Validating and Saving our Changes

Before we send the changes to the service on the middle-tier we should validate the business objects here to save a round-trip. When we were working with the LINQ to SQL DataContext in connected mode the objects were validated when we called SubmitChanges(). This still happens in our middle-tier code but we need to validate here on the client as well so I added a public Validate method to the LINQ to SQL partial classes that just simply call into the OnValidate private methods we wrote previously. In the case of Order we'll also validate any OrderDetails.

Partial Class Order
    Inherits BaseBusiness
    .
    .
    . 
    Public Sub Validate()
        Me.OnValidate(System.Data.Linq.ChangeAction.None)

        'Validate the OrderDetails if there are any
        For Each d In Me.OrderDetails
            d.Validate()
        Next
    End Sub
.
.
.

Now we're ready to write our save code. If everything validates here on the client we first then send the deletes to the middle-tier, and if all goes well there then we clear the lists where we were tracking those objects. Then we can send the added and updated rows into the middle-tier. The middle-tier will then perform the validation there and then update and insert the business objects, and return the added primary/foreign keys. If we had any additional middle-tier business rules then those would also run and we could add additional validation messages that would be sent back in the ValidationErrors collection on each object.

The last thing left to do is dump the collection coming back from the middle-tier with our added keys back into the BindingList on our form. We just need to suspend the data binding first then we can copy the array back into the BindingList collection. Here's all the save code and supporting form methods.

Private Sub OrderBindingNavigatorSaveItem_Click() _
    Handles OrderBindingNavigatorSaveItem.Click

    Me.Save()
End Sub

''' <summary>
''' Saves all changes to the middle-tier
''' </summary>
''' <remarks></remarks>
Private Sub Save()
    'Push any pending edits on the BindingSources to the BindingList
    Me.Validate()
    Me.OrderBindingSource.EndEdit()
    Me.OrderDetailsBindingSource.EndEdit()

    Dim saved = True

    'Only save changes if there are some and they are valid
    If Me.HasChanges AndAlso Me.ValidateOrders() Then

        Dim saveOrders = Me.Orders.ToArray

        Try
            If Me.DeletedDetails.Count > 0 OrElse Me.DeletedOrders.Count > 0 Then
                'Delete any orders/details
                If proxy.DeleteOrders(Me.DeletedOrders.ToArray, _
                                      Me.DeletedDetails.ToArray) Then

                    Me.DeletedDetails.Clear()
                    Me.DeletedOrders.Clear()
                Else
                    saved = False
                End If
            End If

            If saved Then
                If saveOrders.Length > 0 Then
                    'Update/insert orders/details
                    saved = proxy.SaveOrders(saveOrders)
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        'merges added keys and any validation errors from the middle-tier
        Me.MergeOrdersList(saveOrders)
    End If

    If Me.HasErrors Then
        'Display any errors if there are any (same technique as before)
        Me.DisplayErrors()
        MsgBox("Please correct the errors on this form.")
    Else
        If saved Then
            MsgBox("Your data was saved.")
        Else
            MsgBox("Your data was not saved.")
        End If
    End If
End Sub

''' <summary>
''' Returns True if there are any validation errors on the business objects.
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Private Function HasErrors() As Boolean
    For Each o In Me.Orders
        If o.HasErrors Then Return True
    Next
    Return False
End Function

''' <summary>
''' Validates all the orders (order details are validated in the Order.Validate)
''' </summary>
''' <remarks></remarks>
Private Function ValidateOrders() As Boolean
    Try
        For Each o In Me.Orders
            o.Validate()
        Next
    Catch ex As ValidationException
        Return False
    End Try
    Return True
End Function

''' <summary>
''' Returns True if there are any changes to any of the orders/details.
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Private Function HasChanges() As Boolean
    If Me.DeletedDetails.Count > 0 OrElse Me.DeletedOrders.Count > 0 Then
        Return True
    End If
    For Each o In Me.Orders
        If o.IsDirty Then Return True
        For Each d In o.OrderDetails
            If d.IsDirty Then Return True
        Next
    Next
    Return False
End Function

''' <summary>
''' Copies from array to the BindingList while suspending data binding
''' </summary>
''' <param name="changes"></param>
''' <remarks></remarks>
Private Sub MergeOrdersList(ByVal changes() As Order)
    Dim pos = Me.OrderBindingSource.Position
    Me.OrderBindingSource.SuspendBinding()
    Me.OrderBindingSource.RaiseListChangedEvents = False

    Me.Orders.Clear()
    For Each o In changes
        Me.Orders.Add(o)
    Next

    Me.OrderBindingSource.ResumeBinding()
    Me.OrderBindingSource.RaiseListChangedEvents = True
    Me.OrderBindingSource.Position = pos
End Sub

And that's basically it. As you can see even in it's simplest implementation (that I could think of) writing n-tier applications with LINQ to SQL takes some work, especially as the relations between our object collections increase. LINQ to SQL is really just used as the data access technology in the middle-tier, everything on top of that is up to us to implement as we see fit for our particular scenarios.

You can download the sample application on CodeGallery here.

Enjoy!

LINQ to SQL N-Tier Smart Client - Part 1 Building the Middle-Tier

Beth Massi — Sat, 12 Apr 2008 19:01:00 GMT

In my previous posts on LINQ to SQL I showed how to build LINQ to SQL classes and set up the data binding in your Windows applications. If you missed them:

Creating Lookup Lists with LINQ to SQL

One-To-Many (Master-Detail) Forms with LINQ to SQL

Simple Validation with LINQ to SQL Classes

These articles focus on the binding and validation and use a connected model; meaning that the DataContext is alive and available tracking the changes we make to the collections of LINQ to SQL objects. Out of the box, LINQ to SQL is really easy to get working with a connected client-server architecture. As long as your objects are attached to the DataContext you get all the nice features like change tracking and automatic lazy loading of related collections. However, unlike the DataSet, once you disconnect from the data source you lose all automatic change tracking. So why use LINQ to SQL objects and not distributed DataSets?

The Great Debate - DataSets or objects?

It depends on your particular scenarios which approach you would take, and you are not limited to using just one approach in your application depending on your scenario. I always try to keep these guidelines in mind when I'm trying to decide which approach to take:

- DataSets are easy to define, serialize, and can now be easily separated from the data access logic in the TableAdapters. (Take a look at how to do this in VS 2008 in this video)

-DataSets have built-in change tracking, batched editing, viewing and filtering (also supports LINQ), and handles complex relationships automatically

-DataSets are easy to use with advanced DataBinding scenarios

-DataSets are easy to implement simple field and row validation but become challenging when complex behavior needs to be coded, they cannot inherit from a business class so complex business logic and data become separated

-DataSets should not be used if your clients are not all .NET clients because this type is a very complex type specific to .NET

-LINQ to SQL classes are plain old CLR objects (POCO) that only implement a couple property changed notification interfaces

-LINQ to SQL classes allow you full control over the validation, behavior, business logic, and can inherit from any class you provide so you can create a base business class

-LINQ to SQL object collections can be serialized as arrays to non-.NET clients

-LINQ to SQL objects, once detached from the DataContext, do not track changes or support any advanced DataBinding features out of the box, those must be implemented by you and the code is often non-trivial

Personally I like both approaches, it just depends on the scenario. Typically if I have a batch .NET data-entry form that I need to service with only a few field or column validations necessary, DataSets are my first choice, especially if there are more than a couple related tables. They are also a good choice for reports and simple view-only forms. If I have more complex rules and behavior or I need full control over my object hierarchy then POCO business objects are the better choice.

Going N-Tier with LINQ to SQL

If you're ready to jump off the cliff there's some really good information in the MSDN Library on how to set up LINQ to SQL in distributed applications. There are a variety of ways to handle LINQ to SQL in these scenarios as the library shows. In this post I'm going to walk through the most common scenarios to get this to work with a remote .NET Windows smart client (WPF coming soon!). This post will focus on the data access and service layer and I'll follow up with the client in the next post.

We're going to continue with the application that we built in the previous articles which was just a single Windows forms client built into one EXE. What we need to do is modify the design by adding a data access layer that communicates with our database as well as a service layer that the client will communicate with. Since our client and our server are both written in .NET I am also going to reuse the LINQ to SQL objects that contain our validation so we can keep those rules in one place. Please note that this is not SOA (Service Oriented Architecture). If you need to go that route then I highly recommend this book.

So here's a diagram of what we're going to implement.

The first thing to do is add a Class Library project called OMSDataLayer to contain the data access layer and the generated LINQ to SQL classes (our business objects) along with the partial class code and base business object class. Unfortunately the O/R designer doesn't let you separate the generated classes from the DataContext object (database access) like the DataSet designer does so if you really want to be a stickler and you have a large project you're developing you should create your own classes and then you can decorate them with the right attributes to get them to work with LINQ to SQL or use an external mapping file.

Next add a reference to System.Data.Linq to the class library and then move all the associated .dbml files as well as the BaseBusiness and ValidationException classes into the project. Then add a project reference to this OMSDataLayer to the client project. At this point if I want the client to compile and work exactly like before I can add a project level imports to the OMSDataLayer on the client, Import the OMSDataLayer namespace, and run it like before (we just have to go through and change the namespace on our objects on our form).

Finally, add a new WCF Service project to the solution and add the OMSDataLayer as a project reference. Now we've got the structure of our solution set up.

Contract First

Now it's time to think about the interaction our client will need to have with our service. It's always a good idea to think about how the endpoints in your distributed applications need to communicate with each other before you start developing them. For this application we only need interfaces for retrieval of the Orders and Products and saving and deleting Orders and OrderDetails. So I defined the following operations:

Imports OMSDataLayer

<ServiceContract()> _
Public Interface IOMSService

    <OperationContract()> _
    Function GetOrdersByCustomerID(ByVal customerID As Integer) As IEnumerable(Of Order)

    <OperationContract()> _
    Function GetProductList() As IEnumerable(Of Product)

    <OperationContract()> _
    Function SaveOrders(ByRef orders As OrderList) As Boolean

    <OperationContract()> _
    Function DeleteOrders(ByVal orders As IEnumerable(Of Order), _
                          ByVal details As IEnumerable(Of OrderDetail)) As Boolean

End Interface

I'm just returning a collection of our business objects in the Get* methods and I've defined Save and Delete methods that accept a collection of orders to either Save (update or insert) and Delete against the database. I'm sending Orders and OrderDetail collections in the case of Delete because I implemented a very simple change-tracking strategy and I want to be able to delete OrderDetails independently. OrderList is a simple Serializable class definition that inherits from List(Of Order) and has the KnownType attribute applied. This is needed so I can pass the Orders collection by reference so that primary keys and validation messages are sent back on the objects cleanly. (There are many ways to do this like passing messages and keys back separately. This is the easiest and cleanest in my opinion but you need to be aware that there is more data on the wire in this approach.)

<KnownType(GetType(Order))> _
<Serializable()> _
Public Class OrderList
    Inherits List(Of Order)

End Class

The last step to defining our contract is to set up our business objects so that they can be serialized as a WCF DataContract. We can do this via the O/R designer. Just open up the OMS.dbml file and in the properties for the DataContext set the Serialization mode to "Unidirectional".

This adds the DataContract attribute to the classes and the DataMember attribute to the properties. If we're using a WCF service with the DataContractSerializer then all this will work just dandy. This is because this serializer supports bi-directional relationships (like we have with Order and OrderDetail). However if we were trying to serialize this with the XMLSerializer used with non-WCF services we'd have issues; you'd only be allowed to serialize objects with no cyclic relationships.

We also need to add the DataContract and DataMember attributes to our BaseBusiness class as well because our objects inherit from this class. Also add a property called IsDirty to the BaseBusiness class that will help us later when we need to implement change tracking on our client. In the next post I'll talk about my approach to doing that when we talk about the client.

Disconnected Retrieval

Now that we have our contracts and solution set up we're ready to start partying on the data and write our data access layer code. Let's start with retrieving our Products and Orders. To recap what I mentioned in previous articles, when working in connected mode with the DataContext, collections of objects are not retrieved until they are accessed. This means that if I write a LINQ query to select an Order from the database it will not execute any statements to retrieve the OrderDetails until I attempt to access that child collection. This is a good thing because it saves calls to the database if they are not needed.

However when we are disconnected like in a distributed application there is no automatic lazy loading available, you have to roll your own. You need to decide how chatty you want to be in favor of less data on the wire. If you expect the client to be scrolling through smaller child collections it's probably better to just bring down a larger chunk of data in one call instead of making the client call the service multiple times. (Just to be clear, the client can still perform LINQ queries on the collections on the client-side to do additional filtering but that will be performed with standard LINQ to Objects, LINQ to SQL is only used on our server in the middle-tier.)

In this example, we won't need any child collections filled on Products, we're just using it as a lookup list. However, with Orders I want to pull all the Orders that haven't shipped for a specific customer ID and I want to also pull down those OrderDetails all in one call.

So now create a OMSDataManager class in our data access layer which our service implementation can call into. It's really easy to retrieve the Products. Note that the objects are automatically detached from the DataContext when they are serialized to the client through the service so we don't have to explicitly detach them here.

Imports System.Data.Linq

Public Class OMSDataManager

    Public Shared Function GetProductsList() As IEnumerable(Of Product)
        'Create the Datacontext
        Dim db As New OMSDataContext

        'Return the list of products. 
        'Objects are detached from the DataContext 
        ' automatically on serialization through the service
        Return db.Products.AsEnumerable()
    End Function

Now with Orders because we want load the OrderDetails as well we need to specify this in a DataLoadOptions object and pass them to the DataContext. The way we use this object is we call the LoadWith method to specify which data related to the main target should also be retrieved at the same time, resulting in one trip to the database. You pass it a Lambda expression to specify this. So to retrieve the Orders and OrderDetails in one LINQ to SQL query we can write:

 Public Shared Function GetOrdersByCustomerID(ByVal customerID As Integer) As IEnumerable(Of Order)
    'Create the Datacontext
    Dim db As New OMSDataContext

    'We want to also retrieve the OrderDetails collection as well.
    Dim dlo As New DataLoadOptions()
    dlo.LoadWith(Of Order)(Function(o As Order) o.OrderDetails)
    db.LoadOptions = dlo

    Dim orders = From order In db.Orders _
                 Select order _
                 Where order.CustomerID = customerID AndAlso _
                       order.ShipDate Is Nothing

    Return orders.AsEnumerable()
End Function

Disconnected Updates and Deletes

Disconnected updates are a bit more involved from a decision-making point of view. The way you update your data depends on the schema of your database and how you are performing concurrency checking. The documentation in the library goes into details on all the ways you can update your data but the simplest is using a timestamp field to track row modifications.

In our OMS database I have a "Modified" field specified on every table and I'm using that field to perform the concurrency checks. This makes it much easier to deal with updates on our middle-tier because that field's value is used to compare with the property value on the object and the update will only succeed if the values match. If you don't provide a RowVersion mechanism like a timestamp on your tables then you have to pass all the original values into the middle-tier as well. So I'm all for saving space on the wire and writing less code and timestamps are a common way to do row versioning and concurrency checking so I feel that this is the best approach for most applications.

The other issue you need to consider is what the best way is to work with your data. Is it better to update one entity at a time (for instance just sending one Order at a time to save) or is it better to accept a collection of updates and make just one chunky call to the middle-tier? Or a combination? I've almost always gone with the batch editing approach in the types of Windows applications I've written. Although there is more data on the wire, there's less calls to the middle-tier. So the client form caches all the updates, inserts and deletes to the collections and passes that around. You really need to watch how much data you're passing around, so in this example we're only pulling up open orders for a particular customer. Remember every scenario needs to be evaluated carefully. I took a simple approach, more data on the wire but 2 calls to the middle-tier, one to update/insert data and one call to delete data.

You can streamline this more by only sending the changes to the middle-tier from the client but then you need more code to figure out how to merge the middle-tier changes (validation messages and keys, for instance) back into the client. This is really easy to do with if we were using DataSets but for this example I took to simple route and am passing the entire collection -- but be warned -- this approach may not scale depending on your situation. As always, your mileage may vary.

So let's write our Save code for our Orders. Unlike when using DataSets with the TableAdapter, the DataContext in disconnected mode needs to be told explicitly what to do, insert, update or delete. An easy way to tell whether the objects in our collection are added or modified is by checking the primary key; if it's less then 1 then we know we have a new object to save. We also use the IsDirty flag we added on our BaseBusiness class to determine how to attach our objects to the DataContext.

Public Shared Function SaveOrders(ByVal orders As IEnumerable(Of Order)) As Boolean
    If orders Is Nothing OrElse orders.Count = 0 Then
        Return False
    End If

    Dim db As New OMSDataContext

    For Each o In orders
        If o.OrderID = 0 Then
            db.Orders.InsertOnSubmit(o)
        Else
            db.Orders.Attach(o, o.IsDirty)
        End If

        For Each d In o.OrderDetails
            If d.IsDirty Then
                If d.OrderDetailID = 0 Then
                    db.OrderDetails.InsertOnSubmit(d)
                Else
                    db.OrderDetails.Attach(d, True)
                End If
            End If
        Next
    Next

    Try
        'This will continue to process the 
        'rest of the orders even if one fails
        db.SubmitChanges(ConflictMode.ContinueOnConflict)

        'Reset the IsDirty flag
        For Each o In orders
            o.IsDirty = False
            For Each d In o.OrderDetails
                d.IsDirty = False
            Next
        Next

    Catch ex As ChangeConflictException
        'TODO: Conflict Handling
        Throw
        Return False
    End Try

    Return True
End Function

Disconnected deletes of Orders in our case are very easy because of the way we set up how deletes should work by manually setting setting the DeleteOnNull attribute to true (see the end of this post for info on that). We just need to attach the incoming Orders and OrderDetails to the DataContext and then we can delete them all.

Public Shared Function DeleteOrders(ByVal orders As IEnumerable(Of Order), _
                                    ByVal details As IEnumerable(Of OrderDetail)) As Boolean

    Dim db As New OMSDataContext
    Dim submit = False

    If orders IsNot Nothing AndAlso orders.Count > 0 Then
        'Delete orders and related details
        db.Orders.AttachAll(orders, False)
        db.Orders.DeleteAllOnSubmit(orders)

        For Each o In orders
            For Each detail In o.OrderDetails
                db.OrderDetails.DeleteOnSubmit(detail)
            Next
        Next
        submit = True
    End If

    If details IsNot Nothing AndAlso details.Count > 0 Then
        'Now delete the order details that were passed in
        ' (these order parents were not deleted, just the details)
        db.OrderDetails.AttachAll(details, False)
        db.OrderDetails.DeleteAllOnSubmit(details)
        submit = True
    End If
    Try
        If submit Then
            db.SubmitChanges(ConflictMode.ContinueOnConflict)
            Return True
        End If

    Catch ex As ChangeConflictException
        'TODO: Conflict Handling
         Return False

    End Try
End Function

The last thing we need to do to our middle-tier is call the data access layer from our service implementation. Note that I'm catching the ValidationException that's thrown from our business objects if they are invalid when the call to db.SubmitChanges is made and just returning false, which keeps our service from entering the fault state. The client will also perform validation of our business objects before the data is submitted to save a round-trip but the validation is also run here on the middle-tier. Validation messages are collected in a dictionary and serialized back to the client. (Read this post on how we set up validation on our LINQ to SQL classes.)

Imports OMSDataLayer

Public Class OMSService
    Implements IOMSService

    Public Sub New()
    End Sub

    Public Function GetOrdersByCustomerID(ByVal customerID As Integer) _
    As IEnumerable(Of OMSDataLayer.Order) _
    Implements IOMSService.GetOrdersByCustomerID

        Return OMSDataManager.GetOrdersByCustomerID(customerID)
    End Function

    Public Function GetProductList() As IEnumerable(Of OMSDataLayer.Product) _
    Implements IOMSService.GetProductList

        Return OMSDataManager.GetProductsList()
    End Function

    Public Function DeleteOrders(ByVal orders As IEnumerable(Of OMSDataLayer.Order), _
                                 ByVal details As IEnumerable(Of OMSDataLayer.OrderDetail)) _
                                 As Boolean _
                                 Implements IOMSService.DeleteOrders

        Return OMSDataManager.DeleteOrders(orders, details)
    End Function

    Public Function SaveOrders(ByRef orders As OrderList) As Boolean _
    Implements IOMSService.SaveOrders

        Try
            Return OMSDataManager.SaveOrders(orders)

        Catch ex As ValidationException
            Return False
        End Try
    End Function
End Class

So that's the meat of our middle-tier. In the next post we'll build our client form and implement a simple technique for change tracking.

UPDATE: Read how to create the client in this post.

Enjoy!

Simple Validation with LINQ to SQL Classes

Beth Massi — Tue, 26 Feb 2008 04:04:24 GMT

In the last few posts on LINQ to SQL I've showed how to set up an object model using the O/R designer and how to handle a couple data binding scenarios with Comboboxes here and here. Last post on this topic we implemented a one-to-many data entry form and I showed how to work with stored procs as well as how to properly configure delete behaviors. In this post I want to explore how to easily add validation rules to our LINQ to SQL classes and how we can get these rules automatically displayed in the UI.

LINQ to SQL Classes -- A Closer Look

Classes that implement the IDataErrorInfo interface in conjunction with INotifyPropertyChanged are able to automatically notify UI objects like the ErrorProvider and the DataGridView to display validation errors. LINQ to SQL classes that are generated for you when you create your object models already implement the INotifyPropertyChanged interface and using partial methods we can easily add validation to our classes. For this example we'll expand the One-to-Many data entry form we built to include some business rules.

First let's open up the generated LINQ to SQL classes by opening the .Designer.vb file under the dbml file (if you don't see the designer file, just click the "show all files" button on the Solution Explorer tool strip first). If we take a look at our Order class we will see the following class definition:

<Table(Name:="dbo.Orders")>  _
Partial Public Class [Order]
    Implements System.ComponentModel.INotifyPropertyChanging, _
               System.ComponentModel.INotifyPropertyChanged

As you can see, this class is just a plain old CLR object (POCO) that implements interfaces that notify when properties are changing or changed on the class. That's it. It's the DataContext that does the heavy lifting, knowing what objects have changes, which ones were added and removed and how to persist these to the database. It does its simple mapping via the attributes on the class and it's properties. Here's the property for CustomerID on our Order class:

<Column(Storage:="_CustomerID", DbType:="Int NOT NULL", UpdateCheck:=UpdateCheck.Never)>  _
    Public Property CustomerID() As Integer
        Get
            Return Me._CustomerID
        End Get
        Set
            If ((Me._CustomerID = value)  _
                        = false) Then
                If Me._Customer.HasLoadedOrAssignedValue Then
                    Throw New System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException
                End If
                Me.OnCustomerIDChanging(value)
                Me.SendPropertyChanging
                Me._CustomerID = value
                Me.SendPropertyChanged("CustomerID")
                Me.OnCustomerIDChanged
            End If
        End Set
    End Property

Partial Classes and Methods

In order to add validation and business rules we can add code to the OnCustomerIDChanging and OnCustomerIDChanged methods. But we don't add them here in this generated file, instead we add them into the Partial Class. This is possible because of a new feature in Visual Studio 2008 called Partial Methods. Partial Classes were introduced in Visual Studio 2005 as a way to extend generated classes with additional functionality by allowing you to split classes across physical files. You can add new methods or properties in order to extend a generated class easily by using the Partial Class keyword and then writing your own code. The compiler will "merge" these files into one class.

In Visual Studio 2008 we can go a step further using Partial methods. Instead of raising and handling private events, partial methods can be used instead as a better performing and cleaner alternative. They are declared by creating a private method with an empty body and decorating it with the Partial keyword. The method may then be "re-implemented" elsewhere within its containing class. If the method is implemented, then the compiler will redirect all calls to the partial method to the implementing method. If the method is not implemented in its containing class, then the compiler silently removes any calls to it from the program.

If we take a look at the generated LINQ to SQL Order class again you can see there is a region called "Extensibility Method Definitions" that contain Partial methods. There will be On...Changed and On...Changing partial methods here for each of the properties on the class.

#Region "Extensibility Method Definitions"
    Partial Private Sub OnLoaded()
    End Sub
    Partial Private Sub OnValidate(action As System.Data.Linq.ChangeAction)
    End Sub
    Partial Private Sub OnCreated()
    End Sub
    Partial Private Sub OnCustomerIDChanging(value As Integer)
    End Sub
    Partial Private Sub OnCustomerIDChanged()
    End Sub
.
.
.

Notice that the Changing and Changed methods are called at the appropriate times in each of the property setters. This allows us to write clean business rules on these properties in our partial class by defining the partial method. However if we don't write any code for these partial methods, the calls to them are removed from the IL. To access the partial class code open the model (dbml file) in the O/R designer, select a class, right-click on the class name and select "View Code". This will create a file named after your model where the partial class code that we write can reside.

So to write some business rules for our classes in this example we're going to place code in the On...Changing methods and then implement IDataErrorInfo so that we can have the UI automatically display the validation messages. To make it easier to implement IDataErrorInfo on all our LINQ to SQL classes I'm going to create a base class that we can inherit from called BaseBusiness.

Implementing IDataErrorInfo

IDataErrorInfo requires us to implement only two properties, one called Error and one default property Item that both return a string. Error is used to describe what is wrong with the entire object. For instance, if we are displaying this object in a row of a DataGridView this property indicates what error message appears on the row header. If you use DataSets, this corresponds to the DataRow.RowError property. The Item property is used to determine the error message for a specific property (or column) that is passed in. In order to collect these messages for each property on our object (i.e. column in our table) we can use a generic dictionary of strings and either add or remove messages from the dictionary depending on the validation rules. Here's an example implementation of IDataErrorInfo on our base class:

Imports System.ComponentModel

''' <summary>
''' Base class for our LINQ to SQL classes.
''' This class demonstrates one way to implement the IDataErrorInfo
'''  interface so that the ErrorProvider and DataGridView can display
'''  validation errors in the UI.
''' </summary>
''' <remarks></remarks>
Public Class BaseBusiness
    Implements IDataErrorInfo

    'This dictionary contains a list of our validation errors for each field
    Private validationErrors As New Dictionary(Of String, String)

    Protected Sub AddError(ByVal columnName As String, ByVal msg As String)
        If Not validationErrors.ContainsKey(columnName) Then
            validationErrors.Add(columnName, msg)
        End If
    End Sub

    Protected Sub RemoveError(ByVal columnName As String)
        If validationErrors.ContainsKey(columnName) Then
            validationErrors.Remove(columnName)
        End If
    End Sub

    Public Overridable ReadOnly Property HasErrors() As Boolean
        Get
            Return (validationErrors.Count > 0)
        End Get
    End Property

    Public ReadOnly Property [Error]() As String _
        Implements System.ComponentModel.IDataErrorInfo.Error
        Get
            If validationErrors.Count > 0 Then
                Return String.Format("{0} data is invalid.", TypeName(Me))
            Else
                Return Nothing
            End If
        End Get
    End Property

    Default Public ReadOnly Property Item(ByVal columnName As String) As String _
        Implements System.ComponentModel.IDataErrorInfo.Item
        Get
            If validationErrors.ContainsKey(columnName) Then
                Return validationErrors(columnName).ToString
            Else
                Return Nothing
            End If
        End Get
    End Property
End Class

Next we need to inherit from this class in all our LINQ to SQL Classes. Back in our partial class file we can inherit all our LINQ to SQL classes from BaseBusiness:

Partial Class Customer
    Inherits BaseBusiness

End Class

Partial Class Product
    Inherits BaseBusiness

End Class

Partial Class OrderDetail
    Inherits BaseBusiness

End Class

Partial Class Order
    Inherits BaseBusiness

End Class

Writing the Business Rules

Now we need to write our business rules into these classes. Let's take Order as an example. There are a couple rules I want to implement here:

The OrderDate cannot be after the ShipDate
The Customer must be specified

There are a couple places that we need to run these rules. One is when any of these fields change and the other is before the changes are submitted to the database. We need to handle both places because the user doesn't necessarily change all the properties and raise the On...Changing event. We need to handle the case when the user doesn't fill out a field and immediately attempts to save. This partial method is called OnValidate on our LINQ to SQL classes. This method is called automatically by the DataContext right before it attempts to submit the changes to the database when SubmitChanges is called.

Because you have to run the rules in both of these situations it's probably easier to create a private method that checks each rule and call that method from both Partial On...Changing and OnValidate methods. If the rule is broken then we just call AddError to add the error message to the dictionary, otherwise we call RemoveError to remove it from the dictionary. Here's the code for my Order Partial class:

Partial Class Order
    Inherits BaseBusiness

    Private Sub OnCustomerIDChanging(ByVal value As Integer)
        Me.CheckCustomerID(value)
    End Sub

    Private Sub OnOrderDateChanging(ByVal value As Date?)
        Me.CheckOrderDate(value)
    End Sub

    Private Sub OnShipDateChanging(ByVal value As Date?)
        Me.CheckShipDate(value)
    End Sub

    Private Sub OnValidate(ByVal action As System.Data.Linq.ChangeAction)
        Me.CheckCustomerID(Me.CustomerID)
        Me.CheckOrderDate(Me.OrderDate)
        Me.CheckShipDate(Me.OrderDate)

        If Me.HasErrors Then
            Throw New ValidationException(Me.Error)
        End If
    End Sub

    Private Sub CheckCustomerID(ByVal value As Integer)
        If value < 1 Then
            Me.AddError("CustomerID", "Customer cannot be empty.")
        Else
            Me.RemoveError("CustomerID")
        End If
    End Sub

    Private Sub CheckOrderDate(ByVal value As Date?)
        If value.HasValue Then
            If value > Me.ShipDate Then
                Me.AddError("OrderDate", "Order date cannot be after the ship date.")
            Else
                Me.RemoveError("OrderDate")
            End If
        End If
    End Sub

    Private Sub CheckShipDate(ByVal value As Date?)
        If value.HasValue Then
            If value < Me.OrderDate Then
                Me.AddError("ShipDate", "Ship date cannot be before the order date.")
            Else
                Me.RemoveError("ShipDate")
            End If
        End If
    End Sub

    Public Overrides ReadOnly Property HasErrors() As Boolean
        Get
            If Not MyBase.HasErrors Then
                'Returns True if any order details are invalid 
                For Each detail In Me.OrderDetails
                    If detail.HasErrors Then
                        Return True
                    End If
                Next
            End If
            Return MyBase.HasErrors
        End Get
    End Property

End Class

So here we are calling our validation methods from the On...Changing as well as the OnValidate partial methods. If you throw an exception from OnValidate then that will halt the SubmitChanges from going further. I created my own ValidationException class that we can use to check from our save code on the form. I also included any Order Detail errors in the HasErrors property of this Order class. This means that if any order details have errors then this Order also reports that HasErrors is True.

Displaying Validation Errors in the UI

Now that we have our business rules implemented we can hook up the UI to display these messages. Objects that are being displayed in a DataGridView will automatically pick up our validation messages but for simple controls like textboxes we need to hook up an ErrorProvider. Just drag the ErrorProvider from the toolbox onto your form and then specify the BindingSource as the DataSource property, for this example it's the OrderBindingSource. That's it. The ErrorProvider will look for any error messages on our objects through the IDataErrorInfo interface. This occurs when a property changes. For instance, if I run the form now and change the Order Date to be after the Ship Date, an error will be displayed as I tab off of the Order Date:

In order to display the messages after validation fails when we attempt to submit changes to the database, we need to write some code to refresh the display. In this scenario we also need to check the OrderBindingSource's position to make sure that the user is sitting on the invalid Order so the visuals are clear as to what needs fixing. This is why I created my own exception class called ValidationException and added a property to our BaseBusiness class called HasErrors, so that we could easily handle this case.

So back in our form we'll write the following Save code:

Private Sub OrderBindingNavigatorSaveItem_Click() _
    Handles OrderBindingNavigatorSaveItem.Click

    Me.Validate()
    Me.OrderBindingSource.EndEdit()
    Me.OrderDetailsBindingSource.EndEdit()

    Try
        db.SubmitChanges()

        MsgBox("Your data was saved.")

    Catch ex As ValidationException
        Me.DisplayErrors()
        MsgBox("Please correct the errors on this form before saving.")

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

''' <summary>
''' Displays any error information and navigates to the first error row
''' </summary>
''' <remarks></remarks>
Private Sub DisplayErrors()
    Me.ErrorProvider1.UpdateBinding()
    Me.OrderDetailsDataGridView.Refresh()

    If Me.OrderBindingSource.Position > -1 Then
        Dim currentOrder As Order = CType(Me.OrderBindingSource.Current, Order)

        If Not currentOrder.HasErrors Then
            'The error is not in view so navigate to it
            For i = 0 To Me.OrderBindingSource.Count - 1
                Dim order As Order = CType(Me.OrderBindingSource(i), Order)
                If order.HasErrors Then
                    Me.OrderBindingSource.Position = i
                    Exit Sub
                End If
            Next
        End If
    End If
End Sub

To test this, add a new Order then navigate away from it. When you click Save the validation will fail and you will be positioned back on the order that failed.

I placed the code for this article (including the previous article code on this topic) into a Code Gallery project for you to play with.

Enjoy!

One-To-Many (Master-Detail) Forms with LINQ to SQL

Beth Massi — Wed, 20 Feb 2008 02:11:00 GMT

In previous posts this month I showed how to use LINQ to SQL classes with a couple different Combobox data binding scenarios. (You can read those articles here and here.) Today I'm going to show you how to create a one-to-many data entry form (and we'll use a couple Combobox lookup lists as well). I'll show you what you need to do to enable proper insert, update and deletes of the hierarchical data. I'll also show how we can specify that these operations happen via stored procedures.

The Database Model

For this example I'll be creating my own database and not using Northwind. This is because Northwind isn't a very typical database especially when it comes to referential integrity. I want to create tables that have non-nullable foreign keys as well as use a timestamp field for concurrency checking.

So here's the database diagram of what we'll be building off of:

I've also specified Update, Insert and Delete stored procedures for each of the tables. This is so we can lock down the database security a bit and disallow UPDATE, INSERT and DELETE SQL statements from executing against it. All we need to grant is SELECT and EXECUTE permissions. This is a typical configuration for databases because it helps prevent against malicious code executing on the database by stopping changes from happening outside the stored procs.

So to get started building my form, I'll start by adding a new item to my project called "LINQ to SQL Classes" which will open the O/R designer and allow me to drag tables in my database from the Server Explorer onto the model's design surface. I'll drag all of the four tables above and since the database is called OMS I'll name the model OMS.dbml. This will create our LINQ to SQL classes and infer the associations from the database relationships. I'll also drag all the stored procs onto the Methods pane.

Next we want to associate the stored procs with the update, insert and delete behaviors for each class. Select the class then in the properties window you will see three properties; Delete, Insert and Update and they are all set to "Use Runtime". Select the properties and you then can specify the procedures in the methods pane to use for each behavior. You can also simply right-click on the class and select "Configure Behavior". On this screen you can specify the behavior for all the classes by selecting the Customize radio button and then selecting the corresponding procedure shown in the method pane.

After you got all of these set up, save the model and this will generate all the LINQ to SQL classes plus the DataContext which is used to manage the connection and communication to our database.

Data Sources and Data Binding the Form

Next we need to get these into our Data Sources window so that we can quickly get our form designed. I showed how to do this before in the previous posts against Northwind. This time I want to create a master-detail form of Orders and related OrderDetails in a grid and I want to show the Customer and Product as lookup lists. Select Add New DataSource from the Data menu and select Object as the Data Source Type. Next, select the Order class and click Finish. This will populate your Data Sources window with the Order and also its related OrderDetails because of the association. We'll also want to add Customer then Product to our Data Sources window as well because we'll need those for our lookup lists.

When you inspect the properties of the classes in the Data Sources window you will see that the associated parent object is also visible along with the child collections. For instance, if you expand Order you will see the parent Customer as well as the child OrderDetails. This indicates the associated parent Customer object for that Order object. I'm going to want to display that information as a lookup list so change the drop control for Customer to "None" and change the CustomerID to a Combobox. I also do not want to display the Modified field so also set that to "None". Then I'll set the drop control of the Order to "Details".

Drag the Order onto the form to set up the controls as well as the BindingNavigator and BindingSource for the Order. Next drag the Customer from the Data Sources Window onto the top of the CustomerID Combobox to set up the CustomerDataSource for the list of items. In the properties for the Combobox set the ValueMember = CustomerID and the DisplayMember = LastName in order to finish setting up the lookup list for Customer.

Next drag the OrderDetails listed under Order onto the form to drop down a DataGridView. You will notice that this will also pull in the parent objects, Product and Order in this case. Just edit the columns and remove those as well as the Modified field. For this example, I'll still display but set the OrderDetailID and OrderID to ReadOnly since these will be filled in automatically for us after we save the data. We'll also need to change the ProductID column type to a DataGridViewComboBoxColumn and then select the Product as the DataSource by selecting Other Data Sources --> Project Data Sources --> Product. This process will create a ProductBindingSource in the component tray.

Also we will need to specify the DisplayMember = Name and ValueMember = ProductID on the column here.

Loading the LINQ to SQL Classes with Data

Now that we have our form designed and the data binding all set up we're ready to create our objects and fill them with data. Unlike when we are using DataSets on our forms, the Form Designer will not generate any loading or saving code for us when using LINQ to SQL classes. But the code we need to write is very straightforward and we can write LINQ queries to limit our result sets. For this simple example I will select all Orders and also all of the Customers and Products into our lookup lists but keep in mind this may be a bad design if there are hundreds of rows in your database. In that situation it's better to write a search form.

So in the Form's Load event handler we need to set up the BindingSource's DataSources with data returned from our tables. First we'll fill the form with all the orders from the Orders table.

Public Class Form1

    Dim db As New OMSDataContext

    Private Sub Form1_Load() Handles MyBase.Load

        Me.OrderBindingSource.DataSource = db.Orders

Next we want to populate the Customers list. We can get cute and can specify a LINQ query here in order to select the customer names in "LastName, FirstName" format.

        Me.CustomerBindingSource.DataSource = From c In db.Customers _
                                              Let LastName = c.LastName & ", " & c.FirstName _
                                              Select LastName, c.CustomerID _
                                              Order By LastName

Finally we want to select our list of Products. Here's a trick that will place an "empty" product at the top of the list so that it can indicate to the user to select a value. We can add validation later to check that the selection has been made by checking that the ProductID > 0.

        Dim emptyProduct As Product() = _
                {New Product With {.Name = "<Select a product>", .ProductID = 0}}

        Me.ProductBindingSource.DataSource = (From Empty In emptyProduct).Union( _
                                              From Product In db.Products _
                                              Order By Product.Name)

    End Sub

You might be wondering why we're not explicitly setting the OrderDetailBindingSource's DataSource. This is because the OrderDetails are loaded from the database automatically only when we access the OrderDetails collection on the Order object. This happens when the OrderBindingSource moves position and the OrderDetailsBindingSource needs to display the OrderDetail objects for the Order. If you want to see the T-SQL statements being run just put a call to db.Log = Console.Out in the Load to display the statements in the Debug Output window. Just make sure to remove it before building your release.

Saving Hierarchical Data

Next we need to add the save code by enabling the save button and handling the click event. In my previous post when we built a single-table entry form with a lookup list I showed how to do this:

    Private Sub OrderBindingNavigatorSaveItem_Click() _
        Handles OrderBindingNavigatorSaveItem.Click

        Me.Validate()
        Me.OrderBindingSource.EndEdit()
        Me.OrderDetailsBindingSource.EndEdit()

        Try
            db.SubmitChanges()

            MsgBox("Your data was saved.")

        Catch ex As Exception
           MsgBox(ex.ToString)

        End Try

    End Sub

Okay so let's give this form a try. Run the form and make a change, and/or insert a new Order, click save, and you should see that everything worked out smoothly. The keys are properly populated on insert and the relationship works correctly. However if we try to delete an OrderDetail (child) row from our grid we get the following error:

System.InvalidOperationException: An attempt was made to remove a relationship between a Order and a OrderDetail. However, one of the relationship's foreign keys (OrderDetail.OrderID) cannot be set to null.

To fix this we need to indicate to the model that we want to delete the OrderDetail when it's OrderID is set to null. Unfortunately this cannot be done in the O/R designer so you have to open the model in an XML editor. Fortunately, once you change it the designer won't mess with it again unless you remove the class completely. Open the dbml file with the XML Editor (just right-click on it an select "Open with...") and locate the XML that describes the OrderDetail class. Notice the association under the OrderDetail table:

<Table Name="dbo.OrderDetail" Member="OrderDetails">
  <Type Name="OrderDetail">
    <Column Name="OrderDetailID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" 
            IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
    <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    <Column Name="Quantity" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    <Column Name="Price" Type="System.Decimal" DbType="Money" CanBeNull="true" />
    <Column Name="Modified" Type="System.Data.Linq.Binary" DbType="rowversion NOT NULL" 
            CanBeNull="false" IsVersion="true" />
    <Association Name="Order_OrderDetail" Member="Order" ThisKey="OrderID" 
                 Type="Order" IsForeignKey="true"/>
    <Association Name="Product_OrderDetail" Member="Product" ThisKey="ProductID" 
                 Type="Product" IsForeignKey="true" />
  </Type>

We need to add an attribute here called DeleteOnNull and set it to true in order to be able to delete a child row independently in the database when calling SubmitChanges(). Once we make this change we can now delete just a single OrderDetail from the grid and save normally:

<Association Name="Order_OrderDetail" Member="Order" ThisKey="OrderID" Type="Order" 
             IsForeignKey="true" DeleteOnNull="true"/>

The other option to fix this issue is to modify the Delete Rule to "Cascade" on the relationship in the database. In that case the designer correctly infers this attribute on the association.

Okay let's run the form again and now when you try to delete an OrderDetail child from the grid and click save, it saves without error. But if you try to delete an entire order by clicking the delete button on the ToolStrip and then save we now get a database error:

System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_OrderDetail_Orders". The conflict occurred in database "OMS", table "dbo.OrderDetail", column 'OrderID'.
The statement has been terminated.

This is because unlike DataSets, you can't specify in the model that when you delete a parent, it should cascade to the children automatically. So we need to write some code to do this. There's a variety of ways you can do this and one way I already showed in a previous post by adding code to the DataContext that works nicely if we are not using stored procs. The basic idea is that we need to tell the DataContext to delete the child objects anytime an Order is deleted. In this example, I chose to do this by calling DeleteOnSubmit before we call SubmitChanges. I added this code into the Click event handler for the Delete button on the ToolStrip of the form:

    Private Sub BindingNavigatorDeleteItem_Click() _
        Handles BindingNavigatorDeleteItem.Click

        If Me.OrderBindingSource.Position > -1 Then
            'Grab a reference to the currently selected order
            Dim order As Order = CType(Me.OrderBindingSource.Current, Order)
            
            'Ensure that children are deleted when the parent is deleted
            For Each detail In order.OrderDetails
                db.OrderDetails.DeleteOnSubmit(detail)
            Next
        End If
    End Sub

Now run the form and try a variety of Update, Insert and Delete operations on the data and you will have a smooth ride. If you enable logging on the DataContext or run SQL profiler on the database you will see our stored procedures being called in the proper order.

Next time I'll show you how we can add simple validation to our LINQ to SQL classes by creating a base business class to inherit from and using the IDataErrorInfo interface along with the ErrorProvider.

UPDATE: I placed the code for this article (including the previous article code on this topic) into a Code Gallery project for you to play with.

Enjoy!

Creating Lookup Lists with LINQ to SQL

Beth Massi — Thu, 07 Feb 2008 20:00:00 GMT

In yesterday's post I showed you how to bind LINQ to SQL classes to a Combobox in order to filter records on a one-to-many form. Today I want to show you how how you can use a Combobox as a lookup list in order to edit values on a record. We'll be building on yesterday's example. So to recap, we have added a LINQ to SQL object model called Northwind.dbml to our Windows Forms application that contains Regions and Territory classes and then added the Region object to the Datasources Window by selecting "Add New DataSource" and selecting the Object data source type.

Let's create a simple form that we can use to edit Territories. From the Data Sources window, expand the Region and its related Territories collection then drag the TerritoryID and TerritoryDescription onto the form as Textboxes. This will automatically create the TerriroriesBindingSource and BindingNavigator. Next, select the Combobox control for the RegionID and then drag that onto the form.

This sets up the binding to the Territory so that when a value is selected in the Combobox, the Territory.RegionID property is set to that value. Next we need to bind the list of Regions to display in the Combobox, so just drag the Region object (root table displayed in the Data Sources Window) onto the top of (directly over) the Combobox you just dropped on the form. This is a handy trick that will set up the RegionBindingSource in the component tray and set the Combobox's DataSource property to the RegionBindingSource. Next set the ValueMember of the Combobox to "RegionID" in the properties sheet. You may also want to set the Combobox DropDownStyle to "DropDownList".

If we were to look at the generated code, this is how it sets up our data binding in this scenario:

Me.RegionIDComboBox.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", _
                                     Me.TerritoriesBindingSource, "RegionID", True))

Me.RegionIDComboBox.DataSource = Me.RegionBindingSource
Me.RegionIDComboBox.DisplayMember = "RegionDescription"
Me.RegionIDComboBox.ValueMember = "RegionID"

What this is doing is first setting up the data binding between the selected value of the Combobox and the TerritoriesBindingSource on the RegionID property. Then we set the DataSource property of the Combobox to the RegionBindingSource which fills the list with Regions to choose from.

Now the rest is up to us. Unlike when using DataSets, the designer doesn't generate code to fill or save our data, but fortunately the code we have to write is minimal. First let's fill our data. It's important in this scenario to fill the Territories before the Regions so that the data binding will work properly.

Public Class Form2

    Dim db As New NorthwindDataContext

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Me.TerritoriesBindingSource.DataSource = db.Territories
        Me.RegionBindingSource.DataSource = db.Regions
    End Sub

End Class

Next we need to write our save code. It's really easy to do this when working with client-server forms like this one because the DataContext automatically tracks changes to our objects for us. We just call SubmitChanges on the DataContext when we want to send all the changes to the database. Enable the save button on the BindingNavigator and then write some simple Save code in the click event handler:

Private Sub TerritoriesBindingNavigatorSaveItem_Click() _
    Handles TerritoriesBindingNavigatorSaveItem.Click

    Me.TerritoriesBindingSource.EndEdit()

    Try
        db.SubmitChanges()

        MsgBox("Saved.")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

Let's give it a try! Run the form and edit, add and delete the Territories then click Save and you should see your changes next time you run the form. If you want to see the T-SQL statements being run just put a call to db.Log = Console.Out in order to display the statements in the Debug Output window. Just make sure to remove it before building your release.

Hopefully this helps get you started designing forms using LINQ to SQL classes. It's interesting to note that I actually didn't write one LINQ query in this example. However it's really easy to write queries against your object model now and have the LINQ to SQL provider worry about the SQL statements. For instance, if I wanted to only return the Territories in the Region that started with "East" then we could set the TerritoriesBindingSource.DataSource property like so:

Me.TerritoriesBindingSource.DataSource = _
    From Territory In db.Territories _
    Where Territory.Region.RegionDescription Like "East*"

Me.RegionBindingSource.DataSource = db.Regions

This would result in only a subset of data returned to our form which is always a good idea especially in multi-user database systems! Here's the SQL statements that are automatically sent to the database for us:

SELECT [t0].[TerritoryID], [t0].[TerritoryDescription], [t0].[RegionID]
FROM [dbo].[Territories] AS [t0]
INNER JOIN [dbo].[Region] AS [t1] ON [t1].[RegionID] = [t0].[RegionID]
WHERE [t1].[RegionDescription] LIKE @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [East%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[RegionID], [t0].[RegionDescription]
FROM [dbo].[Region] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Looks like reasonable SQL code to me! ;-)

Next time we'll create a one-to-many data entry form and talk about a couple tricks you'll need to know in order to update data properly. (Psssst... here's a hint)

UPDATE: I placed the code for all the articles on this topic into a Code Gallery project for you to play with.

Enjoy!

Related Data Binding and ComboBoxes with LINQ to SQL

Beth Massi — Thu, 07 Feb 2008 01:07:00 GMT

In a previous post I showed how to set up related data binding using ComboBoxes against DataSets and a loyal reader asked how this would be done using LINQ and Visual Studio 2008. I assume he meant LINQ to SQL in this case, because remember LINQ can be used over DataSets too -- you just have to call the TableAdapter fill methods like normal and then you can write queries over the in-memory DataSets like I show in this video.

With LINQ to SQL this can be easier to do and more efficient depending on how you set it up. This is because LINQ to SQL has the ability to fetch data from SQL Server only as you need it automatically for you. With the DataSet this is a more manual process of calling the TableAdapter's Fill method.

In this first example I'm going to set up a ComboBox that is used as a filter to select records to display in a grid. So we want to display a list of parent table's records and use that as a filter to display related child records. For instance, as the user selects a record in the ComboBox, we want to display all the related child records in a grid below.

The first thing is to create a new Windows Forms application and I'll call it LINQComboBinding. Next, we need to add a LINQ to SQL data model that maps 1:1 to our database tables. This is a new item in Visual Studio 2008 that automatically generates classes for our tables which allows us to perform LINQ queries in our code and have them automatically translated and sent as T-SQL statements to SQL-Server. The advantage to using LINQ to SQL classes is that now you can have compile-time checking on your queries and access the results as strongly-typed objects. To add LINQ to SQL classes, right-click on the Project and select "Add New Item" and then choose the LINQ to SQL classes template.

I'll be using the Northwind sample database in this example so I'll name the model Northwind.dbml. Click "Add" and this will open the O/R designer. Just like the DataSet designer, you can drag tables from your Server Explorer (or Database Explorer if using VS2008 Express edition) onto the surface of the designer in order to automatically generate code that defines your LINQ to SQL classes. I'll select Region and Territories for this example.

Notice that the O\R designer infers the associations of the classes it creates by reading the database relationships. In the above diagram, the Region class will contain a collection of Territory classes called Territories. You can take a look at the code it generates by showing all files on the project and then opening the Northwind.designer.vb file.

Okay now we're ready to start designing our form. When we work with data binding on Windows forms we use the Data Sources Window to drag items onto the form designer to create and bind our controls. Under the covers all the controls are bound to a BindingSource that manages the data binding between the control and the DataSource. I've covered this all before with respect to using DataSets as the DataSource of the BindingSource here in this video as well as the previous article on this topic.

What's great about the BindingSource is that it can manage data binding between the control and any type of DataSource, including collections of objects, not just DataSets. And we can use the same familiar drag-drop from the Data Sources Window even with LINQ to SQL classes, there's just a couple of manual steps.

When we open the Data Sources Window (from the menu select Data --> Show Data Sources) you'll see that our LINQ to SQL classes do not show up automatically like our DataSets do. But all we need to do is "Add New DataSource" and then select Object as the Data Source Type in the Data Source Configuration Wizard:

Next thing we do is select the object we want to bind to. In this case, all we need to select is the Region class because this class already contains the Territories collection which we will use on our one-to-many form.

Click Next then Finish and this will populate your Data Sources window with Region and you will see the Territories collection under that. For this example we're going to want a Combobox that is used as a filter for a grid that is displaying the related Territories. In the Data Sources window with the Form Designer open, set the Region to drop a Combobox control.

If you only see DataGridView and Details listed then select "Customize..." and check the Combobox as an available control for the List type. Next drag the Region Combobox onto the form. This will automatically create the RegionBindingSource and a RegionBindingNavigator. I'm going to delete the BindingNavigator because we're going to use the Combobox as a navigator. Next drag the related Terrirories DataGridView onto the form to automatically set up the related TerritoryBindingSource. The DataGridView will also pick up the Region property on the Territory class which is a reference back to the parent Region object, just edit the columns on the DataGridView through its smart tag and remove it.

When we drag the Region Combobox onto our form the designer generates the proper code to set up the ValueMember and DisplayMember properties on the Combobox. ValueMember doesn't actually matter in this case. It also sets up the related TerriroryBindingSource. The key to setting up related BindingSources (even when using DataSets) is that the child's BindingSource's DataSource property must be set to the parent BindingSource and the child's DataMember property is set to the name of the "relation", in the case of a DataSet this would be the DataRelation, in this case however, it's the name of the child collection "Territories". If we were to set this up in code ourselves it would look like this:

Me.RegionComboBox.DataSource = Me.RegionBindingSource
Me.RegionComboBox.DisplayMember = "RegionDescription"

Me.TerritoriesBindingSource.DataMember = "Territories"
Me.TerritoriesBindingSource.DataSource = Me.RegionBindingSource

When we use the Data Sources window this is all set up automatically in the designer-generated code. The only thing the designer doesn't do for us is fill the data like it does when using DataSets. It's very straightforward. In the code-behind for the form add the following code:

Public Class Form1

    Dim db As New NorthwindDataContext

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Me.RegionBindingSource.DataSource = db.Regions
    End Sub

End Class

The NorthwindDataContext is a class that was generated when we created our model Northwind.dbml. This manages the connection between our LINQ to SQL classes and the database. It also keeps track of changes we make to the Region and Territory objects. All we need to do is set the RegionBindingSource.DataSource property equal to the Regions property on the DataContext. This will initiate the load of all the regions from the database and will create a collection of Region objects. Notice that we aren't specifically loading any territories. By default, LINQ to SQL classes will lazy load related collections so only the related Territory objects will load from the database when the selected Region object's Territories collection is accessed. This happens when we move the position of the RegionBindingSource and the TerritoryBindingSource filters its list. You can see what I mean by adding this code before you set the RegionBindingSource.DataSource property in order to log the T-SQL statements to Debug Output window:

' Add any initialization after the InitializeComponent() call.
db.Log = Console.Out
Me.RegionBindingSource.DataSource = db.Regions

Run the form. When it opens you will see two statements, one to retrieve all the Regions and one to just load the Territories related to the selected Region. As you select a Region in the Combobox, just the related Territories are selected and displayed.

Voila! Notice that in VS 2008 the Combobox will automatically move the BindingSource's position similar to other list controls like the ListBox and the DataGridView eliminating the need to handle the SelectedIndexChanged event to set the BindingSource.Position manually.

In the next post I'll show how we can use the Region Combobox as a lookup list when editing Territories and how we can submit changes to the database.

UPDATE: I placed the code for all the articles on this topic into a Code Gallery project for you to play with.

Enjoy!

Using Data Across Multiple Windows Forms

Beth Massi — Mon, 01 Oct 2007 21:11:00 GMT

Recently I've had more than a few questions about how to handle working with data across multiple forms. If you've watched my Forms over Data video series you know how to create a database, connect to it, save your data properly, and work with data in your code. In this post I'll expand upon those videos and walk through an example of how to create multiple forms that work against the same DataSet. We'll use the Northwind Categories and Products for this example.

There are actually many ways to connect multiple forms to the same DataSet depending on your scenario, but I'll take a common example of displaying editable detail forms from a main form with a grid-style edit like this:

Here we've got a form with two DataGridViews displaying Categories and their related Products. Once you connect to the Northwind Database with the Data Sources Window, you can choose Categories and Products tables and that will create a DataSet and the corresponding TableAdapters for you just like I showed in the One-to-Many video. Once your DataSet is set up then you can drag and drop the Categories and related Products tables from the Data Sources Window to your form. This will set up the two BindingSources and bind the grids properly as well as add a BindingNavigator ToolStrip to the form. Now what we want to do is create a second form that allows us to edit the product details.

First I added a couple lines of code to the TableNewRow event handler on the Categories and Products DataTables so that it would be easier working with the data. In this handler I just set some defaults for the non-nullable fields. To get to the code behind file for your DataSet just right-click on the DataSet in the Solution Explorer and select "View Code". This is where you can add simple validation as well like I showed in the video on adding validation.

Partial Class CategoriesProductsDataSet

Partial Class CategoriesDataTable

Private Sub CategoriesDataTable_TableNewRow(ByVal sender As Object, _

ByVal e As System.Data.DataTableNewRowEventArgs) _

Handles Me.TableNewRow

'Set defaults for non-nullable fields

Dim category As CategoriesRow = CType(e.Row, CategoriesRow)

category.CategoryName = "New Category"

End Sub

End Class

Partial Class ProductsDataTable

Private Sub ProductsDataTable_TableNewRow(ByVal sender As Object, _

ByVal e As System.Data.DataTableNewRowEventArgs) _

Handles Me.TableNewRow

'Set defaults for non-nullable fields

Dim product As ProductsRow = CType(e.Row, ProductsRow)

product.ProductName = "New Product"

product.Discontinued = False

End Sub

End Class

Next I added a new form to the project and again used the Data Sources Window to drag and drop the Product fields I want to edit onto the new form. This time, however, I don't need the BindingNavigator ToolStrip so I just selected that and deleted it and instead added two buttons on the bottom of the form for OK and Cancel.

Next I deleted ProductsTableAdapter in the component tray and then opened up the code-behind of the detail form. I then deleted the Form.Load handler with the Fill code that was auto-generated for us. This is because we do not want to re-fill the DataSet from the database, instead we are going to pass the data to this form from our main form.

The easiest way to pass the data in this situation is to create a new constructor that accepts the DataSet we're editing on the main form and the primary key of the product we want to edit. Then we can set the ProductBindingSource's DataSource and Filter property on the detail form so that the it displays the selected row. This keeps the main form and the detail form edits in sync. In the details form code right under the class definition type "Sub New" and hit enter to auto-generate the correct constructor call, then change the signature and set the ProductBindingSource properties.

Sub New(ByVal ds As CategoriesProductsDataSet, ByVal id As Integer)

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

' Set the DataSource of the BindingSource and then set the Filter

' so that the correct row will be displayed on the detail form.

Me.ProductsBindingSource.DataSource = ds

Me.ProductsBindingSource.Filter = "ProductID = " & id.ToString

End Sub

Then back in the main form I created a button on the ToolStrip that opens this details form. I call ProductsBindingSource.EndEdit() first so that any changes made on the main form are pushed into the DataSet before we attempt to edit the row on the detail form. Then I get the current Product row by casting the ProductBindingSource.Current property to ProductsRow and pass the ProductID to the detail form's constructor along with the reference to the CategoriesProductsDataSet on the main form:

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) _

Handles ToolStripButton1.Click

Me.ProductsBindingSource.EndEdit()

If Me.ProductsBindingSource.Position > -1 Then

'Get the current product row

Dim row As CategoriesProductsDataSet.ProductsRow

row = CType(CType(Me.ProductsBindingSource.Current, _

DataRowView).Row, CategoriesProductsDataSet.ProductsRow)

'Open the product detail form passing the dataset and the product ID

Dim frm As New Form2(Me.CategoriesProductsDataSet, row.ProductID)

frm.Show()

End If

End Sub

You can open the details form modal or modeless. Here I'm allowing the user to open as many detail forms as they want but if you want to only open one at a time then just change the frm.Show() line to frm.ShowDialog() instead. This example demonstrates editing the current row and does nothing if there is no product row selected. You could optionally add a call to ProductBindingSource.AddNew() before the EndEdit() call in this scenario if you need to add a row programmatically before the form is opened. (NOTE: It's important that you add defaults for non-nullable fields like I showed above in the DataTable partial classes if you call AddNew() then EndEdit() in succession.)

The last bit of code is on the detail form that calls EndEdit() or CancelEdit() on the ProductsBindingSource depending on if they clicked Cancel or OK on the detail form, or just closed the form without selecting OK. This will either accept or discard the changes made to the product row on the particular detail form.

Private Sub Form2_FormClosing(ByVal sender As Object, _

ByVal e As System.Windows.Forms.FormClosingEventArgs) _

Handles Me.FormClosing

Me.ProductsBindingSource.CancelEdit()

End Sub

Private Sub cmdCancel_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles cmdCancel.Click

Me.ProductsBindingSource.CancelEdit()

Me.Close()

End Sub

Private Sub cmdOK_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles cmdOK.Click

Me.ProductsBindingSource.EndEdit()

Me.Close()

End Sub

I've attached a complete example to this post that works against the Northwind database. It also shows how to properly save related DataTables like I demonstrated in this video and this post. Remember the key to working with data on your forms is to use the BindingSource object as this class really makes working with data and form controls very simple. For more information see the Understanding Data video.

Enjoy!

Binding Multiple Combo Boxes to the Same Data Source

Beth Massi — Wed, 19 Sep 2007 21:03:00 GMT

Recently the VB Team received a customer bug submitted through Connect that had to do with binding multiple combo boxes to the same data source. The customer was reporting that once a selection was made in one combo, the other combos were also changing. The resolution was "By Design" because of the way the customer was setting up the data binding. We thought it would benefit the community if I posted information on how we helped the customer resolve the issue, so let's talk about what was happening and the solution. But first some architectural background on data binding.

Data binding on Windows Forms involves a few different objects depending on where your data is coming from (i.e. an object or the database directly) but we're going to focus on the database scenario. The TableAdapter is responsible for talking to the database and pulling in result sets into DataTables contained in a DataSet and for sending back the updates -- this is referred to sometimes as CRUD -- Create, Retrieve, Update, Delete. The DataSet maintains the client-side representation of that data (and can also perform validation) by placing the data in DataRow objects that are contained in DataTables. The BindingSource is responsible for maintaining currency, that's the position of the DataRow in the DataTable in which the controls are displaying data from. Here's a simple diagram pulled from my How Do I video on Understanding Data.

Once you get your DataSet and TableAdapters set up you're ready to start data binding to your controls. You use the Data Sources Window to drag fields and tables onto your form and I've shown how to do this in many videos in the Forms Over Data series. When you do this, the DataSet and TableAdapters are dropped onto your form and then a new BindingSource object is created for you. It's this BindingSource that is providing the "glue" between the DataSet and your controls.

There's one video in particular that shows you how to create lookup lists. In this scenario, the data binding is a bit more complex. When you bind a textbox to a BindingSource it's a simple binding to a single field in the current row. When you bind a combo box that acts as a lookup list however, you need two binding sources because you need to be able to display the data from one lookup table and place the value into the table that you're editing.

It's very easy to set this up through the Data Sources Window. First drag all the fields you want to edit onto the from from your edit table by selecting the drop down next to the table and choosing "Details". You can change the lookup fields to combo boxes by selecting the drop down next to the fields and changing the control to a combo box:

Once you have the controls bound to your edit table you can easily bind the combo box list with data from your lookup table. Just drag the lookup table from the Data Sources Window directly over the combo box. Repeat this process for each combo box you want to display that lookup data. Then use the property window or the smart tags on the combo boxes to adjust the ValueMember (the field who's value you want placed in your edit table) and DisplayMember (the field who's value you want displayed in the combo) properties.

This will create THREE separate LookupBindingSources, (four total BindingSources once you count the edit table's BindingSource). This is the key to binding the same lookup data to multiple combo box controls. If you use the same LookupBindingSource instance for the combo boxes then you will end up in the same situation as the customer who reported the bug. If you don't use separate BindingSource instances then as a selection is made in one combo, the others also change to that value, and that's probably not the desired behavior you want.

Why this works this way is because each BindingSource object manages it's own position within the data source that is bound to it. If you use one LookupBindingSource in this example for multiple combos, then as one selection is made, the other will also change. (Keep in mind that using multiple LookupBindingSources does not load multiple instances of the LookupTable's data from the database, it just simply creates multiple BindingSource objects.)

So the customer easily fixed this issue and was very happy with the VB Team's quick response.

Enjoy!

Resizing Images Stored in SQL-Server

Beth Massi — Tue, 28 Aug 2007 21:35:00 GMT

In SQL-Server we can store images inside database tables directly using the Image column type. And with .NET 2.0 data binding it can automatically convert these images (stored as byte arrays) into System.Drawing.Image classes for you. For instance, say you have a table called Pictures that has a column called Picture of the data type Image. You can create a new data source to this table to generate a strongly typed DataSet and use drag-and drop data binding to automatically display the image in a PictureBox. (For information on connecting to your database and creating strongly typed DataSets watch this video.)

If you don't see a PictureBox icon in the Data Sources window next to the Picture column in your DataTable, you may need to select the PictureBox to associate the byte array with the control:

Now you can drag-and-drop the Picture onto your form to set up the data binding to the PictureBox. The binding will automatically handle converting the byte array stored in the column into an image using the System.Drawing.ImageConverter. You can then set the SizeMode property on the PictureBox depending on how you want the image displayed; resized, stretched or otherwise.

But what if you just want to resize the image and not use a PictureBox? In that case you just need to convert the byte array stored in the Picture column into an System.Drawing.Image. Once you have that then you can use methods on this Image class to perform all sorts of transformations. To resize the image, just call GetThumbnailImage and pass it the new size requirements:

Dim resizeImg, origImg As Image

'Get the current row

Dim row As PictureDemoDataSet.PictureRow

row = CType(CType(Me.PictureBindingSource.Current, DataRowView).Row, _

PictureDemoDataSet.PictureRow)

'Convert byte array to image

Using ms As New System.IO.MemoryStream(row.Picture)

origImg = Image.FromStream(ms)

Dim width As Integer = 25

Dim height As Integer = 25

'Resize image

resizeImg = origImg.GetThumbnailImage(width, height, Nothing, Nothing)

End Using

I've attached a simple application that demonstrates these techniques for you to play with. You'll need Visual Basic 2005 and SQL Server 2005 (or Express) to run.

Enjoy!

Building a Secure Login Form (Parameterized Queries Part 2)

Beth Massi — Thu, 07 Jun 2007 08:48:00 GMT

In my first post on parameterized queries I built a simple login form that really was a contrived example meant to showcase how to use the TableAdapter Configuration Wizard to configure a parameterized query. However, since I opened myself up here, I felt it socially responsible to show how to make this a bit more secure by showing you how to store passwords in a secure way in a database.

There are actually many many secure ways to store data in your database including using the encryption features of SQL-Server 2005 which allows you to protect columns inside your database at the database level, independent of the application. Additionally, if we're just talking about user's logins and you're building a multi-tier or SO application then using the ASP.NET membership services is probably your best choice. In next version of Visual Studio, Microsoft made these ASP.NET services easily accessible to any client application (Winforms, WPF, Silverlight) not just WebForms. But what if you're building a single-user application or a client-server app with only a handful of users and you don't have (or want) a web server or maybe you're not using SQL-Server as your database?

The first recommendation for this scenario is don't store passwords at all. If you don't have passwords in your application then you don't have to worry about someone stealing them. Instead, consider using the Windows Identity as the user of your application. This means that your application will not need to store passwords, only user names, because it would be using the logged in Windows user which has already been authenticated through the Windows OS. To access the user name of the currently logged in user from your client code:

Imports System.Security.Principal
...
Dim user As WindowsIdentity = WindowsIdentity.GetCurrent()
Dim userName As String = user.Name

The only thing you would need to do in your database Login table is make the UserName field unique. Then you could write a very simple parameterized query.

SELECT COUNT(*) FROM Login WHERE UserName = @UserName

So your login code would be very simple. If you name the above parameterized query on your TableAdapter "GetLoginByUserName" then it would be something like:

Imports System.Security.Principal
...
Dim user As WindowsIdentity = WindowsIdentity.GetCurrent()
Dim userName As String = user.Name

If CType(Me.LoginTableAdapter1.GetLoginByUserName(userName), Integer) = 1 Then

MsgBox("Welcome to my application!")

Else

MsgBox("Invalid username or password.")

End If

(NOTE: This code assumes that the application is connecting to the database directly and not through a service layer. If you are connecting to a service layer then you need to configure your web server to authenticate Windows users by not allowing anonymous access and only allowing Windows Authentication. For more information, read this.)

However, what if you cannot use this method of authentication? For instance, your application runs on a shared computer that remains logged in under one Windows login, but you require users to login separately to your application. In that case you're going to need to store passwords. However, if we store passwords as clear text in our database, anybody that can get a glimpse of the Login table will have a bunch of user credentials to access the application! The safest thing to do is to use a one-way hashing algorithm and store the hashes in your database table instead. The .NET Framework gives you a lot of help here by providing a handful of proven hashing algorithms in the System.Security.Cryptography namespace. The most common are SHA-1 and MD5. To hash a string using the SHA-1 becomes very simple in .NET:

Imports System.Security.Cryptography

Imports System.Text

...

Function HashEncryptString(ByVal s As String) As String

Dim hasher As New SHA1CryptoServiceProvider()

Dim clearBytes As Byte() = Encoding.UTF8.GetBytes(s)

Dim hashedBytes As Byte() = hasher.ComputeHash(clearBytes)

Return Convert.ToBase64String(hashedBytes)

End Function

Hash algorithms are one-way so it's very very difficult to tell what the original password is from a computed hash. (So if a user forgets their password, you won't be able to tell them what it was.) So when we store user names and passwords in our Login table we can easily hash the value of the submitted password and store that instead. So we're secure now, right? Well almost! Let's take a look at my Login table in this example:

Notice that Beth and Joe both have the same hashed password. This means that both these passwords are the same as clear text as well. An attacker could probably figure out the password by using a dictionary attack on our table. So what can we do?

There's a technique called salting where you take the password and "salt" it with a random value and then hash that. This random value is different for each login. This will create different hashed passwords for the same clear text password, making it extremely difficult to break. To be even more secure, you should store this salt value in a separate table from the passwords. To obtain an appropriate salt (random) value in .NET you can use the RNGCryptoServiceProvider class.

Imports System.Security.Cryptography

...

Function GetSalt(ByVal saltSize As Integer) As String

Dim buffer() As Byte = New Byte(saltSize) {}

Dim rng As New RNGCryptoServiceProvider()

rng.GetBytes(buffer)

Return Convert.ToBase64String(buffer)

End Function

So now we can take the salt value and store that in a table called Salt which has a foreign key to our Login table. Then I can create a couple parameterized queries on my TableAdapters for Login and Salt.

On the LoginTableAdapter we can add a parameterized query called GetLoginByUserNameAndPassword where we pass the UserName and the salted hashed password. The select statement returns a scalar value and we add it through the TableAdapter Query Configuration Wizard just like I showed in my previous post.

SELECT COUNT(*) FROM Login WHERE UserName = @UserName AND Password = @Password

In order to pass the correct value for the @Password parameter, we need the salt value first. On the SaltTableAdapter we can add a parameterized query that returns the salt value for a given UserName called GetSaltByUserName.

SELECT TOP (1) Salt.Salt FROM Salt INNER JOIN Login ON Salt.LoginID = Login.LoginID WHERE (Login.UserName = @UserName)

To make it easier to access the hashing functions we can create a module called PasswordCrypto:

Imports System.Security.Cryptography
Imports System.Text

Module PasswordCrypto

Private Hasher As New SHA1CryptoServiceProvider()

Friend Function GetSalt(ByVal saltSize As Integer) As String

Dim buffer() As Byte = New Byte(saltSize) {}

Dim rng As New RNGCryptoServiceProvider()

rng.GetBytes(buffer)

Return Convert.ToBase64String(buffer)

End Function

Friend Function HashEncryptString(ByVal s As String) As String

Dim clearBytes As Byte() = Encoding.UTF8.GetBytes(s)

Dim hashedBytes As Byte() = Hasher.ComputeHash(clearBytes)

Return Convert.ToBase64String(hashedBytes)

End Function

Friend Function HashEncryptStringWithSalt(ByVal s As String, _

ByVal salt As String) As String

Return HashEncryptString(salt + s)

End Function

End Module

Now that we have our hashing code and our TableAdapters configured, taking our Login form we can add code like this to verify whether a user's entered password matches the hashed password in the Login table:

Try

Dim isOK As Boolean = False

'Get the salt value for this username

Dim saltValue As Object = _

Me.SaltTableAdapter1.GetSaltByUserName(Me.txtUserName.Text)

If Not IsDBNull(saltValue) Then

'Hash the user entered password with the salt value stored in the Salt table

Dim password As String = _
PasswordCrypto.HashEncryptStringWithSalt(Me.txtPassword.Text, saltValue.ToString)

'Now check the Login table to see if this hashed password matches

isOK = CType(Me.LoginTableAdapter1.GetLoginByUserNameAndPassword( _
Me.txtUserName.Text, password), Integer) = 1

End If

If isOK Then

MsgBox("Welcome to my Application!")

Else

MsgBox("Invalid user name or password.")

End If

Catch ex As Exception

MsgBox(ex.ToString)

End Try

So this is how we can store passwords in a secure way in our database, even if our database does not support encrypted columns. With .NET, accessing hashing algorithms is a snap. I've attached a complete sample that demonstrates these techniques (as well as saving users passwords and salts) so that you can learn from them. You'll need Visual Studio or Visual Basic Express and SQL-Server Express installed to compile and run the sample.

Enjoy! And be secure!

Many-to-Many Data Binding

Beth Massi — Wed, 30 May 2007 21:25:00 GMT

In my Forms over Data videos series I show you how to create a one-to-many data entry form in video #3. Recently, I've had a few people ask how to create a form that displays a many-to-many relationship so I thought I'd post on how to do that today.

You can actually think of a many-to-many relationship as two one-to-many's and depending on how you are designing your UI, you may be displaying it one way or the other. Take the many-to-many relationship Orders --< OrderDetail >-- Product where an Order has many OrderDetails and Product also has many OrderDetails. Typically when users are entering the data, we're choosing to display one of the one-to-many relationships for editing. For instance we could have users enter one order at a time that displayed the Order fields as textboxes and the OrderDetails in a grid. Then we could choose Products from a dropdown list in the OrderDetails grid. (The Products are edited elsewhere on the Product Catalog form for instance.)

However when we want to display or report on data, we don't necessarily need it to be easy to enter the data, we want to be able to easily see the data instead. And depending on your UI and your target users, you still may be able to easily guide the users through editing. But what if we wanted to just get a list of all the Products on a particular Order? Or know all of the Orders for a particular Product? These are easy SQL queries but what if we want to reuse a DataSet we already have built, or we want to allow editing of the data?

Say I have a DataSet of what we're describing now: Orders --< OrderDetail >-- Product.

We want a form that will allow the users to scroll through a list of orders and see all the products on that order. So we just want two grids on a form, one of Orders and one of Product. To get the filtering of rows set up properly, we will use the same exact technique for setting up automatic filtering on a One-to-Many form, but with one additional manual step which I'll show you isn't so bad.

To get the form quickly designed and set up, from the Data Sources window I just drag the Orders table and it's related OrderDetails onto the Form. Make sure you select the related OrderDetail table under the Order table otherwise you won't get the automatic filtering set up on the BindingSources it creates for you.

Then drag the Product table onto the form and delete the OrderDetails grid becasue we don't want to display that to the user. This process sets up all the binding components in the Form's component tray properly for all three of our DataTables. It should look something like this:

Now when the position changes in the OrdersBindingSource, the OrderDetailBindingSource will filter the proper rows based on the selected Order. All that's left to do is to filter the ProductBindingSource based on this list of OrderDetails. So open up the code behind and we're going to handle the OrderDetailBindingSource.ListChanged event. In this handler we'll create the filter to apply on the ProductBindingSource based on the filtered rows in the OrderDetailsBindingSource.

Private Sub OrderDetailBindingSource_ListChanged(ByVal sender As Object, _

ByVal e As System.ComponentModel.ListChangedEventArgs) _

Handles OrderDetailBindingSource.ListChanged

If Me.OrderDetailBindingSource.Count > 0 Then

Dim filter As New System.Text.StringBuilder()

For Each orderDetail As DataRowView In CType(Me.OrderDetailBindingSource.List, DataView)

If filter.Length <> 0 Then

' Adding criteria

filter.Append(" OR ")

End If

filter.Append(String.Format("ProductID = {0}", orderDetail!ProductID))

Me.ProductBindingSource.Filter = filter.ToString

Else

' display no rows

Me.ProductBindingSource.Filter = "1 = 0"

End If

End Sub

Now when we run this form, as the user scrolls through the Order rows in the first grid, the Product rows are displayed in the second grid for the entire order. To really understand what is going on just take a look at how the BindingSources are set up in the property sheet. The OrdersBindingSource has the DataSet as the Datasource and the DataMember is set to "Order". Then the OrderDetailBindingSource has its Datasource set to the OrdersBindingSource and its DataMember to "OrderDetails". This sets up the One-to-Many chaining and automatic filtering as explained in video #3 of my Forms over Data videos series.

All that's left in this case is getting the filtering on the ProductBindingSource which has the same Datasource as the OrderBindingsource, the DataSet, but its DataMember is set to "Product". We do this by simply handling the ListChanged event on the OrderDetailBindingSource. These grids are editable too, just be careful that your users understand the filtering that's being applied when working with the Product grid.

Happy Data Binding!