Two continuation-articles of this one:
- Databinding for Beginners
For those, who don't know it, Databinding is a new world of programming. But all Databinding requires a right understanding of what a DataModel is.
- Programming against typed Dataset
continues unfolding the subject so far, that you can develop databinding-driven Applications with several Forms, with avoidance of nasty code-smells.
Table of Contents
- The hierarchical Model
- The relational Model
- Technical Terms
- Set up a relational Datamodel
- Coding against a typed Dataset
- "DatasetOnly" - Data-Application without Database
- For Beginners: Learn Databinding before Database-Access
- The Sample-Application
The hierarchical Model
As object-oriented Programmer we always deal with hierarchical structured Objects, that means: An Object contains other objects, either single Objects or even lists of other objects, for example a Form has a ControlCollection, containing many Controls, eg. Textboxes, and each Textbox has a (single) vertical Scrollbar, and of course it has a Text, which has some chars.
This is the hierarchical model, where each object is either a (single) Attribute/Property of another object, or it is an element of a collection, which is property of the Owner-Object. And of course an object better should be element of only one collection - otherwise things become difficult or even inconsistent.
Hierarchical models - no matter how complex - can be visualized as tree-views - for instance watch the document-outline of a (not completely simple) WinForms-Form:
You see: The Form contains a TabControl, the TabControl contains TabPages, TabPages contain SplitContainers, SplitContainers contain SplitterPanels, SplitterPanels contain Groupboxes, Groupboxes contain DatagridViews, DatagridViews contains DatagridViewColumns...
Everything has its proper defined place within that hierarchical model.
But the thinking of an OOP-Programmer itself must change, when he comes in contact with relational modelling. Because unless reality often fits into a hierarchical model, it often does not fit ;P
See this little Xml:
It is about Persons and their Profession - each Person has one Profession, and a Profession has many Persons. The datamodel is structurated hierarchical and everything seems fine.
But how would you model, if Gustav has more Professions than one - in the evening he may style his hair and go clubbing (or however a punk does his job)?
Now we have two Gustavs, and the data is inconsistent, corrupted, bullsh*** - name it as you like. The model is wrong, when in reality it is one Gustav, and in your model it is two.
Long story short: hierarchical models are not sufficiant. Even more un-modellable would be a Gustav with no profession at all :wtf:
The relational Model
Since hierarchical structures are irreparable unusable to model reality, the nesting of objects is banned completely from databasing. There was found something better: one can emulate nesting.
A relational Model only contains simple, flat lists of objects (named "table"). And instead of nesting records into (Parent-)records, a child-record only gets a Reference to its Parent.
(By the way, in Xml a table is represented simply by several Xml-Elements of same Tag-Name):
<Profession Name="Police-Man" ID="1"/>
<Profession Name="Punk" ID="2"/>
<Person Name="Gustav" ID="1" ProfessionId="1" />
<Person Name="Simone" ID="2" ProfessionId="1" />
<Person Name="Klaus" ID="3" ProfessionId="1" />
<Person Name="Sigi" ID="4" ProfessionId="2"/>
<Person Name="Flo" ID="5" ProfessionId="2"/>
This is the relational version of the very first Xml-Listing: You see two tables
Person, and each Record got an additional Property, the
ID, which is the "PrimaryKey". A PrimaryKey is unique in that table. You see two times
ID="1" - this is valid, since theese doubles occure in different tables. (Now if i wanted, i could add a second Gustav, with another
ID, and i still could distinguish them.)
Moreover records of the
Person-Table now have the promised Reference to the Parent-Record - the "ForeignKey", here named
ProfessionId. You see, the ForeignKey is not unique, three persons are Police-Men, and two Persons are Punk.
Ok, but how we achieve, that Gustav can have two professions, and how we can introduce "Ansgar", a gui without a Profession at all?
By changing the model:
<Profession Name="Police-Man" ID="1"/>
<Profession Name="Punk" ID="2"/>
<Person Name="Gustav" ID="1"/>
<Person Name="Simone" ID="2"/>
<Person Name="Klaus" ID="3"/>
<Person Name="Sigi" ID="4"/>
<Person Name="Flo" ID="5"/>
<Person Name="Ansgar" ID="6"/>
<PersonProfession PersonID="1" ProfessionID="1"/>
<PersonProfession PersonID="1" ProfessionID="2"/>
<PersonProfession PersonID="2" ProfessionID="1"/>
<PersonProfession PersonID="3" ProfessionID="1"/>
<PersonProfession PersonID="4" ProfessionID="2"/>
<PersonProfession PersonID="5" ProfessionID="2"/>
Now we have the third table,
PersonProfession, whiches records associate a person with a Profession. As promised Gustav is associated with two Professions, and there is also the new Gui, Ansgar, without Profession.
Alternatively see the same data as tables:
|Name ||ID |
|Gustav ||1 |
|Simone ||2 |
|Klaus ||3 |
|Sigi ||4 |
|Flo ||5 |
|Ansgar ||6 || ==> ||
|PersonID ||ProfessionID |
|1 ||1 |
|1 ||2 |
|2 ||1 |
|3 ||1 |
|4 ||2 |
|5 ||2 || <== ||
|ID ||Name |
|1 ||Police-Man |
|2 ||Punk |
an Object with several Values. You can also imagine a Record as Row of a table, although that may be not 100% correct.
Our Datamodel contains overall 14 Data-Records/-Rows
Entity / Table
the abstract of a Record. An Entity tells, out of what a Record consists. In a database Entities are represented by tables, and it is easy to imagine an Entity as table, but in general an Entity is a kind of "thinking-unit".
Because as well as an Entity is the abstract of a Record, it can bee seen as the abstract of a peace of reality. Maybe "Entity" can be defined as that, what Datamodel and Reality have in common? Sorry - i have a kind of feeling, what an Entity is, but i have troubles to define it.
Nevertheless - our Datamodel contains 3 Entities, two of them (
Profession) quite concret, the third -
PersonProfession - is more abstract, but also is an Entity: When you associate Persons with Professions, these Associations are real, means: part of the reality, and our Model models that.
PrimaryKey / ID / PK
the property of a Record, or if you want the column of a table, which identifies the record. For that, PrimaryKeys must be unique.
In our Datamodel
Profession have Primarykeys, but
PersonProfession has not (yet). Mostly it is recommended to equip each entity with a Primarykey, but it's not absolutely necessary.
ForeignKey / FK
the property of a Record, which refers to its Parent-Record. Foreignkeys don't need to be unique.
In our Datamodel
PersonProfession has two ForeignKeys, one refers to
Person, and the other to
As seen in relational Datamodels the nesting of Records into each other is replaced by the Referencing-System. A Child-Record refers via its ForeignKey to a Parent-Record of another Entity. The principle is simple: The child-records ForeignKey targets that parent-record, whiches Primarykey equals the Foreignkey.
Since the Primkey is unique, but the Foreignkey is not, a ParentRecord can have many ChildRecords. But a ChildRecord has only one ParentRecord (per Foreignkey).
(Note: If there is no matching PrimKey in the ParentTable, then the ChildRow is invalid.)
In our Model the Profession "Police-Man", has three ChildRows as well as the Profession "Punk" has.
From the other side only "Gustav" has two Childrows, the other Persons have each one Child, and "Ansgar" has none.
Relation / 1:n-Relation/ Parent-Child-Relation / One-to-many-Relation
a Relation exists between two tables, if a ForeignKey of one refers to the PrimaryKey of the other. This is how the relational model "emulates" the hierarchical model, where a Parent-Object may contain several Child-Objects within a collection.
Many-to-many-Relation / m:n-Relation
basically there is only one kind of relation: one-to-many. The term "m:n-Relation" is stricktly speaking just a shortcut for two 1:n-Relations, connected in the way which is shown in our Datemodel.
Our Datamodel shows the m:n-relation between Person and Profession.
I took especially this construction to point out, how the relational model exceeds the capabilities of a hierarchical model: In a relational model a Child-Record can be subordered to more than one ParentTables.
the Mediator-Table is needed to model m:n-Relations - it is the table, which is subordered to both Parent-Tables. Each mediator-record associates a record from the one parent-table with a record from the other.
In our Datamodel
PersonProfession is the Mediator-Table.
Very often Mediator-Tables contain more Informations than only the ForeignKeys. For instance the Mediator of Appointments will associate the Appointment-Members, and will add an Information about the DateTime, when the Appointment shall happen.
Set up a relational Datamodel
The most simple way ever to set up a Datamodel is to use the Designer for typed Datasets. Click Menu: "Project-Add_New_Item" - choose "Dataset", enter a meaningful Name for it, then you will come up here:
With ContextMenu add a DataTable:
Give it a meaningful Name, and - again with Contexmenu - add Columns to it:
Configure the Columns in the Property-Grid. Then (Contextmenu) set the intended Column as PrimaryKey:
Note the Configuration in the Property-Grid:
There are many options to configure DataColumns, and to PrimKeys that is a usual Cofiguration.
In same manner create all Tables you need. Remember: PersonProfession has no PrimaryKey, but two ForeignKeys - they are normal Columns, of course of Typ
Int32, since a ForeignKey-Value only can equal a PrimKey-Value, if they are of the same DataType.
Then use the mouse to drag the PrimaryKey on the ForeignKey:
This will open the DataRelation-Configuration-Dialogue:
The configuration as shown is the most usual. Especial
DeleteRule.Cascade is valuable, because its effect is, when you delete a Person, all his ChildRows will be also deleted. Otherwise they would become invalid, because their Foreign-Keys would "hang", and that would cause errors.
In the end it should look like this:
(note, that i set the column
Name.AllowDbNull=False, since a Person with no name makes no sense.)
This is a typed Dataset, and at the same time it is the Entity-Relationship-Diagram (follow the link, if you don't know the term) of our Datamodel. Watch the Relation-Lines: The Primary-Key-end shows a very small Key, and the ForeignKey-End shows a very small Infinite-Symbol. These Symbols represent the direction of the one-to-many-Relation.
And in Background the Designer generates lots of typed classes for comfortably usage of it.
Coding against a typed Dataset
For instance you can loop now all persons, and output their Professions:
Dim OutputLines As New List(Of String)()
For Each rwPerson As PersonRow In PersonProfessionDts.Person
OutputLines.Add(rwPerson.Name & ": ")
For Each rwPersonProfession As PersonProfessionRow In rwPerson.GetPersonProfessionRows()
OutputLines.Add(" " & rwPersonProfession.ProfessionRow.Name)
MessageBox.Show(String.Join(Environment.NewLine, OutputLines), "Professions of Persons")
Professions of Persons
(note Gustav with two, and Ansgar without Profession)
And the other way round: loop professions and output "their" persons:
Dim OutputLines As New List(Of String)()
For Each rwProfession As ProfessionRow In PersonProfessionDts.Profession
OutputLines.Add(rwProfession.Name & ": ")
For Each rwPersonProfession As PersonProfessionRow In rwProfession.GetPersonProfessionRows()
OutputLines.Add(" " & rwPersonProfession.PersonRow.Name)
MessageBox.Show(String.Join(Environment.NewLine, OutputLines), "Persons of Professions")
Persons of Professions
You see: Very trivial OOP-Code. A nested loop throught strongly typed Collections. That simplicity is intended: It's the generated classes of the typed Dataset, which provide to navigate such simple and OOP-like through the relational Datamodel.
Load and save of the Datamodel
Don't worry, the complicated stuff is already done. Loading and Saving is very easy - when you do without a database
Public Class frmPersonProfession
Private _DataFile As New FileInfo("..\..\PersonProfessionDts.Xml")
Public Sub New()
Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
That's it: a typed Dataset can directly read and write from Xml-Files - no database is required at all! :wtf:
The shown code above is complete to provide full CRUD (follow the link, if you don't know the term) -Support to the Relational Data-Application:
You can add, change, delete any DataRecord you want:
"DatasetOnly" - Data-Application without Database
You see: We have a relational Data-Application, but not a single Sql-Command to call. And although most programmers see typed Dataset as outdated, the pattern, to avoid Sql-Usage in managed Code is absolutely modern. Entity-Framework hides the Sql-Commands as well, and so does every other OR-Mapper.
Unfortunately there is no OR-Mapper, who can read and write a complete Datamodel directly from Disk. For that in the area of easy-to-use, flexibility and portability typed Dataset still has significant advantages over all modern OR-Mappers:
- no additional Library to include
- no Database-Provider to install
- Data-Xml-Files can simply be zipped and shipped. So complete application-sources incl. Data can be shipped within a single zip-file. This can be worthy to discuss problems or prototypes.
- The DataModel is easy to change with Visual-Studio On-Bord-Tools
For local Data-Applications the approach - i name it: "DatasetOnly" is a serious alternative to embedded Databases. Loading about 10000 or 20000 Records into memory is shurely no problem - nowadays a simple bitmap often takes more memory.
Moreover the option to switch the Data-Backend from an Xml-File to a real Database stays always available. The Dataset does not care about how it is filled - whether via DataAdapter from a Database or directly from an Xml-File.
Nothing of the Bindings or Code must be changed for such migration - the Dataset stays working, only the way it is filled and saved becomes more powerful (and more complicated).
For Beginners: Learn Databinding before Database-Access
One point is to understand: A seriously Data-Application bases on two very different Instances of the same abstract Datamodel: One Instance is modeled in the Database, and the other - and that is the model, our code deals with - is the local, typed model in the Client-Application. The latter is either designed as typed Dataset or as Entity-Model-Classes of the EntityFramework (or of other OR-Mappers).
If you start first with Database-Access you most likely run into hopeless confusion, using DbCommands directly and propably using them wrong.
The main-disadvantage of direct DbCommand-Usage is, they get data even if there is no structurated typed model, where the data is to put in. It's like going to the river to fetch water - and forget the bucket :wtf: .
And if you continue to do so you can never learn the concepts of Databinding at all. Because the Databinding-concepts include the concept, to configure Bindings in Designers - eg in the Form-Designer - but the Designer can only support design Bindings, if a typed Datamodel is present.
So you will never see the capabilities of Databinding, you will never miss it, you will work very very hard, but your applications - from a state-of-art-point-of-view: ähm - rubbish, sorry :-( .
But when you learn to design Databindings, in Designers, you know the value of a typed model, and you will organize the Database-Access in a way that respects that (namely either by DataAdapters or by the OR-Mapper-Stuff).
This article shows the entry to development of Data-Applications via old-fashioned typed Dataset - and that is the most easy approach, since a database is not needed yet.
Later you can switch to Entity-Framework, without loss of the most important principles, you learnd, namly how to design a datamodel and develope with designer-designed Databindings. The principles are same, although some Designers may look different, or - eg with "code-first" - you need not necessarily an Entity-Relationship-Designer (is it an advantage or a disadvantage?).
One important note is, that developing Datamodels is a question of experience. No-one will immediately know the most appropriate model to every given Problem, when he just did understand the principles as explained here.
The way of thinking in entities and relations must be practiced for a while to become good in that.
The skills to create Datamodels is also one of the values, which will not loss, when you later may migrate away from "DatasetOnly".
is litteraly the application of the given theory: You will find there the Datamodel, designed in the Dataset-Designer.
And there are two Tabpages, which give a small glimpse on what design of Databinding means:
The first Tabpage simply shows all Tables as they are - in DatagridViews (see image above).
The other Tabpage shows a more complex view: On the left you can select a profession, and on the right are the Persons of that profession displayed.
Moreover via ComboboxColumn you can change the Person, which is refered by the DataRecord.
Below that the same is done once again, but now on the left there are the Persons to select, and you get displayed eaches Professions (and you can add, change, delete them):
All that is done in Designers, it works reliable, full Crud-supported, and behaves consistant - without a single line of code.
In my next article i will give a deeper introduction into designing Databindings on WinForms.
We saw, how the relational Datamodel exceeds a cruicial restriction of the usual hierarchical Datamodel, with which every object-oriented programmer is very familiar: A relational entity can be subordered under several Parent-Entities - without occurance of redundancy.
We've got some definitions of some common technical terms.
We saw, how to design a relational Datamodel by using an Entity-RelationShip-Diagram - in concret: by using the Dataset-Designer.
We got a glimpse of how to use the classes, which the Dataset-Designer generates for us. Means: We saw how to access typed data from our typed Datamodel - eg to output all Persons grouped by their Professions as well as all Professions grouped by the Persons, who executes them.
We got a glimpse of what can be accomplished by designed DataBindings in Gui (but without deeper explanations, how to configure especially the advanced functionality. As said this will be subject of another article.).
We learned, that complete relational Data-Applications can be developed without a database at all - and how easy that is. This may be interesting for Learners as well as for the development of prototypes. And in some cases a simple Xml-File may replace an embedded database.