Dcoder - Response (blast)
I'm running into this same problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356806&SiteID=1
Databinding seems useless. The idea of the disconnected data model is a really difficult process, mainly because of relational databases. Many of my tables have identities, and if multiple people are hitting the database at once, there is no guarantee that the identity generated in your datagrid is going to be the same when you actually submit the in memory dataset to your database. This makes it a real pain to update related records.
Can anyone shed some insight on complex databinding with identities and relationships? Perhaps, someone has some good documentation on some methodologies used to create windows forms applications which use databinding.
-Watson
[980 byte] By [
Blast] at [2007-12-22]
I prefer to use a guid for the primary key. That way you do not have to worry about having the wrong id number.
How do you use a GUID (global unique identifiers?) with data tables? Any reference material you can point me to?
Hi Watson,
I'm afraid this is the model of distributted computing, the problem of synchronization, concurrency and consistency.
 Blast wrote: |
| Databinding seems useless. The idea of the disconnected data model is a really difficult process, mainly because of relational databases. Many of my tables have identities, and if multiple people are hitting the database at once, there is no guarantee that the identity generated in your datagrid is going to be the same when you actually submit the in memory dataset to your database. This makes it a real pain to update related records. |
|
where to find more discussions on this issue? anyone
| hrubesh wrote: |
| where to find more discussions on this issue? anyone |
|
Good question, I've been asking around and haven't gotten any clear results.
Here is an example of the master / child problem -
http://www.condoresorts.com/example/doc.html //This example illustrates identity problem with databinding.
Basically, the two datagrids are databound to the same bindingsource, but the datamember for the child datagrid's bindingsource is set the FK relation.
I update the changes like so:
mastertableadapter mymaster = new mastertableadapter();
mymaster.Update( this.dataset1.mastertable);
childtableadapter mychild = new ChildDataadapter();
mychild.Update( this.dataset1.childtable);
Let me know if that makes sense?
Also, see my post for why i'm not using databinding -
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=577530&SiteID=1
Ok, I think I have figured out the identity column child table problem. You simply need to go to the dataset designer, right click on your master child relation line and click on edit relation. You can then set Update and delete to cascade. Which Updates your dataset's child table to the appropriate primary key value after the master table updates.
For example, your app generates primary key 900. But while you're working other people generates primary key 900 and post to db. So, now when you post your 900 pk to the database, the database will shoot back the true pk and then your child table will get updated appropriately.
I use a stored procedure to get a Primary Key from the database for a new record. The database hands them out one by one, so there are never any dups. Then there is no issue when inserting the new record.
Have you tried something like that?
Greg Gum
Yes, I have my first implementation did exactly that. But, if you do that, what do you give to your child tables when you need to update them with a foriegn key? I like how datasets handle that problem for you.
I am not quite sure I understand the question. If your creating a child record, you should already have the foriegn key from the parent record.
Greg
AutoIncrement Problem Answer:
The problem can be avoided by setting the AutoIncrementSeed value to -1 and the AutoIncrementStep value to -1 thereby generating a sequence of negative values that does not conflict with the values generated by the data source, as long as the data source does not generate negative values. When the disconnected data is reconciled with the underlying data , the data source correctly identifies the records that have negative AutoIncrement field values as new records, adds them to the data source, and in the process generates new values for the AutoIncrement field. (From ADO .NET Cookbook - O'Reilly)
So for every columns that has AutoIncrement set to be true in your dataSet, just set the AutoIncrementSeed and AutoIncrementStep to any number that with your real database, the auto increment column will never met the value.
Ex: if you set AutoIncrementSeed = 1 and AutoIncrementStep = 1 in your database, then set in your dataset AutoIncrementStep = -1 and AutoIncrementStep = -1, so the increment step from your dataset will never met the increment step in your database. When you're trying to update to database, your database server will smart enough to think that the value you're trying to insert is not yet being added, so your database server will add the new record base on the last auto increment value has been added.
dcoder_85.
Ok, I didn't set the relationship to the child table correctly - you must set the relationship in the dataset designer for the child table to both relation and foriegn key. And then toggle the cascade update and cascade delete. Then, when you update your parent table, it will reassign the proper ID which will cascade down to the child.
For example, I inserted a CustomerID into my datagrid and it gives it an ID of 800 because it was the next ID from the datatable, but my database has been updated by other users who have added customers and now the CustomerID is actually at 815. When I use the dataadapter to update the database I will try and add customerid 800 but since 800 is already take it will reassign a new ID and update my datatable automatically, and at this point if there are any child rows that have a customer value of 800 it will reassign it the correct value that was reassigned to the parent table. I hope that makes sense.
The next question is, how do you databind a result set?
Lets say I want a textbox in my form to display the result set of the customers who do not have orders.
The query would look like this:
Select Count(CustomerID) From Customers Where CustomerID not in (Select CustomerID From Customer_Orders)
How do you add this to your dataset and create the capability to bind this to a textbox and have an effect to where if the user adds a customer row or assigns a customer an order the textbox will automatically reflect the changes?
I think the problem is, I want to be able to query a dataset. This resultset never need to be filled, it just needs to query the current in memory dataset tables for the answer. How to do this anyone?
I saw this product http://www.queryadataset.com/dataset.aspx Does this mean you cannot query the in memory dataset? That sounds crummy.
Q1: Parent-Child Relationship Problem, Answer:
Actually you don't have to add cascade constraint in your dataset, the default that visual studio did (by MSDatasetGenerator tool) when we create a strong-typed dataset is only built the relations but that that's enough to us to do a parent-child relation task. How to do that is create two bindingsources, one is for the parent and one is for the child, ex:
' vb .net 2.0
Dim parentBinding as BindingSource = New BindingSource(myDataSet, "ParentTable")
Dim childBinding as BindingSource = New BindingSource(parentBinding, "FK_ChildTable_ParentTable_ParentID")
// c# .net 2.0
BindingSource parentBinding = new BindingSource(myDataSet, "ParentTable");
BindingSource childBinding = new BindingSource(parentBinding, "FK_ChildTable_ParentTable_ParentID");
Now because the childBinding is bind to it's parent the parent-child relation will automatically functions because we use it's foreign key table not the child table actually, but the results the child table will automatically have the values just like the foreign key table. Note: the foreign key table will automatically generated when we drop the parent and the child table in the dataset designer as long as they have the relations. You can bind any of your master controls to the parentBindingSource and the detail controls (ex: DataGridView) to your childBindingSource.
Q2: Bind to a DataSet that has the different result sets, Answer:
Well what we wanna do now is to get this Select Count(CustomerID) From Customers Where CustomerID not in (Select CustomerID From Customer_Orders) result. Ok, the first step is go to the dataSet designer and add the tables, i'm gonna use the NorthWind database for example:
- First i add the Customers tableAdapter, and the Orders tableAdapter.
- and then i right click the Customers tableAdapter and select the Add -> Query.
- when we got in the Choose Query Type, i choose Select which returns a single value.
- the i type Select Count(CustomerID) From Customers Where CustomerID Not In (Select CustomerID From Orders), and i name it NonOrderCustomerCountQuery.
- (for vb .net 2.0) after that, i go to the Windows Forms designer and drop a textBox into my form. And then go to the code window, from that window there were two comboBoxes, one is for the variables, one is for the event name, yes both of them are use to add event handler. From the first comboBox (that is the variables) i choose the CustomersBindingSource, and from the second comboBox (that is the event name) i choose the PositionChanged, so Visual Studio will add the PositionChanged event handler, i do this because my dataset is in the same project so if i use dataSource wizard from the properties window Visual Studio will add the necessary components (ex: tableAdaper instance, dataSet instance, and the bindingSource instance). But if you're not using dataSource wizard, you can add this in your codes:
' vb .net 2.0
' local variables
Dim WithEvents customersTableAdapter as CustomersTableAdapter = New CustomersTableAdapter
Dim WithEvents myDataSet1 as DataSet1 = New DataSet1
Dim WithEvents customerBindingSource as BindingSource = New BindingSource(myDataSet1, "Customers")
- c# .net 2.0
// somewhere in my codes (wheter some functions or my constructors) i add the event handler
customerBindingSource.PositionChanged += CustomerBindingSourcePositionChanged;
- finally, from the PositionChanged event handler i add this:
' vb .net 2.0
Private
Sub customersBindingSource_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CustomersBindingSource.PositionChangedTextBox1.Text = customersTableAdapter.NonOrderCustomerCountQuery().ToString()
End Sub
// c# .net 2.0
private void customerBindingSource_PositionChanged(Object sender, System.EventArgs e) {
TextBox1.Text = customersTableAdapter.NonOrderCustomerCountQuery().ToString();
}
- and done. Note: the weakness of this approaching is that this is a server-side concept, you won't be able to see the changes until the value in the database also changes.
If your problem is how to get data that can be resolved in client-side way is easy, but your problem is how to get data that is not in the relations, and the data is an expression. Please tell me if i'm doing wrong, and if someone had another good solutions, please share with us.
dcoder_85.
You illustrate some good points I want to go over. First of all, like you said, this is a server side solution. When you use the dataadapter to query our customers that don't have orders you are querying the database not the dataset.
First let me go over how I see the lifecycle of data with datasets, hope this helps to show where my thought process lies.
1. Application pulls down information from database, fills datasets.
2. The application then manipulates the data in the datasets. At this point, if we want to query what customers do not have orders we must query our datasets.
3. Now, based on our different dataviews and other display of data we can make changes to our dataset via windows forms.
4. Now, at some point we want to reconcile our dataset with the database, this is where concurrency comes in. So, we post our changes to the database, handle concurrency and then refill our dataset at this point to include our updated data (I believe our datasets automatically update on tableadaptor.update)
And that's it! I guess the problem is, datasets are not a database. You can't query them, so you can't really manipulate data locally all too well it seems.
Let me know if my approach is totally wrong. Dcoder, your approach maybe the only way that works. THanks for your response.
Maybe there is a better approach to data manipulation then datasets and tableadapters. Business objects with orm? Anyone have anything to add?
Just for more informations, i hope will helps you more.
Actually DataSets don't have any informations wheter we already have update to the database or not. If we're working in a multi-user environment, maybe you'll have to refill the datasets after you update your datasets. DataSets only have the information from the DataRow/DataRowView objects, that is from its DataRowSate/DataViewRowState property, that is the state your records (current/unchanged/deleted/added/modified, etc). AcceptChanges(), RejectChanges() methods from the dataAdapter can changes the state of your dataRow/dataViewRow, and also addNew(), Delete(), CancelEdit()/CancelCurrentEdit() methods from the bindingSource also can changes the state of your dataRow/dataViewRow, including when user edit controls that already bounds to dataSet.
If the problems are like when we want to get an expression (ex: Sum, Count, etc) or when we want to get a rows that is somewhat custom (Ex: Select * From SomeTable Where TheColumn Is Not In (Select * From AnotherTable)) i choose to create queries. But if the problems are like we want to get the child rows or we want to get the parent rows, you don't have to create queries. The DataRow class already have the properties/methods, the methods are GetChildRows(), and GetParentRow()/GetParentRows(), and if we use the dataSet designer, Visual Studio (by MsDatasetGenerator tool) will add the necessary properties of the parent rows and child rows. For example if i have three tables, i name the first one is Currency, the second one is PurchaseOrderHeader, and the third one is PurchaseOrderDetail. PurchaseOrderHeader is the child of Currency but don't have cascade constraints (update/delete), Visual studio will add the CurrencyRow for me in the PurchaseOrderHeader row. PurchaseOrderDetail is the child of PurchaseOrderHeader and have cascade constraints (update/delete), and Visual Studio will add the PurchaseOrderHeader row in PurchaseOrderDetail, and also in PurchaseOrderHeader row Visual Studio will add the PurchaseOrderDetail row. So i don't have to create any queries, because add run-time i can get all the properties i need. As long i fill the three tables, and use the the same dataSet at run-time, i can do any thing that i want to manipulate the data. Note: Use the strong-typed name if we want to get just like what i've said, for example PurchaseOrderHeaderRow is a strong-typed name that's created by Visual Studio when i drop the dataAdapter, so it will have the properties just like what i've said before.
dcoder_85.
