SqlDataReader 列序号

发布于 2024-07-21 04:44:24 字数 835 浏览 6 评论 0原文

假设我正在调用一个查询“SELECT name, city, Country FROM People”。执行 SqlDataReader 后，列的顺序是否与 sql 查询中的顺序相同？

换句话说，我可以相信以下代码将始终正确工作：

SqlConnection connection = new SqlConnection(MyConnectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT [name], [city], [country] WHERE [id] = @id";

try
{
    connection.Open();
    SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleRow);

    if (reader.Read())
    {
        // Read values.
        name = reader[0].ToString();
        city = reader[1].ToString();
        country = reader[2].ToString();
    }
}
catch (Exception)
{
    throw;
}
finally
{
    connection.Close();
}

另外，如果我使用列名而不是序数（reader[“name”]），我会损失多少性能？

是否有任何官方微软文档描述 SqlDataReader 中列排序的行为？

原文

Suppose I am calling a query "SELECT name, city, country FROM People". Once I execute my SqlDataReader do columns come in the same order as in my sql query?

In other words can I rely that the following code will always work correctly:

SqlConnection connection = new SqlConnection(MyConnectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT [name], [city], [country] WHERE [id] = @id";

try
{
    connection.Open();
    SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleRow);

    if (reader.Read())
    {
        // Read values.
        name = reader[0].ToString();
        city = reader[1].ToString();
        country = reader[2].ToString();
    }
}
catch (Exception)
{
    throw;
}
finally
{
    connection.Close();
}

Also how much performance do I lose if I use column names instead of ordinals (reader["name"])?

Are there any official microsoft documents describing the behavior of column ordering in SqlDataReader?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

待天淡蓝洁白时 2024-07-28 04:44:25

我完全同意 Josh 的观点 - 字段的位置确实如您在 SQL 查询文本中指定的那样。

但是：我仍然更喜欢使用列名，因为它更强大。例如，如果您需要向 SQL 查询添加一个字段怎么办？

command.CommandText = "SELECT [name], [jobtitle], [city], [country] WHERE [id] = @id";

现在突然你必须重写所有代码来更改位置......

我通常在枚举数据读取器返回的所有行的循环之外所做的就是确定我感兴趣的每个字段的位置：

int namePosition = reader.GetOrdinal("name");
int cityPosition = reader.GetOrdinal("city");

然后我在处理数据的循环中使用这些位置来快速访问各个字段。这样，您只需确定一次位置，但您可以在循环数据时使用位置 - 两全其美！ :-)

马克

I totally agree with Josh - the positions of the fields are indeed such as you specify them in your SQL query text.

BUT: I would still prefer to use the column names, since it's more robust. E.g. what if you need to add a field to your SQL query?

command.CommandText = "SELECT [name], [jobtitle], [city], [country] WHERE [id] = @id";

Now suddenly you have to rewrite all your code to change the positions....

What I normally do outside the loop that enumerates through all the rows returned by the data reader is determine the positions of each field I'm interested in:

int namePosition = reader.GetOrdinal("name");
int cityPosition = reader.GetOrdinal("city");

and then I use these positions inside my loop handling the data to get quick access to the individual fields. That way you determine the positions only once, but you're using positions in your looping over the data - the best of both worlds! :-)

Marc

回复收藏 0 原文

余生一个溪 2024-07-28 04:44:25

这个例子是最容易维护和最容易阅读的：

int? quantity = reader.Get<int?>("Quantity");
Guid token = reader.Get<Guid>("Token");

它依赖于我创建的以下扩展方法。它执行数据库空值检查，在未找到字段时提供信息性错误消息，并且在重新对齐列时不会中断。

internal static T Get<T>(this SqlDataReader reader, string fieldName)
{
    int ordinal;
    try
    {
        ordinal = reader.GetOrdinal(fieldName);
    }
    catch (IndexOutOfRangeException)
    {
        throw new IndexOutOfRangeException(string.Format("Field name '{0}' not found.", fieldName));
    }

    return !reader.IsDBNull(ordinal) ? (T)reader.GetValue(ordinal) : default(T);
}

This example is the most maintainable and easiest to read:

int? quantity = reader.Get<int?>("Quantity");
Guid token = reader.Get<Guid>("Token");

It relies on the following extension method I created. It performs DB null checks, provides an informative error message when field is not found, and does not break when columns are re-aligned.

internal static T Get<T>(this SqlDataReader reader, string fieldName)
{
    int ordinal;
    try
    {
        ordinal = reader.GetOrdinal(fieldName);
    }
    catch (IndexOutOfRangeException)
    {
        throw new IndexOutOfRangeException(string.Format("Field name '{0}' not found.", fieldName));
    }

    return !reader.IsDBNull(ordinal) ? (T)reader.GetValue(ordinal) : default(T);
}

回复收藏 0 原文