Correction de L' Examen de fin de formation 2013 TSDI Pratique Variante 1-1

V1-1-----------------------

2. alter table Session add constraint checkdate check(datediff(day,dateDebutSessionstage,datefinSessionStage) > 0)

3.
create trigger tr1 on
instead of insert,update
as
declare @age int
set @age = (select age from inserted)
if @age < 18
print 'age inferieur a 18 ans'
else
insert into professeur select * from insterted

4.

create proc ps1(@NumSession int , @countEnsigne int output)
as
begin
set @countEnsigne = (select count(*) from Inscrit where numerodesession = @NumSession)
end

5.

create trigger tr2 on Session
instead of insert
as
declare @coutnParticipant int, @numSession int
set @numSession = (select numerodesession from insterted)
Exec ps1 @numSession , @coutnParticipant
if @coutnParticipant > 20
print 'vous avez déja 20 participant dans cette session'
else
insert into Session select * from insterted

7.
create function fn1 returns table
as
return
select p.* from Participant p inner join Inscrit ins on p.numeroparticipant = ins.numeroparticipant inner join SEssion s on s.numeroSession = ins.numeroSession inner join Stage st on st.numerostage = s.numerostage group by st.numerostage having count(*) >= 2


<asp:RangeValidator
ControlToValidate="TextBox1"
MinimumValue="100"
Type="Integer"
EnableClientScript="false"
Text="il faut entré un numeroparticipant séprieure a 100"
runat="server" />

//hada validation date de naissense
<asp:RegularExpressionValidator
ControlToValidate="textbox2"
ValidationExpression="([0-9]{2})\-([0-9]{2})\-([0-9]{4})"
EnableClientScript="false"
ErrorMessage="The zip code must be 5 numeric digits!"
runat="server" />

<asp:RequiredFieldValidator
ControlToValidate="textbox1"
Text="remplireles champs"
runat="server" />

<asp:RequiredFieldValidator
ControlToValidate="textbox2"
Text="remplireles champs"
runat="server" />

4.

<asp:GridView Runat="server" ID="GridView1"
AutoGenerateSelectButton="true" />
<asp:GridView Runat="server" ID="GridView2" />

codebehind

public int idParticipant = 0;
public GridViewRow row = null;

void GridView1_SelectedIndexChanged(Object sender, EventArgs e)
{

// Get the currently selected row using the SelectedRow property.

row = GridView1.SelectedRow;

// Display the company name from the selected row.

// In this example, the third column (index 2) contains

// the company name.

idParticipant = Convert.toInt32(row.Cells[0].Text);
sqlCommand cmd = new SqlCommand("select * from participant whre numerostaPariticinapt = " + idParticipant.toString() , cnx)
SqlDataReader dr = cmd.ExecuteReader()
dr.read()
textbox1.text = dr[0].toString();
textbox2.text = dr[0].toString();
dr.close();

}

code button supprimer
a)
void Supprimer_Click(Object sender, EventArgs e){
SqlCommand cmd = new SqlCommand("delete from Participant where numerostaparticipant = " + idParticipant.toString() , cnx)
cmd.ExecuteNonQuery();
}
b)
void Modifier_Click(Object sender, EventArgs e){

SqlCommand cmd2 = new SqlComand("update participant set nomparticipant = '"+textbox1.Text+"' wehre numerostaParticipant = "+ idParticipant.toString(), cnx);
cmd2.ExecuteQuery();
}
d)
void Selectioner_Click(Object sender, EventArgs e){
SqlCommand cmd = new SqlCommand("select * from Sessision where numerosession in(select numerosession from Inscrit where numeroparticipant = " + idParticipant.toString(),cnx);
DataTable t = new DataTable();
SqlDataReader dr = cmdExecuteReader();
t.Load(dr);
GridView2.DataSource = t;
GridView2.DataBind();
}