Database Design & Implementation

According to the syllabus of this course, the second major part is Working With Data. We can categorize this part into two main categories:

1. Database Design and Implementation
• Prepare data model normally Entity relationship diagram
• Prepare database schema which states the physical structure of our database
• Create the database based on the schema by using any DBMS we want
2. Database Manipulation 

•  Work with the database tables to insert, update, delete or retrieve data

As part of ASP.NET we will be dealing with the second category. To manipulate database the ADO.NET (one of the fascinating features of .NET framework) is our first choice. Database design is students' responsibility as it will help the student to exercise real application of database concepts. However, I have written a simple design of each system and you can use as an alternative.
 Now we have four different projects one for each class:

1. Hotel Room Reservation System
2. University Registrar System
3. Online Shopping System
4. Airline Booking System

I have tried to present you with the simplest design of each of these systems.

    

Hotel Reservation System

In hotel reservation system we will be dealing with these two main entities and two weak entities:

Customers (CustId, Title, FName, LName, DoB, Tel, Email, city, countryRes, Nationality, PassNo)

Rooms (RoomId, Type, Price, Floor, Facilities, Notes)

Bookings (BookingID, CustId, RoomId, DateMade, TimeMade, StartDate, EndDate paid)

RoomsCustomers (CustId, RoomId, StartDate, EndDate, RegistrationDate)

University Registrar System

In registrar system we will be dealing with these entities:

Student(StudId, FName, LName, DoB, Gender, DCode, DoB, PoB, PrevEducation, PrevInstitution,         Program, ProgramType, City, Region, Tel, PoBox, Email, EmergencyContactName, EmergencyContactPhone, AppDate, Approved, ApprovalDate)

Department (DCode, DName, Major,Minor)

Employee (EmpId, FName, LName,DCode, Gender, Qualification, Position, Nationality, DoB, EmpType, EducLevel, HireDate, Tel, Email, PoBox)

Course (CourseNo, CourseName, CreditHour, AcadTermLevel,AcadYearLevel, DCode)

Term ( TermID,Term, AcadamicYear, StartDate, EndDate)

Note: for groups who are working on University Registrar System, tables used are more than the stated and that is left as a challenge for you. Study how your university's registrar system works and drive the rest of the tables.

 Online Shopping System

Products (ProdId, ProdName, Description, SupplierName, Category, Quantity, UnitPrice, Picture)

Customers (CustId, FName, LName, City, State, PostalCode, Country, Phone, Email, CreditCardType, CreditCardNo)

Orders (OrderId, CustId, OrderDate, ShipDate, RequiredDate, ShipperName, Paid)

OrderItems (OrderId, ProdId, Price, Quantity, Discount, ShipDate, OrderItemsId)

Airline Booking System

Passenger (Email, GivenName, SurName)

Airplane (RegNo, ModelNo, Capacity, Airline)

Flight( FlightNo, ModelNo, From, To, DepartTime, DepartDate, ArrivalTime, ArrivalDate)

Booking (Email, FlightNo, BookingDate, Status)

**********

Database Implementation

You have a number of options and a number of DBMS softwares but for now we will be using SQL Server Express edition as our DBMS. When you installed your web developer sql server express was also installed but with no graphic interface to use. Web Developer supports many features that enables us interact with the SQL Server. I am going to show you how you can use web developer to create and test your database.

Take these steps:

1.      Website > Add New Item > SQL Server Database > Add > Yes

2.      View > Database Explorer > Expand your database

3.      R-click on Tables folder > Add New Table

4.      Define fields for the table

5.      Define primary key constraint by R-clicking on the left side button before the column (field) you want to make primary key > set primary key > Save

To see how you can do this and how you can create relationships between tables watch this video:

if you don't want this way go to my channel http://youtube.com/nuux14 and find the video by title

Dynamically Changing Master Pages and their contents

Some times we may need to use different master pages for may be different users. To have this capability you need to have more than one master page inside your website first, and control which master page to display inside the code behind but you should select a default master page for each web form.

