Sign in Register
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
    Jan Egil Kristiansen
    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 389 posts 1362 karma points
    Jul 17, 2017 @ 17:31
    Marcio Goularte
    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
    Jan Egil Kristiansen
    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
    Jan Egil Kristiansen
    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 389 posts 1362 karma points
    Jul 17, 2017 @ 21:58
    Marcio Goularte
    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
    Jan Egil Kristiansen
    0

    7.1.8

    Copy Link
  • pranjal 75 posts 188 karma points
    Apr 17, 2018 @ 09:31
    pranjal
    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

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies