C# – How to get one field value from Sqlite request

cc#-4.0sqlitewinforms

I use this function to make query:

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;
            }
        }

And this is my query:

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

I tried to get value of created_at field from query like as:

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

It returns me error.
Also I have checked this query directly in Sqlite manager. It works and return one row.

Error is:

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)

Best Answer

Just calling ExecuteReader is not enough. Initially any kind of DataReader is positioned before any row retrieved. You need to call Read on the reader to position it on the first row returned

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

or add a loop 

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

However I don't consider your approach to return a SqlLiteDataReader a good practice. The reader depends on the connection being open and the connections are objects that you should really close as fast as possible to avoid dangerous resources leaks.

I suggest to use another approach to read you data

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

Then you call this method passing the delegate to the method that reads your data

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

and then write the method readCreatedData that will be called by the code where you open the connection and the other disposable objects.

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

Finally as Rufo pointed in its comment you are really just reading a single value from a single column and this is better done using a simple 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());
    }
}
Related Topic