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 | Using MySQL with .NET |
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.
08 Aug 2007: SharpDevelop 2.2.1 Final has been released containing bug fixes.
Links:
Data Readers
Once I started converting a few of the slightly more complex Delphi applications I began to notice some unexpected results when working with more than one Data Reader.
In particular I typically got errors such as "There is already an open DataReader associated with this Command which must be closed first". So I checked carefully that I only had one open DataReader at a time and I even put the DataReaders through different connections. Unfortunately nothing helped and at this moment I do not know whether this is a NET framework problem, a MySql problem or both - certainly whenever I looked for a solution in the forums the answers at the beginning were very prompt and then gradually faded away as it became 'a too difficult problem'. So in case you are also in the Microsoft DataReader blackhole the following might give you some ideas. Unfortunately, I found that I had to change my processing fairly significantly whenever I was processing more than one table at a time. It made no difference whether I was reading or updating multiple tables, I had to rethink the processing strategy.
Updating Two Tables on a Form
There are quite a few occasions where you have a master detail relationship and you update one of the detail records and you want the change(s) to be reflected back into the header record. A typical scenario would be where you are updating balances for an account shown in a DataGridView and you want to show the updated balance in the account held record and may be also the total balance for all the accounts held for that customer. In Delphi this would not be a problem because you could just recalculate the balances for the detail rows and then update the header row balance. Unfortunately, this is not quite so simple in NET framework, but I am always ready for someone to show me a simpler way.
Adding/Updating a Row
Firstly, I set a flag 'recalbal' to instruct whether or not the detail balances have changed and then in the RowValidated event for the DataGridView I call the routine to recalculate the balances which iterates through the Rows and individual cells changing the values in the DataGridView only. Then next I update the DataTable and reset the flag:-
{
RecalBalance();
updateDataTable();
}
recalbal = false;
My main problem was in the RecalBalance() routine where I needed to also update the header record. So I set off in the routine finding the row in the other table and editing the header balance. The interesting bit came when I tried to use an UpdateDataTable routine on the other table; I just ran into exceptions. So as I couldn't find any good explanation for this I gave it up and called some update SQL to update the table directly. This worked first time with no problems and the only thing I had to do next was to update the textbox with the new balance field. At first I was a little surprised that changes to the dataset did not reflect immediately into the textbox fields, even when I tried refreshes, but then I just accepted this as being yet another feature of using datasets.
Deleting a Row
Reading from One Table, Writing to Another
The normal way in Delphi is to read the values from the source table and then immediately write the values out to the target table. Then read the next row from the source table and so on. So I set up the DataReader to read the values from the source table and then tried to update the target table. The usual error message!
Workaround Using an Array
So I then counted the number of rows I was going to read and set up an array to store the values from the source table. I then stored all the values from the source table and closed the DataReader. I then used an INSERT command to write stored array values directly to the target table.