m1hKH8i7

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(string sched)//not using this if I can add it in the loadSchedules ignore it
47        {
48            conn.Open();
49            string sql = "select * from exercises where schedname=@Sched";
50            SQLiteCommand command = new SQLiteCommand(sql, conn);
51            command.Parameters.Add(new SQLiteParameter("Sched", sched));
52            command.ExecuteNonQuery();
53        }
54        public void loadSchedule()
55        {
56            conn.Open();
57            string sql = "select * from schedules";
58            SQLiteCommand command = new SQLiteCommand(sql, conn);
59            command.ExecuteNonQuery();
60            var reader = command.ExecuteReader();
61            while (reader.Read())
62            {
63                //get each column in the row like this:
64                var column = reader["schedName"]?.ToString();
65                if (column == null)
66                {
67                    // log the error here, do some handling or cleanup, whatever you think needs to be done
68                    // if this isnt a big deal its usually safe to just move on to the next row like this:
69
70                    continue; // skips the rest of the code in the while loop and moves on to the next row, if any
71                }
72                Schedule newSchedule = new Schedule(column);
73            sql = "select * from exercises where schedname=@Sched";
74            command = new SQLiteCommand(sql, conn);
75            command.Parameters.Add(new SQLiteParameter("Sched", newSchedule));
76            command.ExecuteNonQuery();
77                while (reader.Read())
78                {
79                        //get each column in the row like this:
80                        var columnA = reader["exName"]?.ToString();
81                        var columnB = reader["sets"]?.ToString();
82                        var columnC = reader["reps"]?.ToString();
83                        var columnD = reader["rest"]?.ToString();
84                        int setsx = Convert.ToInt32(columnB);
85                        int repsx = Convert.ToInt32(columnB);
86                        int restx = Convert.ToInt32(columnB);
87                        if (columnA == null)
88                        {
89                            // log the error here, do some handling or cleanup, whatever you think needs to be done
90                            // if this isnt a big deal its usually safe to just move on to the next row like this:
91
92                            continue; // skips the rest of the code in the while loop and moves on to the next row, if any
93                        }
94                        Exercise newExercise = new Exercise(columnA, setsx, repsx, restx, column);
95                        newSchedule.addExercise(newExercise);
96                        //Add exercise to the right schedule, but how do I do that? x_x
97 
98                    }
99                                schedulesDB.Add(newSchedule);
100            }
101            conn.Close();
102        }
103        public void deleteRow(string sched)
104        {
105            conn.Open();
106            string sql = "delete from schedules where schedName=@Sched;";
107            SQLiteCommand command = new SQLiteCommand(sql, conn);
108            command.Parameters.Add(new SQLiteParameter("Sched", sched));
109            command.ExecuteNonQuery();
110            conn.Close();
111        }
112    }
113}