/// <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);
}
}