/// <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(); } }
/// <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(); } }
/// <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(); } }
/// <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(); } }