- Using the DataSet Designer to create a strongly-typed DataSet based on your database.
- Extending the DataSet by adding additional methods.
- Saving, and using, the DataSet as a Class Library.
- Building a WinForm Application, initially with a DataGridView.
- Using BindingSources with the DataSet.
- Synchronizing DataTables of the DataSet to create Master/Details Forms.
- Using TableAdapters to update the database from the DataSet.
- Synchronizing two forms.
- You need to be confident with C# fundamentals - an 'advanced beginner' possibly.
- Some familiarity with WinForms is required, although I will guide you through the use of a DataGridView.
- Some familiarity with ADO.NET is needed. For example, knowing the difference between a DataSet and DataTable.
- You need to know how to design, build, and connect to a database.
This last requirement is necessary because, although I provide enough information to build my sample database, I leave the details of which database to use up-to you. I used SQL Server Express but you could use SQL Server, Compact Edition, a SQL localdb, SQLite, etc.. A necessary step is that you must be able to connect to the database from the Database Explorer (or Server Explorer) Window, so that it is then displayed in that window. Necessary, because we need to drag it from that window onto the DataSet Designer surface. (It is possible to build the DataSet without dragging, but I don't explore this in the tutorial.)
Hint: Whichever database you will use, create a database (CREATE DATABASE StaffDB) and check that you can connect to it from the Database Explorer window, and that it then appears in its databases-list. Then you can go back to it and build the tables.
You could use a database of your own if you want; as long as it contains a couple of tables with relationships between them you should be able to follow along.
Why no database guidance?
There are many ways to work with a database and, unfortunately, there isn't one that can be considered as the right way. These include:
- Dropping back to the dark-ages with ADO and recordsets.
- ADO.NET - the connected layer: using connection, commands, DataReaders and executing queries.
- ADO.NET - the disconnected layer: Filling DataSets and updating with data adapters.
- The drag-and-drop approach: dragging a DataGridView onto a form and using its Tasks button to assign a DataSource.
- Creating a strongly-typed DataSet as we will do.
- Manually building a strongly-typed DataSet, or object-model, to work with the database. That is, providing a DAL - a Data Access Layer.
- Using an ORM (object-relational mapping) framework such as the Entity Framework or NHibernate (there are others).
DAL is a term that Microsoft made up (I suspect). The intention is that we write code against a model of the database, which uses familiar OOP concepts and objects, rather than communicating with the database directly. That is, as OOP-whizzes, we want to instantiate objects and invoke methods of these objects, not to grapple with unfamiliar (and database-specific) SQL-statements. cf. code separation.
The ideal is that we extract all the database-specific code, and sql-statements, into a data-layer which we can ignore. If all goes well then we should, in principle, be able to switch this low-level layer from, say, SQL Server to Oracle (or MySql..) with the minimum of fuss; perhaps even just with a single change from one connection-string to another.
For me, creating a strongly-typed DataSet using the DataSet Designer is a further step to understanding database-interaction, and data-binding. If you do some searching you will discover some dissenting voices about this process. In particular, the use of, and trust in, the DataSet Designer. If, after this tutorial, this is an approach that you wish to consider then you should do some further research.
Even if you should decide not to pursue this particular approach I believe there is a lot to be learnt here. In particular, I believe it provides a good stepping-stone before examining something as big as the Entity Framework.
You might add an additional field to this table bonus bit NULL. I added this to the typed-DataSet later-on using the steps given in the spoiler. I'll leave it up-to you if you feel happy to leave this until later.
Edited: HolidayID should be the primary key.
Creating Our Class Library
Create a new Class Library Project named StaffDAL. Planning ahead, you'll want to save it in an accessible area. Use Solution Explorer to delete the default class-file that it creates.
Make sure the Database Explorer window is open and pinned and use it to connect to the database (or one of your own) so that it appears in the list. Expand the tables folder.
Go to the Project menu, Add New Item, (possibly) the Data area on the left, and choose DataSet. Name it StaffDS. Drag the tables from the Database Window onto the Design surface. The DataSet Designer should be populated with DataTables that mirror the database-tables.
If you show all files in Solution Explorer and open StaffDS.Designer.cs you'll see some of the magic that's just happened. My version has 3480 lines of code!! DO NOT CHANGE THIS FILE. Once you've had a glance through it make sure that you close it.
This file should not be changed. Even if you were adventurous enough to do this your changes will be over-written when its re-built.
Creating and Editing Typed Datasets
If you double-click an empty area of the DataSet Designer (or right-click StaffDS.cs in Solution Explorer and choose View Code) you should find this:
Partial Class StaffDS End Class
Notice that this is a partial class. We can extend our DataSet by adding additional members here - NOT in the designer file.
A strongly-typed DataSet does not on its own (in my opinion) constitute a DAL. Yes, it does provide a level of abstraction, but you will notice in several places in my code that I refer to fields as strings ("StaffID") and we still need to have some understanding of the underlying structure and data. (Admittedly, our example needs to be kept to a reasonable size.) If we continued to extend our Class Library then it could, eventually, be considered a DAL.
The term DAL is not formalized enough to define what constitues a 'layer'. If we cannot see any sql, and we use objects, their methods and properties (with limited use of strings), then I would consider this a 'layer'. But this is just a casual remark, seek out wikipedia for more considered definitions.
Add the following method that we will use to fully populate our DataSet from the database.
Partial Class StaffDS Shared Sub PopulateStaff(ByVal pStaffDS As StaffDS) Dim adaptStaff As New StaffDSTableAdapters.StaffTableAdapter() adaptStaff.Fill(pStaffDS.Staff) Dim adaptDepts As New StaffDSTableAdapters.DepartmentTableAdapter() adaptDepts.Fill(pStaffDS.Department) Dim adaptHols As New StaffDSTableAdapters.HolidayTableAdapter() adaptHols.Fill(pStaffDS.Holiday) End Sub End Class
Notice that we have explicitly (strongly)-typed TableAdapters, one for each table (DataTable) of our DataSet. (We also have a TableAdapterManager which can be used to perform hierarchical updates; that is, across several related tables.)
Choose the View menu, Class View (in Express) to discover more about our class-library.
Save, Build (for Release) and Close our library.
Using Our Library
Create a new WinForm Application named StaffApp1 (or whatever you fancy). Rename the form to frmStaff.
Go to the Project menu, Add Reference. You will probably need to click Browse.. and track down our library. You are looking for the .dll file which will be in the bin folder, and either the Release or Debug folder. Tick it.
In the form's class-file you can add:
Design the form (frmStaff) shown earlier. I use fairly obvious names:
btnFirst, btnPrev, txtFullName, dgvStaff (the DataGridView)
Full code of the form: (for reference)
Imports StaffDAL Public Class frmStaff Private _staffDS As New StaffDS() Private _bsStaff As New BindingSource() Private _bsDept As New BindingSource()
I try to keep the number of instance-variables (fields) of a form to a minimum, mainly just for objects that need to persist throughout the lifetime of the form.
We need a new instance of our typed-DataSet. Originally I also held references to a StaffDataTable and DepartmentDataTable, but these are always available as _staffDS.Staff and _staffDS.Department anyway. I do, however, create a useful local reference in the Load event (_staffDT).
BindingSource Class :MSDN
The BindingSource component serves many purposes. First, it simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources.
In addition, the BindingSource component can act as a strongly typed data source.
The BindingSource is fab. It is what Microsoft encourages us to use and performs the roles previously worked by a CurrencyManager and BindingContext (and more).
Currency Management is nothing to do with pounds and pence. it is about maintaining a list by keeping track of the current item. The current item's Position.
Private Sub frmStaff_Load(sender As Object, e As EventArgs) Handles MyBase.Load StaffDS.PopulateStaff(_staffDS) Dim _staffDT As StaffDS.StaffDataTable = _staffDS.Staff
In the Load-event we call the method that we built earlier (in our library) to populate all three of our DataTables. We shouldn't always do this so you could add methods to the library to populate individual DataTables.
As part of our progress towards a DAL I would move all uses of our TableAdapters into the library as methods.
'add a calculated column to Staff DataTable Dim cFullName As DataColumn = New DataColumn() cFullName.DataType = System.Type.GetType("System.String") cFullName.ColumnName = "FullName" cFullName.Expression = "FirstName+' '+LastName" _staffDS.Staff.Columns.Add(cFullName)
I want to use a TextBox on our form to display the fullname of the current staff-member; that is, the one currently selected in the DGV (DataGridView). We cannot bind a control to an expression. I (very briefly) thought about having two hidden textboxes displaying the first and last-name, and a third one to concatenate them. But I would then have had to use some event(s) to update the textbox's Text. Very messy.
The expression could be added as a calculated-field in the (database) table. I don't want to change the database design just to enable a simple expression.
It should, I believe, be possible to add the expression into our library. It wasn't obvious how this could be achieved, or how complicated it might be. Expression is the solution to this. StaffDS.Staff is, after all, still a DataTable, so there is no reason why we cannot added, or remove, columns. We can then simply refer to this new column in the TextBox (even though it won't be displayed in the DGV).
It is possible to add queries to our TableAdapters and to use LINQ to DataSets.
_bsStaff.DataSource = _staffDS _bsStaff.DataMember = _staffDT.TableName _bsDept.DataSource = _staffDS _bsDept.DataMember = _staffDS.Department.TableName
The DataSource for both of our BindingSources is the same DataSet. The DataMember identifies the particular DataTable to bind to. (Remember that _staffDT is a local reference to _staffDS.Staff.)
In actuality, we are binding to a list which could be, practically, any kind of list. It is this list that the BindingSource will maintain currency for; that is, maintain the current position of.
dgvStaff.AutoGenerateColumns = False Dim colID As DataGridViewTextBoxColumn = New DataGridViewTextBoxColumn() colID.DataPropertyName = _staffDT.StaffIDColumn.ColumnName 'or just "StaffID" colID.HeaderText = _staffDT.StaffIDColumn.ColumnName dgvStaff.Columns.Add(colID)
If you want to test this quickly you could set AutoGenerateColumns to true and remove all the code that defines the individual columns. You would just need this line at the bottom of the Load event:
dgvStaff.DataSource = _bsStaff
This works (and is a better demonstration of code-separation) but I set out to use the DGV with a particular, and very common, aim in mind. If you were to try the auto-generated version it would just display the DeptID number. I want it to display the Department-name, and use a combobox so that I can select a different Department.
We could autogenerate the columns, then remove and insert a new one for the Department. This is messy and, besides, I want to format some of the columns, and also use a Checkbox for Bonus.
Unfortunately, we now need to specify the columns individually. This is a step-back from the code-separation we are keen on. [I suppose it would be possible to add some (perhaps complicated) code to our library that matches data-types to DGV column-types, and other column properties.]
Dim colID As DataGridViewTextBoxColumn = New DataGridViewTextBoxColumn() colID.DataPropertyName = _staffDT.StaffIDColumn.ColumnName 'or just "StaffID" colID.HeaderText = _staffDT.StaffIDColumn.ColumnName dgvStaff.Columns.Add(colID)
Is StaffIDColumn.ColumnName better than just "StaffID"? It is more OOP but I can't see that we are gaining much. For our simple application/demonstration we can use either.
I'll leave you to study the other column definitions, so let's have a look at the DeptID:
Dim colDept As DataGridViewComboBoxColumn = New DataGridViewComboBoxColumn() colDept.DataPropertyName = "DeptID" colDept.HeaderText = "Department" colDept.DataSource = _bsDept colDept.ValueMember = "DeptID" colDept.DisplayMember = "Department" colDept.DefaultCellStyle.NullValue = "(please select)" dgvStaff.Columns.Add(colDept)
DataPropertyName is the name of the data source property or database column to which the column is bound. Because we have our DataSet it is the data source property (which corresponds to a database column).
ValueMember is the value that will be retrieved, and stored, from the drop-down list.
DisplayMember is what we will see in the combobox.
NullValue is some temporary text (or value) that we can display if the cell is blank (or the database-field is null).
For existing Staff how does it know which Department to display? Because:
- The DataSource for both BindingSources is the same DataSet
- The DataMember (the list) for each is a different DataTable but..
- The DataTables are related.
When we built our DataSet, Relations between the DataTables were created that mirror the relationships between the database-tables.
Each BindingSource only has one CurrencyManager. It can only maintain the current position of a single item in its list. While the cursor is in a particular row of the DGV a particular item is selected in the combobox. Therefore, to synchronize two lists, we will always need two BindingSources. They remain synchronized by means of a relation between them (or BindingSources can be chained - see the second form example).
dgvStaff.DataSource = _bsStaff txtFullName.DataBindings.Add("Text", _bsStaff, "FullName")
The first line is what keeps the DataSet in-synch with the DGV. The second is how we bind a single control (other than a DGV or other list-source) to a single data-member (FullName). It is actually the "Text" property of the TextBox that is bound to the data-member.
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click _bsStaff.MoveFirst() End Sub Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click _bsStaff.MovePrevious() End Sub
Changing the Position of the BindingSource has the same effect in the DGV. A BindingSource is very well behaved and doesn't cause an error if, for example, there is no previous item.
Homework: However, using AddNew() twice in succession will cause an exception. You should consider adding exception handling code, together with EndEdit() as seen in the following code. Given this, I think it sensible to at least use a try-catch block for each of these calls.
There is a BindingNavigator Control if you prefer. But it is simple enough to create our own version, which we might create as a custom control.
Private Sub btnSaveAll_Click(sender As Object, e As EventArgs) Handles btnSaveAll.Click Dim adaptStaff As New StaffDAL.StaffDSTableAdapters.StaffTableAdapter() Try _bsStaff.EndEdit() If _staffDS.HasChanges Then adaptStaff.Update(_staffDS.Staff) Else MessageBox.Show("Nothing has changed.") End If Catch ex As Exception MessageBox.Show("Update failed: \n" + ex.Message) End Try End Sub
The BindingSource automatically keeps the DGV and DataSet in step. TableAdapters are then used to Update the database-tables to reflect the current state of the DataTables. The above code, and the following, could be methods within our library.
Private Sub btnCancelChanges_Click(sender As Object, e As EventArgs) Handles btnCancelChanges.Click Dim adaptStaff As New StaffDAL.StaffDSTableAdapters.StaffTableAdapter() _bsStaff.CancelEdit() _bsStaff.ResetCurrentItem() _bsStaff.ResetBindings(False) adaptStaff.Fill(_staffDS.Staff) End Sub
Notice that when you run the form you can click on the DGV column headings to sort, and the BindingSource keeps the DataSet in-sync.
The Second Form
Private Sub btnNextForm_Click(sender As Object, e As EventArgs) Handles btnNextForm.Click Dim frm2 = New frmNext2(_staffDS, _bsStaff) frm2.Show(Me) End Sub
The second form (image shown earlier) is passed the DataSet and the Staff BindingSource. Neither is essential but by passing, in particular, the BindingSource they share it - and the two forms remain in-sync. If you position the forms next to each other then you will notice this more easily.
Full code for second form:
In this form I wanted to use standard Controls for the Staff details, and a DGV to show related holiday details. As Next or Previous are clicked the Holiday details change to reflect the current staff-member. New holiday details will also be stored for this staff-member.
Private _bsHols As BindingSource = New BindingSource()
We need a new BindingSource for the holiday details.
Public Sub New(ByVal pStaffDS As StaffDS, ByVal bsStaff As BindingSource) InitializeComponent() _staffDS = pStaffDS _bsStaff = bsStaff End Sub Private Sub frmNext2_Load(sender As Object, e As EventArgs) Handles MyBase.Load txtFirstName.DataBindings.Add("Text", _bsStaff, "FirstName") txtLastName.DataBindings.Add("Text", _bsStaff, "LastName") _bsHols.DataSource = _bsStaff _bsHols.DataMember = "FK_Holiday_Staff" dgvHols.AutoGenerateColumns = True dgvHols.DataSource = _bsHols End Sub
The Holidays BindingSource has its DataSource set to the Staff BindingSource; the BindingSources are chained.
The DataMember of the Holidays BindingSource is set to a Relation. It is the name of the foreign-key constraint in the Holidays table, which, more importantly, the DataSet Designer also uses as the name of a Relation in the DataSet. You may need to check this name in the library (or the database).
In the DataSet Designer you can point at a line between tables and the name of the Relation will appear.
The DataMember refers to a list. "FK_Holiday_Staff" is the name of a DataSet-Relation which identifies this list.
Study the rest of the code, it is not too dissimilar to that for the the first form.
Further Topic Suggestions:
- Sorting, Filtering of the DGV
- DGV events
- Linking two DGV
- Many-to-many data binding
- LINQ to DataSets
- Form Validation
- Extending the typed-DataSet
- The Entity Framework
This post has been edited by andrewsw: 10 April 2014 - 06:39 AM