/// <summary>
        /// Brisanje dosežka iz baze
        /// </summary>
        /// <param name="idDosezka">Id dosežka kateroga brišemo</param>
        /// <returns></returns>
        public static bool Brisanje(int idDosezka)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string from   = "[StackDB].[dbo].[tblDosezki]";
            string delete = "DELETE FROM " + from + " WHERE Id=" + idDosezka;

            cmd.CommandText = delete;
            cmd.Connection  = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return(true);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(false);
            }
            catch (Exception ex)
            {
                // log
                return(false);
            }
            finally
            {
                con.Close();
            }
        }
Exemplo n.º 2
0
        /// <summary>
        /// Dodavanje novog dosežka za uporabnika
        /// </summary>
        public static bool DodajDosezekUporabnika(int idUporabnika, int idDosezka)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string into   = "[StackDB].[dbo].[tblDosezkiUporabnikov]";
            string insert = "INSERT INTO " + into + " (IdUporabnika, IdDosezka) VALUES (" +
                            idUporabnika + ", " + idDosezka + ");";

            cmd.CommandText = insert;
            cmd.Connection  = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return(true);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(false);
            }
            catch (Exception ex)
            {
                // log
                return(false);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Dodavanje novih dosežkov
        /// </summary>
        /// <param name="dosezek">Objekt s dosežkom kateroga hočemo dodati v bazo</param>
        /// <returns></returns>
        public static bool Dodaj(Dosezek dosezek)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string into   = "[StackDB].[dbo].[tblDosezki]";
            string insert = "INSERT INTO " + into + " (Naziv, Nagrada) VALUES ('" +
                            dosezek.Naziv + "', '" + dosezek.Nagrada + "');";

            cmd.CommandText = insert;
            cmd.Connection  = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return(true);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(false);
            }
            catch (Exception ex)
            {
                // log
                return(false);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Dodavanje novega top rezultata
        /// </summary>
        /// <param name="rezultat">Objekt z rezultatom kateroga hočemo dodati v bazo</param>
        /// <returns></returns>
        public static bool Dodaj(int userId, int score)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string into   = "[StackDB].[dbo].[tblTopRezultati]";
            string insert = "INSERT INTO " + into + " (IdUporabnika, Rezultat) VALUES (" +
                            userId + ", " + score + ");";

            cmd.CommandText = insert;
            cmd.Connection  = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return(true);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(false);
            }
            catch (Exception ex)
            {
                // log
                return(false);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Brisanje vsih rezultata kateri nisu med najboljšima
        /// </summary>
        /// <param name="steviloRezultata">Kolko najboljših rezultata želimo ostaviti</param>
        /// <returns></returns>
        public static bool BrisanjeSlabih(int steviloRezultata)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string from   = "[StackDB].[dbo].[tblTopRezultati]";
            string delete = "DELETE FROM " + from + " WHERE Rezultat < " +
                            "(SELECT MIN(Rezultat) FROM " +
                            "(SELECT TOP " + steviloRezultata + " Rezultat " +
                            "FROM " + from + " ORDER BY Rezultat DESC) AS Reze)";

            cmd.CommandText = delete;
            cmd.Connection  = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return(true);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(false);
            }
            catch (Exception ex)
            {
                // log
                return(false);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Brskanje baze po uporabniškem imenu
        /// </summary>
        /// <param name="uporabniskoIme">Brska se po parametru 'Uporabnik'</param>
        /// <returns>Eneg uporabnika ali prazno listo</returns>
        public static List <Uporabnik> Brskaj(int idUporabnika = -1, string uporabniskoIme = "")
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string where = " 1 = 1 ";
            if (uporabniskoIme != "")
            {
                where += " AND (Uporabnik = '" + uporabniskoIme + "')";
            }
            if (idUporabnika != -1)
            {
                where += " AND (Id = " + idUporabnika + ")";
            }
            //                       0   1      2       3        4            5           6
            string select = "SELECT Id, Ime, Priimek, Email, Uporabnik, TipUporabnika, Kovanc " +
                            "FROM [StackDB].[dbo].[tblUporabnik] WHERE" + where;

            cmd.CommandText = select;
            cmd.Connection  = con;

            List <Uporabnik> lista = new List <Uporabnik>();

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int           id          = reader.GetInt32(0);
                    string        ime         = reader.IsDBNull(1) ? "" : reader.GetString(1).Trim();
                    string        priimek     = reader.IsDBNull(2) ? "" : reader.GetString(2).Trim();
                    string        email       = reader.IsDBNull(3) ? "" : reader.GetString(3).Trim();
                    string        uporabnisko = reader.GetString(4).Trim();
                    TipUporabnika tip         = (TipUporabnika)reader.GetInt32(5);
                    int           kovanc      = reader.GetInt32(6);

                    Uporabnik uporabnik = new Uporabnik(id, ime, priimek, email, uporabnisko, tip, kovanc);
                    lista.Add(uporabnik);
                }
                reader.Close();
                return(lista);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(null);
            }
            catch (Exception ex)
            {
                // log
                return(null);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Pridobivanje najboljših rezultatov
        /// </summary>
        /// <param name="velikostSeznama">Število rezultatov kateri se vrnejo</param>
        /// <returns></returns>
        public static List <TopRezultat> GetTopRezultati(int velikostSeznama)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            if (velikostSeznama < 1)
            {
                velikostSeznama = 1;
            }

            string select = "SELECT TOP " + velikostSeznama +
                            //     0         1      2       3        4         5
                            " IdUporabnika, Ime, Priimek, Email, Uporabnik, Rezultat " +
                            "FROM [StackDB].[dbo].[viewRezultati] ORDER BY Rezultat DESC";

            cmd.CommandText = select;
            cmd.Connection  = con;

            List <TopRezultat> lista = new List <TopRezultat>();

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int    id          = reader.GetInt32(0);
                    string ime         = reader.IsDBNull(1) ? "" : reader.GetString(1).Trim();
                    string priimek     = reader.IsDBNull(2) ? "" : reader.GetString(2).Trim();
                    string email       = reader.IsDBNull(3) ? "" : reader.GetString(3).Trim();
                    string uporabnisko = reader.IsDBNull(4) ? "" : reader.GetString(4).Trim();
                    int    rezultat    = reader.IsDBNull(5) ? -1 : reader.GetInt32(5);

                    Uporabnik   uporabnik   = new Uporabnik(id, ime, priimek, email, uporabnisko, 0, 0);
                    TopRezultat topRezultat = new TopRezultat(uporabnik, rezultat);

                    lista.Add(topRezultat);
                }
                reader.Close();
                return(lista);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(null);
            }
            catch (Exception ex)
            {
                // log
                return(null);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Avtorizacija uporabnika
        /// </summary>
        /// <param name="idUporabnika">ID uporabnika kateroga preverjamo</param>
        /// <param name="novoGesloString">Geslo katero preverjamo če velja</param>
        /// <returns></returns>
        internal static bool?PotrdiGeslo(int idUporabnika, string novoGesloString)
        {
            Uporabnik u = Brskaj(idUporabnika)[0];

            byte[] novoGeslo = Encoding.UTF8.GetBytes(novoGesloString);
            byte[] geslo     = new byte[32];
            byte[] salt      = new byte[28];

            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string select = "SELECT Geslo, Salt FROM [StackDB].[dbo].[tblUporabnik] WHERE Id = " + idUporabnika;

            cmd.CommandText = select;
            cmd.Connection  = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    geslo = (byte[])reader["Geslo"];
                    salt  = (byte[])reader["Salt"];
                }
                reader.Close();
                byte[] noviHash = GenerateSaltedHash(novoGeslo, salt);
                if (CompareByteArrays(noviHash, geslo))
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(null);
            }
            catch (Exception ex)
            {
                // log
                return(null);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Update postoječih uporabnikov
        /// </summary>
        /// <param name="uporabnik">Objekt s uporabnikom kateroga hočemo spremeniti v bazi</param>
        /// <param name="salt">Opcionalni parameter kje se pohrani salt od uporabnikovog gesla</param>
        /// <param name="geslo">Opcionalni parameter z hashiranim geslom</param>
        /// <returns></returns>
        public static bool Update(Uporabnik uporabnik, byte[] salt = null, byte[] geslo = null)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string table = "[StackDB].[dbo].[tblUporabnik]";

            string where = " WHERE Id = " + uporabnik.Id;
            string update = "UPDATE " + table +
                            " SET Ime = '" + uporabnik.Ime + "', " +
                            "Priimek = '" + uporabnik.Priimek + "', " +
                            "Email = '" + uporabnik.Email + "', " +
                            "Uporabnik = '" + uporabnik.Uporabnisko + "', " +
                            "TipUporabnika = " + (int)uporabnik.Tip +
                            ", Kovanc = " + uporabnik.Kovanc;

            if (salt != null && geslo != null)
            {
                update += ", Salt = @Salt, Geslo = @Geslo";

                cmd.Parameters.Add("@Salt", SqlDbType.Binary);
                cmd.Parameters["@Salt"].Value = salt;

                cmd.Parameters.Add("@Geslo", SqlDbType.Binary);
                cmd.Parameters["@Geslo"].Value = geslo;
            }

            cmd.CommandText = update + where;
            cmd.Connection  = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return(true);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(false);
            }
            catch (Exception ex)
            {
                // log
                return(false);
            }
            finally
            {
                con.Close();
            }
        }
