//노트북리스트 불러오기 : /index
/*
* 목적 : 노트북 리스트 보여주기
* 준비물 : 노트북리스트, db커넥션
* (1) 빈 노트북 리스트를 만든다.
* (2) db커넥션을 생성하여 notebook을 모두 불러옴
* (3) 빈 노트북리스트에 (2)에서 받아온 notebook을 넣음.
*/
public static List <NoteBookVO> GetNoteBookList()
{
List <NoteBookVO> noteBooks = new List <NoteBookVO>();
OracleConnection conn = DbHelper.NewConnection();
conn.Open();
String sql = "select * from NoteBook";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NoteBookVO noteBook = new NoteBookVO();
noteBook.NoteBookId = int.Parse(reader["NOTEBOOKID"].ToString());
noteBook.Name = reader["NAME"].ToString();
noteBooks.Add(noteBook);
}
reader.Close();
conn.Close();
return(noteBooks);
}
// 아이디로 노트북 불러오기
/*
* 목적 : 노트북을 불러옴
* 준비물 : 빈 노트북, 노트북아이디, db커넥션
* (1) 빈 노트북 생성
* (2) db커넥션을 생성하여 해당 노트북아이디를 가진 노트북 부름
* (3) 빈 노트북의 해당 노트북 정보 넣음
*/
public static NoteBookVO GetNoteBookbyId(int noteBookId)
{
NoteBookVO noteBook = new NoteBookVO();
OracleConnection conn = DbHelper.NewConnection();
conn.Open();
String sql = $"select * from NoteBook where noteBookId = {noteBookId}";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
noteBook.NoteBookId = int.Parse(reader["NOTEBOOKID"].ToString());
noteBook.Name = reader["NAME"].ToString();
noteBook.IsDefault = int.Parse(reader["ISDEFAULT"].ToString());
}
reader.Close();
conn.Close();
return(noteBook);
}
public static Dictionary <int, object> GetShortcuts()
{
Dictionary <int, object> shortcutDic = new Dictionary <int, object>();
//키값으론 order를 넣는다. 리스트에는 Note or Notebook을 넣고 각각 id, title을 넣는다.
//shortcut에서 orderby order로 모든 데이터를 긁어온 후 order를 키값으로 넣고 type과 id를 해당 value 넣는다.
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
String sql = "SELECT * FROM shortcut ORDER BY ORDERS ASC";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int order = int.Parse(reader["orders"].ToString());
int noteid = 0;
int notebookid = 0;
if (reader["noteid"] != DBNull.Value)
{
noteid = int.Parse(reader["noteid"].ToString());
}
else if (reader["notebookid"] != DBNull.Value)
{
notebookid = int.Parse(reader["notebookid"].ToString());
}
if (noteid != 0) //노트일경우
{
NoteVO newNote = new NoteVO()
{
NoteId = noteid,
Title = NoteDAO.GetNotebyId(noteid).Title
};
shortcutDic.Add(order, newNote);
}
else if (notebookid != 0) //노트북일경우
{
NoteBookVO newNoteBook = new NoteBookVO()
{
NoteBookId = notebookid,
Name = NoteBookDAO.GetNoteBookbyId(notebookid).Name
};
shortcutDic.Add(order, newNoteBook);
}
}
reader.Close();
return(shortcutDic);
}
}
//노트북 이나 노트가 0이 아닌 것을 찾아서 title을 따온다.
public static List <object> GetShorcutList()
{
List <object> shortcuts = new List <object>();
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
String noteSql = "SELECT NOTE.NOTEID, NOTE.TITLE, SHORTCUT.ORDERS, isdeleted FROM NOTE, SHORTCUT WHERE NOTE.NOTEID = SHORTCUT.NOTEID AND ISDELETED = 0";
String noteBookSql = "SELECT NOTEBOOK.NOTEBOOKID, NOTEBOOK.NAME, SHORTCUT.ORDERS FROM NOTEBOOK, SHORTCUT WHERE NOTEBOOK.NOTEBOOKID = SHORTCUT.NOTEBOOKID";
string all = "";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = noteSql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NoteVO note = new NoteVO();
note.Title = reader["title"].ToString();
note.NoteId = int.Parse(reader["noteid"].ToString());
int index = int.Parse(reader["orders"].ToString()) - 1;
shortcuts.Add(note);
}
reader.Close();
OracleCommand noteBookCmd = new OracleCommand
{
Connection = conn,
CommandText = noteBookSql
};
OracleDataReader noteBookReader = noteBookCmd.ExecuteReader();
while (noteBookReader.Read())
{
NoteBookVO notebook = new NoteBookVO();
notebook.Name = noteBookReader["name"].ToString();
notebook.NoteBookId = int.Parse(noteBookReader["notebookid"].ToString());
int index = int.Parse(noteBookReader["orders"].ToString()) - 1;
shortcuts.Add(notebook);
}
noteBookReader.Close();
return(shortcuts);
}
}