ASP.net : Databases

  1. Inleiding
  2. Connectiestring
  3. Gegevens ophalen: SqlDataAdapter en DataSet
  4. Gegevens ophalen: SqlCommand en SqlDataReader
  5. Select instructie met parameters
  6. Gegevens toevoegen: Insert
  7. Gegevens bewerken: Update
  8. Gegevens verwijderen: Delete
  9. Gegevens sorteren
  10. Stored Procedures gebruiken

Inleiding

In dit hoofdstuk leer je hoe je ASP.net kan gebruiken voor toepassingen die verbinding maken met een server-side gegevensbron zoals een SQL Server Database.

De basisklassen die je hiervoor nodig hebt leer je kennen in de cursus ADO.net.

Database Spionshop.mdf voor SQL Server downloaden
(unzippen, toevoegen via Alle Taken > Attach Database, eigen uid en pwd gebruiken)

Connectiestring

Meestal zal je binnen een toepassing meermaals verbinden met dezelfde database op dezelfde SQL-server. Vandaar dat het een goed idee is om de connectiestring op te nemen in het algemene configuratiebestand Web.config: 

Web.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
        <add key="SpionString" 
        value="server=(local);database=Spionshop;uid=spion;pwd=spionpas;" />
  </appSettings>

  <system.web>
...

Je kan de gegevens binnen het configuratiebestand benaderen via de namespace System.Configuration.

Gegevens ophalen: SqlDataAdapter en DataSet

Je kan nu heel eenvoudig de gegevens uit een database laden in een DataGrid:

Gegevens ophalen: SqlCommand en SqlDataReader

Voor een forward-only, read-only bewerking kan je een SqlDataReader binden aan een DataGrid: 
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);
SqlCommand cmd = new SqlCommand("select * from Klant", conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataGrid1.DataSource = dr;
DataGrid1.DataBind();
conn.Close();

Select instructie met parameters

In de cursus ADO.net leerde je reeds hoe je parameters kan toevoegen aan een SqlCommand. Deze keer leer je hoe je een parameter kan toevoegen aan een SqlDataAdapter om een DataSet te vullen.

In deze toepassing vullen we een ListBox met de categorieën uit de database. Wanneer de gebruiker een categorie kiest worden de artikels uit deze categorie getoond in een DataGrid. Voor het vullen van de DataGrid kijken we naar de SelectedValue van de DataList die het cat_id voor deze categorie bevat.

We voegen de SqlParameter toe aan het SelectCommand van de SqlDataAdapter.

Je kan voor de DataGrid de eigenschap EnableViewState best op false plaatsen. Het heeft geen zin de inhoud van de DataGrid te laten opnemen in de ViewState wanneer deze bij elke PostBack wijzigt. 

...
using System.Data.SqlClient;
using System.Configuration;

namespace Basis
{
    public class Parameter : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid dgArtikel;
        protected System.Web.UI.WebControls.ListBox lstCategorie;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!IsPostBack)
            {
                SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);
                SqlCommand cmd = new SqlCommand("select * from Categorie order by categorie", conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                lstCategorie.DataSource = dr;
                lstCategorie.DataTextField = "Categorie";
                lstCategorie.DataValueField = "cat_id";
                lstCategorie.DataBind();
                conn.Close();
                if(lstCategorie.Items.Count > 0)
                {
                    lstCategorie.SelectedIndex = 0;
                    VulArtikels();
                }
            }
        }

        private void VulArtikels()
        {
            String sql = @"select artikel, omschrijving, verkoopprijs 
                           from artikel 
                           where cat_id = @catid
                           order by artikel";

            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

            da.SelectCommand.Parameters.Add(new SqlParameter("@catid",SqlDbType.SmallInt));
            da.SelectCommand.Parameters["@catid"].Value = lstCategorie.SelectedValue;

            DataSet ds = new DataSet();
            da.Fill(ds, "Artikels");


            dgArtikel.DataSource= ds;
            dgArtikel.DataMember = "Artikels";
            dgArtikel.DataBind();
        }

        ...

        private void lstCategorie_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            VulArtikels();
        }
    }
}

