Accessing a Database Through ASP.NET

At the heart of any database application is, of course, a database. To use a database, you must first have a way to connect to it reliably and somewhat securely. This capability is provided through the System.Data namespace and is usually a simple string.

A number of difficulties are associated with accessing a database. One difficulty that you may not be aware of is that the ASPNET user doesn't have rights to do anything on the database, including even the ability to execute a SELECT on a table. Another frequently encountered problem is the storage of names and passwords of system administrator-level users in plain-text in a file that typically the Web server would not display to outside users. For example, it isn't uncommon in viewing the source of an ASP page to find code such as

 Set Conn = Server.CreateObject("ADODB.Connection")  Conn.Open("server=myServerName;uid=sa;pwd=") 

For the examples presented in this chapter, the TRUSTED_CONNECTION = YES option is used. Using a trusted connection means that the user is authenticated to Windows and that authenticated account information also exists within SQL Server. In most real-world situations, you wouldn't add the ASPNET account to SQL Server unless you were going to restrict the functionality provided to this user to SELECT only.

Note

The ASPNET user is the default identity used by anonymous Web requests performed against Internet Information Server when the .NET Runtime is installed.

Adding the ASPNET User to the SQL Server Logins

Again, to make the TRUSTED_CONNECTION option work, a Windows account must exist, and that account must be added to the SQL Server logins. Also, the SQL Server must be set up to allow Windows Authentication. Even though we covered most of this process in a previous chapter, we present it here to show how a direct relation to managing the ASPNET account within SQL Server allows for the TRUSTED_CONNECTION option to be used. We now demonstrate how to do so for the rare case mentioned previously, where we let the ASPNET user execute SELECT statements against the Novelty database.

1. Open SQL Server Enterprise Manager and navigate to the Security node and expand it, as shown in Figure 11.1

   Figure 11.1. SQL Server Enterprise Manager Security node

   

2. Right-click on Logins and select New Login. A dialog similar to that shown in Figure 11.2 appears.

   Figure 11.2. Login dialog

   

3. On the General tab, at the top of the login dialog is a textbox for inserting the user. At this point, either click on the ' … ' button to browse for a user or enter the user's name in the format of machinename\username or domain\username. Use of the browse functionality is shown in Figure 11.3.

   Figure 11.3. General tab Selecting a user

   

4. Scroll down the list to find the ASPNET user entry and double-click on it. Then click on OK on the browse dialog.

5. At the bottom of the General tab, click on the drop-down list labeled "Database" and change it to Novelty, as shown in Figure 11.4

   Figure 11.4. General tab Selecting a database

   

6. Next click on the Database Access tab. Click on the checkbox beside the Novelty database only. Also, under Permit in Database Role at the bottom of the dialog, select public. These steps are shown in Figure 11.5

   Figure 11.5. Database Access tab Setting a database and role

   

7. Click on the public role and click on the Properties button located beside the Permit in Database Role list. A dialog similar to that shown in Figure 11.6 is presented.

   Figure 11.6. Properties for public role

   

8. Click on the Permissions … button at the top right of the dialog. Doing so opens the low-level permissions that we're going to modify. Figure 11.7 shows the resulting dialog.

   Figure 11.7. Permissions dialog for public role

   

9. Be sure that the option List all objects is selected and scroll to the bottom of the list, where the tables from the Novelty database will show up. For each table listed, check the box in the SELECT column, as shown in Figure 11.8. Click on it only once to allow the user to perform a SELECT; clicking on it twice causes a red X to appear, indicating that access to that table is explicitly denied.

   Figure 11.8. Table-level permissions dialog

   

10. At this point, you should also put a check in the Insert column for the tblOrders table, as shown in Figure 11.9. You wouldn't want potential customers not to be able to buy anything.

    Figure 11.9. Setting INSERT on tblOrders

    

11. For each open dialog window, simply click on OK. There should be about three of them. Now the ASPNET user will appear in the Logins windows of SQL Server Enterprise Manager, as shown in Figure 11.10.

    Figure 11.10. SQL Server Enterprise Manager Logins window

    

