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