Updating a database row from C#

Question

I need help in updating a row on a database table and stuck in that. Anybody please help me how to achieve this?

I want to check whether the product already exists on my database and if yes, i need to check whether any of the fields is been changed since its been inserted from JSon, if thats the case, then I need to update the fields.

My model looks like this

namespace MySite.Models
{
    [TableName("Product")]
    [PrimaryKey("ProductId", autoIncrement = false)]
    [ExplicitColumns]
    public class Product
    {
        [Column("ProductId")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public Guid ProductId{ get; set; }        
        [Column("ProductName")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string ProductName { get; set; }
        [Column("ProductPrice")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string ProductPrice { get; set; }
        [Column("Category")]
        public string Category { get; set; }
    }
}

Upon user request for a productid, i check the database for whether the product exists in case not exsits, I insert from a json string retrieved from external url:

var myProduct = this.DatabaseContext.Database.Fetch<Product>("Select * from Product where ProductId = @0", productId);

JSon looks like:

{"productId":"4632fdeb-0b8e-471f-a44a-0b07b5444656","ProductName":"MyProduct ABC 01","ProductPrice":"1000","Category":"1"}

My insert looks like this:

Product productInfo = null;
productInfo = JsonConvert.DeserializeObject<Product>(jsonResponse);

if (!myProduct.Any())
{
    this.DatabaseContext.Database.Insert(productInfo);
}

I want to in the same way, update the product row from the same json string, in case it finds a record, how do I do that?

if (myProduct.Any())
{
    //I want to update the matching product row on the database, how do I do that?
}

`if (product.ProductName != myProduct.ProductName) { product.ProductName = myProduct.ProductName; } ... this.DatabaseContext.SaveChanges();`? What have you tried and why didn't that work? — CodeCaster, Jan 22 '20 at 11:46
try this https://stackoverflow.com/a/5842555/349974 but what you are looking for is a MERGE db operation. EF does not provide that out pf the box. So you may want smth like described here: https://stackoverflow.com/a/41515103/5189507 — Jury Golubev, Jan 22 '20 at 11:48
Why do you create your own query instead of using ORM capabilities? — Michał Turczyn, Jan 22 '20 at 11:57
It does seem like you are trying to learn how to use EF by trial/error. I recommend following a tutorial or 2, that will give you a better idea of the out of the box mechanisms that are available to you like how to access/retrieve an entity, update it, and persist the changes back to the server. That is covered in almost all intro tutorials. — Igor, Jan 22 '20 at 12:09

Michał Turczyn · Accepted Answer · 2020-01-22T12:02:58.403

2

You should take advantage of EF, if you are using it! Don't send custom queries, if those can be constructed for you by EF. So you should do something like below:

var productInfo = JsonConvert.DeserializeObject<Product>(jsonResponse);
var product = this.DatabaseContext.Products
  .FirstOrDefault(p => p.ProductId == productInfo.ProductId);

if (product == null) {
  // no product, insert
  this.DatabaseContext.Products.Add(productInfo);
}
else {
  // there is product, update, for example
  product.ProductPirce = productInfo.ProductPrice;
}

this.Databasecontext.SaveChanges();

edited Jan 22 '20 at 12:02

answered Jan 22 '20 at 11:59

Michał Turczyn

32,028
14
47
69

I am getting error on "this.DatabaseContext.Products". this.DatabaseContext doesnt have Products. Am I missing something? – Danny Web Jan 22 '20 at 22:09
@user2160310 How is your table represented in your context? Field of type `DbSet`? – Michał Turczyn Jan 22 '20 at 22:12
Product productInfo = null; productInfo = JsonConvert.DeserializeObject(jsonResponse); I have no table representation in my context. Other than I have declared in Models as described. pls. bear with me im new to this... – Danny Web Jan 22 '20 at 22:19
@user2160310 What approach did you use? Code first or database first? Anyway, context should containt field for all tables in database... Are you passing right connection string? – Michał Turczyn Jan 22 '20 at 22:24
i couldnt get context and all tables therefore I used: var myProduct = this.DatabaseContext.Database.Fetch("Select * from Product where ProductId = @0", productId); am I missing something? – Danny Web Jan 22 '20 at 22:28
@user2160310 I am afraid so. When working with EF, context should always have field for a table - that's the whole point! So you don't need to worry about writing queries, just using strongly typed classes :) You should redo adding EF to your project. – Michał Turczyn Jan 22 '20 at 22:30
I have achieved now updating in this way: myProduct.ProductName = productInfo.ProductName; myProduct.ProductPrice = productInfo.ProductPrice; myProduct.Category = productInfo.Category; this.DatabaseContext.Database.Update(myProduct); it seem to work, is it ok? – Danny Web Jan 22 '20 at 22:52
I think it's enough to call `SaveChanges` instead of `Update`. The rest is perfect :) Don't forget to accept the answer if it helped you :) – Michał Turczyn Jan 22 '20 at 23:23
on my context this.DatabaseContext doent have a SaveChanges method therefore Im using Database.Update instead. Thank you for your support! – Danny Web Jan 22 '20 at 23:36

Updating a database row from C#

1 Answers1