SQLite problems/C#/Visual Studio

Question

Updated by other case:

I am processing and sending data base files (db3). I attach two methods which do not function the way I expect them to do as well as the file I am using to test these function. The db3 file is sent as a csv file. Am running Win 11, Visual Studio 2022, using c# and SQLite.

If I run the two methods sequentially the SortColAscending first, the sort does not work (nothing in the file is changed) and, when the DoFileRowID is run, it fails on the execution of the set rowID to 0 saying the database is locked.

If the DoFileRowID is run by itself, the set row ID to 0 works but the Set row ID = ROWID fails like the sort in that nothing in the file is changed.

I have tried changing everything to use "using" hoping it might at least solve the lock problem, but there is no change.

I do have other SQLite methods that are working fine.

I really need and will appreciate some help!!

---

Below are the files:

Methods:

        /// <summary>
        /// Method to add or correct row ids in a fiole already prepared for tranmission to terminal
        /// </summary>
        /// <param name="path"></param>
        /// <param name="table"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public static bool DoFileRowID (string path, string table, int rows)
        {
            Processing proc = new Processing();

            int RowToDo = 1;
            
            string sc = "Data Source = " + path;
            SQLiteConnection db = new SQLiteConnection(sc);
            db.Open();
            //use parameterized query to prevent SQL injection attacks
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = db;
            cmd.CommandText = "UPDATE " + table + " SET RowID = 0 ";

            try 
            {
                cmd.ExecuteReader();
            }
            catch(SQLiteException e)
            {
                proc.WriteLog("DoFileRowID zero error " + e);
                MessageBox.Show("DoFileRowID zero error");
                return true;
            }
            cmd.Dispose();
            db.Close();
            db.Open();
            //this should have change all to 0
            string thisRow = Convert.ToString(RowToDo);
            cmd.CommandText = "UPDATE " + table + " SET RowID = ROWID ";  // WHERE RowID = 0 ";
            try 
            {
                cmd.ExecuteReader();
            }
            catch (SQLiteException e)
            {
                proc.WriteLog("DoFileRowID num error " + e);
                MessageBox.Show("DoFileRowID zero error ");
                return true;
            }
            cmd.Dispose();
            db.Close();
            return false;
        }

        /// <summary>
        /// Method to sort a file in ascending order ot designated coloumn
        /// </summary>
        /// <param name="path"></param>
        /// <param name="table"></param>
        /// <param name="col"></param>
        /// <returns></returns>

        public static bool SortColAscending(string path, string table, string col)
        {
            Processing proc = new Processing();    

            string sc = "Data Source = " + path;
            SQLiteConnection db = new SQLiteConnection(sc);
            db.Open();
            //use parameterized query to prevent SQL injection attacks
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = db;
            cmd.CommandText = "SELECT * FROM " + table + " ORDER BY " + col + " ASC ";

            try
            {
                cmd.ExecuteReader();
            }
            catch (SQLiteException e)
            {
                proc.WriteLog(" Column Sort Error " + e);
                MessageBox.Show("Column Sort Error");
                return true;
            }
            cmd.Dispose();
            db.Close();            
            //this should have sorted all rows in ascending order            
            return false;
        }

RowID,PIN,FirstName,LastName
0,6842,Tom,Cat
0,5491,Sue,Sharp
0,2673,Billy,Cutit
0,1004,first,last
0,9999,first,last
0,8888,first,last
0,9462,Sharon,Best
0,7651,Linda,Best
0,1041,Sandy,Best
0,1051,Randy,Really
0,1061,Bob,Really
0,1071,Scott,Really
0,1032,Chuck,West
0,1042,Hal,West
0,1052,Gary,West
0,1062,Sam,West
0,1072,Colleen,Nortn
0,1043,Janie,North
0,1053,Christina,North
0,1063,Chris,North
0,1073,Gabe,Doe
0,1054,Mike,Doe
0,1064,Tom,Doe
0,1074,Robert,Doe
0,1084,Leslie,Scott

Answer 1

DoFileRowID and SortColAscending returns seem to be backwards.

Also you are invoking ExecuteReader but using it.

Example what it might be targeting one table rather than many. Note that in this example I'm using IConfiguration to read the connection string from appsettings.json which you can do or not and logging is done with SeriLog. Done in C#, .NET Core 7 class project.

using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Configuration;
using Serilog;

namespace SqlLibrary.Models;
public class SomeTableOperations
{
    private static IConfiguration? _configuration;
    public static (bool, List<SomeTable> list) ReadSomeTable(SortOrder order)
    {
        List<SomeTable> list = new();
        using SqliteConnection cn = new();
        cn.ConnectionString = _configuration.GetValue<string>("ConnectionStrings:ApplicationConnection");
        using SqliteCommand cmd = new();
        cmd.Connection = cn;
        var sort = order == SortOrder.Ascending ? "asc" : "desc";
        cmd.CommandText = $"SELECT RowID,PIN,FirstName,LastName FROM SomeTable ORDER BY LastName {sort}";
        try
        {
            cn.Open();
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new SomeTable()
                {
                    RowID = reader.GetInt32(0),
                    PIN = reader.GetString(1),
                    FirstName = reader.GetString(2),
                    LastName = reader.GetString(3)
                });
            }
            return (true, list);
        }
        catch (Exception localException)
        {
            Log.Error(localException, $"Failed to read data in {nameof(ReadSomeTable)}");
            return (false, null);
        }
    }
}