Improving Your Data Access Code

You have to ask yourself at this point whether the code is robust and scalable. Take each portion of the Web Form and ask yourself what would happen if

■ The volume of data grows substantially.

■ The data values change.

■ The display requirements change.

Consider how even this simple application might be used. For a single school, the number of teachers probably wouldn't grow beyond manageable limits. But for a school system or a set of school systems in a state, the volume of data would soon become unmanageable. For example, an administrator would definitely want to filter the list of teachers to show only those associated with a single school. In other words, if the volume of data grew substantially, the database would need to change, as would the interface and display. By simply considering the problem in advance, you can decide whether to add database items such as School and District to the database and then alter the application plan accordingly. A user might want to view the data in other ways. By anticipating the possible requirements before you design the database and write the application, you'll save yourself a great deal of time and effort later.

Suppose the data values change. This application works only because each teacher has a unique name. You should never use data values as selection criteria that might at some point be duplicated. Fortunately, this is a relatively simple change. Rather than returning the teacher name when the form submits, you can submit the TeacheriD value associated with that name.

Finally, suppose the display requirements change. For example, it's not particularly useful to display the studentiDs—they're meaningless to humans. It would be better to display the students' names. Similarly, what would a user want to do with those names? It's highly likely that administrators would want to create classes and assign teachers and students to those classes, as well as add new students and teachers, change their properties, and remove students, classes, and teachers as the schedule required. In the next section, "Inserting, Updating, and Deleting Data," you'll create a complete application that limits access to authenticated administrators, but because it's so important to use row ID values rather than possibly duplicated data for queries, you'll change the lstTeachers ListBox to post TeacheriD values right away.

The first step in altering the ListBox is to create a stored procedure that returns the TeacheriD values as well as the names. Define a stored procedure called qryGetTeachers that retrieves the TeacheriD, LastName, and FirstName values from the Teachers table. Note that the following query no longer forces the database to concatenate the names as FirstName LastName; instead, two parameters determine the way the database returns the names and the sort order. Note that you don't have to do this, but it's very convenient and saves you from having to add code at the interface level to sort the returned data. It's much simpler to change or add a parameter value than to add sorting capabilities in code. If you were using DataGrids or a third-party Server control that can concatenate column values under an alias name, or sort columns, you wouldn't have to do this.

CREATE PROCEDURE qryGetTeachers

@nameorder char(9) = 'lastfirst', @sortorder varchar(4) = 'ASC'

SET @nameorder=lower(@nameorder) set @sortorder=lower(@sortorder) iF (@nameorder='firstlast')

begin iF (@sortorder='desc')

begin

SELECT TeacheriD, FirstName + ' ' + LastName

AS Name FROM Teachers

ORDER BY LastName DESC, FirstName

END ELSE

begin

SELECT TeacheriD, FirstName + ' ' + LastName AS Name FROM Teachers

ORDER BY LastName ASC, FirstName ASC

END ELSE

BEGIN

IF (@sortorder='desc') BEGIN

SELECT TeacherID, LastName + ', ' + FirstName AS Name FROM Teachers

ORDER BY LastName DESC, FirstName ASC

END ELSE

BEGIN

SELECT TeacherID, LastName + ', ' + FirstName AS Name FROM Teachers

ORDER BY LastName ASC, FirstName ASC

This procedure contains some Transact-SQL (T-SQL) code you haven't seen before. First, note that the parameters have default values.

@nameorder char(9) = 'lastfirst', @sortorder varchar(4) = 'ASC'

The calling code does not have to pass these parameters explicitly, meaning that if you're willing to accept the default name order (last, first) and sort order (asc), you don't have to pass the parameters at all.

Second, T-SQL supports an if...else construct. You must place the test between parentheses.

IF (@nameorder='firstlast')

If you have more than a single statement following the if test (called a statement block), you must place begin and end statements around the statement block. I've found that it's a good idea to always add the begin and end statements.

Finally, note that when you want to sort by multiple columns in different orders, you must explicitly tell the server the sort order for each column. For example, this clause order by LastName, Firstname desc doesn't sort the names in descending order as you might expect. Instead, it sorts by LastName asc, FirstName desc, which in this particular case has the effect of applying no sort order whatsoever. Instead, use ORDER BY LastName DESC, Firstname ASC.

You'll also need to create a stored procedure to retrieve the students for a particular teacher. Rather than passing the first and last names, which might be ambiguous if the school acquires two teachers with the same name, the altered procedure accepts a TeacherID. The procedure now returns the studentiD, LastName, and FirstName column values for each student. Doing so requires an additional inner join clause to the Students table to get the names. Finally, the procedure adds @nameorder and @sortorder parameters that work exactly the same as those in the qryGetTeachers procedure. Here's the new procedure, called qryGetStudentsForTeacherlD:

CREATE PROCEDURE qryGetStudentsForTeacherlD

@TeacherID int, @nameorder char(9)='lastfirst', @sortorder varchar(4)='ASC'

set @nameorder=lower(@nameorder) set @sortorder=lower(@sortorder) IF (@nameorder='firstlast') BEGIN

IF (@sortorder='desc') BEGIN

SELECT TeacherStudent.StudentID, Students.FirstName + ' ' + Students.LastName AS Name FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherID INNER JOIN Students

ON TeacherStudent.StudentID=Students.StudentID

WHERE [email protected] ORDER BY Students.Lastname DESC, Students.Firstname ASC END ELSE

BEGIN

SELECT TeacherStudent.StudentID, Students.FirstName + ' + Students.LastName AS Name FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherID INNER JOIN Students

ON TeacherStudent.StudentID=Students.StudentID WHERE [email protected] ORDER BY Students.Lastname ASC, Students.Firstname ASC

END ELSE

BEGIN

IF (@sortorder='desc') BEGIN

SELECT TeacherStudent.StudentID, Students.LastName + ', + Students.FirstName AS Name FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherID INNER JOIN Students

ON TeacherStudent.StudentID=Students.StudentID WHERE [email protected] ORDER BY Students.Lastname DESC, Students.Firstname ASC

END ELSE

BEGIN

SELECT TeacherStudent.StudentID, Students.LastName +

', ' + Students.FirstName AS Name FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherID INNER JOIN Students

ON TeacherStudent.StudentID=Students.StudentID WHERE [email protected] ORDER BY Students.Lastname ASC, Students.Firstname ASC

Finally, you need to change the code a bit to view and select teachers by ID rather than name. The Web Form ch14-4.aspx contains a new version of the ch14-3.aspx Web Form that uses the updated procedures. I won't show the code here, as it's very similar, but when you run it, it looks like Figure 14.8.

Figure 14.8: The Web Form chl4-4.aspx contains a modified version for selecting students of a specific teacher

In the next section, you'll work with the DataList and Repeater controls to display data. Then you'll create a Web Form to perform create, update, and delete operations on the student records in the classRecords database.

Was this article helpful?

0 0

Post a comment