从 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屋!