Getting data from Database object without using foreach

Question

I understand this method of getting DB data (using foreach):

  var db = Database.Open("Connection");
  var rows = db.Query("SELECT 1 columnName,2 columnName2 FROM Table");

  foreach(var row in rows){
      var data = row.columnName;
      //or
      var data = row[0];          
  }

This works, but how do I get the data without using a foreach?

  var data = rows[0][1];

^ This doesn't work.

Basically, I am trying to figure out how to get the data without using a foreach. What would I have to do?

---

EDIT:

.Query() returns:

Type: System.Collections.Generic.IEnumerable

The rows returned by the SQL query.

As shown here: http://msdn.microsoft.com/en-us/library/webmatrix.data.database.query%28v=vs.111%29.aspx

Answer 1

You can use the ElementAt method to get to the row then use indexing to get to the column of that row:

var data = rows.ElementAt(0)[0];

You can even reference the columns by name as you are actually receiving an IEnumerable<dynamic>:

var row = rows.ElementAt(0);
//now you can access:
//row.columnName
//row.columnName2

Answer 2

Using ToArray() on your collection is an option;

var results = db.Query("SELECT * FROM Table").ToArray();

Console.WriteLine(results[1][2]);

This will allow you to reference your result set the way you want. However, if you don't bound your collection in the query somehow, you could end up loading a large collection into memory to reference it this way. I've never seen the WebMatrix Database before, so I can't give any tips on doing this in the most efficient manner, but ToArray() will give you what you want.

db.Query actually returns type DynamicRecord, so if you want to reference your column names by name, you can do;

var results = db.Query("SELECT * FROM Table").Cast<DynamicRecord>().ToArray();

Console.WriteLine(results[0]["Id"]);

Now you can use column names

And as petelids mentions,

IEnumerable<dynamic> results = db.Query("SELECT * FROM Table");

Console.WriteLine(results.ElementAt(0).Id);

Answer 3

If you are wanting a specific answer for the WebMatrix.Data example, then this won't help you at all.

However, if you like writing actual SQL statements, but don't like tedious mapping code, then I like to use micro-ORMs like OrmLite, PetaPoco, Massive, Dapper, etc...

This is an example using my favorite: http://www.toptensoftware.com/petapoco/

http://www.nuget.org/packages/petapoco

/// <summary>
/// Create a Poco mapping class where the class name matches the Table name 
/// and the properties match the column names and data types from the table
/// </summary>
public class Table{  
     public int ColumnName {get;set;} 
     public int ColumnName2 {get;set;}
}

 int id = 1;

 var db = new Database("Connection Name");

 const string sql = @"SELECT 
                        1 columnName,
                        2 columnName2 
                      FROM Table 
                      WHERE ColumnName = @0";

 return db.FirstOrDefault<Table>(sql, id);

 or

 // Using auto-generated Select statement
 return db.FirstOrDefault<Table>("WHERE ColumnName = @0", id);

 // Fetch all records...
 return db.Fetch<Table>("");

 // PetaPoco also supports dynamic
 return db.FirstOrDefault<dynamic>(sql, id);

Answer 4

If you are using DataTable use:

datatable.rows[0][1].toString()

or if you are using an IEnumerable object use

objectName[0].propertyName.toString()

Here the propertyName is the name of the property you are using for that DataColumn.

From http://msdn.microsoft.com/en-us/library/webmatrix.data.database.query%28v=vs.111%29.aspx i am seeing query method returns IEnumerable objects so you may need the second way.

Thank you

Answer 5

What does db.Query returns is some information we need. What you can try is fill your result in a datatable.

And then getting it back like this:

DataTable dt = db.Query("SELECT 1 columnName,2 columnName2 FROM Table");
string s=  dt.Rows[0][1].ToString();

But to be honest as long as you can get 0 or more then 1 results back you want to use a loop to irate through.