SsjCrSuG

1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using System.Data.SQLite;
7
8namespace killerapp
9{
10    class Database
11    {
12        public List<Schedule> schedulesDB = new List<Schedule>();
13        public List<Schedule> exDB = new List<Schedule>();
14        SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
15        public void enterSchedule(string sched)
16        {
17            conn.Open();
18            string sql = "create table if not exists schedules (schedName varchar(20))";
19            SQLiteCommand command = new SQLiteCommand(sql, conn);
20            command.ExecuteNonQuery();
21            sql = "insert into schedules (schedName) values (@Sched)";
22            command = new SQLiteCommand(sql, conn);
23            command.Parameters.Add(new SQLiteParameter("Sched", sched));
24            command.ExecuteNonQuery();
25            conn.Close();
26
27        }
28
29        public void enterExercise(string sched, string exname, int reps, int sets, int rest)
30        {
31            conn.Open();
32            string sql = "create table if not exists exercises (schedName varchar(20), exName varchar(20), reps INT, sets INT, rest INT)";
33            SQLiteCommand command = new SQLiteCommand(sql, conn);
34            command.ExecuteNonQuery();
35            sql = "insert into exercises (schedName, exName, reps, sets, rest) values (@Sched, @ex, @rep, @set, @rust)";
36            command = new SQLiteCommand(sql, conn);
37            command.Parameters.Add(new SQLiteParameter("Sched", sched));
38            command.Parameters.Add(new SQLiteParameter("ex", exname));
39            command.Parameters.Add(new SQLiteParameter("rep", reps));
40            command.Parameters.Add(new SQLiteParameter("set", sets));
41            command.Parameters.Add(new SQLiteParameter("rust", rest));
42            command.ExecuteNonQuery();
43            conn.Close();
44
45        }
46        public void loadExercise()//not using this if I can add it in the loadSchedules ignore it
47        {
48            conn.Open();
49            foreach (var Schedule in schedulesDB)
50            {
51                string sched = Schedule.getName;
52                string sql = "select * from exercises where schedname=@Sched";
53                SQLiteCommand command = new SQLiteCommand(sql, conn);
54                command.Parameters.Add(new SQLiteParameter("Sched", sched));
55                command.ExecuteNonQuery();
56                var reader = command.ExecuteReader();
57                while (reader.Read())
58                {
59                    string columnA = Convert.ToString(reader["exName"]);
60                    int setsx = Convert.ToInt32(reader["sets"]);
61                    int repsx = Convert.ToInt32(reader["reps"]);
62                    int restx = Convert.ToInt32(reader["rest"]);
63                    if (columnA == null)
64                    {
65                        // log the error here, do some handling or cleanup, whatever you think needs to be done
66                        // if this isnt a big deal its usually safe to just move on to the next row like this:
67
68                        continue; // skips the rest of the code in the while loop and moves on to the next row, if any
69                    }
70                    Exercise newExercise = new Exercise(columnA, setsx, repsx, restx, sched);
71                    Schedule.addExercise(newExercise);
72                    //Add exercise to the right schedule, but how do I do that? x_x
73
74                }
75
76
77            }
78        }
79        public void loadSchedule()
80        {
81            conn.Open();
82            string sql = "select * from schedules";
83            SQLiteCommand command = new SQLiteCommand(sql, conn);
84            command.ExecuteNonQuery();
85            var reader = command.ExecuteReader();
86            while (reader.Read())
87            {
88                //get each column in the row like this:
89                var column = reader["schedName"]?.ToString();
90                if (column == null)
91                {
92                    // log the error here, do some handling or cleanup, whatever you think needs to be done
93                    // if this isnt a big deal its usually safe to just move on to the next row like this:
94
95                    continue; // skips the rest of the code in the while loop and moves on to the next row, if any
96                }
97                Schedule newSchedule = new Schedule(column);
98                schedulesDB.Add(newSchedule);
99            }
100            conn.Close();
101        }
102        public void deleteRow(string sched)
103        {
104            conn.Open();
105            string sql = "delete from schedules where schedName=@Sched;";
106            SQLiteCommand command = new SQLiteCommand(sql, conn);
107            command.Parameters.Add(new SQLiteParameter("Sched", sched));
108            command.ExecuteNonQuery();
109            conn.Close();
110        }
111    }
112}