hamang.net

Passing an object to ObjectDataSource

I like the ObjectDataSources in some occasions, even if liked the .NET 1.1 way of working with data better. I have played around with ObejctdataSources for a while, and it hit me that it's not intuitive to pass an object as the parameter.  The Visual Studio designer has functionality for passing parameters to the business objects, but only as strings. This is actually a limitation to the Parameter class as the DefaultValue is of type string.

Coding standards suggest that a method should have 7 parameters or less (some say even 5), and databinding methods can have a lot of parameters based on what data to fetch. A good way is to pass a struct or object instead of several parameters.

So it's not possible to initially pass an object as a parameter. You have to do it in an event instead. The following solution illustrates how it could be done:

• Select the ObjectDataSource in the designer and select the Events for the ObjectdataSource.
• Double click the Selecting event. This generates a callback method for the event in your code behind file.
• The method should look something like this:

protected void ObjectDataSourceNewsList_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
NewsArchive newsArchive = new NewsArchive();
newsArchive.Language = "no";
newsArchive.Country = "NO";
e.InputParameters["newsArchive"] = newsArchive;
}

The newsArchive parameter is defined in the ObjectDataSource as of type Object. This works fine, but it's rather strange that you have to define the parameter in an event and not by the parameters in the ObjectDataSource.

Tags: ADO.NET

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!

Tags: ADO.NET