public void Update(Artist Artist)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
// SqlCommand Cmd = new SqlCommand("UPDATE Artist SET ... WHERE ([ArtistId] = @ArtistId)");
SqlCommand Cmd = new SqlCommand("UPDATE Artist " +
"SET Username = @Username" +
", DisplayName = @DisplayName" +
", Email = @Email" +
", Passwd = @Passwd" +
", PasswordSalt = @PasswordSalt" +
", Bio = @Bio" +
" WHERE ArtistId = @ArtistId", con
);
con.Open();
Cmd.Parameters.AddWithValue("@ArtistId", Artist.Id);
Cmd.Parameters.AddWithValue("@Username", Artist.Username);
Cmd.Parameters.AddWithValue("@DisplayName", Artist.DisplayName);
Cmd.Parameters.AddWithValue("@Email", Artist.Email);
Cmd.Parameters.AddWithValue("@Passwd", Artist.Passwd);
Cmd.Parameters.AddWithValue("@PasswordSalt", Artist.PasswordSalt);
Cmd.Parameters.AddWithValue("@Bio", Artist.Bio);
Cmd.ExecuteNonQuery();
Quick.Print(Artist.Id);
con.Close();
}
}
protected void LoadButtons(int ItemLimit)
{
if (pageNo == 1)
{
PrevPage.Visible = false;
}
else
{
PrevPage.Visible = true;
}
// Get no of records in selected table
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("SELECT COUNT(*) FROM ARTPIECE INNER JOIN ARTIST ON ARTPIECE.ARTISTID = ARTIST.ARTISTID INNER JOIN WISHEDART ON WISHEDART.ARTPIECEID = ARTPIECE.ARTPIECEID INNER JOIN CUSTOMER ON CUSTOMER.CUSTID = WISHEDART.CUSTID WHERE CUSTOMER.USERNAME = @USERNAME ", con);
Cmd.Parameters.AddWithValue("@USERNAME", username);
int NoOfRecords = Convert.ToInt32(Cmd.ExecuteScalar());
if (pageNo * ItemLimit < NoOfRecords)
{
NextPage.Visible = true;
}
else
{
NextPage.Visible = false;
}
con.Close();
}
}
protected void LoadButtons(int ItemLimit)
{
if (pageNo == 1)
{
PrevPage.Visible = false;
}
else
{
PrevPage.Visible = true;
}
// Get no of records in selected table
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("SELECT COUNT(*) FROM ARTPIECE INNER JOIN ARTIST ON ARTPIECE.ARTISTID = ARTIST.ARTISTID WHERE (ARTPIECE.ISPUBLIC = 1)", con);
int NoOfRecords = Convert.ToInt32(Cmd.ExecuteScalar());
if (pageNo * ItemLimit < NoOfRecords)
{
NextPage.Visible = true;
}
else
{
NextPage.Visible = false;
}
con.Close();
}
}
public WishedArt GetSpecific(string custId, string artpieceId)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd;
Cmd = new SqlCommand("SELECT * FROM WishedArt WHERE ([CUSTID] = @CUSTID) AND ([ARTPIECEID] = @ARTPIECEID)", con);
Cmd.Parameters.AddWithValue("@CUSTID", custId);
Cmd.Parameters.AddWithValue("@ARTPIECEID", artpieceId);
using (SqlDataReader Dr = Cmd.ExecuteReader())
{
if (Dr.Read())
{
/* method thanks to Ron C - https://stackoverflow.com/a/41041029 */
// int i = 0;
//return new Customer(Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), (byte[]) Dr["PasswordSalt"], Dr.GetString(i++));
// method thanks to Andy Edinborough & Cosmin - https://stackoverflow.com/a/5371281
WishedArt wishedArt = new WishedArt(
(int)Dr["WishIndex"],
(string)Dr["ArtpieceId"],
(string)Dr["CustId"]
);
Dr.Close();
con.Close();
return(wishedArt);
}
}
con.Close();
return(null);
}
}
public void Update(Artpiece Artpiece)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
// SqlCommand Cmd = DBUtil.GenerateSql("UPDATE Customer SET ... WHERE ([CustomerId] = @CustomerId)");
SqlCommand Cmd = new SqlCommand("UPDATE Artpiece " +
"SET ArtistId = @ArtistId" +
", Title = @Title" +
", ImageLink = @ImageLink" +
", Price = @Price" +
", Stocks = @Stocks" +
", IsForSale = @IsForSale" +
", Tags = @Tags" +
", IsPublic = @IsPublic" +
" WHERE ArtpieceId = @ArtpieceId", con
);
con.Open();
Cmd.Parameters.AddWithValue("@ArtpieceId", Artpiece.ArtpieceId);
Cmd.Parameters.AddWithValue("@ArtistId", Artpiece.ArtistId);
Cmd.Parameters.AddWithValue("@Title", Artpiece.Title);
Cmd.Parameters.AddWithValue("@ImageLink", Artpiece.ImageLink);
Cmd.Parameters.AddWithValue("@Price", Artpiece.Price);
Cmd.Parameters.AddWithValue("@Stocks", Artpiece.Stocks);
Cmd.Parameters.AddWithValue("@IsForSale", Artpiece.IsForSale);
Cmd.Parameters.AddWithValue("@Tags", Artpiece.Tags);
Cmd.Parameters.AddWithValue("@IsPublic", Artpiece.IsPublic);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public Order_Artwork Get(string Field, string Value)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd;
Cmd = new SqlCommand("SELECT * FROM Order_Artwork WHERE ([" + Field + "] = @Value)", con);
Cmd.Parameters.AddWithValue("@Value", Value);
using (SqlDataReader Dr = Cmd.ExecuteReader())
{
if (Dr.Read())
{
/* method thanks to Ron C - https://stackoverflow.com/a/41041029 */
// int i = 0;
//return new Customer(Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), (byte[]) Dr["PasswordSalt"], Dr.GetString(i++));
// method thanks to Andy Edinborough & Cosmin - https://stackoverflow.com/a/5371281
Order_Artwork orderArtwork = new Order_Artwork(
(int)Dr["Index"],
(string)Dr["OrderId"],
(string)Dr["ArtpieceId"],
(int)Dr["Quantity"]
);
Dr.Close();
con.Close();
return(orderArtwork);
}
}
con.Close();
return(null);
}
}
// crud functions
public void Add(Customer Customer)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("INSERT INTO Customer(CustId, Username, DisplayName, Email, Passwd, PasswordSalt, CreditCardNo)"
+ "VALUES(@CustID, @Username, @DisplayName, @Email, @Passwd, @PasswordSalt, @CreditCardNo)", con);
Cmd.Parameters.AddWithValue("@CustID", Customer.Id);
Cmd.Parameters.AddWithValue("@Username", Customer.Username);
Cmd.Parameters.AddWithValue("@DisplayName", Customer.DisplayName);
Cmd.Parameters.AddWithValue("@Email", Customer.Email);
Cmd.Parameters.AddWithValue("@Passwd", Customer.Passwd);
Cmd.Parameters.AddWithValue("@PasswordSalt", Customer.PasswordSalt);
if (Customer.CreditCardNo == null)
{
Customer.CreditCardNo = "not given";
}
Cmd.Parameters.AddWithValue("@CreditCardNo", Customer.CreditCardNo);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public void Update(Customer Customer, string OriginalID)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
// SqlCommand Cmd = DBUtil.GenerateSql("UPDATE Customer SET ... WHERE ([CustID] = @CustID)");
SqlCommand Cmd = new SqlCommand("UPDATE Customer " +
"SET CustID = @CustID, Username = @Username" +
", DisplayName = @DisplayName" +
", Email = @Email" +
", Passwd = @Passwd" +
", PasswordSalt = @PasswordSalt" +
", CreditCardNo = @CreditCardNo" +
" WHERE CustID = @OriginalCustID", con
);
con.Open();
Cmd.Parameters.AddWithValue("@CustID", Customer.Id);
Cmd.Parameters.AddWithValue("@OriginalCustID", OriginalID);
Cmd.Parameters.AddWithValue("@Username", Customer.Username);
Cmd.Parameters.AddWithValue("@DisplayName", Customer.DisplayName);
Cmd.Parameters.AddWithValue("@Email", Customer.Email);
Cmd.Parameters.AddWithValue("@Passwd", Customer.Passwd);
Cmd.Parameters.AddWithValue("@PasswordSalt", Customer.PasswordSalt);
Cmd.Parameters.AddWithValue("@CreditCardNo", Customer.CreditCardNo);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public void Delete(WishedArt WishedArt)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("DELETE FROM WishedArt WHERE WishIndex = @WishIndex", con);
Cmd.Parameters.AddWithValue("@WishIndex", WishedArt.WishIndex);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public void Delete(Order_Artwork Order_Artwork)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("DELETE FROM Order_Artwork WHERE Index = @Index", con);
Cmd.Parameters.AddWithValue("@CustomerId", Order_Artwork.Index);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public List <Artist> GetList(string Type, string Value)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
SqlCommand Cmd;
if (!Type.Equals("Passwd") && !Type.Equals("PasswordSalt"))
{
con.Open();
Cmd = new SqlCommand("SELECT * FROM Artist WHERE ([" + Type + "] = @Value)", con);
Cmd.Parameters.AddWithValue("@Value", Value);
}
else
{
return(null);
}
using (SqlDataReader Dr = Cmd.ExecuteReader())
{
List <Artist> Artists = new List <Artist>();
while (Dr.Read())
{
/* method thanks to Ron C - https://stackoverflow.com/a/41041029 */
// int i = 0;
//return new Artist(Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), (byte[]) Dr["PasswordSalt"], Dr.GetString(i++));
// method thanks to Andy Edinborough & Cosmin - https://stackoverflow.com/a/5371281
Artists.Add(new Artist(
(string)Dr["ArtistId"],
(string)Dr["Username"],
(string)Dr["DisplayName"],
(string)Dr["Email"],
(string)Dr["Passwd"],
(byte[])Dr["PasswordSalt"],
(string)Dr["Bio"])
);
}
Dr.Close();
con.Close();
if (Artists.Any())
{
return(Artists);
}
return(null);
}
}
}
public List <Customer> GetList(string Field, string Value)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
SqlCommand Cmd;
if (!Field.Equals("Passwd") && !Field.Equals("PasswordSalt"))
{
con.Open();
Cmd = new SqlCommand("SELECT * FROM Customer WHERE ([" + Field + "] = @Value)", con);
Cmd.Parameters.AddWithValue("@Value", Value);
}
else
{
return(null);
}
using (SqlDataReader Dr = Cmd.ExecuteReader())
{
List <Customer> Customer = new List <Customer>();
while (Dr.Read())
{
/* method thanks to Ron C - https://stackoverflow.com/a/41041029 */
// int i = 0;
//return new Customer(Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), (byte[]) Dr["PasswordSalt"], Dr.GetString(i++));
// method thanks to Andy Edinborough & Cosmin - https://stackoverflow.com/a/5371281
Customer.Add(new Customer(
(string)Dr["CustID"],
(string)Dr["Username"],
(string)Dr["DisplayName"],
(string)Dr["Email"],
(string)Dr["Passwd"],
(byte[])Dr["PasswordSalt"],
(string)Dr["CreditCardNo"])
);
}
Dr.Close();
con.Close();
if (Customer.Any())
{
return(Customer);
}
return(null);
}
}
}
public void Delete(Artpiece Artpiece)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("DELETE FROM Artpiece WHERE ArtpieceId = @ArtpieceId", con);
Cmd.Parameters.AddWithValue("@ArtpieceId", Artpiece.ArtpieceId);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public void Delete(Customer Customer)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("DELETE FROM Customer WHERE CustID = @CustID", con);
Cmd.Parameters.AddWithValue("@CustID", Customer.Id);
Cmd.ExecuteNonQuery();
con.Close();
}
}
// crud functions
public void Add(WishedArt WishedArt)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("INSERT INTO WishedArt(ArtpieceId, CustId)"
+ "VALUES(@ArtpieceId, @CustId)", con);
Cmd.Parameters.AddWithValue("@ArtpieceId", WishedArt.ArtpieceId);
Cmd.Parameters.AddWithValue("@CustId", WishedArt.CustId);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public List <Artpiece> GetList(string Field, string Value)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd;
Cmd = new SqlCommand("SELECT * FROM Artpiece WHERE ([" + Field + "] = @Value)", con);
Cmd.Parameters.AddWithValue("@Value", Value);
using (SqlDataReader Dr = Cmd.ExecuteReader())
{
List <Artpiece> Artpiece = new List <Artpiece>();
while (Dr.Read())
{
/* method thanks to Ron C - https://stackoverflow.com/a/41041029 */
// int i = 0;
//return new Customer(Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), (byte[]) Dr["PasswordSalt"], Dr.GetString(i++));
// method thanks to Andy Edinborough & Cosmin - https://stackoverflow.com/a/5371281
/*Artpiece.Add(new Artpiece(
* (string)Dr["ArtpieceId"],
* (string)Dr["ArtistId"],
* (string)Dr["Title"],
* (string)Dr["About"],
* (string)Dr["ImageLink"],
* Convert.ToDouble((decimal)Dr["Price"]),
* (int)Dr["Stocks"],
* (bool)Dr["IsForSale"],
* (string)Dr["Tags"],
* (bool)Dr["IsPublic"]
* );*/
}
Dr.Close();
con.Close();
if (Artpiece.Any())
{
return(Artpiece);
}
return(null);
}
}
}
// crud functions
public void Add(Order_Artwork Order_Artwork)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd = new SqlCommand("INSERT INTO Order_Artwork(OrderId, ArtpieceId, Quantity)"
+ "VALUES(@OrderId, @ArtpieceId, @Quantity)", con);
// Cmd.Parameters.AddWithValue("@Index", Order_Artwork.Index); Auto generated hence no need to insert
Cmd.Parameters.AddWithValue("@OrderId", Order_Artwork.OrderId);
Cmd.Parameters.AddWithValue("@ArtpieceId", Order_Artwork.ArtpieceId);
Cmd.Parameters.AddWithValue("@Quantity", Order_Artwork.Quantity);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public void Update(WishedArt WishedArt)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
// SqlCommand Cmd = DBUtil.GenerateSql("UPDATE Customer SET ... WHERE ([CustomerId] = @CustomerId)");
SqlCommand Cmd = new SqlCommand("UPDATE WishedArt " +
"SET ArtpieceId = @ArtpieceId" +
", CustId = @CustId" +
" WHERE WishIndex = @WishIndex", con
);
con.Open();
Cmd.Parameters.AddWithValue("@WishIndex", WishedArt.WishIndex);
Cmd.Parameters.AddWithValue("@ArtpieceId", WishedArt.ArtpieceId);
Cmd.Parameters.AddWithValue("@CustId", WishedArt.CustId);
Cmd.ExecuteNonQuery();
con.Close();
}
}
// crud functions
public void Add(Artist Artist)
{
// Prevent any connection leak
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open(); // Open connection to DB
SqlCommand Cmd = new SqlCommand("INSERT INTO Artist(ArtistId, Username, DisplayName, Email, Passwd, PasswordSalt, Bio, Active)"
+ "VALUES(@ArtistId, @Username, @DisplayName, @Email, @Passwd, @PasswordSalt, @Bio, @Active)", con);
Cmd.Parameters.AddWithValue("@ArtistId", Artist.Id);
Cmd.Parameters.AddWithValue("@Username", Artist.Username);
Cmd.Parameters.AddWithValue("@DisplayName", Artist.DisplayName);
Cmd.Parameters.AddWithValue("@Email", Artist.Email);
Cmd.Parameters.AddWithValue("@Passwd", Artist.Passwd);
Cmd.Parameters.AddWithValue("@PasswordSalt", Artist.PasswordSalt);
Cmd.Parameters.AddWithValue("@Bio", Artist.Bio);
Cmd.Parameters.AddWithValue("@Active", true);
Cmd.ExecuteNonQuery();
con.Close(); // Close connection to DB
}
}
public void Update(Order_Artwork Order_Artwork)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
// SqlCommand Cmd = DBUtil.GenerateSql("UPDATE Order_Artwork SET ... WHERE ([Index] = @Index)");
SqlCommand Cmd = new SqlCommand("UPDATE Order_Artwork " +
"SET OrderId = @OrderId" +
", ArtpeiceId = @ArtpeiceId" +
", Quantity = @Quantity" +
" WHERE Index = @Index", con
);
con.Open();
Cmd.Parameters.AddWithValue("@Index", Order_Artwork.Index);
Cmd.Parameters.AddWithValue("@OrderId", Order_Artwork.OrderId);
Cmd.Parameters.AddWithValue("@ArtpeiceId", Order_Artwork.ArtpieceId);
Cmd.Parameters.AddWithValue("@Quantity", Order_Artwork.Quantity);
Cmd.ExecuteNonQuery();
con.Close();
}
}
public List <WishedArt> GetList(string Field, string Value)
{
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open();
SqlCommand Cmd;
Cmd = new SqlCommand("SELECT * FROM Customer WHERE ([" + Field + "] = @Value)", con);
Cmd.Parameters.AddWithValue("@Value", Value);
using (SqlDataReader Dr = Cmd.ExecuteReader())
{
List <WishedArt> WishedArt = new List <WishedArt>();
while (Dr.Read())
{
/* method thanks to Ron C - https://stackoverflow.com/a/41041029 */
// int i = 0;
//return new Customer(Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), Dr.GetString(i++), (byte[]) Dr["PasswordSalt"], Dr.GetString(i++));
// method thanks to Andy Edinborough & Cosmin - https://stackoverflow.com/a/5371281
WishedArt.Add(new WishedArt(
(int)Dr["WishIndex"],
(string)Dr["ArtpieceId"],
(string)Dr["CustId"])
);
}
Dr.Close();
con.Close();
if (WishedArt.Any())
{
return(WishedArt);
}
return(null);
}
}
}
// crud functions
public void Add(Artpiece Artpiece)
{
// Prevent any connection leak
using (SqlConnection con = DBUtil.BuildConnection())
{
con.Open(); // Open connection to DB
SqlCommand Cmd = new SqlCommand("INSERT INTO Artpiece(ArtpieceId, ArtistId, Title, About, ImageLink, Price, Stocks, IsForSale, Tags, IsPublic)"
+ "VALUES(@ArtpieceId, @ArtistId, @Title, @About, @ImageLink, @Price, @Stocks, @IsForSale, @Tags, @IsPublic)", con);
Cmd.Parameters.AddWithValue("@ArtpieceId", Artpiece.ArtpieceId);
Cmd.Parameters.AddWithValue("@ArtistId", Artpiece.ArtistId);
Cmd.Parameters.AddWithValue("@Title", Artpiece.Title);
Cmd.Parameters.AddWithValue("@ImageLink", Artpiece.ImageLink);
Cmd.Parameters.AddWithValue("@Price", Artpiece.Price);
Cmd.Parameters.AddWithValue("@Stocks", Artpiece.Stocks);
Cmd.Parameters.AddWithValue("@IsForSale", Artpiece.IsForSale);
Cmd.Parameters.AddWithValue("@Tags", Artpiece.Tags);
Cmd.Parameters.AddWithValue("@IsPublic", Artpiece.IsPublic);
Cmd.Parameters.AddWithValue("@About", Artpiece.About);
Cmd.ExecuteNonQuery();
con.Close(); // Close connection to DB
}
}