Good day.

There is JSON I write it in a DB.

The task is to check if there are any fields in the database, if there is just an update, if there is a new value to add it, if the table is empty, then write the values.

Json write like that

public class Billing { public string first_name { get; set; } public string last_name { get; set; } public string company { get; set; } public string address_1 { get; set; } public string address_2 { get; set; } public string city { get; set; } public string state { get; set; } public string postcode { get; set; } public string country { get; set; } public string email { get; set; } public string phone { get; set; } } public class Shipping { public string first_name { get; set; } public string last_name { get; set; } public string company { get; set; } public string address_1 { get; set; } public string address_2 { get; set; } public string city { get; set; } public string state { get; set; } public string postcode { get; set; } public string country { get; set; } } public class RootObject { public int id { get; set; } public int parent_id { get; set; } public string status { get; set; } public string order_key { get; set; } public string currency { get; set; } public string version { get; set; } public bool prices_include_tax { get; set; } public string date_created { get; set; } public string date_modified { get; set; } public int customer_id { get; set; } public double discount_total { get; set; } public double discount_tax { get; set; } public double shipping_total { get; set; } public double shipping_tax { get; set; } public double cart_tax { get; set; } public double total { get; set; } public double total_tax { get; set; } public Billing billing { get; set; } public Shipping shipping { get; set; } public string payment_method { get; set; } public string payment_method_title { get; set; } public string transaction_id { get; set; } public string customer_ip_address { get; set; } public string customer_user_agent { get; set; } public string created_via { get; set; } public string customer_note { get; set; } public string date_completed { get; set; } public string date_paid { get; set; } public string cart_hash { get; set; } public List<object> line_items { get; set; } public List<object> tax_lines { get; set; } public List<object> shipping_lines { get; set; } public List<object> fee_lines { get; set; } public List<object> coupon_lines { get; set; } } public async void Down() { RestAPI rest = new RestAPI("http://simplegames.com.ua/wp-json/wc/v1/", "ck_9d64c027d2c5f81b8bed3342eeccc6d337be813d", "cs_60697b1e6cbdeb8d62d19e0765e339f8e3334754"); WCObject wc = new WCObject(rest); //Get all products var orders = await wc.GetOrders(new Dictionary<string, string>() { { "per_page", "100" }}); string products = orders.ToFormattedJsonString(); List<RootObject> rootObjectData = JsonConvert.DeserializeObject<List<RootObject>>(products); foreach (RootObject root in rootObjectData) { int id = root.id; int customer_id = root.customer_id; string fio = root.billing.first_name + " " + " " + root.billing.last_name; string adress = root.billing.address_1 + " " + " " + root.billing.address_2; double total = root.total;

I'm trying to do a check and update so

  using (MySqlConnection connection = new MySqlConnection("Database= milanoold_db;Data Source=144.76.133.122;User Id=*********;Password=********; SslMode=None; CharSet=utf8")) { // Открытие соединения connection.Open(); using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = "if exists(select 183 from orders where id =@id) begin update orders set customer_id = @customer_id, total = @total, fio = @fio, adress = @adress where id = @id end else begin insert into orders (id, customer_id, total, fio, adress) values(@id, @customer_id, @total, @fio, @adress) end"; command.Parameters.AddWithValue("@id", id); command.Parameters.AddWithValue("@customer_id", customer_id); command.Parameters.AddWithValue("@total", total); command.Parameters.AddWithValue("@fio", fio); command.Parameters.AddWithValue("@adress", adress); connection.Open(); command.ExecuteNonQuery(); connection.Close(); }

It gives this error here. Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if exists(select 1983 from orders where id =1943) begin update orders set custom' at line 1 Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if exists(select 1983 from orders where id =1943) begin update orders set custom' at line 1

The question of how this can be fixed or how to rewrite the code, so that it performs the task.

  • Does anyone know how to solve the problem? - Eugene
  • Password, I hope, not real? - Alexander Petrov

1 answer 1

Solution found

 using (MySqlConnection connection = new MySqlConnection("Database= milanoold_db;Data Source=144.76.133.122;User Id=milanoold_u;Password=sK5PvCDP; SslMode=None; CharSet=utf8")) { // Открытие соединения using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = "INSERT INTO orders (id,customer_id,total,fio,adress) VALUES(@id, @customer_id, @total, @fio, @adress) ON DUPLICATE KEY UPDATE id = @id,customer_id = @customer_id,total = @total,fio = @fio,adress = @adress;"; command.Parameters.AddWithValue("@id", id); command.Parameters.AddWithValue("@customer_id", customer_id); command.Parameters.AddWithValue("@total", total); command.Parameters.AddWithValue("@fio", fio); command.Parameters.AddWithValue("@adress", adress); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } }
  • I advise you to change the user and password and no longer shine in open access;) - xSx