Cumbersome on DataItemBound event

[Nightcrawler]

This is what I have

1. Two tables, User and Country. User contains UserId, UserName and
Country contains CountryId, and CountryName.

2. I dragged in both tables from SQL server 2005 into a dataset and
has VS 2005 create the standard Select, Update, Insert, Delete stored
procedures.

3. I have a aspx page with a datalist that has an objectdatasource
poiting to the User Tableadapter which simply displays the User data
on the page.

Now, for each row that is displayed I currently have to fire an
OnItemDataBound event, take the CountryId, execute a storedprocedure
to get the CountryName based, find a Label within the template for the
row and display the CountryName. So basically I execute one SP to get
the Users than an additional Stored Procedure for each row. So if I
have 10 users, I have to hit the database 11 times.

I know I can cache the country table since it is not gonna change at
all BUT what if the Country table (in theory) grew alot, then caching
would not be an option. Is there a clean and efficient way of
displaying foreign key values and still keeping the ease of strongly
typed datasets without doing timeconsuming custom coding?

Please let me know.

Thanks in advance

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

This is what I have

1. Two tables, User and Country. User contains UserId, UserName and
Country contains CountryId, and CountryName.

2. I dragged in both tables from SQL server 2005 into a dataset and
has VS 2005 create the standard Select, Update, Insert, Delete stored
procedures.

3. I have a aspx page with a datalist that has an objectdatasource
poiting to the User Tableadapter which simply displays the User data
on the page.

Now, for each row that is displayed I currently have to fire an
OnItemDataBound event, take the CountryId, execute a storedprocedure
to get the CountryName based, find a Label within the template for the
row and display the CountryName. So basically I execute one SP to get
the Users than an additional Stored Procedure for each row. So if I
have 10 users, I have to hit the database 11 times.

Why don't you save a copy of the country table in memory, in a datatable.
Additionally you could just return an extra colunms when you are getting the
users and add the CountryName to that dataset.

I know I can cache the country table since it is not gonna change at
all BUT what if the Country table (in theory) grew alot

In this particular case, I do not think that the Country table grows to over
200 rows, that is nothing really.

 

[Nicholas Paldino [.NET/C# MVP]]

Well, I would think you wouldn't have to, because you can have an
in-memory representation of the relationship (which would make the coding in
the OnItemDataBound event easier). You mention that you have to run a
stored procedure to get the country description for each item in the row.

Instead of having a select which gets your user data, then country data,
then one stored procedure which has to be called for each row in the
country, why not just write a stored procedure which will give you the
country table and the related data, instead of calling it ten times. It
seems like that's going to be one of the big bottlenecks in all of this.

 

[Nightcrawler]

Well, I would think you wouldn't have to, because you can have an
in-memory representation of the relationship (which would make the coding in
the OnItemDataBound event easier). You mention that you have to run a
stored procedure to get the country description for each item in the row.

Instead of having a select which gets your user data, then country data,
then one stored procedure which has to be called for each row in the
country, why not just write a stored procedure which will give you the
country table and the related data, instead of calling it ten times. It
seems like that's going to be one of the big bottlenecks in all of this.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

This is what I have

1. Two tables, User and Country. User contains UserId, UserName and
Country contains CountryId, and CountryName.

2. I dragged in both tables from SQL server 2005 into a dataset and
has VS 2005 create the standard Select, Update, Insert, Delete stored
procedures.

3. I have a aspx page with a datalist that has an objectdatasource
poiting to the User Tableadapter which simply displays the User data
on the page.

Now, for each row that is displayed I currently have to fire an
OnItemDataBound event, take the CountryId, execute a storedprocedure
to get the CountryName based, find a Label within the template for the
row and display the CountryName. So basically I execute one SP to get
the Users than an additional Stored Procedure for each row. So if I
have 10 users, I have to hit the database 11 times.

I know I can cache the country table since it is not gonna change at
all BUT what if the Country table (in theory) grew alot, then caching
would not be an option. Is there a clean and efficient way of
displaying foreign key values and still keeping the ease of strongly
typed datasets without doing timeconsuming custom coding?

Please let me know.

Thanks in advance- Hide quoted text -

- Show quoted text -

By doing that I would have to add an extra column in my so nicely
automatically created datatable in my dataset, correct? Or I could
simply replace countryId in my User Table with say a CountryName, then
fill that datatable with one stored procedure that would do a Inner
Join with the country table.

Am I on the right track here?

Thanks

 

[Mythran]

Well, I would think you wouldn't have to, because you can have an
in-memory representation of the relationship (which would make the coding
in
the OnItemDataBound event easier). You mention that you have to run a
stored procedure to get the country description for each item in the row.

Instead of having a select which gets your user data, then country
data,
then one stored procedure which has to be called for each row in the
country, why not just write a stored procedure which will give you the
country table and the related data, instead of calling it ten times. It
seems like that's going to be one of the big bottlenecks in all of this.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

This is what I have

1. Two tables, User and Country. User contains UserId, UserName and
Country contains CountryId, and CountryName.

2. I dragged in both tables from SQL server 2005 into a dataset and
has VS 2005 create the standard Select, Update, Insert, Delete stored
procedures.

3. I have a aspx page with a datalist that has an objectdatasource
poiting to the User Tableadapter which simply displays the User data
on the page.

Now, for each row that is displayed I currently have to fire an
OnItemDataBound event, take the CountryId, execute a storedprocedure
to get the CountryName based, find a Label within the template for the
row and display the CountryName. So basically I execute one SP to get
the Users than an additional Stored Procedure for each row. So if I
have 10 users, I have to hit the database 11 times.

I know I can cache the country table since it is not gonna change at
all BUT what if the Country table (in theory) grew alot, then caching
would not be an option. Is there a clean and efficient way of
displaying foreign key values and still keeping the ease of strongly
typed datasets without doing timeconsuming custom coding?

Please let me know.

Thanks in advance- Hide quoted text -

- Show quoted text -

By doing that I would have to add an extra column in my so nicely
automatically created datatable in my dataset, correct? Or I could
simply replace countryId in my User Table with say a CountryName, then
fill that datatable with one stored procedure that would do a Inner
Join with the country table.

Am I on the right track here?

Thanks

Why don't you create a View on the DBMS that returns all of the User fields
and the remaining Country fields that are required for display? Then, only
a single stored procedure call would be necessary and you receive all the
information in a single DataTable. Am I missing something as to why you are
not doing this?

HTH,
Mythran