commit c791569ff962580947cae1c1a36b91cf77521348
parent 1dc141b3bbbd07dc4e80ec6d739b3a5d21c02f87
Author: William Lindholm <a22willi@student.his.se>
Date: Wed, 11 Oct 2023 11:02:25 +0200
Added ability to delete rows.
Diffstat:
3 files changed, 67 insertions(+), 11 deletions(-)
diff --git a/MVC-databaskonstruktion/Controllers/AgentsController.cs b/MVC-databaskonstruktion/Controllers/AgentsController.cs
@@ -1,18 +1,19 @@
using Microsoft.AspNetCore.Mvc;
using MVC_databaskonstruktion.Models;
-using MVC_databaskonstruktion.Utils;
-using System.Data;
+using MySql.Data.MySqlClient;
+using System.Linq.Expressions;
namespace MVC_databaskonstruktion.Controllers
{
public class AgentsController : Controller
{
private readonly IConfiguration _configuration;
- private AgentsModel _agentsModel;
+ private AgentsModel _agentsModel { get; set; }
public AgentsController(IConfiguration configuration)
{
this._configuration = configuration;
+ _agentsModel = new AgentsModel(_configuration);
}
public IActionResult Index()
@@ -21,13 +22,38 @@ namespace MVC_databaskonstruktion.Controllers
return View();
}
- private void BuildAgentTables()
+ public IActionResult Delete(string table, string CodeName)
{
- _agentsModel = new AgentsModel(this._configuration);
+ try
+ {
+ _agentsModel.DeleteAgent(table, CodeName);
+ }
+ catch (MySqlException e)
+ {
+ switch (e.Number)
+ {
+ case 1451:
+ TempData["ErrorMessage"] = "Foreign Key Constraint Failed!";
+ break;
+ case 1452:
+ TempData["ErrorMessage"] = "Foreign Key Constraint Failed!";
+ break;
+ case 1062:
+ TempData["ErrorMessage"] = "Unique Constraint Failed!";
+ break;
+ default:
+ TempData["ErrorMessage"] = $"Something went wrong: {e.Number}";
+ break;
+ }
+ }
+ return RedirectToAction("Index");
+ }
+ private void BuildAgentTables()
+ {
ViewBag.FieldAgents = _agentsModel.GetFieldAgents();
ViewBag.GroupLeaders = _agentsModel.GetGroupLeaders();
ViewBag.Managers = _agentsModel.GetManagers();
}
}
-}
+}
+\ No newline at end of file
diff --git a/MVC-databaskonstruktion/Models/AgentsModel.cs b/MVC-databaskonstruktion/Models/AgentsModel.cs
@@ -1,5 +1,4 @@
using MVC_databaskonstruktion.Utils;
-using System.Data;
namespace MVC_databaskonstruktion.Models
{
@@ -13,19 +12,29 @@ namespace MVC_databaskonstruktion.Models
{
_configuration = configuration;
_databaseRepository = new DatabaseRepository(_configuration);
- _tableBuilder = new TableObjectBuilder();
+ _tableBuilder = new TableObjectBuilder()
+ .SetPrimaryKeys(new List<string> { "CodeName" })
+ .SetDeleteTable("Agent")
+ .SetRedirect("Index");
}
public TableObject GetAgents()
{
return _tableBuilder.SetControllerName("Agents")
- .SetDeleteTable("Agent")
.SetDataTable(_databaseRepository.GetTable("Agent"))
- .SetPrimaryKeys(new List<string> { "AgentId" })
- .SetRedirect("Index")
.Build();
}
+ public void DeleteAgent(string table, string CodeName)
+ {
+ List<KeyValuePair<string, string>> conditions = new List<KeyValuePair<string, string>>
+ {
+ new KeyValuePair<string, string>("CodeName", CodeName)
+ };
+
+ _databaseRepository.DeleteRow(table, conditions);
+ }
+
public TableObject GetFieldAgents()
{
return _tableBuilder
diff --git a/MVC-databaskonstruktion/Utils/DatabaseRepository.cs b/MVC-databaskonstruktion/Utils/DatabaseRepository.cs
@@ -28,5 +28,25 @@ namespace MVC_databaskonstruktion.Utils
return ds.Tables["result"];
}
}
+
+ public void DeleteRow(string table, List<KeyValuePair<string, string>> primaryKeys)
+ {
+ string whereClause = "WHERE ";
+ for (var i = 0; i < primaryKeys.Count; i++)
+ {
+ whereClause += $"{primaryKeys[i].Key} = '{primaryKeys[i].Value}'";
+ if (i < primaryKeys.Count - 1)
+ {
+ whereClause += " AND ";
+ }
+ }
+
+ using (MySqlConnection dbcon = new MySqlConnection(_connectionString))
+ {
+ dbcon.Open();
+ MySqlCommand cmd = new MySqlCommand($"DELETE FROM {table} {whereClause};", dbcon);
+ cmd.ExecuteNonQuery();
+ }
+ }
}
}
