CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

Schoology Facebook        Search CIS Site      Tutorials

A Quick Web App to Get 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. Because of all of these database-related details, this example will not seem to be quick, but once we get past these database details, creating the web app is very short and easy.

Warning: With this type of web app there are many things that can go wrong. On the database end there are logins, users, roles, etc. The SQL server database may be running on a different server than your web server. Meanwhile you are probably doing your software development on a PC. All of this complexity gives many possible points of failure.

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. Specifically, this will be a table of product information.
  • In later examples we will see 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 product data.

Starting the Web App

  • In Visual Studio 2012 or later, open the solution that we have been using in this series of examples, the one that contains your web site set up in the first example of this tutorial. You can probably find this solution under File, Recent Projects and Solutions.
  • Add a new form to your web site and set its name to Quick.aspx.
  • In Design View, drag the bottom edge of the body so as to get a reasonable rectangle, then drag the edges of the form and div so that they almost fill the body, but the form is contained in the div, which is contained in the body.
  • If you did not catch that name in time, you can change the name of this web form, Default.aspx, to Quick.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-behind file to Quick.aspx.vb.
  • Change the title of the web form to "Quick Display of SQL Server Data" or similar. The easiest way to do this might be to type the words after the opening title tag in source view.
  • 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 2012 (or whatever version of SQL Server you have). This login should use Windows authentication. Thus your Windows username and password will be used to log in at your PC (to a domain controller in most cases). Once logged in, this account has automatic access to 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. Under Securables, you should probably check Connect SQL and View any definition.
  • 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. (A good scheme is to use the same name as your Windows username.) In our example here the name used for the database is carlsond.
  • System administrator note: Look at the login for this user, e.g. CISDEPT\carlsond, and find User Mapping under Properties. Make sure that the newly-created database is checked for user CISDEPT\carlsond or whatever the login name is. At the bottom, the database role membership for this database should be db_owner and public.
  • Also ask your system administrator for a new login and username (with the password that goes with it), both here named webapp (but you might use the username followed by web app, as in carlsondwebapp). This login will be used by your web app whenever it needs to access your SQL Server database. Notes for the system administrator: Create this new login by right clicking on the Security Folder for all databases and choosing New Login. Fill in a password and have this login use SQL Server authentication. Turn off Enforce password policy, as you don't want this person's web apps to quit working because the password has expired. The default service role value of public is fine. Under User Mapping, check this user's database. The user name filled in will automatically be the same as the login name, which is good. This will create a new user under this particular person's database and having the same name as the login name. Also under User Mapping, make sure that public, db_datareader and db_datawriter are checked (and only these are checked). If you are certain that the web apps will not need to write to the database, you could omit db_datawriter. Under Securables, the server itself should be listed in the Securables section, and under the explicit permissions, View any definition (granted by sa) and Connect sql (granted by sa) should be checked.
  • From this point on, you yourself can do much of the work of administering your particular SQL Server database. This can be done using Server Explorer (along with the similar SQL Server Object Explorer) within Visual Studio and by means of the separate SQL Server Management Studio. (Note that sometimes these refuse to connect to our server. Often pinging the server first and then trying to connect leads to success. Just go to a command prompt in Windows, enter the command ping 10.25.1.43, and then see if you can connect to SQL Server.) Here is how to start a connection using both pieces of software mentioned above:
    • Start SQL Server Management Studio. At its logon screen, the Server Type should be Database Engine, and the Server Name should be 10.25.1.43 (or the symbolic name mssql.stvincentcisdept.com). Users outside of Saint Vincent should substitute the IP address or name for their server that is running SQL Server. (The IP address is probably more reliable, as it is common to have problems in getting a symbolic name resolved to an IP address.) The type of authentication should be Windows Authentication, and the Username should be something like CISDEPT\carlsond. Click the Connect button, and you should be in. Under Databases, expand the node for your particular database (carlsond in this case) and then expand the Security node under this. Under this one, expand the Users node and make sure that you have the carlsondwebapp user that you asked for above. If you wish to use SQL Server Management Studio to create tables, etc. leave this software open.
    • To work on your database from Visual Studio itself, start with Server Explorer. We need to get it to connect to your database. If Server Explorer is not visible, look for it under the View menu. In Server Explorer, right click on Data Connections and select Add Connection. Choose Microsoft SQL Server as the data source and .NET Framework Data Provider for SQL Server as the data provider. Click Continue and fill in the data connection information: Microsoft SQL Server (SQL Client) as the data source, mssql.stvincentcisdept.com as the server name, use Windows Authentication, and carlsond (substitute your username) as the name of the database to connect to. Click the Test Connection button to see that the connection works. If not, try the IP address (10.25.1.43 in our case) instead of the symbolic name. If it still does not work, consult your instructor or server administrator. Once connected to your database, you can see your tables and other items. For example, you can right click on any table and select Open Table Definition to see how a table was designed, or you can right click on a table and select Show Table Data to see the data within that table.

      Next, in Server Explorer, right click on your connection string (under Data Connections) and select Browse in SQL Server Object Explorer. In the Databases section of SQL Server Object Explorer, find your particular database (such as carlsond) and expand its node so that you can see the subnodes for Tables, Views, etc. You can expand the Tables node to see if you have any tables currently. If so, you can right click on a table and select View Designer to see how the table was defined, or you can right click on a table and select View Data. Note that Server Explorer and SQL Server Object Explorer both provide you much of the same functionality. However there are differences, such as where some items such as Stored Procedures appear in the tree of nodes.

