locked
Read the latest record on a table RRS feed

  • Question

  • Hi i have a db named database.db and a table named system. I want to read the systems latest record. 

    I have my db connected to my program and i can read and write to in but don't know how to sort. Any hints? 


    • Edited by g90 Tuesday, December 30, 2014 10:12 AM
    Tuesday, December 30, 2014 10:12 AM

Answers

  • This will show the value of the first column:

    Console.WriteLine(r[0]);

    ...and this wull show the value of the "id" column (change if to whatever column you want to display):


    Console.WriteLine(r["id"]);


    >is there an "entity framework" way?

    There is an OrderByDescending method:

    var lastRow = yourContext.System.OrderByDescending(x => x.id).FirstOrDefault();
    

    Please remember to mark all helpful posts as answer and/or helpful.

    • Marked as answer by g90 Tuesday, December 30, 2014 12:02 PM
    Tuesday, December 30, 2014 11:53 AM
  • You may want to try this method instead:

    using (var myEntities = new dataEntities())
    {
    var lastValue = (from values in myEntities.PointValues
    orderby values.id).Last();
    }


    • Marked as answer by g90 Tuesday, December 30, 2014 1:13 PM
    Tuesday, December 30, 2014 12:27 PM
  • It returns an instance of the last entity. To get the values you should access the properties of the entity object, e.g.:

    var lastRow = yourContext.System.OrderByDescending(x => x.id).FirstOrDefault();
    string id = lastrow.id; //gets the id
    

    Please remember to mark all helpful posts as answer and/or helpful.

    • Marked as answer by g90 Tuesday, December 30, 2014 1:13 PM
    Tuesday, December 30, 2014 12:57 PM

All replies

  • Does the system table contains some primary key column (id) that gets auto-incremented when you add a new row to the table? Or do you have some column that stores the date and time when the row was inserted? Anyway, you must sort by a column to be able to get the latest record.

    In the following sample code, there is a column named id in the system table and I sort by this one:


    using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\database.db"))
        {
            connect.Open();
            using (SQLiteCommand fmd = connect.CreateCommand())
            {
                fmd.CommandText = @"SELECT * FROM [system] ORDER BY id DESC LIMIT 1";
    fmd.CommandType = CommandType.Text;
                SQLiteDataReader r = fmd.ExecuteReader();
                while (r.Read()) 
                {
                    //....latest record...
    
                }
        }
    

    It will return the row with the highest id. You should change "id" to the name of the column that keeps track of when the row was inserted (a counter or a datetime column).

    Please remember to mark helpful posts as answer and/or helpful.

    Tuesday, December 30, 2014 11:07 AM
  • There is an auto-incremented colum on the left side. 

    You use sql queries to retrieve the data, is there an "entity framework" way?

    How can i show in a console the newest record?? 

    Console.WriteLine(r.Read());

    • Edited by g90 Tuesday, December 30, 2014 11:24 AM
    Tuesday, December 30, 2014 11:18 AM
  • This will show the value of the first column:

    Console.WriteLine(r[0]);

    ...and this wull show the value of the "id" column (change if to whatever column you want to display):


    Console.WriteLine(r["id"]);


    >is there an "entity framework" way?

    There is an OrderByDescending method:

    var lastRow = yourContext.System.OrderByDescending(x => x.id).FirstOrDefault();
    

    Please remember to mark all helpful posts as answer and/or helpful.

    • Marked as answer by g90 Tuesday, December 30, 2014 12:02 PM
    Tuesday, December 30, 2014 11:53 AM
  • This

    var lastRow = yourContext.System.OrderByDescending(x => x.id).FirstOrDefault();


    doesn't return the values of the record.  What am i missing? I used .Take(1); and .ToString(); but i get the name of the colums instead of their values. 

    Tuesday, December 30, 2014 12:09 PM
  • You may want to try this method instead:

    using (var myEntities = new dataEntities())
    {
    var lastValue = (from values in myEntities.PointValues
    orderby values.id).Last();
    }


    • Marked as answer by g90 Tuesday, December 30, 2014 1:13 PM
    Tuesday, December 30, 2014 12:27 PM
  • It returns an instance of the last entity. To get the values you should access the properties of the entity object, e.g.:

    var lastRow = yourContext.System.OrderByDescending(x => x.id).FirstOrDefault();
    string id = lastrow.id; //gets the id
    

    Please remember to mark all helpful posts as answer and/or helpful.

    • Marked as answer by g90 Tuesday, December 30, 2014 1:13 PM
    Tuesday, December 30, 2014 12:57 PM
  • It returns an instance of the last entity. To get the values you should access the properties of the entity object, e.g.:

    var lastRow = yourContext.System.OrderByDescending(x => x.id).FirstOrDefault();
    string id = lastrow.id; //gets the id

    Please remember to mark all helpful posts as answer and/or helpful.

    It doesn't work. Here is my code so far. With yours i get the same message which is not the value but something like project_name.table_name

    table log = new table();
                log.id = 11357;
                log.item = 100;
                log.message = "Hello";
    
                Entitie db = new Entitie();
    
                db.table.Add(log);
                db.SaveChanges();
    
                var lastRow = db.table.OrderByDescending(x => x.id).FirstOrDefault();
                string id = lastRow.id.ToString(); //gets the id
                
                Console.WriteLine(lastRow);
                Console.ReadLine();

    PS. I got it right the Console.WriteLine(lastRow); was wrong. What a headache!!! Thank you guys! 
    • Edited by g90 Tuesday, December 30, 2014 1:13 PM
    Tuesday, December 30, 2014 1:10 PM