/// <summary> /// Deletes the user based on username. /// </summary> /// <param name="userName"></param> /// <returns></returns> public bool DeleteUser(string userName) { bool result = true; SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; try { cmd.CommandText = "removeUser"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@userName", userName); connection.Open(); cmd.ExecuteNonQuery(); } catch (SqlException e) { DbLogging.Log(e); result = false; } finally { connection.Close(); cmd.Parameters.Clear(); } return(result); }
/// <summary> /// Adds a company to the database. /// </summary> /// <param name="name"> The company name </param> /// <param name="street">The company's street</param> /// <param name="postalCode">The company postal code </param> /// <param name="city">The company city </param> /// <param name="country">The country </param> /// <param name="phone">The phone number </param> /// <param name="fax">The fax number </param> /// <param name="year">The year</param> /// <returns>A boolean </returns> public bool AddCompany(string name, string street, string postalCode, string city, string country, string phone, string fax, string year) { bool result = true; int yearParsed = 0; bool yearParsedResult = false; if (year.Length <= 4) { yearParsedResult = int.TryParse(year, out yearParsed); } SqlCommand cmd = new SqlCommand(); try { cmd.CommandText = "addCompany"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = connection; cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@street", street); cmd.Parameters.AddWithValue("@postalcode", postalCode); cmd.Parameters.AddWithValue("@city", city); cmd.Parameters.AddWithValue("@country", country); cmd.Parameters.AddWithValue("@phone", phone); cmd.Parameters.AddWithValue("@fax", fax); cmd.Parameters.AddWithValue("@year", yearParsed); cmd.Connection.Open(); cmd.ExecuteNonQuery(); } catch (SqlException e) { result = false; DbLogging.Log(e); } finally { connection.Close(); cmd.Parameters.Clear(); } return(result); }
/// <summary> /// Auto creates and returns the desired table by /// using parameters present in the command /// object. This method implements support for /// stored procedures. /// </summary> /// <param name="command">The command object holding desired command information.</param> /// <param name="table">The table to return data into.</param> /// <exception cref="SqlException"></exception> /// <returns>int -> The return value of store procedure</returns> private int FillTable(SqlCommand command, DataTable table) { SqlDataReader reader = null; //Used for reading from database SqlParameter procReturn = null; //Holds return value from a stored procedure int retVal = 0; //Less than 0 if problem occurs try { /* Get return value if running a stored procedure */ if (command.CommandType == CommandType.StoredProcedure) { procReturn = command.Parameters.Add("return_value", SqlDbType.Int); procReturn.Direction = ParameterDirection.ReturnValue; } /* Open connection and execute reader */ command.Connection.Open(); reader = command.ExecuteReader(); /* Load data into table */ table.Load(reader); /* Set retVal to what was returned from stored procedure (if applicable) */ if (command.CommandType == CommandType.StoredProcedure) { retVal = (int)procReturn.Value; } } catch (SqlException e) { DbLogging.Log(e); /* Rethrow */ throw; } finally { /* Close connection and reader if open */ if (reader != null) { reader.Close(); } command.Connection.Close(); } return(retVal); }
/// <summary> /// Gets the reports. /// </summary> /// <param name="Table"></param> /// <param name="funcName"></param> /// <param name="companyName"></param> /// <param name="employeeType"></param> /// <param name="Week"></param> /// <returns></returns> public bool GetReports(DataTable Table, string funcName, string companyName, string employeeType = "", string Week = "") { bool result = true; // Create the command and set the connection. SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; //Used for reading from database cmd.Connection = connection; try { cmd.CommandText = funcName; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = companyName; if (employeeType != "") { cmd.Parameters.Add("@employeeType", SqlDbType.NVarChar).Value = employeeType; } if (Week != "") // Not all them need a week so don't add parametr if dont need to { cmd.Parameters.Add("@StartDay", SqlDbType.Date).Value = Convert.ToDateTime(Week); } cmd.Connection.Open(); reader = cmd.ExecuteReader(); /* Load data into table */ Table.Load(reader); } catch (SqlException ex) { DbLogging.Log(ex); } finally { connection.Close(); cmd.Parameters.Clear(); } return(result); }
/// <summary> /// Gets a list of all company names stored /// in the database. /// </summary> /// <exception cref="SqlException"></exception> /// <returns>DataTable -> List of all company names</returns> public DataTable GetCompanyNames() { DataTable companyNames = new DataTable(); try { /* Set to get all company names from database */ string cmdText = "SELECT name FROM Companies ORDER BY name ASC"; SqlCommand cmd = new SqlCommand(cmdText, connection); /* Get the company names */ FillTable(cmd, companyNames); } catch (SqlException e) { DbLogging.Log(e); throw; } return(companyNames); }
/// <summary> /// Gets the date of incorporation. /// </summary> /// <param name="companyName"></param> /// <returns></returns> public string GetDateOfIncorporation(string companyName) { SqlDataReader reader = null; string incorporationYear = ""; string cmdStr = "SELECT DISTINCT yearOfIncorporation FROM companies WHERE name=@companyName"; try { SqlCommand command = new SqlCommand(cmdStr, connection); command.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = companyName; /* Open connection and get data */ connection.Open(); reader = command.ExecuteReader(); /* Get the year of incorporation from reader */ if (reader.HasRows) { if (reader.Read()) { incorporationYear = reader["yearOfIncorporation"].ToString(); } } } catch (SqlException ex) { DbLogging.Log(ex); throw; } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } connection.Close(); } return(incorporationYear); }
/// <summary> /// Addds a user to the database /// </summary> /// <param name="userName">The username</param> /// <param name="firstName">The firstname </param> /// <param name="lastName">The lastname</param> /// <param name="securityClearence"> Security Clearence </param> /// <param name="password">The password</param> /// <returns></returns> public bool AddUser(string userName, string firstName, string lastName, int securityClearence, string password) { bool result = true; // Create the command and set the connection. SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; try { cmd.CommandText = "addUser"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@userName", SqlDbType.NVarChar).Value = userName; cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = firstName; cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = lastName; cmd.Parameters.Add("@securityId", SqlDbType.Int).Value = securityClearence; cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password; connection.Open(); cmd.ExecuteNonQuery(); } catch (SqlException e) { result = false; DbLogging.Log(e); } finally { connection.Close(); cmd.Parameters.Clear(); } return(result); }
/// <summary> /// Adds a time card /// </summary> /// <param name="EmployeeID"> The employee id </param> /// <param name="TCDate"> The time card date </param> /// <param name="Hours"> The hours 1</param> /// <returns></returns> public bool AddTimecard(int EmployeeID, DateTime TCDate, decimal Hours) { bool result = true; // Create the command and set the connection. SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; try { cmd.CommandText = "addTimeCard"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@employeeID", SqlDbType.BigInt).Value = EmployeeID; cmd.Parameters.Add("@date", SqlDbType.Date).Value = TCDate; cmd.Parameters.Add("@hours", SqlDbType.Decimal).Value = Hours; connection.Open(); cmd.ExecuteNonQuery(); } catch (SqlException e) { DbLogging.Log(e); } finally { connection.Close(); cmd.Parameters.Clear(); } return(result); }
/// <summary> /// Inserts an employee of either seasonal, fulltime, part /// </summary> /// <param name="firstName">Employee first name</param> /// <param name="lastName">Employee last name</param> /// <param name="sin">Employee sin or business number</param> /// <param name="pay">Pay amount of the employee</param> /// <param name="companyName">Name of the company employee works for</param> /// <param name="dateOfBirth">Employee date of birth</param> /// <param name="dateOfHire">Employee date of hire</param> /// <param name="dateOfTermination">Employee date of termination</param> /// <param name="reasonForLeaving">Employee reason for leaving</param> /// <param name="employeeType">Type of employee</param> /// <param name="season">The season the employee is working (if seasonal)</param> /// <param name="seasonYear">The year the employee is working (if seasonal)</param> /// <param name="Clearance">If admin or general user is inserting</param> public void CreateEmployee(string firstName, string lastName, string sin, string pay, string companyName, string dateOfBirth, string dateOfHire, string dateOfTermination, string reasonForLeaving, string employeeType, string season, string seasonYear, string Clearance) { SqlCommand cmd = new SqlCommand("", connection); cmd.CommandType = CommandType.StoredProcedure; try { /* Remove spaces from sin */ sin = Regex.Replace(sin, "[\\s]", ""); /* Add all common parameters */ cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = firstName; cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = lastName; cmd.Parameters.Add("@sin", SqlDbType.NChar).Value = sin; cmd.Parameters.Add("@employedWithCompany", SqlDbType.Bit).Value = true; cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = companyName; cmd.Parameters.Add("@dateOfBirth", SqlDbType.Date).Value = dateOfBirth; /* Add specific parameters */ if (employeeType != "seasonal") { if (!string.IsNullOrEmpty(dateOfTermination) && Clearance == "1") { cmd.Parameters.Add("@reasonForLeaving", SqlDbType.NVarChar).Value = reasonForLeaving; cmd.Parameters.Add("@dateOfTermination", SqlDbType.Date).Value = dateOfTermination; } else { cmd.Parameters.Add("@reasonForLeaving", SqlDbType.NVarChar).Value = DBNull.Value; cmd.Parameters.Add("@dateOfTermination", SqlDbType.Date).Value = DBNull.Value; } cmd.Parameters.Add("@dateOfHire", SqlDbType.Date).Value = dateOfHire; } else { cmd.Parameters.Add("@season", SqlDbType.NVarChar).Value = season; cmd.Parameters.Add("@seasonYear", SqlDbType.NChar).Value = seasonYear; cmd.Parameters.Add("@reasonForLeaving", SqlDbType.NVarChar).Value = DBNull.Value; } /* Set to valid if admin, false otherwise */ if (Clearance == "1") { cmd.Parameters.Add("@isValid", SqlDbType.Bit).Value = true; cmd.Parameters.Add("@pay", SqlDbType.Money).Value = pay; } else { cmd.Parameters.Add("@isValid", SqlDbType.Bit).Value = false; cmd.Parameters.Add("@pay", SqlDbType.Money).Value = DBNull.Value; } /* Call appropriate method */ switch (employeeType) { case "fulltime": cmd.CommandText = "CreateFullTime"; break; case "parttime": cmd.CommandText = "CreatePartTime"; break; case "contract": cmd.CommandText = "CreateContract"; break; case "seasonal": cmd.CommandText = "CreateSeasonal"; break; } connection.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { DbLogging.Log(ex); throw; } finally { connection.Close(); } }