ASP.NET: Using a Database on Your Web Site

	ASP.NET Topics: Using a Database on Your Web Site

Introduction

With ASP.NET, you can use a database on your web site. There are a few steps you primarily have to perform and some details you should take care of.

When looking for a host (the company that would host your web site), make sure your web site would support the .NET Framework. Usually, this means that you need to make sure that the web site is hosted on a server that runs Microsoft Windows (Server 2003). If you are planning to use Microsoft SQL Server (or Oracle, or MySQL), you must enquire from the company. Most of the time, they may as you to provide (or purchase) a license for the database. Once you do, they would install it for you (since you don't have access to the database anyway).

In this exercise, we will use a typical and simple Microsoft Access database on a commercial (Internet) web site. Assuming that you have installed Micrsoft Visual Studio (2005) or Microsoft Visual C# (2005) successfully (with all the necessary ActiveX drivers) but without probably having Microsoft Access on your computer, we will start by creating a database from a regular Windows application.

Practical Learning: Introducing Data Web Hosting

Start Microsoft Visual Studio or Visual C#

To create a web site, on the main menu, click File -> New -> Web Site...

Change the name of the web site to persons2 and set the language to Visual C#

Click OK

In the Solution Explorer, right-click the C:\...\person2\ node and click New Folder

Set the name to CGI-BIN (we could as well use the existing App_Data folder but we will use this one instead) and press Enter

In the Solution Explorer, right-click Default.aspx and click Rename

Change the name to index.aspx and press Enter

Save everything

Using a Microsoft Access Database

If you will be using Microsoft Access as your database, making sure that the web site is hosted on a Microsoft Windows operating system should be enough. In this case, the next question you would ask is to know where you must place your database(s). Most companies would ask you to put it (them) in a folder called CGI-BIN that they would have created for you. You need to know where your database will need to reside, otherwise, the web pages might have a hard time find it. Of course, when creating your web page(s), you must specify where the database would be located.

Practical Learning: Using a Microsoft Access Database

Open Windows Explorer or My Computer. Locate the folder where you created the persons2 database. Copy the folder's complete path up to CGI-BIN.
Mine is C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\persons2\CGI-BIN

Return to Microsoft Visual Studio or Visual C#

To create a Windows application, on the main menu, click File -> New -> Project...

In the Templates list, click Windows Application.
Set the Name to SanePeople1 and press Enter

On the main menu, click Project -> Add Reference...

In the Add Reference dialog box, click the COM property page

In the list, click Microsoft ActiveX Data Object Library

Press and hold Ctrl

Click Microsoft ADO Ext. 2.8 for DDL and Security:

Click OK

From the Toolbox, click the Button and click the form

Double-click the new button and implement its event as follows (for more information about this code, refer to our section on ADO with C#):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ADOX;
using ADODB;

namespace SanePeople1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            CatalogClass catDatabase;
            string strDatabase = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source='C:\\Documents and Settings\\" +
                   "Administrator\\My Documents\\Visual Studio 2005\\" +
                           "WebSites\\persons2\\CGI-BIN\\people.mdb'";

            catDatabase = new CatalogClass();
            catDatabase.Create(strDatabase);
            MessageBox.Show("A new Microsoft JET database named " +
                            "people.mdb has been created");


            ConnectionClass conPeople = new ConnectionClass();

            try
            {
                object objAffected;
                string strStatement = 
		    "CREATE TABLE persons(PersonID COUNTER PRIMARY KEY, " +
                                                "FirstName varchar(20), " +
                                                "LastName varchar(20));";
                string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                               "Data Source='C:\\Documents and Settings\\" +
                       "Administrator\\My Documents\\Visual Studio 2005\\" +
                           "WebSites\\persons2\\CGI-BIN\\people.mdb'";

                conPeople.Open(strConnection, "", "", 0);
                conPeople.Execute(strStatement, out objAffected, 0);
                MessageBox.Show(
			"A new table named persons has been added to " +
                        "the People.mdb database");
            }
            finally
            {
                conPeople.Close();
            }
        }
    }
}

Execute the application and click the button on the form

Reopen the persons2 web application (you don't need to save SanePeople1 but it's up to you, we will not use it anymore)

To create a new web page, on the main menu, click Website -> Add New Item...

In the Templates, make sure Web Form is selected. Change the name to persons.
Make sure the language is set to Visual C# and click Add

In the bottom section of the code editor, click Design

From the Data section of the Toolbox, drag AccessDataSource and drop it on the form

Click Configure Data Source

In the Configure Data Source wizard, click Browse

Click the CGI-BIN node and, on the the right side, select the people.mdb item:

Select Microsoft Access Database

Click OK

Click Next and click Next

In the next page of the Configure Data Source wizard, in the Columns list box, click the * check box

Click Next and click Finish

In the form, click below the data source and type List of People

From the Data section of the Toolbox, drag GridView and drop it on the form below the List of People title

While the data grid is still selected on the form, in the Properties window, set its DataSourceID to AccessDataSource1

Click Source and change the file as follows:

<%@ Page Language="C#" 
         AutoEventWireup="true" 
         CodeFile="persons.aspx.cs" 
         Inherits="persons" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>People</title>
