ADONET

//Classe
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.OleDb;

namespace StudentiVoti
{
    public class DAL
    {
        private OleDbConnection _conn;


        public DAL()
        {
            string cns = BuildConnectionString();
            _conn = new OleDbConnection(cns);
            _conn.Open();
        }

        // dovrebbe essere letto da file di configurazione
        private string BuildConnectionString()
        {
            string dbDirPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

            string cns = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbDirPath + @"\StudentiVoti.accdb";

            return cns;
        }

        public DataTable StudentiElenco()
        {
            DataTable tbl = new DataTable();

            string sql = "SELECT IDStudente, Cognome + ' ' + Nome AS CognomeNome FROM Studenti ORDER BY Cognome,Nome";
            OleDbDataAdapter da = new OleDbDataAdapter(sql, _conn);
            da.Fill(tbl);
            //tbl.Load(datareader cmd.executereader)
            return tbl;
        }
        // cbo.DataSource = tbl
        // cbo.DisplayMember = "CognomeNome" // deve essere nome colonna
        // cbo.ValueMember = "IDStudente" // deve essere nome colonna
        // per leggerlo int IDStud = (int) cbo.SelectedValue


        // es CognomeFiltro="Ros"  => LIKE 'Ros%'   (ricordarsi escape "'")
        // CognomeFiltro.Replace("'","''")
        public DataTable StudentiElenco(string CognomeFiltro)
        {
            DataTable tbl = new DataTable();

            string sql = "SELECT IDStudente, Cognome, Nome, DataNascita FROM Studenti " +
                " WHERE Cognome LIKE ? " +
                " ORDER BY Cognome,Nome";
            OleDbCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("Cognome", CognomeFiltro.Replace("*","%") + "%");

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(tbl);

            return tbl;
        }

        public DataTable MaterieElenco()
        {
            DataTable tbl = new DataTable();

            string sql = "SELECT IDMateria, Nome FROM Materie ORDER BY Nome";
            OleDbDataAdapter da = new OleDbDataAdapter(sql, _conn);
            da.Fill(tbl);
            //tbl.Load(datareader cmd.executereader)
            return tbl;
        }

        // data la foreign key (selezionata via combo, grid, ...) => voti
        public DataTable VotiElenco(int IDStudente)
        {
            DataTable tbl = new DataTable();

            /*
            // attenzione a concatenare le stringhe
            string sql = "SELECT V.IDVoto, M.Nome AS Materia, V.Data, V.Tipo, V.Voto" +
                         " FROM Voti AS V INNER JOIN Materie AS M ON V.IDMateria=M.IDMateria" +
                         " WHERE V.IDStudente=" + IDStudente;
            OleDbDataAdapter da = new OleDbDataAdapter(sql, _conn);
            da.Fill(tbl);
            */
            string sql = "SELECT V.IDVoto, M.Nome AS Materia, V.Data, V.Tipo, V.Voto" +
                                     " FROM Voti AS V INNER JOIN Materie AS M ON V.IDMateria=M.IDMateria" +
                                     " WHERE V.IDStudente=?";
            OleDbCommand cmd = _conn.CreateCommand();
            cmd.CommandText=sql;
            cmd.Parameters.AddWithValue("IDStudente", IDStudente);

            //OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //da.Fill(tbl);
            // oppure
            OleDbDataReader dr = cmd.ExecuteReader();
            tbl.Load(dr);

            return tbl;
        }


        public bool VotoAggiungi(int IDStudente, int IDMateria, DateTime Data, double Voto, string Tipo)
        {
            string sql = "INSERT INTO Voti(IDStudente, IDMateria, Data, Voto, Tipo)" +
                         " VALUES(?,?,?,?,?)";
            OleDbCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("IDStudente", IDStudente);
            cmd.Parameters.AddWithValue("IDMateria", IDMateria);
            cmd.Parameters.AddWithValue("Data", Data).OleDbType=OleDbType.Date;
            cmd.Parameters.AddWithValue("Voto", Voto);
            cmd.Parameters.AddWithValue("Tipo", Tipo);


            int nr;
            nr = cmd.ExecuteNonQuery();
            return (nr > 0);
        }
    }
}

//classe form
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentiVoti
{
    public partial class FormMain : Form
    {
        private DAL _dal;

        public FormMain()
        {
            InitializeComponent();
        }

        private void FormMain_Load(object sender, EventArgs e)
        {
            _dal = new DAL();

            cboStudenti.DataSource = _dal.StudentiElenco();
            cboStudenti.DisplayMember = "CognomeNome";
            cboStudenti.ValueMember = "IDStudente";


            cboStudenteIns.DataSource = _dal.StudentiElenco();
            cboStudenteIns.DisplayMember = "CognomeNome";
            cboStudenteIns.ValueMember = "IDStudente";

            cboMateriaIns.DataSource = _dal.MaterieElenco();
            cboMateriaIns.DisplayMember = "Nome";
            cboMateriaIns.ValueMember = "IDMateria";
        }

        private void btnVisualizza_Click(object sender, EventArgs e)
        {
            int selID;
            selID = (int)cboStudenti.SelectedValue;
            //MessageBox.Show("Studente ID:" + selID);

            dgvVoti.DataSource = _dal.VotiElenco(selID);

            dgvVoti.Columns["IDVoto"].Visible = false;
        }

        private void btnCercaStudente_Click(object sender, EventArgs e)
        {
            dgvStudenti.DataSource = _dal.StudentiElenco(txtCognomeFiltro.Text);
            //dgvStudenti.Columns["IDStudente"].Visible = false;
        }

        private void txtCognomeFiltro_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == '\r')
                btnCercaStudente_Click(sender, e);
        }

        private void btnVisualizzaVoti_Click(object sender, EventArgs e)
        {
            int selID = 0;
            if (dgvStudenti.CurrentRow != null)
                selID = (int)(dgvStudenti.CurrentRow.DataBoundItem as DataRowView)["IDStudente"];

            dgvVotiBis.DataSource = _dal.VotiElenco(selID);
        }

        private void dgvStudenti_CurrentCellChanged(object sender, EventArgs e)
        {
            btnVisualizzaVoti_Click(sender, e);
        }

        private void btnAggiungiVoto_Click(object sender, EventArgs e)
        {
            int IDStudente, IDMateria;
            double Voto;
            DateTime Data;
            string TipoVoto;

            IDStudente = (int)cboStudenteIns.SelectedValue;
            IDMateria = (int)cboMateriaIns.SelectedValue;
            //Voto = double.Parse(txtVotoIns.Text);
            if(!(double.TryParse(txtVotoIns.Text, out Voto) && Voto>=1 && Voto<=10))
            {
                MessageBox.Show("Errore formato voto", "Errore", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            Data = dtpDataIns.Value;

            TipoVoto = "";
            if (rbTipoVotoS.Checked) TipoVoto = "S";
            else if (rbTipoVotoO.Checked) TipoVoto = "O";
            else if (rbTipoVotoP.Checked) TipoVoto = "P";
            else if (rbTipoVotoG.Checked) TipoVoto = "G";

            bool ok = _dal.VotoAggiungi(IDStudente, IDMateria, Data, Voto, TipoVoto);
            if (ok)
                MessageBox.Show("Voto aggiunto");
            else
                MessageBox.Show("Errore");
        }
    }
}