/// <summary>
/// 노트아이디로 태그리스트 찾음
/// </summary>
/// <param name="noteId"></param>
/// <returns></returns>
public static List <TagVO> GetTagListByNote(int noteId)
{
List <TagVO> tagList = new List <TagVO>();
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
//중복검사
String sql = $"select tag_id from note_tag_map where note_id = :noteId";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleParameter paramnoteId = cmd.Parameters.Add("noteId", OracleDbType.Varchar2);
paramnoteId.Value = noteId;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //
{
TagVO tag = new TagVO();
tag.Tag_Id = int.Parse(reader["tag_id"].ToString());
tag.Tag_Name = getTagNameByTagId(tag.Tag_Id);
tagList.Add(tag);
}
return(tagList);
}
}
/// <summary>
/// 태그 리스트 불러오기
/// </summary>
/// <returns>List<Tag></returns>
public static List <TagVO> GetTagList()
{
List <TagVO> TagList = new List <TagVO>();
OracleConnection conn = DbHelper.NewConnection();
conn.Open();
String sql = "select * from Tag";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TagVO tag = new TagVO();
tag.Tag_Id = int.Parse(reader["TAG_ID"].ToString());
tag.Tag_Name = reader["TAG_NAME"].ToString();
TagList.Add(tag);
}
reader.Close();
conn.Close();
return(TagList);
}
public static void AddTagToNote(string tagName, int noteId)
{
TagVO newTag = new TagVO();
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
String checkSql = "select * from tag where tag_name like :CheckName";
OracleCommand checkCmd = new OracleCommand
{
Connection = conn,
CommandText = checkSql
};
OracleParameter CheckName = checkCmd.Parameters.Add("CheckName", OracleDbType.Varchar2);
CheckName.Value = tagName;
OracleDataReader reader = checkCmd.ExecuteReader();
if (reader.HasRows) //중복 태그가 있는 경우
{
newTag.Tag_Id = int.Parse(reader["TAG_ID"].ToString());
reader.Close();
}
else // 중복 태그가 없는 경우
{
String sql = $"INSERT INTO TAG (TAG_ID, TAG_NAME) VALUES (:TAGID, :NAME)";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleParameter TagId = cmd.Parameters.Add("TAGID", OracleDbType.Int32);
TagId.Value = GetNewTagId();
OracleParameter Name = cmd.Parameters.Add("Name", OracleDbType.Varchar2);
Name.Value = tagName;
cmd.ExecuteNonQuery();
newTag.Tag_Id = int.Parse(TagId.ToString());
}
//note_tag_map 테이블에 관계 추가
String mapSql = $"INSERT INTO NOTE_TAG_MAP (NOTE_ID, TAG_ID ) VALUES ({noteId},{newTag.Tag_Id})";
OracleCommand mapCmd = new OracleCommand
{
Connection = conn,
CommandText = mapSql
};
mapCmd.ExecuteNonQuery();
conn.Close();
}
}
