Reworked Web App Using a Database
The Goals for this Example
Our goal is to recreate essentially the same web app as the last one, but
to write our own VB .NET code to connect to the database, retrieve the data using the stored procedure,
and display this data in a GridView. Refer to this initial view of the web app
running in a browser and this view of the web app showing the auto parts data
after the user has clicked on the button.
What to Use from the Old Example
Copying the Old Example
- To save time, we copy the last example and then remove some of the autogenerated items.
- Begin by using My Computer to copy the PartsList project folder that you created in
the last example.
- Name the copied folder PartsList2.
- You can build and run the copy in Visual Studio if you wish to see that it is working OK.
|
What to Remove
- We want to remove some of the items that Visual Studio automatically generated for us.
Then we will use VB code to do pretty much the same thing.
- In Visual Studio, open your project's web.config file. Find the connection string.
We will leave this item in place. Plus, if we would write another web app that needs to
connect to this same CS305 database, we could copy this connection string to the new project's
web.config file. Here is what the connection string might look like, though the details
of yours will vary from this.
- Write down the name of your connection string, especially if it is not PartsConnectionString
as seen below. You will need to use this name in your VB code.
|
<connectionStrings>
<add name="PartsConnectionString" connectionString="Data Source=CIS-W2K8SERVER\SQL_CIS;
Initial Catalog=CS305;Integrated Security=True"/>
</connectionStrings>
|
- Although we could put the connection string into our PartsList.aspx.vb file, it is better to
have it in the web.config file. In a larger project we might have more than one VB file that
needs to use this connection string. If the connection string needs to be changed, you can
simply edit the web.config file; no rebuilding of the web app is needed.
After you have seen a few of these connection strings, you will be able to write your
own without using an autogenerated one.
For security, it is also possible to encrypt the web.config
file, thus hiding the connection string, though we won't do that here.
- Click on the GridView's smart tag in the upper right corner of the GridView. Change the
Data Source to (None). Visual Studio may ask if you want to keep or
delete column information for the GridView. If you keep it, this saves the names of the column headers
and the names of the Parts table fields to put into these columns. (Typically the column header names
are the same as the table field names.) If you remove this information from the GridView, then you
either need to see that the GridView's AutoGenerateColumns property is set to True, or you need
to manually supply the column information.
Warning: Failure to do one of these will result in a GridView
that does not show up on your running web app! The easiest option is the autogenerate one, but if
you wish to manually supply the information, do this: Find the GridView's Columns property and click
in the rightmost box. Then click on the ... button that appears. This brings up a dialog box
about the fields (columns) to be displayed. If the AutoGenerate box is checked, that does the
same thing as AutoGenerateColumns set to True. You can uncheck this box and supply the column
information manually. Add 4 databound fields to the list of selected fields. Under the
BoundField properties section of this dialog box, fill in at least
the Header Text and DataField for each.
Use the exact names of the 4 fields of your table for the DataField entries. That is how the
GridView knows what fields to place where in the grid. You can use the same names for the Header
Text or some abbreviation of these.
- Find the SqlDataSource on your PartsList.aspx form and delete it. (Click on it and press delete.)
It may be easier to go into Source mode and delete all the stuff from the opening
<asp:SqlDataSource ... to the closing </asp:SqlDataSource>.
- You should now have pretty much the same thing you would get by creating a new web form
and adding the labels, button, and GridView. True, we kept the autogenerated connection string,
plus there is a small VB code file, but we
will rewrite the VB code shortly.
|
Database Items
- We assume that you still have your login for SQL Server, your database, and the
Parts table within that databse.
- We also assumme that you still have your stored procedure named SelectAllParts.
Recall that we use stored procedures, not plain SQL, to make our web app less susceptible to
hacking attempts.
|
Getting the Data Using VB Code
- Before looking at VB code, add one more label to your form. Change its ID to ErrorLabel so that
we can refer to it in our code. Its text should read "Error in accessing the database". Change
the text to Medium, bold, and red. Change the Visible property of this label to False.
Should the VB code to access the database fail, we will then make this ErrorLabel visible.
- Let's add the new code in chunks to the PastsList.aspx.vb file (or whatever you named it),
discussing these chunks as we add them.
- All of the code will go inside the Page_Load procedure.
- Change the existing Page_Load code left over from the old example, so that it reads as follows:
|
If IsPostBack Then
Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
Dim myCommand As SqlClient.SqlCommand = Nothing
Dim partsDataSet As DataSet = Nothing
Try
ErrorLabel.Visible = False
ListAllButton.Visible = False
AllPartsLabel.Visible = True
Dim connectionString As String = _
ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
myCommand = New SqlClient.SqlCommand()
|
- Note that this code is not complete: The If is not finished. There is also an
opening Try without a closing "End Try". Recall that the Try construct is
used to handle exceptions.
- If the user clicks on the button, there will be a postback. That's when we want to
retrieve our data from the database and display it in the GridView.
- Other than setting up a few variables, we start by making sure that the ErrorLabel
is not visible. We might run our web app
a couple of times in a row and get a database error on the first attempt. Then, if the system
admin adjusts permissions, we might refresh the page and find that this attempt works,
so we do need to hide that error label.
- We also make hide the button that the user just clicked on and instead display the label
that says we are displaying the list of all parts.
- The next line shows how to get that connection string from the web.config file.
Note that the name PartsConnectionString is exactly the name we used for the connection
string in the web.config file. Important: If you used a different name for your connection
string, then change the VB code to use that name.
- Next, we set up a new database connection based on this connection string.
- Then we set up a new object to hold the command we want to execute on the database.
This can be used either with SQL or a stored procedure. We will use the latter, of course.
- To prepare this SqlCommand object so that we can execute the stored procedure, add this code
after the code we did above:
|
'Set up to use a stored procedure:
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Connection = myDatabaseConnection
myCommand.CommandText = "SelectAllParts"
|
- Note that this SqlCommand object is given command type StoredProcedure, the connection
it should use is listed as myDatabaseConnection (the one we just set up above that uses
our specific connection string), and the command text to use is SelectAllParts, the exact
name of our stored procedure.
- Now copy in the following code, right after where we left off inside of the
Page_Load procedure:
|
'Use a DataAdapter to execute the stored procedure and
'get the results into the GridView:
Dim adapter As New SqlClient.SqlDataAdapter(myCommand)
partsDataSet = New DataSet
myDatabaseConnection.Open()
adapter.Fill(partsDataSet)
myDatabaseConnection.Close()
myDatabaseConnection.Dispose()
PartsGridView.DataSource = partsDataSet
PartsGridView.DataBind()
PartsGridView.Visible = True
|
- We want to use a DataAdapter to execute the stored procedure, the one referenced inside
myCommand. Hence we have the line setting up the adapter object.
- We next set up a DataSet object named partsDataSet, open the database using our connection,
use our adapter to fill the partsDataSet, and then close the database connection right away.
We also displose the the connection object because we are then finished with it.
- It is generally best to open the connection to the database only when needed and to close it
as soon as you are done with it.
- The partsDataSet is used to hold a copy of the Parts table data retrieved by our stored procedure.
This partsDataSet is held in main memory. If the amount of data returned could be large, then
this might well not be the most efficient way of doing things, either in terms of memory use
or speed. In a later example, we will look at using a DataReader instead of a DataSet to
get a more efficient way of reading in this data.
- Once we have the desired data in partsDataSet, we use it as the data source for the GridView
on our form. We then to a DataBind on the GridView and make sure that the GridView is visible.
At this point, the data should be visible in the GridView on the form.
- Next paste in the last of the Page_Load code:
|
Catch exception As System.Data.SqlClient.SqlException
ErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally 'Do cleanup here:
myCommand = Nothing
partsDataSet = Nothing
If Not myDatabaseConnection Is Nothing AndAlso _
myDatabaseConnection.State = ConnectionState.Open Then
myDatabaseConnection.Close()
myDatabaseConnection.Dispose()
End If
End Try
End If
|
- If there is an exception, we make the error label visible.
- The Finally part of the Try constuct is executed after the code inside of the Try
is executed or is attempted to be executed (when something goes wrong and an exception occurs).
- The Finally section is the ideal place to do various cleanup tasks. Here we set the objects
we used to Nothing. We also check to see if myDatabaseConnection is not Nothing. If so and
its state is open, we close the connection and dispose of the object.
- For your convenience, here is a text file containing the complete VB code
for the Page_Load procedure.
|
Testing and Debugging The App
Finding Errors
- 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).
- 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 the above change 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 PartsList2 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 might be http://cis2.stvincent.edu/studentj/PartsList2/PartsList.aspx,
where we assume that your username (and hence your web folder) on cis2.stvincent.edu is studentj.
|
|