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 += " ";
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 "글이 삭제되었습니다";
}
}