public static Control showStudents() { List <SomerenModel.Student> sl = new List <SomerenModel.Student>(); //SomerenDB. SomerenModel.Student s1 = new SomerenModel.Student(); s1.setName("Henk"); SomerenModel.Student s2 = new SomerenModel.Student(); s2.setName("Piet"); SomerenModel.Student s3 = new SomerenModel.Student(); s3.setName("Amber"); sl.Add(s1); sl.Add(s2); sl.Add(s3); int aantal = sl.Count(); ListView c = new ListView(); c.Height = 1000; ListViewItem li = new ListViewItem(); li.SubItems.Add(s1.getName()); li.SubItems.Add(s1.getId().ToString()); c.Items.Add(s1.getName()); c.Items.Add(s2.getName()); c.Items.Add(s3.getName()); c.Items.Add(li); return(c); }
/* // replace this with the connection string you received from Thijs Otter * string host = "den1.mssql5.gear.host"; * string db = "projectdbgroupb2"; * string user = "******"; * string password = "******"; * //string port = "3306"; * * try * { * SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); * builder.DataSource = host; * builder.UserID = user; * builder.Password = password; * builder.InitialCatalog = db; * * SqlConnection connection = new SqlConnection(builder.ConnectionString); * * connection.Open(); * return connection; * * } * catch (SqlException e) * { * SqlConnection connection = null; * Console.WriteLine(e.ToString()); * return connection; * } * } * * private static void sluitConnectieDB(SqlConnection connection) * { * connection.Close(); * } */ public List <SomerenModel.Student> DB_getStudents() { // SqlConnection connection = openConnectionDB(); SqlConnection connection = new SqlConnection(connString); List <SomerenModel.Student> studenten_lijst = new List <SomerenModel.Student>(); connection.Open(); StringBuilder sb = new StringBuilder(); // write your query here to ensure a list of students is shown sb.Append("#query"); /* EXAMPLE QUERY */ //sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName "); //sb.Append("FROM [SalesLT].[ProductCategory] pc "); //sb.Append("JOIN [SalesLT].[Product] p "); //sb.Append("ON pc.productcategoryid = p.productcategoryid;"); /* */ String sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { SomerenModel.Student student = new SomerenModel.Student(); studenten_lijst.Add(student); } return(studenten_lijst); }
public List <SomerenModel.Student> DB_gettudents() { SqlConnection connection = openConnectieDB(); List <SomerenModel.Student> studenten_lijst = new List <SomerenModel.Student>(); StringBuilder sb = new StringBuilder(); // schrijf hier een query om te zorgen dat er een lijst met studenten wordt getoond sb.Append("SELECT id,naam "); sb.Append("FROM B8_Student"); /* VOORBEELDQUERY */ //sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName "); //sb.Append("FROM [SalesLT].[ProductCategory] pc "); //sb.Append("JOIN [SalesLT].[Product] p "); //sb.Append("ON pc.productcategoryid = p.productcategoryid;"); /* */ String sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection); command.Prepare(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); string naam = reader.GetString(1); SomerenModel.Student student = new SomerenModel.Student(id, naam); studenten_lijst.Add(student); } sluitConnectieDB(connection); return(studenten_lijst); }
public static Control showStudents() { List <SomerenModel.Student> sl = SomerenDB.DB_gettudents(); ListViewItem li = new ListViewItem(); ListView c = new ListView(); foreach (var item in sl) { SomerenModel.Student Student = new SomerenModel.Student(); Student.setNaam(item.getNaam()); li.SubItems.Add(Student.getNaam()); li.SubItems.Add(Student.getId().ToString()); c.Items.Add(Student.getNaam()); c.Items.Add(li); } int aantal = sl.Count(); c.Height = 1000; return(c); }
//Door Juan public void bestel(SomerenModel.Student student, SomerenModel.DrankVoorraad voorraad) { SqlConnection connection = openConnectieDB(); StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [dbo].[B8_Verkopen] ([student] ,[datum] ,[drankId] ,[aantal])"); sb.Append(" VALUES (@student, @datum, @drankId, @aantal)"); String sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection); SqlParameter studentParam = new SqlParameter("@student", System.Data.SqlDbType.Int); SqlParameter datum = new SqlParameter("@datum", System.Data.SqlDbType.DateTime); SqlParameter drankId = new SqlParameter("@drankId", System.Data.SqlDbType.Int); SqlParameter aantal = new SqlParameter("@aantal", System.Data.SqlDbType.Int); studentParam.Value = student.getId(); datum.Value = DateTime.Now; drankId.Value = voorraad.getId(); aantal.Value = 1; command.Parameters.Add(studentParam); command.Parameters.Add(datum); command.Parameters.Add(drankId); command.Parameters.Add(aantal); command.Prepare(); command.ExecuteNonQuery(); sluitConnectieDB(connection); }
public List <SomerenModel.Student> DB_getstudents() { SqlConnection connection = openConnectieDB(); List <SomerenModel.Student> studenten_lijst = new List <SomerenModel.Student>(); //connection.Open(); StringBuilder sb = new StringBuilder(); // schrijf hier een query om te zorgen dat er een lijst met studenten wordt getoond sb.Append("SELECT student_id,student_voornaam,student_achternaam FROM studenten"); String sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int id = (int)reader["student_id"]; string naam = (string)reader["student_voornaam"]; string achternaam = (string)reader["student_achternaam"]; SomerenModel.Student student = new SomerenModel.Student(); student.setId(id); student.setNaam(naam); student.setAchterNaam(achternaam); studenten_lijst.Add(student); } sluitConnectieDB(connection); return(studenten_lijst); }
public static SomerenModel.Student LeesStudent(SqlDataReader reader) { SomerenModel.Student student = new SomerenModel.Student(); student.Studentnummer = (int)reader["Studentnummer"]; student.Voornaam = (string)reader["Voornaam"]; student.Achternaam = (string)reader["Achternaam"]; student.Taal = (string)reader["Taal"]; student.Kamernummer = (int)reader["Kamernummer"]; return(student); }
private void afrekenenEvent(object sender, EventArgs e) { foreach (ListViewItem sitem in studentenList.SelectedItems) { SomerenModel.Student student = (SomerenModel.Student)sitem.Tag; foreach (ListViewItem vitem in drankenList.SelectedItems) { SomerenModel.DrankVoorraad voorraad = (SomerenModel.DrankVoorraad)vitem.Tag; SomerenDB somerenDB = new SomerenDB(); somerenDB.bestel(student, voorraad); } } }
public static List <SomerenModel.Student> DB_getStudents() { // make a sql connection SqlConnection connection = openConnectionDB(); // make a list to store data from DB into it List <SomerenModel.Student> students_list = new List <SomerenModel.Student>(); // string builder to write query StringBuilder sb = new StringBuilder(); // the sql query sb.Append("SELECT student_id, FirstName,LastName ,room_number_id FROM Students"); // convert the sql query to string String sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); // retrieving data from DB while (reader.Read()) { // store data to the list SomerenModel.Student student = new SomerenModel.Student(); student.setId((int)reader["student_id"]); student.setFirstName((string)reader["FirstName"]); student.setLastName((string)reader["LastName"]); student.setRoomNumber((int)reader["room_number_id"]); students_list.Add(student); } // close the reader and sql connection reader.Close(); connection.Close(); return(students_list); }
public static Control showDocent() { List <SomerenModel.Docent> dl = SomerenDB.DB_GetDocent(); ListViewItem li = new ListViewItem(); ListView c = new ListView(); foreach (var item in dl) { SomerenModel.Student Docent = new SomerenModel.Student(); Docent.setNaam(item.getNaam()); li.SubItems.Add(Docent.getNaam()); li.SubItems.Add(Docent.getId().ToString()); c.Items.Add(Docent.getNaam()); c.Items.Add(li); } int aantal = dl.Count(); c.Height = 1000; return(c); }
public static List <SomerenModel.Student> DB_getStudents(List <SomerenModel.Student> Studenten) { SqlConnection connection = openConnectieDB(); openConnectieDB(); StringBuilder sbquery = new StringBuilder(); sbquery.Append("SELECT Studentnummer, Voornaam, Achternaam, Taal, Kamernummer FROM STUDENT"); String sql = sbquery.ToString(); SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { SomerenModel.Student student = LeesStudent(reader); Studenten.Add(student); } reader.Close(); sluitConnectieDB(connection); return(Studenten); }
public void addList(SomerenModel.Student s) { sl.Add(s); }