Working with Database Tables in Visual Studio

  • Your next step is to create a table in your database. In Server Explorer, expand as needed the tree of nodes beginning at your new connection (the one for carlsond or whatever it is named). Within you will see folders for tables, views, stored procedures, etc.
  • Next, right click on the Tables node and select Add New Table. This allows you to add a new table to your database. This will bring up a design view for creating a new table. We are going to create a Product table for use by your web app.
  • Fill in the 3 columns with the names, types, etc. shown here. Leave the default column empty.
Name Data Type Allow Nulls
ProductNum int not checked
ProductName nvarchar(20) checked
Price money checked
NumInStock int checked

  • Right click at the front of the ProductNum field and make the selection to designate this field to be the primary key.
  • Right click again at the front of the ProductNum field and select Properties. In the Properties window, expand Identity Specification. Change (IsIdentity) to True. (The quick way is to double click the value of this field.) The default values of 1 for both the Indentity Seed and Identity Increment are perfect. This will cause the values in the ProductNum field to automatically be 1, 2, 3, etc.
  • Below the design view should be a T-SQL view showing the SQL code that will be run to create your table. It probably begins with CREATE TABLE [dbo].[Table], but manually change Table to Product, ProductTable, or similar so that the name of the table gives you an idea of what it contains.
  • See this image of design and T-SQL views for what you should have at this point.
  • Click Update in the upper left corner of design view. Then click Update Database. You should now have a new, but empty table.
  • You can now close the window where we set up the fields for this table.
  • Next we add data to this 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. The ProductNum field is an identity field, so its values will automatically be filled in. For the other fields, enter the data that is shown below:
ProductNum ProductName Price NumInStock
1 Flatscreen monitor 123.95 5
2 Keyboard 12.99 125
3 Mouse 6.98 37

  • 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.

