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