TimeTracker

The following are major activities in this application

• User Registration
• Login
• Password Recovery
• Creation of Project
• Adding members to project
• Listing projects
• Listing users
• Generating report regarding project
• Logging Time entries
• Displaying projects assigned to current user
• Logout

Technologies and Products Used

• ASP.NET 3.5
• C# language
• Visual Studio.NET 2008
• SQL Server 2005 Express Edition
• ADO.NET
• Login controls - Membership
• Stored procedures using T-SQL.
• Identify columns for auto increment columns
• Master pages and Themes
• DataBound controls such as GridView, FormView etc.
• ObjectDataSource to get data from BLL and bind data to data-bound controls such as FormView and GridView.
• Business Logic Layer - BLL, to access DAL on one side and Object data source on the other side.
• Data Access Layer - DAL, to access database.

Steps to download, deploy and run this project

1. Download timetracker.rar and unzip it into any directory in your system. For example, if you extract to c:\ then it will create a directory c:\timetracker. The download contains all ASP.NET pages but it has NO DATABASE. We have to create database objects using ASP.NET configuration tool manually.
2. Open Visual Studio.NET 2008 or Visual Web Developer 2008.
3. Open the project from the directory into which you extracted project.For example, c:\timetracker
4. Select Website->ASP.NET Configuration option
5. Select Security tab
6. Select Use the security Setup Wizard to configure security step by step.
7. Select From Internet option in Step 2
8. Click on Next button in the remaining screens and finally click on Finish.
9. It create a database called ASPNETDB.MDF with required tables and other database components
10. Open the database in Server explorer or Database Explorer and create tables - PROJECTS, PROJECT_MEMBERS and TIMEENTRY. The structure for these tables is shown below. PROJECT and PROJECT_MEMBERS tables refer to USERID column of ASPNET_USERS table, which is created by Configuration Tool.

    PROJECTS Table

    Column Name	Data Type	Remarks
    id	int	Identify Column
    title	varchar(50)
    description	varchar(1000)
    createdon	datetime
    creatorid	uniqueidentifier	References USERID column in ASPNET_USERS table
    estduration	int
    manager	uniqueidentifier	References USERID column in ASPNET_USERS table

    PROJECT_MEMEBRS table

    Column Name	Data Type	Remarks
    projectid	int	References ID column in PROJECTS table
    userid	uniqueidentifier	References USERID column in ASPNET_USERS table

    Note: PROJECTID and USERID together make up composite primary key for PROJECT_MEMBERS table.

    TIMEENTRY table

    Column Name	Data Type	Remarks
    entryid	int	Identify Column
    projectid	int
    userid	uniqueidentifier
    entrydate	datetime
    createdon	datetime
    duration	int
    description	varchar(1000)

    Note: PROJECTID and USERSID reference PROJECTID and USERID of PROJECT_MEMEBRS table. It is a composite foreign key.

11. Create the following stored procedure in the database.

    CREATE PROCEDURE dbo.CreateProject(@title  varchar(50),
    @description varchar(1000),
    @creatorid uniqueidentifier,
    @estduration int,
    @managerid uniqueidentifier)
    AS
        insert into projects values(@title,
        @description,getdate(), @creatorid,@estduration,
        @managerid)
    

    CREATE PROCEDURE dbo.GetAllProjects
    AS
    select * from projects order by id;    
    

    CREATE PROCEDURE dbo.GetAllUsers
    AS
    select  u.userid, u.username, m.email, m.createdate
    from   aspnet_users  u  join  aspnet_membership m
    on ( u.userid = m.userid)
    order by u.username
    

    CREATE PROCEDURE dbo.GetNonMembersOfProject(@projectid int)
    AS
    
     select userid, username from aspnet_users
     where  userid not in (
     select userid from project_members
     where projectid = @projectid)
     
    

    CREATE PROCEDURE dbo.GetProjectDetails
    (@projectid int)
    AS
      select p.id, p.title, p.description, p.createdon,
      p.estduration,  u.username
      from projects p  inner join  aspnet_users u
      on (p.managerid = u.userid) 
      where  p.id = @projectid       
    

    CREATE PROCEDURE dbo.GetProjectMembers(@projectid int)
    AS
    	select userid, username
    	from aspnet_users
    	where userid  in 
    	  (select userid from  project_members
    	   where projectid = @projectid);
    

    CREATE PROCEDURE dbo.GetTimeEntriesByProject
    	(@projectid int)
    AS
       select  t.entryid, t.entrydate, t.duration, t.description, t.createdon, u.username 
       from  timeentry t inner join aspnet_users u
       on ( t.userid = u.userid)
       where  t.projectid  = @projectid
    
    

    CREATE PROCEDURE dbo.AddMemberToProject(@projectid int, @userid uniqueidentifier)
    AS
       insert into project_members values(@projectid, @userid);
       
    

    CREATE PROCEDURE dbo.AddTimeEntry(@projectid int,@userid uniqueidentifier,@entrydate datetime,@duration int,@description varchar(1000))
    AS
      insert into timeentry values(@projectid,@userid,
      @entrydate, getdate(), @duration,@description)
       
    

12. Goto Solution Explorer and make login.aspx the startup page.
13. Run project from Visual Studio.NET 2008 or Visual Web Developer 2008.
14. You should see login.aspx page.