public static bool InsertDTOEmployee(Employees e)
{
string connectionString = ConfigurationManager.ConnectionStrings["Week08Day03"].ConnectionString;
string query = string.Format(@"IF EXISTS (SELECT * FROM Employees WHERE id = {0})
BEGIN
UPDATE Employees
SET Name = 'First'
WHERE ID = {0}
END
ELSE
BEGIN
INSERT INTO Employees
VALUES ('{1}', NULL, '1900-01-01', 2, 2)
END", e.Id, e.Name);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
int affectedRows = command.ExecuteNonQuery();
if (affectedRows != 0)
{
return true;
}
}
return false;
}
static void Main(string[] args)
{
Employees e = new Employees();
e.Id = 30;
e.Name = "VS TEST";
Console.WriteLine(InsertDTOEmployee(e));
}
public static List<Employees> GetEmployees()
{
List<Employees> list = new List<Employees>();
string connectionString = ConfigurationManager.ConnectionStrings["Week08Day03"].ConnectionString;
string query = @"SELECT [ID],
[Name],
[Email],
[DateOfBirth],
[ManagerID],
[DepartmentID]
FROM [Employees]";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Employees temp = new Employees();
temp.Id = GetIntOrZero(reader["ID"]);
temp.Name = GetStringOrNull(reader["Name"]);
temp.Email = GetStringOrNull(reader["Email"]);
temp.DateOfBirth = GetDateOrNull(reader["DateOfBirth"]);
temp.ManagerId = GetIntOrZero(reader["ManagerID"]);
temp.DepartmentId = GetIntOrZero(reader["DepartmentID"]);
list.Add(temp);
}
}
}
return list;
}