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 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 Artpiece Get(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()) { 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 Artpiece artpiece = 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(); return(artpiece); } } con.Close(); 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 } }