Suppose you have two different master pages one for students and the other for web administrator.
To change the master page of a web form you need to add this event to your code behind file

Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs)    Handles Me.PreInit

        If Request.QueryString("userName") = "Ahmed" Then

            Me.MasterPageFile = "~/MasterPage2.master"

        End If

    End Sub

Here I am changing the masterpagefile of the current web form iside its code file. I am using query string to identify which user logged in but this may not be the case when we manage users. We shall see this topic in the following chapters.

Also you can manage the contents inside the masterpage without the need to change the master page. So we need to access the controls inside the master page directly inside the content page to do this you should use the property Master ( which is an object) of the current page. Within that object we have the FindControl() method. See this example which changes the banner image displayed inside the masterpage:

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

        Dim IMG As Image = CType(Master.FindControl("Image1"), Image)

        IMG.ImageUrl = "~/RememberAllah.png"

    End Sub

 

ADO.NET

ADO.NET is a set of classes designed to access data sources.
We have two categories of these classes:

1. Data Container classes: these classes are merely used for temporary data storage. Data retrieved from database are stored in these classes. Beyond that data container classes can act like your database by maintaining relationships between tables (offline tables not real database tables). This is sometimes referred as Disconnected architecture. These classes include: DataSet
2. Data Connection classes (Data Providers): these classes are the concrete tools to work with data sources. Data providers help us establish connection with our database, retrieve data, update and many related tasks. These classes include: [provider]Connection.

In ADO.NET we have four Data Providers:

1. Sql Data Provider
2. Oracle Data Provider
3. OleDb Data Provider
4. ODBC Data Provider

The main common classes in all data providers are: Connection, Command, DataAdapter, & DataReader.

Sql Data Provider:

• SqlConnection - to establish a connection to our database. This is the first task when we want to deal with database. If the connection is established successfully you have cut a major part of your journey. As a rule every opened connection must be closed after usage. ConnectionString is a property of this class that needs to be set with a number of parameters passed with their values. After this you call Open() method to establish the connection and to disrupt it you call Close() method.
• SqlCommand - to execute sql statements (commands) against the database.This is second task you should do. Two properties need to be set to use this class. The CommandText is set with an sql statement string. The Connection is set with the connection object we have created.
• SqlDataAdapter- it is a bridge between database and dataset.
• SqlDataReader - to read the data returned by SqlCommand

Connection String
To establish a successful connection we need to understand the different ways to construct connection string based on different DBMSs & their different versions. Basically we need to set and pass at least the following parameters in the connection string:

• Data Source - the name or the address where the database is residing.
• Initial Catalog , Database or AttachDbFilename - the name and the path of the database file
• Integrated Security - the type of authentication to use. We have two types of authentication here. Windows Authentication and SQL Server Authentication. If you set this parameter to true you want to use Windows Authentication which is recommended otherwise you are using SQL Server Authentication which requires you to provide two other parameters namely User ID and Password.
• User Instance - you set this to true if your database is not in the master list of sql server. This means roughly your database is not attached to the sql server.

Let us go down: when building connection string you need to consider the version of sql server you are using and whether or not your database is attached to the sql server.

Option 1: You are using Full version of SQL Server

Criteria:  your database must be attached to the sql server
 Example connection string:

Dim conString As String

conString = "Data Source=(local);Initial Catalog=yourdatabase.mdf;" _ 

& "Integrated Security=true;"

Option 2: You are using SQL Express and Database is attached to server

Example connection string

Dim conString As String

conString = "Data Source=(local)\SQLExpress;Initial Catalog=yourdatabase.mdf;" _ 

& "Integrated Security;"

Option 3: You are using SQL Express and Database is not attached to server

Exmaple connection string

Dim conString As String

conString = "Data Source=(local)\SQLExpress;" _ 

& "AttachDbFilename=|DataDirectory|\yourdatabase.mdf;Integrated Security=true;"_ 

& "User Instance=true;"

ASP.NET Quiz

