c# - Select query to get data from SQL Server

Question

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

int result = command.ExecuteNonQuery();

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

ConnectString is also fine.

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 am not getting why only select query is not working.

All other queries are working.

Answer 1

SqlCommand.ExecuteNonQuery Method

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements. Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to 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 on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

SqlCommand.ExecuteScalar Method Executes a Transact-SQL statement against the connection and returns the number of rows affected.

So to get no. of statements returned by SELECT statement you have to use ExecuteScalar method.

Reference: 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();