create table date(username varchar(50),password varchar(50), logindate datetime ,logoutdate datetime)

-----------loginpage.aspx.cs-----------

protected void Button1_Click(object sender, EventArgs e)

{

cls_PMPtables obj = new cls_PMPtables();

DataSet ds = new DataSet();

username = TextBox1.Text;

password = TextBox2.Text;

Session["username"] = username;

obj.savelogin(username,password);

if (ds != null)

{

Response.Redirect("Default2.aspx");

}

}

}

-------------------- cls_PMPtables.cs class file define globally connection----------------------------

public SqlConnection sqlconn = new SqlConnection(ConfigurationManager.AppSettings["statecity"]);

SqlCommand cmd = new SqlCommand();

-----------------------------------------------------------------------------------------------

------------------ cls_PMPtables.cs class file--------------------------------------------------

public void savelogin( string username, string password)

{

try

{

sqlconn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "PMP_Savelogin";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = sqlconn;

cmd.Parameters.Add("@Username", SqlDbType.VarChar);

cmd.Parameters["@Username"].Value = username;

cmd.Parameters.Add("@Password", SqlDbType.VarChar);

cmd.Parameters["@Password"].Value = password;

cmd.ExecuteScalar();

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

finally

{

sqlconn.Close();

}

}

------savelogin procedure---------

 

create PROCEDURE [dbo].[PMP_Savelogin]

@Username AS varchar(50),

@Password AS varchar(50)

AS

SELECT logindate FROM DATE WHERE CONVERT(VARCHAR(25),logindate,101)= CONVERT(VARCHAR(25),getDate(),101)

IF (@@rowcount = 0)

BEGIN

INSERT INTO date (username,password,logindate,logoutdate)

VALUES

(@Username,@Password,getdate(),getdate())

insert into Attendence(username,hourlytime,AttendenceDate)

values

(@Username,'',getdate())

END

ELSE

BEGIN

update date set username = @Username , password = @Password where username = @Username

END

---------------Default2.aspx.cs---------------

protected void Page_Load(object sender, EventArgs e)

{

Label1.Text = Session["username"].ToString();

}

*****************************************************************

----------create table attendence fo storing hourlytime on particular date--------

create table attendence(username varchar(50), hourlytime varchar(10),AttendenceDate datetime)

-----------------------------------------------------------------------------------------------------

--------------create GetTime function for calculating login logout difference for particular day--------

Create FUNCTION [dbo].[GetTime](@username VARCHAR(50),@Date DATETIME)

RETURNS VARCHAR(10)

AS

BEGIN

DECLARE @hour AS VARCHAR(10),@minute AS VARCHAR(10)

SELECT @hour=datediff(hour,logindate,logoutdate) from date where username=@username AND CONVERT(VARCHAR(25),logindate,111) = CONVERT(VARCHAR(25),@Date,111)

SELECT @minute=datediff(minute,logindate,logoutdate) from date where username=@username AND CONVERT(VARCHAR(25),logindate,111) = CONVERT(VARCHAR(25),@Date,111)

RETURN (@hour + ':' + @minute)

END

--------------------------------------------------------------------------------------------------------------------------

--------------also Defaul2.aspx page take one hyperlink and set navigationurl property Logout.aspx--------

------------and write code on Logout.aspx.cs file page load-------------------------

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

string user = Session["username"].ToString();

cls_PMPtables obj = new cls_PMPtables();

DataSet ds = new DataSet();

DateTime logoutdate = DateTime.Now;

obj.saveloginn(user,logoutdate);

}

}

-------------- cls_PMPtables.cs class file write saveloginn procedure---------

Create PROCEDURE [dbo].[PMP_Saveloginn]

@Username AS varchar(50),

@Logoutdate AS datetime

As

Declare @loginday as INT, @logoutday as INT

select @loginday = DATEPART(dy, logindate) from date where username = @Username

select @logoutday = DATEPART(dy, logoutdate) from date where username = @Username

if (@loginday = @logoutday )

BEGIN

update date set logoutdate = @Logoutdate where username=@Username and CONVERT(VARCHAR(25),logindate,101)= CONVERT(VARCHAR(25),getDate(),101)

update attendence set hourlytime = dbo.GetTime(@Username,getdate()),AttendenceDate=getdate()

where username = @Username and CONVERT(VARCHAR(25),AttendenceDate,101)= CONVERT(VARCHAR(25),getDate(),101)

END

else

Begin

Declare @password as varchar(50)

SELEct @password = password from date where username = @Username

insert into date (username,password,logindate,logoutdate) values(@Username,@password,getdate(),getdate())

end

-----------------------------------------------------------------------------