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))

        Next

        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!

Come down to the Microsoft San Francisco office on One Market for the SF BAADD .NET meeting starting tonight at 6:30pm. I'll be speaking on The Future of Visual Basic, the same talk I did at DevTeach, but with many more demos because this time I'll have enough time! :-) Please RSVP to dbr@outformations.com. Here's the details:

 The next meeting of the BAADD San Francisco .NET Developer's User Group will be Wednesday, May 30, 2007 from 6:30-9:00 p.m. at the Microsoft Office, One Market Street, 2nd Floor, San Francisco, CA.

Food will arrive at 6:30, and the meeting will get underway formally at 7:00.  If you arrive after 7:00, you will need to have the security guard call me to bring you up.

This month Beth Massi will present on The Future of VB.

More than any other .NET language, Visual Basic is evolving in dramatic ways to provide its legions of developers with untold levels of productivity for developing enterprise data-aware applications. The next version of VB evolves from the influences of dynamic languages such as Ruby and from the field of existing VB6 developers struggling to migrate their applications to .NET. During this session we will walk through the new language improvements coming with Visual Basic 9 and beyond.

Please RSVP to Don Robins at dbr@outformations.com by noon on Wednesday, April 30th, so we can make sure we have enough food and drinks on hand, and so building security knows you are coming.  I hope you will be able to attend.

As always, we will have a drawing for door prizes including a full standard licence for VS2005, SQL Server 2005 and Biztalk Server! Remember you must be present to win. Meetings are free for current BAADD members, and there is a guest fee is $10 at the door for nonmembers. Annual dues are $75, and you can join at the door.

Please also take a moment to forward this email to friends and colleagues who may be interested in this meeting.  These meetings have proven to be extremely valuable, and the more people we can bring together, the better.

Don Robins
Leader BAADD .NET Developer's User Group
President, Bay Area Association of Database Developers

BAADD .NET DEVELOPER USER GROUP MEETINGS ARE HELD EACH MONTH IN SAN FRANCISCO  
San Francisco Meetings (Last Wednesday of each month)

This webcast with John Stallo is starting now!  This time John will go deeper into WCF from the Visual Basic point of view.

Visual Studio 2005 made some big improvements in the way applications work with data, from BindingSources to the Data Sources Window all the way to a free version of SQL-Server called SQL-Server Express. I've had a few customers get stuck on using SQL-Server Express local database files with their project so I thought I'd post on how this works.

The problem that people are having is that when you add a local database file to your project, VS will happily create the proper connection string for you and immediately get you connected and building DataSets, but when you run the project and save some data, then run the app again, you'll notice that your data is gone. Which means you spend time trying to debug your dataset or your connection when your data actually saved just fine.

In my Forms over Data Videos I build a database from scratch which attaches the database to my SQL-Server Express database instance. This is the same experience when creating databases on remote SQL-Servers. The actual database file is located in the Data directory under the appropriate SQL-Server instance, in my case: \\BETHFACE\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data. However, in order to send the data with my code samples I detached the database and added the database MDF file into the project itself and changed the connection string in My.Settings -- at this point it became a local SQL-Server Express data file.

If you look in those sample projects, open the Solution Explorer, and look at the Properties for the MDF file you will see that the Build Action is set to Content and the Copy to Output Directory is set to Copy if Newer. (If not, tell me - I must have missed one <g>). By default, MDF files are treated like any other content file and so it is set to Copy Always. When you change it to Copy if Newer, the database file will be copied to your build output directory only if you made any schema changes to the file or modified the data in VS. This is because there are actually two copies of your MDF file (as a matter of fact there are two copies of any of your files with Build Action = Content).

One copy is in your project folder, this is the file you work with when making schema changes with the VS tools. The VS tools always use this connection. However, you'll notice that the connection in the My.Settings uses a macro called |DataDirectory| to open the connection when your app is running (AttachDBFileName=|DataDirectory|\OMS.mdf). The |DataDirectory| expands to the location the EXE is running during runtime. So because of the default behavior of always copying out content files to the build directory, your database gets copied and it just looks like your data is not saving, sending you on a wild goose chase.

Just remember to mark your databases as Copy if Newer that way you only "lose" data when you make changes to the structure of the database itself or if you add data via the Visual Studio designers. For more information check out this post as well. I'll be creating a video on how to work with local database files including how to deploy them to your users in the next series.

UPDATE: Here's that video - #10

Paul posted an interesting tid-bit of news yesterday if you missed it, yes it's true, the JavaScript compiler they wrote for Silverlight 1.1 is written in Visual Basic! Score for the home team! :-)

I've been asked by a couple readers on how to pass parameters into a SQL statement to your database using the TableAdapters. I actually created a video on how to do this in the context of a search query (play video | download video | entire series). It's really easy to do this using the TableAdapter Query Configuration Wizard. Let's create a quick login form for an example.