1. Explain the benefits of .NET framework in your own term?
2. What are the major components of .NET framework?
3. What is the role of Common Language Runtime (CLR)?
4. How do you relate asp with asp.net?
5. State the difference between these Html tags:

a)      <div id=”x1” runat=”server” ></div>

b)      <div id=”x1” ></div>

6. How a web server handles the following files (explain):

a)      first.html

b)      first.aspx

7. Compare and contrast server side and client side validation?
8. Validate an age input box, age should be in the range 25-35
9. Why rich controls are named so?
10. What is the use of MultiView control?

Ans: To break up a long data collection form (or any other related content) within the same page, use MultiView control. Read this article of mine about How to Use MultiView Control

11.  Validate the following text box: accept only alphabetic input <asp:TextBox id=”txt1” runat=”server” />
12. List and describe the types of Server controls.

Ans: We’ve two types of server controls in asp.net which are

13. How can you send a user to a new page in asp.net? Describe the different methods available and their differences.

Ans:

·         By using html anchor tag <a href=”page2.aspx”>Go to page 2</a>

·         By using asp.net hyperlink control <asp:HyperLink id=”hlk1” runat=”server” navigateUrl=”page2.aspx”>Go to page 2</asp:HyperLink>

·           For more please refer User Redirection article on this blog

14. Pages or web forms exchange data or information. How this happens? State at least one method and how to use it.

Ans:

·        QueryString

·         Session

·         Application

·        Cookies
etc

15. Http is stateless (i.e: page state is lost every time a page is posted back). How does asp.net overcome this fact?

Ans: By using a hidden control known as viewState which saves the contents of all controls in the page and bring back the content to the newly created page after postback.

16. Why do you think Html server controls are included in asp.net while we’ve got their powerful counterparts?

   Ans: For backward compatibility. If you have an already existing html page you can simply convert it into an asp.net web form by converting the html elements into html server controls.

17. List the benefits of using Master pages.

Ans:

·         Centralizes the look and feel of pages

·         Reduces code

·         Minimizes errors

·         Can be used to control access to pages

·         More about Master pages follow this link Master Pages
download doc

The ASP.NET MultiView Control

Have you ever wondered a way in which you can have a long form broken into wizards so that the user navigates step after step without placing contents in different files ?
Microsoft included an easy to use controls to solve this kind of problem. In fact, we can do this by using ordinary html server controls like using different  <div runat="server"></div> tags for different parts of our long form or <asp:Panel> just by making one panel visible and the rest invisible. But as we said Microsoft ASP.NET includes web controls especially designed for this purpose (MultiView and Wizard controls). Let us see how to use MultiView web control to divide a long form. Suppose you are developing registrar web portal for certain university, let us say University of Hargeisa (UoH). The student registration form of UoH is composed of at least three pages.Certainly you don't want to put this pages in different files if you can do this is some other way round.

