[c#] 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.

This question is related to c# sql asp.net sql-server

The answer is


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

      ...
    }
  }

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!


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.


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.


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

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to asp.net

RegisterStartupScript from code behind not working when Update Panel is used You must add a reference to assembly 'netstandard, Version=2.0.0.0 No authenticationScheme was specified, and there was no DefaultChallengeScheme found with default authentification and custom authorization How to use log4net in Asp.net core 2.0 Visual Studio 2017 error: Unable to start program, An operation is not legal in the current state How to create roles in ASP.NET Core and assign them to users? How to handle Uncaught (in promise) DOMException: The play() request was interrupted by a call to pause() ASP.NET Core Web API Authentication Could not load file or assembly 'CrystalDecisions.ReportAppServer.CommLayer, Version=13.0.2000.0 WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for jquery

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd