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 signupapp
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string source = "Data source =LAPTOP-O31U25L6\\SQLEXPRESS;Initial
Catalog=tp1;Integrated Security=True;";
//Part1
SqlConnection cn = new SqlConnection(source);
cn.Open();
//string stateopen = cn.State.ToString();
//MessageBox test pour tester l'ouverture de la base de donnees:
//MessageBox.Show(stateopen);
//cn.Close();
//string stateclose = cn.State.ToString();
//MessageBox test pour tester la fermeture de la base de donnees:
//MessageBox.Show(stateclose);
//Part2
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
//Users c'est le nom de tableau dans la base de donnees tp1
cmd.CommandText = "select * from Users";
//cmd.ExecuteNonQuery();
//cmd.ExecuteScalar();
SqlDataReader reader = cmd.ExecuteReader();
//While loop pour boucler dans la base de donnees et recuperer les
valeur dans la table Users
while (reader.Read())
{
//MessageBox.Show(reader["UserId"].ToString());
// MessageBox.Show(reader[2].ToString());
//part3
dataGridView1.Rows.Add(reader[0].ToString(), reader[1].ToString(),
reader[2].ToString(), reader[3].ToString(), reader[4].ToString());
}
reader.Close();
cn.Close();
}
//
// Button ajouter un user
//
private void btnAjouter_Click(object sender, EventArgs e)
{
//-1 ouvrir la connexion avec la base de donnees
string source = "Data source =LAPTOP-O31U25L6\\SQLEXPRESS;Initial
Catalog=tp1;Integrated Security=True;";
SqlConnection cn = new SqlConnection(source);
cn.Open();
//ecrire la requete de l'ajoute: on utilise les parametres
(@login,@password...) pour plus de securite.
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into Users
values(@userid,@nom,@prenom,@password,@creationdate)";
//Remplacer les parametres par ces valeurs dans la zone textBox.
cmd.Parameters.Add(new SqlParameter("@userid", textBox1.Text));
cmd.Parameters.Add(new SqlParameter("@nom", textBox2.Text));
cmd.Parameters.Add(new SqlParameter("@prenom", textBox3.Text));
cmd.Parameters.Add(new SqlParameter("@password", textBox4.Text));
cmd.Parameters.Add(new SqlParameter("@creationdate",
dateTimePicker1.Value));
//a l'execution de la requete on utilise ExecuteNonQuery car il n'y a
pas de valeur de retour
int nbrajout = cmd.ExecuteNonQuery();
if (nbrajout > 0)
{
//si un user est ajouter on affiche un message
MessageBox.Show(nbrajout + " element a ete bien ajouter");
}
//ajouter un ligne dans la dataGrid
DataGridViewRow newrow = new DataGridViewRow();
newrow.CreateCells(dataGridView1, textBox1.Text, textBox2.Text,
textBox3.Text, textBox4.Text, dateTimePicker1.Value);
dataGridView1.Rows.Add(newrow);
dataGridView1.Refresh();
cn.Close();
}
//
// Button Modifier un enregistrement
//
private void btnModifier_Click(object sender, EventArgs e)
{
//verifier la selection d'une ligne
if (dataGridView1.SelectedRows.Count > 0)
{
//ouvrir la connexion avec la base de donnees
string source = "Data source =LAPTOP-O31U25L6\\SQLEXPRESS;Initial
Catalog=tp1;Integrated Security=True;";
SqlConnection cn = new SqlConnection(source);
cn.Open();
// ecrire la requete de la mise a jour des donnees
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "update users set
nom=@nom,prenom=@prenom,password=@password,creationdate=@creationdate where
userid=@userid";
//remplacer le parametre par ca value de textzone
cmd.Parameters.Add(new SqlParameter("@userid", textBox1.Text));
cmd.Parameters.Add(new SqlParameter("@nom", textBox2.Text));
cmd.Parameters.Add(new SqlParameter("@prenom", textBox3.Text));
cmd.Parameters.Add(new SqlParameter("@password", textBox4.Text));
cmd.Parameters.Add(new SqlParameter("@creationdate",
dateTimePicker1.Value));
//a l'execution de la requete on utilise ExecuteNonQuery car il
n'y a pas de valeur de retour
int nbrupdate = cmd.ExecuteNonQuery();
//on verifie si la ligne est modifiee
if (nbrupdate > 0)
{
//on affiche un message
MessageBox.Show(nbrupdate + "element(s) a ete bien modifie");
}
// Remplacer les column de la table par les nouveau valeurs
int rowIndex = dataGridView1.SelectedRows[0].Index;
//dataGridView1.Rows[rowIndex].Cells["users"].Value =
textBox1.Text;
dataGridView1.Rows[rowIndex].Cells["nom"].Value = textBox2.Text;
dataGridView1.Rows[rowIndex].Cells["prenom"].Value= textBox3.Text;
dataGridView1.Rows[rowIndex].Cells["password"].Value =
textBox4.Text;
dataGridView1.Rows[rowIndex].Cells["creationdate"].Value =
dateTimePicker1.Value;
dataGridView1.Refresh();
}
else
{
MessageBox.Show("Vous devez choisir un ligne!");
}
}
// recuperer les valeurs de la ligne selectione depuis le datagrid et
lafficher dans le textzone
private void dataGridView1_CellClick(object sender,
DataGridViewCellEventArgs e)
{
//string userid =
dataGridView1.SelectedRows[0].Cells["userid"].Value.ToString();
//string nom =
dataGridView1.SelectedRows[0].Cells["nom"].Value.ToString();
//string prenom =
dataGridView1.SelectedRows[0].Cells["prenom"].Value.ToString();
//string password =
dataGridView1.SelectedRows[0].Cells["password"].Value.ToString();
//string creationdate =
dataGridView1.SelectedRows[0].Cells["creationdate"].Value.ToString();
//textBox1.Enabled = false;
//textBox1.Text = userid;
//textBox2.Text = nom;
//textBox3.Text = prenom;
//textBox4.Text = password;
//dateTimePicker1.Text = creationdate;
if (e.RowIndex >= 0)
{
DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
textBox1.Enabled = false;
textBox1.Text = row.Cells["userid"].Value.ToString();
textBox2.Text = row.Cells["nom"].Value.ToString();
textBox3.Text = row.Cells["prenom"].Value.ToString();
textBox4.Text = row.Cells["password"].Value.ToString();
dateTimePicker1.Text = row.Cells["creationdate"].Value.ToString();
dataGridView1.Refresh();
private void btnClear_Click(object sender, EventArgs e)
{
textBox1.Enabled = true;
textBox1.Text = string.Empty;
textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
textBox4.Text = string.Empty;
textBox5.Text = string.Empty;
}
private void btnQuitter_Click(object sender, EventArgs e)
{
this.Close();
//Application.Exit();
}
private void btnSupprimer_Click(object sender, EventArgs e)
{
//verifier la selection d'un ligne
if (dataGridView1.SelectedRows.Count > 0)
{
DialogResult result = MessageBox.Show("Est ce que vous etes sur de
supprimer cette ligne", "Comfirmation de suppression", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
//ouvrir la cnx avec la bdd
string source = "Data source =LAPTOP-O31U25L6\\
SQLEXPRESS;Initial Catalog=tp1;Integrated Security=True;";
SqlConnection cn = new SqlConnection(source);
cn.Open();
//ecrice la requete de la suppression on utilise les parametres
@userid plus securise
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "delete from users where userid =@userid";
//remplacer le parametre par ca value de textezone
cmd.Parameters.Add(new SqlParameter("@userid", textBox1.Text));
//execution de la requete on utilise ExecuteNonQuery car n'a
pas de valeur returned
int nbrdeleted = cmd.ExecuteNonQuery();
// on verifie si la ligne a ete bien supprimer
if (nbrdeleted > 0)
{
//on affiche un message
MessageBox.Show(nbrdeleted + " a ete bien supprimer");
}
//supprimer la ligne de la datagrid
int rowIndex = dataGridView1.SelectedRows[0].Index;
dataGridView1.Rows.RemoveAt(rowIndex);
}
}
private void btnRechercher_Click(object sender, EventArgs e)
{
string source = "Data source =LAPTOP-O31U25L6\\SQLEXPRESS;Initial
Catalog=tp1;Integrated Security=True;";
SqlConnection cn = new SqlConnection(source);
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "select * from users where prenom like
'%'+@prenom+'%'";
cmd.Parameters.Add(new SqlParameter("@prenom", textBox5.Text));
SqlDataReader reader = cmd.ExecuteReader();
dataGridView1.Rows.Clear();
while (reader.Read())
{
// MessageBox.Show(reader["login"].ToString());
dataGridView1.Rows.Add(reader["userid"], reader[1], reader[2],
reader[3], reader[4]);
}
reader.Close();
cn.Close();
}
}
}
