[Long] It should have been easy...

  • Thread starter Thread starter Massimo
  • Start date Start date
M

Massimo

I'm quite new to ADO.NET programming, so please forgive me if some of my
questions sound dumb or are just FAQs, but I'm trying to develop a DB-based
application and I'm getting a lot of problems; maybe I'm just missing some
important point... so any help would be appreciated.

My application is a Winforms one, connected to a SQL Server 2000 database;
the IDE is Visual Studio .NET 2003 and the framework version is 1.1.
The DB is quite simple (for now), since I'm writing a customers and
suppliers database; I have two tables ("Customers" and "Suppliers"), with
the same schema: a GUID and some text strings (name, address, phone, city,
e-mail, and so on). The application consists (for now) of a main form
showing these companies in two ListBoxes and some buttons to add, modify and
delete entries; the first two buttons show a dialog form with controls bound
to the data, while the third one simply deletes a row.
I followed the standard pattern for data binding: a SqlConnection (which is
opened at startup), two SqlDataAdapters (sqlDACustomers and sqlDASuppliers)
and two typed DataSets (objDSCustomers of type DataSetCustomers and
objDSSuppliers of type DataSetSuppliers).
I created a single dialog form for adding and modifying entries in both
tables, since the schema is the same; the form operates on a single DataRow,
passed in the constructor, and modifies it if the user clicks on the Ok
button, while leaving it untouched if the user clicks on Cancel.
When modifying entries, everything works fine with the following code:

---
System.Data.DataRow datarow =
objDSCustomers.Customers.Rows.Find(listBoxCustomers.SelectedValue);

FormCompanyData form = new FormCompanyData(datarow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges());

objDSCustomers.AcceptChanges();
---

When creating a new record, I create it with just the GUID and empty values,
and then pass it to the dialog form; when the dialog is closed, I want to
check the row for modifications and eventually save them (or delete the
row)... and here come the troubles.

My first try was to look for a HasChange() method in the DataRow... but
there isn't anyone (why?); my second try was something like this:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.NewCustomersRow();

newrow.ItemArray = new Object[]
{System.Guid.NewGuid(),"","","","","","","","","","",""};

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(newrow.Name != "") // Exception thrown here
{
objDSCustomers.Customers.Rows.Add(newrow);

sqlDACustomers.Update(objDSCustomers.GetChanges());

objDSCustomers.AcceptChanges();
}
---

This doesn't work, it throws an exception complaining about the row not
being in any table. I *know* the row isn't inside any table, I just want to
look for the data I put inside it three lines before... but I can't. This is
quite crazy, I think.
Anyway, I decided to put the row inside my DataSet, planning to delete it if
the dialog doesn't change it. This is the code I wrote:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

objDSCustomers.AcceptChanges();

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges()); // Exception
thrown here
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

This leads to a DBConcurrencyException, and this quite puzzled me at the
beginning... until I discovered a strange behaviour in the
DataSet/DataAdapter coupling: adding a row to the DataSet, accepting the
changes, modifying the row and then updating the database isn't allowed, I
think what happens is that the DataAdapter believes the row was already
inserted in the database and now only needs to be updated, but the row isn't
here, so it throws the exception. Crazy, again... if the row isn't there,
why not creating it?
I then tried removing the AcceptChanges() call:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges());
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

This way everything works... as long as the dialog form really modifies the
row; if it doesn't, the DataSet still thinks he has changes to save, and an
almost-empty row is inserted into the db.
The only way everything actually works is this one:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

sqlDACustomers.Update(objDSCustomers.GetChanges());

objDSCustomers.AcceptChanges();

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges());
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

But this is definitely an ugly hack, since an useless row is inserted and
then removed from the database.

So, my first and most important question is: how can I make this stuff work?

