locked
reading datatable RRS feed

  • Question

  • User-1091696784 posted

    I can read data

    var query = "select * from (table name)";

    var dt = db.Query(query);

    after getting dt, I like to read it.

    1) I like to use for loop instead of foreach loop.

    2) I like to get column names if possible.  If not, use column index to read each field.

    Thank you in advance.

    Wednesday, May 6, 2015 6:15 PM

Answers

  • User-821857111 posted
    @for(var i = 0; i < dt.Count(); i++){
        var row = dt.ElementAt(i);
        <div>
        @foreach(var col in row.GetDynamicMemberNames()){
            <text>@col: @row[col]</text>
        }
        </div>
    }

    Note the parentheses after Count, and the use of ElementAt to access items by index. The return type from the Database.Query method is an Enumerable, so you need to use Enumerable Extension methods on it. The GetDynamicMemberNames method returns the column names.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 8, 2015 2:20 AM

All replies

  • User-1716253493 posted
                DataTable dt = db.Query(query);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string value = dt.Rows[i]["colname"].ToString();
                }

    Wednesday, May 6, 2015 9:34 PM
  • User281315223 posted

    1) I like to use for loop instead of foreach loop.

    You should always be able to use a basic for-loop and iterate through each item of your collection (by index) :

    for (int i = 0; i < YourDataTable.Rows.Count; i++)
    {
           // Access each row by it's index using YourDataTable.Rows[i]
    }

    2) I like to get column names if possible.  If not, use column index to read each field.

    Using indices to access the columns can be annoying, you can usually use the column name as an indexer as well :

    for (int i = 0; i < YourDataTable.Rows.Count; i++)
    {
           // Access each row by it's index using YourDataTable.Rows[i]
           var property = YourDataTable.Rows[i]["Property"];
    }

    Wednesday, May 6, 2015 9:48 PM
  • User616014865 posted

    In Razor 3,     db.Query comes back as a System.Collection.ObjectModel.ReadOnlyCollection[SystemObject] and it cannot be casted to System.Data.DataTable.

    Am I missing something?   

    Thursday, May 7, 2015 11:38 AM
  • User281315223 posted

    In Razor 3,     db.Query comes back as a System.Collection.ObjectModel.ReadOnlyCollection[SystemObject] and it cannot be casted to System.Data.DataTable.

    Am I missing something?   

    I suppose I didn't see the fact that this was targeting Razor / Web Pages. However you should still be able to iterate through the results as expected using the approaches above :

    var dt = db.Query(query);
    for(int i = 0; i < dt.Count; i++)
    {
         // Get the contents for this specific row here (by index)
         var currentObject = dt[i];
    }

    You may also be able to access the specific property name directly using either an indexer or as a property :

    var dt = db.Query(query);
    for(int i = 0; i < dt.Count; i++)
    {
         // Attempt to get a property called "YourProperty" in two different ways
         var a = dt[i]["YourProperty"];
         var b = dt[i].YourProperty;
    }

    I'm not currently around an environment to test this out, but hopefully this helps.

    Thursday, May 7, 2015 12:00 PM
  • User-821857111 posted
    @for(var i = 0; i < dt.Count(); i++){
        var row = dt.ElementAt(i);
        <div>
        @foreach(var col in row.GetDynamicMemberNames()){
            <text>@col: @row[col]</text>
        }
        </div>
    }

    Note the parentheses after Count, and the use of ElementAt to access items by index. The return type from the Database.Query method is an Enumerable, so you need to use Enumerable Extension methods on it. The GetDynamicMemberNames method returns the column names.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 8, 2015 2:20 AM
  • User616014865 posted

    Thank you very much.  Your code worked.

    I plan to make a static class function that returns DataRow from (connectionstring, sql statement).

    One has to think about all datatype.

    But I think it is worthwhile.

    I will post it here when it gets done.

    If you have something along this line, please post it here too.

    Monday, May 11, 2015 1:54 PM