</head>
<body>
        <h1>List of People</h1>
    <form id="frmPersons" runat="server">
    <div>
        <asp:AccessDataSource ID="AccessDataSource1" 
                              runat="server" 
                              DataFile="~/CGI-BIN/people.mdb" 
                              SelectCommand="SELECT * FROM [persons]">
        </asp:AccessDataSource>
        <br />
        <asp:GridView ID="GridView1" 
                      runat="server" 
                      AutoGenerateColumns="False" 
                      DataKeyNames="PersonID"
                      DataSourceID="AccessDataSource1">
            <Columns>
                <asp:BoundField DataField="PersonID" 
                                HeaderText="PersonID" 
                                InsertVisible="False"
                                ReadOnly="True" 
                                SortExpression="PersonID" />
                <asp:BoundField DataField="FirstName" 
                                HeaderText="FirstName" 
                                SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" 
                                HeaderText="LastName" 
                                SortExpression="LastName" />
            </Columns>
        </asp:GridView>
    
    </div>
    </form>
    <p>
        <a href="index.aspx">Home</a>
    </p>
</body>
</html>

Save the file

To create a new web page, on the main menu, click Website -> Add New Item...

In the Templates, make sure Web Form is selected. Change the name to newperson.
Make sure the language is set to Visual C# and click Add

In the bottom section of the code editor, click Design

Design the form as follows:

Control	Text	(ID)
Label	First Name:
TextBox		txtFirstName
Label	Last Name:
TextBox		txtLastName
Button	Create	btnNewPerson

In the bottom section, click Source and change the file as follows (you don't have to do any of these things, it would not affect your exercise, it will only make the form look good):

<%@ Page Language="C#"
         AutoEventWireup="true" 
         CodeFile="newperson.aspx.cs" 
         Inherits="newperson" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>People - New Person</title>
</head>
<body>
    <form id="frmNewPerson" runat="server" method="post">
    <div>
        <h1>People - New Person</h1>
        <table>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label1"
                               runat="server"
                               Text="First Name:">
                    </asp:Label>
                </td>
                <td style="width: 100px">
                    <asp:TextBox ID="txtFirstName" 
                                 runat="server">
                    </asp:TextBox>
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label2" 
                               runat="server" 
                               Text="Last Name:">
                    </asp:Label></td>
                <td style="width: 100px">
                    <asp:TextBox ID="txtLastName"
                                 runat="server">
                    </asp:TextBox>
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                    <asp:Button ID="btnNewPerson" 
                                runat="server" 
                                Text="Create" 
                                Width="155px" />
                </td>
            </tr>
        </table>
    
    </div>
    <p>
        <a href="index.aspx">Home</a>
    </p>
    </form>
</body>
</html>

In the bottom section of the code editor, click Design

On the form, double-click Create and implement its event as follows:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class newperson : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnNewPerson_Click(object sender,
                                      EventArgs e)
    {
        SqlConnection cnnPerson = new SqlConnection(
            "Server=(local);Database='people1';Integrated Security=sspi");
        cnnPerson.Open();

        string strStatement =
            "INSERT INTO Persons(FirstName, LastName) VALUES('" + 
            txtFirstName.Text + "', '" + 
            txtLastName.Text + "');";
        SqlCommand cmdPerson = new SqlCommand(strStatement, cnnPerson);

        cmdPerson.ExecuteNonQuery();
        cnnPerson.Close();

        txtFirstName.Text = "";
        txtLastName.Text = "";
        txtFirstName.Focus();
    }
}

Click the index.aspx tab to access its file

In the bottom section of the code editor, click Source is necessary and change the file as follows:

<%@ Page Language="C#" 
         AutoEventWireup="true"  
         CodeFile="index.aspx.cs" 
         Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>People</title>
</head>
<body>
<h1>People</h1>
<p><a href="persons.aspx">List of People</a></p>
<p><a href="newperson.aspx">New Person</a></p>

    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>

To execute the application, on the main menu, click Debug -> Start Without Debugging

Click New Person and create a new people before returning to the home page, then click the List of People link

Browser

Close the browser and the application

Delivering the Application

After creating and successfully testing the application on your computer, you can put it on your web site. Once again, find out, from your host, where it should be.

Imagine that your host asks you to put the database on the CGI-BIN. You can access it from your control panel (provided by your host) or you can use an FTP application. Then add or upload the database. Here is an example:

After adding the database to your server's folder, you must add the web files. Normally, you can put the web files on their own folder, whether in the root directory or in a sub-folder. It's up to you but you should know where the files are. Most of the time, you should upload all the files that were created in your web application (Microsoft Visual C# web applications are not big; a basic or simple application should weigh less than 500KB). Here is an example after uploading our persons2 application:

If you had uploaded all of your web application and all its folders, to make sure your test is right, if your host had specified where you should place your database, and if you had put the database in the folder indicated by your host, you should delete the CGI-BIN folder that your FTP application would have uploaded:

After adding the database and the files, of course, you should test the application before distributing the URL. To do this, you can use a regular browser and access the folder you uploaded. Here is an example:

You can then create a couple of people to test the application. Here is one example:

Then make sure that the list shows them. Here is an example:

If the test(s) work(s) fine, you should be ready to either distribute the URL, to employees (for an Intranet web-based application) or provide a URL to your visitors.


Home	Copyright © 2007-2013, FunctionX