create table date(username varchar(50),password varchar(50), logindate datetime,logoutdate datetime)
---------------------- **aspx.cs file button click event
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();
}
}
CREATE PROCEDURE PMP_Savelogin
@Username AS varchar(50),
@Password AS varchar(50)
AS
BEGIN
INSERT INTO date
(
username,password,
logindate,logoutdate
)
VALUES
(
@username,@password ,
getdate(),getdate()
)
END
--------Default2.aspx page ----------------------
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = Session["username"].ToString();
}
and also default2.aspx page take one hyperlink control and set navigationurl property to Logout.aspx page
after that write code on Logout.aspx.cs file on page load
for updating logoutdate column
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 file**********************
public void saveloginn(string user, DateTime logoutdate)
{
try
{
sqlconn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "PMP_Saveloginn";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlconn;
cmd.Parameters.Add("@Username", SqlDbType.VarChar,50);
cmd.Parameters["@Username"].Value = user;
cmd.Parameters.Add("@Logoutdate", SqlDbType.DateTime);
cmd.Parameters["@Logoutdate"].Value = Convert.ToDateTime(logoutdate);
cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
sqlconn.Close();
}
}
---------after create scalervalued function GetTime() for calculating login logout hours and minute difference
CREATE FUNCTION GetTime(@username VARCHAR(50))
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
SELECT @minute=datediff(minute,logindate,logoutdate) from date where username=@username
RETURN (@hour + ':' + @minute)
END
GO
*********create table attendence to store hourlytime difference******************
create table attendence(username varchar(50), hourlytime varchar(10))
*********procedure for update logout date and call gettime function for calculating time difference and store
***********attendence table***************
Create PROCEDURE PMP_Saveloginn
@Username AS varchar(50),
@Logoutdate AS datetime
AS
BEGIN
update date set logoutdate = @Logoutdate where username=@Username
insert into attendence(username,hourlytime) values(@Username,dbo.GetTime(@Username))
END
*****************************************************************
********Example to print function value****************
print(getdate())
print(dbo.GetTime('smita'))
No comments:
Post a Comment