Working with Visual Studio 2005 and ADO.NET
Seems like there are some changes in how to work with related tables in Visual Studio 2005 compared to VS 2003. I really like working with the designer tool as it does a lot of timeconsuming stuff for you. It's really easy to work with typed datasets by dragging and dropping the tables from theServer Explorer into the designer. But there are some hick ups that are really diffucult to find the answers to. In my opinion, Microsoft are really good in making Mickey Mouse examples and not real life examples. What happens when you for instance are trying to update two tables that are related at the same time ? Well, you get an SqlException, typical like:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customers_Employees". The conflict occurred in database "EnterpriseCRM", table "dbo.Employees", column 'EmployeeId'.
The statement has been terminated.
yuck! Ths was really not the intention.
Here is the solution:
Figure 1: An Enterprise CRM system 
My database schema looks something like figure 1 (added to the Visual Studio designer by dragging and dropping). I have added a couple of queries to the Customers table, but have nothing to do with the example.
Now, here is the code that generates this error:
Here is the code that does the logic. It's adding some new rows to the Customers and Employees tables, and setting the relations between the rows. The relation is the newCustomer.EmployeesRow = newEmployee:
ds = new DataSetEnterpriseCRM();
ds.GetRegions();
for (int i = 0; i < 3; i++)
{
DataSetEnterpriseCRM.EmployeesRow newEmployee = ds.Employees.NewEmployeesRow();
newEmployee.Name = "SomeOne";
DataSetEnterpriseCRM.CustomersRow newcustomer = ds.Customers.NewCustomersRow();
newcustomer.Region = 1;
newcustomer.Name = "New TestRelatedRow";
newcustomer.EmployeesRow = newEmployee;
newcustomer.KAM = newEmployee.EmployeeId;
ds.Employees.AddEmployeesRow(newEmployee);
ds.Customers.AddCustomersRow(newcustomer);
}
ds.SaveEmployeeAndCustomer(ds);
The code in the partial class of my DataSet. This code updates the tables in the database by calling the update method on the TableAdapters:
public void SaveEmployeeAndCustomer(DataSetEnterpriseCRM dataSet)
{
CustomersTableAdapter adapter = new CustomersTableAdapter();
EmployeesTableAdapter employeeAdapter = new EmployeesTableAdapter();
employeeAdapter.Update(dataSet.Employees);
adapter.Update(dataSet.Customers);
}
This code is perfectly legal and should teoritically work fine, but instead you will get the error message described above. Seems like there are some problems with cascading updates. There is, and here is the solution to the problem.
Configuring the relation
You need to configure the Typed DataSet. By default it does not support cascading updates. It is easy to do, but not well documented anywhere! One should maybe think that this was automatically supported when dragging and dropping the tables from the designer, but that's not the case.
Double-click the relation between the tables and you will get the following properties box:
Notice the dropdownlists
Set the update and delete dropdowns to "cascade" and the radiobutton to "Both Relation and Foreign Key Constraint".
Updating the AutoIncrementSeed and AutoIncrmentStep
This is only necessary if you are using AutoIncrement for the primary key. You need to specify that the increment seed should start with -1 and that the step should be -1. Click the table containing the primary key and select properties for the primary key column.
Set the AutoIncrementSeed and the AutoIncrementStep to -1 if you are using AutoIncrement for the primary keys.
This will cause the keys to be -1, -2, -3 and so on. This prevents the keys from interferring with the keys in the database. When the update methods of the adapters are executed, the keys will change and fetched again from the database. You can double check that by looking at the insert statement in the adapter. There is an select right after the insert which is returning the Identity_scope of the inserted row. That will always the latest incremented key inserted. The best of all is that the foreign key relation will be correct when the primary key is inserted before the row containing the foreign key.
Now, everything should be up to shape and the relations should work. Happy coding!
No comments yet.
Leave a comment
Search
Knut Hamang
