Exemplo n.º 1
0
        public static int PersonId(Login aanmeld)
        {
            // create a sql connection
            SqlConnection connection = DALConnection.GetConnectionByName("Reader");

            // write a sql query
            string sqlQuery = @"SELECT PersoonId FROM login 
                                where AanmeldNaam = @AanmeldNaam AND PwdHash = @PwdHash";

            // execute the sql query
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            command.Parameters.AddWithValue("@AanmeldNaam", aanmeld.Naam);
            command.Parameters.AddWithValue("@PwdHash", aanmeld.Pwdhash);
            command.ExecuteNonQuery();
            SqlDataReader reader = command.ExecuteReader();

            int personId = 0;

            while (reader.Read())
            {
                personId = (int)reader["PersoonId"];
            }

            // close all connections
            reader.Close();
            DALConnection.CloseSqlConnection(connection);

            return(personId);
        }
Exemplo n.º 2
0
        public static int GetPersonId(DeelnemerCategorie category, string naam, string achternaam,
                                      DateTime gebdatum)
        {
            // create a sql connection
            SqlConnection connection = DALConnection.GetConnectionByName("Writer");

            // write a sql query
            string sqlQuery = @"INSERT INTO Persoon(Naam, Achternaam, Categorie, GeboorteDatum) 
                                VALUES(@Naam, @Achternaam, @Categorie, @GeboorteDatum)";

            string sqlQuery1 = @"SELECT PersoonId FROM Persoon 
                                 where Naam = @Naam AND Achternaam = @Achternaam";

            // execute the sql query
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            command.Parameters.AddWithValue("@Naam", naam);
            command.Parameters.AddWithValue("@Achternaam", achternaam);
            command.Parameters.AddWithValue("@Categorie", category);
            command.Parameters.AddWithValue("@GeboorteDatum", gebdatum);
            command.ExecuteNonQuery();

            // execute the sql query
            SqlCommand command1 = new SqlCommand(sqlQuery1, connection);

            command1.Parameters.AddWithValue("@Naam", naam);
            command1.Parameters.AddWithValue("@Achternaam", achternaam);
            int id = (int)command1.ExecuteScalar();

            // close the connection
            DALConnection.CloseSqlConnection(connection);

            return(id);
        }
Exemplo n.º 3
0
        public static int GetLoginId(int persoonid, string pwdhash)
        {
            SqlConnection connection = DALConnection.GetConnectionByName("Reader");
            // find login based on the person and the hash
            string query = @"
                SELECT LoginId 
                FROM Login
                WHERE PersoonId = @person AND PwdHash = @hash;
            ";

            SqlCommand command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("person", persoonid);
            command.Parameters.AddWithValue("hash", pwdhash);

            SqlDataReader reader = command.ExecuteReader();

            int loginId = 0;

            // should only be one, so no loop
            if (reader.Read())
            {
                loginId = (int)reader["LoginId"];
            }

            reader.Close();
            DALConnection.CloseSqlConnection(connection);

            return(loginId);
        }
Exemplo n.º 4
0
        public static List <Locatie> GetLocaties()
        {
            SqlConnection connection = DALConnection.GetConnectionByName("Reader");

            List <Locatie> locations = new List <Locatie>();

            // select all columns (but no * because columns may be added in the future)
            string sqlQuery = "SELECT LocatieId, Gebouw, Zaal, Omschrijving FROM Locatie";

            SqlCommand    command = new SqlCommand(sqlQuery, connection);
            SqlDataReader reader  = command.ExecuteReader();

            while (reader.Read())
            {
                Locatie location = new Locatie();

                location.Id           = (int)reader["LocatieId"];
                location.Gebouw       = (string)reader["Gebouw"];
                location.Zaal         = (string)reader["Zaal"];
                location.Omschrijving = (string)reader["Omschrijving"];

                locations.Add(location);
            }

            // dispose of all open connections
            reader.Close();
            DALConnection.CloseSqlConnection(connection);

            return(locations);
        }
