public int Insert(VoucherRedeemed vcher)
{
System.Diagnostics.Debug.WriteLine("This is id: " + vcher.VoucherId);
// Execute NonQuery return an integer value
int result = 0;
SqlCommand sqlCmd = new SqlCommand();
//Step 1 - Define a connection to the database by getting
// the connection string from web.config
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
// Step 2 - Instantiate SqlCommand instance to add record
// with INSERT statement
string sqlStmt = "IF EXISTS(SELECT * FROM VoucherRedeemed WHERE VoucherId = " + vcher.VoucherId + " AND UserId = " + vcher.UserId + ") " +
"UPDATE VoucherRedeemed SET Quantity = Quantity + 1 WHERE VoucherId = " + vcher.VoucherId + "AND UserId = " + vcher.UserId + " " +
"ELSE" +
" INSERT INTO VoucherRedeemed(VoucherId, VoucherName, VoucherAmount, VoucherPic, UserId, Quantity) " +
"VALUES (@voucherId, @voucherName, @voucherAmt, @voucherPic, @userId, @quantity)";
sqlCmd = new SqlCommand(sqlStmt, myConn);
// Step 3 : Add each parameterised variable with value
sqlCmd.Parameters.AddWithValue("@voucherId", vcher.VoucherId);
sqlCmd.Parameters.AddWithValue("@voucherName", vcher.VoucherName);
sqlCmd.Parameters.AddWithValue("@voucherAmt", vcher.VoucherAmount);
sqlCmd.Parameters.AddWithValue("@voucherPic", vcher.VoucherPic);
sqlCmd.Parameters.AddWithValue("@userId", vcher.UserId);
sqlCmd.Parameters.AddWithValue("@quantity", vcher.Quantity);
// Step 4 Open connection the execute NonQuery of sql command
myConn.Open();
result = sqlCmd.ExecuteNonQuery();
// Step 5 :Close connection
myConn.Close();
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["user"] == null && Session["admin"] == null)
{
Response.Redirect("homepage.aspx");
}
else if (Session["admin"] != null)
{
Response.Redirect("AddVoucher.aspx");
}
else
{
Users user = (Users)Session["user"];
points = user.points;
Voucher vcher = new Voucher();
vcherList = vcher.GetAllVouchersByName();
if (vcherList.Count == 0)
{
no.Visible = true;
}
VoucherRedeemed voucher = new VoucherRedeemed();
vchers = vcher.GetVoucherById(Request.QueryString["voucherId"]);
if (vchers != null)
{
foreach (var element in vchers)
{
if (points >= double.Parse(element.VoucherPoints))
{
panelError.Visible = false;
voucher.VoucherId = int.Parse(Request.QueryString["voucherId"]);
voucher.VoucherAmount = element.VoucherAmount;
voucher.VoucherName = element.VoucherName;
voucher.VoucherPic = element.VoucherPic;
voucher.UserId = user.id.ToString();
voucher.Quantity = 1;
System.Diagnostics.Debug.WriteLine("This is voucherId: " + voucher.VoucherId);
voucher = new VoucherRedeemed(voucher.VoucherId, voucher.VoucherName, voucher.VoucherAmount, voucher.VoucherPic, voucher.UserId, voucher.Quantity);
int result = voucher.AddVoucher();
user.points = user.points - double.Parse(element.VoucherPoints);
user.UpdatePointsByID(user.id, user.points);
panelSuccess.Visible = true;
points = user.points;
}
else
{
panelSuccess.Visible = false;
panelError.Visible = true;
}
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["user"] != null)
{
VoucherRedeemed vRed = new VoucherRedeemed();
Users user = (Users)Session["user"];
vRedList = vRed.GetAllByUserId(user.id.ToString());
lblPoints.Text = user.points.ToString();
}
}
public List <VoucherRedeemed> SelectAllByUserId(string userID)
{
//Step 1 - Define a connection to the database
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
//Step 2 - Create a DataAdapter to retrieve data from db table
string sqlStmt = "Select * from VoucherRedeemed where UserId = @paraUserId";
SqlDataAdapter da = new SqlDataAdapter(sqlStmt, myConn);
da.SelectCommand.Parameters.AddWithValue("@paraUserId", userID);
//Step 3 - Create a DataSet to store the data to be retrieved
DataSet ds = new DataSet();
//Step 4 - Use the DataAdapter to fill the DataSet with data retrieved
da.Fill(ds);
//Step 5 - Read data from DataSet to List
List <VoucherRedeemed> voucherList = new List <VoucherRedeemed>();
List <VoucherRedeemed> tempList = new List <VoucherRedeemed>();
int rec_cnt = ds.Tables[0].Rows.Count;
for (int i = 0; i < rec_cnt; i++)
{
DataRow row = ds.Tables[0].Rows[i]; // Sql command returns only one record
int voucherId = int.Parse(row["VoucherId"].ToString());
string voucherName = row["VoucherName"].ToString();
string voucherAmt = row["VoucherAmount"].ToString();
string voucherPic = row["VoucherPic"].ToString();
string userId = row["UserId"].ToString();
int quantity = int.Parse(row["Quantity"].ToString());
VoucherRedeemed obj = new VoucherRedeemed(voucherId, voucherName, voucherAmt, voucherPic, userId, quantity);
voucherList.Add(obj);
}
;
return(voucherList);
}
