멀티게시판 쿼리

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient; // 추가

/// <summary>

/// MultBoard의 요약 설명입니다.

/// </summary>

public class MultBoard

{

public MultBoard()

{ //

// TODO: 생성자 논리를 여기에 추가합니다.

//

}

/// <summary>

/// 게시물리스트에 사용하는 메서드 DataSet을 돌려준다

/// </summary>

/// <param name="keyword">검색하고자 하는 테이블필드</param>

/// <param name="value">SELECT Value</param>

/// <returns>DataSet of GridView</returns>

public static DataSet SelectContent(string keyword, string value)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string mainQry = string.Empty;

string opQry = string.Empty;

string orQry = string.Empty;

opQry = "WHERE " + keyword + " LIKE '%" + value + "%' ";

orQry = "ORDER BY refer DESC, step ASC ";

mainQry = "SELECT " +

 "BoardId, Writer, Title, Step, Depth, ReadCount, RegDate, delflag " +

 "FROM t_MultiBoard " + opQry + orQry;

SqlCommand cmd = new SqlCommand(mainQry, conn);

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapter.Fill(ds, "t_MultiBoard");

return ds;

}

/// <summary>

/// 게시물 상세보기 메서드

/// </summary>

/// <param name="boardId">게시물번호</param>

/// <returns>DataSet 객체</returns>

public static DataSet GetContent(string boardId)

{

SqlConnection conn =

  new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string sqlSelQry = "SELECT Title, Writer, RegDate, ReadCount, Content " +

  "FROM t_MultiBoard " +

  "WHERE BoardId= @boardID";

SqlCommand cmd = new SqlCommand(sqlSelQry, conn);

cmd.Parameters.Add("@boardID", SqlDbType.Int).Value = Convert.ToInt32(boardId);

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapter.Fill(ds, "t_MultiBoard");

return ds;

}

/// <summary>

/// 게시물의 조회수올리는 메서드

/// </summary>

/// <param name="boardId">게시물 번호</param>

public static void IncreaseReadCount(string boardId)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string strUpQry = "UPDATE t_MultiBoard SET " +

  "ReadCount = ReadCount + 1 " +

  "WHERE BoardId = @BoardId ";

SqlCommand cmd = new SqlCommand(strUpQry, conn);

cmd.Parameters.Add("@BoardID", SqlDbType.Int).Value = Convert.ToInt32(boardId);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

/// <summary>

/// 작성자와 동일인인가를 확인하는 메서드

/// </summary>

/// <param name="boardId">게시물번호</param>

/// <param name="userId">사용자아이디</param>

/// <returns>true , false</returns>

public static bool IsWriter(string boardId, string userId)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string strCntQry = "SELECT COUNT(*) AS cnt " +

  "FROM t_MultiBoard " +

  "WHERE BoardID = @BoardID " +

  "AND Writer = @Writer ";

SqlCommand cmd = new SqlCommand(strCntQry, conn);

cmd.Parameters.Add("@BoardID", SqlDbType.Int).Value = Convert.ToInt32(boardId);

cmd.Parameters.Add("@Writer", SqlDbType.NVarChar).Value = userId;

conn.Open();

SqlDataReader read = cmd.ExecuteReader();

int ICnt = 0;

if (read.Read())

ICnt = Convert.ToInt32(read["cnt"]);

read.Close();

conn.Close();

if (ICnt >= 1)

return true;

else

return false;

}

/// <summary>

/// 게시물 작성 메서드

/// </summary>

/// <param name="writer">작성자 ID</param>

/// <param name="title">글제목</param>

/// <param name="content">내용</param>

public static void InsertContent(string writer, string title, string content)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string strInsQry = "INSERT INTO t_MultiBoard " +

  "(Writer, Title, Content, Refer, Step, Depth, ReadCount, DelFlag, RegDate) " +

  "VALUES " +

  "(@Writer, @Title, @Content, " +

  getNewRef() +

  ", 0, 0, 0 , 'N', GetDate()) ";

