public IList <Lumen> GetLumenByID(string id)
{
string sqlquery = "SELECT * FROM [Lumen] WHERE ID=" + int.Parse(id) + " ORDER BY ID";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlquery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
List <Lumen> lumenList = new List <Lumen>();
while (reader.Read())
{
//Lumen lumen = ReadLumen(reader);
Lumen lumen = new Lumen();
lumen.ID = reader.GetInt32(0);
lumen.Amount = reader.GetInt32(1);
lumen.Date = reader.GetDateTime(2);
lumenList.Add(lumen);
}
return(lumenList);
}
}
}
}
public Lumen AddLumen(Lumen lumen)
{
const string sqlquery = "INSERT INTO [Lumen] (Amount) OUTPUT Inserted.ID VALUES (@Amount)";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlquery, connection))
{
//command.Parameters.AddWithValue("@ID", lumen.ID);
command.Parameters.AddWithValue("@Amount", lumen.Amount);
//command.Parameters.AddWithValue("@Date", lumen.Date);
//Returns the first column (ID) of the first row in the insert
lumen.ID = (int)command.ExecuteScalar();
return(lumen);
}
}
}
public Lumen UpdateLumen(Lumen lumen, string id)
{
string sqlquery = "UPDATE [Lumen] SET Amount=@Amount, Date=@Date WHERE ID=" + int.Parse(id);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlquery, connection))
{
//command.Parameters.AddWithValue("@ID", lumen.ID);
command.Parameters.AddWithValue("@Amount", lumen.Amount);
command.Parameters.AddWithValue("@Date", lumen.Date);
//if (command.ExecuteNonQuery() > 0)
//{
// //success
// return lumen;
//}
//return null;
//If row affected ( row updated )
return(command.ExecuteNonQuery() > 0 ? lumen : null);
}
}
}