/// <summary>
/// return a datatable of the bug records
/// </summary>
/// <param name="BugID">bugid for the data</param>
/// <returns></returns>
public static DataTable GetBugLogs(int BugID)
{
DataTable table = new DataTable("BugLog"); //Buglog table
SqlDataAdapter dataAdapter = null;
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"ListAllBugLogsSelectedFields";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("BugID", SqlDbType.Int));
command.Parameters["BugID"].Value = BugID;
using (dataAdapter = new SqlDataAdapter(command))
{
int result = dataAdapter.Fill(table);
}
}
}
return(table);
}
/// <summary>
/// Validate that user exists, is admin
/// </summary>
/// <param name="UserName">name to search</param>
/// <returns></returns>
public static User ValidateUserName(string UserName)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"ValidateUser";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@UserName", SqlDbType.Text);
command.Parameters["@UserName"].Value = UserName;
SqlDataReader reader = command.ExecuteReader();
User user = new User();
if (reader.HasRows)
{
while (reader.Read())
{
user.Load(reader);
}
}
else
{
user = null;
}
return(user);
}
}
}
/// <summary>
/// Delete app record
/// </summary>
/// <param name="AppId"></param>
/// <param name="StatusMsg"></param>
/// <returns></returns>
public static bool DeleteApp(int AppId, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"DeleteApplication";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("AppId", System.Data.SqlDbType.VarChar);
parameter1.Value = AppId;
command.Parameters.Add(parameter1);
command.ExecuteNonQuery();
StatusMsg = "Successfully Deleted App";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Update Existing bug
/// </summary>
/// <param name="BugID">BugID</param>
/// <param name="BugSignOff">BugSignOff</param>
/// <param name="BugDate">BugDate</param>
/// <param name="BugDesc">BugDesc</param>
/// <param name="BugDetails">BugDetails</param>
/// <param name="RepSteps">RepSteps</param>
/// <param name="FixDate">FixDate</param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public bool UpdateBug(int BugID, int?BugSignOff, DateTime BugDate,
string BugDesc, string BugDetails, string RepSteps, DateTime?FixDate, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"UpdateBug";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("BugId", System.Data.SqlDbType.VarChar);
parameter1.Value = BugID;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("BugSignOff", System.Data.SqlDbType.Int);
parameter2.Value = ((object)BugSignOff) ?? DBNull.Value;
command.Parameters.Add(parameter2);
SqlParameter parameter3 = new SqlParameter("BugDate", System.Data.SqlDbType.DateTime);
parameter3.Value = BugDate;
command.Parameters.Add(parameter3);
SqlParameter parameter4 = new SqlParameter("BugDesc", System.Data.SqlDbType.VarChar);
parameter4.Value = BugDesc;
command.Parameters.Add(parameter4);
SqlParameter parameter5 = new SqlParameter("BugDetails", System.Data.SqlDbType.VarChar);
parameter5.Value = BugDetails;
command.Parameters.Add(parameter5);
SqlParameter parameter6 = new SqlParameter("RepSteps", System.Data.SqlDbType.VarChar);
parameter6.Value = RepSteps;
command.Parameters.Add(parameter6);
//null values might throw error?
SqlParameter parameter7 = new SqlParameter("FixDate", System.Data.SqlDbType.DateTime);
parameter7.Value = ((object)FixDate) ?? DBNull.Value;
command.Parameters.Add(parameter7);
command.ExecuteNonQuery();
StatusMsg = "Successfully updated record";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Add a new bug log record
/// </summary>
/// <param name="StatusCodeID">StatusCodeID</param>
/// <param name="UserID">UserID</param>
/// <param name="BugLogDesc">BugLogDesc</param>
/// <param name="BugID">BugID</param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public static bool AddNewBugLog(int StatusCodeID, int UserID, String BugLogDesc, int BugID, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"AddBugLog";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("StatusCodeID", System.Data.SqlDbType.Int);
parameter1.Value = StatusCodeID;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("UserID", System.Data.SqlDbType.Int);
parameter2.Value = UserID;
command.Parameters.Add(parameter2);
//used for concurrency check (manual)
SqlParameter parameter3 = new SqlParameter("BugLogDesc", System.Data.SqlDbType.VarChar, 100);
parameter3.Value = BugLogDesc;
command.Parameters.Add(parameter3);
SqlParameter parameter4 = new SqlParameter("BugID", System.Data.SqlDbType.Int);
parameter4.Value = BugID;
command.Parameters.Add(parameter4);
command.ExecuteNonQuery();
StatusMsg = "New bug successfully added";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// update existing app
/// </summary>
/// <param name="AppId">AppId</param>
/// <param name="AppName">AppName</param>
/// <param name="AppVers">AppVers</param>
/// <param name="AppDesc">AppDesc</param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public bool UpdateApp(int AppId, string AppName, string AppVers, string AppDesc, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"UpdateApplication";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("AppId", System.Data.SqlDbType.VarChar);
parameter1.Value = AppId;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("AppName", System.Data.SqlDbType.VarChar);
parameter2.Value = AppName;
command.Parameters.Add(parameter2);
SqlParameter parameter3 = new SqlParameter("AppVersion", System.Data.SqlDbType.VarChar);
parameter3.Value = AppVers;
command.Parameters.Add(parameter3);
SqlParameter parameter4 = new SqlParameter("AppDesc", System.Data.SqlDbType.VarChar);
parameter4.Value = AppDesc;
command.Parameters.Add(parameter4);
command.ExecuteNonQuery();
StatusMsg = "App Successfully Updated";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Update existing user record
/// </summary>
/// <param name="UserID"></param>
/// <param name="UserName"></param>
/// <param name="UserEmail"></param>
/// <param name="UserTel"></param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public static bool UpdateUser(int UserID, string UserName, String UserEmail, String UserTel, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"UpdateUser";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("UserID", System.Data.SqlDbType.Int);
parameter1.Value = UserID;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("UserName", System.Data.SqlDbType.VarChar);
parameter2.Value = UserName;
command.Parameters.Add(parameter2);
SqlParameter parameter3 = new SqlParameter("UserEmail", System.Data.SqlDbType.VarChar);
parameter3.Value = UserEmail;
command.Parameters.Add(parameter3);
SqlParameter parameter4 = new SqlParameter("UserTel", System.Data.SqlDbType.VarChar);
parameter4.Value = UserTel;
command.Parameters.Add(parameter4);
command.ExecuteNonQuery();
StatusMsg = "User Successfully Updated";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Add a new Application to Database
/// </summary>
/// <param name="AppName"></param>
/// <param name="AppVersion"></param>
/// <param name="AppDesc"></param>
public static bool AddNewApp(string AppName, String AppVersion, String AppDesc, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"AddApplication";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("AppName", System.Data.SqlDbType.VarChar);
parameter1.Value = AppName;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("AppVersion", System.Data.SqlDbType.VarChar, 100);
parameter2.Value = AppVersion;
command.Parameters.Add(parameter2);
//used for concurrency check (manual)
SqlParameter parameter3 = new SqlParameter("AppDesc", System.Data.SqlDbType.VarChar, 100);
parameter3.Value = AppDesc;
command.Parameters.Add(parameter3);
command.ExecuteNonQuery();
StatusMsg = "New App Successfully added";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Add a new user to datbase
/// </summary>
/// <param name="UserName"></param>
/// <param name="UserEmail"></param>
/// <param name="UserTel"></param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public static bool AddNewUser(string UserName, String UserEmail, String UserTel, out string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"AddUser";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("UserName", System.Data.SqlDbType.VarChar);
parameter1.Value = UserName;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("UserEmail", System.Data.SqlDbType.VarChar, 100);
parameter2.Value = UserEmail;
command.Parameters.Add(parameter2);
//used for concurrency check (manual)
SqlParameter parameter3 = new SqlParameter("UserTel", System.Data.SqlDbType.VarChar, 12);
parameter3.Value = UserTel;
command.Parameters.Add(parameter3);
command.ExecuteNonQuery();
StatusMsg = "Successfully Added New User"; //get SQL Server Message
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Get a List of BugLogs for a Bug
/// </summary>
/// <param name="BugID">BugID number</param>
/// <returns></returns>
public List <BugLog> GetList(int BugID)
{
List <BugLog> BugLogList = new List <BugLog>();
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"ListAllBugLogs";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
BugLog BugLog = new BugLog();
BugLog.Load(reader);
BugLogList.Add(BugLog);
}
}
}
return(BugLogList);
}
/// <summary>
/// Get a selected app by Id
/// </summary>
/// <param name="AppId">AppId</param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public static Application GetAppByID(int AppId, string StatusMsg)
{
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"GetApplicationById";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameter1 = new SqlParameter("AppId", System.Data.SqlDbType.Int);
parameter1.Value = AppId;
command.Parameters.Add(parameter1);
SqlDataReader reader = command.ExecuteReader();
Application e = new Application();
while (reader.Read())
{
e.Load(reader);
}
return(e);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(null);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(null);
}
}
}
}
/// <summary>
/// Get list of applications
/// </summary>
/// <returns></returns>
public List <Application> GetList()
{
List <Application> applications = new List <Application>();
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"GetAllApplications";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Application e = new Application();
e.Load(reader);
applications.Add(e);
}
}
}
return(applications);
}
public static List <StatusCode> GetList()
{
List <StatusCode> statuscodes = new List <StatusCode>();
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"GetAllStatusCodes";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
StatusCode e = new StatusCode();
e.Load(reader);
statuscodes.Add(e);
}
}
}
return(statuscodes);
}
/// <summary>
/// Get all users in a list
/// </summary>
/// <returns></returns>
public List <User> GetUsers()
{
List <User> UsersList = new List <User>();
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"ListAllUsers";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User User = new User();
User.Load(reader);
UsersList.Add(User);
}
}
}
return(UsersList);
}
/// <summary>
/// Add a new bug record
/// </summary>
/// <param name="AppID">UserID</param>
/// <param name="UserID">UserID</param>
/// <param name="BugDesc">BugDesc</param>
/// <param name="BugDetails">BugDetails</param>
/// <param name="RepSteps">RepSteps</param>
/// <param name="StatusMsg">message that will return from database</param>
/// <returns></returns>
public static bool AddNewBug(int AppID, int UserID, string BugDesc,
String BugDetails, String RepSteps, out string StatusMsg)
{
// Assume all new bugs will be set to unassigned
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = @"AddBug";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@AppID", System.Data.SqlDbType.Int).Value = AppID;
command.Parameters.Add("@UserID", System.Data.SqlDbType.Int).Value = UserID;
command.Parameters.Add("@BugDesc", System.Data.SqlDbType.VarChar, 255).Value = BugDesc;
command.Parameters.Add("@BugDetails", System.Data.SqlDbType.VarChar, 1000).Value = BugDetails;
command.Parameters.Add("@RepSteps", System.Data.SqlDbType.VarChar, 1000).Value = RepSteps;
command.ExecuteNonQuery();
StatusMsg = "Successfully added bug";
return(true);
}
catch (SqlException ex)
{
StatusMsg = DB.DisplaySqlErrors(ex);
return(false);
}
catch (Exception e)
{
StatusMsg = e.Message;
return(false);
}
}
}
}
/// <summary>
/// Get List of all Bugs
/// </summary>
/// <returns></returns>
public static List <Bug> GetList()
{
bugsList = new List <Bug>();
using (SqlConnection connection = DB.GetSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"GetAllBugs";
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Bug e = new Bug();
e.Load(reader);
bugsList.Add(e);
}
}
}
return(bugsList);
}