Gegevens toevoegen: Insert

We staan de gebruiker toe artikels toe te voegen aan de database.

Hiertoe voorzien we TextBox-controls voorzien van Input Validation Conrols waar de gebruiker de gegevens voor een nieuw artikel kan ingeven. We voorzien een Button waarmee de gebruiker de opdracht kan uitvoeren.

Wanneer de pagina valid is worden de gegevens van het artikel toegevoegd, rekening houdend met de geselecteerde categorie. 

...
using System.Data.SqlClient;
using System.Configuration;

namespace Basis
{
    public class Toevoegen : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid dgArtikel;
        protected System.Web.UI.WebControls.TextBox txtArtikel;
        protected System.Web.UI.WebControls.TextBox txtOmschrijving;
        protected System.Web.UI.WebControls.TextBox txtPrijs;
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;
        protected System.Web.UI.WebControls.Button cmdToevoegen;
        protected System.Web.UI.WebControls.CompareValidator CompareValidator1;
        protected System.Web.UI.WebControls.Label lblBericht;
        protected System.Web.UI.WebControls.ListBox lstCategorie;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!IsPostBack)
            {
                SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);
                SqlCommand cmd = new SqlCommand("select * from Categorie order by categorie", conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                lstCategorie.DataSource = dr;
                lstCategorie.DataTextField = "Categorie";
                lstCategorie.DataValueField = "cat_id";
                lstCategorie.DataBind();
                conn.Close();
                if(lstCategorie.Items.Count > 0)
                {
                    lstCategorie.SelectedIndex = 0;
                    VulArtikels();
                }
            }
        }

        private void VulArtikels()
        {
            String sql = @"select artikel, omschrijving, verkoopprijs 
                           from artikel 
                           where cat_id = @catid
                           order by artikel";

            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

            da.SelectCommand.Parameters.Add(new SqlParameter("@catid",SqlDbType.SmallInt));
            da.SelectCommand.Parameters["@catid"].Value = lstCategorie.SelectedValue;

            DataSet ds = new DataSet();
            da.Fill(ds, "Artikels");


            dgArtikel.DataSource= ds;
            dgArtikel.DataMember = "Artikels";
            dgArtikel.DataBind();
        }

        ...

        private void lstCategorie_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            VulArtikels();
        }

        private void cmdToevoegen_Click(object sender, System.EventArgs e)
        {
        lblBericht.Text = "";

            if (Page.IsValid)
            {
                SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);

                String sql = @"insert into artikel (artikel, omschrijving, verkoopprijs, cat_id) 
                               values (@artikel, @omschrijving, @verkoopprijs, @cat_id)";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.Add(new SqlParameter("@artikel", SqlDbType.VarChar,50));
                cmd.Parameters["@artikel"].Value = txtArtikel.Text;

                cmd.Parameters.Add(new SqlParameter("@omschrijving", SqlDbType.VarChar, 500));
                cmd.Parameters["@omschrijving"].Value = txtOmschrijving.Text;

                cmd.Parameters.Add(new SqlParameter("@verkoopprijs", SqlDbType.SmallMoney, 4));
                cmd.Parameters["@verkoopprijs"].Value = txtPrijs.Text;

                cmd.Parameters.Add(new SqlParameter("@cat_id", SqlDbType.SmallInt, 2));
                cmd.Parameters["@cat_id"].Value = lstCategorie.SelectedValue;

                cmd.Connection.Open();

                try
                {
                    cmd.ExecuteNonQuery();
                    lblBericht.Text = "<b>Record toegevoegd</b>";
                    lblBericht.Style["color"] = "green";
                }
                catch (SqlException ex)
                {
                    lblBericht.Text = "Fout: record kon niet worden toegevoegd:<br> " +ex.Message;
                    lblBericht.Style["color"] = "red";
                }

                cmd.Connection.Close();
            }

            VulArtikels();


        }
    }
}

Gegevens bewerken: Update

Met een DataGrid kan je op een eenvoudige manier functionaliteit om gegevens te bewerken invoegen.

