Hi,

Using Visual Studio 2005 and SQL Server 2005:

I am trying to understand the stored procedures that Strongly Typed Datasets 
wizard generates for me.

When we create a new strongly typed Datasets, and choose the following path:

Choose connection-> Create new stored procedures -> Enter SQL (select * from 
tbl) -> Advanced Options -> Refresh the data table

We have an advanced option to refresh the data table. When we check the 
option, the wizard adds a select statement to the end of SP_INSERT and 
SP_UPDATE.

I don't understand how the result of the added select statement (to the 
insert and update stored procedures) are being read back by the internal 
DataAdapter. What makes the table adapter reads the result of the select 
statement after insert or update operations?

A link to an online article would greatly help.

Thank you,
Max

Hello Max,

It seems you want to know SQLDataAdapter's implementation about how to read 
back the added/updated row and update the related row. If I misunderstand, 
please correct me. Thanks.

For Typed Dataset Wizard, after you entered Select SQL statement, it will 
use SqlCommandBuilder to generate the insert/delete/update SQL statement, 
and then create the SP_INSERT/SP_UPDATE for us. If you check the REFRESH 
THE DATA TABLE option in Adavanced Option, the generated stored procedure 
is also included a select statement which will return the modified datarow.

PROCEDURE SP_UPDATE
....
UPDATE [dbo].[Table_1] SET [c2] = @c2, [c3] = @c3 WHERE (([c1] = 
@Original_c1) AND ((@IsNull_c2 = 1 AND [c2] IS NULL) OR ([c2] = 
@Original_c2)) AND ((@IsNull_c3 = 1 AND [c3] IS NULL) OR ([c3] = 
@Original_c3)));	
SELECT c1, c2, c3 FROM Table_1 WHERE (c1 = @c1)

After executed the SP_INSERT/SP_UPDATE, you can notice the stored procedure 
not only insert/update the row in the database, but also return a record 
set which identify the changes in underlying database.

For SQLDataAdapter, its update method will check the RowState property of 
each row. If it is modified, The SqlDataAdapter calls the ExecuteReader 
method on the SqlCommand object stored in its UPDATECOMMAND property, and 
it checks the RecordsAffected property of the resulting SqlDataReader to 
determine whether to apply values from the SqlDataReader to the DataRow. 
Then, if the SqlDataAdapter determines that submitting the pending change 
in a DataRow succeeded, the SqlDataAdapter checks the SqlCommand's 
UpdatedRowSource property to determine how (or whether) it should apply 
values returned by the SqlCommand to the DataRow

UpdatedRowSource:
Both		Tells the SqlCommand to fetch new data for the row using both the 
first returned record and output parameters. This is the default.
FirstReturnedRecord		Tells the SqlCommand to fetch new data for the row 
through the first returned record.
None		Tells the SqlCommand not to fetch new data for the row upon execution.
OutputParameters		Tells the SqlCommand to fetch new data for the row using 
output parameters.

In your case, the UpdatedRowSource property is BOTH(by default). The 
SQLDataAdatper will check both OutputParamets and FirstReturnedRecord. 
However, due to no output parameter in this case, SQLDataAdatper use 
SQLDadaReader.Read() method to retrieve the Fist Returned Record and modify 
the related filed in current updated row.

This behavior has been mentioned in book <Programming Microsoft? ADO.NET 
2.0 Core Reference> by David Sceppa.
Chapter 11. Advanced Updating Scenarios
 - Refreshing a Row After Submitting an Update
 - - Using Batch Queries to Retrieve Data After You Submit an Update

Hope this helps. If you still have anything unclear, feel free to update 
here. We're glad to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Thank you very much Wen Yaun for this comprehensive answer.
Do you know anywhere in MSDN that actually document this?

Thank you again,
Max



"WenYuan Wang [MSFT]"  wrote in message 
news:SrKy$F3xHHA.5204@TK2MSFTNGHUB02.phx.gbl...

