Blogs

SQL Server side CRUD with Blazor Smart.Grid

Setup The Blazor Application

Follow the Getting Started guide to set up your Blazor Application with Smart UI.

Bind to SQL

Follow our Binding to SQL guide to set up the connection between your database and Smart UI.
At the end of the tutorial, the Smart.Grid will be bounded to a SQL DataBase:

Create Methods

To enable adding new rows to the Grid, we must first create the functions for the CRUD operations in the PersonData class.
Navigate to PersonData.cs and implement the additional methods:

.....
public Task<List<PersonModel>> GetPeople()
  {
      string sql = "select * from dbo.peopleTable";

      return _db.LoadData<PersonModel, dynamic>(sql, new { });
  }
  public Task<List<PersonModel>> InsertPerson(string Name, double Balance, string City, string Country)
  {
      string sql =
          "INSERT INTO [dbo].[peopleTable] ([Name], [Balance], [City], [Country]) OUTPUT INSERTED.Id, INSERTED.name, INSERTED.Balance, INSERTED.City, INSERTED.Country VALUES (@Name, @Balance, @City, @Country)";

      return _db.LoadData<PersonModel, dynamic>(sql, new { Name, Balance, City, Country });
  }
  public Task<List<PersonModel>> DeletePerson(int Id)
  {
      string sql =
          "DELETE FROM [dbo].[peopleTable] WHERE [Id]=@Id";

      return _db.LoadData<PersonModel, dynamic>(sql, new { Id });
  }
  public Task<List<PersonModel>> UpdatePerson(int Id, string Name, double Balance, string City, string Country)
  {
      string sql =
          "UPDATE [dbo].[peopleTable] SET [Name] = @Name, [Balance] = @Balance, [City] = @City, [Country] = @Country WHERE [Id] = @Id";

      return _db.LoadData<PersonModel, dynamic>(sql, new { Name, Balance, City, Country, Id });
  }
.....

Then, add the new methods to the IPeopleData interface:

.....
Task<List<PersonModel>> DeletePerson(int Id);
Task<List<PersonModel>> GetPeople();
Task<List<PersonModel>> InsertPerson(string Name, double Balance, string City, string Country);
Task<List<PersonModel>> UpdatePerson(int Id, string Name, double Balance, string City, string Country);
.....

Add Create functionality

Navigate to the Index.razor page and create a “Add new row” Button. Then create an AddRow function that creates a new person and then fetches the updated SQL Table:

<Button OnClick="AddRow">Add new row</Button>
.....
@code{
  .....
  private async Task AddRow()
  {
      PersonModel newPerson = (await _db.InsertPerson("John", 1000, "Paris", "France"))[0];
      people = await _db.GetPeople();
  }
}

The new Person is created in the SQL Table:

Add Delete functionality

Add a “Delete last row” Button. Then create a DeleteLastRow function that removes the last SQL Record and then fetches the updated SQL Table:

<Button OnClick="DeleteLastRow">Delete row</Button>
.....
@code{
  .....
  private async Task DeleteLastRow()
  {
    int lastId = people[people.Count - 1].Id;
    await _db.DeletePerson(lastId);
    people = await _db.GetPeople();
  }
}

The last Person is removed from the SQL Table:

Add Update functionality

To add Update functionality, first enable Grid Editing using the Editing property.
We will use the OnEndEdit Event to update the SQL Table after every change:

<Grid @ref="@grid" DataSource="@people" DataSourceSettings="@dataSourceSettings" OnEndEdit="OnEndEdit" Editing="@editing">
  <Columns>
    <Column DataField="Name" Label="Client Name"></Column>
    <Column DataField="Balance" Label="Acccount Balance"></Column>
    <Column DataField="City" Label="City"></Column>
    <Column DataField="Country" Label="Country"></Column>
  </Columns>
</Grid>
@code{
  GridEditing editing = new GridEditing()
  {
      Enabled = true,
      Mode = GridEditingMode.Cell
  };
}

Create a new OnEndEdit function. Using the Event.detail, get the values of the edited row and use the UpdatePerson Method to make changes in the SQL Table:

        private async Task OnEndEdit(Event ev)
        {
            GridEndEditEventDetail EventDetail = ev["Detail"];
            dynamic Editedrow = JObject.Parse((await grid.GetRowData(EventDetail.Row)).ToString());
    
            int EditedId = (Editedrow.Id).ToObject<int>();
            string EditedName = (Editedrow.Name).ToObject<string>();
            double EditedBalance = (Editedrow.Balance).ToObject<double>();
            string EditedCity = (Editedrow.City).ToObject<string>();
            string EditedCountry = (Editedrow.Country).ToObject<string>();
            await _db.UpdatePerson( EditedId, EditedName, EditedBalance,  EditedCity, EditedCountry);
        }
      

After editing, the changes are applied to the SQL Table: