In general, I have a project that works on a local network on the MSSQL database. There is a need to display data on the Internet to work with it through the telephone and other devices on the Internet. So far, I have not yet found the best way out and am in search. What method can you advise?

My current method (slightly truncated for understanding):

  1. Procedure in MYSQL receiving and writing data

    CREATE DEFINER = 'root'@'localhost' PROCEDURE sp_dk_add_cat_material ( IN p_cat_id int, IN p_material_id int, IN p_modify_date datetime ) BEGIN DECLARE v_material_id int DEFAULT 0; select material_id INTO v_material_id FROM tbl_mg_category_material where tbl_mg_category_material.material_id=p_material_id; if (v_material_id=0) then insert into tbl_mg_category_material ( cat_id, material_id, modify_date ) values ( p_cat_id, p_material_id, p_modify_date ); ELSE update tbl_mg_category_material set cat_id=p_cat_id, material_id=p_material_id, modify_date=p_modify_date where material_id=p_material_id; END IF; END

  2. C # code that sends data to a MYSQL procedure

     public static void sp_dk_add_cat_materialMYSQL(int cat_id, int material_id, DateTime modify_date) { MySqlConnection _MYsqlconn = DK_MySQL_Conn.Connection; MySqlCommand _cmd_add_cat_material = new MySqlCommand(); _cmd_add_cat_material.Connection = _MYsqlconn; _cmd_add_cat_material.CommandType = CommandType.StoredProcedure; _cmd_add_cat_material.CommandText = "sp_dk_add_cat_material"; _cmd_add_cat_material.Parameters.Add("p_cat_id", MySqlDbType.Int32).Value = cat_id; _cmd_add_cat_material.Parameters.Add("p_material_id", MySqlDbType.VarChar).Value = material_id; _cmd_add_cat_material.Parameters.Add("p_modify_date", MySqlDbType.DateTime).Value = modify_date; try { _MYsqlconn.Open(); _cmd_add_cat_material.CommandTimeout = 200; _cmd_add_cat_material.ExecuteNonQuery(); _MYsqlconn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void Btn_insToMySQL_Click(object sender, EventArgs e) { BindingSource _bds, _bdscategory, _bdscatMaterials = new BindingSource(); _bds = _bdsMSSQLitems; _bdscategory = dk_Query.get_tbl_category(); _bdscatMaterials = dk_Query.get_category_material(); SplashScreenManager.ShowForm(typeof(WaitForm1)); SplashScreenManager.Default.SendCommand(WaitForm1.WaitFormCommand.SetProgressBarMaximum, _bds.Count+_bdscategory.Count+_bdscatMaterials.Count); System.Threading.Thread.Sleep(1000); int _splasPosition = 0; SplashScreenManager.Default.SendCommand(WaitForm1.WaitFormCommand.SetLabelText, "Kategoriýalar..."); for (int i=0;i<_bdscategory.Count;i++) { _splasPosition++; SplashScreenManager.Default.SendCommand(WaitForm1.WaitFormCommand.SetProgressBarPosition, _splasPosition); DataRowView drc = (DataRowView)_bdscategory[i]; int cat_id = 0; try { cat_id = Convert.ToInt32(drc["cat_id"]); } catch { } string cat_name = ""; try { cat_name = Convert.ToString(drc["cat_name"]); } catch { } string cat_desc = ""; try { cat_desc = Convert.ToString(drc["cat_desc"]); } catch { } string cat_image_path = ""; try { cat_image_path = Convert.ToString(drc["cat_image_path"]); } catch { } int published = 0; try { published = Convert.ToInt32(drc["published"]); } catch { } int cat_order = 0; try { cat_order = Convert.ToInt32(drc["cat_order"]); } catch { } dk_Query.sp_dk_add_categoryMYSQL(cat_id, cat_name, cat_desc, cat_image_path, published, cat_order); } for (int i = 0; i < _bdscatMaterials.Count; i++) { _splasPosition++; SplashScreenManager.Default.SendCommand(WaitForm1.WaitFormCommand.SetProgressBarPosition, _splasPosition); DataRowView drc = (DataRowView)_bdscatMaterials[i]; int cat_id = 0; try { cat_id = Convert.ToInt32(drc["cat_id"]); } catch { } int material_id = 0; try { material_id = Convert.ToInt32(drc["material_id"]); } catch { } DateTime modify_date =Convert.ToDateTime("01.01.1900 00:00"); try { modify_date = Convert.ToDateTime(drc["modify_date"]); } catch { } dk_Query.sp_dk_add_cat_materialMYSQL(cat_id, material_id, modify_date); } for (int i = 0; i < _bds.Count; i++) { _splasPosition++; SplashScreenManager.Default.SendCommand(WaitForm1.WaitFormCommand.SetProgressBarPosition, _splasPosition); DataRowView drc = (DataRowView)_bds[i]; int material_id = 0; try { material_id = Convert.ToInt32(drc["material_id"]); } catch { } if (material_id != 0) { DateTime modify_date = Convert.ToDateTime("01.01.1900 00:01"); try { modify_date = Convert.ToDateTime(drc["modify_date"]); } catch { } DateTime _logmodify_date = Convert.ToDateTime("01.01.1900 00:00"); try { _logmodify_date = Convert.ToDateTime(dk_Query.get_modify_date_by_matId(material_id)["modify_date"]); } catch { } if (modify_date > _logmodify_date) { string material_code = ""; try { material_code = Convert.ToString(drc["material_code"]); } catch { }; string material_name = ""; try { material_name = Convert.ToString(drc["material_name"]); } catch { } string m_cat_name = ""; try { m_cat_name = Convert.ToString(drc["m_cat_name"]); } catch { } string a_status_name = ""; try { a_status_name = Convert.ToString(drc["a_status_name"]); } catch { } int firm_id = 1; try { firm_id = Convert.ToInt32(drc["firm_id"]); } catch { } int m_cat_id = 0; try { m_cat_id = Convert.ToInt32(drc["m_cat_id"]); } catch { } int unit_id = 1; try { unit_id = Convert.ToInt32(drc["unit_id"]); } catch { } int a_status_id = 1; try { a_status_id = Convert.ToInt32(drc["a_status_id"]); } catch { } int mat_whousetotal_id = 0; try { mat_whousetotal_id = Convert.ToInt32(drc["mat_whousetotal_id"]); } catch { } float mat_whousetotal_amount = 0; try { mat_whousetotal_amount = Convert.ToSingle(drc["mat_whousetotal_amount"]); } catch { } float sale_price = 0; try { sale_price = Convert.ToSingle(drc["sale_price"]); } catch { } float mat_purch_price = 0; try { mat_purch_price = Convert.ToSingle(drc["mat_purch_price"]); } catch { } int wh_id = 1; try { wh_id = Convert.ToInt32(drc["wh_id"]); } catch { } int p_id = 0; try { p_id = Convert.ToInt32(drc["p_id"]); } catch { } int unit_det_id = 1; try { unit_det_id = Convert.ToInt32(drc["unit_det_id"]); } catch { } //int image_pict = 0; //try { image_pict = Convert.ToInt32(drc["image_pict"]); } //catch { } string unit_det_code = ""; try { unit_det_code = Convert.ToString(drc["unit_det_code"]); } catch { } string unit_det_name = ""; try { unit_det_name = Convert.ToString(drc["unit_det_name"]); } catch { } string unit_code = ""; try { unit_code = Convert.ToString(drc["unit_code"]); } catch { } float stock_max_level = 0; try { stock_max_level = Convert.ToSingle(drc["stock_max_level"]); } catch { } float stock_min_level = 0; try { stock_min_level = Convert.ToSingle(drc["stock_min_level"]); } catch { } float stock_worning_level = 0; try { stock_min_level = Convert.ToSingle(drc["stock_worning_level"]); } catch { } float unit_det_conv1 = 0; try { stock_min_level = Convert.ToSingle(drc["unit_det_conv1"]); } catch { } float unit_det_conv2 = 0; try { stock_min_level = Convert.ToSingle(drc["unit_det_conv2"]); } catch { } string spe_code = ""; try { spe_code = Convert.ToString(drc["spe_code"]); } catch { } string group_code = ""; try { group_code = Convert.ToString(drc["group_code"]); } catch { } string security_code = ""; try { security_code = Convert.ToString(drc["security_code"]); } catch { } string mat_last_trans_date = ""; try { mat_last_trans_date = Convert.ToString(drc["mat_last_trans_date"]); } catch { } string bar_barcode = ""; try { bar_barcode = Convert.ToString(drc["bar_barcode"]); } catch { } float mat_auto_price = 0; try { mat_auto_price = Convert.ToSingle(drc["mat_auto_price"]); } catch { } string spe_code1 = ""; try { spe_code1 = Convert.ToString(drc["spe_code1"]); } catch { } string spe_code2 = ""; try { spe_code2 = Convert.ToString(drc["spe_code2"]); } catch { } string spe_code3 = ""; try { spe_code3 = Convert.ToString(drc["spe_code3"]); } catch { } string spe_code4 = ""; try { spe_code4 = Convert.ToString(drc["spe_code4"]); } catch { } string spe_code5 = ""; try { spe_code5 = Convert.ToString(drc["spe_code5"]); } catch { } string spe_code6 = ""; try { spe_code6 = Convert.ToString(drc["spe_code6"]); } catch { } string spe_code7 = ""; try { spe_code7 = Convert.ToString(drc["spe_code7"]); } catch { } string spe_code8 = ""; try { spe_code8 = Convert.ToString(drc["spe_code8"]); } catch { } string spe_code9 = ""; try { spe_code9 = Convert.ToString(drc["spe_code9"]); } catch { } string spe_code10 = ""; try { spe_code10 = Convert.ToString(drc["spe_code10"]); } catch { } string mat_name_lang1 = ""; try { mat_name_lang1 = Convert.ToString(drc["mat_name_lang1"]); } catch { } string mat_name_lang2 = ""; try { mat_name_lang2 = Convert.ToString(drc["mat_name_lang2"]); } catch { } string mat_name_lang3 = ""; try { mat_name_lang3 = Convert.ToString(drc["mat_name_lang3"]); } catch { } string mat_name_lang4 = ""; try { mat_name_lang4 = Convert.ToString(drc["mat_name_lang4"]); } catch { } string mat_name_lang5 = ""; try { mat_name_lang5 = Convert.ToString(drc["mat_name_lang5"]); } catch { } int mat_auto_production = 0; try { mat_auto_production = Convert.ToInt32(drc["mat_auto_production"]); } catch { } float mat_whousetotal_amount_2 = 0; try { mat_whousetotal_amount_2 = Convert.ToSingle(drc["mat_whousetotal_amount_2"]); } catch { } float mat_tot_purch_avg_price = 0; try { mat_tot_purch_avg_price = Convert.ToSingle(drc["mat_tot_purch_avg_price"]); } catch { } float sale_total = 0; try { sale_total = Convert.ToSingle(drc["sale_total"]); } catch { } float purch_total = 0; try { purch_total = Convert.ToSingle(drc["purch_total"]); } catch { } SplashScreenManager.Default.SendCommand(WaitForm1.WaitFormCommand.SetLabelText, material_name); dk_Query.sp_dk_add_materialsMYSQL(material_id, material_code, material_name, m_cat_name, a_status_name, firm_id, m_cat_id, unit_id, a_status_id, mat_whousetotal_id, mat_whousetotal_amount , sale_price, mat_purch_price, wh_id, p_id, unit_det_id, unit_det_code , unit_det_name, unit_code, stock_max_level, stock_min_level, stock_worning_level , unit_det_conv1, unit_det_conv2, spe_code, group_code, security_code, mat_last_trans_date , bar_barcode, mat_auto_price, spe_code1, spe_code2, spe_code3, spe_code4, spe_code5, spe_code6 , spe_code7, spe_code8, spe_code9, spe_code10, mat_name_lang1, mat_name_lang2, mat_name_lang3 , mat_name_lang4, mat_name_lang5, mat_auto_production, mat_whousetotal_amount_2, mat_tot_purch_avg_price , sale_total, purch_total, modify_date); dk_Query.sp_dk_add_materialsMSSQL(material_id, material_code, material_name, m_cat_name, a_status_name, firm_id, m_cat_id, unit_id, a_status_id, mat_whousetotal_id, mat_whousetotal_amount , sale_price, mat_purch_price, wh_id, p_id, unit_det_id, unit_det_code , unit_det_name, unit_code, stock_max_level, stock_min_level, stock_worning_level , unit_det_conv1, unit_det_conv2, spe_code, group_code, security_code, mat_last_trans_date , bar_barcode, mat_auto_price, spe_code1, spe_code2, spe_code3, spe_code4, spe_code5, spe_code6 , spe_code7, spe_code8, spe_code9, spe_code10, mat_name_lang1, mat_name_lang2, mat_name_lang3 , mat_name_lang4, mat_name_lang5, mat_auto_production, mat_whousetotal_amount_2, mat_tot_purch_avg_price , sale_total, purch_total, modify_date); } } } SplashScreenManager.CloseForm(); }
  • one
    Where did you get MySQL from and what are you trying to do? - Pavel Mayorov
  • I am trying to transfer some tables from MSSQL to the database in MYSQL. MYSQL-ohm use other devices that are outside the local network (this is of course not important). It is necessary from one program which in MSSQL to throw data into MYSQL - Dowlpack
  • Do you have MySQL looking on the Internet? Hmm ... - Pavel Mayorov
  • Ok, not put it right. I think the essence of the question is clear. - Dowlpack

1 answer 1

If the task is to transfer data from one server to another, then in your case, you can simply configure the Linked server and do without C #.
Hyde

  • Thanks for the help. Did not know about this opportunity - Dowlpack