var r=db.execute("query")
Hi! I wanna access the columns (and rows) obtained from this query (like q[0].column1). But when I store it in a var it is not possible.
What kind of datatype or technique can I use?
Is there a difference between single row output and multiple row output when doing this?
I am working with sql server CE db (sdf file) in webmatrix.
I suppose that db is an object of Database type from the WebMatrix.Data namespace, opened with a command like
var db = Database.Open("yourDb");
If this is right, the Execute method (with the capital E) doesn't return a query, but an integer that is the count of records affected by the SQL statement executed (INSERT, UPDATE or DELETE).
The methods that return the result of a query are three: Query, QuerySingle and QueryValue.
Query returns a list of rows as IEnumerable of Objects. The following example queries a Northwind table and displays the result:
@{
var db = Database.Open("Northwind");
var data = db.Query("SELECT ProductID, ProductName FROM Products WHERE SupplierID = 1");
foreach (var row in data){
<p>@row.ProductID - @row.ProductName</p>
}
}
QuerySingle returns a single row as Object, e.g.:
@{
var db = Database.Open("Northwind");
var data = db.QuerySingle("SELECT ProductID, ProductName FROM Products WHERE ProductID = 10");
<p>@data.ProductID - @data.ProductName</p>
}
QueryValue returns a single value as Object:
@{
var db = Database.Open("Northwind");
var data = db.QueryValue("SELECT UnitPrice FROM Products WHERE ProductID = 14");
<p>The price of product n. 14 is: @data</p>
}
Your DB is going to return a table or a dataset. In order, to access the data in each cell you need to loop through the DataTable or DataSet rows and specify each column index or name.
