Databases en datagrid
1. Inleiding
2. Gegevens toevoegen
3. Gegevens tonen
4. Gegevens verwijderen
5. Gegevens updaten
top
1. Inleiding
Allereerst: deze tutorial is niet voor beginners. Lees eerst alle andere tutorials rustig door en surf desnoods wat op het internet als je iets niet begrijpt.
Je moet alle vorige tutorials, buiten die van calendar, heel goed begrijpen zonder vragen.
Ook enige kennis van SQL query's (INSERT, DELETE en UPDATE) is vereist.
Een basiskennis van foutopvang is ook gewenst omdat dit wordt toegepast voor de query's.
Je moet weten wat een object is en hoe deze werkt in asp.net en je moet weten hoe 'webcontrols' werken en wat ze doen.
Deze tutorial zal gebruikmaken van een acces database (die gemakelijk te vervangen is door een mysql of sql-server database).
De database vind je hier: Klik
We gaan gebruikmaken van de webcontrol 'datagrid' om gegevens toe te voegen aan een database, deze weer te geven, de mogelijkheid geven om deze te bewerken en om af te sluiten de mogelijkheid om deze te verwijderen.
Een hele boterham dus maar gelukkig heeft asp.net enkele voorgedefiniëerde methodes die dit allemaal toelaten met enkele eenvoudige commando's.
Het eindresultaat kan je hier downloaden (doel opslaan als): Klik
We gaan een tabel gebruiken waar iemand leerlingen kan invullen voor een reisje naar berlijn. De naam, voornaam, telefoonnummer (telnr) en adres moeten kunnen ingevuld worden.
Het kan zijn dat je server een obdc verbinding vereist om te communiceren met je database. Daarom moet je de database toevoegen aan je OBDC lijst.
Dit doe je door naar start->configuratiescherm->systeembeheer->gegevensbronnen (OBDC) te gaan en op toevoegen te klikken.
Vervolgens krijg je een lijstje met verschillende types databases. Hierin staat "Microsoft Acces Driver (.mdb)" wat we nodig hebben voor MS Acces.
Stel je wil met een mysql database werken dan download je de OBDC driver voor mysql, installeer je deze en selecteer je "MySQL OBDC 3.51 Driver" (de 3.51 veranderd dan in jou versie nummer).
top
2. Gegevens toevoegen
We beginnen met een formuliertje om de gegevens te kunnen invullen.
Hier zou normaal gezien geen uitleg bij moeten als de vorige tutorials duidelijk waren.
<table>
<tbody>
<tr>
<td style="FONT: 10pt verdana" bgcolor="#cccfff" colspan="2">
Toevoegen:</td>
</tr>
<tr>
<td nowrap="nowrap">
id:
</td>
<td>
<asp:textbox id="berlijn_id" runat="server" /></td>
</tr>
<tr>
<td nowrap="nowrap">
Naam:
</td>
<td>
<asp:textbox id="berlijn_naam" runat="server" /></td>
</tr>
<tr>
<td nowrap="nowrap">
Voornaam:
</td>
<td>
<asp:textbox id="berlijn_voornaam" runat="server" /></td>
</tr>
<tr>
<td nowrap="nowrap">
Adres:
</td>
<td>
<asp:textbox id="berlijn_adres" runat="server" /></td>
</tr>
<tr>
<td nowrap="nowrap">
Telefoon nummer:
</td>
<td>
<asp:textbox id="berlijn_telnr" runat="server" /></td>
</tr>
<tr>
<td>
</td>
<td style="PADDING-TOP: 15px">
<input type="submit" value="Toevoegen" runat="server" onserverclick="add_data" />
</td>
</tr>
</tbody>
</table>
|
Als alles goed is zal dit formulier bij het submitten de methode 'add_data' aanroepen.
Deze methode zal dus gegevens toevoegen aan de database.
Hiervoor moeten we eerst met de database verbinden (stap 1).
Vervolgens zullen we de 'sub' aanmaken (stap 2).
We maken een standaard query aan (stap 3).
Deze vullen we (op een speciale manier) met de data (stap 4).
We voeren de query uit (stap 5).
Dim MyConnection As New OleDbConnection
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:aspNETdatagrid est_db.mdb"
' (stap 1) '
Sub add_data(Sender As Object, E As EventArgs)
' (stap 2) '
Dim MyConnection As New OleDbConnection _
(connString)
Dim sql_commando As OleDBCommand
Dim InsertCmd As String = "insert into berlijn (id, naam, voornaam, adres, telnr) values
(@id, @naam, @voornaam, @adres, @telrnr)"
' (stap 3) '
sql_commando = New OleDBCommand(InsertCmd, MyConnection)
sql_commando.Parameters.Add(New OleDBParameter("@id", OleDBType.Numeric, 11))
sql_commando.Parameters.Add(New OleDBParameter("@naam", OleDBType.VarwChar, 40))
sql_commando.Parameters.Add(New OleDBParameter("@voornaam", OleDBType.VarwChar, 40))
sql_commando.Parameters.Add(New OleDBParameter("@adres", OleDBType.VarwChar, 40))
sql_commando.Parameters.Add(New OleDBParameter("@telnr", OleDBType.VarwChar, 40))
sql_commando.Parameters("@id").Value = Server.HtmlEncode(berlijn_id.Text)
sql_commando.Parameters("@naam").Value = Server.HtmlEncode(berlijn_naam.Text)
sql_commando.Parameters("@voornaam").Value = Server.HtmlEncode(berlijn_voornaam.Text)
sql_commando.Parameters("@adres").Value = Server.HtmlEncode(berlijn_adres.Text)
sql_commando.Parameters("@telnr").Value = Server.HtmlEncode(berlijn_telnr.Text)
' (stap 4) '
sql_commando.Connection.Open()
Try
sql_commando.ExecuteNonQuery()
' (stap 5) '
Message.InnerHtml = "<strong>Toegevoegd!</strong><br>"
Catch Exp As OleDBException
Message.InnerHtml = "Probleem"
Message.Style("color") = "red"
End Try
sql_commando.Connection.Close()
BindGrid()
End Sub
|
Alle stappen, buiten stap 1 en 4, moeten duidelijk zijn.
We nemen stap 4 eens onder de loep.
Het is waarschijnlijk opgevallen dat in stap 3 een query werd aangemaakt. Dit was echter geen normale query.
In plaats van waardes mee te geven werd er gewerkt met @naam, @voornaam, ...
Dit betekend kortweg: 'dit moet nog opgevuld worden met een waarde'.
In stap 4 gaan we alle mogelijkheden af (@id, @naam, @voornaam, @adres en @telnr).
Deze vervangen we een voor een met hun overeenkomstige waarde.
"sql_commando.Parameters("@id").Value = Server.HtmlEncode(berlijn_id.Text) "
Bovenstaande regel zal dus over in de query (sql_commando) @id vervangen door de waarde die het veld 'berlijn_id' heeft.
Het is een ingewikkelde manier (en ik werk er liever ook niet mee) maar het kan handig zijn bij variabelen in query's.
Als alles goed is wordt er nu data toegevoegd aan de tabel.
De database verbinding begint met een connectie string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:aspNETdatagrid est_db.mdb"
Hier moet je enkel één iets aanpassen, namelijk de 'Data Source'. Dit moete leiden naar het juiste path waar de database te vinden is.
Voor de rest wordt overal automatisch verbinding gemaakt bij het aanroepen van deze connectie string.
top
3. Gegevens tonen
Nu dat we gegevens kunnen toevoegen willen we deze ook kunnen tonen. Daarom gaan we met 'databinding' werken.
Dit betekend kortweg dat je een webcontrol 'datagrid' maakt en deze vult met de elementen die zich in de tabel bevinden.
We maken eerst de webcontrol aan.
<ASP:DataGrid id="berlijn_datagrid" runat="server"
DataKeyField="id"
OnItemDataBound="berlijn_datagrid_ItemDataBound"
/ >
|
In het werkend voorbeeld dat helemaal bovenaan is gepost heeft dit veel meer attributen. Dit heeft twee redenenen:
- er komen er straks nog bij
- de rest is opmaak
Je maakt hier een datafield aan met het id 'berlijn_datagrid'. Deze geven we als key 'id' mee. Dit is een soort van 'primary key' van de datagrid (zodat we straks gemakkelijk kunnen updaten en verwijderen (verwijzen naar het id)).
Als er databinding moet optreden (OnItemDataBound) moet de sub 'berlijn_datagrid_ItemDataBound' worden aangeroepen.
We gaan deze, samen met de methode om de grid te vullen, eens bekijken.
Sub berlijn_datagrid_ItemDataBound(Sender As Object, E As DataGridItemEventArgs)
If (e.Item.ItemType = ListItemType.EditItem) Then
Dim i As Integer
For i = 0 To e.Item.Controls.Count-1
If (e.Item.Controls(i).Controls(0).GetType().ToString()
= "System.Web.UI.WebControls.TextBox") Then
Dim tb As TextBox
tb = e.Item.Controls(i).Controls(0)
tb.Text = Server.HtmlDecode(tb.Text)
End If
Next
End If
End Sub
|
Deze methode zal er straks voor zorgen dat als er op 'edit' wordt gedrukt dat de labels veranderen in tekstveldjes (zodat deze makelijk te bewerken zijn).
Op dit moment hebben we er nog niet veel aan maar straks gaat die ons een hele hoop werk besparen.
De methode gaat via een loop alle items af. Als een ervan overeenkomt met het aangeklikte item (dat we kunnen checken via de primary key 'id' die ik daarnet beschreef) dan wordt deze omgezet naar een tekstveld.
Vervolgens gaan we de datagrid vullen met de gegevens in de database.
Sub BindGrid()
Dim MyConnection As New OleDbConnection _
(connString)
MyConnection.open
Dim DS As DataSet
Dim sql_commando As OleDBDataAdapter
sql_commando = new OleDBDataAdapter("select * from berlijn", MyConnection)
DS = new DataSet()
sql_commando.Fill(DS, "berlijn")
berlijn_datagrid.DataSource=DS.Tables("berlijn").DefaultView
berlijn_datagrid.DataBind()
End Sub
|
Bovenstaande methode zal eerst alle gegevens selecteren die in de tabel 'berlijn' zitten.
Vervolgens een nieuwe dataset aanmaken (die de gegevens zal bevatten en wordt toegekend aan de datagrid).
Het sql_commando (om alle gegevens op te halen) zal de dataset vullen met de gegevens uit de tabel 'berlijn'.
Als laatste worden deze gegevens gekoppeld aan de datagrid en wordt dit weergegeven.
De twee bovenstaande methodes zijn standaard methodes. Als je ze wil gebruiken dan kopiëer je ze gewoon en pas je ze aan waar nodig (de query en de tabelnaam).
Nu krijgen we nog altijd niets op ons scherm omdat dit nergens wordt 'gevraagd'.
Er is een sub die altijd wordt aangeroepen bij het laden van de pagina, namelijk 'Page_Load'.
Hierin gaan we enkele acties beschrijven om de datagrid te vullen.
Sub Page_Load(Sender As Object, E As EventArgs)
Dim MyConnection As New OleDbConnection (connString)
MyConnection.open
If Not (IsPostBack)
BindGrid()
End If
End Sub
|
We maken verbinding met de database en kijken of er data is verzonden.
Zoja, dan wordt deze data (uit zichzelf) weergegeven. Zoneen, dan roepen we de sub 'BindGrid' aan om de grid te vullen.
Nu kunnen we genieten van de data die uit onze tabel komt. Hier zijn we niets mee omdat deze statisch is.
We gaan dit dynamisch maken in het volgende hoofdstukje.
top
4. Gegevens verwijderen
In dit onderdeel gaan we leren hoe we gegevens uit de database (en de datagrid) kunnen verwijderen.
We hebben het geluk dat de datagrid een aumatische knop heeft voorzien voor het verwijderen van de gegevens.
Deze knop moeten we enkel aanroepen (en enkele argumenten meegeven).
Vervolgens schrijven we een korte methode die de geselecteerde gegevens verwijderd.
We gaan terug naar onze 'datagrid'.
Hieraan voegen we enkele attributen en subattributen toe (de actie en de button).
Straks gaan we er nog meer moeten toevoegen voor het bewerken van de data en het cancellen van het editten.
Deze attributen heb ik nu ook ineens al toegevoegd zodat dit straks niet meer hoeft.
<ASP:DataGrid id="berlijn_datagrid" runat="server"
OnEditCommand="berlijn_datagrid_Edit"
OnCancelCommand="berlijn_datagrid_Cancel"
OnUpdateCommand="berlijn_datagrid_Update"
OnDeleteCommand="berlijn_datagrid_delete"
DataKeyField="id" OnItemDataBound="berlijn_datagrid_ItemDataBound"
>
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false" />
<asp:ButtonColumn Text="Verwijder" CommandName="Delete" />
</Columns>
</ASP:DataGrid>
|
'onEditCommand' wordt aangeroepen als er op de 'edit' knop wordt gedrukt.
'onCancelCommand' wordt aangeroepen als er op de 'cancel' knop wordt gedrukt.
'onUpdateCommand' wordt aangeroepen als er op de 'update' knop wordt gedrukt.
'onDeleteCommand' wordt aangeroepen als er op de 'delete' knop wordt gedrukt.
Deze knoppen zijn er nog niet. Daarom moeten we deze gaan toevoegen bij 'columns'.
'asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false"'
Deze regel maakt de eerste 3 knoppen aan. Een knop 'Edit' die veranderd in 'Cancel' en 'Update' als er wordt op geklikt.
De volgende regel is van het type 'Delete' en zal het DeleteCommand aanroepen.
Dit klinkt ingewikkeld omdat het moeilijk is om uit te leggen maar eigenlijk is het helemaal niet zo moeilijk.
Het zit allemaal heel logisch in elkaar: edit zorgt voor bewerken, delete zorgt voor verwijderen, ...
We zijn niets met deze knoppen alleen, er moet nog een 'DELETE' query worden aangemaakt.
Dit gebeurt in de sub 'berlijn_datagrid_delete'.
Deze wordt hieronder aangemaakt.
Sub berlijn_datagrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
Dim MyConnection As New OleDbConnection _
(connString)
Dim sql_commando As OleDBCommand
Dim DeleteCmd As String = "DELETE from berlijn where id = @Id"
sql_commando = New OleDBCommand(DeleteCmd, MyConnection)
sql_commando.Parameters.Add(New OleDBParameter("@id", OleDBType.Numeric, 11))
sql_commando.Parameters("@id").Value = berlijn_datagrid.DataKeys(CInt(E.Item.ItemIndex))
sql_commando.Connection.Open()
Try
sql_commando.ExecuteNonQuery()
Message.InnerHtml = "<strong>Verwijderd!</strong><br>"
Catch Exc As OleDBException
Message.InnerHtml = "Er is een fout opgetreden bij het verwijderen van het record"
Message.Style("color") = "red"
End Try
sql_commando.Connection.Close()
BindGrid()
End Sub
|
We beginnen (zoals altijd) met de verbinding met de database.
Vervolgens maken we een Delete Commando aan ("DELETE from berlijn WHERE id = @id") waarbij de @id straks weer vervangen wordt.
Uit 'E' kunnen we afleiden op welk item er is geklikt. Het ItemIndex is namelijk de primary key waar ik het over had.
Deze zal ervoor zorgen dat het juiste item wordt verwijderd.
Het commando wordt uitgevoerd en om af te sluiten moeten we terug 'databinden' (er is namelijk post data verzonden en dus wordt er niets getoond dus moeten we zorgen dat de datagrid terug wordt gegenereerd).
top
5. Gegevens updaten
De laatste stap, en daarbij ook de moeilijkste, is het updaten van de data.
De link is al gelegd naar de juiste sub, alleen moet deze nog aangemaakt worden.
Toen ik met dit probleem vast zat heb ik hier hard mee lopen knoeien. Ik wilde namelijk alles eerst via de 'vervang manier' zoals hierboven doen.
Dat ik dus @naam, @id, etc. toevoegde en dat het automatisch werd vervangen.
Doordat de datagrid zo complex in elkaar zit en je niet zoveel grip hebt op de code die binnen de datagrid wordt uitgevoerd (klein nadeel) was het moeilijk om de ingevulde gegevens up te daten.
Dit is toch gelukt. Maar eerst moeten we de 'update' button laten verschijnen en alle geselecteerde velden veranderen in tekstvelden.
Dit gebeurt via de sub berlijn_datagrid_edit.
De sub berlijn_datagrid_cancel doet het omgekeerde: dit zet de tekstveldjes terug om naar de normale velden (die als labels worden weergegeven).
Sub berlijn_datagrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
berlijn_datagrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub
Sub berlijn_datagrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
berlijn_datagrid.EditItemIndex = -1
BindGrid()
End Sub
|
Met de eerste sub zeg je 'welke' rij je wil bewerken. De datagrid zal dit automatisch veranderen en van de velden tekstvelden maken.
De tweede sub zal zorgen dat er geen rij meer wordt bewerkt door de waarde -1 aan de 'bewerkte rij index' te geven.
Om af te sluiten moeten we de sub berlijn_datagrid_update aanmaken, deze zal ervoor zorgen dat de data wordt geupdate.
Via 1 SQL commando dat wordt samengesteld uit verschillende waardes.
Sub berlijn_datagrid_Update(Sender As Object, E As DataGridCommandEventArgs)
Dim MyConnection As New OleDbConnection _
(connString)
Dim sql_commando As OleDBCommand
Dim tb As TextBox
Dim val As String
Dim id = berlijn_datagrid.DataKeys(CInt(E.Item.ItemIndex))
tb = E.Item.Cells(3).Controls(0)
val = tb.Text
Dim voornaam = Server.HtmlEncode(val)
tb = E.Item.Cells(4).Controls(0)
val = tb.Text
Dim naam = Server.HtmlEncode(val)
tb = E.Item.Cells(5).Controls(0)
val = tb.Text
Dim adres = Server.HtmlEncode(val)
tb = E.Item.Cells(6).Controls(0)
val = tb.Text
Dim telnr = Server.HtmlEncode(val)
Dim UpdateCmd As String = "UPDATE berlijn SET naam = '" & naam &"', voornaam = '" &
voornaam & "', adres = '" & adres
& "', telnr = '" & telnr &"' where id = " & id
sql_commando = New OleDBCommand(UpdateCmd, MyConnection)
sql_commando.Connection.Open()
Try
sql_commando.ExecuteNonQuery()
Message.InnerHtml = "<strong>Geüpdate!</strong><br>"
berlijn_datagrid.EditItemIndex = -1
Catch Exp As OleDBException
Message.innerHTML = "Er is een fout opgetreden bij het updaten van de data"
Message.Style("color") = "red"
End Try
sql_commando.Connection.Close()
BindGrid()
End Sub
|
Deze sub zullen we stap voor stap doornemen omdat dit de meest complexe van allemaal is.
We beginnen weer met de connectie aan te maken (nog niet te openen).
Vervolgens maken we een sqlcommando aan, een textbox (virtueel) en een string genaamd 'val'.
We halen het id op door te kijken naar de ItemIndex (de primary key) van de datagrid.
Vervolgens halen we een voor een de ingevulde waardes op.
We beginnen bij 3 omdat 0, 1 en 2 gereserveerd zijn (0 is de edit button, 1 is de delete button en 2 is het id veldje).
We geven de waarde van de ingevulde voornaam (die zich in E.Item.Cells(3).Controls(0)) bevind aan de variabele 'voornaam'.
Dit doen we voor elk van de volgende: 'naam', 'adres' en 'telnr'.
We creëeren een query gebasseerd op bovenstaande variabelen.
De update query is niet het moeilijke deel, eerder de data in de query krijgen (via E.Item.Cells).
We kunnen altijd een controle uitvoeren dat bv. de naam enkel uit letters mag bestaan etc maar we besparen ons de moeite.
De query wordt uitgevoerd, bij succes geven we de boodschap 'Geüpdate!' weer. Bij het mislukken geven we een foutmelding weer.
Het gehele script is te downloaden op Klik
Ik weet dat het ingewikkeld uitgeld is maar het is gewoon een heel moeilijk onderwerp. Ik heb het zo eenvoudig mogelijk proberen toe te lichten en overbodige informatie achterwege gelaten zodat er geen onnodige vragen optreden.
Het principe bestaat hem uit 'Delete button, Edit button', 'lees gegevens' en 'voeg nieuwe gegevens toe'.
Het is heel eenvoudig om via de datagrid veel gegevens tegelijkertijd te bewerken, alleen is het op het begin even complex om te maken.
Hopelijk is de tutorial duidelijk genoeg. Zoniet dan kan je altijd vragen stellen en opmerkingen geven waar het beter kan.
top
asp.NET Databases en DataGrid
|