At this point, you have successfully added the ASPNET user to the SQL Server logins. In the next section, we use this account to connect to the database, using a connection string that doesn't expose any login information.

TRUSTED_CONNECTION in Action

We now show how to use TRUSTED_CONNECTION to connect to the database and execute a simple query through ASP.NET. The setup for this example is easy: Create a new VB.NET ASP.NET WebForms Application and name it Novelty.

The first thing to do is to rename the default WebForm1.aspx file as default.aspx by simply right-clicking on the WebForm1.aspx file and selecting Rename. Then, in the highlighted area, type "default.aspx" to make it the default page for the directory. (We will make many changes to this page as we experiment through the rest of this chapter.)

For starters, though, look at the code in Listing 11.1. It is a very basic example of connecting to the database with the ASPNET user account, executing a simple SELECT query, and then displaying the results on a Web page.

Listing 11.1 default.aspx.vb

 Imports System.Data Imports System.Data.SqlClient Public Class WebForm1      Inherits System.Web.UI.Page #Region "Web Form Designer Generated Code" 'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub_InitializeComponent()      End Sub      Private Sub Page_Init(ByVal sender As System.Object,_ByVal e As System.EventArgs)  _Handles MyBase.Init      'CODEGEN: This method call is required by the Web 'Form Designer.      'Do not modify it using the code editor.      InitializeComponent()   End Sub #End Region   Dim connString As String   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As        System.EventArgs) Handles MyBase.Load      'Set the connection string      connString = "server=(local);database=Novelty; TRUSTED_CONNECTION=Yes"      'This is all the information we need      'to connect to the database. Also, if      'someone were to ever find this file      'in a raw format, he could not use the      'information to attempt to login to the database.      'Intern the string. This causes .NET      'to check to see if the string exists      'in the memory heap. If it does not, it      'will be put there. Otherwise, it uses      'the instance already in memory instead      'of creating another one.      String.Intern(connString)      ShowCustomers()   End Sub   Private Sub ShowCustomers()      'This is just a simple function to get      'things started, showing the collection      'and displaying tblCustomer.      'Initialize the connection object with      'the connection string.      Dim conn As New SqlConnection(connString)      'Also, initialize the command object with      'the SQL to be executed.      Dim cmd As New SqlCommand("SELECT * FROM tblCustomer", conn)      conn.Open()      Dim dReader As SqlDataReader =cmd.ExecuteReader (CommandBehavior.CloseConnection)      While dReader.Read        Response.Write(dReader.GetString(1))        Response.Write("&nbsp;" & dReader.GetString(2))        Response.Write("<BR>")      End While      dReader.Close()      conn.Close()   End Sub End Class 

Note that in Listing 11.1 the System.Data and System.Data.SqlClient namespaces are included. These two namespaces provide the classes and functionality needed to connect and query the database. Also note that Listing 11.1 is a code-behind page. That is, the actual file, default.aspx, has no real code to speak of; it's just there to present what we tell it to from the code-behind page. We illustrate this concept in Listing 11.2, which shows the entire code from the file default.aspx.

Listing 11.2 default.aspx

 <%@ Page Language="vb" AutoEventWireup="false" Codebehind="default.aspx.vb"_  Inherits="Novelty.WebForm1" %> <!DOCTYPE HTML PUBLIC "_//W3C//DTD HTML 4.0 Transitional//EN"> <HTML>   <HEAD>     <title>WebForm1</title>     <meta name="GENERATOR" content="Microsoft Visual _Studio.NET 7.0">     <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">     <meta name="vs_defaultClientScript" content="JavaScript">     <meta name="vs_targetSchema" content=_ "http://schemas.microsoft.com/intellisense/ie5">   </HEAD>   <body MS_POSITIONING="GridLayout">     <form  method="post" runat="server">     </form>   </body> </HTML> 

At the top of Listing 11.2 is a directive. It tells the ASP.NET execution engine that a code-behind file of default.aspx.vb is being used. When you use the Build and Browse functionality usually accessed by right-clicking on an aspx file in the Solution Explorer and selecting Build and Browse within VS.NET to view this page two things happen. First, the page is compiled into a .dll; from that point on, whenever the page is requested, ASP.NET will use its compiled copy of the page. Second, a browser window will open, showing the results of executing the code. Figure 11.11 shows the resulting page.

