/// <summary> /// DB에 브릭 데이터 넣기 /// </summary> private void insert_DB() { if (this.cb_Type.SelectedItem == null || this.tb_Name.TextLength == 0 || this.tb_Seq.TextLength == 0) { MessageBox.Show("Error.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { string str_qu = " insert into userbricks values (@NAME, @TYPE, @DESC, @SEQ) "; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", tb_Name.Text); conm.Parameters.AddWithValue("@TYPE", cb_Type.SelectedItem.ToString()); conm.Parameters.AddWithValue("@DESC", tb_Desc.Text); conm.Parameters.AddWithValue("@SEQ", tb_Seq.Text.Replace("\r\n", "").Replace(" ", "")); if (connector.insert_Data(conm)) { MessageBox.Show("Complete.", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information); lv_From_DB(); } else { MessageBox.Show("Fail.", "Fail", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
private void bt_delete_Click(object sender, EventArgs e) { if (lv_All.SelectedItems.Count > 0) { if (MessageBox.Show(lv_All.SelectedItems.Count + "개의 데이터를 삭제하시겠습니까?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { foreach (ListViewItem lvitem in lv_All.SelectedItems) { if (lvitem.Group.Name == "Registered Brick") { string str_qu = "select part_id, part_name, part_short_desc, part_type from part where part_name = '" + lvitem.SubItems[0].Text + "'"; OleDbDataReader oddr = connector.select_Data(str_qu); if (oddr.Read()) { string str_id = oddr.GetInt32(0).ToString(); string str_name = oddr.GetString(1); string str_desc = oddr.GetString(2); string str_type = oddr.GetString(3); str_qu = "delete from part where part_name = '" + lvitem.SubItems[0].Text + "'"; connector.insert_Data(str_qu); str_qu = "insert into part (part_id, part_name, part_short_desc, part_type) values(@ID, @NAME, @DESC, @TYPE)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_id); conm.Parameters.AddWithValue("@NAME", str_name); conm.Parameters.AddWithValue("@DESC", str_desc); conm.Parameters.AddWithValue("@TYPE", str_type); connector.insert_Data(conm); conm.Dispose(); } } else if (lvitem.Group.Name == "User's Brick") { string str_qu = "delete from userbricks where part_name = '" + lvitem.SubItems[0].Text + "'"; connector.insert_Data(str_qu); } } list_from_DB(); } } }
private void BackgroundWorker3_DoWork(object sender, DoWorkEventArgs e) { // XML 에서 다운받기 string str_current_id = null; try { for (int i = 0; i < list_brick.Count; i++) { int j = 1, d = 1, ss = 1; //string str_current_id = null; try { XmlNodeList xnList; XmlDocument xDoc = new XmlDocument(); xnList = null; xDoc.Load("http://partsregistry.org/cgi/xml/part.cgi?part=" + list_brick[i]); // 1 xnList = xDoc.SelectNodes("/rsbpml/part_list/part"); foreach (XmlNode xn in xnList) { str_current_id = xn["part_id"].InnerText; string str_qu = "UPDATE part SET part_short_desc = @DESC, part_short_name = @SNAME, part_status = @STATUS, part_result = @RESULT , part_nickname = @NNAME, part_rating = @RATING, part_url = @URL, part_entered = @ENTERED, part_author = @AUTHOR, best_quality = @QUALITY WHERE part_name = '" + list_brick[i] + "'"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@DESC", xn["part_short_desc"].InnerText); conm.Parameters.AddWithValue("@SNAME", xn["part_short_name"].InnerText); conm.Parameters.AddWithValue("@STATUS", xn["part_status"].InnerText); conm.Parameters.AddWithValue("@RESULT", xn["part_results"].InnerText); conm.Parameters.AddWithValue("@NNAME", xn["part_nickname"].InnerText); if (xn["part_rating"].InnerText.Length != 0) { conm.Parameters.AddWithValue("@RATING", xn["part_rating"].InnerText); } else { conm.Parameters.AddWithValue("@RATING", DBNull.Value); } conm.Parameters.AddWithValue("@URL", xn["part_url"].InnerText); conm.Parameters.AddWithValue("@ENTERED", xn["part_entered"].InnerText); conm.Parameters.AddWithValue("@AUTHOR", xn["part_author"].InnerText); conm.Parameters.AddWithValue("@QUALITY", xn["best_quality"].InnerText); connector.update_Data(conm); conm.Dispose(); } //2 xnList = xDoc.SelectNodes("/rsbpml/part_list/part/deep_subparts/subpart"); foreach (XmlNode xn in xnList) { string str_qu = "insert into deep_subparts (part_id, subpart_id, part_sub_num) values (@ID, @SID, @PSN)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@SID", xn["part_id"].InnerText); conm.Parameters.AddWithValue("@PSN", d); connector.insert_Data(conm); conm.Dispose(); d++; } xnList = xDoc.SelectNodes("/rsbpml/part_list/part/specified_subparts/subpart"); foreach (XmlNode xn in xnList) { string str_qu = "insert into specified_subparts (part_id, subpart_id, part_sub_num ) values (@ID, @SID, @PSN)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@SID", xn["part_id"].InnerText); conm.Parameters.AddWithValue("@PSN", ss); connector.insert_Data(conm); conm.Dispose(); ss++; } xnList = xDoc.SelectNodes("/rsbpml/part_list/part/specified_subscars/scar"); foreach (XmlNode xn in xnList) { string str_qu = "insert into specified_subscars (part_id, part_scar_num , scar_id, scar_type, scar_name, scar_nickname, scar_comments, scar_seq) values (@ID, @PSN, @SID, @STYPE, @SNAME, @SNNAME, @SCOM, @SSEQ)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@PSN", j); conm.Parameters.AddWithValue("@SID", xn["scar_id"].InnerText); conm.Parameters.AddWithValue("@STYPE", xn["scar_type"].InnerText); conm.Parameters.AddWithValue("@SNAME", xn["scar_name"].InnerText); conm.Parameters.AddWithValue("@SNNAME", xn["scar_nickname"].InnerText); conm.Parameters.AddWithValue("@SCOM", xn["scar_comments"].InnerText); conm.Parameters.AddWithValue("@SSEQ", xn["scar_sequence"].InnerText); connector.insert_Data(conm); conm.Dispose(); j++; } xnList = xDoc.SelectNodes("/rsbpml/part_list/part/sequences"); foreach (XmlNode xn in xnList) { string str_qu = "insert into sequences (part_id, seq_data) values (@ID, @SEQ)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@SEQ", xn["seq_data"].InnerText.Replace("\n", "")); connector.insert_Data(conm); conm.Dispose(); } xnList = xDoc.SelectNodes("/rsbpml/part_list/part/features/feature"); foreach (XmlNode xn in xnList) { string str_qu = "insert into features (part_id, feature_id, feature_title, feature_type, feature_direction, feature_startpos, feature_endpos) values (@ID, @FID, @FTITLE, @FTYPE, @FDIRECTION, @FSTART, @FEND)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@FID", xn["id"].InnerText); conm.Parameters.AddWithValue("@FTITLE", xn["title"].InnerText); conm.Parameters.AddWithValue("@FTYPE", xn["type"].InnerText); conm.Parameters.AddWithValue("@FDIRECTION", xn["direction"].InnerText); conm.Parameters.AddWithValue("@FSTART", xn["startpos"].InnerText); conm.Parameters.AddWithValue("@FEND", xn["endpos"].InnerText); connector.insert_Data(conm); conm.Dispose(); } xnList = xDoc.SelectNodes("/rsbpml/part_list/part/parameters/parameter"); foreach (XmlNode xn in xnList) { string str_qu = "insert into parameterz (part_id, par_name, par_value, par_units, par_url, par_id, par_m_data, par_user_id, par_user_name ) values (@ID, @PNAME, @PV, @PUNITS, @PURL, @PID, @PM, @PUI, @PUN)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@PNAME", xn["name"].InnerText); conm.Parameters.AddWithValue("@PV", xn["value"].InnerText); conm.Parameters.AddWithValue("@PUNITS", xn["units"].InnerText); conm.Parameters.AddWithValue("@PURL", xn["url"].InnerText); conm.Parameters.AddWithValue("@PID", xn["id"].InnerText); conm.Parameters.AddWithValue("@PM", xn["m_date"].InnerText); conm.Parameters.AddWithValue("@PUI", xn["user_id"].InnerText); conm.Parameters.AddWithValue("@PUN", xn["user_name"].InnerText); connector.insert_Data(conm); conm.Dispose(); } xnList = xDoc.SelectNodes("/rsbpml/part_list/part/categories/category"); foreach (XmlNode xn in xnList) { string str_qu = "insert into categories (part_id, category) values (@ID, @CA)"; OleDbCommand conm = new OleDbCommand(str_qu); conm.Parameters.AddWithValue("@ID", str_current_id); conm.Parameters.AddWithValue("@CA", xn.InnerText); connector.insert_Data(conm); conm.Dispose(); } } // e t catch (System.Exception ex) { Debug.WriteLine(ex.Message); } bgw3.ReportProgress(i); } // end for } // end try catch (System.Exception ex) { Debug.WriteLine(ex.Message); } }