ePlaice / For the Best Software on the Net
Mainly Free and Open Source Software
Software Dev't Navigation
Delphi Conversion | DataSets | DataGridView | Record Navigation | Data Formatting / Validation | Data Queries Data Input Form Data Readers MDI Forms Charts Using MySQL | MySQL NET Connector |
Latest news
27 Feb 2007: I decided to write a few lines about my experiences with C# and SharpDevelop, but got waylaid looking at some of my earlier experiences.
07 Mar 2007: SharpDevelop 2.1 Final has been released.
Links:
DataGridView
I decided to provide a flavour for some of the little problems I hit when converting a medium sized personal financial package from Delphi and Interbase to C# and MySQL
My main conclusions are that this has been a worthwhile exercise, but rather tedious due to the need for lots of coding particularly in the database area. Delphi is truly a marvellous development environment to get something up and running quickly. C# with MySQL is certainly much more challenging, but hopefully at the end of the day you will have a future proofed application which does not require expensive software licences. Were it not for the use of SharpDevelop I don't think this project would have got started since I certainly cannot justify shelling out for Visual Studio. The other advantage is that once one application is converted the following conversions will be much more straightforward.
DataGridView
The DataGridView is a very powerful component in NET 2.0, but there are many pitfalls waiting for the unwary. Whereas in Delphi there are data aware components, this is not the case in NET where first you have to manage the DataGridView and then you have to manage the database updates. I usually find that most of my uses for the DataGridView control are where there is a need for a master detail relationship. So a record changes in the master record set and a list of detail records appears in the grid relating to the new master record.
DB Combobox
This is the code I used to add a new Combobox column to a DataGridView, where the DB field name is trans_type. For a change I found how to achieve this on the Microsoft site :-
colType.HeaderText = "Type";
colType.DropDownWidth = 90;
colType.Width = 90;
colType.MaxDropDownItems = 5;
this.dataGridView1.Columns.Insert(7, colType);
colType.Items.AddRange("A", "N", "P", "S", "Z");
this.dataGridView1.Columns[7].DataPropertyName = "trans_type";
DBLookup Combobox
Comboboxes in DataGridView caused me a lot of problems - the only real clue was an article on the MySQL forum. This gave a very comprehensive example which worked first time, showing how to create a new combobox column which using the Delphi naming convention is called a DBLookupCombox.
DataGridViewComboBoxColumn colForeign = new DataGridViewComboBoxColumn();
colForeign = MyDB.CreateComboBoxColumn("SELECT subjno,subject FROM subject", "ComboForeign", "subject", "subjno");
colForeign.HeaderText = "Subject";
colForeign.Width = 120;
colForeign.DisplayStyle = 0;
this.dataGridView2.Columns.Insert(3, colForeign)
Use DataPropertyName to get the selected key column as in :-
this.dataGridView2.Columns[3].DataPropertyName = "subjno";
It appears that this would be much easier in VisualStudio - maybe??
And here is the routine to Create the Combobox Column - very messy compared with Delphi :-
{
// Returns the DataGridViewComboBoxColumn to be inserted
DataGridViewComboBoxColumn colComboColumn = new DataGridViewComboBoxColumn();
DataTable dtbElements = new DataTable();
MySqlDataAdapter dbaElements = new MySqlDataAdapter(strSQLSelect, conn);
// Set some parameters for the ComboBoxColumn
colComboColumn.Name = strColName;
colComboColumn.DisplayMember = strDisplay;
colComboColumn.ValueMember = strValue;
// Add the Elements
dbaElements.Fill(dtbElements);
colComboColumn.DataSource = dtbElements;
// Return the column
return colComboColumn;
}
Although one of my applications works perfectly I just ported almost identical code to another application and this just shows the foreign key value instead of the lookup description. So I had a closer look at the tables and found that in one table I had defined the foreign key as 'signed' and in the lookup table it was 'unsigned'. As soon as I made them both unsigned everything worked. Delphi did not worry at all about such niceties!
Data Binding for the Grid
When data is required to be shown in the grid it is necessary to bind the data displayed in the grid to the dataset. If you look at the dataset examples you will see the dataset can contain the rows of each table in the database as well as the data relationships between the tables.
Binding for Master Detail
In order to bind a master detail relationship it is necessary to set up a DataView :
Use the following to setup the grid's view and data member :-
datagridview1.DataMember = "author.authorbook";
where "author" is the master datatable and "authorbook" is the data relation between the author and book tables.
Binding for the Combobox
Use the DataPropertyName to bind the combobox column to the database datasource as shown in the example here :-
Where the combobox appears in the third column of the DataGridView.
Displaying Currency
If you want a datagridview column to display currency use the DefaultCellStyle with a format of "c". This also avoids nasty rounding errors on doubles etc. and also prefixes the value with the local currency.
Hiding Columns
Or not hiding columns if they are primary keys! Here is the normal way to hide a column :-
Of course nobody wants to see a primary key on a datagridview, so naturally the one bit of information you really want to hide appears. This happens for primary keys when you are using tabbed pages - quite a common occurrence. The only way round this, I have found is to make sure the page with the datagridview appears first before you start moving through any records. So in your formload event make sure the first action is to open the tabbed page with the datagridview you want to hide columns. Bad luck if you have multiple datagridviews each on different tabs!
DataGridView Update Events
When you add, update or delete a row in the DataGridView there are a number of events that you need to cater for in your code.
UserAddedRow Event
All that's needed here is to set a flag saying that userAddedRow is true
When you come to validate the row you just need to check if the flag is true or not and take appropriate action (see below)
UserDeletedRow Event
Deleting a DataGridView row is fairly easy - use something like :
which is actioned on a delete row mouse click.
Once the row is removed from the datagridview then the UserDeletedRow event is automatically triggered. Typically this event would update the data table followed by the command to re-fill the dataset where da1 is the dataadapter.
MyDB.da1["book"].Fill(dataSet1, "book");
RowValidating Event
In the RowValidating Event it is quite usual to test certain fields for null values, remove spaces and test mandatory fields are not empty. If the row fails any of these checks then you can just cancel the edit without any notification :-
and return;
If the userAddedRow flag is set to true, unset the flag;
userAddedRow = false; and set id to 0, valid because autoincremented id's from server starting from 1 :-
RowValidated Event
After a little time I realised that this event was being triggered whenever I happened to open a form containing a DataGridView, even though I had not specifically asked for an update. This means that the database is being updated unnecessarily and if date and time fields are involved will cause confusion, so I am looking at a workaround. Well I had to find a solution because I had a DataGridView which had a recalc mode which meant every time a cell value was changed, potentially all the rows could be updated. The automatic calling of the event meant that the updates went into overload. The answer is to set another flag in the RowValidating event if the 'IsCurrentRowDirty' flag is true. Then in RowValidated only call the update events if the flag is set. Once the row has been validated and there are changes then I call a routine :-
{
DataTable changes = dataSet1.Tables["book"].GetChanges();
if (changes != null)
{
//remove row with id=0 if exist
//this is the newly added row, which is about to get inserted, and is 0 because I set it's id to 0! (see dataGridView_RowValidating below)
//because mysql begins with 1 for its autoincrements, this is perfectly safe
//
//this is needed because it gets inserted into the database and then retrieved back from it,
//inc. its correct autoincremented ID, otherwise you would end up with doubles (which is not valid because of the unique country-name part)
DataRow dataRow = dataSet1.Tables["book"].Rows.Find(0);
if (dataRow != null) dataRow.Delete();
//commit changes to the database.
MyDB.da1["book"].Update(changes);
dataSet1.Tables["book"].AcceptChanges();
//get the changed values back to the datatable
dataSet1.Tables["book"].Merge(changes);
dataSet1.Tables["book"].AcceptChanges();
}
}
DefaultValuesNeeded Event
I really like this feature of the DataGridView which has an event dedicated to setting the initial values for your new row. I have to admit it took me a little while to find this after trial and error putting initial values in other events. So, the lesson is you need to look closely at this control because there is much value tucked away. So now in the event I can enter code as follows :-
e.Row.Cells["receipt"].Value = "0.00";