The other questions regard some other approaches I've tried (in vain).
My initial guess at the problem was to pass the whole DataSet to the dialog
form and having it display and modify it; this would also have the added
benefit of being able to visually bind EditBoxes to the DataSet (since
Visual Studio doesn't allow binding to a DataRow (why, again?)and I was
forced to do it manually). But this was the start of a journey to hell :-(
The first problem is that I don't know how to find the position in the
DataSet for a given record (knowing its primary key), so I can't show the
right values on the dialog; I tried creating a temporary one-record DataSet,
but it was definitely ugly, and a DataRow can't be in two DataSets, so I had
to duplicate it (which created even more ugliness). The second problem is
that Visual Studio, when adding a DataSet to a form, wants to manage it on
its own... otherwise the DataSet disappears from the design window and can't
be used for binding; this means I couldn't simply pass the DataSet in as a
parameter and assign it to a private member... at least if I didn't want
(again) to bind all the controls manually. I tried to copy the interested
row into the local DataSet, modifying it and then merging it back when
closing the form... and I discovered that, after a Merge(),
Dataset.HasChanges() returns *false* (even if this definitely doesn't make
any sense), so I (again) don't have any way in the main form to know if the
data were actually modified and need to be written back to the database;
even using some kind of flag wouldn't help, since the DataSet itself doesn't
know what needs to be updated...

After two days of struggling, I'm getting more and more frustrated at this.
I hope someone canactually help me writing decent code, since I'm trying to
develop a program, and not a mass of ugly hacks :-(

Thanks for reading...

Massimo

P.S.
Forgive me if my exposition is quite confused sometimes, but I really tried
lots of different approaches, every one of them leading to a different
failure, so I actually *am* quite confused at the moment.
 
Hummm... not many of us have the bandwidth to wade through all of this, but
I did a quick scan and noticed a couple of things. First, you're right to
drop the AcceptChanges--it's not needed in this case. Next, consider how
bound datasets work. When you change a value in the bound control, the value
is not posted to the dataset until you say you're "done". That's what
BeginEdit and EndEdit are for. I would research those methods here on the
list and elsewhere. I hope this helps.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Massimo said:
I'm quite new to ADO.NET programming, so please forgive me if some of my
questions sound dumb or are just FAQs, but I'm trying to develop a DB-based
application and I'm getting a lot of problems; maybe I'm just missing some
important point... so any help would be appreciated.

My application is a Winforms one, connected to a SQL Server 2000 database;
the IDE is Visual Studio .NET 2003 and the framework version is 1.1.
The DB is quite simple (for now), since I'm writing a customers and
suppliers database; I have two tables ("Customers" and "Suppliers"), with
the same schema: a GUID and some text strings (name, address, phone, city,
e-mail, and so on). The application consists (for now) of a main form
showing these companies in two ListBoxes and some buttons to add, modify and
delete entries; the first two buttons show a dialog form with controls bound
to the data, while the third one simply deletes a row.
I followed the standard pattern for data binding: a SqlConnection (which is
opened at startup), two SqlDataAdapters (sqlDACustomers and sqlDASuppliers)
and two typed DataSets (objDSCustomers of type DataSetCustomers and
objDSSuppliers of type DataSetSuppliers).
I created a single dialog form for adding and modifying entries in both
tables, since the schema is the same; the form operates on a single DataRow,
passed in the constructor, and modifies it if the user clicks on the Ok
button, while leaving it untouched if the user clicks on Cancel.
When modifying entries, everything works fine with the following code:

---
System.Data.DataRow datarow =
objDSCustomers.Customers.Rows.Find(listBoxCustomers.SelectedValue);

FormCompanyData form = new FormCompanyData(datarow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges());

objDSCustomers.AcceptChanges();
---

When creating a new record, I create it with just the GUID and empty values,
and then pass it to the dialog form; when the dialog is closed, I want to
check the row for modifications and eventually save them (or delete the
row)... and here come the troubles.

My first try was to look for a HasChange() method in the DataRow... but
there isn't anyone (why?); my second try was something like this:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.NewCustomersRow();

newrow.ItemArray = new Object[]
{System.Guid.NewGuid(),"","","","","","","","","","",""};

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(newrow.Name != "") // Exception thrown here
{
objDSCustomers.Customers.Rows.Add(newrow);

sqlDACustomers.Update(objDSCustomers.GetChanges());

objDSCustomers.AcceptChanges();
}
---

This doesn't work, it throws an exception complaining about the row not
being in any table. I *know* the row isn't inside any table, I just want to
look for the data I put inside it three lines before... but I can't. This is
quite crazy, I think.
Anyway, I decided to put the row inside my DataSet, planning to delete it if
the dialog doesn't change it. This is the code I wrote:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

objDSCustomers.AcceptChanges();

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges()); // Exception
thrown here
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

This leads to a DBConcurrencyException, and this quite puzzled me at the
beginning... until I discovered a strange behaviour in the
DataSet/DataAdapter coupling: adding a row to the DataSet, accepting the
changes, modifying the row and then updating the database isn't allowed, I
think what happens is that the DataAdapter believes the row was already
inserted in the database and now only needs to be updated, but the row isn't
here, so it throws the exception. Crazy, again... if the row isn't there,
why not creating it?
I then tried removing the AcceptChanges() call:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges());
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

