无法从SQLite数据库获取最后一行

I am new to SQLite and am trying to read the last very last row from the SQLite database file and failing massively.

我是SQLite的新手，我正在尝试从SQLite数据库文件中读取最后一行并且大量失败。

I can successfully establish a connection and get the rows but I have no idea how to get the data from the row(s).

我可以成功建立连接并获取行，但我不知道如何从行中获取数据。

I have a Table, called "Mine", like:

我有一张名为“我的”的桌子，如：

****************************************
id | name | age | time  | task
0  | tommy| 23  | [time]| went to shops
****************************************

I am trying to get the data from the last row of task column, which is of type String.

我试图从任务列的最后一行获取数据，该列的类型为String。

But:

但：

I don't know how to read the data from a row into a C# String, and;
我不知道如何将数据从一行读入C＃字符串，并且;
I don't know how to structure the select statement so that it only returns that single value from the very last row.
我不知道如何构造select语句，以便它只从最后一行返回该单个值。

Below is what I have already, which is mostly from a tutorial I found online (which I can't find again yet):

以下是我已经拥有的内容，主要来自我在网上找到的教程（我还没有找到）：

namespace MyApp
{
    public partial class Form1 : Form
    {
        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter DB;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();

        public Form1()
        {
            InitializeComponent();
        }

        private void SetConnection()
        {
            string databasePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\MyApp\database.db";
            sql_con = new SQLiteConnection
                ("Data Source=" + databasePath + ";Version=3;");
        }

        private void ExecuteQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }

        private void LoadData()
        {
            bool gotRow = false;
            try
            {
                SetConnection();
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
                string CommandText = "select task from Mine";
                DB = new SQLiteDataAdapter(CommandText, sql_con);
                DS.Reset();
                DB.Fill(DS);
                DT = DS.Tables[0];

                foreach (DataRow row in DT.Rows)
                {
                    gotRow = true;
                }

                if (gotRow)
                {
                    MessageBox.Show(row.ToString());
                    gotRow = false;
                }

                sql_con.Close();
            }
            catch(Exception exception)
            {
                Debug.WriteLine(exception.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}

What am I doing wrong? How can I get the data from the very last row?

我究竟做错了什么？如何从最后一行获取数据？

1 个解决方案

#1

Change your query to select last task ordered by id descending:

更改您的查询以选择按ID降序排序的上一个任务：

select task
from Mine
order by id desc
limit 1

then in your method should look like this::

然后在你的方法应该看起来像这样::

private void LoadData()
{
    try
    {
        SetConnection();
        sql_con.Open();
        sql_cmd = sql_con.CreateCommand();
        string commandText = "select task from Mine order by id desc limit 1";

        sql_cmd.CommandText = commandText;
        var lsattask = sql_cmd.ExecuteScalar().ToString();

        MessageBox.Show(lsattask);

        sql_con.Close();
    }
    catch(Exception exception)
    {
        Debug.WriteLine(exception.Message);
    }
}

ExecuteScalar returns result from first row in first column and because we ordered the data by id first row is the last one inserted.

ExecuteScalar返回第一列第一行的结果，因为我们按id排序数据第一行是插入的最后一行。

1 个解决方案

#1

更多相关文章

随机推荐