Getting rowcount from a stored procedure

  • Thread starter Thread starter Richard G
  • Start date Start date
R

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
 
The SqlCommand.ExecuteNonQuery will return the rowcount for non queries. In
SELECT statements, well, just count how many rows you get :).
-mike
MVP
 
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
 
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
 
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();

}

}
 
Back
Top