Enter your search terms

Submit search form

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:

• The Code Project
• SharpDevelop

Data Input Form

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.

Updating from Windows Form

After working out how to update data in a Master Detail DataGridView one might have thought that it would be a fairly trivial exercise to update a MySQL database from a Windows Form using DataSets. It just goes to show how wrong you can be, in fact I was several times tempted to say come back Delphi all is forgiven. But of course you have to remember that you now have a much more sophisticated application that can perform validation etc. Because the Delphi database controls are hardwired into the database it is easy to get lazy and not perform data validation before updating the database. With NET you are forced to go through a more formal method to update your database.

Validating the Form Data

I have started to use a control called Validator which involves adding a validation Dll custom control to your project. This then allows you in form design mode to select the validation required for each textbox. If there is a problem with data entry then an error icon is shown against the textbox and the user can re-enter before attempting dataset/database update. This works very well and even allows validation using regular expressions.

Editing the DataSet Row(s)

The first thing to realise is that you have to move the contents of your form from usually a textbox to a datarow of the DataSet. This doesn't happen by magic you actually have to code each field in turn. All the information talks about textboxes and string fields, nice and easy but what about dates, currency values, comboboxes. It would have been nice to see some examples because I am sure I am not the only person using these datatypes.

Using DataRows

It took me a little while to learn about the significance of datarows since there were many articles on the net which had very different ideas maybe based on NET 1.1. So the first thing to do is to find the datarow to update. To obtain the current primary key bookid, I had to stick this in a texbox on the form and then use a filter expression such as :-

I was quite surprised to see that this worked since it all looks a bit messy!

Begin Edit

Before Updating the DataRow you need the command

Updating DataRow from Text

This is straightforward :-

Updating DataRow from ComboBox

I expected to have to use ValueMember, but no this doesn't work and you have to use SelectedValue (there must be some logic here that I'm not accustomed to!)

Updating DataRow from Date

When you display a date on a form it usually has the format dd/mm/yyyy, whereas MySQL stores a date in ansi format yyyy-mm-dd hh:mm:ss. So you just have to reformat the date using the substring function :-

One other quirky thing about dates if you use a maskedtextbox control and if it is optional, then you need to test for a blank date = " / /" before attempting substringing.

Updating DataRow from Time

This is really weird! I used a maskedtextbox with a mask of Time (European/Military) i.e. mask format '00:00' and set the row update as follows :-

The ':00' on the end gets it into the time format for MySql
I then found that the field kept on resetting to the value on the database when I tried updating the time on the form. Eventually I found that changing the CausesValidation property of the maskedtextbox to false allowed the update to proceed. I don't really understand why this is different for this particular datatype.

End Edit

When you have finished all the edits on the DataRow you need to end the edit :-

Get DataSet Changes

Now you have finished editing the rows, the next step is to get all the changes made to the dataset :-

So here you have a new DataTable containing all the changes made to the dataset since the last time it was filled.

Updating the Database

The changes are fed back to the database using the dataadapter da1 :-

All that remains to be done now is to get the changed values back to the datatable :-

Deleting from Windows Form

I saw what looked like a handy delete icon on the BindingNavigator which looked similar to that found on the Delphi DBNavigator. So I had a closer look but soon gave up when I realised there was no simple way of asking the user to 'Confirm' delete. So I turned off the delete button on the BindingNavigator and created my own delete button as follows :-

Database Insert, Update Delete Commands

The fairly innoccuous looking statement above :- MyDB.da1["book"].Update(changes); calls the actual database insert, update and delete commands. If you are performing single table updates then it is possible to use MySqlCommandBuilder to automatically generate the SQL statements to reconcile the dataset changes back to the database.

Using MySqlCommandBuilder

I started off writing the individual insert, update and delete commands for the data adapters, but this quickly became very tedious and prone to error. So then I gave MySqlCommandBuilder a try and this worked first time - perhaps I was lucky since I have heard of some problems. So after defining the MySqlDataAdapter for the table using a 'select *' statement you just need to add a line as below :-

Looking a little deeper there seems to be a problem with generating the 'last_insert_id' and I have also seen articles suggesting that the Command Builder is OK for prototyping and testing but carries a significant performance overhead. So back to writing the individual database commands, until I can find a suitable code generation tool. Actually, I found quite a nice looking code generator called Smartcode which has just been updated to run with MySQL. So far I have managed to connect up to all my databases but code generation using the supplied templates has defeated me. After return from hols I noticed that some of the offending templates had been removed from the standard download, so I had another go with a different template and actually produced some code.