How to Connect a .NET Blazor App to Salesforce via ADO.NET

public async Task<List<LeadModel>> GetLeadDataAsync()
    {
        var leadData = new List<LeadModel>();

        try
        {
            using (var connection = new SalesforceConnection(_connectionString))
            {
                await connection.OpenAsync();

                // Query to fetch the last 15 rows sorted by CreatedDate
                string query = "SELECT Id, FirstName, LastName, Company, Title, Phone, Email, Status " +
                               "FROM Lead " +
                               "ORDER BY CreatedDate DESC " +
                               "LIMIT 15";

                using (var command = new SalesforceCommand(query, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            leadData.Add(new LeadModel
                            {
                                Id = reader["Id"].ToString(),
                                FullName = $"{reader["FirstName"]} {reader["LastName"]}",
                                Company = reader["Company"].ToString(),
                                Title = reader["Title"].ToString(),
                                Phone = reader["Phone"].ToString(),
                                Email = reader["Email"].ToString(),
                                Status = reader["Status"].ToString()
                            });
                        }
                    }
                }
            }
        }
        catch
        {
            // Suppressed errors for cleaner output
        }

        return leadData;
    }

<h4>Leads</h4>
<table>
    <thead>
        <tr>
            <th>Full Name</th>
            <th>Company</th>
            <th>Title</th>
            <th>Phone</th>
            <th>Email</th>
            <th>Status</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @@foreach (var lead in Leads)
        {
            <tr>
                <td>@@lead.FullName</td>
                <td>@@lead.Company</td>
                <td>@@lead.Title</td>
                <td>@@lead.Phone</td>
                <td>@@lead.Email</td>
                <td>@@lead.Status</td>
                <td>
                    <a href="/salesforceleads?id=@@lead.Id">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

public async Task AddLeadAsync(string firstName, string lastName, string company, string title, string phone, string email)
    {
        using (var connection = new SalesforceConnection(_connectionString))
        {
            await connection.OpenAsync();

            var loader = new SalesforceLoader
            {
                Connection = connection,
                TableName = "Lead"
            };

            // Define the columns and open the loader
            loader.CreateColumns();
            loader.Open();

            // Set values for the new lead
            loader.SetValue("FirstName", firstName ?? string.Empty);
            loader.SetValue("LastName", lastName ?? string.Empty);
            loader.SetValue("Company", company ?? string.Empty);
            loader.SetValue("Title", title ?? string.Empty);
            loader.SetValue("Phone", phone ?? string.Empty);
            loader.SetValue("Email", email ?? string.Empty);
            loader.SetValue("Status", "Open - Not Contacted"); // Default status

            // Commit the row
            loader.NextRow();

            // Close the loader
            loader.Close();
        }
    }

<h3>Add New Lead</h3>

<form>
            <div>
            <label>First Name:</label>
            <input @@bind="FirstName" placeholder="First Name" required="required" />
    </div>
            <div>
            <label>Last Name:</label>
            <input @@bind="LastName" placeholder="Last Name" required="required" />
    </div>
            <div>
            <label>Company:</label>
            <input @@bind="Company" placeholder="Company" required="required" />
    </div>
            <div>
            <label>Title:</label>
            <input @@bind="Title" placeholder="Title" />
    </div>
            <div>
            <label>Phone:</label>
            <input @@bind="Phone" placeholder="Phone" />
    </div>
            <div>
            <label>Email:</label>
            <input @@bind="Email" placeholder="Email" type="email" />
    </div>
            <button type="button" @@onclick="SubmitLead">Add Lead</button>
</form>

@@code {
    private string FirstName, LastName, Company, Title, Phone, Email;

    private async Task SubmitLead()
    {
        await SalesforceService.AddLeadAsync(FirstName, LastName, Company, Title, Phone, Email);
        NavigationManager.NavigateTo("/salesforceleads", true);
    }
}

public async Task<bool> DeleteLeadAsync(string id)
    {
        try
        {
            using (var connection = new SalesforceConnection(_connectionString))
            {
                await connection.OpenAsync();

                string query = "DELETE FROM Lead WHERE Id = @Id";
                using (var command = new SalesforceCommand(query, connection))
                {
                    command.Parameters.AddWithValue("Id", id);
                    await command.ExecuteNonQueryAsync();
                }
            }
            return true;
        }
        catch
        {
            return false;
        }
    }
}

<a href="/[email protected]">Delete</a>

@code {
    protected override async Task OnInitializedAsync()
    {
        // Extract query parameter
        var uri = new Uri(NavigationManager.Uri);
        var query = System.Web.HttpUtility.ParseQueryString(uri.Query);
        var id = query.Get("id");

        if (!string.IsNullOrEmpty(id))
        {
            await DeleteLead(id);
        }
    }

    private async Task DeleteLead(string id)
    {
        var success = await SalesforceService.DeleteLeadAsync(id);
        if (success)
        {
            connectionStatus = $"Lead with ID {id} deleted successfully.";
            Leads = await SalesforceService.GetLeadDataAsync();
        }
        else
        {
            connectionStatus = $"Failed to delete lead with ID {id}.";
        }

        // Remove query parameters after deletion
        NavigationManager.NavigateTo("/salesforceleads", true);
    }
}