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

  • Thread starter Thread starter Steven Spits
  • Start date Start date
S

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

- - -
 
Steven Spits said:
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?
Click to expand...

To get the results of any identity columns, computed columns, defaults or
triggers.
(2) Why is NOCOUNT set to ON in the select?
Click to expand...

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
 
To get the results of any identity columns, computed columns, defaults or
triggers.
Click to expand...

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.
Click to expand...

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

Steven

- - -
 
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
 
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);
Click to expand...
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
 
Back
Top