如何转换数据读取器来动态查询结果 [英] How to convert a data reader to dynamic query results

问题描述

我有一种观点认为，通常会从一个WebMatrix的查询查询结果（的IEnumerable<动态> 数据类型），并显示在表中的结果：

I have a View that typically gets query results from a WebMatrix Query (IEnumerable<dynamic> data type), and displays the results in a table:

@model MySite.Models.Entity
@foreach(var row in Model.Data)
{
    <tr>
        @foreach (var column in row.Columns)
        {
            <td>@column<span>:</span> @row[column]</td>
        }
    </tr>
}

下面是我的模型，我查询数据库：

Here's my model where I query the database:

public class Entity
{
    public dynamic Data {get; set; }
    public Entity(String table)
    {
        if (table == "User" || table == "Group)
        {
            WebMatrix.Data.Database db = new WebMatrix.Data.Database();
            db.Open(ConString);
            Data = db.Query("SELECT * FROM " + table);
        }
        else
        {
            using (OdbcConnection con = ne4w OdbcConnection(ConString))
            {
                OdbcCommand com = new OdbcCommand("Select * From " + table);
                command.CommandType = System.Data.CommandType.Text;
                connection.Open();
                OdbcDataReader reader = command.ExecuteReader();

下面是各种不同的事情，我已经试过从阅读各种其他职位：

Here's all the different things I've tried from reading various other posts:

                // Atempt 1
                Data = reader;
                // Error in view, 'Invalid attempt to call FieldCount when reader is closed' (on 'var row `in` Model.Data')

                // Atempt 2
                Data = reader.Cast<dynamic>;
                // Error: 'Cannot convert method group "Cast" to non-delegate type "dynamic". Did you intend to invoke the method?

                // Atempt 3
                Data = reader.Cast<IEnumerable<dynamic>>;
                // Error same as Atempt 2

                // Atempt 4
                Data = reader.Cast<IEnumerable<string>>;
                // Error same as Atempt 2
            }
        }
    }
}

我在寻找获得读者对象为的IEnumerable<的最佳途径;动态> 对象。

I'm looking for the best way to get the reader object to a IEnumerable<dynamic> object.

<青霉>请注意，这是一个简单的例子，虽然对于两个查询类型的原因并不明显，它们是必要的，我的代码。的

推荐答案

您错过基本的C＃语法。

You're missing basic C# syntax.

Data = reader;
// You cant do this. You have to loop the reader to get the values from it.
// If you simply assign reader object itself as the data you wont be 
// able to get data once the reader or connection is closed. 
// The reader is typically closed in the method.

Data = reader.Cast<dynamic>;
// You should call the Cast method. And preferably execute the resulting query. 
// As of now you're merely assigning method reference to a variable
// which is not what you want. 
// Also bear in mind that, as I said before there's no real benefit in casting to dynamic

Data = reader.Cast<IEnumerable<dynamic>>;
// Cast method itself returns an IEnumerable. 
// You dont have to cast individual rows to IEnumerable

Data = reader.Cast<IEnumerable<string>>;
// Meaningless I believe. 
// The data you get from database is not always strings

您做出的重大失误是的不是调用方法这是你想要什么：

The major mistake you make is not calling the method. This is what you want:

Data = reader.Cast<IDataRecord>().ToList();
                               ^^ // notice the opening and closing parentheses

---

您可以去这一批取决于什么是更容易的过程（比如，在前端显示）的方式。

---

You could go about this a number of ways depending on what is easier to process (say, to display in front-end).

1. 返回的数据记录

1. Return data records.

public IEnumerable<IDataRecord> SelectDataRecord()
{
    ....

    using (var reader = cmd.ExecuteReader())
        foreach (IDataRecord record in reader as IEnumerable)
            yield return record; //yield return to keep the reader open
}

返回ExpandoObjects。 或许这就是你想要的东西

public IEnumerable<dynamic> SelectDynamic()
{
    ....

    using (var reader = cmd.ExecuteReader())
    {
        var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
        foreach (IDataRecord record in reader as IEnumerable)
        {
            var expando = new ExpandoObject() as IDictionary<string, object>;
            foreach (var name in names)
                expando[name] = record[name];

            yield return expando;
        }
    }
}

归来序列属性包

Return sequence of property bag

public IEnumerable<Dictionary<string, object>> SelectDictionary()
{
    ....

    using (var reader = cmd.ExecuteReader())
    {
        var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
        foreach (IDataRecord record in reader as IEnumerable)
            yield return names.ToDictionary(n => n, n => record[n]);
    }
}

普通的对象数组<归来序列/ p>

Return sequence of plain object array

public IEnumerable<List<object>> SelectObjectArray()
{
    ....

    using (var reader = cmd.ExecuteReader())
    {
        var indices = Enumerable.Range(0, reader.FieldCount).ToList();
        foreach (IDataRecord record in reader as IEnumerable)
            yield return indices.Select(i => record[i]).ToList();
    }
}

返回的数据行

Return data rows

public IEnumerable<DataRow> SelectDataRow()
{
    ....

    using (var reader = cmd.ExecuteReader())
    {
        var table = new DataTable();
        table.BeginLoadData();
        table.Load(reader);
        table.EndLoadData();
        return table.AsEnumerable(); // in assembly: System.Data.DataSetExtensions
    }
}

最后但并非最不重要的，如果有帮助，你可以返回一个强类型的序列，无需任何手动管道。您可以使用表达式目录树在运行时编译代码。请参见这为如

这篇关于如何转换数据读取器来动态查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！