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