Follow these thorough steps to save the login date, time, logout date, time, and IP address to SQL Server via ASP.NET MVC. This implementation will cover developing the database schema, stored procedures, and MVC application.
Step 1: Create the Database and Table
Open SQL Server Management Studio (SSMS).
Create a new database named UserActivityDB.
Create a table to store user login/logout information. Execute the following SQL script:
sql
CREATE TABLE UserLog (
Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
UserId INT NOT NULL,
LoginDateTime DATETIME NOT NULL,
LogoutDateTime DATETIME NULL,
IpAddress VARCHAR(45) NOT NULL
);
Step 2: Create Stored Procedures
Create stored procedures to handle login and logout activities.
Stored Procedure for Login:
sql
CREATE PROCEDURE SP_LogUserLogin
@UserId INT,
@IpAddress VARCHAR(45)
AS
BEGIN
INSERT INTO UserLog (UserId, LoginDateTime, IpAddress)
VALUES (@UserId, GETDATE(), @IpAddress);
END
Stored Procedure for Logout:
sql
CREATE PROCEDURE SP_LogUserLogout
@UserId INT
AS
BEGIN
UPDATE UserLog
SET LogoutDateTime = GETDATE()
WHERE UserId = @UserId AND LogoutDateTime IS NULL;
END
Step 3: Create the ASP.NET MVC Project
Open Visual Studio.
Create a new ASP.NET MVC project:
Go to File > New > Project.
Select ASP.NET Web Application and name your project (e.g., UserActivityApp).
Choose the MVC template and click OK.
Step 4: Create the Model
Create a Model Class:
In the Models folder, create a new class named UserLogModel.cs:
csharp
public class UserLogModel
{
public int Id { get; set; }
public int UserId { get; set; }
public DateTime LoginDateTime { get; set; }
public DateTime? LogoutDateTime { get; set; }
public string IpAddress { get; set; }
}
Step 5: Create the Controller
Create a Controller:
Right-click on the Controllers folder and add a new controller named AccountController.cs:
csharp
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
using YourNamespace.Models; // Update with your actual namespace
public class AccountController : Controller
{
private string connectionString = “YourConnectionStringHere”; // Update with your connection string
public ActionResult Login()
{
return View();
}
[HttpPost]
public ActionResult Login(string username, string password)
{
// Validate user credentials (this is a placeholder, implement your own logic)
int userId = ValidateUser(username, password);
if (userId > 0) // User is valid
{
// Get IP address
string ipAddress = Request.UserHostAddress;
// Log user login
LogUserLogin(userId, ipAddress);
// Set session or cookie for user
Session[“UserId”] = userId;
return RedirectToAction(“Index”, “Home”); // Redirect to home page
}
ModelState.AddModelError(“”, “Invalid username or password.”);
return View();
}
public ActionResult Logout()
{
int userId = (int)Session[“UserId”];
LogUserLogout(userId);
Session[“UserId”] = null; // Clear session
return RedirectToAction(“Login”);
}
private int ValidateUser(string username, string password)
{
// Implement your user validation logic here.
// Return user ID if valid, otherwise return 0.
return 1; // Placeholder for demonstration
}
private void LogUserLogin(int userId, string ipAddress)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(“SP_LogUserLogin”, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@UserId”, userId);
cmd.Parameters.AddWithValue(“@IpAddress”, ipAddress);
conn.Open();
cmd.ExecuteNonQuery();
}
}
private void LogUserLogout(int userId)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(“SP_LogUserLogout”, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@UserId”, userId);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
Step 6: Create the Login View
Create the Login View:
Right-click on the Login action in AccountController and select Add View.
Name the view Login.cshtml.
xml
@model YourNamespace.Models.UserLogModel
@{
ViewBag.Title = “Login”;
}
<h2>Login</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
<label>Username</label>
<input type=”text” name=”username” required />
</div>
<div>
<label>Password</label>
<input type=”password” name=”password” required />
</div>
<div>
<input type=”submit” value=”Login” />
</div>
}
Step 7: Configure the Connection String
Update Connection String:
Open Web.config and add your connection string inside the <connectionStrings> section:
xml
<connectionStrings>
<add name=”DefaultConnection” connectionString=”Data Source=YOUR_SERVER;Initial Catalog=UserActivityDB;Integrated Security=True” providerName=”System.Data.SqlClient” />
</connectionStrings>
Update the connectionString variable in the AccountController to use this connection string:
csharp
private string connectionString = ConfigurationManager.ConnectionStrings[“DefaultConnection”].ConnectionString;
Step 8: Run Your Application
Start the Application:
Press F5 to run your application.
Navigate to /Account/Login to access the login page.
Enter the username and password to log in.
Conclusion
This post walks you through the entire process of saving the login date, time, logout date, time, and IP address to SQL Server using ASP.NET MVC. You can improve this implementation by including features such as:
- User validation logic to check against a database.
- Error handling for database operations.
- Session management to keep track of user sessions.
