Indicate how many times are matched from keywords for each matched record

How to get many times are matched from keywords for each matched record from csv files.

That I need to search all the characters in the [StringData] in the csv file to find out the records which contain the words and many times that are matched in the StringData on the Text box Search.

Code

        string search = txtBoxSearch.Text;
        string pathOnly = Path.GetDirectoryName(csvPath);
        string fileName = Path.GetFileName(csvPath);

        string sql = @"SELECT F1 AS StringID, F2 AS StringContent FROM [" + fileName + "] WHERE F2 LIKE '%" + search + "%'";

        using (OleDbConnection connection = new OleDbConnection(
                  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                  ";Extended Properties="Text;HDR=No""))
        using (OleDbCommand command = new OleDbCommand(sql, connection))
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            GridViewResult.DataSource = dataTable;
            GridViewResult.DataBind();
        }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can add a new column to DataTable and calculate it’s value on memory foreach rows by using Regex.Matches:

string search = txtBoxSearch.Text;
string pathOnly = Path.GetDirectoryName(csvPath);
string fileName = Path.GetFileName(csvPath);

string sql = @"SELECT F1 AS StringID, F2 AS StringContent FROM [" + fileName + "] WHERE F2 LIKE '%" + search + "%'";

using (OleDbConnection connection = new OleDbConnection(
          @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
          ";Extended Properties="Text;HDR=No""))
using (OleDbCommand command = new OleDbCommand(sql, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    dataTable.Columns.Add("MatchTimes", typeof(System.Int32));
    
    foreach(DataRow row in dataTable.Rows)
    {
        row["MatchTimes"] = Regex.Matches(row["StringContent"].ToString(), row["StringID"].ToString()).Count
    }

    GridViewResult.DataSource = dataTable;
    GridViewResult.DataBind();
}

See also: Count the number of times a string appears within a string

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0