Questions about generated stored procedures by VS 2005 (TableAdapter).

[Steven Spits]

Hi,

When I let a TableAdapter generate stored procedure for "SELECT * FROM
Supplier WHERE SupplierID = @SupplierID", it generates (some parts removed):

Select:
SET NOCOUNT ON;
SELECT * FROM Supplier WHERE SupplierID = @SupplierID

Update:
SET NOCOUNT OFF;
UPDATE [Supplier] SET <...> WHERE <...>;
SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);

Insert:
SET NOCOUNT OFF;
INSERT INTO [Supplier] VALUES <...>;
SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);

Delete:
SET NOCOUNT OFF;
DELETE FROM [Supplier] WHERE <...>;

My questions:

(1) Why does Visual Studio generate a SELECT after the INSERT & UPDATE
statements?

(2) Why is NOCOUNT set to ON in the select?

Kind regards,

Steven

- - -

 

[David Browne]

Hi,

When I let a TableAdapter generate stored procedure for "SELECT * FROM
Supplier WHERE SupplierID = @SupplierID", it generates (some parts
removed):

Select:
SET NOCOUNT ON;
SELECT * FROM Supplier WHERE SupplierID = @SupplierID

Update:
SET NOCOUNT OFF;
UPDATE [Supplier] SET <...> WHERE <...>;
SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);

Insert:
SET NOCOUNT OFF;
INSERT INTO [Supplier] VALUES <...>;
SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);

Delete:
SET NOCOUNT OFF;
DELETE FROM [Supplier] WHERE <...>;

My questions:

(1) Why does Visual Studio generate a SELECT after the INSERT & UPDATE
statements?

To get the results of any identity columns, computed columns, defaults or
triggers.

(2) Why is NOCOUNT set to ON in the select?

Because setting NOCOUNT ON is a general best-practice. It's only set OFF
for the DML to inform of the number of rows affected.

David

 

[Steven Spits]

To get the results of any identity columns, computed columns, defaults or
triggers.

And in my particular case, what happens with that data? Will the
TableAdapter update the row in the DataSet with the newly read data?

Because setting NOCOUNT ON is a general best-practice. It's only set OFF
for the DML to inform of the number of rows affected.

So it's only needed for statements that need to check for concurrency
violation, like DELETE, INSERT & UPDATE?

Steven

- - -

 

[Arnie Rowland]

First off, recognize that Visual Studio, normally, IS NOT creating Stored
Procedures. It is creating 'inline' SQL code that will be submitted for the
appropriate datadapapter command. It will often be more efficient to create
stored procedures and use them instead of inline code.

Your questions:

1. The SELECT after a UPDATE or INSERT or DELETE is used to refresh your
dataset. Other users may have been making changes to the data included in
your dataset.

2. SET NOCOUNT ON curtails a unnecessary (in most situations) roundtrip
between the client and the server to send back the number of rows affected
by the command.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

 

[Ivan Arjentinski]

Update:

SET NOCOUNT OFF;
UPDATE [Supplier] SET <...> WHERE <...>;
SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);

Insert:
SET NOCOUNT OFF;
INSERT INTO [Supplier] VALUES <...>;
SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID);

This behaviour of VS could be turned off. There was a checkbox somewhere in
the wizard which removes the additional SELECTs if you don't need them