CIS Logo SVC Logo

   Computing & Information Systems


Schoology Facebook        Search CIS Site      Tutorials

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
        /* SET NOCOUNT ON */ 

    • 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
        /* SET NOCOUNT ON */ 
        SELECT     PartNumber, Description, Type, Price
        FROM         Parts
        ORDER BY Type

    • 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 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, where we assume that your username (and hence your web folder) on is studentj.
    • Congratulations on completing your first database-related web application!

    Back to the main page for ASP .NET Web Apps

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