SqlServer.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntitiesLayer;
using System.Data.SqlClient;
using System.Data;

namespace DataAccessLayer
{
    class SqlServer : DataBaseBridge
    {
        private string _connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=H:\\Documents\\ThronesTournamentConsole\\Database\\db.mdf;Integrated Security=True;Connect Timeout=30";
        
        private DataTable SelectByDataAdapter(string request)
        {
            DataTable results = new DataTable();

            using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
            {
                try
                {
                    SqlCommand sqlCommand = new SqlCommand(request, sqlConnection);
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    sqlDataAdapter.Fill(results);
                }
                catch (SqlException e)
                {
                    Console.WriteLine(e.Message);
                }
            }

            return results;
        }

        private int UpdateByCommandBuilder(string request, DataTable table)
        {
            int result = 0;

            using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(request, sqlConnection);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

                sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
                sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand();
                sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();

                sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                result = sqlDataAdapter.Update(table);
            }

            return result;
        }

        public List<Character> CharactersList()
        {
            List<Character> res = new List<Character>();

            DataTable dataTable = SelectByDataAdapter("Select * From Character");

            foreach (DataRow row in dataTable.Rows)
            {
                int id_house = Convert.ToInt32(row["id_house"]);

                DataTable houseData = SelectByDataAdapter("Select * From House Where Id=" + id_house);
                DataRow houseRow = houseData.Rows[0];
                House house = new House(Convert.ToInt32(houseRow["Id"]), houseRow["name"].ToString(), Convert.ToInt32(houseRow["nbUnits"]));
                Character character = new Character(Convert.ToInt32(row["Id"]), row["FirstName"].ToString(), row["LastName"].ToString(), Convert.ToUInt32(row["PV"]), (CharacterType)Convert.ToInt32(row["Type"]), Convert.ToInt32(row["Bravoury"]), Convert.ToInt32(row["Crazyness"]), house);
                res.Add(character);
            }

            return res;
        }

        public List<Characteristics> CharactersCharacteristicsList()
        {
            List<Characteristics> res = new List<Characteristics>();

            DataTable dataTable = SelectByDataAdapter("Select * From Character");

            foreach (DataRow row in dataTable.Rows)
            {
                Characteristics characteristics = new Characteristics(Convert.ToUInt32(row["PV"]), (CharacterType)Convert.ToInt32(row["Type"]), Convert.ToInt32(row["Bravoury"]), Convert.ToInt32(row["Crazyness"]));
                res.Add(characteristics);
            }

            return res;
        }


        public List<Fight> FightsList()
        {
            List<Fight> res = new List<Fight>();
            return res;
        }

        public List<House> HousesList()
        {
            List<House> res = new List<House>();

            DataTable dataTable = SelectByDataAdapter("Select * From House");

            foreach (DataRow row in dataTable.Rows)
            {
                House house = new House(Convert.ToInt32(row["Id"]), row["name"].ToString(), Convert.ToInt32(row["nbUnits"]));
                res.Add(house);
            }

            return res;
        }

        public List<House> HousesList(int sup)
        {
            List<House> res = new List<House>();

            DataTable dataTable = SelectByDataAdapter("Select * From House Where nbUnits >" + sup);

            foreach (DataRow row in dataTable.Rows)
            {
                House house = new House(Convert.ToInt32(row["Id"]) , row["name"].ToString(), Convert.ToInt32(row["nbUnits"]));
                res.Add(house);
            }

            return res;
        }

        public List<Territory> TerritoriesList()
        {
            List<Territory> res = new List<Territory>();

            DataTable dataTable = SelectByDataAdapter("Select * From Territory");

            foreach (DataRow row in dataTable.Rows)
            {
                int id_house = Convert.ToInt32(row["id_house"]);

                DataTable houseData = SelectByDataAdapter("Select * From House Where Id=" + id_house);
                DataRow houseRow = houseData.Rows[0];
                House owner = new House(Convert.ToInt32(houseRow["Id"]), houseRow["name"].ToString(), Convert.ToInt32(houseRow["nbUnits"]));
                Territory territory = new Territory(Convert.ToInt32(row["Id"]), (TerritoryType)Convert.ToInt32(row["type"]), owner);
                res.Add(territory);
            }

            return res;
        }

        public void addCharacter(Character character)
        {
            SelectByDataAdapter("INSERT INTO Character VALUES('" + character.FirstName + "','" + character.LastName + "'," + character.Characteristics.PV + "," + character.Characteristics.Type + "," + character.Characteristics.Bravoury + "," + character.Characteristics.Crazyness + "," + character.House.ID);
        }

        public void deleteCharacter(int id)
        {
            SelectByDataAdapter("DELETE FROM Character Where Id=" + id);
        }


        public void updateCharacter(Character character)
        {
            
        }
    }
}