De DataGrid kan een EditCommandColumn bevatten die links weergeeft die drie speciale events uitvoeren: EditCommand, UpdateCommand, en CancelCommand.
Deze kolom kan je in Visual Studio.net eenvoudig toevoegen via de Property Builder... > Columns > Button Column > Edit, Update, Cancel
Je kan de tekst voor de hyperlinks zelf instellen.

Voor deze toepassing plaats je de eigenschap EnableViewState van de DataGrid weer op true.

Voeg voor de drie besproken events event-handler methoden toe. De UpdateCommand voert de eigenlijke update naar de database uit.

Het is nu wenselijk dat we beschikken over het juiste artikel_id. We zorgen er dus voor dat dit veld ook uit de tabel wordt geladen. Je kan dit veld instellen als DataKeyField van de DataGrid. 

dgArtikel.DataKeyField = "artikel_id";

Wens je nu ook meer controle over het weergeven van de kolommen, werk dan bijvoorbeeld met BoundColumns in plaats van automatisch gegenereerde kolommen.

In deze toepassing zorgen we ervoor dat de kolom die de artikel_id's bevat onzichtbaar is.

TemplateColumn

Soms is het wenselijk om de te editeren inhoud weer te geven in een andere Control dan een standaard TextBox. Hiervoor kan je gebruik maken van een TemplateColumn en de opmaak van de EditItemTemplate.

Met een TemplateColumn kan je zelfs Input Validation Controls aan de EditItemTemplate toevoegen !

Gegevens verwijderen: Delete

Je kan op een eenvoudige manier een kolom aan een DataGrid toevoegen waarmee je gegevens kan verwijderen. Dit doe je door een ButtonColumn toevoegen met CommandName Delete. Dit kan je voor jou laten doen door Visual Studio.net:

Gegevens sorteren

Wanneer voor een DataGrid de eigenschap AllowSorting op true staat ingesteld, worden de kolomkoppen als LinkButtons weergegeven. Wanneer je een LinkButton aanklikt wordt het event SortCommand van de DataGrid uitgevoerd. Standaard wordt de naam van de kolom meegegeven als waarde voor de eigenschap SortExpression van het argument DataGridSortCommandEventArgs in de event-handler. Deze SortExpression kan je gebruiken om de eigenschap Sort van de onderliggende DataView in te stellen.

Wanneer je de kolommen automatisch laat genereren wordt de SortExpression voor elke kolom automatisch ingesteld, gebruik je zelfgeplaatste kolommen zoals BoundColumn, dan stel je de SortExpression voor de kolom zelf in (op de naam van een veld van de onderliggende tabel).

Doordat we in onze toepassing PostBacks naar de server hebben wanneer de gebruiker een andere categorie kiest laat ik de gekozen SortExpression bijhouden in de ViewState.

Oplopend en aflopend

De sortering van gegevens gebeurt standaard oplopend (ascending). Door aan de methode Sort van de DataView een extra string " DESC" mee te geven kan je de gegevens ook aflopend (descending) sorteren.

In de toepassing zorgen voor een extra element in de ViewState: SortArtikelsOplopend. We houden bij of er oplopend of aflopend wordt gesorteerd. We wisselen de sorteerorde wanneer twee keer op eenzelfde kolomkop wordt geklikt. 


    ...

        private void VulArtikels()
        {
            String sql = @"select artikel_id, artikel, omschrijving, verkoopprijs 
                           from artikel 
                           where cat_id = @catid
                           order by artikel";

            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

            da.SelectCommand.Parameters.Add(new SqlParameter("@catid",SqlDbType.SmallInt));
            da.SelectCommand.Parameters["@catid"].Value = lstCategorie.SelectedValue;

            DataSet ds = new DataSet();
            da.Fill(ds, "Artikels");

            DataView bron =ds.Tables["Artikels"].DefaultView;
            if(ViewState["SortArtikelsOplopend"] == null) ViewState["SortArtikelsOplopend"] = true;
            if(ViewState["SortArtikels"] == null) ViewState["SortArtikels"] = "artikel";
            
            if((bool)ViewState["SortArtikelsOplopend"])
                bron.Sort = ViewState["SortArtikels"].ToString();
            else
                bron.Sort = ViewState["SortArtikels"].ToString() +" DESC";



            dgArtikel.DataSource= bron;
            dgArtikel.DataKeyField = "artikel_id";
            dgArtikel.DataBind();
        }

        ...

        private void lstCategorie_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            VulArtikels();
        }

        private void dgArtikel_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
        {
            if(ViewState["SortArtikels"].Equals(e.SortExpression)) ViewState["SortArtikelsOplopend"] = !(bool)ViewState["SortArtikelsOplopend"];
            ViewState["SortArtikels"] = e.SortExpression;
            VulArtikels();

        }


    }
}

