C#: How to get data from the last row in database? I'm using in visual studio 2017

Question

Need help in C# ( Visual Studio 2017 )

I want to get a data from my database from the last row or last inputted data. My code is here..

            String query = "SELECT TOP(1) MessageNumber FROM ncslbpHighWay";
            SqlCommand SDA = new SqlCommand(query, AR);

            SqlDataReader data = SDA.ExecuteReader();
            if (data.Read())
            {
                textBox2.Text = data.GetValue(0).ToString();
            }
            AR.Close();

I already got the data but from the specific column at the top only. I don't know how to get the bottom value. Also i tried the DESC but it doesn't work.

String query = "SELECT TOP(1) MessageNumber FROM ncslbpHighWay ORDER BY COLUMN DESC";

This is my first question here in Stackoverflow. I hope someone would help me on this.

How about `SELECT * FROM ncslbpHighWay ORDER BY COLUMN DESC LIMIT 1`? — Tetsuya Yamamoto, Jul 13 '17 at 03:53
It would be easier to answer if you could display the table structure. — Shakti Prakash Singh, Jul 13 '17 at 03:53

score 1 · Answer 1 · answered Jul 13 '17 at 04:21

1

Considering your question below snippet can help you.

-- Method 01--
SELECT * FROM TestData where ID =(Select Max(ID) from TestData)

-- Method 02--
SELECT top 1 * FROM TestData order by ID Desc;

Here I have consider ID columns as Auto Increment.

answered Jul 13 '17 at 04:21

Pramod Sutar

70
1
5

score 0 · Answer 2 · answered Jul 13 '17 at 03:53

0

you can use orm. Things will become easy.

answered Jul 13 '17 at 03:53

kangcheng chu

1
3

score 0 · Accepted Answer · answered Jul 13 '17 at 04:21

Your specified query below works for SQL Server for getting last value in table:

SELECT TOP(1) MessageNumber FROM ncslbpHighWay ORDER BY [ColumnName] DESC

However since you're mentioning MySQL tag (implying you're using MySQL instead of SQL Server), you need to use LIMIT n after ORDER BY instead, where n is the number of returned results, i.e. 1 for single result:

SELECT MessageNumber FROM ncslbpHighWay ORDER BY [ColumnName] DESC LIMIT 1

-- or using offset:
SELECT MessageNumber FROM ncslbpHighWay ORDER BY [ColumnName] DESC LIMIT 0, 1

If you're using MySql.Data.MySqlClient.MySqlConnection instead using standard System.Data.SqlClient.SqlConnection, you can write data retrieval like this:

using (MySqlConnection AR = new MySqlConnection())
{
    AR.Open();
    String query = "SELECT MessageNumber FROM ncslbpHighWay ORDER BY [ColumnName] DESC LIMIT 1";
    using (MySqlCommand SDA = new MySqlCommand(query, AR))
    {
        MySqlDataReader data = SDA.ExecuteReader();
        if (data.Read())
        {
            textBox2.Text = data.GetValue(0).ToString();
        }
    }
    AR.Close();
}

NB: I recommend you using ORM (e.g. Entity Framework) with MySQL Connector .NET library to enable LINQ functionality when managing database queries.

Similar issues:

How to select the last record from MySQL table using SQL syntax

Select last row in MySQL

C#: How to get data from the last row in database? I'm using in visual studio 2017

3 Answers3