public User loginDA(User user)
{
PasswordProtection pp = new PasswordProtection();
var hashedPassowrd = pp.GenerateSHA256(user.password);
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
queryParameters["userName"] = new SqlParameter("userName", user.userName);
queryParameters["password"] = new SqlParameter("password", hashedPassowrd);
try
{
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[Login]", queryParameters);
while (reader.Read() == true)
{
if (reader.GetInt32(reader.GetOrdinal("id")) > 0)
{
user.id = reader.GetInt32(reader.GetOrdinal("id"));
user.userName = reader.GetString(reader.GetOrdinal("userName"));
user.role = reader.GetInt32(reader.GetOrdinal("role"));
}
else
{
user.userName = null;
user.role = 0;
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
return(user);
}
public string AddUsersToTeamDA(Team_User team_User)
{
string msg = "";
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
if (team_User.teamId > 0)
{
cmdParameters["teamId"] = new SqlParameter("teamId", team_User.teamId);
}
else
{
int teamId = GetSavedTeamIDDA();
cmdParameters["teamId"] = new SqlParameter("teamId", teamId);
}
cmdParameters["userId"] = new SqlParameter("userId", team_User.userId);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[AddUserstoTeam]", cmdParameters);
if (rc == -1)
{
msg = "add user to team successfully...!";
}
else
{
msg = "OOPs, something went wrong...!";
}
return(msg);
}
public int DeleteTaskDA(int id)
{
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["id"] = new SqlParameter("id", id);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[DeleteTask]", cmdParameters);
return(rc);
}
public int UpdateStatusDA(Tasks task)
{
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["id"] = new SqlParameter("id", task.id);
cmdParameters["status"] = new SqlParameter("status", task.status);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[UpdateTask]", cmdParameters);
return(rc);
}
public int CreateTaskDA(Tasks task)
{
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["taskDetails"] = new SqlParameter("taskDetails", task.taskDetails);
cmdParameters["taskAssignedDate"] = new SqlParameter("taskAssignedDate", task.taskAssignedDate);
cmdParameters["taskEndDate"] = new SqlParameter("taskEndDate", task.taskEndDate);
cmdParameters["responsibleUserId"] = new SqlParameter("responsibleUserId", task.responsibleUserId);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[InsertTask]", cmdParameters);
return(rc);
}
public int GetSavedTeamIDDA()
{
var savedTeamId = 0;
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[GetSavedTeamID]", queryParameters);
while (reader.Read() == true)
{
savedTeamId = reader.GetInt32(reader.GetOrdinal("maxId"));
}
return(savedTeamId);
}
public List <Tasks> ViewTaskDA(int id)
{
List <Tasks> taskList = new List <Tasks>();
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
if (id == 0)
{
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[ViewTask]", queryParameters);
while (reader.Read() == true)
{
taskList.Add(new Tasks()
{
id = reader.GetInt32(reader.GetOrdinal("id")),
taskDetails = reader.GetString(reader.GetOrdinal("taskDetails")),
taskAssignedDate = reader.GetString(reader.GetOrdinal("taskAssignedDate")),
taskEndDate = reader.GetString(reader.GetOrdinal("taskEndDate")),
responsibleUserId = reader.GetInt32(reader.GetOrdinal("responsibleUserId")),
responsibleUser = reader.GetString(reader.GetOrdinal("responsibleUser")),
teamName = reader.GetString(reader.GetOrdinal("teamName")),
status = reader.GetInt32(reader.GetOrdinal("status"))
});
}
}
else
{
queryParameters["id"] = new SqlParameter("id", id);
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[ViewTask]", queryParameters);
while (reader.Read() == true)
{
taskList.Add(new Tasks()
{
id = reader.GetInt32(reader.GetOrdinal("id")),
taskDetails = reader.GetString(reader.GetOrdinal("taskDetails")),
taskAssignedDate = reader.GetString(reader.GetOrdinal("taskAssignedDate")),
taskEndDate = reader.GetString(reader.GetOrdinal("taskEndDate")),
responsibleUserId = reader.GetInt32(reader.GetOrdinal("responsibleUserId")),
responsibleUser = reader.GetString(reader.GetOrdinal("responsibleUser")),
teamName = reader.GetString(reader.GetOrdinal("teamName")),
status = reader.GetInt32(reader.GetOrdinal("status"))
});
}
}
return(taskList);
}
public List <UserView> ViewUserDA()
{
List <UserView> userList = new List <UserView>();
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[GetUserList]", queryParameters);
while (reader.Read() == true)
{
userList.Add(new UserView()
{
id = reader.GetInt32(reader.GetOrdinal("id")),
userName = reader.GetString(reader.GetOrdinal("userName")),
role = reader.GetInt32(reader.GetOrdinal("role"))
});
}
return(userList);
}
public List <Team> ViewTeamListDA()
{
List <Team> teamList = new List <Team>();
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[TeamList]", queryParameters);
while (reader.Read() == true)
{
teamList.Add(new Team()
{
teamId = reader.GetInt32(reader.GetOrdinal("teamId")),
teamName = reader.GetString(reader.GetOrdinal("teamName")),
teamLead = reader.GetString(reader.GetOrdinal("teamLead"))
});
}
return(teamList);
}
public List <UsersToBeTeam> ViewTeamMembersDA(int teamId)
{
List <UsersToBeTeam> userList = new List <UsersToBeTeam>();
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
queryParameters["teamId"] = new SqlParameter("teamId", teamId);
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[GetTeamMembersList]", queryParameters);
while (reader.Read() == true)
{
userList.Add(new UsersToBeTeam()
{
id = reader.GetInt32(reader.GetOrdinal("id")),
userName = reader.GetString(reader.GetOrdinal("userName"))
});
}
return(userList);
}
public string CreateTeamDA(Team team)
{
string msg = "";
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["teamName"] = new SqlParameter("teamName", team.teamName);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[CreateTeam]", cmdParameters);
if (rc == -1)
{
msg = "team create successfully...!";
}
else
{
msg = "OOPs, something went wrong...!";
}
return(msg);
}
public string DeleteUserDA(User user)
{
string msg = "";
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["id"] = new SqlParameter("id", user.id);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[DeleteUser]", cmdParameters);
if (rc == -1)
{
msg = "user delete successfully...!";
}
else
{
msg = "OOPs, something went wrong...!";
}
return(msg);
}
public string UpdatePasswordDA(User user)
{
string msg = "";
PasswordProtection pp = new PasswordProtection();
var hashedPassowrd = pp.GenerateSHA256(user.password);
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["id"] = new SqlParameter("id", user.id);
cmdParameters["password"] = new SqlParameter("password", hashedPassowrd);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[UpdateUsers]", cmdParameters);
if (rc == -1)
{
msg = "Password updated successfully...!";
}
else
{
msg = "OOPs, something went wrong...!";
}
return(msg);
}
public int GetTeamIdDA(int userId)
{
int teamId = 0;
Dictionary <string, SqlParameter> queryParameters = new Dictionary <string, SqlParameter>();
queryParameters["userId"] = new SqlParameter("userId", userId);
try
{
SqlDataReader reader = SqlDatabaseUtility.ExecuteQuery("[dbo].[GetTeamID]", queryParameters);
while (reader.Read() == true)
{
teamId = reader.GetInt32(reader.GetOrdinal("teamId"));
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
return(teamId);
}
public string CreateUsersDA(User user)
{
string msg = "";
PasswordProtection pp = new PasswordProtection();
var hashedPassowrd = pp.GenerateSHA256(user.password);
Dictionary <string, SqlParameter> cmdParameters = new Dictionary <string, SqlParameter>();
cmdParameters["userName"] = new SqlParameter("userName", user.userName);
cmdParameters["password"] = new SqlParameter("password", hashedPassowrd);
cmdParameters["role"] = new SqlParameter("role", user.role);
int rc = SqlDatabaseUtility.ExecuteCommand("[dbo].[InsertUser]", cmdParameters);
if (rc == -1)
{
msg = "user create successfully...!";
}
else
{
msg = "OOPs, something went wrong...!";
}
return(msg);
}
