c# Dapper insert and update issue

Question

Good morning all,

so i been racking my brain all day checked youtube and managed to put together working code however it does not work as i would expect it too

so i have a sqlite database that is called settings and it holds filepaths

and managed to get them working,

however when i add a path in it adds fine but say i in the same session or if i re open and change the path it will create a new entry when i want to update the existing entry

i have thought of pre making the database and having the table setup but would rather make it with the program and just update the paths,

so i have the following to create the tables,

        public void Settings()  
        {  
            {  
                using (SQLiteCommand command = KaoriConnect.CreateCommand())  
                {  
                    OpenConnection();  
                    //command.CommandText = "SELECT name FROM sqlite_master WHERE name='Programs'";  
                    //var name = command.ExecuteScalar();  
  
                    //if (name != null && name.ToString() == "Programs")  
                    //    return;  
                    //    command.CommandText = "CREATE TABLE Programs (ProgID INT PRIMARY KEY , UserID TEXT , UserName TEXT , MachineID TEXT , Program TEXT , FilePath TEXT , Installed BOOLEAN ";  
                    //    command.ExecuteNonQuery();  
                    //    command.CommandText = "FOREIGN KEY(`UserID`) REFERENCES `User`(`ID`)";  
                    //    command.ExecuteNonQuery();  
  
  
                    string createfulltableprograms = @"CREATE TABLE IF NOT EXISTS  
                                               [Settings] (  
                                               [SettingsID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,  
                                               [ffmpegPath] TEXT  NULL,  
                                               [SongPath] TEXT NULL,  
                                               [DefaultPath] TEXT NULL)";  
  
                    command.CommandText = createfulltableprograms;  
                    command.ExecuteNonQuery();  
  
  
  
                }  
                CloseConnection();  
            }  
        }  

then i use dapper and created a few functions to handle the data going though

        public static void SaveffmpegPath(SettingsModel.SettingsModelffmpegPath ffmpeg)  
        {  
            using(IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))  
            {  
                string elfentest = ffmpeg.returnffmpegPath;  
                if (checkfilepathsettings(elfentest, "ffmpegPath"))  
                {  
                    XtraMessageBox.Show("Already Added Path To DataBase: " + elfentest, "ffmpeg Path Add");  
                }  
                else  
                {  
  
                        cnn.Execute("insert into Settings (ffmpegPath) values(@ffmpegPath) where SettingsID = '1'", ffmpeg);  
                      
                    
                }  
            }  
  
  
  
        }  

this adds fine however if i change the path it will create a new line and i trying to think of logic to update the path that already exists in there

the database structure is here

so when its first made it will create this blank db

then when i populate with folder dialog box it populates as seen here

the issue i have is see the second one i want it to replace the first occorance insent of creating it,

i have tryied if statments getting the current value pulling the one in the db and see if the match if not do update statement but the first time it wont work because there no id number or ref its a blank db,

what i have for the read database
and for the checkingpath

        public static bool checkfilepathsettings(string Name, string row)  
        {  
  
  
            //MySqlCommand cmd = new MySqlCommand("SELECT COUNT (*) FORM Appplication_Details WHERE FriendlyNameMS='" + Name + "'", conn);   SELECT EXISTS(SELECT * from ExistsRowDemo WHERE ExistId=105     
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))  
            {  
  
                //SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) FROM Settings WHERE " + row + " = '" + Name + "'", KaoriConnect);  
                object obj = cnn.ExecuteScalar("SELECT COUNT(*) FROM Settings WHERE " + row + " = '" + Name + "'");  
                if (Convert.ToInt32(obj) > 0)  
                {  
                    return true;  
                }  
                else  
                {  
                      
                    return false;  
                }  
            }  
  
  
        }  
  
        public static List<SettingsModel.SettingsModelffmpegPath> GetffmpegPath()  
        {  
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))  
            {  
                var output = cnn.Query<SettingsModel.SettingsModelffmpegPath>("select ffmpegPath from Settings where SettingsID = '1'", new DynamicParameters());  
                return output.ToList();  
            }  
        }  

any help would be much appreciated its driving me crazy
thank you for your time reading
kind regards
elfenliedtopfan5

Answer 1

It appears you are trying different data providers so why not EF Core?

I would recommend experimenting outside the current project using a console project then once satisfied incorporate code into your project.

Full source

In the following code sample we start with a model

public class FileContainer  
{  
    public int Id { get; set; }  
    public string Path1 { get; set; }  
    public string Path2 { get; set; }  
    public string Path3 { get; set; }  
}  

Then a class inheriting DbContext which in this case will point to a database in the app folder.

public class Context : DbContext  
{  
    public DbSet<FileContainer> FileContainers { get; set; }  
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
        => optionsBuilder.UseSqlite("Data Source=files.db");  
}  

Then code in Program.cs

internal partial class Program  
{  
    static void Main(string[] args)  
    {  
        using (var context = new Context())  
        {  
  
            BuildData(context);  
            ShowData(context);  
            UpdateOneRecord(context);  
            ShowData(context);  
        }  
  
        Console.ReadLine();  
    }  
  
    /// <summary>  
    /// First two lines create the db each time the app runs  
    /// Once created feel free to remove the first line to keep existing data  
    /// </summary>  
    private static void BuildData(Context context)  
    {  
        context.Database.EnsureDeleted();  
        context.Database.EnsureCreated();  
  
        context.FileContainers.Add(new FileContainer() { Path1 = "A1", Path2 = "A2", Path3 = "A3" });  
        context.FileContainers.Add(new FileContainer() { Path1 = "B1", Path2 = "B2", Path3 = "B3" });  
        context.FileContainers.Add(new FileContainer() { Path1 = "C1", Path2 = "C2", Path3 = "C3" });  
  
        context.SaveChanges();  
    }  
  
    /// <summary>  
    /// Update a record  
    /// </summary>  
    private static void UpdateOneRecord(Context context)  
    {  
        var item = context.FileContainers.FirstOrDefault(x => x.Id == 2);  
        Console.WriteLine();  
        item!.Path1 = Prompts.GetPath();  
        context.SaveChanges();  
    }  
  
    private static void ShowData(Context context)  
    {  
        Console.WriteLine();  
        var items = context.FileContainers.ToList();  
        foreach (var container in items)  
        {  
            Console.WriteLine($"{container.Id,-3}{container.Path1,-20}{container.Path2,-13}{container.Path3,-13}");  
        }  
    }  
}  

Note I use a third party NuGet package to prompt for a string

internal class Prompts  
{  
    public static string GetPath() =>  
        AnsiConsole.Prompt(  
            new TextPrompt<string>("[white]Enter new path[/]?")  
                .PromptStyle("yellow"));  
}