Stored Procedures gebruiken

Om de performatie van je toepassing te verhogen kan je gebruik maken van Stored Procedures. De code van je applicatie wordt ook toegankelijker, daar je nu geen SQL-statements meer hoeft op te nemen in je toepassing. Wanneer de database wijzigt hoef je de applicatie niet aan te passen, maar voer je de wijzigingen op Database-niveau door.

Stored Procedures maken

In SQL-Server kan je Stored Procedures maken via de SQL Query Analyzer. 
CREATE PROCEDURE spToonArtikelGegevens
AS
SELECT artikel, omschrijving
FROM artikel
ORDER BY artikel

Stored Procedures gebruiken

Werk je rechtstreeks met een SqlCommand, bijvoorbeeld voor het vullen van een SqlDataReader, dan stel je het CommandType van de SqlCommand in op StoredProcedure, en de CommandText op de naam van de Stored Procedure. 

SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);

//STP aanroepen voor een DataReader
SqlCommand cmd = new SqlCommand("spToonArtikelGegevens",conn);
cmd.CommandType = CommandType.StoredProcedure;

conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataGrid1.DataSource=dr;
DataGrid1.DataBind();
conn.Close();

Je kan ook een DataSet vullen met behulp van een Stored Procedure. 

SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);

//STP aanroepen voor een DataAdapter
SqlDataAdapter da = new SqlDataAdapter("drie duurste producten", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();
da.Fill(ds, "ArtikelGegevens");

DataGrid2.DataSource=ds;
DataGrid2.DataMember = "ArtikelGegevens";
DataGrid2.DataBind();

De Stored Procedure drie duurste producten ziet er als volgt uit: 

CREATE  procedure "Drie duurste producten" AS
SET ROWCOUNT 3
SELECT artikel, verkoopprijs
FROM artikel
ORDER BY verkoopprijs DESC

Parameters aan Stored Procedures doorgeven

Je kan analoog aan het werken met gewone SQL-statements ook parameters doorgeven aan een Stored Procedure. In het voorbeeld werken we met een SqlDataAdapter en een DataSet, je kan natuurlijk ook parameters meegeven met een SqlCommand voor het vullen van een SqlDataReader.

De Stored Procedure: 

CREATE PROCEDURE ArtikelUitCategorie @CatID smallint
AS
SELECT artikel, omschrijving, verkoopprijs
FROM artikel
WHERE cat_id = @CatID
ORDER BY artikel

Code in Page_Load: 

SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["SpionString"]);

//STP met parameter aanroepen voor een DataAdapter
SqlDataAdapter daAC = new SqlDataAdapter("ArtikelUitCategorie", conn);
daAC.SelectCommand.CommandType = CommandType.StoredProcedure;

daAC.SelectCommand.Parameters.Add(new SqlParameter("@CatID", SqlDbType.SmallInt, 15));
daAC.SelectCommand.Parameters["@CatID"].Value = 1;


DataSet dsAC = new DataSet();
daAC.Fill(dsAC, "ArtikelGegevens");

DataGrid3.DataSource=dsAC;
DataGrid3.DataMember = "ArtikelGegevens";
DataGrid3.DataBind();

Meer tutorials:
leer ook: html | xhtml | css | asp | asp.net | c# | ado.net | linq | ajax | java | javascript
Valid HTML 4.01! Valid CSS! © - Cursusweb