How can Store login in history Asp.Net and Oracle

Step :1 Make this store procedure
PROCEDURE Proc_InsLogginDetail(LoginId IN VARCHAR2,IpAddress IN VARCHAR2,RETOUT OUT NUMBER) IS
BEGIN
SELECT COUNT(*) INTO TEMPCOUNT FROM MONT_LOGIN_HISTORY WHERE USER_ID=LoginId;
IF (TEMPCOUNT>0) THEN
SELECT TO_CHAR(LOGIN_DATE,’DD/MM/YYYY’)LOGIN_DATE,TO_CHAR(SYSDATE,’DD/MM/YYYY’)SystemDate,DAY_COUNT,
TOTAL_COUNT INTO LoginDate,SystemDate,DAY_TOTAL,TotalCount
FROM MONT_LOGIN_HISTORY   WHERE USER_ID=LoginId
AND LOGIN_DATE=(SELECT MAX(LOGIN_DATE) FROM MONT_LOGIN_HISTORY WHERE USER_ID=LoginId);
END IF;
–SELECT TO_CHAR(TRUNC(SYSDATE),’HH:MM:SS AM’) from dual;
IF ((TEMPCOUNT = 0) OR  (LoginDate <> SystemDate)) THEN
INSERT INTO MONT_LOGIN_HISTORY
(USER_ID,IP_ADDRESS,LOGIN_DATE,DAY_COUNT,TOTAL_COUNT) VALUES(LoginId,IpAddress,sysdate,1,TotalCount+1);
RETOUT :=1;
END IF;
IF (LoginDate = SystemDate) THEN
BEGIN
UPDATE MONT_LOGIN_HISTORY SET IP_ADDRESS=IpAddress,
TOTAL_COUNT=TOTAL_COUNT+1,DAY_COUNT=DAY_COUNT+1,LOGIN_DATE=SystemDate WHERE USER_ID=LoginId
AND TO_CHAR(LoginDate,’dd/MM/yyyy’)=SystemDate;
RETOUT:=2;
END;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETOUT:=-100;
END;

Step 2: Now Write Down this code on LoginHistory Method

Public Sub User_LoggedIn_History(ByVal LoginId As String, ByRef RETOUT As Integer)

Dim dt As New DataTable

Dim strHostName, Ip_Address As String

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd As New OracleClient.OracleCommand()

cmd.Connection = conn

strHostName = System.Net.Dns.GetHostName()

Ip_Address = System.Net.Dns.GetHostAddresses(strHostName).GetValue(0).ToString()

‘Dim Ip_Address As String = Request.ServerVariables(“REMOTE_ADDR”)

If Ip_Address.Length < 1 Then

Ip_Address = “11”

End If

‘Try

‘    Dim adp As New OracleDataAdapter(“Proc_GetLoggedInHistory”, conn)

‘    adp.SelectCommand.CommandType = CommandType.StoredProcedure

‘    adp.SelectCommand.Parameters.AddWithValue(“LoginId”, HttpContext.Current.Session(“LoginId”))

‘    adp.SelectCommand.ExecuteNonQuery()

‘    adp.Fill(dt)

‘Catch ex As Exception

‘    ex.Message.ToString()

‘End Try

‘Dim LoginDate As DateTime = dt.Rows(0)(0)

If Ip_Address <> “193.193.193.251” Then

If Ip_Address <> “127.0.0.1” Then

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = “PKG_MCFMS.Proc_InsLogginDetail”

cmd.Parameters.AddWithValue(“LoginId”, LoginId)

cmd.Parameters.AddWithValue(“IpAddress”, Ip_Address)

cmd.Parameters.Add(“RETOUT”, OracleType.Number).Direction = ParameterDirection.Output

cmd.ExecuteNonQuery()

RETOUT = (cmd.Parameters(“RETOUT”).Value)

cmd.Dispose()

End If

End If

End Sub

Now Call this on Login Click Button

Table Structure For LoginHistory

Be Happy and Enjoy in Programming World!!!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s