I've got a table called Users in my database and I've created a Dataset called UsersDataset by opening up the Datasources window and adding a new datasource to my database. Then I created a form called LoginForm and from the Toolbox I dragged some labels and textboxes for the entry and a couple buttons, OK and Cancel, onto the form. I then set the PasswordChar property on the PasswordTextbox to "*". This indicates that the textbox should display this character instead of what the user types, but the value of the Text property will still be what the user enters.

(By the way, this example does NOT demonstrate a secure way of writing login forms. We'll be passing what the user enters directly into the database which stores the password in clear text. It is NOT safe practice to store clear text passwords in your database. I'll post a follow-up that talks about techniques we can use to protect users' passwords, especially if we need to store them in a database. For now, let's concentrate on how we add parameterized queries to our TableAdapters. UPDATE: Here's the follow up.)

Now at this point I build the project and in the WindowsApplication1 Components section at the top of the Toolbox I can now see the UsersDataset and the UsersTableAdapter listed (it's important that you build your project to get the components listed in the Toolbox). I drag the UsersTableAdapter onto the LoginForm which creates a component in the tray named UsersTableAdapter1.

So now that we've got that all set up, we're going to add a parameterized query to the UsersTableAdapter. First open up the UsersDataset and right-click on the TableAdapter in the designer and choose "Add Query".

This will open up the TableAdapter Query Configuration Wizard. First it asks you to choose a command type. Keep the default selected as "Use SQL Statement" and then click Next. Next it asks you to choose a query type. We're going to select the second option, to create a SELECT statement that returns a single value, not a result set.

Next we'll write the parameterized query as "SELECT COUNT(*) FROM Users WHERE UserName = @UserName AND Password = @Password". Then click Next and name the function "Login". This will create a TableAdapter method called Login that accepts a UserName and Password as a parameter and returns an Object which will be an Integer in our case since we're specifying to return the COUNT(*) of the rows that match the WHERE clause.

Now back to the LoginForm, we can double-click on the OK button to add a Click event handler.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

    Handles Button1.Click

    Try

        If CType(Me.UsersTableAdapter1.Login(Me.UserNameTextBox.Text, _

                 Me.PasswordTextBox.Text), Integer) > 0 Then

            MsgBox("Welcome to my application!")

        Else

            MsgBox("Invalid username or password.")

        End If

    Catch ex As Exception

        MsgBox(ex.ToString)

    End Try

End Sub

Now when we run this example and enter a user name and password that is contained in the database table Users, we get the greeting message otherwise we get the message that our user name or password is invalid.

As you can see it's really easy to add parameterized queries to your TableAdapters using the wizards. There's even a simple query builder you can use to help you write your queries and test them out before they are generated on the TableAdapter. In a follow up post I'll show you how to add secure password storage to your application using hashing and encryption.

Based on all the great (and numerous) amounts of positive feedback on the first series of videos, I'm ramping up to start the next wave. I took into account each and every request I got and came up with the following areas of focus - all related to what you can do now with VS 2005:

Object Binding  
Reporting & Printing
Managing Database Changes
Deployment
Interop Forms
Validation

These areas will span at least 10 videos that I'm shooting to have done by mid-June so stay tuned! And once again, keep the feedback coming!

A bunch of speakers went to a great Italian place the last day of DevTeach and Rob took a funny picture of me, Nick Landry and Scott Belware. Both born in Montreal (though not living there anymore) Nick and Scott have bald heads so I figured my Visual Basic love could flow into them easier this way :-).

I just finished my last session here at DevTeach called "The Future of Visual Basic" and it was another packed room. I have to say I really enjoyed giving this talk because the audience was awesome. I just love the Visual Basic community. When you get a bunch of VB geeks in a room talking about language, data access and n-tier applications the excitement is just too much.

I (as always) had way too much content to present in way too little time so I had to cut out the VB Silverlight demo but fortunately Alan did two sessions on building Silverlight apps and more than half the people had seen it already so I didn't feel so bad.

I did 50 minutes of LINQ and VB language features including XML literals and people were very jazzed. I got some good questions on anonymous types and LINQ to SQL especially. One person had the same question I had when I first saw LINQ to SQL: "What is the T-SQL code it's writing and how do I know it's good?" <paraphrase>. I told him about the Log feature of the DataContext so he could see for himself. For more information on the LINQ to SQL and the O/R designer check out Young's webcast.

I also spoke to the new tools around making n-tier applications really easy to build with the new n-tier datasets and WCF proxy type sharing. I didn't have time to build the application from scratch but I did show a completed solution I built from scratch in my Datasources and Data binding talk. You can also see John do it here in this webcast.

Finally I spent 20 minutes on the DLR and VB 10 (VBx). I showed an implementation of Visual Basic on the DLR that showed interactive programming and dynamic event handlers. People were VERY impressed with this (lots of applause when I showed the dynamic event handler). This version of VB is NOT VBScript, it is VB on .NET and the DLR. For more info, check out Amanda and Paul's posts.

I think what made the talk most enjoyable is that I had a C# developer come up to me at the end and tell me that based on all these new features and the bright future of our language, that he's making the switch to VB!! Yea! Also, everyone chipped in and Dave came up at the end and gave me flowers! Wow! What an awesome community!!!!