using System using System Collections Generic using System ComponentMo

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;

namespace BDEddyCourse2009
{
    public partial class StudentForm : Form
    {
        public string serverString;
        private string strSQL,strConn;
        OleDbConnection cn;
        public StudentForm(string strCon1)
        {
            InitializeComponent();
            strConn = strCon1;
        }

        private void списокСтудентовПоГруппамToolStripMenuItem_Click(object sender, EventArgs e)
        {
            label1.Visible = true;
            comboBox1.Visible = true;
            button1.Visible = true;

            label2.Visible = false;
            comboBox2.Visible = false;
            button2.Visible = false;



            OleDbCommand IDS = new OleDbCommand("SELECT name FROM groups", cn);
            OleDbDataReader rdr = IDS.ExecuteReader();
            while (rdr.Read())
                comboBox1.Items.Add(rdr["name"]);
            rdr.Close();
        }

        private void сравнениеГруппToolStripMenuItem_Click(object sender, EventArgs e)
        {
            label1.Visible = false;
            comboBox1.Visible = false;
            button1.Visible = false;

            label2.Visible = false;
            comboBox2.Visible = false;
            button2.Visible = false;

            dataGridView1.DataSource = null;

            strSQL = "SELECT groups.name AS 'Группа', AVG(marks.value) AS 'Средний Балл' FROM  groups JOIN students ON students.group_id=groups.iid JOIN marks on marks.student_id=students.iid GROUP BY groups.name";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
            DataTable dt1 = new DataTable();
            da.Fill(dt1);
            dataGridView1.DataSource = dt1;
        }

        private void dToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;

            strSQL = "SELECT students.name,students.family,students.parentname FROM students WHERE  students.group_id IN (SELECT groups.iid FROM groups WHERE groups.name= '" + comboBox1.Text + "');";
          
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
            DataTable dt1 = new DataTable();
            da.Fill(dt1);
            dataGridView1.DataSource = dt1;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void оценкиСтудентаToolStripMenuItem_Click(object sender, EventArgs e)
        {
            label1.Visible = false;
            comboBox1.Visible = false;
            button1.Visible = false;

            label2.Visible = true;
            comboBox2.Visible = true;
            button2.Visible = true;

            dataGridView1.DataSource = null;

            OleDbConnection cn = new OleDbConnection(strConn);
            cn.Open();

            OleDbCommand IDS = new OleDbCommand("SELECT family FROM students", cn);
            OleDbDataReader rdr = IDS.ExecuteReader();
            while (rdr.Read())
                comboBox2.Items.Add(rdr["family"]);
            rdr.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;

            strSQL = "SELECT students.name as Имя,students.family as Фамилия,students.parentname as Отчество,subjects.name as Предмет,marks.value as Оценка FROM students,marks,subjects WHERE  students.iid=marks.student_id AND marks.subject_id=subjects.iid AND students.family='" + comboBox2.Text + "';";

            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
            DataTable dt1 = new DataTable();
            da.Fill(dt1);
            dataGridView1.DataSource = dt1;
        }

        private void helpToolStripMenuItem_Click(object sender, EventArgs e)
        {
            HelpForm HelpForm1 = new HelpForm();
            HelpForm1.Show();
        }

        private void StudentForm_Load(object sender, EventArgs e)
        {
            cn = new OleDbConnection(strConn);
            cn.Open();
            //MICROSOF-2370FB
        }
    }
}