SqlCommand cmd = new SqlCommand(strInsQry, conn);

cmd.Parameters.Add("@Writer", SqlDbType.NVarChar).Value = writer;

cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = title;

cmd.Parameters.Add("@Content", SqlDbType.NText).Value = content;

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

private static string getNewRef()

{

string strVal = string.Empty;

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string strSelQry = "SELECT ISNULL(MAX(refer), 0)  + 1 as Maxref " + "FROM t_multiBoard ";

SqlCommand cmd = new SqlCommand(strSelQry, conn);

conn.Open();

SqlDataReader read = cmd.ExecuteReader();

if (read.Read())

{

strVal = read["Maxref"].ToString();

}

read.Close();

conn.Close();

return strVal;

}

/// <summary>

/// 게시물 수정 메서드

/// </summary>

/// <param name="boardId">게시물번호</param>

/// <param name="Writer">작성자아이디</param>

/// <param name="content">내용</param>

public static void UpdateContent(string boardId, string title, string content)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

string strUpQry = "UPDATE t_Multiboard SET " +

 "Title = @title, " +

 "Content = @content " +

 "WHERE boardId = @boardId ";

SqlCommand cmd = new SqlCommand(strUpQry, conn);

cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = title;

cmd.Parameters.Add("@content", SqlDbType.NText).Value = content;

cmd.Parameters.Add("@boardId", SqlDbType.Int).Value = Convert.ToInt32(boardId);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

/// <summary>

/// 답변게시물 삭제 메서드

/// </summary>

/// <param name="boardId">게시물 번호</param>

/// // 하위 게시물들은 refer가 똑같다

public static void DeleteContent(string boardId)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

// refer, step depth를 다 읽어온다

string strSelQry = "SELECT refer, step, depth " +

  "FROM t_multiboard " +

  "WHERE boardID = @boardID ";

SqlCommand cmd = new SqlCommand(strSelQry, conn);

cmd.Parameters.Add("@BoardId", SqlDbType.Int).Value = Convert.ToInt32(boardId);

conn.Open();

int refer = 0;

int step = 0;

int depth = 0;

SqlDataReader read = cmd.ExecuteReader();

if (read.Read())

{

refer = Convert.ToInt32(read["refer"]);

step = Convert.ToInt32(read["step"]);

depth = Convert.ToInt32(read["depth"]);

}

read.Close();

//내글을 삭제

string strUpQry = "UPDATE t_MultiBoard SET "

 + "DelFlag = 'Y' "

 + "WHERE Refer = @Refer "

 + "AND Step BETWEEN @Step AND "

 + " ( "

 + "    SELECT "

 + "        CASE "

 + "            WHEN @Step = 0  THEN (SELECT MAX(Step) FROM t_MultiBoard) "

 + "            WHEN @Step > 0  THEN ISNULL((SELECT MIN(step)-1 FROM t_MultiBoard WHERE Depth <= @Depth AND Step > @Step), (SELECT MAX(Step) FROM t_MultiBoard)) "

 + "        END "

 + "    FROM t_MultiBoard "

 + "    WHERE BoardId = @BoardId "

 + ") "

 + "OR BoardId = @BoardId ";

cmd.CommandText = strUpQry;

cmd.Parameters.Clear();

cmd.Parameters.Add("@Refer", SqlDbType.Int).Value = refer;

cmd.Parameters.Add("@Step", SqlDbType.Int).Value = step;

cmd.Parameters.Add("@Depth", SqlDbType.Int).Value = depth;

cmd.Parameters.Add("@BoardId", SqlDbType.Int).Value = boardId;

cmd.ExecuteNonQuery();

////내글을 삭제

//string strUpQry = "UPDATE t_multiboard SET " +

//                  "delflag ='Y' " +

//                  "WHERE boardID = @boardID ";

//cmd.CommandText = strUpQry;

