public List <ShopMessage> GetActive()
{
List <ShopMessage> returnMe = new List <ShopMessage>();
using (SqlConnection connection = new SqlConnection(Settings.DBConnectionString_ShopVision))
{
SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = SQL + " WHERE MsgStart < {fn NOW()} AND MsgEnd > {fn NOW()};"
};
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
ShopMessage msg = dataReaderToShopMessage(dbDataReader);
if (msg != null)
{
returnMe.Add(msg);
}
}
}
sqlCommand.Connection.Close();
}
return(returnMe);
}
public void Expire(ShopMessage message)
{
Expire(message.ID);
}
public ShopMessage Add(string MsgSender, string MsgContent, DateTime MsgStart, DateTime MsgEnd, bool IsImportant, string Icon)
{
DateTime createdTime = DateTime.Now;
using (SqlConnection connection = new SqlConnection(Settings.DBConnectionString_ShopVision))
{
SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "INSERT INTO ShopMessages(MsgSender, MsgContent, MsgStart, MsgEnd, HighImportance, MsgCreated, MsgIcon) VALUES(@MSGSENDER, @MSGCONTENT, @MSGSTART, @MSGEND, @MSGHIGH, @MSGCREATED, @MSGICON);"
};
sqlCommand.Parameters.AddWithValue("MSGSENDER", MsgSender);
sqlCommand.Parameters.AddWithValue("MSGCONTENT", MsgContent);
sqlCommand.Parameters.AddWithValue("MSGSTART", MsgStart);
sqlCommand.Parameters.AddWithValue("MSGEND", MsgEnd);
sqlCommand.Parameters.AddWithValue("MSGHIGH", IsImportant);
sqlCommand.Parameters.AddWithValue("MSGCREATED", createdTime);
sqlCommand.Parameters.AddWithValue("MSGICON", Icon);
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
// Now get the SQL ID of the message we just added
using (SqlConnection connection = new SqlConnection(Settings.DBConnectionString_ShopVision))
{
SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "SELECT * FROM ShopMessages WHERE MsgSender=@MSGSENDER AND MsgContent=@MSGCONTENT AND MsgStart=@MSGSTART AND MsgEnd=@MSGEND AND HighImportance=@MSGHIGH AND MsgCreated=@MSGCREATED AND MsgIcon=@MSGICON"
};
sqlCommand.Parameters.AddWithValue("MSGSENDER", MsgSender);
sqlCommand.Parameters.AddWithValue("MSGCONTENT", MsgContent);
sqlCommand.Parameters.AddWithValue("MSGSTART", MsgStart);
sqlCommand.Parameters.AddWithValue("MSGEND", MsgEnd);
sqlCommand.Parameters.AddWithValue("MSGHIGH", IsImportant);
sqlCommand.Parameters.AddWithValue("MSGCREATED", createdTime);
sqlCommand.Parameters.AddWithValue("MSGICON", Icon);
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
ShopMessage msg = dataReaderToShopMessage(dbDataReader);
if (msg != null)
{
return(msg);
}
}
}
sqlCommand.Connection.Close();
}
return(new ShopMessage()
{
Sender = MsgSender,
Content = MsgContent,
Start = MsgStart,
End = MsgEnd,
IsImportant = IsImportant,
Created = DateTime.Now,
Icon = Icon
});
}
public void Delete(ShopMessage message)
{
Delete(message.ID);
}