Razor
DbNetEditCore ProductsEdit = new DbNetEditCore(DataSourceType.List);
ProductsEdit.AddList(Model.Products);
ProductsEdit.Column(nameof(Product.ProductID)).PrimaryKey();
ProductsEdit.Column(nameof(Product.UnitPrice)).Format("c");
ProductsEdit.Column(nameof(Product.Discontinued)).DataType(typeof(bool));
ProductsEdit.Column(nameof(Product.SupplierID)).Lookup(Model.SupplierLookup);
ProductsEdit.Column(nameof(Product.CategoryID)).Lookup(Model.CategoryLookup);
ProductsEdit.Column(new string[] { nameof(Product.UnitPrice), nameof(Product.ProductName), nameof(Product.CategoryID), nameof(Product.SupplierID), nameof(Product.QuantityPerUnit), nameof(Product.ReorderLevel), nameof(Product.UnitsInStock), nameof(Product.UnitsOnOrder) }).Required();
ProductsEdit.Column(nameof(Product.ProductName)).Browse();
ProductsEdit.Column(nameof(Product.ReorderLevel)).DefaultValue("0");
ProductsEdit.Column(nameof(Product.UnitsInStock)).DefaultValue("0");
ProductsEdit.LayoutColumns = 2;
ProductsEdit.Insert = true;
ProductsEdit.Delete = true;
ProductsEdit.Bind(EventType.onJsonUpdated, "applyJsonChanges");
@ProductsEdit.Render()
Controller
...
public async Task OnGet()
{
using (var conn = new SqliteConnection(_connectionString))
{
SqlMapper.AddTypeHandler(new BooleanTypeHandler());
SqlMapper.AddTypeHandler(new DecimalTypeHandler());
Products = await conn.QueryAsync("select * from products;");
SupplierLookup = await GetLookup(conn, "select SupplierID as Key, CompanyName as Value from Suppliers order by 2");
CategoryLookup = await GetLookup(conn, "select CategoryID as Key, CategoryName as Value from Categories order by 2");
}
}
public async Task<IActionResult> OnPost([FromBody] JsonUpdateRequest jsonUpdateRequest)
{
string message = "";
bool success = true;
using (var conn = new SqliteConnection(_connectionString))
{
string sql = string.Empty;
var paramValues = new DynamicParameters();
switch (jsonUpdateRequest.EditMode)
{
case EditMode.Update:
string set = string.Join(",", jsonUpdateRequest.Changes.Keys.Select(k => $"{k} = @{k}"));
paramValues.Add($"@{jsonUpdateRequest.PrimaryKeyName}", jsonUpdateRequest.PrimaryKeyValue);
foreach (string key in jsonUpdateRequest.Changes.Keys)
{
paramValues.Add($"@{key}", jsonUpdateRequest.Changes[key]);
}
sql = $"update products set {set} where {jsonUpdateRequest.PrimaryKeyName} = @{jsonUpdateRequest.PrimaryKeyName}";
message = "Product updated";
break;
case EditMode.Insert:
string columns = string.Join(",", jsonUpdateRequest.Changes.Keys.Select(k => $"{k}"));
string values = string.Join(",", jsonUpdateRequest.Changes.Keys.Select(k => $"@{k}"));
foreach (string key in jsonUpdateRequest.Changes.Keys)
{
paramValues.Add($"@{key}", jsonUpdateRequest.Changes[key]);
}
sql = $"insert into products({columns}) values({values})";
message = "Product added";
break;
case EditMode.Delete:
paramValues.Add($"@{jsonUpdateRequest.PrimaryKeyName}", jsonUpdateRequest.PrimaryKeyValue);
sql = $"delete from products where {jsonUpdateRequest.PrimaryKeyName} = @{jsonUpdateRequest.PrimaryKeyName}";
message = "Product deleted";
break;
}
try
{
conn.Execute(sql, paramValues);
}
catch (Exception ex)
{
message = ex.Message;
success = false;
}
var products = await conn.QueryAsync("select * from products;");
return new JsonResult(new JsonUpdateResponse { Success = success, Message = message, DataSet = products });
}
}
...