querying the sql database - Using Umbraco and getting started

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

Jan Egil Kristiansen 37 posts 160 karma points

Jul 17, 2017 @ 15:57

0

Querying the SQL database?

I'd like to run a query against the SQL database. Specifically, I want

select max(updateDate) from cmsDocument

As I understand it, I need an SqlConnection object to execute the query. I suppose Umbraco already has a connection, if I only can get hold of it?

(Based on the assumption that this SQL query is faster than looping through all the documents. I hear something about documents being cached, so maybe not?)

Copy Link
Marcio Goularte 388 posts 1360 karma points

Jul 17, 2017 @ 17:31
101
You can use DatabaseContext that already has sqlconnection:
```
  var db = ApplicationContext.Current.DatabaseContext.Database;
   var result = db.Query<DateTime>("select max(updateDate) from cmsDocument");
```
Copy Link
Jan Egil Kristiansen 37 posts 160 karma points

Jul 17, 2017 @ 22:33
0
And for ignorants like myself, I have to add
```
modificationStamp = result.ElementAt<DateTime>(0);
```
Copy Link
Jan Egil Kristiansen 37 posts 160 karma points

Jul 17, 2017 @ 21:18
0
I got it working with
```
umbraco.DataLayer.IRecordsReader reader;
reader = umbraco.BusinessLogic.Application.SqlHelper.
    ExecuteReader("select max(updateDate) as modified from cmsDocument");
reader.Read();
modificationStamp = reader.GetDateTime("modified") ;
```
My reader is an Umbraco object, while Marcio's result is I think a C# object? The latter is more tempting to me.

I didn't find documentation for neither object, but I suppose Visual studio will help me out with Query objects, too.
Copy Link
Marcio Goularte 388 posts 1360 karma points

Jul 17, 2017 @ 21:58

0

What version of umbraco are you using? This code is using legacy api. I've never used it that way.

Copy Link
Jan Egil Kristiansen 37 posts 160 karma points

Jul 17, 2017 @ 22:24

0

7.1.8

Copy Link
pranjal 75 posts 188 karma points

Apr 17, 2018 @ 09:31

0

var db = ApplicationContext.Current.DatabaseContext.Database;

var query = new Sql("select max(updateDate) from cmsDocument");

var cmsData = db.Fetch(query);

1) - please refer this query => "db.Fetch

( This will fetch max update date from your table, note: - u must have to specify your model name through which data will be bind )

Copy Link
is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?

Querying the SQL database?