通过OleDbConnection提取单个记录的最佳方法是什么? [英] What is the best way to fetch a single record via an OleDbConnection?

问题描述

C#，. Net 2.0:我有一个类，该类包装通过OleDbConnection对象访问的数据库中的单个记录.这很简单，它执行一个"SELECT * FROM table WHERE key = {some value};".然后使用一些操作数据的方法将字段公开为属性.当我创建该对象的新实例时，执行的代码如下:

        DataSet ds = new DataSet();
        ds.Locale = CultureInfo.InvariantCulture;
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        if (cmd.Connection.State != ConnectionState.Open)
        {
            cmd.Connection.Close();
            cmd.Connection.Open();
        }

        da.Fill(ds);

        return ds.Tables[0];

cmd是传递给该方法的OleDbCommand对象.根据VS 2008分析器，执行此操作时，创建对象大约需要95％的时间在da.Fill(ds)调用中.

我还有一个类，表示实现IEnumerable的这些对象的集合，当使用foreach迭代该对象时，将即时创建每个单个记录对象，并且这些da.Fill(ds)语句会迅速加起来. /p>

我的问题是，这是获取单个记录的最佳方法吗?或者，是否有一种更优选的方法来实现集合对象，从而使其迭代过程不会花费那么长时间?

谢谢

解决方案

使用OleDbDataReader并考虑使用CommandBehavior.SingleRow.

using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
    if (reader.Read())
    {
        // Bind your object using the reader.
    }
    else
    {
        // No row matched the query
    }
}

SingleRow向底层的OLEDB提供程序提供了提示，使它可以优化其处理结果的方式.

C#, .Net 2.0: I have a class that wraps a single record from a database accessed via an OleDbConnection object. It is quite simple, it executes a "SELECT * FROM table WHERE key = {some value};" and then exposes the fields as properties with a few methods to manipulate the data. When I create a new instance of this object, the code that gets executed looks like:

        DataSet ds = new DataSet();
        ds.Locale = CultureInfo.InvariantCulture;
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        if (cmd.Connection.State != ConnectionState.Open)
        {
            cmd.Connection.Close();
            cmd.Connection.Open();
        }

        da.Fill(ds);

        return ds.Tables[0];

cmd is an OleDbCommand object passed to the method. When I execute this, around 95% of the time it takes to create the object is in the da.Fill(ds) call, according to the VS 2008 profiler.

I also have a class that represents a collection of these objects that implements IEnumerable, and when iterating that object using foreach, each single record object is created on the fly and those da.Fill(ds) statements add up quickly.

My question is, is this the best way to fetch a single record? Alternately, is there a more preferred way to implement the collection object so iterating it does not take so long?

Thanks

解决方案

Use OleDbDataReader and consider using CommandBehavior.SingleRow.

using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
    if (reader.Read())
    {
        // Bind your object using the reader.
    }
    else
    {
        // No row matched the query
    }
}

SingleRow provides a hint to the underlying OLEDB provider that allows it to optimize how it processes the result.

这篇关于通过OleDbConnection提取单个记录的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！