CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

Schoology Facebook        Search CIS Site      Tutorials

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.

Back to the main page for ASP .NET Web Apps



Author: Br. David Carlson
Last updated: October 08, 2008
Disclaimer