Getting Data into the Web App

  • Now we turn to the promised quick creation of the web app. So far we simply created an empty web form named Quick.aspx.
  • In design view for this form, check that the div rectangle is big enough to hold the items we see in our target result for the web app after it displays its data.
  • Drag a panel from the Toolbox and drop it inside of the div. Drag the bottom edge of the panel so that the panel encompasses about two-thirds of the div. Then, while the panel is selected, click on Format, Set Position, Absolute so that we can easily move the panel around. Move the panel to the bottom two-thirds of the div. Check in design view to verify that the panel is still inside of the div.
  • Drag a label from the Toolbox and put it at the top of the div. While it is selected, click on Format, Set Position, Absolute so that we can move it around as needed. Make the text of the label be "Display of a Table from an SQL Server Database" and make the font extra large and bold.
  • Put another label on the form after the first one and set it to use absolute positioning. Drag it so that it is underneath the first label. Use "This is a quick solution created by dragging the table from SQL Server Object Explorer to the form in Design View." or similar as the text.
  • Place a third label on the form after the other two and have it use absolute positioning. Drag it so that it is underneath the second label. For its text, use "All product data:" and set the label's visible property to False, as we do not want to see this label when we initially go to this page in a browser.
  • Drag a button from the Toolbox and place it right after the third label. Set it to use absolute positioning as well. Then drag it to the right of the third label. Use "Show Product Data" for the Text on the button.
  • If need be, drag the labels and button around so that they are arranged reasonably, as in this image of the form in Design View.
  • Now we come to the key step in getting quick access to the data from our Product table. In Server Explorer, find your Product table and drag it onto the panel within your form. Just drop it there and Visual Studio creates a grid view to display your data, along with an SqlDataSource appropriate to the situation.
  • The previous step will probably leave you seeing the menu of GridView tasks. If not, click on the grid view and then on the little arrow button on its upper right corner to get the smart tasks menu. Select AutoFormat, Colorful, and OK.
  • Click on the grid view to select it and look at the Properties window. Notice that its DataSourceID is set to the value SqlDataSource1, the ID of the data source automatically put on the form for you. Set the grid view to be invisible by changing its Visible property to False.
  • If the position of the grid view is not what you want, click on it, then click Format, Set Position, Absolute so that you can drag it to a better position within the panel. Note that you cannot set the positioning to absolute for the SQL data source and cannot move it around. This is not a big problem as it will be invisible in the running app.
  • Check the ConnectionString property of the SQL data source. Notice that it uses your connection string to connect to your database, something like "Data Source=mssql.stvincentcisdept.com;Initial Catalog=carlsond;Integrated Security=True". Integrated Security means that this app is using Windows authentication in connecting to the database. This connection string is fine for developing your app and running it locally on your PC, but it will not work if the app is run from a separate server. Leave this alone for now. We will fix it later.
  • Double click on a blank spot on your form in Design View to create the outline of a Page_Load subroutine. Do not double click on any item that is on the form, such as on the panel or the div. In the code-behind file Quick.aspx.vb you should now see the following:

Partial Class Quick3
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    End Sub
End Class

  • Add code to process the Page_Load event so as to obtain the following.
  • Note that nothing happens when the web page is first loaded, but when the user clicks on the button a postback to the server occurs and the code within the IF is executed, making the grid view and its label visible, while the button becomes invisible. Thus the user can now see the data in the grid view.

Partial Class Quick
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If IsPostBack Then
            'Page is being reloaded, for example, because the user clicked the button.
            Button1.Visible = False
            GridView1.Visible = True
            Label3.Visible = True
        End If

    End Sub
End Class

Build 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 not, fix any problems.

Testing and Debugging The App



Finding Errors

  • Use Debug, Start Without Debugging to run your web app locally. (Alternatively, you can click on the button with the little green triangle and then select the option to run without debugging.) This starts a simple version of the IIS web server on your PC and has your browser retrieve the web page from that local web server.
  • Click on the button in your web app and see what happens. You should see the grid of data from the Product table.
  • If it does not work, you might try setting the Web.config file so that you can see any error messages and/or run the debugger to step though lines of code. The latter is not too helpful in this case as this app has very little code.
  • To see errors in your browser, double click your web.config file in Solution Explorer and add the line <customErrors mode="Off" />. With custom errors turned off you will be able to see normal error messages after rebuilding your page and starting it again in a browser.
  • Before running the debugger, set a break point by clicking in the left margin in front of the first IF in Quick.aspx.vb.
  • Select Debug, Start Debugging. You will be asked whether to modify the web.config file to enable debugging. Click OK.
  • Your app will start in a browser. When the breakpoint is reached you will be shown Visual Studio, in which you will now have several buttons for debugging. If you hover over them with the mouse, you will see that there are buttons for "continue", "step into", "step over", "step out" "stop debugging" and "restart". Stepping over lines of code is perfect for this program. If you reach a subroutine call, then you can use step into to go inside that routine's code. Once in that routine, you can use step out to exit that routine. You should also have a window that shows you the values of local variables, one that shows the call stack, and perhaps others. If you do not see the windows you want, look under the Debug menu, and then Windows, to see what windows you can show.
  • With or without the aid of the debugger, you hopefully now have your app working. 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 this:

      <compilation debug="false" strict="false" explicit="true" targetFramework="4.5"/>
      <customErrors mode="On"/>

  • When finished with debugging and putting web.config back to normal, 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.

