public static void updateLookBook(LookBookModel LB) { try { // ssh 접속 using (var client = new SshClient("106.10.57.242", 5000, "root", "qawzsx351")) { client.Connect(); if (client.IsConnected) { try { // 내부 db 접속을 위한 포트포워딩 var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306); client.AddForwardedPort(portForwarded); portForwarded.Start(); // db 접속 using (MySqlConnection con = new MySqlConnection("SERVER=localhost;PORT=3306;UID=root;PASSWORD=qawzsx351;DATABASE=codibook;SslMode=None")) { con.Open(); string query1 = "UPDATE LookBook SET NAME='" + LB.Name + "' WHERE LOOKBOOK_ID=" + LB.IDX; MySqlCommand sqlCom = new MySqlCommand(query1, con); sqlCom.ExecuteNonQuery(); query1 = "DELETE FROM LookBook_item WHERE LOOKBOOK_ID=" + LB.IDX; sqlCom = new MySqlCommand(query1, con); sqlCom.ExecuteNonQuery(); for (int i = 0; i < LB.ItemList.Count(); i++) { query1 = "INSERT INTO LookBook_item (ITEM_ID, LOOKBOOK_ID) VALUES (" + LB.ItemList[i].Item_ID + "," + LB.IDX + ")"; sqlCom = new MySqlCommand(query1, con); sqlCom.ExecuteNonQuery(); } con.Close(); client.Disconnect(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { MessageBox.Show("Client cannot be reached..."); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void addItem(LookBookModel L) { lookBookItems.Add(L); }
// 단일 룩북데이터 db에 저장후 LookBook_ID 받아오기 public static LookBookModel setLookBook(User user, LookBookModel LB) { try { // ssh 접속 using (var client = new SshClient("106.10.57.242", 5000, "root", "qawzsx351")) { client.Connect(); if (client.IsConnected) { try { // 내부 db 접속을 위한 포트포워딩 var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306); client.AddForwardedPort(portForwarded); portForwarded.Start(); // db 접속 using (MySqlConnection con = new MySqlConnection("SERVER=localhost;PORT=3306;UID=root;PASSWORD=qawzsx351;DATABASE=codibook;SslMode=None")) { con.Open(); // 항목 중복체크 string query1 = "SELECT COUNT(*) FROM LookBook WHERE USER_ID=" + user.User_ID + " AND NAME='" + LB.Name + "';"; MySqlCommand sqlCom = new MySqlCommand(query1, con); if (int.Parse(sqlCom.ExecuteScalar().ToString()) != 0) { MessageBox.Show("이미 같은 이름의 항목이 존재합니다!"); return(LB); } // db에 데이터 INSERT query1 = "INSERT INTO LookBook (NAME, USER_ID) VALUES ('" + LB.Name + "'," + user.User_ID + ");"; sqlCom = new MySqlCommand(query1, con); sqlCom.ExecuteNonQuery(); // IDX 받아오기, LookBook_item 테이블에 item id 넣기 query1 = "SELECT * FROM LookBook WHERE USER_ID=" + user.User_ID + " AND NAME='" + LB.Name + "';"; sqlCom = new MySqlCommand(query1, con); MySqlDataReader reader = sqlCom.ExecuteReader(); while (reader.Read()) { LB.IDX = (int)reader["LOOKBOOK_ID"]; } reader.Close(); // LookBook_item 테이블에 데이터 저장 for (int i = 0; i < LB.ItemList.Count; i++) { string query2 = "INSERT INTO LookBook_item (ITEM_ID, LOOKBOOK_ID) VALUES (" + LB.ItemList[i].Item_ID + "," + LB.IDX + ");"; MySqlCommand sqlCom2 = new MySqlCommand(query2, con); sqlCom2.ExecuteNonQuery(); } con.Close(); client.Disconnect(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { MessageBox.Show("Client cannot be reached..."); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } return(LB); }
// 단일 룩북 데이터 받아오기 public static LookBookModel getLookBook(int lookbookID) { LookBookModel result = null; try { // ssh 접속 using (var client = new SshClient("106.10.57.242", 5000, "root", "qawzsx351")) { client.Connect(); if (client.IsConnected) { try { // 내부 db 접속을 위한 포트포워딩 var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306); client.AddForwardedPort(portForwarded); portForwarded.Start(); // db 접속 using (MySqlConnection con = new MySqlConnection("SERVER=localhost;PORT=3306;UID=root;PASSWORD=qawzsx351;DATABASE=codibook;SslMode=None")) { con.Open(); string query1 = "SELECT * FROM LookBook WHERE LOOKBOOK_ID=" + lookbookID + ";"; MySqlCommand sqlCom1 = new MySqlCommand(query1, con); MySqlDataReader reader1 = sqlCom1.ExecuteReader(); while (reader1.Read()) { result = new LookBookModel(lookbookID, reader1["NAME"] as string); } reader1.Close(); ObservableCollection <ItemModel> list = new ObservableCollection <ItemModel>(); string query2 = "SELECT * FROM LookBook_item WHERE LOOKBOOK_ID=" + lookbookID + ";"; MySqlCommand sqlCom2 = new MySqlCommand(query2, con); MySqlDataReader reader2 = sqlCom2.ExecuteReader(); while (reader2.Read()) { list.Add(getItem((int)reader2["ITEM_ID"])); } reader2.Close(); result.ItemList = list; con.Close(); client.Disconnect(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { MessageBox.Show("Client cannot be reached..."); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } return(result); }