Exercices ASP.NET ? Gestion de notes
Thèmes : Bases de données, SQL, ASP.NET
Niveau : débutant
Durée : 4 / 8 h
En tant que débutant en développement Web (ASP.NET) il faut travailler le maximum possible des exemples avec différentes façons.
Ici on prend l?exemple de gestion d?une école en trois exercices avec une base de données sur SQL Server selon cette structure
Source / Exemple :
Utiliser le nom d?espace suivant
Imports System.Data.SqlClient
Dans l?Evenement session_start du fichier global.asax
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
Dim cn As New SqlClient.SqlConnection
Session("con") = cn
cn.ConnectionString = "server=(local);integrated security=sspi;initial catalog=ecole"
cn.Open()
End Sub
Dans l?Evenement Evenement session_end du fichier global.asax
Sub Session_End(ByVal sender As Object, ByVal e As EventArgs)
CType(Session("con"), SqlConnection).Close()
End Sub
Dans l?Evenement load de la Page de démarrage
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Try
Dim dr As SqlDataReader
Dim cmd As New SqlCommand
cmd.CommandText = "select * from classes"
cmd.Connection = CType(Session("con"), SqlConnection)
dr = cmd.ExecuteReader
While dr.Read
DropDownList1.Items.Add(dr(0) & " : " & dr(1))
DropDownList1.DataBind()
End While
dr.Close()
Catch ex As Exception
Label2.Text = ("erreur :" & ex.Message)
End Try
End If
End Sub
Dans l?Evenement linkbutton1_click
Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
Try
Dim nr As String
nr = DropDownList1.SelectedItem.Value
Dim ch() As String = nr.Split(":")
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
cmd.CommandText = "select * from eleves where id_classe=" & "'" & ch(0) & "'"
cmd.Connection = CType(Session("con"), SqlConnection)
dr = cmd.ExecuteReader
ListBox1.Items.Clear()
While dr.Read
ListBox1.Items.Add(dr(1))
ListBox1.DataBind()
End While
dr.Close()
Label3.Text = ListBox1.Items.Count & " :" & " Etudiants dans la classe " & ch(1)
Catch ex As Exception
Label2.Text = ("erreur: " & ex.Message)
End Try
End Sub
Dans l?Evenement load :
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim cmdc As New SqlCommand
Dim cmdmat As New SqlCommand
cmdc.CommandText = "select * from classes "
cmdmat.CommandText = "select * from matieres "
cmdc.Connection = CType(Session("con"), SqlConnection)
cmdmat.Connection = CType(Session("con"), SqlConnection)
Dim drc As SqlDataReader
drc = cmdc.ExecuteReader
While drc.Read
listeclasses.Items.Add(drc(0) & " : " & drc(1))
End While
drc.Close()
Dim drmat As SqlDataReader
drmat = cmdmat.ExecuteReader
While drmat.Read
listematieres.Items.Add(drmat(0) & " : " & drmat(1))
End While
drmat.Close()
End If
End Sub
Evénement l LinkButton1_Click:
Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
Dim moy As Double = 0
Dim min As Double = 20
Dim max As Double = 0
Dim ecart As Double = 0
Dim sw, sz As Double
Dim etudmax, etudmin As String
'on recupere l'element sélectionne dans le combos
'concernant les classes mais sous forme 1:tsdi3
Dim idcl As String = listeclasses.SelectedItem.Value
'de meme pour les matieres
Dim idmat As String = listematieres.SelectedItem.Value
'il faut decouper la chaine et la mettre dans un tableau
'a l'aide de la methode split
Dim tabcl() As String = idcl.Split(":")
Dim tabmat() As String = idmat.Split(":")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "meseleves"
?meselves est une procedure stockee sur sql server comme suivant:
????????????????????????????procédure stockée????????????
CREATE proc meseleves(@idclasse int,@idmatiere int) as
select eleves.prenom,eleves.nom,notes.note from notes
inner join ( eleves inner join classes on classes.id=eleves.id_classe)
on eleves.id=notes.id_eleve
where notes.id_matiere=@idmatiere and classes.id=@idclasse
GO
??????????????????????????????????????????????????????????
Dim p1 As New SqlParameter("@idclasse", SqlDbType.Int)
Dim p2 As New SqlParameter("@idmatiere", SqlDbType.Int)
p1.Value = tabcl(0)
p2.Value = tabmat(0)
cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
cmd.Connection = CType(Session("con"), SqlConnection)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
ListBox1.Items.Clear()
While dr.Read
ListBox1.Items.Add(dr(0) & " " & dr(1) & " : " & dr(2))
ecart = ecart + dr(2) * dr(2)
If dr(2) > max Then
max = dr(2)
etudmax = dr(1) & " " & dr(0)
End If
If dr(2) <= min Then
min = dr(2)
etudmin = dr(1) & " " & dr(0)
End If
moy = moy + dr(2)
End While
sw = moy * moy
sz = ListBox1.Items.Count * ListBox1.Items.Count
Label7.Text = moy / ListBox1.Items.Count
Label8.Text = ecart / ListBox1.Items.Count - sw / sz
Label9.Text = max & " obtenue par :" & etudmax
Label10.Text = min & " obtenue par :" & etudmin
dr.Close()
End Sub
Dans événement load de la page courante mettre :
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim cmdc As New SqlCommand
Dim cmdmat As New SqlCommand
cmdc.CommandText = "select * from classes "
cmdc.Connection = CType(Session("con"), SqlConnection)
Dim drc As SqlDataReader
drc = cmdc.ExecuteReader
While drc.Read
listeclasses.Items.Add(drc(0) & " : " & drc(1))
End While
drc.Close()
End If
End Sub
Dans l?événement linkboutton (afficher les élèves):
Private Sub LinkButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton2.Click
listeeleves.Items.Clear()
Dim d As String = listeclasses.SelectedItem.Value
Dim sss() As String = d.split(":")
Dim cmd As New SqlCommand
Dim cmdmat As New SqlCommand
cmd.CommandText = "select * from eleves where id_classe=" & sss(0)
cmd.Connection = CType(Session("con"), SqlConnection)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
While dr.Read
listeeleves.Items.Add(dr(0) & " : " & dr(1))
End While
dr.Close()
End Sub
Dans l?événement linkboutton (afficher les notes):
Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
Dim moy As Double = 0
Dim sw, sz As Double
Dim etudmax, etudmin As String
'On recupere l'element selectionne dans le combos
'Concernant les classes mais sous forme 1:tsdi3
Dim idcl As String = listeclasses.SelectedItem.Value
'de meme pour les matieres
Dim idelv As String = listeeleves.SelectedItem.Value
'Il faut decouper la chaine et la mettre dans un tableau
'a l'aide de la methode split
Dim tabcl() As String = idcl.Split(":")
Dim tabelv() As String = idelv.Split(":")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "mesnotes"
???????????ps :mesnotes?????????????????????
CREATE proc mesnotes(@idclasse int,@ideleve int) as
Select matieres.nom,notes.note from matieres inner join (notes inner join eleves on eleves.id=notes.id_eleve)
on matieres.id=notes.id_matiere
where eleves.id=@ideleve and eleves.id_classe=@idclasse
GO
????????????????????????????????????????????????????
Dim p1 As New SqlParameter("@idclasse", SqlDbType.Int)
Dim p2 As New SqlParameter("@ideleve", SqlDbType.Int)
p1.Value = tabcl(0)
p2.Value = tabelv(0)
cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
cmd.Connection = CType(Session("con"), SqlConnection)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
ListBox1.Items.Clear()
While dr.Read
ListBox1.Items.Add(dr(0) & " : " & dr(1))
moy = moy + dr(1)
End While
Label7.Text = moy / ListBox1.Items.Count
dr.Close()
End Sub
Conclusion :
j'attend vos suggestion mes amis les developpeurs et la prochaine..
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.