C# 以正确的方式从 SQL Server 获取单个记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29864990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get a single record from SQL Server the correct way
提问by Luke101
I'm using Ado to retrieve a single record by id. Observe:
我正在使用 Ado 按 id 检索单个记录。观察：
public async Task<Image> GetImage(int id)
{
var image = new Image();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
string sql = @" SELECT * FROM Images where id = @id";
using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.Parameters.AddWithValue("@id", id);
var reader = await comm.ExecuteReaderAsync();
int ordId = reader.GetOrdinal("id");
int ordName = reader.GetOrdinal("name");
int ordPath = reader.GetOrdinal("path");
while (reader.Read())
{
image.Id = reader.GetInt32(ordId);
image.Name = reader.GetString(ordName);
image.Path = reader.GetString(ordPath);
}
return image;
}
}
}
As you can see I am using While to iterate through the records. Since while is signifying there may be more than one record to iterate I believe this may be the wrong way to get get a single record. Considering ADO has ExecuteScalar for one row one field maybe they have a specified way for one row multiple fields. Is there a specified way to get a single record in ADO?
如您所见，我正在使用 While 来遍历记录。由于 while 表示可能有多个记录要迭代，我相信这可能是获取单个记录的错误方法。考虑到 ADO 对一行一个字段有 ExecuteScalar，也许他们对一行多个字段有指定的方式。是否有指定的方法在 ADO 中获取单个记录？
采纳答案by Douglas
I would go with your current approach, except that I'd eliminate the while loop. If you want to ensure that only one record is returned, perform an extra Readto ensure it returns false. This is similar to the semantics of the LINQ Singleoperator.
我会采用你目前的方法，只是我会消除 while 循环。如果要确保仅返回一条记录，请执行额外操作Read以确保它返回 false。这类似于 LINQSingle运算符的语义。
if (!reader.Read())
throw new InvalidOperationException("No records were returned.");
image.Id = reader.GetInt32(ordId);
image.Name = reader.GetString(ordName);
image.Path = reader.GetString(ordPath);
if (reader.Read())
throw new InvalidOperationException("Multiple records were returned.");
Assuming that the idcolumn in your database is a primary key (unique), there is no need to specify a TOPclause in the SQL query; the SQL Server query optimizer would deduce that only at most one record is returned due to the WHEREclause. However, if you don't have a primary key or unique index/constraint on the idcolumn, then you should issue a TOP (2)clause to restrict the number of returned rows. You should avoid using TOP (1)because you would be unable to detect (and raise an error for) extra matches.
假设id你数据库中的列是主键（唯一），那么TOPSQL查询中就不需要指定子句；SQL Server 查询优化器会推断，由于该WHERE子句，最多只返回一条记录。但是，如果列上没有主键或唯一索引/约束id，则应发出TOP (2)子句来限制返回的行数。您应该避免使用，TOP (1)因为您将无法检测（并引发错误）额外的匹配项。
string sql = @"SELECT TOP (2) * FROM Images WHERE id = @id"
回答by Ehsan Sajjad
回答by Eugene Podskal
What if you just read once:
如果你只读一次会怎样：
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
string sql = @" SELECT id, name, path FROM Images where id = @id";
using (SqlCommand comm = new SqlCommand(sql, conn))
{
comm.Parameters.AddWithValue("@id", id);
using (var reader = await comm.ExecuteReaderAsync())
{
if (!reader.Read())
throw new Exception("Something is very wrong");
int ordId = reader.GetOrdinal("id");
int ordName = reader.GetOrdinal("name");
int ordPath = reader.GetOrdinal("path");
image.Id = reader.GetInt32(ordId);
image.Name = reader.GetString(ordName);
image.Path = reader.GetString(ordPath);
return image;
}
}
}
P.S.:I have also changed select statement to select only required fields and wrapped reader in using statement.
PS：我还更改了 select 语句以仅选择必填字段并将阅读器包装在 using 语句中。
