A Base Windows Form for displaying, editing and automatically updating Many-to-Many relationships - Part I

• Download source files - 23.6 Kb

Introduction

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.

Table A

AID    AName

1    Mr Moore
2    Ms Smith
3
4
...

Table B

BID    BName

1    John
2    Fred
3    Julie
4    Usha
5    Ellen
6
...

Table AB

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 DataGridView columns.

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.

using Data.OleDB

string myDB = "AorB.mdb"; //or whatever your database is called
OleDbConnection conn = null;
DataSet ds;
OleDbDataAdapter daA;
OleDbDataAdapter daB;
OleDbDataAdapter daAB;
OleDbDataAdapter daBA;

DataRelation drAB;
DataRelation drBA;

public void InitialCode() //called from FormLoad or elsewhere.
{

    //create a connection string to the access database
    conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;
    User Id=;Password=;
    Data Source=" + myDB);

    LoadDatabase();

}

public void LoadDatabase()
{

    //clear the dataset
    ds = null;
    ds = new DataSet();

    //set up the link
    daA = new OleDbDataAdapter("SELECT * FROM A ORDER BY AName", conn);
    daB = new OleDbDataAdapter("SELECT * FROM B ORDER BY BName", conn);

    //fill the dataset with data
    daA.Fill(ds, "A");
    daB.Fill(ds, "B");

    // Create Data Relation from A to B, ForeignKey constraint
    // auto created apparently - a bit complex!

    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);
    daAB.Fill(ds, "ABx");

    drAB = new DataRelation("AToB",
    ds.Tables["A"].Columns["AID"],
    ds.Tables["ABx"].Columns["AID"], true);
    ds.Relations.Add(drAB);

    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);
    daBA.Fill(ds, "BAx");

    drBA = new DataRelation("BToA",
    ds.Tables["B"].Columns["BID"],
    ds.Tables["BAx"].Columns["BID"], true);
    ds.Relations.Add(drBA);

    //enforce cascade contraints, auto added, if need change 
    //then look up ForeignKeyContraints.DeleteRule
    ds.EnforceConstraints = true;  

    BindDataGridViews();

}

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

    //bind the datagridviews
    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";

    DisplayColumnsinGrids();
}

//note the form designer sorted out all the form stuff 
//I just renamed the default datagridviews to be called dgvA
//dgvAtoB (on tab page 1) dgvB and dgvBtoA (on tab page 2)

DataGridViewTextBoxColumn dataGridViewTextBoxColumn1;
DataGridViewTextBoxColumn dataGridViewTextBoxColumn2;
//...ect

private void DisplayColumnsinGrids()
{
    //sort out columns - I'm not sure how or if it's possible 
    //to use the designer to do this with datarelations etc?
    //so I created some unbound columns and manually 
    //wired it all up - anyone know the correct procedure?
    //I know the designer can create unbound columns, 
    //but I couldn't see how to bind them.

    dataGridViewTextBoxColumn1 = 
       new System.Windows.Forms.DataGridViewTextBoxColumn();
    dataGridViewTextBoxColumn1.HeaderText = "AID";
    dataGridViewTextBoxColumn1.Name = "AID";
    dataGridViewTextBoxColumn1.DataPropertyName = "AID";
    dataGridViewTextBoxColumn1.Width = 30;
    dgvA.Columns.Add(this.dataGridViewTextBoxColumn1);

    //...and so on for all 12 columns - see the full source 
    //in the download zip

}

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