List of public pages created with Protopage

Wythenshawe Community Housing Group WCHG-XRA2652

Bookmarks

Bookmarks

Proto Search

Rich sticky notes

Gemini Request

Write me some code that compares a search request with a seo field stored in a sql server database to report business urls do the compare as a jagged array compare.

ProtoSearch Code

@page "/"
@using System.Data.SqlClient
@using System.Text.RegularExpressions
@using Microsoft.Extensions.Configuration
@inject IConfiguration Configuration

<h1>Search Request to SEO Field Comparison</h1>

<p>Enter a search request to find matching business URLs:</p>

<div class="input-group mb-3">
<input type="text" class="form-control" @bind="SearchRequest" placeholder="Enter search terms..." />
<button class="btn btn-primary" @onclick="SearchAndCompare">Search</button>
</div>

@if (SearchResults != null)
{
@if (SearchResults.Any())
{
<h2>Search Results:</h2>
<ul>
@foreach (var result in SearchResults)
{
<li>
<strong>URL:</strong> @result.BusinessUrl, <strong>Matched SEO:</strong> @result.SeoField, <strong>Matching Word:</strong> @result.MatchingWord
</li>
}
</ul>
}
else
{
<p>No matching URLs found.</p>
}
}
else if (SearchError != null)
{
<div class="alert alert-danger">
<strong>Error:</strong> @SearchError
</div>
}

@code {
private string SearchRequest { get; set; } = "";
private List<SearchResult> SearchResults { get; set; } = null;
private string SearchError { get; set; } = null;

public class SearchResult
{
public string BusinessUrl { get; set; }
public string SeoField { get; set; }
public string MatchingWord { get; set; }
}

private async Task SearchAndCompare()
{
SearchError = null;
SearchResults = new List<SearchResult>();

if (string.IsNullOrWhiteSpace(SearchRequest))
{
SearchError = "Please enter a search request.";
return;
}

// 1. Split the search request into individual words. Use a regular expression
// to handle multiple spaces and punctuation.
string[] searchWords = Regex.Split(SearchRequest.Trim(), @"\s+");

// 2. Fetch data from the SQL Server database.
string connectionString = Configuration.GetConnectionString("YourConnectionString"); // "YourConnectionString"
if (string.IsNullOrEmpty(connectionString))
{
SearchError = "Connection string is missing or invalid. Ensure 'YourConnectionString' is defined in configuration.";
return;
}

try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
// 3. Important: Use parameters to prevent SQL injection.
string sqlQuery = "SELECT BusinessURL, SEO_Field FROM YourTableName"; // "YourTableName"
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string businessUrl = reader["BusinessURL"].ToString();
string seoField = reader["SEO_Field"].ToString();

// 4. Split the SEO field into words, using the same regex.
string[] seoWords = Regex.Split(seoField.Trim(), @"\s+");

// 5. Perform the jagged array comparison.
string matchingWord = FindMatchingWord(searchWords, seoWords);

// 6. If a match is found, add the result to the list.
if (matchingWord != null)
{
SearchResults.Add(new SearchResult
{
BusinessUrl = businessUrl,
SeoField = seoField,
MatchingWord = matchingWord
});
}
}
}
}
}
}
catch (Exception ex)
{
SearchError = $"An error occurred: {ex.Message}";
return; // Exit the method on error.
}
}

private string FindMatchingWord(string[] searchWords, string[] seoWords)
{
// 7. Iterate through each word in the search request.
foreach (string searchWord in searchWords)
{
// 8. For each search word, iterate through the words in the SEO field.
foreach (string seoWord in seoWords)
{
// 9. Perform a case-insensitive comparison.
if (searchWord.Equals(seoWord, StringComparison.OrdinalIgnoreCase))
{
// 10. If a match is found, return the matching word.
return searchWord;
}
}
}
// 11. If no match is found after checking all words, return null.
return null;
}
}

Narrative

Narrative Pseudo-Code with C# and Blazor/Azure Context

Here's a breakdown of what the C# / Blazor Azure code does, combining narrative and pseudo-code:

  1. User Interface (Blazor Component):

    • A Blazor component presents a text input field where the user enters their SearchRequest.
    • A "Search" button triggers the SearchAndCompare method.
    • The component displays the SearchResults (business URLs, matched SEO fields, and the matching word) or an error message.
  2. Search Request Processing (SearchAndCompare method):

    • The SearchAndCompare method is called when the user clicks the "Search" button.
    • It initializes an empty list called SearchResults to store the results.
    • It retrieves the database connection string from the application's configuration (Azure App Configuration or appsettings.json in a local Blazor app). Important: This keeps the connection string secure and configurable.
    • It handles potential errors, such as an empty search request or a database connection problem, and displays user-friendly error messages in the Blazor UI.
  3. Database Interaction (Inside SearchAndCompare):

    • The code connects to the SQL Server database using the provided connection string.
    • It executes a SQL query to retrieve BusinessURL and SEO_Field from a table (e.g., "YourTableName"). Important: The query uses a parameterized query.
    • It reads the data from the database.
  4. Data Processing and Comparison (Inside the while loop in SearchAndCompare):

    • For each row retrieved from the database:
      • The SearchRequest is split into individual words using a regular expression to handle multiple spaces or punctuation.
      • The SEO_Field from the database is also split into individual words using the same regular expression.
      • The FindMatchingWord method is called to compare the search words with the SEO field words.
      • If the FindMatchingWord method returns a matching word (meaning at least one word in the search request is found in the SEO field):
        • A SearchResult object is created, containing the BusinessURL, the SEO_Field, and the MatchingWord.
        • This SearchResult object is added to the SearchResults list.
  5. Jagged Array Comparison (FindMatchingWord method):

    • The FindMatchingWord method takes the arrays of search words and SEO field words as input.
    • It iterates through each word in the searchWords array (outer loop).
    • For each search word, it iterates through each word in the seoWords array (inner loop).
    • Inside the inner loop, it performs a case-insensitive comparison of the current search word and the current SEO word.
    • If the words match, the method immediately returns the matching searchWord.
    • If the loops complete without finding any matching words, the method returns null.
  6. Displaying Results (Blazor Component):

    • After the database query and comparison are complete, the SearchAndCompare method updates the SearchResults property.
    • The Blazor component then displays the results:
      • If SearchResults contains any items, it displays them in a list, showing the BusinessURL, SEO_Field, and the MatchingWord.
      • If SearchResults is empty, it displays a "No matching URLs found" message.
    • If any errors occurred during the process, the Blazor component displays the error message stored in the SearchError property.

Manchester Post Supplement

Bookmarks

Bookmarks

Andy Burnham

Bookmarks

Bookmarks