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: