Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
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) _
'Set defaults for non-nullable fields
Dim category As CategoriesRow = CType(e.Row, CategoriesRow)
category.CategoryName = "New Category"
Partial Class ProductsDataTable
Private Sub ProductsDataTable_TableNewRow(ByVal sender As Object, _
ByVal e As System.Data.DataTableNewRowEventArgs) _
Dim product As ProductsRow = CType(e.Row, ProductsRow)
product.ProductName = "New Product"
product.Discontinued = False
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.
' 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
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) _
If Me.ProductsBindingSource.Position > -1 Then
'Get the current product row
Dim row As CategoriesProductsDataSet.ProductsRow
row = CType(CType(Me.ProductsBindingSource.Current, _
'Open the product detail form passing the dataset and the product ID
Dim frm As New Form2(Me.CategoriesProductsDataSet, row.ProductID)
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) _
Private Sub cmdCancel_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdCancel.Click
Private Sub cmdOK_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdOK.Click
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.
I'm developing ERP project for Furniture company. I have 5 Forms in that project. They are,
1) Warehouse 2) Stock Maintanence 3) Purchase 4) Sales and 5) Report Form
My question is,
1)How can prevent CONCURRENCY PROBLEM and Unit-In-Stock is upto date?
2)And also, If I take report it is always slow. even single transaction the report is taking time.
3)How can I improve the performance?
How can I design DAL in this case?
Can you provide links or samples...please?
Thanks for your very good article.
I have one question though: how do you perform the same task if your data come from a LINQ query?
The "Filter" property would not work in this case, the "ProductRow" should probably be retrieved differently... but i do not know how to do it.
Can you please point out how to modify your example to make it work with LINQ? Thanks a lot.
Really nice. Can you please point out how to do the same using linq?
I have been looking everywhere but cannot find anything.
I just spent a week trying to come up with a solution to connect a common form(s) to several dynamically different datasets... this approach just got me on the right track. My thought is to open a data control form in a hidden state, and then run all the data views and manipulation procedures through the hidden form.
It would actually be better to do this as a class, but I have had a lot problems instantiating the datasets, tableadpters and table references in the class. This sound easiers, although not as clean as I'd like. Any suggestions?
I am very annoyed that all samples always requires SQL Server instead of MS Access. I have never succeded to install SQL Server Express in any of my wondows 7 computer so I cannot test these samples many people have the same problem sql team just faint to ignore :(
I hate this forcing of sql server everywhere whereas most advanced users use MS Access.
thank you very very very much! i've been stucked for weeks until i found your solution. really appreciate it. thank you! :)
can you share the dataset between two PROJECTS in the SAME SOLUTION, too?