This way everything works... as long as the dialog form really modifies the
row; if it doesn't, the DataSet still thinks he has changes to save, and an
almost-empty row is inserted into the db.
The only way everything actually works is this one:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

sqlDACustomers.Update(objDSCustomers.GetChanges());

objDSCustomers.AcceptChanges();

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges());
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

But this is definitely an ugly hack, since an useless row is inserted and
then removed from the database.

So, my first and most important question is: how can I make this stuff work?

The other questions regard some other approaches I've tried (in vain).
My initial guess at the problem was to pass the whole DataSet to the dialog
form and having it display and modify it; this would also have the added
benefit of being able to visually bind EditBoxes to the DataSet (since
Visual Studio doesn't allow binding to a DataRow (why, again?)and I was
forced to do it manually). But this was the start of a journey to hell :-(
The first problem is that I don't know how to find the position in the
DataSet for a given record (knowing its primary key), so I can't show the
right values on the dialog; I tried creating a temporary one-record DataSet,
but it was definitely ugly, and a DataRow can't be in two DataSets, so I had
to duplicate it (which created even more ugliness). The second problem is
that Visual Studio, when adding a DataSet to a form, wants to manage it on
its own... otherwise the DataSet disappears from the design window and can't
be used for binding; this means I couldn't simply pass the DataSet in as a
parameter and assign it to a private member... at least if I didn't want
(again) to bind all the controls manually. I tried to copy the interested
row into the local DataSet, modifying it and then merging it back when
closing the form... and I discovered that, after a Merge(),
Dataset.HasChanges() returns *false* (even if this definitely doesn't make
any sense), so I (again) don't have any way in the main form to know if the
data were actually modified and need to be written back to the database;
even using some kind of flag wouldn't help, since the DataSet itself doesn't
know what needs to be updated...

After two days of struggling, I'm getting more and more frustrated at this.
I hope someone canactually help me writing decent code, since I'm trying to
develop a program, and not a mass of ugly hacks :-(

Thanks for reading...

Massimo

P.S.
Forgive me if my exposition is quite confused sometimes, but I really tried
lots of different approaches, every one of them leading to a different
failure, so I actually *am* quite confused at the moment.
Click to expand...
 
Hummm... not many of us have the bandwidth to wade through all of this, but
I did a quick scan and noticed a couple of things. First, you're right to
drop the AcceptChanges--it's not needed in this case.
Click to expand...

Maybe you should look more carefully :-)
Here's the code:

---
MyApp.DataSetCustomers.CustomersRow newrow =
objDSCustomers.Customers.AddCustomersRow(System.Guid.NewGuid(),"","","","","
","","","","","","");

objDSCustomers.AcceptChanges();

FormCompanyData form = new FormCompanyData(newrow);

form.ShowDialog(this);

if(objDSCustomers.HasChanges())
sqlDACustomers.Update(objDSCustomers.GetChanges()); // Exception
thrown here
else
objDSCustomers.Customers.Rows.Remove(newrow);

objDSCustomers.AcceptChanges();
---

I'm passing the new DataRow to a dialog form that *may* modify it, so I'm
using the DataSet to track changes in this row; if the form didn't modify
it, I remove it from the DataSet, otherwise I save it in the database. If I
add the row without committing it, DataSet.HasChanges() will return always
true (even if the form didn't modify anything), so it becomes useless for
this tracking...
Next, consider how bound datasets work. When you change a value
in the bound control, the value is not posted to the dataset until you say
you're "done". That's what BeginEdit and EndEdit are for. I would research
those methods here on the list and elsewhere. I hope this helps.
Click to expand...

I'm already doing this in the dialog form to accept or reject changes to the
DataRow. And, in the code above, I'm getting meaningful values from
DataSet.HasChanges(), which is true only if the form actually changed the
DataRow. I think the code is correct, the only problem is that awful
DBConcurrencyException (which I, by the way, don't understand, since nobody
else is accessing the DB) :-(

Massimo
 
Back
Top