12.ASP.NET - 자료실 게시판(5) : Dac
using
System;
using
System.Configuration;
using
System.Data;
using
System.Data.SqlClient;
/// <summary>
/// 설 명 : 현재 웹 사이트에서 사용되는 모든 Database처리 로직
/// 작성자 : 박용준
/// 작성일 :
/// 변경일 :
/// 기타 :
/// 등등 :
/// </summary>
public class UploadDac
{
#region 변수/필드
private string _ConnectionString; // 연결문자열보관
#endregion
#region 생성자
public UploadDac()
{
// Web.config의 <connectionStrings> 섹션의 값 읽어서 보관
_ConnectionString =
ConfigurationManager.ConnectionStrings[
"ConnectionString"].ConnectionString;
}
#endregion
#region 메서드
#region 입력 : AddUpload()
/// <summary>
/// 자료실에 데이터를 입력하는 메서드
/// </summary>
/// <param name="name">이름</param>
/// <param name="email">이메일</param>
/// <param name="title">제목</param>
/// <param name="postIp">IP주소</param>
/// <param name="content">내용</param>
/// <param name="password">비밀번호</param>
/// <param name="encoding">인코딩</param>
/// <param name="homepage">홈페이지</param>
/// <param name="fileName">파일이름</param>
/// <param name="fileSize">파일크기</param>
/// <returns>입력된 레코드 수 반환</returns>
/// 입력(Inset) : connection() ->
command() -> Parameter 추가 -> ExecuteNonQuery()
public int AddUpload(
string name, string email, string
title, string postIp, string content
, string
password, string
encoding, string
homepage, string
fileName, int
fileSize)
{
//커넥션
SqlConnection con = new SqlConnection(_ConnectionString);
//커멘드
SqlCommand cmd = new SqlCommand("WriteUpload", con);
cmd.CommandType = CommandType.StoredProcedure;
//파라미터 추가
cmd.Parameters.AddWithValue("@Name",
name);
cmd.Parameters.AddWithValue("@Email",
email);
cmd.Parameters.AddWithValue("@Title",
title);
cmd.Parameters.AddWithValue("@PostIp",
postIp);
cmd.Parameters.AddWithValue("@Content",
content);
cmd.Parameters.AddWithValue("@Password",
password);
cmd.Parameters.AddWithValue("@Encoding",
encoding);
cmd.Parameters.AddWithValue("@Homepage",
homepage);
cmd.Parameters.AddWithValue("@FileName",
fileName);
cmd.Parameters.AddWithValue("@FileSize",
fileSize);
//오픈과 동시에 실행, 닫기
con.Open();
int result =
cmd.ExecuteNonQuery();
con.Close();
return result;
}
#endregion
#region 출력 : GetUploads()
/// <summary>
/// 출력 : 자료실 리스트
/// </summary>
/// <returns>데이터셋</returns>
/// 출력(Select) : connection()
-> command() -> DataAdapter() -> DataSet() -> Fill()
public DataSet GetUploads()
{
SqlConnection conn = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand("ListUpload", conn);
cmd.CommandType = CommandType.StoredProcedure;
//입력, 수정, 삭제 기능 테이블 생성
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "Upload");
return ds;
}
#endregion
#region 상세
/// <summary>
/// 상세 : 자료실 콘텐츠 보기
/// </summary>
/// <param name="num">Num</param>
/// 상세(Select) : connection()
-> command() -> parameter 추가 -> DataReader() -> ExecuteReader()
public SqlDataReader GetUploadByNum(int num)
{
SqlConnection conn = new SqlConnection(_ConnectionString);
conn.Open(); //
SqlCommand cmd = new SqlCommand("ViewUpload", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Num",
num);
SqlDataReader dr =
cmd.ExecuteReader();
return dr; // Close() -> uisng () {} 사용해서 Close() 처리 필요
}
#endregion
#region 수정
/// <summary>
/// 수정 : 자료실 수정
/// </summary>
/// <param name="entity"></param>
/// 수정(Update) : connection()
-> command() -> parameter추가 -> ExecuteNonQuery()
public int UpdateUpload(UploadEntity entity)
{
SqlConnection con = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand("ModifyUpload", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name",
entity.Name);
cmd.Parameters.AddWithValue("@Email",
entity.Email);
cmd.Parameters.AddWithValue("@Title",
entity.Title);
cmd.Parameters.AddWithValue("@ModifyIp",
entity.ModifyIP);
cmd.Parameters.AddWithValue("@Content",
entity.Content);
cmd.Parameters.AddWithValue("@Encoding",
entity.Encoding);
cmd.Parameters.AddWithValue("@Homepage",
entity.Homepage);
cmd.Parameters.AddWithValue("@Password",
entity.Password);
cmd.Parameters.AddWithValue("@FileName",
entity.FileName);
cmd.Parameters.AddWithValue("@FileSize",
entity.FileSize);
cmd.Parameters.AddWithValue("@Num",
entity.Num);
con.Open();
int result =
cmd.ExecuteNonQuery();
con.Close();
return result;
}
#endregion
#region 삭제
/// <summary>
/// 삭제
/// </summary>
/// <param name="num"></param>
/// <param name="password"></param>
/// 삭제(Delete) : connection()
-> command() -> parameter추가 -> ExecuteNonQuery()
public int DeleteUpload(int num, string
password)
{
SqlConnection con = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand("DeleteUpload", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Password",
password);
cmd.Parameters.AddWithValue("@Num",
num);
con.Open();
int result =
cmd.ExecuteNonQuery();
con.Close();
return result;
}
#endregion
#region 검색
/// <summary>
/// 검색
/// </summary>
/// <param name="searchField"></param>
/// <param name="searchQuery"></param>
/// 검색(Select) : connection()
-> command() -> parameter추가 -> dataReader()
public IDataReader GetUploadsByWord(string searchField, string searchQuery)
{
SqlConnection conn = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand("SearchUpload", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchField",
searchField);
cmd.Parameters.AddWithValue("@SearchQuery",
searchQuery);
conn.Open(); // ***
SqlDataReader dr =
cmd.ExecuteReader();
return dr; // Close() -> uisng () {} 사용해서 Close() 처리 필요
}
#endregion
#region 다운
/// <summary>
/// 다운
/// </summary>
/// <param name="num"></param>
public void UpdateDownCount(string fileName)
{
SqlConnection con = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand("IncreaseDownCountUpload",
con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FileName",
fileName);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
#endregion
#endregion
}
UploadDac.cs