controle

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;
using System.Data.SqlClient;
namespace controle
{
    public partial class Form1 : Form
    {public SqlCommand cmd=new SqlCommand();
        public SqlConnection cnx;
    public SqlDataAdapter da =new SqlDataAdapter();
            DataSet ds =new DataSet();
        public SqlDataReader dr;
        public SqlDataReader dr2;
        public SqlDataReader dr4;
        string rsql;
        int id;
        public Form1()
        {
            InitializeComponent();
        }
        //public int rechAdh()
        //{
        //    int a;
        //    cnx.Open();
        //    SqlCommand cmd = new SqlCommand("select max(numAdh) from adherent", cnx);
        //    a = cmd.ExecuteNonQuery();
        //    cnx.Close();
        //    return a;
        //}

        public bool exist()
        {
            bool e = false;
            cnx.Open();
            SqlCommand cmd = new SqlCommand("select * from adherent where nomAdh='"+nom.Text+"'",cnx);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows == true)
            {
                e = true;
            }
            dr.Close();
            cnx.Close();
            return e;



        }


        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                cnx = new SqlConnection("integrated security=true; initial catalog=controle;data source=(local);user=administrateur; password=123456 ");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            cnx.Open();
            SqlCommand cmd = new SqlCommand("select * from adherent");
            cmd.Connection = cnx;
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                dataGridView1.Rows.Add(dr[0],dr[1],dr[2],dr[3],dr[4],dr[5]);

            }
            dr.Close();
            cnx.Close();




            cboActiv.Items.Clear();
           da=new SqlDataAdapter("select codeActiv,activite from activite",cnx);
            da.Fill(ds,"activite");
            cboActiv.DataSource=ds.Tables["activite"];
            cboActiv.ValueMember = ds.Tables["activite"].Columns[0].ToString();
            cboActiv.DisplayMember = ds.Tables["activite"].Columns[1].ToString();

            cboLieu.Items.Clear();
            da = new SqlDataAdapter("select codeLieu,lieu from lieu", cnx);
            da.Fill(ds, "lieu");
            cboLieu.DataSource = ds.Tables["lieu"];
            cboLieu.ValueMember = ds.Tables["lieu"].Columns[0].ToString();
            cboLieu.DisplayMember = ds.Tables["lieu"].Columns[1].ToString();

}

        private void button5_Click(object sender, EventArgs e)
        {
            nom.Text = "";
            prenom.Text = "";
            dateNais.Text = "";
            adress.Text = "";
            tel.Text = "";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (nom.Text == "" || prenom.Text == "" || dateNais.Text == "" || adress.Text == "" || tel.Text == "")
            {
                MessageBox.Show("remplir tous les champs");

            }
            else
            {
                if (exist() == true) { MessageBox.Show("existe deja"); }
                else
                {
                    cnx.Open();

                    rsql = "insert into adherent (nomAdh,prenomAdh,datNaisAdh,adrAdh,numTelAdh,codeLieu) values(@nomAdh,@prenomAdh,@datNaisAdh,@adrAdh,@numTelAdh,@codeLieu)";
                    SqlCommand cmd = new SqlCommand(rsql, cnx);
                    cmd.Parameters.AddWithValue("@nomAdh", nom.Text);
                    cmd.Parameters.AddWithValue("@prenomAdh", prenom.Text);
                    cmd.Parameters.AddWithValue("@datNaisAdh", Convert.ToDateTime(dateNais.Text));
                    cmd.Parameters.AddWithValue("@adrAdh", adress.Text);
                    cmd.Parameters.AddWithValue("@numTelAdh", tel.Text);
                    cmd.Parameters.AddWithValue("@codeLieu", cboLieu.SelectedValue);
                    cmd.ExecuteNonQuery();

                    rsql = "select max( numAdh) from adherent";
                    cmd = new SqlCommand(rsql, cnx);
                    dr4 = cmd.ExecuteReader();
                    if (dr4.Read())
                    {
                        id = Convert.ToInt32(dr4[0]);
                    }
                    dr4.Close();





                    rsql = "insert into pratique (numAdh,codeActiv) values (@numAdh,@codeActiv)";
                    cmd = new SqlCommand(rsql, cnx);
                    cmd.Parameters.AddWithValue("@numAdh", id);
                    cmd.Parameters.AddWithValue("@codeActiv",cboActiv.SelectedValue);

                    cmd.ExecuteNonQuery();





                    dataGridView1.Rows.Clear();
                     cmd = new SqlCommand("select * from adherent");
                    cmd.Connection = cnx;
                    SqlDataReader dr;
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        dataGridView1.Rows.Add(dr[0], dr[1], dr[2], dr[3], dr[4], dr[5]);

                    }
                    dr.Close();
                    cnx.Close();

                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (nom.Text == "" || prenom.Text == "" || dateNais.Text == "" || adress.Text == "" || tel.Text == "")
            {
                MessageBox.Show("remplir tous les champs");

            }
            else
            {
                if (exist() == false) { MessageBox.Show("adherent introuvable"); }
                else
                {
                    cnx.Open();
                   SqlCommand cmd = new SqlCommand("update adherent set prenomAdh=@prenomAdh,datNaisAdh=@datNaisAdh,adrAdh=@adrAdh,numTelAdh=@numTelAdh where nomAdh=@nomAdh", cnx);

                    cmd.Parameters.AddWithValue("@nomAdh", nom.Text);
                    cmd.Parameters.AddWithValue("@prenomAdh", prenom.Text);
                    cmd.Parameters.AddWithValue("@datNaisAdh", dateNais.Value.ToString());
                    cmd.Parameters.AddWithValue("@adrAdh", adress.Text);
                    cmd.Parameters.AddWithValue("@numTelAdh", tel.Text);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("bien modifier");
                    cnx.Close();
                    dataGridView1.Rows.Clear();
                    SqlCommand cmd2 = new SqlCommand("select * from adherent");
                    cnx.Open();
                    cmd2.Connection = cnx;
                    dr2 = cmd2.ExecuteReader();
                    while (dr2.Read())
                    {
                        dataGridView1.Rows.Add(Convert.ToInt16(dr2[0]), Convert.ToString(dr2[1]), Convert.ToString(dr2[2]), Convert.ToDateTime(dr2[3]), Convert.ToString(dr2[4]), Convert.ToString(dr2[5]));

                    }
                    dr2.Close();
                    cnx.Close();


                }
            }
        }



        private void supprimerToolStripMenuItem_Click(object sender, EventArgs e)
        {
            cmd.CommandText = "delete pratique where numAdh=" + dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[0].Value + "";
            cnx.Open();
            cmd.Connection = cnx;
            cmd.ExecuteNonQuery();
            cnx.Close();

            cmd.CommandText ="delete adherent where numAdh=" + dataGridView1.Rows[dataGridView1.CurrentRow.Index ].Cells[0].Value + "";
       cnx.Open();
       cmd.Connection = cnx;
      cmd.ExecuteNonQuery();
      cnx.Close();
      dataGridView1.Rows.RemoveAt(dataGridView1.CurrentRow.Index);
        }

        private void tel_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch = e.KeyChar;
            if(!char.IsDigit(ch) && ch !=8)
            {
                e.Handled = true;
            }
        }

        private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
        {
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            int k;
            k = e.RowIndex;
           nom.Text = dataGridView1.Rows[k].Cells[1].Value.ToString();
            prenom.Text = dataGridView1.Rows[k].Cells[2].Value.ToString();
            dateNais.Text = dataGridView1.Rows[k].Cells[3].Value.ToString();
            adress.Text = dataGridView1.Rows[k].Cells[4].Value.ToString();
            tel.Text = dataGridView1.Rows[k].Cells[5].Value.ToString();

        }

        }

    }