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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[c#] Select query to get data from SQL Server

Examples related to c#

Examples related to sql

Examples related to asp.net

Examples related to sql-server