Running Your Web App on a Separate Web Server

  • Close the browser that is displaying your web app running locally.
  • To run your app on a separate web server, you need to use a connection string that will work from that server. This is why you asked your system administrator for a second login on SQL Server called something like webapp, carlsondwebapp, etc. This is the login that used SQL Server authentication, not Windows authentication.
  • Open your web.config file. In the connectionStrings section you will see your existing connection string, the one that uses Integrated Security (i.e. Windows authentication). It may use a symbolic name or an IP address for the Data Source. Add a second connection string as in the example below. Note that we called it carlsondwebappConnectionString and that it uses the same data source (server) and intitial catalog (database). However, instead of Integrated Security it contains the user id and password for connecting to the SQL Server database. Although asterisks are shown in place of the password below, you must actually type the password at this spot. There is a way to encrypt this connectionStrings section so that someone who manages to see your web.config file still does not learn the password, but we do not go into those details here. Note that each connection string must be on a single line. In the box below, each connection string was split into two lines for readability only.

<connectionStrings>
   <add name="carlsondConnectionString" connectionString="Data Source=mssql.stvincentcisdept.comInitial Catalog=carlsond;
      Integrated Security=True" providerName="System.Data.SqlClient"/>
   <add name="carlsondwebappConnectionString" connectionString="Data Source=mssql.stvincentcisdept.com;Initial Catalog=carlsond;
      User Id=webapp; Password=**********" providerName="System.Data.SqlClient"/>
</connectionStrings>

  • Save all of your files in Visual Studio.
  • At Saint Vincent, students were instructed to place their web sites on their W drives, which in effect puts them in each user"s folder within the web root for the IIS web server software on our server. However, trying to run our little database app using that web server will not work with the personal connection string for each user. Try it. In a browser go to http://cis2.stvincent.edu/carlsond/Wrox/Quick.aspx, or whatever the correct address would be for your particular situation.
  • To fix this, edit your Quick.aspx file in Visual Studio as follows: Click on the SQL Data Source in either design view or source view. In the Properties window, find the Connection String property. Click on the right where the value of this property appears. In the pull-down that results, select the new connection string carlsondwebappConnectionString (or whatever yours is called).
  • You will also have to have your instructor or server administrator convert your website folder into an official IIS web app that uses the correct application pool. (For us that pool is the Classic Application Pool.)
  • Save all of your files again, build the page, and then try the http://cis2.stvincent.edu/YourUserName/FolderName/Quick.aspx URL (suitably modified to fit your situation) in your browser. Hopefully, this time the app will display data when you click on the button!

Improvements

  • It is suggested that you use the color scheme found in the first line of the grid view, the one containing the column headings, and use that scheme for the first label, the one at the top of your form. You can find the background color (probably #990000) by selecting the grid view and looking under HeaderStyle in the Properties window. Expand HeaderStyle so that you can see the value of BackColor. Also under HeaderStyle you will see the value of ForeColor (probably White). Go to the properties of the first label and fill in these 2 colors as BackColor and ForeColor for this label.
  • If you have looked carefully at the prices displayed in the grid view, you may have noticed that they show more than two decimal places. You can display the normal two decimal places as follows: Click on the grid view to select it and then in the Properties window find the Columns property. It should show that the value of this property is a collection. Click in the space where the value (Collection) is displayed and then on the ... button that then shows up. In the resulting collection editor, click on the Price field under Selected Fields. In the Data section of the properties that are then displayed on the right of the collection editor, find the DataFormatString property and type in {0:c} as its value and then click OK. The c stands for currency format and the 0 represents the first number in this field (and the only number in this case).
  • Save all of your files, build the page, and try it out in a browser (using the above URL).
  • Congratulations on completing your first database-related web application!

Back to the main page for ASP.NET 4.5 Web Apps



Author: Br. David Carlson
Last updated: September 10, 2017
Disclaimer