I want to replace if (Convert.ToString(rdr["Data"]) != bItems) with something that would check if data already exist in my database or not to make process faster as going in that loop taking too much time for bigger database. Plz HELP!

for (int p = 0; p < 256; p  ) {
    bItems  = "P"   buffer[p];                                   
}
        
using (SQLiteConnection con = new SQLiteConnection(databaseObject.myConnection)) {
    con.Open();
    SQLiteCommand cmd = new SQLiteCommand("select ID, Data from B where Data like 'P%'", con);
    var rdr = cmd.ExecuteReader();
    while (rdr.Read()) {
        if (Convert.ToString(rdr["Data"]) != bItems) {
            SQLiteCommand cmd1 = new SQLiteCommand("INSERT INTO B ('Data') SELECT @Data  WHERE NOT EXISTS (SELECT ID, Data FROM B WHERE  Data = @Data)", con);                                                
            cmd1.Parameters.AddWithValue("@Data", bItems);
            cmd1.ExecuteNonQuery(); 
        }
        else (Convert.ToString(rdr["Data"]) == bItems) {
            sItems = "B"   Convert.ToString(rdr["ID"]);
            rdr.Close();
            break;
        }
    }
}
bItems = "";
Console.WriteLine(sItems);
}

CodePudding user response：

instead of reading each row and check the data against bItems. You may need to either query the table to see if there is any record matches bItems, if not then insert it. Or, you can simply insert the data if not exists (which what you did in the first condition.

To simplify your work, you can do this :

// insert the new item if not exists in the table 
// returns the item Id
private int InsertDataIfNotExists(string bItems)
{
    using (SQLiteConnection connection = new SQLiteConnection(databaseObject.myConnection)) 
    using(SQLiteCommand command = new SQLiteCommand("INSERT INTO B ('Data') SELECT @Data WHERE NOT EXISTS (SELECT 1 FROM B WHERE  Data = @Data);", con))
    {
        connection.Open();
        command.Parameters.AddWithValue("@Data", bItems);

        // insert data if not exists
        command.ExecuteNonQuery(); 

        // get the data's Id
        cmd.CommandText = "SELECT ID FROM B WHERE Data = @Data LIMIT 1;";
        cmd.Parameters.AddWithValue("@Data", bItems);
        var result = cmd.ExecuteScalar()?.ToString();
        
        return int.TryParse(result, out int id) && id > 0 ? id : -1;
    }
}

with the above, you only insert the data if not exists, and then return the id.

usage :

var insertResultId = InsertDataIfNotExists(bItems);

if(insertResultId == -1)
{
    // handle exceptions 
}
else
{
    Console.WriteLine(insertResultId);      
}