Select a query to retrieve data from SQL Server

Question

I am trying to run a SQL Select query in C # code. But I always get -1 output on

int result = command.ExecuteNonQuery(); 

However, the same table, if I use for delete or insert , works ...

ConnectString also ConnectString .

Please check below code

 SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password="); conn.Open(); SqlCommand command = new SqlCommand("Select id from [table1] where name=@zip ", conn); //command.Parameters.AddWithValue("@zip","india"); int result = command.ExecuteNonQuery(); // result gives the -1 output.. but on insert its 1 using (SqlDataReader reader = command.ExecuteReader()) { // iterate your results here Console.WriteLine(String.Format("{0}",reader["id"])); } conn.Close(); 

The query works fine on SQL Server, but I don’t understand why only the query is not working.

All other queries work.

Answer 1

SqlCommand.ExecuteNonQuery

You can use ExecuteNonQuery to perform directory operations (for example, querying a database structure or creating database objects such as tables), or to modify data in a database without using a DataSet by doing UPDATE, INSERT or DELETE. Although ExecuteNonQuery does not return rows, any output parameters or return values ​​associated with the parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists in an inserted or updated table, the return value includes the number of rows affected by both the insert operation and the update, as well as the number of rows affected by the trigger or triggers. For all other types of operators, the return value is -1. If a rollback occurs, the return value is -1.

SqlCommand.ExecuteScalar Method Executes the Transact-SQL statement for the join and returns the number of rows affected.

So it will not work. statements returned by the SELECT statement, you must use the ExecuteScalar method.

Link: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

So try below code:

 SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password="); conn.Open(); SqlCommand command = new SqlCommand("Select id from [table1] where name=@zip ", conn); command.Parameters.AddWithValue("@zip","india"); // int result = command.ExecuteNonQuery(); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { Console.WriteLine(String.Format("{0}",reader["id"])); } } conn.Close(); 

Answer 2

According to MSDN

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

the result is the number of rows affected , and since your query is select , the rows are not affected (i.e., inserted, deleted or updated).

If you want to return a single query string, use ExecuteScalar() instead of ExecuteNonQuery() :

  int result = (int) (command.ExecuteScalar()); 

However, if you expect many lines to return, ExecuteReader() is the only option:

  using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { int result = reader.GetInt32(0); ... } } 

Answer 3

you can use ExecuteScalar() instead of ExecuteNonQuery() to get a single result use it like this

 Int32 result= (Int32) command.ExecuteScalar(); Console.WriteLine(String.Format("{0}", result)); 

It will execute the query and return the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

As you want to return only one row, remove this using SqlDataReader from your code

 using (SqlDataReader reader = command.ExecuteReader()) { // iterate your results here Console.WriteLine(String.Format("{0}",reader["id"])); } 

as it will execute your command again and affect the performance of your page.

Answer 4

This is by design.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists in an inserted or updated table, the return value includes the number of rows affected by both the insert operation and the update, as well as the number of rows affected by the trigger or triggers. For all other types of operators, the return value is -1. If a rollback occurs, the return value is -1.

Answer 5

You should use ExecuteScalar() (which returns the first column of the first row) instead of ExecuteNonQuery() (which returns the number of rows affected).

For more details, you should include the differences between execcescalar and executenonquery .

Hope this helps!

Answer 6

you need to add parameter also @zip

  SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password="); conn.Open(); SqlCommand command = new SqlCommand("Select id from [table1] where name=@zip ", conn); // // Add new SqlParameter to the command. // command.Parameters.AddWithValue("@zip","india"); int result = (Int32) (command.ExecuteScalar()); using (SqlDataReader reader = command.ExecuteReader()) { // iterate your results here Console.WriteLine(String.Format("{0}",reader["id"])); } conn.Close();