Read the latest record on a table

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? 

Answer 1

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.

Answer 2

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());

Answer 3

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.

Answer 4

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. 

Answer 5

You may want to try this method instead:

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

Answer 6

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.

Answer 7

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!