MVC-databaskonstruktion

commit d349f54847318a1f1f06d86d18a55c32b82354e3
parent 486ad52cb23018ca12eb6d9243a3ddccdf072017
Author: William Lindholm <a22willi@student.his.se>
Date:   Mon,  9 Oct 2023 11:42:33 +0200

Added generic implementation for creating databasetables using class DataBaseRepository.

Diffstat:
M
MVC-databaskonstruktion/Controllers/AgentsController.cs
 | 
4
+++-
M
MVC-databaskonstruktion/Models/AgentsModel.cs
 | 
26
+++++++++++++++++---------
A
MVC-databaskonstruktion/Models/DatabaseRepository.cs
 | 
32
++++++++++++++++++++++++++++++++

3 files changed, 52 insertions(+), 10 deletions(-)
diff --git a/MVC-databaskonstruktion/Controllers/AgentsController.cs b/MVC-databaskonstruktion/Controllers/AgentsController.cs
@@ -23,7 +23,9 @@ namespace MVC_databaskonstruktion.Controllers
         private void GetAgents()
         {
             _agentsModel = new AgentsModel(this._configuration);
-            DataTable agents = _agentsModel.GetAgents();
+            DataTable agents = _agentsModel.GetFieldAgents();
+            DataTable groupLeaders = _agentsModel.GetGroupLeaders();
+            DataTable managers = _agentsModel.GetManagers();
             ViewBag.Agents = agents;
         }
     }
diff --git a/MVC-databaskonstruktion/Models/AgentsModel.cs b/MVC-databaskonstruktion/Models/AgentsModel.cs
@@ -8,24 +8,32 @@ namespace MVC_databaskonstruktion.Models
     {
         private IConfiguration _configuration;
         private string connectionString;
+        private DatabaseRepository _databaseRepository;
 
         public AgentsModel(IConfiguration configuration)
         {
             _configuration = configuration;
-            this.connectionString = _configuration["ConnectionString"];
+            _databaseRepository = new DatabaseRepository(_configuration);
         }
 
         public DataTable GetAgents()
         {
-            MySqlConnection dbcon = new MySqlConnection(connectionString);
-            dbcon.Open();
-            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM Agent;", dbcon);
-            DataSet ds = new DataSet();
-            adapter.Fill(ds, "result");
-            DataTable agentsTable = ds.Tables["result"];
-            dbcon.Close();
+            return _databaseRepository.GetTable("Agent");
+        }
+
+        public DataTable GetFieldAgents()
+        {
+            return _databaseRepository.GetTable("FieldAgents");
+        }
 
-            return agentsTable;
+        public DataTable GetGroupLeaders()
+        {
+            return _databaseRepository.GetTable("GroupLeaders");
+        }
+
+        public DataTable GetManagers()
+        {
+            return _databaseRepository.GetTable("Managers");
         }
     }
 }
diff --git a/MVC-databaskonstruktion/Models/DatabaseRepository.cs b/MVC-databaskonstruktion/Models/DatabaseRepository.cs
@@ -0,0 +1,32 @@
+using MySql.Data.MySqlClient;
+using System.Data;
+
+namespace MVC_databaskonstruktion.Models
+{
+    public class DatabaseRepository
+    {
+        private readonly string _connectionString;
+
+        public DatabaseRepository(IConfiguration configuration)
+        {
+            _connectionString = configuration["ConnectionString"];
+        }
+
+        public DataTable GetTable(string query)
+        {
+            using (MySqlConnection dbcon = new MySqlConnection(_connectionString))
+            {
+                if (!query.Contains(" "))
+                {
+                    query = $"SELECT * FROM {query};";
+                }
+
+                dbcon.Open();
+                MySqlDataAdapter adapter = new MySqlDataAdapter(query, dbcon);
+                DataSet ds = new DataSet();
+                adapter.Fill(ds, "result");
+                return ds.Tables["result"];
+            }
+        }
+    }
+}