Exemplo n.º 5
0
        public static List <Persoon> GetPersonen()
        {
            SqlConnection connection = DALConnection.GetConnectionByName("Reader");

            List <Persoon> Personen = new List <Persoon>();

            //get information from DB
            string SQLquery = "SELECT PersoonId, Naam, Achternaam, Categorie, GeboorteDatum FROM Persoon";

            SqlCommand command = new SqlCommand(SQLquery, connection);

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Persoon persoon = new Persoon();
                //save a person
                persoon.Persoonid     = (int)reader["PersoonId"];
                persoon.Naam          = Convert.ToString(reader["Naam"]);
                persoon.Achternaam    = Convert.ToString(reader["Achternaam"]);
                persoon.Geboortedatum = DateTime.Parse(reader["GeboorteDatum"].ToString());
                persoon.Categorie     = (DeelnemerCategorie)reader["Categorie"];
                //add to the list
                Personen.Add(persoon);
            }

            reader.Close();

            DALConnection.CloseSqlConnection(connection);

            return(Personen);
        }
Exemplo n.º 6
0
        public static List <Cursus> GetCursussen()
        {
            // create a sql connection
            SqlConnection connection = DALConnection.GetConnectionByName("Reader");

            // create a list for storing data in it
            List <Cursus> coursesList = new List <Cursus>();

            // write a sql query
            string sqlQuery = "SELECT CursusId, Naam, Niveau, Toelichting, Categorie FROM Cursus";

            // execute the sql query
            SqlCommand    command = new SqlCommand(sqlQuery, connection);
            SqlDataReader reader  = command.ExecuteReader();

            // read data from DB
            while (reader.Read())
            {
                Cursus course = new Cursus();
                course.Id          = (int)reader["CursusId"];
                course.Naam        = Convert.ToString(reader["Naam"]);
                course.Niveau      = Convert.ToInt32(reader["Niveau"]);
                course.Toelichting = Convert.ToString(reader["Toelichting"]);
                course.Categorie   = (DeelnemerCategorie)reader["Categorie"];

                // add data to thr list
                coursesList.Add(course);
            }

            // close all open connections
            reader.Close();
            DALConnection.CloseSqlConnection(connection);

            return(coursesList);
        }
Exemplo n.º 7
0
        public static void UpdatePwd(int loginid, string pwdhash)
        {
            SqlConnection connection = DALConnection.GetConnectionByName("Writer");

            string query = @"
                UPDATE Login 
                SET PwdHash = @hash
                WHERE LoginId = @id;
            ";

            SqlCommand command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("id", loginid);
            command.Parameters.AddWithValue("hash", pwdhash);

            command.ExecuteNonQuery();

            DALConnection.CloseSqlConnection(connection);
        }
Exemplo n.º 8
0
        public static Contact GetContactInfoByPersoonId(int persoonId)
        {
            // NOTE: only here for testing! otherwise cannot test the other methods

            SqlConnection connection = DALConnection.GetConnectionByName("Reader");

            string query = @"
                SELECT * FROM ContactInfo
                WHERE PersoonId = @person
            ";

            SqlCommand command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("person", persoonId);

            SqlDataReader reader = command.ExecuteReader();

            Contact contact = new Contact();

            contact.Persoonid = persoonId;

            if (!reader.Read())
            {
                // user not found
                return(contact);
            }

            contact.Id                   = (int)reader["ContactInfoId"];
            contact.Persoonid            = (int)reader["PersoonId"];
            contact.Straat               = (string)reader["Straat"];
            contact.Huisnummer           = (int)reader["Huisnummer"];
            contact.Huisnummertoevoeging = (string)reader["Huisnummertoevoeging"];
            contact.Plaats               = (string)reader["Plaats"];
            contact.Postcode             = (string)reader["Postcode"];
            contact.Email                = (string)reader["Email"];
            contact.Telefoon             = (string)reader["Telefoon"];

            reader.Close();
            DALConnection.CloseSqlConnection(connection);

            return(contact);
        }
Exemplo n.º 9
0
        public static void SetLoginInfo(Login aanmeld, int persId)
        {
            // create a sql connection
            SqlConnection connection = DALConnection.GetConnectionByName("Writer");

            // write a sql query
            string sqlQuery = @"INSERT INTO Login(AanmeldNaam, PwdHash, PersoonId) 
                                VALUES(@AanmeldNaam, @PwdHash, @PersoonId)";

            // execute the sql query
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            command.Parameters.AddWithValue("@AanmeldNaam", aanmeld.Naam);
            command.Parameters.AddWithValue("@PwdHash", aanmeld.Pwdhash);
            command.Parameters.AddWithValue("@PersoonId", persId);
            command.ExecuteNonQuery();

            // close the connection
            DALConnection.CloseSqlConnection(connection);
        }