> Hello Max,
>
> It seems you want to know SQLDataAdapter's implementation about how to 
> read
> back the added/updated row and update the related row. If I misunderstand,
> please correct me. Thanks.
>
> For Typed Dataset Wizard, after you entered Select SQL statement, it will
> use SqlCommandBuilder to generate the insert/delete/update SQL statement,
> and then create the SP_INSERT/SP_UPDATE for us. If you check the REFRESH
> THE DATA TABLE option in Adavanced Option, the generated stored procedure
> is also included a select statement which will return the modified 
> datarow.
>
> PROCEDURE SP_UPDATE
> ...
> UPDATE [dbo].[Table_1] SET [c2] = @c2, [c3] = @c3 WHERE (([c1] =
> @Original_c1) AND ((@IsNull_c2 = 1 AND [c2] IS NULL) OR ([c2] =
> @Original_c2)) AND ((@IsNull_c3 = 1 AND [c3] IS NULL) OR ([c3] =
> @Original_c3)));
> SELECT c1, c2, c3 FROM Table_1 WHERE (c1 = @c1)
>
> After executed the SP_INSERT/SP_UPDATE, you can notice the stored 
> procedure
> not only insert/update the row in the database, but also return a record
> set which identify the changes in underlying database.
>
> For SQLDataAdapter, its update method will check the RowState property of
> each row. If it is modified, The SqlDataAdapter calls the ExecuteReader
> method on the SqlCommand object stored in its UPDATECOMMAND property, and
> it checks the RecordsAffected property of the resulting SqlDataReader to
> determine whether to apply values from the SqlDataReader to the DataRow.
> Then, if the SqlDataAdapter determines that submitting the pending change
> in a DataRow succeeded, the SqlDataAdapter checks the SqlCommand's
> UpdatedRowSource property to determine how (or whether) it should apply
> values returned by the SqlCommand to the DataRow
>
> UpdatedRowSource:
> Both Tells the SqlCommand to fetch new data for the row using both the
> first returned record and output parameters. This is the default.
> FirstReturnedRecord Tells the SqlCommand to fetch new data for the row
> through the first returned record.
> None Tells the SqlCommand not to fetch new data for the row upon 
> execution.
> OutputParameters Tells the SqlCommand to fetch new data for the row using
> output parameters.
>
> In your case, the UpdatedRowSource property is BOTH(by default). The
> SQLDataAdatper will check both OutputParamets and FirstReturnedRecord.
> However, due to no output parameter in this case, SQLDataAdatper use
> SQLDadaReader.Read() method to retrieve the Fist Returned Record and 
> modify
> the related filed in current updated row.
>
> This behavior has been mentioned in book <Programming Microsoft? ADO.NET
> 2.0 Core Reference> by David Sceppa.
> Chapter 11. Advanced Updating Scenarios
> - Refreshing a Row After Submitting an Update
> - - Using Batch Queries to Retrieve Data After You Submit an Update
>
> Hope this helps. If you still have anything unclear, feel free to update
> here. We're glad to assist you.
> Sincerely,
> Wen Yuan
> Microsoft Online Community Support
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no 
> rights.
> 

Hello Max

Actually, I have searched on MSDN website before my first reply. However, I 
could not found related articles so far.

Have you tried Reflector? This tool is really grateful. We usually use it. 
It could disassemble the .net class for us.
http://www.aisto.com/roeder/dotnet/
[Reflector for .NET]

Hope this helps. If you have any more concern or you have anything unclear, 
please feel free to update here. We are really glad to assist you.

Have a great day, 
Sincerely,
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

I got my answer.
Thanks for Help WenYaun.


"WenYuan Wang [MSFT]"  wrote in message 
news:RBWdLcSyHHA.5848@TK2MSFTNGHUB02.phx.gbl...

> Hello Max
>
> Actually, I have searched on MSDN website before my first reply. However, 
> I
> could not found related articles so far.
>
> Have you tried Reflector? This tool is really grateful. We usually use it.
> It could disassemble the .net class for us.
> http://www.aisto.com/roeder/dotnet/
> [Reflector for .NET]
>
> Hope this helps. If you have any more concern or you have anything 
> unclear,
> please feel free to update here. We are really glad to assist you.
>
> Have a great day,
> Sincerely,
> Wen Yuan
> Microsoft Online Community Support
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no 
> rights.
> 

You are welcome, Max.
I'm glad to work with you. 

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.