DB 생성.
SQL 파일 첨부
ClickDB.sql
################################################################################################################
- GridView 를 사용하지 않는 일반적인 테이블 구조입니다.
* App_Code 의
DBConn.cs
using System.Data.SqlClient;
/// <summary>
/// DBConn의 요약 설명입니다.
/// </summary>
public class DBConn
{
//1. SQL 서버 이름지정
string source = @"Data Source=PC\SQLEXPRESS;Initial Catalog=ClickDB;User ID=sa;Password=XXXXXXXXX";
SqlConnection conn;//2. 데이터 베이스 열기
public void Open()
{
conn = new SqlConnection(source);
conn.Open();
}//3. 데이터 베이스 닫기
public void Close()
{
conn.Close();
}//4. SQL 문 실행
public void ExecuteSQL(string sql)
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
//5. SQL 문을 실행하고, SqlDataReader 객체를 리턴합니다.
public SqlDataReader ExecuteReader(string sql)
{
SqlCommand cmd = new SqlCommand(sql, conn);
return cmd.ExecuteReader();
}
//6.SQL 문을 실행하고, DataSet 객체를 리턴합니다.
public DataSet GetDataSet(string sql)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sql, conn);DataSet ds = new DataSet();
adapter.Fill(ds);return ds;
}public DBConn()
{
//
// TODO: 여기에 생성자 논리를 추가합니다.
//
}
}
Default.aspx.cs
###################################################################################################################using System.Data.SqlClient;
using System.Data.OleDb;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ShowList();
}public void ShowList()
{
//1.데이터베이스 열기
DBConn conn = new DBConn();
conn.Open();//2.SQL 문 실행
//OleDbDataReader reader = conn.ExecuteReader("select * from board");
SqlDataReader reader = conn.ExecuteReader("select * from board");
//실행결과 보여주기
Response.Write("<table border=1>");
while (reader.Read())
{
Response.Write("<tr>");
Response.Write("<td>" + reader["id"]+"</td>");
Response.Write("<td>" + reader["title"]+"</td>");
Response.Write("<td>" + reader["dtime"] + "</td>");
Response.Write("<td>" + reader["name"] + "</td>");
Response.Write("<td>" + reader["hit"] + "</td>");
Response.Write("</tr>");
}
Response.Write("</table>");//4.데이터 베이스 닫기
conn.Close();
}
}
GridView 를 이용한게시판
List.aspx
<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False" BackColor="#CC33FF" BorderColor="Blue"
BorderStyle="Solid" PageSize="5">
<Columns>
<asp:BoundField DataField="id" HeaderText="NO">
<HeaderStyle Width="25px" />
</asp:BoundField>
<asp:TemplateField>
<ItemTemplate>
<img src="images\note.jpg" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="제목">
<HeaderStyle Width="300px" />
<ItemTemplate>
<%# GetReplyIcon(Eval("gr_depth")) %>
<asp:HyperLink ID="Hyperlink2" runat="server" NavigateUrl='<%# GetShowUrl(Eval("id")) %>' Text='<%# Eval("title") %>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="name" HeaderText="이름" >
<HeaderStyle Width="50px"></HeaderStyle>
</asp:BoundField >
<asp:BoundField DataField="dtime" HeaderText="올린날짜">
<HeaderStyle Width="80px"></HeaderStyle>
</asp:BoundField>
<asp:BoundField DataField="hit" HeaderText="조회">
</asp:BoundField>
</Columns>
<PagerStyle HorizontalAlign="Center" />
<AlternatingRowStyle BackColor="#99FFCC" />
</asp:GridView>
<asp:ImageButton ID="btnWrite" runat="server" ImageUrl="Images/write.gif" />
<br />
</div>
* 코드 설명
1. GetReplyIcon()
답글을 올렸을 때 글이 조금씩 들어가 보이도록 하기 위해 Depth 정보에 따라 공백 문자를 추가하고 아이콘
이미지를 넣어줍니다.
2. GetShowUrl()
제목 링크를 클릭했을때, 선택한 글을 보여줄 수 있도록 URL을 리턴합니다.
예) NavigateUrl='show.asp?id=100'
List.aspx.cs
public partial class List : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//1.데이터 베이스 열기
DBConn conn = new DBConn();
conn.Open();//2.데이터셋 구하기
DataSet ds = conn.GetDataSet("select * from Board order by gr_id desc, gr_pos asc");//3.DataSource 지정
GridView1.DataSource = ds.Tables[0].DefaultView;//4.페이지 번호에 따라 이동, 페이지 번호가 없으면 첫 번째 페이지로 이동
GridView1.PageIndex = (Session["page"] == null) ? 0 : (int)Session["Page"];
GridView1.DataBind();//5.데이터베이스 닫기
conn.Close();
}
public string GetReplyIcon(object obj)
{
int depth = (int)obj;
if (depth == 0) return "";string shtml = "";
for (int i = 0; i < depth; i++)
shtml += " ";shtml += "<img src='images\\re.gif'>";
return shtml;
}
public string GetShowUrl(object obj)
{
return "show.aspx?id=" + obj;
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
//새로운 페이지에 번호를 넣어줍니다.
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();//현재 페이지 번호 저장
Session["page"] = GridView1.PageIndex;
}
protected void btnWrite_Click(object sender, ImageClickEventArgs e)
{
Response.Write("write.aspx");
}
}
Write.aspx
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td colspan="2" style="height: 25px">
<asp:Label ID="Label4" runat="server" BackColor="#8080FF" BorderColor="Black" BorderStyle="Solid"
BorderWidth="1px" Font-Bold="True" ForeColor="White" Height="20px" Width="454px">글 쓰기</asp:Label></td>
<td style="width: 20px; height: 25px">
</td>
</tr>
<tr>
<td style="width: 55px; height: 25px;">
<asp:Label ID="Label1" runat="server" Text="글쓴이"></asp:Label>
</td>
<td style="width: 284px; height: 25px;">
<asp:TextBox ID="txtName" runat="server" Width="291px"></asp:TextBox></td>
<td style="width: 20px; height: 25px;">
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtName"
ErrorMessage="RequiredFieldValidator">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="width: 55px">
<asp:Label ID="Label2" runat="server" Text="글제목"></asp:Label>
</td>
<td style="width: 284px">
<asp:TextBox ID="txtTitle" runat="server" Width="292px"></asp:TextBox></td>
<td style="width: 20px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="RequiredFieldValidator" ControlToValidate="txtTitle">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="width: 55px; height: 86px;">
<asp:Label ID="Label3" runat="server" Text="내용"></asp:Label>
</td>
<td style="width: 284px; height: 86px;">
<asp:TextBox ID="txtBody" runat="server" Height="125px" Rows="12" TextMode="MultiLine"
Width="391px"></asp:TextBox></td>
<td style="width: 20px; height: 86px;">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="RequiredFieldValidator" ControlToValidate="txtBody">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="width: 55px; height: 16px">
</td>
<td style="width: 284px; height: 16px">
<asp:ImageButton ID="btnSave" runat="server" ImageUrl="~/Images/write.gif" OnClick="btnSave_Click" />
<asp:ImageButton ID="btnList" runat="server" ImageUrl="~/Images/list.gif"
OnClick="btnList_Click" style="height: 25px" /></td>
<td style="width: 20px; height: 16px">
</td>
</tr>
</table>
</div>
</form>
</body>
Write.aspx.cs
using System.Data.SqlClient;
public partial class Write : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if(Request["mode"] == "modify") //수정(modify)모드
{
string sql = "select * from board id = " + Request["id"];
//1.데이터 베이스 열기
DBConn conn = new DBConn();
conn.Open();
//2. sql 문 실행
SqlDataReader reader = conn.ExecuteReader(sql);
if (reader.Read())
{
txtName.Text = reader["name"].ToString();
txtTitle.Text = reader["title"].ToString();
txtBody.Text = reader["body"].ToString();
}
//3. 데이터 베이스 닫기.
reader.Close();
conn.Close();
}
}
}
private void New()
{
string sql1 = "Insert into board(name, title, body, dtime, hit, gr_id, gr_depth, gr_pos) values("
+ "'" + txtName.Text + "',"
+ "'" + txtTitle.Text + "',"
+ "'" + txtBody.Text + "',"
+ "'" + DateTime.Now.ToShortDateString() + "',0,0,0,0)";
string sql2 = "update board set gr_id = id where gr_id = 0";
DBConn conn = new DBConn();
conn.Open();
conn.ExecuteSQL(sql1); //1. insert 문 실행, 새 글을 올립니다.
conn.ExecuteSQL(sql2); //2. 글번호를 그룹 아이디 지정
conn.Close();
}
private void Reply()
{
//1. 원래 글의 그룹 아이디, 깊이, 위치 정보를 구합니다.
int groupid = int.Parse(Request["gr_id"]);
int depth = int.Parse(Request["gr_depth"]);
int pos = int.Parse(Request["gr_pos"]);
//2. 답변이 들어갈 위치(pos)를 비우는 SQL문
string sql1 = "update board set gr_pos=gr_pos+1 " + "where gr_id =" + groupid + "and gr_pos>" + pos;
//3. 답변 글이므로, 원래 글의 깊이(depth)와 위치(pos)를 하나씩 증가시킵니다.
depth++;
pos++;
//4. 답변 글을 추가하는 SQL 문
string sql2 = "Insert into board (name, title, body, dtime, hit, gr_id, gr_depth, gr_pos) values ("
+ "'" + txtName.Text + "',"
+ "'" + txtTitle.Text + "',"
+ "'" + txtBody.Text + "',"
+ "'" + DateTime.Now.ToShortDateString() + "',0,"
+ groupid + "," + depth + "," + pos + ")";
//5. 데이터 베이스 연결 및 실행
DBConn conn = new DBConn();
conn.Open();
conn.ExecuteSQL(sql1); //글이 들어갈 위치 확보
conn.ExecuteSQL(sql2); //답변 글 추가
conn.Close();
}
private void update()
{
string sql = "update board Set "
+ "title='" + txtTitle.Text + "',"
+ "body ='" + txtBody.Text + "',"
+ "whrere id = " + Request["id"];
DBConn conn = new DBConn();
conn.Open();
conn.ExecuteSQL(sql);//글 업데이트
conn.Close();
}
protected void btnSave_Click(object sender, ImageClickEventArgs e)
{
if (Request["mode"] == "Modify")
update();
else
if (Request["mode"] == "Reply")
Reply();
else
New();
//목록보기
Response.Redirect("list.aspx");
}
protected void btnList_Click(object sender, ImageClickEventArgs e)
{
Response.Redirect("list.aspx");
}
}
Show.aspx
<body>
<form id="form1" runat="server">
<asp:Label id="Label4" runat="server" Width="454px" Height="20px" BackColor="#8080FF" Font-Bold="True" BorderColor="Black" ForeColor="White" BorderWidth="1px" BorderStyle="Solid">글 보기</asp:Label>
<br />
<br />
<table>
<tr>
<td style="width: 100px">
<asp:Label id="Label2" runat="server" Width="68px" Height="18px" BackColor="Transparent" >글번호 :</asp:Label>
</td>
<td style="width: 100px">
<asp:label id="id" runat="server" Width="119px" Height="19px" BackColor="White" BorderColor="Black" BorderWidth="1px"></asp:label>
</td>
<td style="width: 100px">
<asp:Label id="Label7" runat="server" Width="76px" Height="18px" BackColor="Transparent" >조회수 :</asp:Label>
</td>
<td style="width: 100px">
<asp:label id="hit" runat="server" Width="118px" Height="19px" BackColor="White" BorderColor="Black" BorderWidth="1px"></asp:label>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:Label id="Label3" runat="server" Width="68px" Height="18px" BackColor="Transparent" >글쓴이 :</asp:Label>
</td>
<td style="width: 100px">
<asp:label id="name" runat="server" Width="118px" Height="19px" BackColor="White" BorderColor="Black" BorderWidth="1px"></asp:label>
</td>
<td style="width: 100px">
<asp:Label id="Label5" runat="server" Width="73px" Height="18px" BackColor="Transparent" >올린 날짜 :</asp:Label>
</td>
<td style="width: 100px">
<asp:label id="dtime" runat="server" Width="116px" Height="19px" BackColor="White" BorderColor="Black" BorderWidth="1px"></asp:label>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:Label id="Label6" runat="server" Width="68px" Height="18px" BackColor="Transparent" >제목 :</asp:Label>
</td>
<td colspan="3">
<asp:label id="title" runat="server" Height="19px" Width="341px" BackColor="White" BorderColor="Black" BorderWidth="1px"></asp:label>
</td>
</tr>
<tr>
<td colspan="4">
<asp:label id="body" runat="server" Height="110px" Width="450px" BackColor="White" BorderColor="Black" BorderWidth="1px"></asp:label></td>
</tr>
<tr>
<td style="width: 100px">
<asp:ImageButton ID="btnList" runat="server" ImageUrl="~/Images/list.gif" OnClick="btnList_Click" /></td>
<td style="width: 100px">
<asp:ImageButton ID="btnModify" runat="server" ImageUrl="~/Images/modify.gif" OnClick="btnModify_Click" /></td>
<td style="width: 100px">
<asp:ImageButton ID="btnReply" runat="server" ImageUrl="~/Images/reply.gif" OnClick="btnReply_Click" /></td>
<td style="width: 100px">
<asp:ImageButton ID="btnDelete" runat="server" ImageUrl="~/Images/delete.gif" OnClick="btnDelete_Click" /></td>
</tr>
</table>
<asp:label id="gr_pos" runat="server" Visible="False">gr_pos</asp:label>
<asp:label id="gr_depth" runat="server" Visible="False">gr_depth</asp:label>
<asp:label id="gr_id" runat="server" Visible="False">gr_id</asp:label>
</form>
</body>
Show.aspx.cs
using System.Data.SqlClient;
public partial class show : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string hitsql = "update board set hit=hit+1 where id=" + Request["id"];
string sql = "SELECT * FROM Board where id = " + Request["id"];
DBConn conn = new DBConn();
conn.Open();
//1. 조회수 증가시킨다.
conn.ExecuteSQL(hitsql);
//2. 해당 글 정보(제목,내용, 조회, 이름, 날짜)를 가져온다.
SqlDataReader reader = conn.ExecuteReader(sql);
//3.글 내용을 보여준다.
if (reader.Read())
{
id.Text = reader["id"].ToString();
hit.Text = reader["hit"].ToString();
name.Text = reader["name"].ToString();
dtime.Text = reader["dtime"].ToString();
title.Text = reader["title"].ToString();
string sbody = reader["body"].ToString();
body.Text = sbody.Replace("\n", "<br>");
gr_id.Text = reader["gr_id"].ToString();
gr_depth.Text = reader["gr_depth"].ToString();
gr_pos.Text = reader["gr_pos"].ToString();
}
reader.Close();
conn.Close();
}
}
protected void btnList_Click(object sender, ImageClickEventArgs e)
{
// 리스트 보기
Response.Redirect("list.aspx");
}
protected void btnModify_Click(object sender, ImageClickEventArgs e)
{
//수정하기.
Response.Redirect("write.aspx?mode=modify&id=" + id.Text);
}
protected void btnReply_Click(object sender, ImageClickEventArgs e)
{
//답변쓰기.
string str = String.Format("write.aspx?mode=reply&id={0}&gr_id={1}&gr_depth={2}&gr_pos={3}",
id.Text, gr_id.Text, gr_depth.Text, gr_pos.Text);
Response.Redirect(str);
}
protected void btnDelete_Click(object sender, ImageClickEventArgs e)
{
//삭제하기
string sql = "delete from board where id = " + id.Text;
DBConn conn = new DBConn();
conn.Open();
conn.ExecuteSQL(sql);
conn.Close();
Response.Redirect("list.aspx");
}
}
Rss Feed