选择查询以从SQL Server获取数据 [英] Select query to get data from SQL Server

问题描述

我正在尝试在C#代码中运行SQL Select查询.但是我总是在

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

但是，如果我将delete或insert用于同一表，则可以使用...

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

ConnectString也可以.

请检查下面的代码

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

查询在SQL Server上工作正常，但是我不明白为什么只有选择查询无法正常工作.

The query works fine on SQL Server, but I am not getting why only select query is not working.

所有其他查询都有效.

推荐答案

SqlCommand.ExecuteNonQuery方法

您可以使用ExecuteNonQuery执行目录操作(例如，查询数据库的结构或创建数据库对象(例如表))，或通过执行UPDATE，INSERT或DELETE语句. 尽管ExecuteNonQuery不返回任何行，但是将使用数据填充任何输出参数或映射到参数的返回值. 对于UPDATE，INSERT和DELETE语句，返回值是该命令影响的行数.当要插入或更新的表上存在触发器时，返回值包括受插入或更新操作影响的行数以及受一个或多个触发器影响的行数.对于所有其他类型的语句，返回值为-1.如果发生回滚，则返回值为-1.

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方法 针对该连接执行Transact-SQL语句，并返回受影响的行数.

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

因此无法获得. SELECT语句返回的语句，您必须使用ExecuteScalar方法.

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

因此请尝试以下代码:

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

这篇关于选择查询以从SQL Server获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！