return value from stored procedure using SqlHelper.UpdateDataset

[Guest]

Hi,
I'm trying to update the Database (sql2000) using the DAAB v2.0
SqlHelper.UpdateDataset method. The problem is that my insert stored
procedure returns the primary keys of my new inserted rows as a returned
values parameters. But with the above method there seems to be no way to get
back those parameteres.
What woul be the best way to overcome this problem?
Thanks,
Gwenda

 

[Kevin Yu [MSFT]]

Hi Gwenda,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the return parameters
of the insert stored procedures when calling UpdateDataset method. If there
is any misunderstanding, please feel free to let me know.

In the current DataAccess Application Block, we can see that the
UpdateDataset method doesn't support returning output parameter values.
However, the DAAB is open source and we can make any changes on it. So my
suggestion is that we can add an overload for the UpdateDataset method
which returns the output value as you need. We can copy the original code
and make some small changes on it.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin,
Thank you for your reply. I was thinking of implementing your suggestion
when I bounced into an article in MSDN "Managing an @@IDENTITY Crisis"
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp).
If I understood the article correctly, the child - paret FK-PK relations
should be handled automatically by ado.net. But although I followed the
article recommendation it still does not work for me. The main differences
are that I'm using the DAAB and transactions while the article does not.
Would you still reccomend manipulating the DAAB? Does the approach suggested
by the article should work with DAAB and transactions too?
Thank you again,
Gwenda

 

[Guest]

Hi Kevin,
After doing further reading and playing with it some more, I think I have
better understanding of the problem. I implemented your suggestion and it
works to some extent but does not solve the entire problem.
I actually have 3 tables (let’s say tbl1, tbl2 and tbl3) in one DataSet
which are connected in parent child relations so tbl1 contains 1 row, tbl2
contains multiple rows with a FK column to tbl1 PK column. tbl3 contains
multiple rows with a FK column to tbl2 PK column.
The update sequence is all part of one transaction but is not performed by
the same function, as different components of the application handle
different parts of the data. The sequence is:
1. Start transaction.
2. Update tb1.
3. I've modified the DAAB to returned the PK of the new inserted row.
3. I enter this value to the dataset both as the PK of tbl1 and as the FK of
tbl2.
4. I update tbl2
…
And now my problem is I only get one returned value parameter although the
update inserted more than one row to the database. What would be the best
approach to handle such a scenario?

Thank you,
Gwenda

 

[Kevin Yu [MSFT]]

Hi Gwenda,

Did you mean that you're inserting multiple rows in the transaction and you
need to return all the new identities to the app? Could you let me know how
these values are returned? As return value or output parameters? If it is
the return value of the stored procedure, only one value can be returned.
However, if we use output parameters, we have to assign the @@identity to
the parameter each time a record is inserted, because @@identity only
returns the identity for the last inserted record.

DAAB is a pattern that is suitable for general usage of Data Access to the
SQL Server database. If you have specific requirements for the stored
procedure to return values, I think you can try to extend it or even
implement your own Data Access tier.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."