Getting rowcount from a stored procedure

[Richard G]

I'm a database guy, so go easy on me here. How can I get the rowcount
of the affected rows of a SQL statement from a stored procedure call? I
know that "set nocount on" does not return the number of affected rows to
the client, so I would assume that "set nocount off" sends the number of
affected rows to the client, and therefore, is available programatically.
If so, how to get that rowcount?

Thanks,
Richard

 

[Michael Giagnocavo [MVP]]

The SqlCommand.ExecuteNonQuery will return the rowcount for non queries. In
SELECT statements, well, just count how many rows you get .
-mike
MVP

 

[m]

Mike-

int count = command.ExecuteNonQuery();

return ( ( count == -1 ) ? count : ((int)count / 2) );

Then why do I always have to halve the number of rows affected in order to
get an accurate result? Am I going about this wrong in some way? I
consistently get results that are double what my actual database tables
reflect, in both delete and bulk insert queries.

Thanks

 

[Michael Giagnocavo [MVP]]

Do you have any triggers? One way would be to try using the SQL Query
analyser and see what's going on. I believe you can do a SELECT @@ROWCOUNT
after your query.
-mike
MVP

 

[Guest]

I ran an identical bulk insert query in Query Analyzer and got the correct
number of rows import, 1520. However, with code, I always get 3040 for some
reason. Do you have any ideas why I am getting double the rows affected on
bulk inserts? This has happened on a dev box and our live boxes(we are
currently halving num rows affected in order to get accurate results from
the function, but now I am trying again because I don't like that band-aid
approach )

Thanks!
M

Query Analyzer:
BULK INSERT residential FROM
'\\workskibbs\c$\databases\arizona\residentialExport.txt' WITH
(FIELDTERMINATOR = '|', ROWTERMINATOR='\n')

C# Code Chunk:
private int importData( string tableName )

{

string query = "BULK INSERT " + tableName + " FROM '" + OutputDirectory +
tableName + ".txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR='\n')";


SqlConnection connection;

connection = new SqlConnection(connectionString);

SqlCommand command = new SqlCommand( query, connection );

try

{

connection.Open();

command.CommandTimeout = 6000;

int count = command.ExecuteNonQuery();

Console.WriteLine( "Imported " + count.ToString() + " rows into the " +
tableName + " table..." );


return count;

}

catch( Exception yy )

{

new ErrorLog( yy.ToString() );

return -1;

}

finally

{

if( connection != null )

if( connection.State == ConnectionState.Open )

connection.Close();

connection.Dispose();

if( command != null )

command.Dispose();

}

}