CODEWITHSUNDEEP
c#sqlasp.netsql-server
user3226440
user3226440

Reputation: 559

Select query to get data from SQL Server

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.

Upvotes: 31

Views: 392290

Answers (6)

immayankmodi
immayankmodi

Reputation: 8600

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

You should refer differences between executescalar and executenonquery for more details.

Hope it helps!

Upvotes: 3

Dhwani
Dhwani

Reputation: 7626

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

Upvotes: 52

Vikas Rana
Vikas Rana

Reputation: 1999

you can use ExecuteScalar() in place 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 returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

As you want only one row in return, remove this use of SqlDataReader from your code

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

because it will again execute your command and effect your page performance.

Upvotes: 5

V2Solutions - MS Team
V2Solutions - MS Team

Reputation: 1127

you have 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();

Upvotes: 3

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186833

According to MSDN

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

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

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

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

However, if you expect many rows to be returned, ExecuteReader() is the only option:

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

      ...
    }
  }

Upvotes: 18

aliensurfer
aliensurfer

Reputation: 1650

That 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 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.

Upvotes: 3

Related Questions