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