Project Introduction
The Search Engine Project is designed to automate the process of crawling web pages, indexing their content, and managing search queries. Built using ASP.NET and SQL Server, this application provides a robust platform for users to manage URLs, track their crawling status, and analyze search results. The system allows users to submit URLs for crawling, view indexed pages, and gather analytics on search queries, enhancing the efficiency of web data management and retrieval.
Project Objectives
- To create a secure user authentication system for managing user accounts and roles.
- To enable users to submit and manage URLs for crawling.
- To track the status of each URL, including its last crawled date and any errors encountered.
- To index pages associated with crawled URLs, capturing their titles, content, and meta descriptions.
- To log user search queries and their corresponding search terms.
- To store and manage search results, including the ranking of indexed pages for each query.
- To implement a caching mechanism for frequently accessed data to improve performance.
- To collect and analyze analytics data related to user queries, including clicks and impressions.
- To gather user feedback to continuously improve the system's functionality and user experience.
- To manage security settings to ensure the integrity and safety of the application.
Project Modules
- User Management Module: Handles user registration, login, and role management.
- URL Management Module: Allows users to submit, edit, and delete URLs for crawling.
- Crawling Management Module: Manages the crawling process and tracks the status of each URL.
- Indexing Module: Facilitates the indexing of pages and stores their content and metadata.
- Query Management Module: Logs user search queries and tracks their details.
- Search Results Module: Manages and displays search results based on user queries.
- Analytics Module: Collects and analyzes data related to user queries and interactions.
- Feedback Module: Collects user feedback to enhance the system's features and usability.
- Security Settings Module: Manages application security settings and configurations.
SQL Server Database Tables
-- Users Table
CREATE TABLE Users (
UserId INT PRIMARY KEY IDENTITY(1,1),
Username NVARCHAR(50) NOT NULL UNIQUE,
PasswordHash NVARCHAR(255) NOT NULL,
Email NVARCHAR(100) NOT NULL UNIQUE,
CreatedAt DATETIME DEFAULT GETDATE(),
UpdatedAt DATETIME DEFAULT GETDATE(),
RoleId INT,
FOREIGN KEY (RoleId) REFERENCES Roles(RoleId)
);
-- Roles Table
CREATE TABLE Roles (
RoleId INT PRIMARY KEY IDENTITY(1,1),
RoleName NVARCHAR(50) NOT NULL UNIQUE
);
-- URLs Table
CREATE TABLE URLs (
URLId INT PRIMARY KEY IDENTITY(1,1),
URL NVARCHAR(255) NOT NULL UNIQUE,
Status NVARCHAR(20) DEFAULT 'Pending', -- e.g., Pending, Crawled, Error
LastCrawled DATETIME,
CreatedAt DATETIME DEFAULT GETDATE(),
UpdatedAt DATETIME DEFAULT GETDATE()
);
-- IndexedPages Table
CREATE TABLE IndexedPages (
PageId INT PRIMARY KEY IDENTITY(1,1),
URLId INT,
Title NVARCHAR(255),
Content NVARCHAR(MAX),
MetaDescription NVARCHAR(255),
CreatedAt DATETIME DEFAULT GETDATE(),
UpdatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (URLId) REFERENCES URLs(URLId)
);
-- Queries Table
CREATE TABLE Queries (
QueryId INT PRIMARY KEY IDENTITY(1,1),
UserId INT,
SearchTerm NVARCHAR(255) NOT NULL,
SearchDate DATETIME DEFAULT GETDATE(),
FOREIGN KEY (User Id) REFERENCES Users(UserId)
);
-- SearchResults Table
CREATE TABLE SearchResults (
ResultId INT PRIMARY KEY IDENTITY(1,1),
QueryId INT,
PageId INT,
Rank INT,
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (QueryId) REFERENCES Queries(QueryId),
FOREIGN KEY (PageId) REFERENCES IndexedPages(PageId)
);
-- CacheItems Table
CREATE TABLE CacheItems (
CacheItemId INT PRIMARY KEY IDENTITY(1,1),
Key NVARCHAR(255) NOT NULL UNIQUE,
Value NVARCHAR(MAX),
Expiration DATETIME,
CreatedAt DATETIME DEFAULT GETDATE(),
UpdatedAt DATETIME DEFAULT GETDATE()
);
-- AnalyticsData Table
CREATE TABLE AnalyticsData (
AnalyticsId INT PRIMARY KEY IDENTITY(1,1),
QueryId INT,
Clicks INT DEFAULT 0,
Impressions INT DEFAULT 0,
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (QueryId) REFERENCES Queries(QueryId)
);
-- Feedback Table
CREATE TABLE Feedbacks (
FeedbackId INT PRIMARY KEY IDENTITY(1,1),
UserId INT,
Comments NVARCHAR(MAX),
Rating INT CHECK (Rating >= 1 AND Rating <= 5),
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (User Id) REFERENCES Users(UserId)
);
-- SecuritySettings Table
CREATE TABLE SecuritySettings (
SettingId INT PRIMARY KEY IDENTITY(1,1),
SettingName NVARCHAR(100) NOT NULL UNIQUE,
SettingValue NVARCHAR(255),
CreatedAt DATETIME DEFAULT GETDATE(),
UpdatedAt DATETIME DEFAULT GETDATE()
);
Explanation of Tables
Users: Stores user information, including credentials and roles.
Roles: Defines different roles within the system (e.g., admin, user).
URLs: Manages the URLs that the crawler will process, including their status and last crawled date.
IndexedPages: Contains the pages that have been indexed, including their content and metadata.
Queries: Records search queries made by users, along with the search date.
SearchResults: Stores the results of search queries, linking them to the indexed pages and their rank.
CacheItems: Manages cached items for quick retrieval of frequently accessed data.
AnalyticsData: Tracks analytics related to search queries, such as clicks and impressions.
Feedback: Collects feedback from users regarding their search experience.
SecuritySettings: Stores security-related settings for the application.
To create a model and repository pattern using ADO.NET for the provided SQL Server tables in an ASP.NET application
We will follow these steps:
Define the Models
Create C# classes that represent each table.
public class User
{
public int UserId { get; set; }
public string Username { get; set; }
public string PasswordHash { get; set; }
public string Email { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
public int? RoleId { get; set; }
}
public class Role
{
public int RoleId { get; set; }
public string RoleName { get; set; }
}
public class URL
{
public int URLId { get; set; }
public string URLValue { get; set; }
public string Status { get; set; }
public DateTime? LastCrawled { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}
public class IndexedPage
{
public int PageId { get; set; }
public int URLId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public string MetaDescription { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}
public class Query
{
public int QueryId { get; set; }
public int UserId { get; set; }
public string SearchTerm { get; set; }
public DateTime SearchDate { get; set; }
}
public class SearchResult
{
public int ResultId { get; set; }
public int QueryId { get; set; }
public int PageId { get; set; }
public int Rank { get; set; }
public DateTime CreatedAt { get; set; }
}
public class CacheItem
{
public int CacheItemId { get; set; }
public string Key { get; set; }
public string Value { get; set; }
public DateTime? Expiration { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}
public class AnalyticsData
{
public int AnalyticsId { get; set; }
public int QueryId { get; set; }
public int Clicks { get; set; }
public int Impressions { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Feedback
{
public int FeedbackId { get; set; }
public int UserId { get; set; }
public string Comments { get; set; }
public int Rating { get; set; }
public DateTime CreatedAt { get; set; }
}
public class SecuritySetting
{
public int SettingId { get; set; }
public string SettingName { get; set; }
public string SettingValue { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}
Create the Repository Interfaces
Define interfaces for each repository:
public interface IUserRepository
{
User GetUser ById(int userId);
void AddUser (User user);
void UpdateUser (User user);
void DeleteUser (int userId);
IEnumerable<User> GetAllUsers();
}
public interface IRoleRepository
{
Role GetRoleById(int roleId);
void AddRole(Role role);
void UpdateRole(Role role);
void DeleteRole(int roleId);
IEnumerable<Role> GetAllRoles();
}
Implement the Repositories
Here is an example implementation of the User Repository using ADO.NET:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class UserRepository : IUserRepository
{
private readonly string _connectionString;
public UserRepository(string connectionString)
{
_connectionString = connectionString;
}
public User GetUser ById(int userId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Users WHERE UserId = @User Id", connection);
command.Parameters .AddWithValue("@User Id", userId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new User
{
UserId = (int)reader["User Id"],
Username = reader["Username"].ToString(),
PasswordHash = reader["PasswordHash"].ToString(),
Email = reader["Email"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"],
UpdatedAt = (DateTime)reader["UpdatedAt"],
RoleId = reader["RoleId"] as int?
};
}
}
}
return null;
}
public void AddUser (User user)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Users (Username, PasswordHash, Email, RoleId) VALUES (@Username, @PasswordHash, @Email, @RoleId)", connection);
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@PasswordHash", user.PasswordHash);
command.Parameters.AddWithValue("@Email", user.Email);
command.Parameters.AddWithValue("@RoleId", user.RoleId.HasValue ? (object)user.RoleId.Value : DBNull.Value);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateUser (User user)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Users SET Username = @Username, PasswordHash = @PasswordHash, Email = @Email, RoleId = @RoleId, UpdatedAt = GETDATE() WHERE UserId = @User Id", connection);
command.Parameters.AddWithValue("@User Id", user.UserId);
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@PasswordHash", user.PasswordHash);
command.Parameters.AddWithValue("@Email", user.Email);
command.Parameters.AddWithValue("@RoleId", user.RoleId.HasValue ? (object)user.RoleId.Value : DBNull.Value);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteUser (int userId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Users WHERE UserId = @User Id", connection);
command.Parameters.AddWithValue("@User Id", userId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<User> GetAllUsers()
{
var users = new List<User>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Users", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
users.Add(new User
{
UserId = (int)reader["User Id"],
Username = reader["Username"].ToString(),
PasswordHash = reader["PasswordHash"].ToString(),
Email = reader["Email"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"],
UpdatedAt = (DateTime)reader["UpdatedAt"],
RoleId = reader["RoleId"] as int?
});
}
}
}
return users;
}
}
This pattern can be replicated for other entities by creating similar repository classes for Role, URL, IndexedPage, Query, SearchResult, CacheItem, AnalyticsData, Feedback, and SecuritySetting. Each repository will implement its respective interface and contain methods for CRUD operations using ADO.NET.
Sure! Below are the repository implementations for the remaining models
Query, SearchResult, CacheItem, AnalyticsData, Feedback, and SecuritySetting. Each repository will implement its respective interface and contain methods for CRUD operations using ADO.NET.
Query Repository
public interface IQueryRepository
{
Query GetQueryById(int queryId);
void AddQuery(Query query);
void UpdateQuery(Query query);
void DeleteQuery(int queryId);
IEnumerable<Query> GetAllQueries();
}
public class QueryRepository : IQueryRepository
{
private readonly string _connectionString;
public QueryRepository(string connectionString)
{
_connectionString = connectionString;
}
public Query GetQueryById(int queryId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Queries WHERE QueryId = @QueryId", connection);
command.Parameters.AddWithValue("@QueryId", queryId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new Query
{
QueryId = (int)reader["QueryId"],
UserId = (int)reader["User Id"],
SearchTerm = reader["SearchTerm"].ToString(),
SearchDate = (DateTime)reader["SearchDate"]
};
}
}
}
return null;
}
public void AddQuery(Query query)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Queries (User Id, SearchTerm) VALUES (@User Id, @SearchTerm)", connection);
command.Parameters.AddWithValue("@User Id", query.UserId);
command.Parameters.AddWithValue("@SearchTerm", query.SearchTerm);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateQuery(Query query)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Queries SET UserId = @User Id, SearchTerm = @SearchTerm, SearchDate = GETDATE() WHERE QueryId = @QueryId", connection);
command.Parameters.AddWithValue("@QueryId", query.QueryId);
command.Parameters.AddWithValue("@User Id", query.UserId);
command.Parameters.AddWithValue("@SearchTerm", query.SearchTerm);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteQuery(int queryId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Queries WHERE QueryId = @QueryId", connection);
command.Parameters.AddWithValue("@QueryId", queryId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<Query> GetAllQueries()
{
var queries = new List<Query>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Queries", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
queries.Add(new Query
{
QueryId = (int)reader["QueryId"],
UserId = (int)reader["User Id"],
SearchTerm = reader["SearchTerm"].ToString(),
SearchDate = (DateTime)reader["SearchDate"]
});
}
}
}
return queries;
}
}
SearchResult Repository
public interface ISearchResultRepository
{
SearchResult GetSearchResultById(int resultId);
void AddSearchResult(SearchResult searchResult);
void UpdateSearchResult(SearchResult searchResult);
void DeleteSearchResult(int resultId);
IEnumerable<SearchResult> GetAllSearchResults();
}
public class SearchResultRepository : ISearchResultRepository
{
private readonly string _connectionString;
public SearchResultRepository(string connectionString)
{
_connectionString = connectionString;
}
public SearchResult GetSearchResultById(int resultId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM SearchResults WHERE ResultId = @ResultId", connection);
command.Parameters.AddWithValue("@ResultId", resultId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new SearchResult
{
ResultId = (int)reader["ResultId"],
QueryId = (int)reader["QueryId"],
PageId = (int)reader["PageId"],
Rank = (int)reader["Rank"],
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return null;
}
public void AddSearchResult(SearchResult searchResult)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO SearchResults (QueryId, PageId, Rank) VALUES (@QueryId, @PageId, @Rank)", connection);
command.Parameters.AddWithValue("@QueryId", searchResult.QueryId);
command.Parameters.AddWithValue("@PageId", searchResult.PageId);
command.Parameters.AddWithValue("@Rank", searchResult.Rank);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateSearchResult(SearchResult searchResult)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE SearchResults SET QueryId = @QueryId, PageId = @PageId, Rank = @Rank WHERE ResultId = @ResultId", connection);
command.Parameters.AddWithValue("@ResultId", searchResult.ResultId);
command.Parameters.AddWithValue("@QueryId", searchResult.QueryId);
command.Parameters.AddWithValue("@PageId", searchResult.PageId);
command.Parameters.AddWithValue("@Rank", searchResult.Rank);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteSearchResult(int resultId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM SearchResults WHERE ResultId = @ResultId", connection);
command.Parameters.AddWithValue("@ResultId", resultId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<SearchResult> GetAllSearchResults()
{
var searchResults = new List<SearchResult>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM SearchResults", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
searchResults.Add(new SearchResult
{
ResultId = (int)reader["ResultId"],
QueryId = (int)reader["QueryId"],
PageId = (int)reader["PageId"],
Rank = (int)reader["Rank"],
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return searchResults;
}
}
CacheItem Repository
public interface ICacheItemRepository
{
CacheItem GetCacheItemById(int cacheItemId);
void AddCacheItem(CacheItem cacheItem);
void UpdateCacheItem(CacheItem cacheItem);
void DeleteCacheItem(int cacheItemId);
IEnumerable<CacheItem> GetAllCacheItems();
}
public class CacheItemRepository : ICacheItemRepository
{
private readonly string _connectionString;
public CacheItemRepository(string connectionString)
{
_connectionString = connectionString;
}
public CacheItem GetCacheItemById(int cacheItemId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM CacheItems WHERE CacheItemId = @CacheItemId", connection);
command.Parameters.AddWithValue("@CacheItemId", cacheItemId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new CacheItem
{
CacheItemId = (int)reader["CacheItemId"],
Key = reader["Key"].ToString(),
Value = reader["Value"].ToString(),
Expiration = reader["Expiration"] as DateTime?,
CreatedAt = (DateTime)reader["CreatedAt"],
UpdatedAt = (DateTime)reader["UpdatedAt"]
};
}
}
}
return null;
}
public void AddCacheItem(CacheItem cacheItem)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO CacheItems (Key, Value, Expiration) VALUES (@Key, @Value, @Expiration)", connection);
command.Parameters.AddWithValue("@Key", cacheItem.Key);
command.Parameters.AddWithValue("@Value", cacheItem.Value);
command.Parameters.AddWithValue("@Expiration", cacheItem.Expiration.HasValue ? (object)cacheItem.Expiration.Value : DBNull.Value);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateCacheItem(CacheItem cacheItem)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE CacheItems SET Key = @Key, Value = @Value, Expiration = @Expiration, UpdatedAt = GETDATE() WHERE CacheItemId = @CacheItemId", connection);
command.Parameters.AddWithValue("@CacheItemId", cacheItem .CacheItemId);
command.Parameters.AddWithValue("@Key", cacheItem.Key);
command.Parameters.AddWithValue("@Value", cacheItem.Value);
command.Parameters.AddWithValue("@Expiration", cacheItem.Expiration.HasValue ? (object)cacheItem.Expiration.Value : DBNull.Value);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteCacheItem(int cacheItemId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM CacheItems WHERE CacheItemId = @CacheItemId", connection);
command.Parameters.AddWithValue("@CacheItemId", cacheItemId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<CacheItem> GetAllCacheItems()
{
var cacheItems = new List<CacheItem>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM CacheItems", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
cacheItems.Add(new CacheItem
{
CacheItemId = (int)reader["CacheItemId"],
Key = reader["Key"].ToString(),
Value = reader["Value"].ToString(),
Expiration = reader["Expiration"] as DateTime?,
CreatedAt = (DateTime)reader["CreatedAt"],
UpdatedAt = (DateTime)reader["UpdatedAt"]
});
}
}
}
return cacheItems;
}
}
AnalyticsData Repository
public interface IAnalyticsDataRepository
{
AnalyticsData GetAnalyticsDataById(int analyticsId);
void AddAnalyticsData(AnalyticsData analyticsData);
void UpdateAnalyticsData(AnalyticsData analyticsData);
void DeleteAnalyticsData(int analyticsId);
IEnumerable<AnalyticsData> GetAllAnalyticsData();
}
public class AnalyticsDataRepository : IAnalyticsDataRepository
{
private readonly string _connectionString;
public AnalyticsDataRepository(string connectionString)
{
_connectionString = connectionString;
}
public AnalyticsData GetAnalyticsDataById(int analyticsId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM AnalyticsData WHERE AnalyticsId = @AnalyticsId", connection);
command.Parameters.AddWithValue("@AnalyticsId", analyticsId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new AnalyticsData
{
AnalyticsId = (int)reader["AnalyticsId"],
QueryId = (int)reader["QueryId"],
Clicks = (int)reader["Clicks"],
Impressions = (int)reader["Impressions"],
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return null;
}
public void AddAnalyticsData(AnalyticsData analyticsData)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO AnalyticsData (QueryId, Clicks, Impressions) VALUES (@QueryId, @Clicks, @Impressions)", connection);
command.Parameters.AddWithValue("@QueryId", analyticsData.QueryId);
command.Parameters.AddWithValue("@Clicks", analyticsData.Clicks);
command.Parameters.AddWithValue("@Impressions", analyticsData.Impressions);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateAnalyticsData(AnalyticsData analyticsData)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE AnalyticsData SET QueryId = @QueryId, Clicks = @Clicks, Impressions = @Impressions WHERE AnalyticsId = @AnalyticsId", connection);
command.Parameters.AddWithValue("@AnalyticsId", analyticsData.AnalyticsId);
command.Parameters.AddWithValue("@QueryId", analyticsData.QueryId);
command.Parameters.AddWithValue("@Clicks", analyticsData.Clicks);
command.Parameters.AddWithValue("@Impressions", analyticsData.Impressions);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteAnalyticsData(int analyticsId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM AnalyticsData WHERE AnalyticsId = @AnalyticsId", connection);
command.Parameters.AddWithValue("@AnalyticsId", analyticsId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<AnalyticsData> GetAllAnalyticsData()
{
var analyticsDataList = new List<AnalyticsData>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM AnalyticsData", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
analyticsDataList.Add(new AnalyticsData
{
AnalyticsId = (int)reader["AnalyticsId"],
QueryId = (int)reader["QueryId"],
Clicks = (int)reader["Clicks"],
Impressions = (int)reader["Impressions"],
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return analyticsDataList;
}
}
Feedback Repository
public interface IFeedbackRepository
{
Feedback GetFeedbackById(int feedbackId);
void AddFeedback(Feedback feedback);
void UpdateFeedback(Feedback feedback);
void DeleteFeedback(int feedbackId);
IEnumerable<Feedback> GetAllFeedbacks();
}
public class FeedbackRepository : IFeedbackRepository
{
private readonly string _connectionString;
public FeedbackRepository(string connectionString)
{
_connectionString = connectionString;
}
public Feedback GetFeedbackById(int feedbackId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Feedbacks WHERE FeedbackId = @FeedbackId", connection);
command.Parameters.AddWithValue("@FeedbackId", feedbackId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new Feedback
{
FeedbackId = (int)reader["FeedbackId"],
UserId = (int)reader["User Id"],
Comments = reader["Comments"].ToString(),
Rating = (int)reader["Rating"],
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return null;
}
public void AddFeedback(Feedback feedback)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Feedbacks (User Id, Comments, Rating) VALUES (@User Id, @Comments, @Rating)", connection);
command.Parameters.AddWithValue("@User Id", feedback.UserId);
command.Parameters.AddWithValue("@Comments", feedback.Comments);
command.Parameters.AddWithValue("@Rating", feedback.Rating);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateFeedback(Feedback feedback)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Feedbacks SET UserId = @User Id, Comments = @Comments, Rating = @Rating WHERE FeedbackId = @FeedbackId", connection);
command.Parameters.AddWithValue("@FeedbackId", feedback.FeedbackId);
command.Parameters.AddWithValue("@User Id", feedback.UserId);
command.Parameters.AddWithValue("@Comments", feedback.Comments);
command.Parameters.AddWithValue("@Rating", feedback.Rating);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteFeedback(int feedbackId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Feedbacks WHERE FeedbackId = @FeedbackId", connection);
command.Parameters.AddWithValue("@FeedbackId", feedbackId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<Feedback> GetAllFeedbacks()
{
var feedbacks = new List<Feedback>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Feedbacks", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
feedbacks.Add(new Feedback
{
FeedbackId = (int)reader["FeedbackId"],
UserId = (int)reader["User Id"],
Comments = reader["Comments"].ToString(),
Rating = (int)reader["Rating"],
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return feedbacks;
}
}
SecuritySetting Repository
public interface ISecuritySettingRepository
{
SecuritySetting GetSecuritySettingById(int settingId);
void AddSecuritySetting(SecuritySetting securitySetting);
void UpdateSecuritySetting(SecuritySetting securitySetting);
void DeleteSecuritySetting(int settingId);
IEnumerable<SecuritySetting> GetAllSecuritySettings();
}
public class SecuritySettingRepository : ISecuritySettingRepository
{
private readonly string _connectionString;
public SecuritySettingRepository(string connectionString)
{
_connectionString = connectionString;
}
public SecuritySetting GetSecuritySettingById(int settingId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM SecuritySettings WHERE SettingId = @SettingId", connection);
command.Parameters.AddWithValue("@SettingId", settingId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new SecuritySetting
{
SettingId = (int)reader["SettingId"],
SettingName = reader["SettingName"].ToString(),
SettingValue = reader["SettingValue"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"],
UpdatedAt = (DateTime)reader["UpdatedAt"]
};
}
}
}
return null;
}
public void AddSecuritySetting(SecuritySetting securitySetting)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO SecuritySettings (SettingName, SettingValue) VALUES (@SettingName, @SettingValue)", connection);
command.Parameters.AddWithValue("@SettingName", securitySetting.SettingName);
command.Parameters.AddWithValue("@SettingValue", securitySetting.SettingValue);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateSecuritySetting(SecuritySetting securitySetting)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE SecuritySettings SET SettingName = @SettingName, SettingValue = @SettingValue, UpdatedAt = GETDATE() WHERE SettingId = @SettingId", connection);
command.Parameters.AddWithValue("@SettingId", securitySetting.SettingId);
command.Parameters.AddWithValue("@SettingName", securitySetting.SettingName);
command.Parameters.AddWithValue("@SettingValue", securitySetting.SettingValue);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteSecuritySetting(int settingId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM SecuritySettings WHERE SettingId = @SettingId", connection);
command.Parameters.AddWithValue("@SettingId", settingId);
connection.Open();
command.ExecuteNonQuery();
}
}
public IEnumerable<SecuritySetting> GetAllSecuritySettings()
{
var securitySettings = new List<SecuritySetting>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM SecuritySettings", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
securitySettings.Add(new SecuritySetting
{
SettingId = (int)reader["SettingId"],
SettingName = reader["SettingName"].ToString(),
SettingValue = reader["SettingValue"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"],
UpdatedAt = (DateTime)reader["UpdatedAt"]
});
}
}
}
return securitySettings;
}
}
This completes the repository implementations for all the models defined in your SQL Server tables
Each repository provides methods for creating, reading, updating, and deleting records in the corresponding database table.
To create controllers for each repository in an ASP.NET MVC application
We will follow the standard MVC pattern. Each controller will handle HTTP requests, interact with the corresponding repository, and return views. Below are the example controllers for each repository.
UserController
public class UserController : Controller
{
private readonly IUserRepository _userRepository;
public UserController(IUser Repository userRepository)
{
_userRepository = userRepository;
}
public ActionResult Index()
{
IEnumerable<User> users = _userRepository.GetAllUsers();
return View(users);
}
public ActionResult Details(int id)
{
User user = _userRepository.GetUser ById(id);
return View(user);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(User user)
{
if (ModelState.IsValid)
{
_userRepository.AddUser (user);
return RedirectToAction("Index");
}
return View(user);
}
public ActionResult Edit(int id)
{
User user = _userRepository.GetUser ById(id);
return View(user);
}
[HttpPost]
public ActionResult Edit(User user)
{
if (ModelState.IsValid)
{
_userRepository.UpdateUser (user);
return RedirectToAction("Index");
}
return View(user);
}
public ActionResult Delete(int id)
{
User user = _userRepository.GetUser ById(id);
return View(user);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_userRepository.DeleteUser (id);
return RedirectToAction("Index");
}
}
RoleController
public class RoleController : Controller
{
private readonly IRoleRepository _roleRepository;
public RoleController(IRoleRepository roleRepository)
{
_roleRepository = roleRepository;
}
public ActionResult Index()
{
IEnumerable<Role> roles = _roleRepository.GetAllRoles();
return View(roles);
}
public ActionResult Details(int id)
{
Role role = _roleRepository.GetRoleById(id);
return View(role);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(Role role)
{
if (ModelState.IsValid)
{
_roleRepository.AddRole(role);
return RedirectToAction("Index");
}
return View(role);
}
public ActionResult Edit(int id)
{
Role role = _roleRepository.GetRoleById(id);
return View(role);
}
[HttpPost]
public ActionResult Edit(Role role)
{
if (ModelState.IsValid)
{
_roleRepository.UpdateRole(role);
return RedirectToAction("Index");
}
return View(role);
}
public ActionResult Delete(int id)
{
Role role = _roleRepository.GetRoleById(id);
return View(role);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_roleRepository.DeleteRole(id);
return RedirectToAction("Index");
}
}
URLController
public class URLController : Controller
{
private readonly IURLRepository _urlRepository;
public URLController(IURLRepository urlRepository)
{
_urlRepository = urlRepository;
}
public ActionResult Index()
{
IEnumerable<URL> urls = _urlRepository.GetAllURLs();
return View(urls);
}
public ActionResult Details(int id)
{
URL url = _urlRepository.GetURLById(id);
return View(url);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(URL url)
{
if (ModelState.IsValid)
{
_urlRepository.AddURL(url);
return RedirectToAction("Index");
}
return View(url);
}
public ActionResult Edit(int id)
{
URL url = _urlRepository.GetURLById(id);
return View(url);
}
[HttpPost]
public ActionResult Edit(URL url)
{
if (ModelState.IsValid)
{
_urlRepository.UpdateURL(url);
return RedirectToAction("Index");
}
return View(url);
}
public ActionResult Delete(int id)
{
URL url = _urlRepository.GetURLById(id);
return View(url);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_urlRepository.DeleteURL(id);
return Redirect ToAction("Index");
}
}
4. QueryController
public class QueryController : Controller
{
private readonly IQueryRepository _queryRepository;
public QueryController(IQueryRepository queryRepository)
{
_queryRepository = queryRepository;
}
public ActionResult Index()
{
IEnumerable<Query> queries = _queryRepository.GetAllQueries();
return View(queries);
}
public ActionResult Details(int id)
{
Query query = _queryRepository.GetQueryById(id);
return View(query);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(Query query)
{
if (ModelState.IsValid)
{
_queryRepository.AddQuery(query);
return RedirectToAction("Index");
}
return View(query);
}
public ActionResult Edit(int id)
{
Query query = _queryRepository.GetQueryById(id);
return View(query);
}
[HttpPost]
public ActionResult Edit(Query query)
{
if (ModelState.IsValid)
{
_queryRepository.UpdateQuery(query);
return RedirectToAction("Index");
}
return View(query);
}
public ActionResult Delete(int id)
{
Query query = _queryRepository.GetQueryById(id);
return View(query);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_queryRepository.DeleteQuery(id);
return RedirectToAction("Index");
}
}
SearchResultController
public class SearchResultController : Controller
{
private readonly ISearchResultRepository _searchResultRepository;
public SearchResultController(ISearchResultRepository searchResultRepository)
{
_searchResultRepository = searchResultRepository;
}
public ActionResult Index()
{
IEnumerable<SearchResult> searchResults = _searchResultRepository.GetAllSearchResults();
return View(searchResults);
}
public ActionResult Details(int id)
{
SearchResult searchResult = _searchResultRepository.GetSearchResultById(id);
return View(searchResult);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(SearchResult searchResult)
{
if (ModelState.IsValid)
{
_searchResultRepository.AddSearchResult(searchResult);
return RedirectToAction("Index");
}
return View(searchResult);
}
public ActionResult Edit(int id)
{
SearchResult searchResult = _searchResultRepository.GetSearchResultById(id);
return View(searchResult);
}
[HttpPost]
public ActionResult Edit(SearchResult searchResult)
{
if (ModelState.IsValid)
{
_searchResultRepository.UpdateSearchResult(searchResult);
return RedirectToAction("Index");
}
return View(searchResult);
}
public ActionResult Delete(int id)
{
SearchResult searchResult = _searchResultRepository.GetSearchResultById(id);
return View(searchResult);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_searchResultRepository.DeleteSearchResult(id);
return RedirectToAction("Index");
}
}
CacheItemController
public class CacheItemController : Controller
{
private readonly ICacheItemRepository _cacheItemRepository;
public CacheItemController(ICacheItemRepository cacheItemRepository)
{
_cacheItemRepository = cacheItemRepository;
}
public ActionResult Index()
{
IEnumerable<CacheItem> cacheItems = _cacheItemRepository.GetAllCacheItems();
return View(cacheItems);
}
public ActionResult Details(int id)
{
CacheItem cacheItem = _cacheItemRepository.GetCacheItemById(id);
return View(cacheItem);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(CacheItem cacheItem)
{
if (ModelState.IsValid)
{
_cacheItemRepository.AddCacheItem(cacheItem);
return RedirectToAction("Index");
}
return View(cacheItem);
}
public ActionResult Edit(int id)
{
CacheItem cacheItem = _cacheItemRepository.GetCacheItemById(id);
return View(cacheItem);
}
[HttpPost]
public ActionResult Edit(CacheItem cacheItem)
{
if (ModelState.IsValid)
{
_cacheItemRepository.UpdateCacheItem(cacheItem);
return RedirectToAction("Index");
}
return View(cacheItem);
}
public ActionResult Delete(int id)
{
CacheItem cacheItem = _cacheItemRepository.GetCacheItemById(id);
return View (cacheItem);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_cacheItemRepository.DeleteCacheItem(id);
return RedirectToAction("Index");
}
}
AnalyticsDataController
public class AnalyticsDataController : Controller
{
private readonly IAnalyticsDataRepository _analyticsDataRepository;
public AnalyticsDataController(IAnalyticsDataRepository analyticsDataRepository)
{
_analyticsDataRepository = analyticsDataRepository;
}
public ActionResult Index()
{
IEnumerable<AnalyticsData> analyticsDataList = _analyticsDataRepository.GetAllAnalyticsData();
return View(analyticsDataList);
}
public ActionResult Details(int id)
{
AnalyticsData analyticsData = _analyticsDataRepository.GetAnalyticsDataById(id);
return View(analyticsData);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(AnalyticsData analyticsData)
{
if (ModelState.IsValid)
{
_analyticsDataRepository.AddAnalyticsData(analyticsData);
return RedirectToAction("Index");
}
return View(analyticsData);
}
public ActionResult Edit(int id)
{
AnalyticsData analyticsData = _analyticsDataRepository.GetAnalyticsDataById(id);
return View(analyticsData);
}
[HttpPost]
public ActionResult Edit(AnalyticsData analyticsData)
{
if (ModelState.IsValid)
{
_analyticsDataRepository.UpdateAnalyticsData(analyticsData);
return RedirectToAction("Index");
}
return View(analyticsData);
}
public ActionResult Delete(int id)
{
AnalyticsData analyticsData = _analyticsDataRepository.GetAnalyticsDataById(id);
return View(analyticsData);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_analyticsDataRepository.DeleteAnalyticsData(id);
return RedirectToAction("Index");
}
}
FeedbackController
public class FeedbackController : Controller
{
private readonly IFeedbackRepository _feedbackRepository;
public FeedbackController(IFeedbackRepository feedbackRepository)
{
_feedbackRepository = feedbackRepository;
}
public ActionResult Index()
{
IEnumerable<Feedback> feedbacks = _feedbackRepository.GetAllFeedbacks();
return View(feedbacks);
}
public ActionResult Details(int id)
{
Feedback feedback = _feedbackRepository.GetFeedbackById(id);
return View(feedback);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(Feedback feedback)
{
if (ModelState.IsValid)
{
_feedbackRepository.AddFeedback(feedback);
return RedirectToAction("Index");
}
return View(feedback);
}
public ActionResult Edit(int id)
{
Feedback feedback = _feedbackRepository.GetFeedbackById(id);
return View(feedback);
}
[HttpPost]
public ActionResult Edit(Feedback feedback)
{
if (ModelState.IsValid)
{
_feedbackRepository.UpdateFeedback(feedback);
return RedirectToAction("Index");
}
return View(feedback);
}
public ActionResult Delete(int id)
{
Feedback feedback = _feedbackRepository.GetFeedbackById(id);
return View(feedback);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_feedbackRepository.DeleteFeedback(id);
return RedirectToAction("Index");
}
}
SecuritySettingController
public class SecuritySettingController : Controller
{
private readonly ISecuritySettingRepository _securitySettingRepository;
public SecuritySettingController(ISecuritySettingRepository securitySettingRepository)
{
_securitySettingRepository = securitySettingRepository;
}
public ActionResult Index()
{
IEnumerable<SecuritySetting> securitySettings = _securitySettingRepository.GetAllSecuritySettings();
return View(securitySettings);
}
public ActionResult Details(int id)
{
SecuritySetting securitySetting = _securitySettingRepository.GetSecuritySettingById(id);
return View(securitySetting);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(SecuritySetting securitySetting)
{
if (ModelState.IsValid)
{
_securitySettingRepository.AddSecuritySetting(securitySetting);
return RedirectToAction("Index");
}
return View(securitySetting);
}
public ActionResult Edit(int id)
{
SecuritySetting securitySetting = _securitySettingRepository.GetSecuritySettingById(id);
return View(securitySetting);
}
[HttpPost]
public ActionResult Edit(SecuritySetting securitySetting)
{
if (ModelState.IsValid)
{
_securitySettingRepository.UpdateSecuritySetting(securitySetting);
return RedirectToAction("Index");
}
return View(securitySetting);
}
public ActionResult Delete(int id)
{
SecuritySetting securitySetting = _securitySettingRepository.GetSecuritySettingById(id);
return View(securitySetting);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
_securitySettingRepository.DeleteSecuritySetting(id);
return RedirectToAction("Index");
}
}
This completes the controller implementations for all the repositories defined in your application
Each controller provides methods for handling CRUD operations and returning the appropriate views.
Creating views for each controller in an ASP.NET MVC application
Involves creating Razor view files that correspond to the actions in the controllers. Below, I will provide a basic structure for the views for each controller, including the Index, Details, Create, Edit, and Delete views.
User Views
Views/User/Index.cshtml
@model IEnumerable<User>
<h2>Users</h2>
<a href='@Url.Action("Create")'>Create New User</a>
<table>
<thead>
<tr>
<th>Username</th>
<th>Email</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var user in Model)
{
<tr>
<td>@user.Username</td>
<td>@user.Email</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = user.UserId }) |
@Html.ActionLink("Details", "Details", new { id = user.UserId }) |
@Html.ActionLink("Delete", "Delete", new { id = user.UserId })
</td>
</tr>
}
</tbody>
</table>
Views/User/Details.cshtml
@model User
<h2>User Details</h2>
<div>
<h4>@Model.Username</h4>
<p>Email: @Model.Email</p>
<p>Created At: @Model.CreatedAt</p>
<p>Updated At: @Model.UpdatedAt</p>
</div>
<a href='@Url.Action("Edit", new { id = Model.UserId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/User/Create.cshtml
@model User
<h2>Create User</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.Username)
@Html.TextBoxFor(m => m.Username)
</div>
<div>
@Html.LabelFor(m => m.PasswordHash)
@Html.TextBoxFor(m => m.PasswordHash)
</div>
<div>
@Html.LabelFor(m => m.Email)
@Html.TextBoxFor(m => m.Email)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/User/Edit.cshtml
@model User
<h2>Edit User</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.UserId)
<div>
@Html.LabelFor(m => m.Username)
@Html.TextBoxFor(m => m.Username)
</div>
<div>
@Html.LabelFor(m => m.PasswordHash)
@Html.TextBoxFor(m => m.PasswordHash)
</div>
<div>
@Html.LabelFor(m => m.Email)
@Html.TextBoxFor(m => m.Email)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/User/Delete.cshtml
@model User
<h2>Delete User</h2>
<div>
<h4>Are you sure you want to delete this user?</h4>
<h4>@Model.Username</h4>
<p>Email: @Model.Email</p>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.UserId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
Role Views
Views/Role/Index.cshtml
@model IEnumerable<Role>
<h2>Roles</h2>
<a href='@Url.Action("Create")'>Create New Role</a>
<table>
<thead>
<tr>
<th>Role Name</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var role in Model)
{
<tr>
<td>@role.RoleName</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = role.RoleId }) |
@Html.ActionLink("Details", "Details", new { id = role.RoleId }) |
@Html.ActionLink("Delete", "Delete", new { id = role.RoleId })
</td>
</tr>
}
</tbody>
</table>
Views/Role/Details.cshtml
@model Role
<h2>Role Details</h2>
<div>
<h4>@Model.RoleName</h4>
</div>
<a href='@Url.Action("Edit", new { id = Model.RoleId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/Role/Create.cshtml
@model Role
<h2>Create Role</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.RoleName)
@Html.TextBoxFor(m => m.RoleName)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/Role/Edit.cshtml
@model Role
<h2>Edit Role</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.RoleId)
<div>
@Html.LabelFor(m => m.RoleName)
@Html.TextBoxFor(m => m.RoleName)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/Role/Delete.cshtml
@model Role
<h2>Delete Role</h2>
<div>
<h4>Are you sure you want to delete this role?</h4>
<h4>@Model.RoleName</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.RoleId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
URL Views
Views/URL/Index.cshtml
@model IEnumerable<URL>
<h2>URLs</h2>
<a href='@Url.Action("Create")'>Create New URL</a>
<table>
<thead>
<tr>
<th>URL</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var url in Model)
{
<tr>
<td>@url.Link</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = url.URLId }) |
@Html.ActionLink("Details", "Details", new { id = url.URLId }) |
@Html.ActionLink("Delete", "Delete", new { id = url.URLId })
</td>
</tr>
}
</tbody>
</table>
Views/URL/Details.cshtml
@model URL
<h2>URL Details</h2>
<div>
<h4>@Model.Link</h4>
</div>
<a href='@Url.Action("Edit", new { id = Model.URLId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/URL/Create.cshtml
@model URL
<h2>Create URL</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.Link)
@Html.TextBoxFor(m => m.Link)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/URL/Edit.cshtml
@model URL
<h2>Edit URL</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.URLId)
<div>
@Html.LabelFor(m => m.Link)
@Html.TextBoxFor(m => m.Link)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/URL/Delete.cshtml
@model URL
<h2>Delete URL</h2>
<div>
<h4>Are you sure you want to delete this URL?</h4>
<h4>@Model.Link</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.URLId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
Query Views
Views/Query/Index.cshtml
@model IEnumerable<Query>
<h2 >Queries</h2>
<a href='@Url.Action("Create")'>Create New Query</a>
<table>
<thead>
<tr>
<th>Query Text</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var query in Model)
{
<tr>
<td>@query.Text</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = query.QueryId }) |
@Html.ActionLink("Details", "Details", new { id = query.QueryId }) |
@Html.ActionLink("Delete", "Delete", new { id = query.QueryId })
</td>
</tr>
}
</tbody>
</table>
Views/Query/Details.cshtml
@model Query
<h2>Query Details</h2>
<div>
<h4>@Model.Text</h4>
</div>
<a href='@Url.Action("Edit", new { id = Model.QueryId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/Query/Create.cshtml
@model Query
<h2>Create Query</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.Text)
@Html.TextBoxFor(m => m.Text)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/Query/Edit.cshtml
@model Query
<h2>Edit Query</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.QueryId)
<div>
@Html.LabelFor(m => m.Text)
@Html.TextBoxFor(m => m.Text)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/Query/Delete.cshtml
@model Query
<h2>Delete Query</h2>
<div>
<h4>Are you sure you want to delete this query?</h4>
<h4>@Model.Text</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.QueryId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
SearchResult Views
Views/SearchResult/Index.cshtml
@model IEnumerable<SearchResult>
<h2>Search Results</h2>
<a href='@Url.Action("Create")'>Create New Search Result</a>
<table>
<thead>
<tr>
<th>Result Title</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var result in Model)
{
<tr>
<td>@result.Title</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = result.SearchResultId }) |
@Html.ActionLink("Details", "Details", new { id = result.SearchResultId }) |
@Html.ActionLink("Delete", "Delete", new { id = result.SearchResultId })
</td>
</tr>
}
</tbody>
</table>
Views/SearchResult/Details.cshtml
@model SearchResult
<h2>Search Result Details</h2>
<div>
<h4>@Model.Title</h4>
<p>@Model.Description</p>
</div>
<a href='@Url.Action("Edit", new { id = Model.SearchResultId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/SearchResult/Create.cshtml
@model SearchResult
<h2>Create Search Result</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.Title)
@Html.TextBoxFor(m => m.Title)
</div>
<div>
@Html.LabelFor(m => m.Description)
@Html.TextAreaFor(m => m.Description)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/SearchResult/Edit.cshtml
@model SearchResult
<h2>Edit Search Result</h2>
@using (Html.BeginForm())
{
@Html.Anti ForgeryToken()
@Html.HiddenFor(m => m.SearchResultId)
<div>
@Html.LabelFor(m => m.Title)
@Html.TextBoxFor(m => m.Title)
</div>
<div>
@Html.LabelFor(m => m.Description)
@Html.TextAreaFor(m => m.Description)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/SearchResult/Delete.cshtml
@model SearchResult
<h2>Delete Search Result</h2>
<div>
<h4>Are you sure you want to delete this search result?</h4>
<h4>@Model.Title</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.SearchResultId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
CacheItem Views
Views/CacheItem/Index.cshtml
@model IEnumerable<CacheItem>
<h2>Cache Items</h2>
<a href='@Url.Action("Create")'>Create New Cache Item</a>
<table>
<thead>
<tr>
<th>Cache Key</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.CacheKey</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = item.CacheItemId }) |
@Html.ActionLink("Details", "Details", new { id = item.CacheItemId }) |
@Html.ActionLink("Delete", "Delete", new { id = item.CacheItemId })
</td>
</tr>
}
</tbody>
</table>
Views/CacheItem/Details.cshtml
@model CacheItem
<h2>Cache Item Details</h2>
<div>
<h4>@Model.CacheKey</h4>
<p>Value: @Model.Value</p>
</div>
<a href='@Url.Action("Edit", new { id = Model.CacheItemId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/CacheItem/Create.cshtml
@model CacheItem
<h2>Create Cache Item</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.CacheKey)
@Html.TextBoxFor(m => m.CacheKey)
</div>
<div>
@Html.LabelFor(m => m.Value)
@Html.TextAreaFor(m => m.Value)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/CacheItem/Edit.cshtml
@model CacheItem
<h2>Edit Cache Item</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.CacheItemId)
<div>
@Html.LabelFor(m => m.CacheKey)
@Html.TextBoxFor(m => m.CacheKey)
</div>
<div>
@Html.LabelFor(m => m.Value)
@Html.TextAreaFor(m => m.Value)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/CacheItem/Delete.cshtml
@model CacheItem
<h2>Delete Cache Item</h2>
<div>
<h4>Are you sure you want to delete this cache item?</h4>
<h4>@Model.CacheKey</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.CacheItemId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
AnalyticsData Views
Views/AnalyticsData/Index.cshtml
@model IEnumerable<AnalyticsData>
<h2>Analytics Data</h2>
<a href='@Url.Action("Create")'>Create New Analytics Data</a>
<table>
<thead>
<tr>
<th>Data Point</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var data in Model)
{
<tr>
<td @data.DataPoint</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = data.AnalyticsDataId }) |
@Html.ActionLink("Details", "Details", new { id = data.AnalyticsDataId }) |
@Html.ActionLink("Delete", "Delete", new { id = data.AnalyticsDataId })
</td>
</tr>
}
</tbody>
</table>
Views/AnalyticsData/Details.cshtml
@model AnalyticsData
<h2>Analytics Data Details</h2>
<div>
<h4>@Model.DataPoint</h4>
<p>Value: @Model.Value</p>
</div>
<a href='@Url.Action("Edit", new { id = Model.AnalyticsDataId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/AnalyticsData/Create.cshtml
@model AnalyticsData
<h2>Create Analytics Data</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.DataPoint)
@Html.TextBoxFor(m => m.DataPoint)
</div>
<div>
@Html.LabelFor(m => m.Value)
@Html.TextBoxFor(m => m.Value)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/AnalyticsData/Edit.cshtml
@model AnalyticsData
<h2>Edit Analytics Data</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.AnalyticsDataId)
<div>
@Html.LabelFor(m => m.DataPoint)
@Html.TextBoxFor(m => m.DataPoint)
</div>
<div>
@Html.LabelFor(m => m.Value)
@Html.TextBoxFor(m => m.Value)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/AnalyticsData/Delete.cshtml
@model AnalyticsData
<h2>Delete Analytics Data</h2>
<div>
<h4>Are you sure you want to delete this data point?</h4>
<h4>@Model.DataPoint</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.AnalyticsDataId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
Feedback Views
Views/Feedback/Index.cshtml
@model IEnumerable<Feedback>
<h2>Feedback</h2>
<a href='@Url.Action("Create")'>Create New Feedback</a>
<table>
<thead>
<tr>
<th>Feedback Text</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var feedback in Model)
{
<tr>
<td>@feedback.Text</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = feedback.FeedbackId }) |
@Html.ActionLink("Details", "Details", new { id = feedback.FeedbackId }) |
@Html.ActionLink("Delete", "Delete", new { id = feedback.FeedbackId })
</td>
</tr>
}
</tbody>
</table>
Views/Feedback/Details.cshtml
@model Feedback
<h2>Feedback Details</h2>
<div>
<h4>@Model.Text</h4>
</div>
<a href='@Url.Action("Edit", new { id = Model.FeedbackId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/Feedback/Create.cshtml
@model Feedback
<h2>Create Feedback</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.Text)
@Html.TextAreaFor(m => m.Text)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/Feedback/Edit.cshtml
@model Feedback
<h2>Edit Feedback</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.FeedbackId)
<div>
@Html .LabelFor(m => m.Text)
@Html.TextAreaFor(m => m.Text)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/Feedback/Delete.cshtml
@model Feedback
<h2>Delete Feedback</h2>
<div>
<h4>Are you sure you want to delete this feedback?</h4>
<h4>@Model.Text</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.FeedbackId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
SecuritySetting Views
Views/SecuritySetting/Index.cshtml
@model IEnumerable<SecuritySetting>
<h2>Security Settings</h2>
<a href='@Url.Action("Create")'>Create New Security Setting</a>
<table>
<thead>
<tr>
<th>Setting Name</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var setting in Model)
{
<tr>
<td>@setting.SettingName</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id = setting.SecuritySettingId }) |
@Html.ActionLink("Details", "Details", new { id = setting.SecuritySettingId }) |
@Html.ActionLink("Delete", "Delete", new { id = setting.SecuritySettingId })
</td>
</tr>
}
</tbody>
</table>
Views/SecuritySetting/Details.cshtml
@model SecuritySetting
<h2>Security Setting Details</h2>
<div>
<h4>@Model.SettingName</h4>
<p>Value: @Model.Value</p>
</div>
<a href='@Url.Action("Edit", new { id = Model.SecuritySettingId })'>Edit</a> |
<a href='@Url.Action("Index")">Back to List</a>
Views/SecuritySetting/Create.cshtml
@model SecuritySetting
<h2>Create Security Setting</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
@Html.LabelFor(m => m.SettingName)
@Html.TextBoxFor(m => m.SettingName)
</div>
<div>
@Html.LabelFor(m => m.Value)
@Html.TextBoxFor(m => m.Value)
</div>
<input type="submit" value="Create" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/SecuritySetting/Edit.cshtml
@model SecuritySetting
<h2>Edit Security Setting</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.SecuritySettingId)
<div>
@Html.LabelFor(m => m.SettingName)
@Html.TextBoxFor(m => m.SettingName)
</div>
<div>
@Html.LabelFor(m => m.Value)
@Html.TextBoxFor(m => m.Value)
</div>
<input type="submit" value="Save" />
}
<a href='@Url.Action("Index")">Back to List</a>
Views/SecuritySetting/Delete.cshtml
@model SecuritySetting
<h2>Delete Security Setting</h2>
<div>
<h4>Are you sure you want to delete this security setting?</h4>
<h4>@Model.SettingName</h4>
</div>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.SecuritySettingId)
<input type="submit" value="Delete" />
}
<a href='@Url.Action("Index")'>Cancel</a>
This completes the view implementations for all the controllers defined in your application
Each view provides a user interface for performing CRUD operations corresponding to the actions in the controllers.
Creating a dashboard page in an ASP.NET MVC application
Involves creating a new view that consolidates data from various repositories and displays it in a user-friendly format. Below, I will outline the steps to create a dashboard page and provide an example of how to implement it.
Step 1: Create a Dashboard ViewModel
First, we need to create a ViewModel that will hold the consolidated data for the dashboard.
Models/DashboardViewModel.cs
public class DashboardViewModel
{
public int TotalUsers { get; set; }
public int TotalRoles { get; set; }
public int TotalURLs { get; set; }
public int TotalQueries { get; set; }
public int TotalSearchResults { get; set; }
public int TotalFeedbacks { get; set; }
public int TotalCacheItems { get; set; }
public int TotalAnalyticsData { get; set; }
public int TotalSecuritySettings { get; set; }
}
Step 2: Create the Dashboard Controller
Next, we will create a new controller that will handle requests for the dashboard.
Controllers/DashboardController.cs
using System.Web.Mvc;
public class DashboardController : Controller
{
private readonly IUserRepository _userRepository;
private readonly IRoleRepository _roleRepository;
private readonly IURLRepository _urlRepository;
private readonly IQueryRepository _queryRepository;
private readonly ISearchResultRepository _searchResultRepository;
private readonly IFeedbackRepository _feedbackRepository;
private readonly ICacheItemRepository _cacheItemRepository;
private readonly IAnalyticsDataRepository _analyticsDataRepository;
private readonly ISecuritySettingRepository _securitySettingRepository;
public DashboardController(IUser Repository userRepository, IRoleRepository roleRepository, IURLRepository urlRepository,
IQueryRepository queryRepository, ISearchResultRepository searchResultRepository,
IFeedbackRepository feedbackRepository, ICacheItemRepository cacheItemRepository,
IAnalyticsDataRepository analyticsDataRepository, ISecuritySettingRepository securitySettingRepository)
{
_userRepository = userRepository;
_roleRepository = roleRepository;
_urlRepository = urlRepository;
_queryRepository = queryRepository;
_searchResultRepository = searchResultRepository;
_feedbackRepository = feedbackRepository;
_cacheItemRepository = cacheItemRepository;
_analyticsDataRepository = analyticsDataRepository;
_securitySettingRepository = securitySettingRepository;
}
public ActionResult Index()
{
var model = new DashboardViewModel
{
TotalUsers = _userRepository.GetAllUsers().Count(),
TotalRoles = _roleRepository.GetAllRoles().Count(),
TotalURLs = _urlRepository.GetAllURLs().Count(),
TotalQueries = _queryRepository.GetAllQueries().Count(),
TotalSearchResults = _searchResultRepository.GetAllSearchResults().Count(),
TotalFeedbacks = _feedbackRepository.GetAllFeedbacks().Count(),
TotalCacheItems = _cacheItemRepository.GetAllCacheItems().Count(),
TotalAnalyticsData = _analyticsDataRepository.GetAllAnalyticsData().Count(),
TotalSecuritySettings = _securitySettingRepository.GetAllSecuritySettings().Count()
};
return View(model);
}
}
Step 3: Create the Dashboard View
Now, we will create the view that will display the dashboard data.
Views/Dashboard/Index.cshtml
@model DashboardViewModel
<h2>Dashboard</h2>
<div class="dashboard">
<div class="dashboard-item">
<h3>Total Users</h3>
<p>@Model.TotalUsers</p>
</div>
<div class="dashboard-item">
<h3>Total Roles</h3>
<p>@Model.TotalRoles</p>
</div>
<div class="dashboard-item">
<h3>Total URLs</h3>
<p>@Model.TotalURLs</p>
</div>
<div class="dashboard-item">
<h3>Total Queries</h3>
<p>@Model.TotalQueries</p>
</div>
<div class="dashboard-item">
<h3>Total Search Results</h3>
<p>@Model.TotalSearchResults</p>
</div>
<div class="dashboard-item">
<h3>Total Feedbacks</h3>
<p>@Model.TotalFeedbacks</p>
</div>
<div class="dashboard-item">
<h3>Total Cache Items</h3>
<p>@Model.TotalCacheItems</p>
</div>
<div class="dashboard-item">
<h3>Total Analytics Data</h3>
<p>@Model.TotalAnalyticsData</p>
</div>
<div class="dashboard-item">
<h3>Total Security Settings</h3>
<p>@Model.TotalSecuritySettings</p>
</div>
</div>
<style>
.dashboard {
display: flex;
flex-wrap: wrap;
gap: 20px;
}
.dashboard-item {
border: 1px solid #ccc;
padding: 20px;
border-radius: 5px;
width: 200px;
text-align: center;
}
.dashboard-item h3 {
margin: 0;
font-size: 1.5em;
}
.dashboard-item p {
font-size: 2em;
color: #333;
}
</style>
Step 4: Update the Route Configuration
Ensure that the route configuration allows access to the dashboard. You may need to add a route in your RouteConfig.cs file.
routes.MapRoute(
name: "Dashboard",
url: "Dashboard",
defaults: new { controller = "Dashboard", action = "Index" }
);
Step 5: Add Navigation to the Dashboard
You can add a link to the dashboard in your main layout or navigation menu.
<li>@Html.ActionLink("Dashboard", "Index", "Dashboard")</li>
This completes the implementation of the dashboard page
Which consolidates and displays various data related to the project. Users can now access the dashboard to view key metrics at a glance.