A Web App that Gets Data from a Database
The Goals for this Example
Our goal in this example is to learn the basics of writing a web app that gets data from an
SQL Server database. To do this, we need to learn a bit about how SQL Server works, how to
set up a connection from our web app to SQL Server, and other database-related topics.
Warning: With this type of web app there are many things that can go wrong. On the database
end there are logins, roles, and permissions that need to be set up correctly.
The system administrator for your server may have to adjust some of these for you.
Creating the Example
The Desired Web App
- We will create a web app that does a very simple lookup of all data in a database table.
- The idea is that the app is the web site for a nonsensical used auto parts store. The user
can click on a button to have all of the data on all of the auto parts displayed. We will
simply display the data on the same web form.
- In a later example, we will figure out how to search for particular data instead of getting it all.
- The initial view of the application shows a web page with a few labels and
a button.
- Once the button is clicked, the web app shows the auto parts data.
|
Starting the Web App
- In Visual Studio 2005, make a new project of type ASP .NET web app.
Use a reasonable location, such as your M drive. Use PartsList as the name of the project.
- Change the name of the web form, Default.aspx, to PartsList.aspx. (Recall that you do
this in Solution Explorer. One way is to right click on Default.aspx, select Rename, and then fill
in the desired name. This will also automatically rename the accompanying code file.
- Change the title of the web form to "Vintage Used Auto Parts".
- Save your work so far by using File, Save All.
|
SQL Server
- Get your SQL Server administrator to give you a login on
SQL Server 2005. This login should use Windows authentication.
Thus your Windows username and password will be used to access SQL Server.
In this presentation, the Windows account used was CISDEPT\carlsond.
- Note to the system administrator: Under server roles, this new login will have the public role
enabled by default. You may also want to check dbcreator, at least if you want this user
to be able to create a new database (instead of you creating the database for the user).
- Also have the administrator give you access to a particular database,
probably a new, empty database made just for you. Make sure that you know the name for
this database. In our example here the name used for the database is CS305.
Yours is likely to be different.
- System administrator note: Look at the login for this user, e.g. CISDEPT\carlsond,
and find User Mapping under Properties.
Make sure that the new database is checked for user CISDEPT\carlsond,
and that the schema is db_datareader. At the bottom, the database role membership for this
CS305 database should be db_datareader, db_owner, and public.
Also check the login for NT AUTHORITY\NETWORK SERVICE and go to User Mapping under its Properties.
Make sure that the new database is checked for NT AUTHORITY\NETWORK SERVICE and that the schema is
db_datareader. At the bottom, the database role membership for this
CS305 database should just be public. This is the login used for browsers running web apps.
- Another sys admin note: Find the CS305 database permissions and set them so that user
CISDEPT\carlsond (or whatever the account is) has these permissions granted: connect, create function,
create procedure, create table, execute, and select. Also, give the user NT AUTHORITY\NETWORK SERVICE
the permissions connect, execute, and select.
The first group of permissions is for the person
who is building the web app, whereas the second are for the web app itself when
it is running in a browser. For security reasons, you should keep the permissions given to
NT AUTHORITY\NETWORK SERVICE to a minimum.
Some additional permissions may need to be added later for these logins, but these should suffice
for our first several database-related web apps.
|
Visual Studio Database-Related Items
|
In Visual Studio make sure that Server Explorer is visible. If not, find it under View.
In Server Explorer, right click on Data Connections. The first time you write a web app
to access data in a database you need to select Add Connection. For later apps that use the same
database you can reuse the existing connection.
The Add Connection dialog allows you to select the Data Source (which should
be Microsoft SQL Server), the Server Name (which is CIS-W2K8SERVER\SQL_CIS in the example we
are presenting, but see your system administrator
for the server name that you should select for your server),
etc. Choose Windows Authentication. The drop-down box for selecting a database should be used
to choose your particular database. (Here, CS305 was used.) After this, click on the Test
Connection button. If it does not work, check with your system administrator. Otherwise, click OK.
Your next step is to create a new table in your database.
In Server Explorer, click on the + sign in front of your new connection so as to expand that node.
Within you will see folders for tables, stored procedures, etc.
Next, right click on the Tables icon and select Add New Table. This allows you
to add a new table to your database.
We are going to create a parts table for our used autoparts store.
Fill in the 4 fields with the types, lengths, etc. as shown here:
|
| Column Name |
Data Type |
Allow Nulls |
| PartNumber |
nchar(10) |
no |
| Description |
nchar(60) |
yes |
| Type |
nchar(20) |
yes |
| Price |
money |
yes |
- Right click at the front of the PartNumber field and make the selection to designate this field
to be the primary key.
- Do File, Save to save this table. You will be prompted for a name. Name the table Parts.
- You can now close the window where we set up the fields for this table.
- Next we add data to this Parts table. Begin by right clicking on the table in Server
Explorer. Select Show Table Data.
- In the resulting window you can fill in data for this table. Use what is
shown in this image and also listed below:
|
| PartNumber |
Description |
Type |
Price |
| 120 |
From 1977 Chevy Nova. Like new! |
muffler |
29.99 |
| 128 |
From 1966 Volkswagon Beetle. |
muffler |
9.98 |
| 155 |
From 1966 Volkswagon Beetle. |
engine |
450 |
| 226 |
From 1977 Chevy Nova. Collector's item! |
steering wheel |
12.55 |
| 333 |
From 1977 Chevy Nova. Slightly cracked. |
fan belt |
8.88 |
- You may now close the window where you entered the data. The data is saved automatically.
- You can right click on your table and select Show Table Data at any time that
you want to adjust the data.
- Similarly, you can right click on your table and select Open Table Definition if you wish to change
the design of the table. This could be used to add another field, for example.
- Next, we want to make a stored procedure, compiled SQL code that we can use to retrieve data
from our database.
- We will always use stored procedures, not ordinary SQL statements.
The reason for this is that stored procedures are faster and more secure.
(Attackers love to use SQL injection attacks against a database. In particular, if your
web app allows user input as part of an SQL query, the attacker may be able to append
extra SQL as part of the input in just the right way to make the database return data
that you never intended to be seen by any user. Stored procedures can limit this problem.
- Begin by right clicking on Stored Procedures in Server Explorer.
Select Add New Stored Procedure.
- This will give you an outline of a stored procedure.
- To see this better, here is the outline:
|
CREATE PROCEDURE db_datareader.StoredProcedure1
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN
|
- The parameter section is commented off. Since we don't need any parameters to this
stored procedure, you can even delete this section.
- Place the mouse at the start of the line that says RETURN. Press Enter to get some room to add code.
Right click at this spot and select Add SQL. This will bring up a query builder where
you can create the SQL that should get compiled into your stored procedure.
- Add the Parts table to the query builder.
- Check each of the 4 fields so that they will be selected by the SQL.
- Also click in the Sort Type column for the row (field) named Type. In the drop-down
choose Ascending so that the data will be presented in ascending order by Type.
- Click on Execute Query to test the SQL. The resulting data set will be shown
at the bottom of the window.
- Click OK. Change the name StoredProcedure1 to SelectAllParts.
- Make sure that the code now reads as in the following listing. If not, edit it manually
to make it match this. Be very sure that the RETURN is the last line of code.
If you do RETURN earlier on, the last part of the stored procedure will never be executed.
|
CREATE PROCEDURE db_datareader.SelectAllParts
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
SELECT PartNumber, Description, Type, Price
FROM Parts
ORDER BY Type
RETURN
|
- Do File, Save to save your stored procedure.
- This stored procedure should now show up under Stored Procedures for this data connection in
Server Explorer. Right click it and select Execute. This will show you the
data returned by the stored procedure.
- To be more accurate, the code that we wrote above is the code to create a stored procedure that
contains the desired SQL. After you execute it once, note that the word CREATE changes to the
word ALTER. That allows you to adjust the SQL, select Execute again, and get the SelectAllParts
stored procedure altered.
|
Getting Data into the Web App
- In Solution Explorer, double click on your PartsList.aspx file and view it in Design mode
- Drag 3 labels and a button from the Standard section of
the Toolbox and place them on your web form.
- Adjust them so that the results match what we want in our
initial view of the web app.
- Change the ID of the button to be ListAllButton. That will make it easier to refer
to this button later if we need to do so.
- Drag a GridView from the Data section of the Toolbox to the form. Place it underneath the button.
We will use this GridView to hold the data that we look up in the Parts database.
- Click on the GridView's smart tag. This little tag appears at the top left corner of the
object when the mouse is in its vicinity. Under autoformat select Colorful.
- Set the visible property of this data grid to False since we don't want to see it initially,
but only after the user clicks on the button.
- Drag another label onto the form, just to the right of the button (or even on top of it),
and rename it as AllPartsLabel. The text for it should be "List of All Parts". Set its
visible property to false.
- This screen shot shows this last label and the button overlapping,
as well as the first label and the SqlDataSource overlapping. This is one way to get things to
appear where you want them, but it can be a little confusing to look at when designing the form.
- Use the GridView's smart tag again. This time, in the Choose Data Source drop-down box,
select New Data Source.
- Click on Database and OK.
- The next screen will allow you to choose your existing data connection in a drop-down box.
Do so as seen in this screen shot. Then click Next.
- The following screen allows you to
save the connection string in the configuration file for your
web app. It will be helpful to save it under the name PartsConnectionString, as future example
web apps will use that name, not the name shown in the screen shot. Do so and click Next.
- In the resulting Configure the Select Statement dialog box, select the first radio button,
the one that says "Specify a custom SQL statement or stored procedure". Click Next.
- On the next screen, select the radio button for Stored Procedure and pick the SelectAllParts
stored procedure in the drop-down. Click Next.
- Click the Test Query button on the next screen. It should retrieve the
same data that we saw above. Click Finish.
- Now, our simple web app automatically calls the stored procedure and places the results into
the GridView. The only Visual Basic code we write is to adjust what items are visible on the
form. In this app, the data is in the GridView as soon as we load the app, but we don't make
the GridView visible until the user clicks on the button.
- To write this code, go to Solution Explorer. Make sure that you have clicked on the Show All Files
button, so that you can see all of the files. Find and double click on PartsList.aspx.vb to bring it
into the editor.
- Change the Page_Load procedure so that it looks like this screen shot.
For convenience, the code is repeated here as well:
|
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'If there is no postback (e.g. the first time the page loads), nothing
'special needs to be done. But...
If IsPostBack Then
'Page was reloaded, for example, when user clicked the button.
ListAllButton.Visible = False
AllPartsLabel.Visible = True
PartsGridView.Visible = True
End If
End Sub
|
Build Your App
- Click on the Save All button in Visual Studio or use File, Save All.
- Then use Build, Build PartsList (or whatever you named your application).
- If the build succeeded, the Output window will give you a message to that effect.
- If not, fix any problems.
|
Testing and Debugging The App
Finding Errors
- Use Debug, Start Without Debugging to run your web app locally.
- Click on the List All Parts button and see what happens.
- If it does not work, you might try setting the Web.config file so that you can see
any error messages, as we have done in previous web apps.
Double click the Web.config file in Solution Explorer and add the line
<customErrors mode="Off" />
- Save everything, rebuild your application, and refresh the initial page in your browser.
- See if you get an error message now when you click on the button.
- Often it is a matter of getting the permissions right on the stored procedure if you
get an error at this point. Note that the stored procedure may work fine when you preview
the data in Visual Studio, yet not work when called from your web app. This is because the
stored procedure is run by one user (such as NT AUTHORITY\NETWORK SERVICE)
when the web app calls it, but is no doubt run
by a different user (your Windows account) when called from Visual Studio.
Consult your system admin or another
.NET web developer if you cannot get rid of an error at this point.
- When finished with debugging, undo all of your changes to Web.config, save everything,
and rebuild your app. This will leave your app in a more secure state. If a runtime
error crops up later, you don't want everyone on the Internet to be able to read the
detailed error messages.
|
Publishing Your Web App
- Close the browser that is displaying your web app running locally.
- Click on Build, Publish and publish your app to the web server
to which you have access. For example, in CS 305
at Saint Vincent College, you can publish to your W drive,
which is your web folder on the cis2.stvincent.edu
server. Click on the Create Folder button to make a new folder
to hold your web app. Use a name such as PartsList for the folder.
- Have your web serve administrator convert your project to an IIS web app.
- Now try looking at your web app by going to the appropriate URL
for the above web page. For the example described
above, the correct URL would be http://cis2.stvincent.edu/studentj/PartsList/PartsList.aspx,
where we assume that your username (and hence your web folder) on cis2.stvincent.edu is studentj.
- Congratulations on completing your first database-related web application!
|
|
Search CIS Site
Tutorials
