12.ASP.NET - 자료실 게시판(5) : Dac

App_Code - Dsl - UploadDac.cs

---

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