Creating data classes for ASP.NET data controls
Part seven of a seven-part series of blogs

Yes, ASP.NET provides DataSource controls, but wizards only get you so far. Share the secret of how Wise Owl create all our ASP.NET websites, using a single, simple data class.

  1. Data Classes in ASP.NET
  2. Our Example for this Data Classes Tutorial
  3. Storing and Retrieving Connection Strings
  4. ADO.NET: DataSets, DataTables and Data Adapters
  5. Consuming our Data Classes
  6. A Class to Run a Stored Procedure and Return its Rows
  7. Consuming our Data Class on the Web Page (this blog)

This blog is part of a larger online ASP.NET online tutorial.  For ASP.NET training or courses in VB programming or programming in C#, see the separate pages.

Posted by Andy Brown on 07 August 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Consuming our Data Class on the Web Page

Loading the Dropdownlist

We've already seen that we can load data into our dropdownlist when the page loads, whether in C#:

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

// on first loading the page, create a new "query"

aaaQuery sp = new aaaQuery("spListMovies", "csMovies");

 

// add a parameter so that we only show films winning

// at least 1 Oscar

sp.AddParameter("MinOscars", 1);

 

// get at the data table returned from this "query"

DataTable dt = sp.DtSelectedRecords;

 

// use this to populate our dropdown list

ddlMovieId.DataSource = dt;

ddlMovieId.DataBind();

}

}

Or alternatively in VB:

Protected Sub Page_Load(sender As Object,

e As System.EventArgs) Handles Me.Load

 

If Not Page.IsPostBack Then

 

'on first loading the page, create a new "query"

Dim sp As New aaaQuery("spListMovies", "csMovies")

 

'add a parameter so that we only show films winning

'at least 1 Oscar

sp.AddParameter("MinOscars", 1)

 

'get at the data table returned from this "query"

Dim dt As DataTable = sp.DtSelectedRecords

 

'use this to populate our dropdown list

ddlMovieId.DataSource = dt

ddlMovieId.DataBind()

 

End If

 

End Sub

However, we also have to ensure that we then display details for the movie requested.

Note that we could do this using either a FormView or DetailsView control, but here I've decided to do things the hard way.

The Panel Containing the Movie's Details

Our chosen movie's details will be contained in a panel, which initially will be hidden.  Here's what it will look like in a web browser:

The panel showing the film

The previously hidden panel showing the film's details.

 

And here's what it looks like in HTML:

Panel containing movie details

The panel containing the movie's name and number of Oscars won.

Handling the DropDownList's Selection Event

When someone changes the movie, we need to display its details.  To do this, first make sure that the dropdownlist automatically posts back to the server:

Dropdownlist autopostback event

Make sure that the AutoPostBack property is set to True for the dropdownlist.

You now need to attach code to the dropdownlist - in VB you can just double-click on it in design view, but in C# you need to attach an event:

SelectedIndexChanged event

Double-click on the SelectedIndexChanged event of the dropdownlist to create an event-handler for C#.

 

A Stored Procedure to Return a Single Row

We can write a new stored procedure called spMovieRecord to return a single row from the tblMovie table:

CREATE PROC [dbo].[spMovieRecord](

@MovieId int=0

)

AS

SELECT

*

FROM

tblMovie

WHERE

MovieId=@MovieId

This will take an integer parameter giving the movie's id number, and return a data table which we know will contain only a single record.

The C# and VB Code to Handle the Event

Here's the C# event-handler for the dropdownlist:

protected void ddlMovieId_SelectedIndexChanged(object sender, EventArgs e)

{

// find out the unique number of the movie chosen

int MovieId = Convert.ToInt16(ddlMovieId.SelectedValue);

 

// create a "query" showing this single movie

aaaQuery sp = new aaaQuery("spMovieRecord", "csMovies");

sp.AddParameter("MovieId", MovieId);

DataTable dt = sp.DtSelectedRecords;

 

// get a reference to the first (and only) data row,

// noting that data rows are numbered from 0

DataRow dr = dt.Rows[0];

 

// display the name of the movei and the number of Oscars

// it won (we have to convert the datarow objects to the

// right data type

txtMovieName.Text = Convert.ToString(dr["MovieName"]);

txtOscars.Text = Convert.ToString(dr["Oscars"]);

 

// make sure tha panel is now visible

pnlShow.Visible = true;

}

Here's the equivalent VB code:

Protected Sub ddlMovieId_SelectedIndexChanged(sender As Object,

e As System.EventArgs) Handles ddlMovieId.SelectedIndexChanged

 

'find out the unique number of the movie chosen

Dim MovieId As Integer = Convert.ToInt16(ddlMovieId.SelectedValue)

 

'create a "query" showing this single movie

Dim sp As New aaaQuery("spMovieRecord", "csMovies")

sp.AddParameter("MovieId", MovieId)

Dim dt As DataTable = sp.DtSelectedRecords

 

'get a reference to the first (and only) data row,

'noting that data rows are numbered from 0

Dim dr As DataRow = dt.Rows(0)

 

'display the name of the movei and the number of Oscars

'it won (we have to convert the datarow objects to the

'right data type

txtMovieName.Text = Convert.ToString(dr("MovieName"))

txtOscars.Text = Convert.ToString(dr("Oscars"))

 

'make sure tha panel is now visible

pnlShow.Visible = True

 

End Sub

In both cases we refer to the collection of rows returned from our class, and look only at the first object in this collection.

Conclusion

You can now use your data class to return sets of rows as the basis for dropdownlists, gridviews and any other data-bound control in ASP.NET.  As a bonus, you can even run stored procedures to insert, update or delete data without worrying about what rows they return by adding this method to your C# class:

public void Run()

{

DcmdSelect.Connection.Open();

DcmdSelect.ExecuteNonQuery();

DcmdSelect.Connection.Close();

}

Or for VB:

Sub Run()

DcmdSelect.Connection.Open()

DcmdSelect.ExecuteNonQuery()

DcmdSelect.Connection.Close()

End Sub

And with that thought, I've reached the end of this blog!

This blog has 0 threads Add post