Start:
Open your web developer (assuming that you've installed at least the free Web Developer Express edition)
if you don't have one go to this Microsoft official link and download it for free Visual Web Developer 2010 Express.
 Go to File>New Website, a default.aspx template page will be added to your new website.
 Go to Source view of the page, put your cursor in between the opening and closing tags of the form tag.
and type:

<asp:MultiView ID="MultiView1" runat="server">

   

</asp:MultiView>

Now you have added the MultiView web control but the different views(steps) not added yet so you may ask, what is the job of MultiView then ? It is purpose is just to keep the rule: no two views shall be visible at the same time, only one is allowed to be active. To add views we need to add another control known as View which serves as the step for our wizard. So put your cursor between the opening and closing tags of the above MultiView control and type three times of the following content:

 

<asp:View ID="View1" runat="server">

       

</asp:View>

Your  Mutliview with the Views should look like this:

<asp:MultiView ID="MultiView1" runat="server">

   

        <asp:View ID="View1" runat="server">       

        </asp:View>

       

        <asp:View ID="View2" runat="server">

        </asp:View>

       

        <asp:View ID="View3" runat="server">

        </asp:View>

  </asp:MultiView>

Now in between the opening and closing tags of each View add the contents of one of the pages of the registration form. For instance, Page 1 content should be added to View1. Finally, your code should be similar (this is a model and necessarily is not reflecting your code) to this:

<asp:MultiView ID="MultiView1" runat="server">

   

        <asp:View ID="View1" runat="server">       

           UoH Registration Form<br />

            &nbsp;Page 1 of 3<br />

            <br />

            <b>Personal Information</b><br />

            <br />

            First Name:

            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

            <br />

            Last Name:

            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

            <br />

            <asp:Button ID="Button2" runat="server" CommandName="NextView"

                Text="Next &gt;" />

        </asp:View>

       

        <asp:View ID="View2" runat="server">

            Page 2 of 3<br />

            <br />

            <b>Educational Background</b><br />

            <br />

            Last Institution Name:

            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

            <br />

            <asp:Button ID="Button3" runat="server" CommandName="PrevView"

                Text="&lt; Previous" />

            <asp:Button ID="Button4" runat="server" CommandName="NextView"

                Text="Next &gt;" />

        </asp:View>

       

        <asp:View ID="View3" runat="server">

            Page 3 of 3<br />

            <br />

            <asp:CheckBox ID="CheckBox1" runat="server"

                Text="I certify that the above information is correct " />

            <br />

            <asp:Button ID="Button5" runat="server" CommandName="PrevView"

                Text="&lt; Previous" />

            &nbsp;&nbsp;

            <asp:Button ID="Button6" runat="server" Text="Register Student" />

        </asp:View>

   

    </asp:MultiView>

 Now if you switch to the design view you would see the following design:

If you run your website at this point you will not be seeing any of these views. The reason is that MultiView control is not told yet which view to display by default. To do this set the ActiveViewIndex attribute of the MultiView control to 0 which means MultiView will be displaying the View with the index 0 at the first request of the page. After you add ActiveViewIndex attribute, the opening tag of the MultiView control should look like this:

 <asp:MultiView ID="MultiView1" runat="server" ActiveViewIndex="0">

Now you are ready to run your website, press F5 or go to the menu  Debug > Start Debugging, click Ok for the message box that appears. The result should be this:

Click the Next > button. Yes ! It takes you to the next view and so forth. What happened is an interesting phenomenon. You may have expected a code behind for the button to navigate us to the next level but this is not the case. So what is the magic around ? Go back to the code and take a look at the declarations of the buttons, you'll see a new attribute known as CommandName set to NextView for Next buttons and PrevView for the Previous buttons. Cool, isn't it ? This attribute is available for all button types (i.e: Buttons, ImageButtons, LinkButtons).

Briefly introducing .NET Framework

 .NET framework is a general-purpose software development platform. The .NET framework solved most of the problems of the traditional COM components programmers refer as DLL hell.

Microsoft defines .NET framework as "The .NET Framework is a development and execution environment that allows different programming languages and libraries to work together seamlessly to create Windows-based applications that are easier to build, manage, deploy, and integrate with other networked systems."

The main components of .NET framework :

• Several programming languages (e.g: VB, C#, J#)
•  Class libraries (pre-coded solutions)
• Common Language Runtime (CLR)
• ASP.NET Engine
• Visual Studio (a convenient IDE)

          Image Source: MSDN Website (without the handwritten comments)
The image above shows that all codes in .net framework to be executed against the system must pass by the Common Language Runtime (CLR) which means CLR is the agent that manages code. CLR also provides memory management and thread management among others. So there is a kind of unification among the programming languages you use as far as codes are executed by the same agent (CLR) no matter which language generates. Note: when we say codes are executed by CLR we don't mean source codes but source codes must be compiled first by the compiler of the language we are using. The compiled code then is in the form of Intermediate Language (IL) which is assembly language format understandable by the CLR.

See how codes are compiled into IL then executed by the notorious CLR

Image Source: ASP Free

Features of .NET framework includes:

•  Easier and quicker programming
• Simplified Development and Deployment
• Larger class library (largely reduced amount of code we write)
• Language Independency 
• Language Integration