As a thank you to all the people from the internet whom I have learnt from in using .NET, I would like to offer this Form for displaying, editing and automatically updating a database (Access) for many to many relationships.
I have tried to make it as generic as possible so that others can adapt it easily to their needs.
The first version of this Form was written using .NET 1.0 and 1.1 and I encountered many problems and issues. In particular I wanted the Form to automatically update the underlying database without having to press a Commit button, which seemed to me to be a retrograde step. After having written some programs using VBA for Access, I appreciated that data was updated straightaway. [However I also appreciate the disconnected nature of the .NET philosophy and can see that there should be some way of not committing changes immediately as well.]
With this in mind, I wanted to write apps that used events to trigger updates as needed. The original structural design of the
DataGrid for .NET 1.1 seemed inadequate for this task. I had to find workarounds and delve far deeper into currency managers and other esoteric structures than I had really wanted, and I still feel that there are bugs in my original code (for a commercial on-line assessment system for schools).
With the availability of .NET 2.0 and the
DataGridView, I wondered if the design might have been enhanced and so I decided to rewrite my code.
I have stripped out all my unnecessary code and structures from my new application and here is a purer version.
To be honest, the original code was created after a lot of experimentation, reading the voluminous documentation, lots of very late nights and spilt blood and sweat - and I cannot remember all the details of why I had to do some things now! I'm no longer a professional programmer so I don't do this stuff every day and I forget.
I am hoping that colleagues can tighten the code and amend any improper usage so that the base code can become very useful for everyone.
Many to many relationships are common. In a school setting, we have many teachers and many pupils. Pupils can belong to the classes taught by many teachers and teachers have many classes of pupils.
So Mr. Moore can have John, Fred, Julie, Usha in his class. Ms. Smith can have Fred, Usha and Ellen in her class.
So Fred is in the classes of Mr. Moore and Ms. Smith, as is Usha, but John is only in Mr. Moore’s class and Ellen is only in Ms. Smith’s class.
In order to map this in a relational database, we usually use an intermediary mapping table.
1 Mr Moore
2 Ms Smith
ABID AID BID
1 1 1
2 1 2
3 1 3
4 1 4
5 2 2
6 2 4
7 2 5
AID is a unique ID for table A, and
BID is a unique ID for table B.
ABID is a unique identifier for the mapping table. [Though I’m not sure it is strictly necessary.]
The table AB provides the many to many mappings between data in table A and data in table B. We can look at the relationships from both directions.
For the implementation, I have used a MS Access database. This has autonumbering for the unique identifier, but there are some difficult issues in using this with .NET – not least concurrency violation problems where the autonumber field may become out of sync with the
DataSet numbering. Although my original version used autonumber (just) I decided that because I might want to port my design to SQL Server or some other database, I had best not assume the existence of an autonumber facility and also because the implementation would then be specific to Access.
So although the Access database tables have, and I allow them to generate, autonumber ID columns, I don’t use them. Instead the application will generate and manage them. [I am not too sure what the impact of this is in a multi user environment. I feel that stronger routines may be required for avoiding concurrency violations. I leave this for others to figure out.]
This first part of the code is just the simple stuff for displaying the data. The next part will add in the editing and database update routines.
I have put in the code for manually displaying the
I am sure that it must be possible to get the designer to do all this manually, but I wasn’t sure how to bind to the manually coded
DataSet. It’s easy for simple tables, but this is not a simple
DataSet. If anyone knows please could you let us (me) know and I’ll amend the code.
Here are the essentials of the code. Firstly, we get a connection and load the
DataSet with data from the Access database AoB.mdb.
string myDB = "AorB.mdb";
OleDbConnection conn = null;
public void InitialCode()
conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + myDB);
public void LoadDatabase()
ds = null;
ds = new DataSet();
daA = new OleDbDataAdapter("SELECT * FROM A ORDER BY AName", conn);
daB = new OleDbDataAdapter("SELECT * FROM B ORDER BY BName", conn);
daAB = new OleDbDataAdapter("SELECT AB.ABID, AB.AID, AB.BID," +
" B.BName FROM B INNER JOIN AB ON" +
" B.BID = AB.BID ORDER BY B.BName", conn);
drAB = new DataRelation("AToB",
daBA = new OleDbDataAdapter("SELECT AB.ABID, AB.BID, AB.AID," +
" A.AName FROM A INNER JOIN AB ON" +
" A.AID = AB.AID ORDER BY A.AName", conn);
drBA = new DataRelation("BToA",
ds.EnforceConstraints = true;
The data is then bound to the
DataGridViews. I created a two tab page with two
DataViews on each tab and manually sorted out the columns.
public void BindDataGridViews()
dgvA.DataSource = ds;
dgvA.DataMember = "A";
dgvAtoB.DataSource = ds;
dgvAtoB.DataMember = "A.AToB";
dgvB.DataSource = ds;
dgvB.DataMember = "B";
dgvBtoA.DataSource = ds;
dgvBtoA.DataMember = "B.BToA";
private void DisplayColumnsinGrids()
dataGridViewTextBoxColumn1.HeaderText = "AID";
dataGridViewTextBoxColumn1.Name = "AID";
dataGridViewTextBoxColumn1.DataPropertyName = "AID";
dataGridViewTextBoxColumn1.Width = 30;
It would seem that the new Express versions of C# do things differently from the C# version I originally used. In particular the source code has been put into different files now, so it will probably be best to download the zip and look at it all there.
In particular I am indebted to George Shepherd's Windows Form FAQ and to the many groups on Google, as well as to this august body :)