Exemplo n.º 10
0
        public static void voegtoeContactInfo(Contact info)
        {
            SqlConnection connection = DALConnection.GetConnectionByName("Writer");

            //insert a new user information
            string     sqlQuery = @"
                INSERT INTO ContactInfo(
                    PersoonId,
                    Straat,
                    Huisnummer,
                    Huisnummertoevoeging,
                    Plaats,
                    Postcode,
                    Email,
                    Telefoon
                ) VALUES (
                    @PersoonId,
                    @Straat,
                    @Huisnummer,
                    @Huisnummertoevoeging,
                    @Plaats,
                    @Postcode,
                    @Email,
                    @Telefoon
                )";
            SqlCommand command  = new SqlCommand(sqlQuery, connection);

            //replace parameters by values
            command.Parameters.AddWithValue("PersoonId", info.Persoonid);
            command.Parameters.AddWithValue("Straat", info.Straat);
            command.Parameters.AddWithValue("Huisnummer", info.Huisnummer);
            command.Parameters.AddWithValue("Huisnummertoevoeging", info.Huisnummertoevoeging);
            command.Parameters.AddWithValue("Plaats", info.Plaats);
            command.Parameters.AddWithValue("Postcode", info.Postcode);
            command.Parameters.AddWithValue("Email", info.Email);
            command.Parameters.AddWithValue("Telefoon", info.Telefoon);
            command.ExecuteNonQuery();
            //close connection

            DALConnection.CloseSqlConnection(connection);
        }
Exemplo n.º 11
0
        public static Persoon checkCredentials(Login user)
        {
            // create a sql connection
            SqlConnection connection = DALConnection.GetConnectionByName("Reader");

            // get a person ID
            int personId = PersonId(user);

            // write a sql query
            string SQLquery = @"SELECT PersoonId, Naam, Achternaam, Categorie, GeboorteDatum 
                                FROM Persoon WHERE PersoonId = @PersoonId ";

            // execute the sql query
            SqlCommand command = new SqlCommand(SQLquery, connection);

            command.Parameters.AddWithValue("@PersoonId", personId);
            command.ExecuteNonQuery();

            // read from db
            SqlDataReader reader = command.ExecuteReader();

            Persoon persoon = new Persoon();

            while (reader.Read())
            {
                persoon.Persoonid     = (int)reader["PersoonId"];
                persoon.Naam          = Convert.ToString(reader["Naam"]);
                persoon.Achternaam    = Convert.ToString(reader["Achternaam"]);
                persoon.Geboortedatum = DateTime.Parse(reader["GeboorteDatum"].ToString());
                persoon.Categorie     = (DeelnemerCategorie)reader["Categorie"];
            }

            // close all connections
            reader.Close();
            DALConnection.CloseSqlConnection(connection);

            return(persoon);
        }
Exemplo n.º 12
0
        public static void vernieuwContactInfo(Contact info)
        {
            SqlConnection connection = DALConnection.GetConnectionByName("Writer");

            //update user info, creating the query
            string     sqlQuery = "UPDATE ContactInfo SET PersoonId = @PersoonId, Straat = @Straat, Huisnummer = @Huisnummer, Huisnummertoevoeging = @Huisnummertoevoeging, Plaats = @Plaats, Postcode = @Postcode, Email = @Email, Telefoon = @Telefoon WHERE ContactInfoId = @ContactInfoId";
            SqlCommand command  = new SqlCommand(sqlQuery, connection);


            //replace parameters by values
            command.Parameters.AddWithValue("PersoonId", info.Persoonid);
            command.Parameters.AddWithValue("Straat", info.Straat);
            command.Parameters.AddWithValue("Huisnummer", info.Huisnummer);
            command.Parameters.AddWithValue("Huisnummertoevoeging", info.Huisnummertoevoeging);
            command.Parameters.AddWithValue("Plaats", info.Plaats);
            command.Parameters.AddWithValue("Postcode", info.Postcode);
            command.Parameters.AddWithValue("Email", info.Email);
            command.Parameters.AddWithValue("Telefoon", info.Telefoon);
            command.Parameters.AddWithValue("ContactInfoId", info.Id);
            command.ExecuteNonQuery();
            //close connection

            DALConnection.CloseSqlConnection(connection);
        }