R
Renaud Langis
Hi all,
I am having a hard time figuring out how database update works with
DataSets. Here is an example of what i want to do:
To make it simple, let's say i have 5 tables Users, Teams_Users,
Teams, Operators and Operators_Teams
Users: ID uniqueidentifier
--some text elements--
TEAM_ID -- Many teams for a user
Teams: ID uniqueidentifier
--text elements--
Teams_Users : TEAM_ID - teams have many users. Msers may have
USER_ID many teams
Operators_Teams : TEAM_ID -- teams can have many operators
OPER_ID which can operate multiple
teams.
Operators : ID uniqueidentifier
--some text elements--
Relations : Users.TEAM_ID to Teams.ID
Teams.ID to Operators_Teams.TEAM_ID
Operators_Teams.OPER_ID to Operators.ID
In this example, cardinalities aren't very important. I stated those
only to show basic queries can't do the job.
I want to load all the data related to a given user in a dataset. In
order to be able to achieve database update, i should have no joins.
Select * From Users Where ID=@ID -- straightforward
With no joins, i should get the TEAM_ID value from the Users table
before building the query to retrieve the TEAMS data. In this example,
it's not much of a hassle but in real life, it can be. For example
suppose i want all users having the same operator as a given user.
Getting all data can require a lot of sub-queries if joins cannot be
used.
===================== With ADO ==========================
In ADO, i could do: Select TEAMS.* From TEAMS, USERS Where
TEAMS.ID=USERS.TEAM_ID And USERS.ID=?
Then perform an UpdateBatch after updating some data in a recordset.
In ADO, i could also do something like
Select Distinct U.NAME, T.NAME, O.NAME
From USERS U, TEAMS T, OPERATORS_TEAMS OT, OPERATORS O
Where U.TEAM_ID=T.ID
And T.ID=OT.TEAM_ID
And OT.OPER_ID = O.ID
Then update all names and UpdateBatch.
Or else use data shaping to avoid redundancy.
========================================================
How can i achieve something like this using ADO.NET?
Of course i could get all data from the TEAMS table then define
relations in the dataset. But if there are a lot of teams, this can be
a performance bottleneck when the database is on a remote server.
(data in a dataset reside on the client right? i suppose disconnected
datasets cannot use server-side cursors). For example, in our
database, there are currently 240000 users. Getting all data in a
dataset cannot be done. If many users use the application (web app),
the web server (even the web farm) will crash pretty fast.
Are there any good documentation regarding DataAdapter InsertCommand
(or delete, update...)? What i found on MSDN or elsewhere on internet
were always useless trivial samples.
Thanks for any help
Renaud
I am having a hard time figuring out how database update works with
DataSets. Here is an example of what i want to do:
To make it simple, let's say i have 5 tables Users, Teams_Users,
Teams, Operators and Operators_Teams
Users: ID uniqueidentifier
--some text elements--
TEAM_ID -- Many teams for a user
Teams: ID uniqueidentifier
--text elements--
Teams_Users : TEAM_ID - teams have many users. Msers may have
USER_ID many teams
Operators_Teams : TEAM_ID -- teams can have many operators
OPER_ID which can operate multiple
teams.
Operators : ID uniqueidentifier
--some text elements--
Relations : Users.TEAM_ID to Teams.ID
Teams.ID to Operators_Teams.TEAM_ID
Operators_Teams.OPER_ID to Operators.ID
In this example, cardinalities aren't very important. I stated those
only to show basic queries can't do the job.
I want to load all the data related to a given user in a dataset. In
order to be able to achieve database update, i should have no joins.
Select * From Users Where ID=@ID -- straightforward
With no joins, i should get the TEAM_ID value from the Users table
before building the query to retrieve the TEAMS data. In this example,
it's not much of a hassle but in real life, it can be. For example
suppose i want all users having the same operator as a given user.
Getting all data can require a lot of sub-queries if joins cannot be
used.
===================== With ADO ==========================
In ADO, i could do: Select TEAMS.* From TEAMS, USERS Where
TEAMS.ID=USERS.TEAM_ID And USERS.ID=?
Then perform an UpdateBatch after updating some data in a recordset.
In ADO, i could also do something like
Select Distinct U.NAME, T.NAME, O.NAME
From USERS U, TEAMS T, OPERATORS_TEAMS OT, OPERATORS O
Where U.TEAM_ID=T.ID
And T.ID=OT.TEAM_ID
And OT.OPER_ID = O.ID
Then update all names and UpdateBatch.
Or else use data shaping to avoid redundancy.
========================================================
How can i achieve something like this using ADO.NET?
Of course i could get all data from the TEAMS table then define
relations in the dataset. But if there are a lot of teams, this can be
a performance bottleneck when the database is on a remote server.
(data in a dataset reside on the client right? i suppose disconnected
datasets cannot use server-side cursors). For example, in our
database, there are currently 240000 users. Getting all data in a
dataset cannot be done. If many users use the application (web app),
the web server (even the web farm) will crash pretty fast.
Are there any good documentation regarding DataAdapter InsertCommand
(or delete, update...)? What i found on MSDN or elsewhere on internet
were always useless trivial samples.
Thanks for any help
Renaud