SqlServer.cs 6.07 KB
Newer Older
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
1
2
3
4
5
6
7
8
9
10
11
12
13
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
    {
14
15
        private string _connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=H:\\Documents\\ThronesTournamentConsole\\Database\\db.mdf;Integrated Security=True;Connect Timeout=30";
        
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
16
17
18
19
20
21
        private DataTable SelectByDataAdapter(string request)
        {
            DataTable results = new DataTable();

            using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
            {
22
23
24
25
26
27
28
29
30
31
                try
                {
                    SqlCommand sqlCommand = new SqlCommand(request, sqlConnection);
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    sqlDataAdapter.Fill(results);
                }
                catch (SqlException e)
                {
                    Console.WriteLine(e.Message);
                }
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
32
33
34
35
36
            }

            return results;
        }

NEBOIT Jean-Charles's avatar
Update    
NEBOIT Jean-Charles committed
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
        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;
        }

NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
60
61
62
63
        public List<Character> CharactersList()
        {
            List<Character> res = new List<Character>();

64
            DataTable dataTable = SelectByDataAdapter("Select * From Character");
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
65
66
67

            foreach (DataRow row in dataTable.Rows)
            {
68
69
70
71
72
73
74
                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);
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
            }

            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()
        {
98
99
            List<Fight> res = new List<Fight>();
            return res;
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
        }

        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)
            {
NEBOIT Jean-Charles's avatar
Update    
NEBOIT Jean-Charles committed
125
                House house = new House(Convert.ToInt32(row["Id"]) , row["name"].ToString(), Convert.ToInt32(row["nbUnits"]));
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
126
127
128
129
130
131
132
133
                res.Add(house);
            }

            return res;
        }

        public List<Territory> TerritoriesList()
        {
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
            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;
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
150
151
152
153
        }

        public void addCharacter(Character character)
        {
NEBOIT Jean-Charles's avatar
Update    
NEBOIT Jean-Charles committed
154
            SelectByDataAdapter("INSERT INTO Character VALUES('" + character.FirstName + "','" + character.LastName + "'," + character.Characteristics.PV + "," + character.Characteristics.Type + "," + character.Characteristics.Bravoury + "," + character.Characteristics.Crazyness + "," + character.House.ID);
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
155
156
157
158
        }

        public void deleteCharacter(int id)
        {
NEBOIT Jean-Charles's avatar
Update    
NEBOIT Jean-Charles committed
159
            SelectByDataAdapter("DELETE FROM Character Where Id=" + id);
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
160
161
162
163
164
        }


        public void updateCharacter(Character character)
        {
165
            
NEBOIT Jean-Charles's avatar
TP2  
NEBOIT Jean-Charles committed
166
167
168
        }
    }
}