//cmd.Parameters.Clear();

//cmd.Parameters.Add("@boardID",SqlDbType.Int).Value = Convert.ToInt32(boardId);

//cmd.ExecuteNonQuery();

////내 하위의 글들 삭제

//strUpQry = "UPDATE t_multiboard SET " +

//           "delflag = 'Y' " +

//           "WHERE refer = @refer " +

//           "AND step > @step " +

//           "AND depth > @depth ";

//cmd.CommandText= strUpQry;

//cmd.Parameters.Clear();

//cmd.Parameters.Add("@refer", SqlDbType.Int).Value = refer;

//cmd.Parameters.Add("@step", SqlDbType.Int).Value = step;

//cmd.Parameters.Add("@depth", SqlDbType.Int).Value = depth;

//cmd.ExecuteNonQuery();

conn.Close();

}

/// <summary>

/// 게시물 답변 메서드

/// </summary>

/// <param name="boardId">원본게시물번호</param>

/// <param name="writer">작성자</param>

/// <param name="title">제목</param>

/// <param name="content">내용</param>

public static void ReplyContent(string boardId, string writer, string title, string content)

{

SqlConnection conn =

new SqlConnection(ConfigurationManager.ConnectionStrings["DBconStr"].ConnectionString);

int refer = 0;

int step = 0;

int depth = 0;

// refer, step depth를 다 읽어온다

string strSelQry = "SELECT refer, step, depth " +

  "FROM t_multiboard " +

  "WHERE boardID = @boardID ";

SqlCommand cmd = new SqlCommand(strSelQry, conn);

cmd.Parameters.Add("@BoardId", SqlDbType.Int).Value = Convert.ToInt32(boardId);

conn.Open();

SqlDataReader read = cmd.ExecuteReader();

if (read.Read())

{

refer = Convert.ToInt32(read["refer"]);

step = Convert.ToInt32(read["step"]);

depth = Convert.ToInt32(read["depth"]);

}

read.Close();

//동일한 refer를 가지는 게시물중 

//원본게시물 step보타 큰 step을 가지는 애들은 step을 1씩 증가시킨다

string strUpQry = "UPDATE t_multiBoard SET " +

 "step = step + 1 " +

 "WHERE Refer = @refer " +

 "AND Step > @step ";

cmd.CommandText = strUpQry;

cmd.Parameters.Clear();

cmd.Parameters.Add("@refer", SqlDbType.Int).Value = refer;

cmd.Parameters.Add("@step", SqlDbType.Int).Value = step;

cmd.ExecuteNonQuery();

// 답변게시물저장

string strInsQry = "INSERT INTO t_multiBoard " +

  "(writer, Title, content , refer, step, depth, ReadCount, delflag, regdate) " +

  "VALUES " +

  "(@writer, @title, @content, @refer, @step, @depth, 0, 'N', GetDate()) ";

cmd.CommandText = strInsQry;

cmd.Parameters.Clear();

cmd.Parameters.Add("@writer", SqlDbType.NVarChar).Value = writer;

cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = title;

cmd.Parameters.Add("@content", SqlDbType.NText).Value = content;

cmd.Parameters.Add("@refer", SqlDbType.Int).Value = refer;

cmd.Parameters.Add("@step", SqlDbType.Int).Value = (step + 1);

cmd.Parameters.Add("@depth", SqlDbType.Int).Value = (depth + 1);

cmd.ExecuteNonQuery();

conn.Close();

}

public static bool IsReply(string step)

{

if (step != "0") return true;

else return false;

}

public static string GetDepth(int depth)

{

string r = string.Empty;

for (int i = 0; i < depth; i++)

r += "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";

return r;

}

public static string GenTitle(string boardId, string title, string delFlag)

{

if (delFlag == "N")

return "<a href='MultiBoard_View.aspx?BoardID=" + boardId + "'>" + title + "</a>";

else

return "글이 삭제되었습니다";

}

}