c# sqlite return value

Question

c# sqlite return value

mion shion 241

Hello all i have been racking my brain here i not touched sqlite for about a year and my brain seems to have gone to mush

i want to check if a value is the same in the database

and where this db has tables colums and values added at diffent times i cant use id as a certin method for doing this.

i have the following code

        public void insetintosettingsffmpegpath(string SettingsID, string ffmpegPath)  
        {  
  
            if (checkfilepath(SettingsID, "SettingsID"))  
            {  
                //updateDB(songid, artist, songname, fullpath);  
                updatesettingsffmpegpath(SettingsID, ffmpegPath);  
            }  
            else  
            {  
                string query = "INSERT INTO Settings (`SettingsID`, `ffmpegPath`) VALUES (@SettingsID , @ffmpegPath)";  
                SQLiteCommand setupusername = new SQLiteCommand(query, KaoriConnect);  
                OpenConnection();  
                setupusername.Parameters.AddWithValue("@SettingsID", SettingsID);  
                setupusername.Parameters.AddWithValue("@ffmpegPath", ffmpegPath);  
  
  
  
                var result = setupusername.ExecuteNonQuery();  
                CloseConnection();  
                if (KaoriDB.rowsaffected == 0)  
                {  
                    KaoriDB.rowsaffected = result;  
                }  
                else  
                {  
                    KaoriDB.rowsaffected = result++;  
                }  
  
  
            }  
  
        }

the question is i want to check if ffmpegPath is the same value as what is passed in via the function,

so made this,

public bool checkfilepath(string Name, string row)  
        {  
  
            OpenConnection();  
  
  
            //MySqlCommand cmd = new MySqlCommand("SELECT COUNT (*) FORM Appplication_Details WHERE FriendlyNameMS='" + Name + "'", conn);   SELECT EXISTS(SELECT * from ExistsRowDemo WHERE ExistId=105     
            SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) FROM Songs WHERE " + row + " = '" + Name + "'", KaoriConnect);  
            object obj = cmd.ExecuteScalar();  
            if (Convert.ToInt32(obj) > 0)  
            {  
                return true;  
            }  
            else  
            {  
                return false;  
            }  
  
        }

to check if the value exists if not then then add if it does then update existing,

however i cant use id

is there a way of using something like
select * from Settings where ffmpegPath =
at the same time i cant give it a value because unsure what value is there
is there a way to get the value from ffmpegPath then compare it to the path that is sent though the function if they match then do nothing if they dont update the database

thank you for taking your time to read

kind regards,

elfenliedtopfan5

P a u l 10,761 Reputation points

2022-11-29T19:22:14.69+00:00

Just to clarify you have a SettingsID that you're passing into insetintosettingsffmpegpath. You're checking whether there are any Songs rows recorded against SettingID and then inserting into Settings if not.

This makes the assumption that if there is a Settings row with that ID, but there are no Songs rows it'll create a duplicate row in Settings - is that correct?

Also how do you derive the SettingsID that you're passing into your insetintosettingsffmpegpath method?
Jack J Jun 25,296 Reputation points

2022-12-06T07:51:48.89+00:00

@mion shion , is any update? Could you check my answer if works for you?

2 answers

Your answer

P a u l 10,761 Reputation points

2022-11-29T19:22:14.69+00:00

Just to clarify you have a SettingsID that you're passing into insetintosettingsffmpegpath. You're checking whether there are any Songs rows recorded against SettingID and then inserting into Settings if not.

This makes the assumption that if there is a Settings row with that ID, but there are no Songs rows it'll create a duplicate row in Settings - is that correct?

Also how do you derive the SettingsID that you're passing into your insetintosettingsffmpegpath method?
Jack J Jun 25,296 Reputation points

2022-12-06T07:51:48.89+00:00

@mion shion , is any update? Could you check my answer if works for you?

Answer 1

Yeah that will create multiple occurrences however how the settings tables work is SettingsID is a auto increment

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

Answer 2

@mion shion , Welcome to Microsoft Q&A, you could try the following code to check if ffmpegPath is the same as the before value.

  public static void insertintosettingsffmpegpath(string SettingsID, string ffmpegPath)  
        {  
  
            if (checkfilepath(SettingsID, "SettingsID"))  
            {  
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
                m_dbConnection.Open();  
  
                SQLiteCommand cmd = new SQLiteCommand("SELECT ffmpegPath FROM Settings WHERE SettingsID= "+SettingsID, m_dbConnection);  
                SQLiteDataReader reader = cmd.ExecuteReader();  
                string cffmpegPath = "";  
                while (reader.Read())  
                {  
                    cffmpegPath = reader.GetValue(0).ToString();  
                }  
                if(cffmpegPath != ffmpegPath)  
                {  
                    string updatesql = string.Format("Update Settings set ffmpegPath='{0}' where SettingsID='{1}' ", ffmpegPath,SettingsID);  
                    SQLiteCommand updatecmd = new SQLiteCommand(updatesql, m_dbConnection);  
                    updatecmd.ExecuteNonQuery();  
  
                }  
            }  
            
            }  
  
        }

Hope my code could help you.

Best Regards,
Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

c# sqlite return value

2 answers

Your answer