public static List <knjige> IzpisIzposojenih(int id_u) { List <knjige> knjige = new List <knjige>(); SQLiteConnection con = connect(); con.Open(); using (SQLiteCommand com = new SQLiteCommand(con)) { com.CommandText = "SELECT b.title, b.author, b.year, s.name, b.id FROM books b INNER JOIN sections s ON s.id=b.section_id INNER JOIN rents r ON r.book_id=b.id WHERE (b.current_state=0) AND (r.user_id='" + id_u + "');"; SQLiteDataReader read = com.ExecuteReader(); while (read.Read()) { string naslov = read.GetString(0); string avtor = read.GetString(1); int leto = read.GetInt32(2); string oddelek = read.GetString(3); int id = read.GetInt32(4); knjige knjiga = new knjige(naslov, avtor, leto, oddelek, id); knjige.Add(knjiga); } } con.Close(); return(knjige); }
public static List <knjige> IzpisKnjigNaVoljo(int status) { List <knjige> knjige = new List <knjige>(); SQLiteConnection con = connect(); con.Open(); using (SQLiteCommand com = new SQLiteCommand(con)) { com.CommandText = "SELECT b.id,b.title,b.year,b.author,b.publisher,s.name,b.current_state FROM books b INNER JOIN sections s ON b.section_id = s.id WHERE current_state = " + status + " ORDER BY b.id;"; SQLiteDataReader read = com.ExecuteReader(); while (read.Read()) { int id = read.GetInt32(0); string naslov = read.GetString(1); int leto = read.GetInt32(2); string avtor = read.GetString(3); string publisher = read.GetString(4); string section = read.GetString(5); int state = read.GetInt32(6); knjige knjiga = new knjige(id, naslov, leto, avtor, publisher, section, state); knjige.Add(knjiga); } com.Dispose(); con.Close(); return(knjige); } }
public static List <knjige> SearchKnjig(string src) { SQLiteConnection con = connect(); List <knjige> searchedbooks = new List <knjige>(); con.Open(); src = src.ToLower(); using (SQLiteCommand com = new SQLiteCommand(con)) { com.CommandText = "SELECT b.id,b.title,b.year,b.author,b.publisher,s.name,b.current_state FROM books b INNER JOIN sections s ON b.section_id = s.id WHERE LOWER(b.title) LIKE '%" + src + "%' OR b.year LIKE '%" + src + "%' OR LOWER(b.author) LIKE '%" + src + "%' OR LOWER(b.publisher) LIKE '%" + src + "%' OR LOWER(s.name) LIKE '%" + src + "%' ORDER BY b.id;"; SQLiteDataReader read = com.ExecuteReader(); if (read.HasRows == true) { while (read.Read()) { int id = read.GetInt32(0); string naslov = read.GetString(1); int leto = read.GetInt32(2); string avtor = read.GetString(3); string publisher = read.GetString(4); string section = read.GetString(5); int state = read.GetInt32(6); knjige knjiga = new knjige(id, naslov, leto, avtor, publisher, section, state); searchedbooks.Add(knjiga); } } else { } } con.Dispose(); return(searchedbooks); }
public static knjige PodatkiOknjigi(int idk) { knjige kn; SQLiteConnection con = connect(); con.Open(); using (SQLiteCommand com = new SQLiteCommand(con)) { com.CommandText = "SELECT k.id,k.title,k.shop,k.notes,s.name,k.year,k.author,k.publisher,k.lost,k.current_state FROM books k INNER JOIN sections s ON s.id = k.section_id WHERE k.id = " + idk + ";"; SQLiteDataReader read = com.ExecuteReader(); read.Read(); int it = read.GetInt32(0); string naslov = read.GetString(1); int shop = read.GetInt32(2); string opombe = read.GetString(3); string section = read.GetString(4); int leto = read.GetInt32(5); string avtor = read.GetString(6); string publisher = read.GetString(7); int lost = read.GetInt32(8); int current_state = read.GetInt32(9); string nacinpridobitve = ""; switch (shop) { case 0: nacinpridobitve = "Nakup"; break; case 1: nacinpridobitve = "Darilo"; break; case 2: nacinpridobitve = "Ostalo"; break; } int st = 0; kn = new knjige(current_state, nacinpridobitve, opombe, lost, it, naslov, leto, avtor, publisher, section, st); com.Dispose(); con.Close(); } return(kn); }
private void dodajKnjigoButton_Click(object sender, EventArgs e) { string naslovKnjige = naslovKnjigeTextBox.Text; string nacinPridobitve = pridobitevComboBox.SelectedIndex.ToString(); string oddelek = oddelekComboBox.SelectedItem.ToString(); int leto = Convert.ToInt32(letoTextbox.Text); string avtor = avtorTextBox.Text; string zalozba = zalozbaBox.Text; string opombe = opombeTextBox.Text; knjige knjiga = new knjige(naslovKnjige, nacinPridobitve, oddelek, leto, avtor, zalozba, opombe); baza.DodajKnjigo(knjiga.Naslov, knjiga.Shop, knjiga.Section, knjiga.Leto, knjiga.Avtor, knjiga.Zalozba, knjiga.Opomba); MessageBox.Show("Knjiga uspešno dodana!"); polnjenje(); }
private void polnjenje() { knjige kn = baza.PodatkiOknjigi(id); invtext.Text = kn.inventarna_st.ToString(); nastext.Text = kn.Naslov; avtext.Text = kn.Avtor; zatext.Text = kn.Zalozba; letext.Text = kn.Leto.ToString(); odtext.Text = kn.Section; if (kn.Lost == 1) { ja_izgubljena.Checked = true; } else { ne_izgubljena.Checked = true; } nacintext.Text = kn.Shop; optext.Text = kn.notes; string stat = ""; if (kn.Status == 0) { stat = "Izposojeno"; vrnibutton.Visible = true; spremeniStatusButton.Visible = false; } else if (kn.Status == 1) { stat = "Prosto"; vrnibutton.Visible = false; spremeniStatusButton.Visible = true; } statlabel.Text = statlabel.Text + " " + stat; }