Figure 11.11. default.aspx results

Note

All the code examples presented in this chapter are available from the publisher's Web site, http://www.awprofessional.com.

Working with the DataGrid

When Microsoft released early versions of the .NET Framework SDK, the samples of how to iterate through a collection of data were shown by use of a DataGrid User Control. A User Control has little or nothing to do with an ActiveX control from days past. A User Control for use on a WebForm indicates the use of some kind of templated functionality that executes server-side code to produce HTML for the client. This technology is very powerful as it allows development of complex logic related to the user interface into a reusable, compiled object that doesn't have any security worries or, worse, browser incompatibility issues. As Server Controls are written to produce HTML, browser compatibility is up to the developer.

The DataGrid control is an excellent example of a control that allows the developer simply to bind a DataSet to a DataGrid and get instant visual results. The code presented in Listing 11.3 shows the basic query in Listing 11.1 being used to fill a DataGrid control. To use any sort of Server Control, you must declare it. Once you've declared it, you can access its methods and properties just as you can any other object on the page. To begin, we created a new WebForm in Visual Studio.NET and named it WebGrid.aspx. Then, we placed a DataGrid control on the page, using the available controls from the WebForms tab of the toolbox.

Listing 11.3 WebGrid.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebGrid.aspx.vb" Inherits="Novelty1.WebGrid"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML>      <HEAD>        <title>WebGrid</title> <%Dim connString As String    connString = "server=(local);database=Novelty;TRUSTED_CONNECTION=Yes"    Dim conn As New SqlConnection(connString)    Dim cmd As New SqlCommand("select * from tblCustomer", conn)    conn.Open()    Dim dReader As SqlDataReader = cmd.ExecuteReader (CommandBehavior.CloseConnection)    DataGrid1.DataSource = dReader    DataGrid1.DataBind    dReader.Close()    conn.Close() %> <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0"> <meta name="CODE_LANGUAGE" content="Visual Basic 7.0"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body MS_POSITIONING="GridLayout"> <form  method="post" runat="server"> <asp:DataGrid  style="Z-INDEX: 101; LEFT: 179px;   POSITION: absolute; TOP: 73px"   runat="server"   Width="640px"   Height="480px"   BackColor="#fffff5"   BorderColor="black"   ShowFooter="true"   CellPadding="1"   CellSpacing="1"   Font-Name="Arial"   Font-Size="8pt"   HeaderStyle-BackColor="#c0c0c0"   EnableViewState="false"> </asp:DataGrid> </form> </body> </HTML> 

When this code is executed against the Novelty database, the results should be similar to those shown in Figure 11.12.

Figure 11.12. WebGrid.aspx results

This server control provides a way to display the data without writing a single line of code related to the actual logic of displaying the data. Again, to clarify, server controls basically are made up of pagelets. They aren't complete aspx pages (in fact, they have an .ascx extension), but only specific sections, such as a reusable form or code that could be used to show an error message provided on a common error page. You can create these types of controls to meet any need for specific functionality embedded in a Web-based user interface.

Note

In an environment of dynamic content, certain variables often remain static. This is usually the case with a database connection string. In Listing 11.1, the connection string is interned. That is, it checks the .NET Runtime's memory heap to see if an instance of the string "connString" exists. And, if it does, does it have the same value as this instance? If it does, use the instance of the string that is already in memory instead of creating a new string object and filling it with the value given. If it doesn't exist in memory, put it there with the current value assigned to it. This approach actually conserves resources because the CLR performs these checks faster than it can allocate memory for a new string. Throughout a project, you can reuse many strings in this manner, thus enhancing performance of the application.

You shouldn't use this method for strings that frequently change because there's no use in checking for an existing value if you know it won't be there. This is not to say that this method should not be used with properties. There are certain times when a property will be required to have a specific set of values, such as in the case of an enumeration. In these instances, it is perfectly acceptable to use the Intern method to save a little memory as the application executes. In Chapter 12 we describe this and other performance enhancing techniques.