从 C# 代码执行 SQL 查询 [英] Executing SQL query from C# code

问题描述

我正在尝试从 C# 代码执行 SQL 查询，但它不返回任何值，但是当我将查询直接写入 SQL 时，它工作正常.

I'm trying to execute SQL query from C# code, but it doesn't return any values, but when I write query directly to SQL it works fine.

static int TestGettingData()
{
        int rows;
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("SELECT [UserName] FROM [aspnet_Users]", connection))
            {
                connection.Open();
                rows = command.ExecuteNonQuery();
            }
        }
        return rows;
}

和连接字符串:

<connectionStrings>
<add name ="DefaultConnection" connectionString="Server=myServer;
            Database=MyDatabase;User 
            Id=User;
            Password=password;
            MultipleActiveResultSets=True;"
            providerName="System.Data.SqlClient"/>
</connectionStrings>

并且 TestGettingData() 总是返回 -1.

And TestGettingData() always returning -1.

推荐答案

有多种方法可以从数据库中取出数据

There are multiple ways to get Data out of your Database

ExecuteScalar

如果你有一个结果字段，你可以使用

if you have one result field you can use

string Command = "SELECT [UserName] FROM [aspnet_Users];";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    myConnection.Open();
    using (SqlCommand myCommand = new SqlCommand(Command, myConnection))
    {
        string Result = (string)myCommand.ExecuteScalar(); // returns the first column of the first row
    }
}

SqlDataAdapter

如果您需要多行/多列，您可以通过

if you expect multiple rows / columns you can load it into a DataTable by

string Command = "SELECT [UserName] FROM [aspnet_Users]";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    using (SqlDataAdapter myDataAdapter = new SqlDataAdapter(Command, myConnection))
    {
        DataTable dtResult = new DataTable();
        myDataAdapter.Fill(dtResult);
    }
}

SqlDataReader

另一个解决方案是 SQLDataReader.例如.如果要将列的所有行加载到列表中.这比 DataAdapter 的开销更少.

Another solution is the SQLDataReader. E.g. if you want to load all rows of a column into a List. This has less overhead than the DataAdapter.

List<string> Result = new List<string>();
string Command = "SELECT [UserName] FROM [aspnet_Users];";
using (SqlConnection mConnection = new SqlConnection(ConnectionString))
{
    mConnection.Open();
    using (SqlCommand cmd = new SqlCommand(Command, mConnection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Result.Add((string)reader[0]);
            }
        }
    }
}

这篇关于从 C# 代码执行 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！