public static async Task <List <Idea> > getList(int page)
{
var commandString = @"listIdeas";
using (var db = new SQLDisposible())
{
var command = new SqlCommand(commandString, db.Connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@pageNumber", page));
db.Connection.Open();
var reader = await command.ExecuteReaderAsync();
var ideas = new List <Idea>();
while (reader.Read() == true)
{
ideas.Add(Idea.getIdeaFromReader(reader));
}
return(ideas);
}
}
public static async Task <Idea> getIdea(int ideaId)
{
var commandString = @"SELECT * FROM ideas WHERE ideaId = @id;";
using (var db = new SQLDisposible())
{
var command = new SqlCommand(commandString, db.Connection);
command.Parameters.Add(new SqlParameter("@id", ideaId));
db.Connection.Open();
var reader = await command.ExecuteReaderAsync();
if (reader.Read() == true)
{
var idea = Idea.getIdeaFromReader(reader);
return(idea);
}
else
{
return(null);
}
}
}
public async Task <Boolean> Save()
{
using (var db = new SQLDisposible())
{
var commandString = "";
if (this.ideaId == 0)
{
commandString = @"
INSERT INTO ideas(ideaHeading, ideaBody, likes, dislikes, ideaStatus, addedBy, dateAdded) output INSERTED.ideaId
VALUES(@ideaHeading, @ideaBody, @likes, @dislikes, @ideaStatus, @addedBy, @dateAdded);" ;
}
else
{
commandString = @"UPDATE ideas
SET ideaHeading = @ideaHeading,
ideaBody = @ideaBody,
likes = @likes,
dislikes = @dislikes,
ideaStatus = @ideaStatus
WHERE ideaId = @ideaId;" ;
}
var command = new SqlCommand(commandString, db.Connection);
command.Parameters.Add(new SqlParameter("@ideaHeading", this.ideaHeading));
command.Parameters.Add(new SqlParameter("@ideaBody", this.ideaBody));
command.Parameters.Add(new SqlParameter("@likes", this.likes));
command.Parameters.Add(new SqlParameter("@dislikes", this.dislikes));
command.Parameters.Add(new SqlParameter("@ideaStatus", this.ideaStatus));
command.Parameters.Add(new SqlParameter("@addedBy", this.addedBy));
command.Parameters.Add(new SqlParameter("@dateAdded", this.dateAdded));
command.Parameters.Add(new SqlParameter("@ideaId", this.ideaId));
db.Connection.Open();
if (this.ideaId == 0)
{
var newId = (int)await command.ExecuteScalarAsync();
command.Dispose();
if (newId > 0)
{
this.ideaId = newId;
return(true);
}
else
{
return(false);
}
}
else
{
var effected = await command.ExecuteNonQueryAsync();
command.Dispose();
if (effected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
}
