public async Task <bool> UpdateAsync(DataLayer.Entities.Task task)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
await sqlConnection.OpenAsync();
using (var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = "dbo.sp_UpdateTask",
CommandType = System.Data.CommandType.StoredProcedure
})
{
//set parameters
sqlCommand.Parameters.AddWithValue("Id", task.Id);
sqlCommand.Parameters.AddWithValue("Title", task.Title);
sqlCommand.Parameters.AddWithValue("Description", task.Description);
sqlCommand.Parameters.AddWithValue("Status", task.Status);
sqlCommand.Parameters.AddWithValue("Priority", task.Priority);
sqlCommand.Parameters.AddWithValue("Type", task.Type);
//execute command
int updatedRows = await sqlCommand.ExecuteNonQueryAsync();
return(updatedRows == 1);
}
}
}
private IEnumerable <SubTask> GetSubTasks(DataLayer.Entities.Task task)
{
//you should always close your sqlConnection or put it into using statement so it will be disposed
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();//it will throw exception if connection is not opened
var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = $"select * from dbo.SubTask where ParentTaskId={task.Id}"
};
var dataReader = sqlCommand.ExecuteReader();
var result = new List <SubTask>();
while (dataReader.Read())
{
var subTask = new SubTask
{
Id = (int)dataReader["Id"],
Description = (string)dataReader["Description"],
ParentTask = task,
Status = (DataLayer.Enums.Status)dataReader["Status"],
Title = (string)dataReader["Title"]
};
result.Add(subTask);
}
return(result);
}
}
public async Task <DataLayer.Entities.Task> GetByIdAsync(int id)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
await sqlConnection.OpenAsync();
var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = $"select * from dbo.Tasks where id={id}"
};
SqlDataReader reader = await sqlCommand.ExecuteReaderAsync();
var result = new List <DataLayer.Entities.Task>();
while (reader.Read())
{
var task = new DataLayer.Entities.Task
{
Id = (int)reader["Id"],
Description = (string)reader["Description"],
Priority = (DataLayer.Enums.Priority)reader["Priority"],
Status = (DataLayer.Enums.Status)reader["Status"],
Title = (string)reader["Title"],
Type = (DataLayer.Enums.TaskType)reader["Type"]
};
//select n + 1 problem, n == taskNumber
//task.SubTasks = GetSubTasks(task);
result.Add(task);
}
SetSubTasks(result);
return(result.SingleOrDefault());
}
}
public async Task AddAsync(DataLayer.Entities.Task task)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("Id", dbType: System.Data.DbType.Int32,
direction: System.Data.ParameterDirection.Output);
parameters.Add("Title", task.Title);
parameters.Add("Description", task.Description);
parameters.Add("Priority", task.Priority);
parameters.Add("Status", task.Status);
parameters.Add("Type", task.Type);
int insertedRows =
await sqlConnection.ExecuteAsync("sp_InsertTask", parameters,
commandType : System.Data.CommandType.StoredProcedure);
if (insertedRows != 1)
{
throw new Exception("Insert was unsuccessful");
}
task.Id = parameters.Get <int>("Id");
}
}
private static void InsertTaskTest()
{
var taskRepository = new AdoNetTasksDbRepository();
var task = new DataLayer.Entities.Task();
Console.WriteLine("Enter task title");
task.Title = Console.ReadLine();
Console.WriteLine("Enter task description");
task.Description = Console.ReadLine();
Console.WriteLine("Enter task status");
task.Status = (DataLayer.Enums.Status) int.Parse(Console.ReadLine());
Console.WriteLine("Enter task type");
task.Type = (DataLayer.Enums.TaskType) int.Parse(Console.ReadLine());
Console.WriteLine("Enter task priority");
task.Priority = (DataLayer.Enums.Priority) int.Parse(Console.ReadLine());
taskRepository.AddAsync(task).GetAwaiter().GetResult();
Console.WriteLine($"Id of the new inserted task: {task.Id}");
Console.ReadLine();
}
private static void PrintTask(DataLayer.Entities.Task task)
{
Console.WriteLine("---------------------------------------------------");
Console.WriteLine($"Task Id:{task.Id} Title:{task.Title} Description:{task.Description} Priority:{task.Priority} Status:{task.Status} Type:{task.Type}");
foreach (var subtask in task.SubTasks)
{
Console.WriteLine($"Subtask Id: {subtask.Id}, Title: {subtask.Title}, Description: {subtask.Description}, Status: {subtask.Status}, ");
}
Console.WriteLine("---------------------------------------------------");
}
public async Task RemoveAsync(DataLayer.Entities.Task task)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
var sql = "delete from dbo.SubTask where ParentTaskId=@Id;delete from dbo.Tasks where Id=@Id";
int affectedRows = await sqlConnection.ExecuteAsync(sql, new { task.Id });
if (affectedRows == 0)
{
throw new Exception("Delete was unsuccessful");
}
}
}
public async Task <IEnumerable <DataLayer.Entities.Task> > GetAllAsync()
{
//var sqlConnection = new SqlConnection(_connectionString);
//try
//{
// await sqlConnection.OpenAsync();
//}
//finally
//{
// sqlConnection.Close();
//}
using (var sqlConnection = new SqlConnection(_connectionString))
{
await sqlConnection.OpenAsync();
var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = "select * from dbo.Tasks"
};
SqlDataReader reader = await sqlCommand.ExecuteReaderAsync();
var result = new List <DataLayer.Entities.Task>();
while (reader.Read())
{
var task = new DataLayer.Entities.Task
{
Id = (int)reader["Id"],
Description = (string)reader["Description"],
Priority = (DataLayer.Enums.Priority)reader["Priority"],
Status = (DataLayer.Enums.Status)reader["Status"],
Title = (string)reader["Title"],
Type = (DataLayer.Enums.TaskType)reader["Type"]
};
//select n + 1 problem, n == taskNumber
//task.SubTasks = GetSubTasks(task);
result.Add(task);
}
SetSubTasks(result);
return(result);
}
}
public async Task <bool> UpdateAsync(DataLayer.Entities.Task task)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("Id", task.Id);
parameters.Add("Title", task.Title);
parameters.Add("Description", task.Description);
parameters.Add("Priority", task.Priority);
parameters.Add("Status", task.Status);
parameters.Add("Type", task.Type);
int updatedRows =
await sqlConnection.ExecuteAsync("sp_UpdateTask", parameters,
commandType : System.Data.CommandType.StoredProcedure);
return(updatedRows == 1);
}
}
public async System.Threading.Tasks.Task AddAsync(DataLayer.Entities.Task task)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
await sqlConnection.OpenAsync();
using (var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = "dbo.sp_InsertTask",
CommandType = System.Data.CommandType.StoredProcedure
})
{
//set parameters
sqlCommand.Parameters.AddWithValue("Title", task.Title);
sqlCommand.Parameters.AddWithValue("Description", task.Description);
sqlCommand.Parameters.AddWithValue("Status", task.Status);
sqlCommand.Parameters.AddWithValue("Priority", task.Priority);
sqlCommand.Parameters.AddWithValue("Type", task.Type);
var idParameter = new SqlParameter("Id", System.Data.SqlDbType.Int)
{
Direction = System.Data.ParameterDirection.Output
};
sqlCommand.Parameters.Add(idParameter);
//execute command
int insertedRows = await sqlCommand.ExecuteNonQueryAsync();
if (insertedRows != 1)
{
throw new Exception("Task insert was not successful");
}
//get the id of the inserted task
task.Id = (int)idParameter.Value;
}
}
}
public async System.Threading.Tasks.Task RemoveAsync(DataLayer.Entities.Task task)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
await sqlConnection.OpenAsync();
using (var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = "delete from dbo.Tasks where Id=@Id"
})
{
//set parameters
sqlCommand.Parameters.AddWithValue("Id", task.Id);
//execute command
int deletedRows = await sqlCommand.ExecuteNonQueryAsync();
if (deletedRows != 1)
{
throw new Exception("Delete was not successful");
}
}
}
}
public DataLayer.Entities.Task GetByTitle(string title)
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
//bad way, sql injection
//CommandText = $"select * from dbo.Tasks where id={id}"
CommandText = $"select * from dbo.Tasks where Title=@title"
};
sqlCommand.Parameters.AddWithValue("title", title);
SqlDataReader reader = sqlCommand.ExecuteReader();
var result = new List <DataLayer.Entities.Task>();
while (reader.Read())
{
var task = new DataLayer.Entities.Task
{
Id = (int)reader["Id"],
Description = (string)reader["Description"],
Priority = (DataLayer.Enums.Priority)reader["Priority"],
Status = (DataLayer.Enums.Status)reader["Status"],
Title = (string)reader["Title"],
Type = (DataLayer.Enums.TaskType)reader["Type"]
};
//select n + 1 problem, n == taskNumber
//task.SubTasks = GetSubTasks(task);
result.Add(task);
}
SetSubTasks(result);
return(result.SingleOrDefault());
}
}
public Task <bool> UpdateAsync(DataLayer.Entities.Task task)
{
throw new NotImplementedException();
}
public System.Threading.Tasks.Task RemoveAsync(DataLayer.Entities.Task task)
{
throw new NotImplementedException();
}
public Task AddAsync(DataLayer.Entities.Task task)
{
throw new NotImplementedException();
}
