User Input to Get Matching Data From 2 Related Tables
The Goals for this Example
The overall goal is to again use some user input as in our previous web app to get data from a
database, but this time the data is in 2 related tables, not 1 table.
Our revised application has the user enter a minimum and maximum price, and allows the user to check a box if only those items
that are in stock should be displayed. The app can tell what is in stock by looking for a positive number in the NumInStock
column of the correct row of the new table, the Inventory table.
When the button is then clicked, all the data that fits this input is displayed in a grid view.
This version again uses validation controls, but adds the ValidationSummary control, in this case to pop up a window
listing all of the errors in one handy place. This application uses the Entity Framework (EF) and LINQ.
Introduction
Details on the App
- We wish to create a web form that gets a minimum and maximum price from the user
and that allows the user to check a check box to limit the results to bicycle parts that are in stock.
- After the postback (which happens automatically when the user clicks the button) all
matching data is displayed in a grid view.
- Besides using the Parts table as in the previous web app, this one also uses an Inventory table, which you will
set up below. The number of items in stock will be displayed for each part on the results page, and if the check box
is checked, only those parts with a "number in stock" greater than zero will be displayed.
|
First Steps
- Create a new web site in Visual Studio, placing it on your mapped network drive for your live web server if possible.
Add the App_Code folder to your web site by right clicking the site in Solution Explorer, etc.
- Use Computer to find your Web.config file from the previous web app. Copy the section
for the webappConnection into your Web.config file for the new web site.
This connection string was originally created in this prior example.
This is the connection string that allows you to connect to the database using SQL Server authentication. That is,
it passes the username and password to log into SQL Server over the network so as to make the connection.
Also copy the connection string for logging into SQL Server using Windows authentication with your Windows account.
You would need that if you want to use Server Explorer in Visual Studio to access your database.
Leave out any entity connections.
|
Creating the Inventory Table
- In this example, create the Inventory table using SQL Server Management Studio.
This is because Visual Studio's Server Explorer does not appear to allow you to make a database diagram.
However, you could use Server Explorer to set up the structure (the columns) for the Inventory table and to fill
in the data. Then you would need to go to SQL Server Management Studio to make the database diagram.
- The way to connect to the database and work with it are the same as discussed in
this section of the quick database app which we did earlier.
- Like in that earlier case, create a new table called Inventory within your database. Give it the following fields:
|
| Name |
Data Type |
Allow Nulls |
| Id |
int |
not checked |
| PartNumber |
int |
not checked |
| NumInStock |
int |
not checked |
- For this list of steps, the explanation is geared toward how things would be done in SQL Server Management Studio. If
you are using Server Explorer in Visual Studio, you will have to adapt this a bit or refer
back to the directions for this prior example as a general outline.
- In the Designer for this table, right click at the front of the Id field and make the selection to designate this field
to be the primary key.
- Right click again at the front of the Id field and look at Properties. In the Properties window,
find the Identity Column and set its value to be Id.
- Next, look at the Properties for this table. Find the (Name) entry and change it to Inventory.
- Now do a File, Save or click the save button.
- You can now close the window where we designed the columns for this table.
- Next we add data to this table. Begin by right clicking on the table in SQL Server Management Studio and
then select Edit Top 200 Rows.
- In the resulting window you can fill in data for this table. The Id field is an identity field, so its values
will automatically be filled in. For the other fields, enter the data that is shown below:
|
| Id |
PartNumber |
NumInStock |
| 1 |
3 |
11 |
| 2 |
1 |
4 |
| 3 |
5 |
0 |
| 4 |
4 |
26 |
| 5 |
2 |
1 |
| 6 |
6 |
17 |
| 7 |
7 |
33 |
| 8 |
13 |
0 |
| 9 |
10 |
1 |
| 10 |
8 |
5 |
| 11 |
9 |
8 |
| 12 |
11 |
3 |
| 13 |
12 |
6 |
- Be sure that for every row in your Parts table, the PartNumber for that row occurs in a row of the Inventory table.
Adjust your data if need be so that this condition is satisfied. Each part needs to have inventory data.
- You may now close the window where you entered the data. The data for each row is saved automatically
when you move to the next row.
- The next step is to add a database drawing that establishes the relationship between these two tables that is based
on the PartNumber field that they both have. In the Parts file, PartNumber is the primary key. In the Inventory file,
it is called a foreign key. In SQL Server Management Studio, in Object Explorer, find your database folder. Inside of that
folder, right click on the Database Diagram folder. Select New Database Diagram. Put checks next to the Parts and Inventory
tables to add them to the digram. Then click in the left margin of the PartNumber field of the Parts table in the drawing
and drag it to the PartNumber field of the Inventory table. A line should be drawn indicating the relationship.
If you click on that line, you can then examine the properties of this relationship.
- Note that the property named Enforce Foreign Key Constraint will keep you (and your web apps) from deleting a part from the Parts
table if that part number is in use for some entry in the Inventory table. If you do the deletion from the Inventory table
and then delete the part in the Parts table that has the same PartNumber, it should work.
- Also note that you can right click on a table and select Edit Top 200 Rows at any time that you want to adjust the data.
- You can now close SQL Server Management Studio.
|
Setting up the New Form
- In Solution Explorer, add a new web form called PartsLookup to your web site, adjusting the title (in Source view) to
"Super Bicycle Parts" or similar.
- On your PartsLookup form, you want to make it look like this image of the form in Design View.
- You may want to copy in the controls that you placed on the form in the previous web app.
- Then add the label whose text says "Only report parts that are in stock" along with the checkbox that is next to it.
- The textboxes, validators, and grid view are the same as before, although you may want to move the validators closer together
so that the grid view is closer to the textboxes and button.
- However, we do add one new control that is related to the validators: a validation summary control.
Set it to use absolute positioning and move it to the bottom of the main div on your form, below all the validations and grid
view. Resize it so that it fits our image of the form in Design View.
In the properties for this control, set the foreground color to #CC3300 or whatever red color you used for the validators.
The header text should be "Correct the following before clicking List Matches:". Finally set the ShowMessageBox
property to True and the ShowMessageSummary to False. The former has the app pop up a message box if there are errors reported by
one or more of your validations. That message box will list these errors. If ShowMessageSummary is True, the same list of errors
is placed on the form itself.
- Save all of your files before going on.
|
Getting the Data from the Database
Entity Framework
- IMPORTANT: If you have your Web.config file open in Visual Studio, close it. In the following steps Visual Studio
will try to store another type of connection string, referred to as "entity connection settings" in
your Web.config file. You must not have your Web.config file open for this to succeed.
- Right click the App_Code folder in Solution Explorer. Select Add, ADO.NET Entity Data
Model. When asked to name the model, call it PartsModel and click OK.
- At the next screen you are asked what the model should contain. Select "Generate from database" and click Next.
- The next window has you choose your connection to the database. In the pull-down box, choose your webappConnectionString (or
whatever you named the connnection that uses SQL Server authentication in order to connect to your database.
Select the radio button labeled "Yes, include the sensitive data in the connection string".
Also choose to save the entity connection settings. The default name of carlsondEntities (or whatever username appended
to Entitites) should be fine. Then click Next.
- The next window is for choosing your database objects and settings. Keep expanding the tree starting at the Tables node
until you find your Parts and Inventory tables. Put a check in front of each of those tables.
Leave all 3 check boxes at the bottom checked. In the
box at the bottom, use carlsondModel (your username appended with Model) for this model namespace. Then click Finish.
- You will now probably be asked whether it is OK to run certain code, maybe with a mention that this could be dangerous.
However, this is exactly what you need to do. Select OK.
- You might also be asked whether to reload certain files that got modified outside of the environment. Select Yes to All.
- Wait until Visual Studio completes its work. You should see the Entity Designer displaying
a drawing of the two classes that were created based on the Parts and Inventory tables, along with a line between them
representing the relationship based on the PartNumber. Note that we have a 1-to-many relationship here. The asterisk on the
Inventory table end means that several inventory records could possibly correspond to the same PartNumber. Our data does not
set it up that way, since it was 1-to-1, but someone could put in extra records with the same PartNumber.
The Navigation Properties in the drawing are also helpful. In the Inventory table, referring to a PartNumber gives you
one part. Thus the Navigation Property here is Part. However, in the Part table, a PartNumber might give you several
rows of the Inventory table, which is why the Navigation Property here is Inventories. (Inventory records would be a better
term for this.)
- If you click on that line between the two tables, under Properties
you should see much of the same information that you saw about the relationship in SQL Server Management Studio.
Note that one end of this relationship is one single part, whereas the other end is for a collection of inventory records.
This is again because someone could set the Inventory table up with several inventory records having the same PartNumber,
even though our data does not do this.
- Be sure to save and close this diagram.
|
LINQ
- Next, we write some LINQ (language integrated query) code in our VB code-behind file,
along with some other event-handler code for the Load event for our page.
- Open your PartsLookup.aspx form in Design view if it is not already opened.
Click toward the bottom of your form until you see the little tag saying body at the top of the form.
Double click this spot at the bottom of the page. (Do NOT double click the button on the form as we are not
going to use a button click handler in this project.)
You will get an outline of the handler for the Page_Load event in the PartsLookup.aspx.vb
code-behind file. Place the following code within this function, adjusting the names if you choose different names.
See this picture of the approximate end result.
|
'If there is no postback (e.g. the first time the page loads), nothing special needs to be done. But...
If IsPostBack Then
Dim Min, Max As String
LabelError.Visible = False
Button1.Visible = False
LabelParts.Visible = True
Page.Validate() ' Check all validators to see if user input is valid.
If Page.IsValid Then
Min = TextBoxMin.Text
Max = TextBoxMax.Text
Try
Using pEntities As New carlsondEntities()
If CheckBox1.Checked Then
Dim parts = From Part In pEntities.Parts.Include("Inventories")
Where (Part.Price >= CDbl(Min)) And (Part.Price <= CDbl(Max)) _
And (Part.Inventories.FirstOrDefault.NumInStock > 0)
Order By Part.Price Ascending
Select New With {Part.Description, Part.Color, Part.Price, Part.Inventories.FirstOrDefault.NumInStock}
GridView1.DataSource = parts.ToList()
Else
Dim parts = From Part In pEntities.Parts.Include("Inventories")
Where (Part.Price >= CDbl(Min)) And (Part.Price <= CDbl(Max))
Order By Part.Price Ascending
Select New With {Part.Description, Part.Color, Part.Price, Part.Inventories.FirstOrDefault.NumInStock}
GridView1.DataSource = parts.ToList()
End If
GridView1.DataBind()
End Using
GridView1.Visible = True
Catch ex As Exception
LabelError.Visible = True
'LabelError.Text = LabelError.Text + ex.Message These 2 lines are for debugging only.
'LabelError.Text = LabelError.Text + ex.StackTrace
End Try
End If
End If
|
- Most of this code is the same as in the previous web app and does not need to
be explained further here.
- The Using statement sets up a variable (an instance) named pEntities that uses carlsondEntities
(or whatever name you chose above for the connection settings for the Entity Framework to access the
database via the webappConnectionString). This pEntities instance takes care of the connection to your
database and sends the database the LINQ queries that you use on it. For you, as you write your VB code,
pEntities gives you an object-oriented view of the database tables.
When "End Using" is reached this pEntities variable is disposed of.
- I have often gotten errors at this point. Specifically, when I type carlsondEntities in that Using line, Intellisense
often can't find any such thing. However, it often has worked if I close Visual
Studio and start it up on the project later. All of a sudden it can find carlsondEntities.
- The parts variable is used to hold the data returned by our LINQ query. Note that it is getting data from the Parts object
(that maps to the Parts table), but that we have told it to include the Inventories (inventory records). If we did not
do that, the Inventories navigation item would still be there in the object, but would be null. This is for efficiency.
There is no sense in loading the data from the second table unless we need to. In this case, we need to, and so
use the include to get that data ahead of time (rather that ask for one inventory record after another).
Notice that we use a Select to pick only only those Part items whose Price fits the desired range.
- The CDbl function converts the Min and Max strings (taken from the textboxes) into doubles.
- If the check box has been checked to say that we only want to see the data for parts that are in stock,
we look at
Part.Inventories.FirstOrDefault.NumInStock to see if that is greater than 0.
This code starts with the particular part and then uses the Inventories navigation item
to get to the matching inventory records. Since
we know there is only one inventory record per part, it is safe for us to use FirstOrDefault to pick out the first (only)
inventory record. In that record we look at the NumInStock field.
- It is not used here, but if you would ever need to get the Last or LastOrDefault (which the Entity Framework supposedly does not
provide), you can specify Descending order and then use the First operator or the FirstOrDefault operator.
- The list of fields in the With clause of the Select is used to indicate the fields that we want to have returned. This list
includes Part.Inventories.FirstOrDefault.NumInStock, which we analyzed above. It's type is called an anonymous type, since
we did not directly say what it is. Instead, the compiler figures it out from how we selected the data, that is,
from Part.Inventories.FirstOrDefault.NumInStock.
- The data in the parts variable is converted to a list that is used as the data source for your GridView and then
the data is bound to the GridView so that it shows up in the user's browser.
- Note that the catch clause for the try statement has commented off two lines for displaying
the details of any error message and/or a stack trace. This is a useful debugging technique, but not something that you
want to leave active in your final code. When running your web app locally, you might also be able to use MsgBox
to pop up a window that displays the same two messages.
|
Building and Running Your App
- Save all of your files in Visual Studio.
- Then use Build, Build Page.
- If the build succeeded, the Output window will give you a message to that effect.
- If it does not build, fix the errors.
- Then run your app first locally, by clicking the green triangle button labeled as Internet Explorer or by selecting
Debug, Start Without Debugging.
- Like the previous app, this one will probably not run correctly at first. If you get an UnobtrusiveValidationMode error,
the easiest fix is to turn off unobtrusive validation.
(Unobtrusive validation produces cleaner validation code on the client end, but is not necessary.)
Just edit your Web.config file, placing the following after the end of the configSections region so that
your Web.config looks much like this Web.config file.
|
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>
</appSettings>
|
- Then run your app by going to the correct URL with a browser. For example, the URL I am using is:
http://cis2.stvincent.edu/carlsond/Test10/TwoTables.aspx.
- This too might not work at first. It depends on having the project folder (Test10 here) somewhere under
the webroot on your webserver. (In my URL, the carlsond folder is the mapped network drive on the web server cis2.stvincent.edu
where I created the project in Visual Studio in the first place. If you have no network drive for this, you can use Website,
Copy Web Site to copy your website files. You then click Connect, select an appropriate folder for the copied files, highlight
in the left panel the files to be copied, and click the right arrow to copy those files to the folder shown in the right panel.
You can later transfer these files to your web server, perhaps by ftp.
- In addition, IIS web server
needs to be told to use a certain application pool, one probably named ASP.NET v4.0, for your app.
Have your instructor or system administrator do this for you, as in the previous web app.
- Now check to see if the web form, accessed on the live web server, works correctly. Hopefully the answer is yes.
- In checking your app, be sure to try invalid input.
For example, for this input the resulting page after the postback should
show two validators with error messages as well as a popup window summarizing the errors.
The latter is due to the validation summary control that you put on the page.
- If you turned on error reporting or debugging in your Web.config file, turn them off now. To return your Web.config file to normal
(no debugging and no displaying of error messages in the browser), edit the file so that the customErrors and compilation
sections look like the following. Note that either On or RemoteOnly for customErrors will protect users not on the same
machine from the technical details of errors and protect your website from hackers
who would like to see those detailed error messages!
|
<compilation debug="false" strict="false" explicit="true" targetFramework="4.5"/>
<customErrors mode="On"/>
|
|
Search CIS Site
Tutorials
