Introduction to Databases
Databases are used to store information that are arranged in a specific manner. Each and every entry in a database is called a record and are organized into tables. Each table in turn contains fields which contains information. For example, in a student management database there may be tables pertaining to students general info, assignments, fee status, exams etc. Each of these tables contains fields according to the requirements. We use to browse the net mainly for getting information related to various topics. But the most important point is that nearly 85% of the sites on the internet are relying upon databases for specific needs.
For example consider the following scenarios:
You are searching the web using yahoo.com by entering a keyword and you are viewing the search results immediately. So how does the information come from, it is through the databases
stored on the server.
You are using emails, have you ever imagined where your mails are stored safely till you retrieve them from the server -yes it is in databases. Hence we can conclude that without databases there will be no business over the web. The most powerful and easiest database application now available is Microsoft Access. You can easily create a database with a few mouse clicks if you are using packages like these. Moreover you can use distributed database languages such as Oracle, SQL Server etc. Suppose you have been asked to develop a database project using Visual basic and Microsoft Access by your employer. After sometime your employer decides to implement the same project on the web by using ASP and this time by making use of distributed databases like Oracle or SQL Server.
What is the difference between the first and second projects? The basic idea behind the first project is that, Microsoft Access is primarily meant for applications having a single user at a time. They are mainly located on a single computer and can be used only by one person at a time. But these types of projects cannot be avoided and are still used by large number of concerns. The concept behind the second project is that of Multi user databases which means they are located on a network and can be accessed by many users at a time. Moreover it is implemented over the web also and any user can access the application irrespective of the operating system they are using. Normally databases used for building the web projects may be Oracle, SQL Server because of their ability to handle large number of records. But instead of VB forms, HTML forms are used in web projects. We cannot use HTML directly for accessing databases. Instead we have to use server-side technologies like ASP. ASP uses either VBScript or JavaScript as its scripting language. Front-end and Back-end Concept
The front-end is the program with which the user interact. It mainly consists of text fields, checkboxes, radio buttons, combo boxes, command buttons etc to facilitate data entry and other related tasks like updating, deletions etc. The back-end lies between the front-end and the database itself. The back-end is actually provided as part of the operating system. The Microsoft Jet Database Engine is an example of a back-end. The jet engine interacts with the database file to perform the data manipulations. The database backend running on the web server should support the database file format.
For example a Microsoft Access 2000 database does not support Microsoft Jet 3.51.Instead we have to use 4.0 version. If you are using Oracle or SQL Server, you have to configure the ODBC driver setup (from the control panel ODBC32 bit icon) and choose the appropriate drivers for the same. After that you have to link your application and the database via a appropriate data source name. Hence you have to consider the following aspects while designing a database application.
(1) Front-end designing
(2) Designing of a database
(3) Linking with an appropriate data source. You can connect web pages to databases via numerous backend engines. The best way is to use ActiveX Data Objects or ADO. But before examining ADO let us examine OLEDB. It is a set of Component Object Model (COM) interfaces that you can use to access a variety of information Sources. But you cannot access OLEDB directly, but indirectly by means of ADO.
ADO provide programmers with an application level interfaces to OLEDB. However several earlier technologies such as RDO / DAO are still supported and can use them in your ASP programming. However ADO is preferred due to its simplicity and easy maintainability. The ADO object model consists of the following objects viz Connection, Command, Recordset, Field, Error & Parameter. We should understand the functioning of the Connection, Recordset objects before proceeding further. It represents connection to a data source. We have to open a connection to a data source with the Open method. To execute a specified query we have to use Execute method.
But before opening a connection, you have to create an instance of the connection object, the coding of which looks like this <% Set conn = Server.createobject("ADODB.Connection") %>.
To open the data source use the following code <% conn.Open <your DSN Name> %>. Recordset Object
This is the main interface to data. It represents the set of records from a table in a database. You have to create a Recordset object similar to the connection object and use that object to open the connection to the records. The coding looks like this
<% Set rs = server.createobject("ADODB.Recordset") %>.
After that you have to open the record set by using the following coding
<% rs.Open <your table name>,conn %>. conn is our connection object which we created above.
The Recordset object contains several methods to create a new record, delete a record, update a record etc. For example to add new records to a record set use the following code
<% rs.addnew %> and update that record set using <% rs.update %>.
Field Object
It represents a column of data in a Recordset. For example while searching you may want to populate the records in the corresponding textboxes. For that purpose you have to make use of fields object and its item property. The coding for fetching the field age from a record set is as follows <% rs.fields.item("age") %>.
Case Study
Mark Inc decides to build a web application so that users can register at their site and login with their username and password to enter the members' area. They had decided to use HTML as the front-end and ASP scripting using VBScript. The database being used is Microsoft Access 2000. The first step will be to build an HTML Form. Click here to get the HTML Form. If you need the source code, right click on the page and select View Source from the pop-up menu. When the user fills out the form and clicks on the submit button, the data contained in the form should have to be passed on to a database on the server. For this purpose, the necessary .asp file is to be written and the complete code for the same is given below: <% set conn = Server.createobject("ADODB.Connection")
conn.Open "Member"
set rs = Server.CreateObject("ADODB.Recordset")
conn.BeginTrans
rs.Open "newcustomer",conn,3,3
rs.AddNew
rs("urname") = Request("urname")
rs("pass") = Request("pass")
rs("email") = Request("email")
rs("address") = Request("address")
rs("country") = Request("country")
rs.Update
session("permission") = "YES"
session("urname") = Request("urname")
conn.CommitTrans
rs.Close
conn.Close
response.write("Your data has been successfully submitted")
%>
<body><form>
<input type = "text" name = "name">
<input type = "password" name = "pass">
<input type = "submit" value = "Login">
</form></body> When this code is run, the form data will be passed to the database on the server, and you will get a confirmation message. After that you will be provided with an HTML form so that you can login to the members' area by entering your username and password. The site should check the validity of your username and password. If both are correct, then you will be either redirected to a new page or you will be shown your account details and other relevant information. If the entered information is not correct, then you will be shown an error message asking you to login again. Typically sites will provide a login form on their home page so that registered users can login straight away. In order to validate the username and password as mentioned above, the database will have to be searched. The complete code to search the database is given below: <% set conn = server.CreateObject("ADODB.Connection")
conn.Open "Member"
set rs = server.CreateObject("ADODB.Recordset")
rs.Open "newcustomer",conn,3,3
flag = 0
rs.MoveFirst
do while not rs.EOF
if request("pass") = rs("pass") and request("name") = rs("name") then
session("permission") = "YES"
session("urname") = Request.Form("urname") %>
<body>Welcome<% session("urname") %> to Mark Inc's members area</body>
<% flag = 1
end if
rs.MoveNext
loop
if flag = 0 then
Response.Write("Please reenter your username and password")
end if %>
When you execute this code, if your username and password are correct then you will be shown a message Welcome<your user name> to Mark Inc.'s members area. The user name is displayed here by creating a session variable. The above code assumes that you have created a data source name called Member and a table named newcustomer (using Microsoft Access). If you do not follow these steps, then you cannot run this application. However, you can run this application without creating a data source name on the control panel by applying the following piece of code. The code directly uses the database name as a parameter to the MapPath method of the server object. This method will automatically map this database to a proper WinNT path. If your web server supports .asp files but doesn't permit creation of data source names, then this code will be useful for you to test and deploy your .asp scripts.
<% set conn= server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")
openstring = "data source =" & server.MapPath("customers.mdb")
openstring = openstring & ";Provider=Microsoft.Jet.OLEDB.4.0;"
conn.Open openstring
rs.Open "newcustomer",conn,3,3 %>
|
.NET Tutorials 
