Select Page

SQL Server-side CRUD with Smart.Grid for Blazor

admin
Published: January 12, 2022

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:

Basic Grid

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();
  }
}

Grid with new row

The new Person is created in the SQL Table:

SQL with new row

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();
  }
}

Grid with removed row

The last Person is removed from the SQL Table:

SQL with removed row

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);
        }
      

Grid editing

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

SQL with removed row

Source: www.jqwidgets.com