Exemplo n.º 10
0
        /// <summary>
        /// Seznam dosezkov katere je uporabnik odljučal
        /// </summary>
        /// <returns>seznam dosezkov</returns>
        public static List <int> UporabnikMaDosezek(int idUporabnika)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string where = "1 = 1 ";
            where       += (idUporabnika != -1) ? ("AND (IdUporabnika = " + idUporabnika + ") ") : ("");

            string from = "[StackDB].[dbo].[tblDosezkiUporabnikov]";
            //                           0             1
            string select = "SELECT IdUporabnika, idDosezka FROM " + from + " WHERE " + where;


            cmd.CommandText = select;
            cmd.Connection  = con;

            List <int> lista = new List <int>();

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int idDosezka = reader.GetInt32(1);

                    lista.Add(idDosezka);
                }
                reader.Close();
                return(lista);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(null);
            }
            catch (Exception ex)
            {
                // log
                return(null);
            }
            finally
            {
                con.Close();
            }
        }
Exemplo n.º 11
0
        /// <summary>
        /// Seznam shop itema katere je uporabnik odljučal
        /// </summary>
        /// <returns>seznam shop itema</returns>
        public static List <ShopItem> Brskaj()
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string from = "[StackDB].[dbo].[tblShop]";
            //                       0    1
            string select = "SELECT Id, Naziv, Cijena FROM " + from;

            cmd.CommandText = select;
            cmd.Connection  = con;

            List <ShopItem> lista = new List <ShopItem>();

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int    idItema = reader.GetInt32(0);
                    string naziv   = reader.GetString(1);
                    int    cijena  = reader.GetInt32(2);

                    lista.Add(new ShopItem(idItema, naziv.Trim(), cijena));
                }
                reader.Close();
                return(lista);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(null);
            }
            catch (Exception ex)
            {
                // log
                return(null);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Dodavanje novih uporabnikov
        /// </summary>
        /// <param name="uporabnik">Objekt s uporabnikom kateroga hočemo dodati v bazo</param>
        /// <returns>ID noveg uporabnika</returns>
        public static int Dodaj(Uporabnik uporabnik, string geslo)
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string into   = "[StackDB].[dbo].[tblUporabnik]";
            string insert = "INSERT INTO " + into + " (Ime, Priimek, Email, Uporabnik, Geslo, Salt, TipUporabnika, Kovanc) " +
                            "VALUES (@Ime, @Priimek, @Email, @Uporabnik, @Geslo, @Salt, @TipUporabnika, @Kovanc); " +
                            "SELECT CAST(scope_identity() as int)";//'" + uporabnik.Ime + "', '" + uporabnik.Priimek + "', '" + uporabnik.Email + "', '" +

            //uporabnik.Uporabnisko + "', " + uporabnik.Tip + ", " + uporabnik.Kovanc;

            byte[] gesloBytes = Encoding.UTF8.GetBytes(geslo);
            byte[] saltBytes  = Encoding.UTF8.GetBytes(CreateSalt(20));
            byte[] gesloHash  = GenerateSaltedHash(gesloBytes, saltBytes);

            uporabnik.Parametriziraj(ref cmd, gesloHash, saltBytes);
            cmd.CommandText = insert;
            cmd.Connection  = con;

            try
            {
                con.Open();
                int?id = -1;
                id = (int?)cmd.ExecuteScalar();
                return((id != null) ? (int)id : -1);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(-1);
            }
            catch (Exception ex)
            {
                // log
                return(-1);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Brskanje dosezkov za pregled
        /// </summary>
        /// <param name="naziv">brskanje po nazivu dosezka</param>
        /// <param name="nagradaMin">donja granica vrednosti nagrade</param>
        /// <param name="nagradaMax">zgornja granica vrednosti nagrade</param>
        /// <returns>seznam dosezkov</returns>
        public static List <Dosezek> Brskaj(string naziv = "", int nagradaMin = -1, int nagradaMax = -1, int idUporabnika = -1, string uporabnisko = "")
        {
            SqlConnection con = new SqlConnection(Nastavitve.GetConnectionString());
            SqlCommand    cmd = new SqlCommand();

            string where = "1 = 1 ";
            where       += (naziv.Trim() != "") ? ("AND (Naziv = '" + naziv + "') ") : ("");
            where       += (nagradaMin != -1) ? ("AND (Nagrada > " + nagradaMin + ") ") : ("");
            where       += (nagradaMax != -1) ? ("AND (Nagrada < " + nagradaMax + ") ") : ("");
            where       += (idUporabnika != -1) ? ("AND (UporabnikId = " + idUporabnika + ") ") : ("");
            where       += (uporabnisko != "") ? ("AND (Uporabnik = '" + uporabnisko + "') ") : ("");

            string from = "[StackDB].[dbo].[tblDosezki]";
            //  0    1       2
            string select = "SELECT Id, Naziv, Nagrada FROM " + from + " WHERE " + where;


            // če se brska za nekaterog uporabnika
            if (idUporabnika != -1 || uporabnisko != "")
            {
                from = "[StackDB].[dbo].[viewDosezki]";
                //    0        1       2
                select = "SELECT DosezekId, Naziv, Nagrada FROM " + from + " WHERE " + where;
            }

            cmd.CommandText = select;
            cmd.Connection  = con;

            List <Dosezek> lista = new List <Dosezek>();

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int    id           = reader.GetInt32(0);
                    string nazivDosezka = reader.IsDBNull(1) ? "" : reader.GetString(1).Trim();
                    int    nagrada      = reader.IsDBNull(2) ? -1 : reader.GetInt32(2);

                    Dosezek enota = new Dosezek(id, nazivDosezka, nagrada);

                    lista.Add(enota);
                }
                reader.Close();
                return(lista);
            }
            catch (TimeoutException tEx)
            {
                // Zapisivanje u log
                return(null);
            }
            catch (Exception ex)
            {
                // log
                return(null);
            }
            finally
            {
                con.Close();
            }
        }