/// <summary>
/// Add new item to cart or increments qty if item is already in cart.
/// </summary>
/// <param name="ASIN"></param>
public int AddToCart(string asin)
{
//Add the item to update qty if already exists.
//Sql Upsert: Update if record exists, otherwise insert.
string SqlSelect = "SELECT qty from tblCart " +
"WHERE ASIN = @asin AND sessionID = @SessionId ";
string SqlUpdate = "Update tblCart SET qty = (qty + 1) " +
"WHERE ASIN = @asin AND sessionID = @SessionId ";
string SqlInsert = "Insert into tblCart (sessionID, ASIN, qty, dateAdded) values " +
"(@sessionID, @asin, 1, @dateAdded);";
string sql = "if exists (" + SqlSelect + ") " + SqlUpdate + " else " + SqlInsert;
//populate model
MusicCartModel musicCartModel = new MusicCartModel();
musicCartModel.ASIN = asin;
musicCartModel.SessionId = SessionId;
musicCartModel.DateAdded = DateTime.Now;
using (IDbConnection db = new SqlConnection(connectString))
{
int RecordsAffected = db.Query <Int32>(sql, musicCartModel).SingleOrDefault();
return(RecordsAffected);
}
}
/// <summary>
/// Decreases qty. by one item. Item is deleted if resulting quantity is < 1.
/// </summary>
/// <param name="ASIN">ASIN</param>
public int RemoveFromCart(string ASIN)
{
string sqlUpdate = "Update tblCart SET qty = (qty - 1) " +
"WHERE ASIN = @ASIN AND sessionID = @SessionId; ";
string sqlDelete = "Delete from tblCart " +
"WHERE ASIN = @ASIN AND sessionID = @SessionId AND qty < 1 ;";
//execute both sql statements
string sql = sqlUpdate + sqlDelete;
MusicCartModel musicItem = new MusicCartModel();
musicItem.ASIN = ASIN;
musicItem.SessionId = SessionId;
using (IDbConnection db = new SqlConnection(connectString))
{
int RecordsAffected = db.Query <Int32>(sql, musicItem).SingleOrDefault();
return(RecordsAffected);
}
}
