Introduction
This is a project on which I worked for my MS assignment. The aim of this project is to implement a web application, where students can register for an online course. If the user is an administrator, he can add and remove courses and modify the student information. It's a very useful example for three tier application. I implement this application using C# and ASP.NET Web Forms. It contains of three parts.
-
User interface layer: Web application.
-
Data processing layer: Class library, which interact with SQL and the user interface.
-
Database: SQL server version.
The Database
This is the Database diagram I created for the university database. The best part is, if you find any mistake into your database or would like to change anything, like any relation, you can modify through the diagram and its will update your database table relationship for you automatically.
I used a SQL server database for this project. You can go to server explorer and create a new database, and add new tables, views, stored procedures etc.
Figure 1.1
These are all tables I used in the project in relationship to each other.
This is the tables I created for university database. ( I don't see any usage of this screenshot, because it describes the same as figure 1.1. )
Figure 1.2
When you run the project in your favorite internet browser, you should see a page like the above shown screenshot.
Figure 1.3
When you try to register for any courses, it asks you to log in. The page you will see looks like the image in figure 1.3. After entering the required username and password, you should be redirected to the requested page.
CheckPassword(string sUid, string sPwd) is the main function to validate the login information.
For test purpose you can use the following data:
Login: admin
Password: a
If you don't have a database, please uncomment first line and build it.
The parameters in the function should look like this sUid and sPwd, however this is a programming topic and not an editorial thing !
private
string CheckPassword( string sUid, string sPwd)
{
// If you don't have database, please uncomment first line.
// return "1"; // that does not make any sense for me ?
string connStr="server=guddu;uid=sa;pwd=;database=university";
SqlConnection conn=new SqlConnection(connStr);
string sql = "select userid from registereduser where name='" +uid + "' and password='"+pwd+"'";
SqlCommand comm=new SqlCommand(sql, conn);
conn.Open();
Object obj = comm.ExecuteScalar();// returns one row
string userid = "";
if (obj != null)
userid = obj.ToString(); // AccessLevel column
string strRole = "";
sql = "select role from role where roleid='" +
userid + "'";
SqlConnection conn2 = new SqlConnection(connStr);
SqlCommand comm2 =new SqlCommand(sql, conn2);
conn2.Open();
obj = comm2.ExecuteScalar();// returns one row
if (obj != null)
strRole = obj.ToString(); // AccessLevel column
conn2.Close();
conn.Close();
return strRole;
}
Figure 1.4
This is the enrollment page. The students can enter his/her student id and the course they are interested in. If they don't know the course id, they can check that in the course offered page.
Here is the code for the registration process of the courses:
public
void RegisterStudentInCourseAndUpdateSeats(string StudentID, string CourseID)
{
string connStr="server=guddu;uid=sa;pwd=;database=university";
System.Data.SqlClient.SqlConnection conn=new System.Data.SqlClient.SqlConnection(connStr);
string sql = "update courseoffsered set seattaken = seattaken +1 where courseid='" + CourseID + "'";
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sql, conn);
conn.Open();
comm.ExecuteScalar();// returns one row
sql = "insert into enrollment(courseid, studentid,sectionnum, cnum) values('" + CourseID + "' ," + StudentID + " , 1 , 1)";
System.Data.SqlClient.SqlCommand comm2 = new System.Data.SqlClient.SqlCommand(sql, conn);
comm2.ExecuteScalar();// returns one row
conn.Close();
}
Figure 1.5
This above figure shows you the courses offered in the current semester.
The following code is responsible to get the results shown in figure 1.5.
private
void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
Response.Expires=0; //do not cache the salaries page
Session["RequestedPage"]=Request.ServerVariables["SCRIPT_NAME"];
if ((Session["UID"]==null))
Response.Redirect("Login.aspx");
Response.Write("<center>Welcome " + Session["UID"] + " You are loged on as " + Session["AccessLevel"] + "</center>");
sqlDataAdapter1.Fill(dsCourseOffered1);
DataGridCourseOffered.DataBind();
}
Figure 1.6
This page will be used to display the details of the courses. If you are logged in as an administrator, you have the rights to edit and update the student's details.
Figure 1.7
This page is meant for administrators only. If you logged on as a normal user, you won't be able to access this page. Administrators can add and delete the courses offered in the current semester.