public IEnumerable <Salary> GetAllSalaries()
{
var result = new List <Salary>();
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = new SqlCommand("GetAllSalaries", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var salary = new Salary
{
IDSalary = (int)reader["IDSalary"],
DateOfPayment = (int)reader["DateOfPayment"],
Amount = (int)reader["Amount"]
};
result.Add(salary);
}
}
return(result);
}
public IEnumerable <Responsibilities> GetAllResponsibilities()
{
var result = new List <Responsibilities>();
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = new SqlCommand("GetAllResponsibilities", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var responsibilities = new Responsibilities
{
IDPosition = (int)reader["IDPosition"],
IDTask = (int)reader["IDTask"]
};
result.Add(responsibilities);
}
}
return(result);
}
public IEnumerable <Entities.Task> GetAllTasks()
{
var result = new List <Entities.Task>();
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = new SqlCommand("GetAllTasks", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var task = new Entities.Task
{
IDTask = (int)reader["IDTask"],
NameOfTask = (string)reader["NameOfTask"]
};
result.Add(task);
}
}
return(result);
}
public IEnumerable <PayrollForms> GetAllPayrollForms()
{
var result = new List <PayrollForms>();
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = new SqlCommand("GetAllPayrollForms", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var payrollForms = new PayrollForms
{
IDEmployee = (int)reader["IDEmployee"],
IDSalary = (int)reader["IDSalary"]
};
result.Add(payrollForms);
}
}
return(result);
}
public void DeleteListOfWorkers(int value)
{
const string sqlExpression = "DELETE FROM ListOfWorkers WHERE IDEmployee = @idEmployee";
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
var idParam = new SqlParameter("@idEmployee", value);
command.Parameters.Add(idParam);
command.ExecuteNonQuery();
}
}
public void DeletePosition(int value)
{
const string sqlExpression = "DELETE FROM Position WHERE IDPosition = @idPosition";
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
var idParam = new SqlParameter("@idPosition", value);
command.Parameters.Add(idParam);
command.ExecuteNonQuery();
}
}
public void DeleteSalary(int value)
{
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "DeleteSalary";
cmd.Parameters.Add(new SqlParameter("@IDSalary", value));
connection.Open();
cmd.ExecuteNonQuery();
}
}
//private string connnectionString = "Data Source=DESKTOP-EMEUIMH\\SQLEXPRESS; Initial Catalog = Company; Integrated Security = True";
public int AddListOfWorkers(ListOfWorkers value)
{
const string sqlExpression =
"INSERT INTO ListOfWorkers (IDEmployee, IDPosition) VALUES (@IDEmployee, @IDPosition)";
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
var param = new SqlParameter("@IDEmployee", value.IDEmployee);
command.Parameters.Add(param);
var param2 = new SqlParameter("@IDPosition", value.IDPosition);
command.Parameters.Add(param2);
var number = command.ExecuteNonQuery();
return(number);
}
}
//private string connnectionString = "Data Source=DESKTOP-EMEUIMH\\SQLEXPRESS; Initial Catalog = Company; Integrated Security = True";
public int AddPosition(Position value)
{
const string sqlExpression =
"INSERT INTO Position (IDPosition, NameOfPosition) VALUES (@IDPosition, @NameOfPosition)";
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
var param = new SqlParameter("@IDPosition", value.IDPosition);
command.Parameters.Add(param);
var param2 = new SqlParameter("@NameOfPosition", value.NameOfPosition);
command.Parameters.Add(param2);
var number = command.ExecuteNonQuery();
return(number);
}
}
//private string connnectionString = "Data Source=DESKTOP-EMEUIMH\\SQLEXPRESS; Initial Catalog = Company; Integrated Security = True";
public int AddEmployee(Employee value)
{
const string sqlExpression =
"INSERT INTO Employee (FullName, Age, PhoneNumber, Email, Address) VALUES (@FullName, @Age, @PhoneNumber, @Email, @Address)";
using (var connection = MSSQLdb.GetConnection())
{
//SqlCommand cmd = connection.CreateCommand();
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.CommandText = "AddEmployee";
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
var param = new SqlParameter("@IDEmployee", value.IDEmployee);
command.Parameters.Add(param);
var param2 = new SqlParameter("@FullName", value.FullName);
command.Parameters.Add(param2);
var param3 = new SqlParameter("@Age", value.Age);
command.Parameters.Add(param3);
var param4 = new SqlParameter("@PhoneNumber", value.PhoneNumber);
command.Parameters.Add(param4);
var param5 = new SqlParameter("@Email", value.Email);
command.Parameters.Add(param5);
var param6 = new SqlParameter("@Address", value.Address);
command.Parameters.Add(param6);
var number = command.ExecuteNonQuery();
return(number);
//cmd.Parameters.Add(new SqlParameter("@IDEmployee", value.IDEmployee));
//cmd.Parameters.Add(new SqlParameter("@FullName", value.FullName));
//cmd.Parameters.Add(new SqlParameter("@Age", value.Age));
//cmd.Parameters.Add(new SqlParameter("@PhoneNumber", value.PhoneNumber));
//cmd.Parameters.Add(new SqlParameter("@Email", value.Email));
//cmd.Parameters.Add(new SqlParameter("@Address", value.Address));
//connection.Open();
//Object tmp = cmd.ExecuteScalar();
//if (tmp == null)
// return -1;
//return (int)tmp;
//return (int)cmd.ExecuteScalar();
}
}
public IEnumerable <ListOfWorkers> GetAllListOfWorkers()
{
const string sqlExpression = "SELECT * FROM ListOfWorkers";
var result = new List <ListOfWorkers>();
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
using (var dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
result.Add(new ListOfWorkers(
(int)dataReader["IDEmployee"],
(int)dataReader["IDPosition"]));
}
}
}
return(result.AsEnumerable());
}
public IEnumerable <Position> GetAllPositions()
{
const string sqlExpression = "SELECT * FROM Position";
var result = new List <Position>();
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
using (var dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
result.Add(new Position(
(int)dataReader["IDPosition"],
(string)dataReader["NameOfPosition"]));
}
}
}
return(result.AsEnumerable());
}
//private string connnectionString = "Data Source=DESKTOP-EMEUIMH\\SQLEXPRESS; Initial Catalog = Company; Integrated Security = True";
public int AddTask(Entities.Task value)
{
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AddTask";
cmd.Parameters.Add(new SqlParameter("@IDTask", value.IDTask));
cmd.Parameters.Add(new SqlParameter("@NameOfTask", value.NameOfTask));
connection.Open();
Object tmp = cmd.ExecuteScalar();
if (tmp == null)
{
return(-1);
}
return((int)tmp);
//return (int)cmd.ExecuteScalar();
}
}
public Employee GetEmployeeById(int id)
{
const string sqlExpression =
"SELECT IDEmployee, FullName, Age, PhoneNumber, Email, Address FROM Employee where IDEmployee = @id";
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
command.Parameters.AddWithValue("@id", id);
using (var dataReader = command.ExecuteReader())
{
dataReader.Read();
return(new Employee(
(int)dataReader["IDEmployee"],
(string)dataReader["FullName"],
(int)dataReader["Age"],
(string)dataReader["PhoneNumber"].ToString(),
(string)dataReader["Email"],
(string)dataReader["Address"]));
}
}
}
//private string connnectionString = "Data Source=DESKTOP-EMEUIMH\\SQLEXPRESS; Initial Catalog = Company; Integrated Security = True";
public int AddPayrollForm(PayrollForms value)
{
using (var connection = MSSQLdb.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AddPayrollForm";
cmd.Parameters.Add(new SqlParameter("@IDEmployee", value.IDEmployee));
cmd.Parameters.Add(new SqlParameter("@IDSalary", value.IDSalary));
connection.Open();
Object tmp = cmd.ExecuteScalar();
if (tmp == null)
{
return(-1);
}
return((int)tmp);
//return (int)cmd.ExecuteScalar();
}
}
public IEnumerable <Employee> GetAllEmployees()
{
const string sqlExpression = "SELECT * FROM Employee";
var result = new List <Employee>();
using (var connection = MSSQLdb.GetConnection())
{
connection.Open();
var command = new SqlCommand(sqlExpression, connection);
using (var dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
result.Add(new Employee((int)dataReader["IDEmployee"],
(string)dataReader["FullName"],
(int)dataReader["Age"],
(string)dataReader["PhoneNumber"].ToString(),
(string)dataReader["Email"],
(string)dataReader["Address"]));
}
}
}
return(result.AsEnumerable());
}
