c# - 如何从Sqlite请求中获取一个字段值？

Question

我使用这个函数进行查询:

public SQLiteDataReader returnDataReader(string txtQuery)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                cmd.Connection = Openconn();
                cmd.CommandText = txtQuery;
                SQLiteDataReader rd;
                rd = cmd.ExecuteReader();
                return rd;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                cmd = null;
            }
        }

这是我的查询:

 SQLiteDataReader data = db.returnDataReader("SELECT created_at FROM Transactions ORDER BY created_at DESC LIMIT 1");

我尝试从查询中获取 created_at 字段的值，如下所示:

string res = data["created_at"].ToString();

它返回错误。我还直接在 Sqlite 管理器中检查了此查询。它可以工作并返回一行。

错误是:

System.InvalidOperationException occurred HResult=0x80131509
Message=No current row Source=System.Data.SQLite StackTrace: at System.Data.SQLite.SQLiteDataReader.CheckValidRow() at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i) at System.Data.SQLite.SQLiteDataReader.get_Item(String name) at Ukraine.StatisticService.lastsync() in D:\Projects\c-tests-ukraine\Ukraine\Library\StatisticService.cs:line 25 at Ukraine.Main.Form1_Load(Object sender, EventArgs e) in D:\Projects\c-tests-ukraine\Ukraine\Main.cs:line 81 at System.Windows.Forms.Form.OnLoad(EventArgs e) at System.Windows.Forms.Form.OnCreateControl() at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl() at System.Windows.Forms.Control.WmShowWindow(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.Form.WmShowWindow(Message& m) at System.Windows.Forms.Form.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Answer 1

仅调用 ExecuteReader 是不够的。最初，任何类型的 DataReader 都位于检索的任何行之前。您需要在阅读器上调用 Read 将其定位在返回的第一行

SQLiteDataReader data = db.returnDataReader(.....);
if(data.Read())
{
    string res = data["created_at"].ToString();
    ....
}

或者添加一个循环

SQLiteDataReader data = db.returnDataReader(.....);
while(data.Read())
{
     ..get data from your rows here
}

但是，我不认为您返回 SqlLiteDataReader 的方法是一个好的做法。读者取决于打开的连接，并且连接是您应该尽快关闭的对象，以避免危险的资源泄漏。

我建议使用另一种方法来读取您的数据

public void ReadData(string txtQuery, Action<SQLiteDataReader> loader)
{
    using(SQLiteConnection con = Openconn())
    using(SQLiteCommand cmd = new SQLiteCommand(txtQuery, con))
    using(SQLiteDataReader rd = cmd.ExecuteReader())
    {
        while(rd.Read())
            loader(rd);
    }
}

然后调用此方法，将委托(delegate)传递给读取数据的方法

ReadData(...querytext..., readCreatedData);

然后编写方法readCreatedData，该方法将由打开连接和其他一次性对象的代码调用。

void readCreatedData(SQLiteDataReader data)
{
     string res = data["created_at"].ToString();
     .....
}

最后，正如 Rufo 在其评论中指出的那样，您实际上只是从单个列中读取单个值，最好使用简单的 ExecuteScalar 来完成此操作

public string ReadString(string txtQuery)
{
    using(SQLiteConnection con = Openconn())
    using(SQLiteCommand cmd = new SQLiteCommand(txtQuery, con))
    {
        object result = cmd.ExecuteScalar();
        return (result == null ? "" : result.ToString());
    }
}