05장 ADO.Net 카테고리 추가,리스트,뷰(CategoryAdd,List,View)

Magic_kit 2009. 9. 28. 10:33

- 6가지 패던을 머리에 정리 -
입력 : Connection -> Command -> ExecuteNonQuer()
출력 : Connection -> Command -> DataReader() -> DataBind()
상세 : Connection -> Command -> DataReader() -> Read()
수정
삭제
검색

동기식(Synchronous) : 하나만 처리
비동기식(Asynchronous) : 다중처리, AJAX

CategoryAdd 카테고리 추가.Cs

public partial class Category_FrmCategoryAdd : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    }
    protected void btnCategoryAdd_Click(object sender, EventArgs e)
    {
        //변수선언부
        string insertQuery =

        //"Insert into Categories(CategoryName) Values('컴퓨터')";

        //매개변수 처리 : 컴퓨터
        //   "Insert Into Categories(CategoryName)
                            values('" + txtCategoryName.Text + "')";

//최적으로 사용하는 방법[추천] Named 매개변수 처리 : @변수
"Insert Into Categories(CategoryName) Values(@CategoryName)";

        //[1] 커넥션
        SqlConnection con = new SqlConnection(
                ConfigurationManager.ConnectionStrings
                      ["ConnectionString"].ConnectionString);

        //[2] 커맨드
        SqlCommand cmd = new SqlCommand(insertQuery, con);
        cmd.CommandType = CommandType.Text;

        //파라미터 추가
        cmd.Parameters.AddWithValue
                  ("@CategoryName", txtCategoryName.Text);

        //[3] 실행
        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException se)
        {
            lblError.Text = se.Message; //에러메시지 출력

        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close(); //연결상태라면, 닫기
            }
        }
        con.Close();

}
}

CategoryList 카테고리 리스트.Cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Category_FrmCategoryList : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            DisplayData();
            DisplayCount();
        }

    }

     //Categories 레코드 갯수
    private void DisplayCount()
    {
        SqlConnection con = new SqlConnection
                 (ConfigurationManager.ConnectionStrings
                  ["ConnectionString"].ConnectionString);
        con.Open();

        SqlCommand cmd = new SqlCommand
                ("Select Count(*) From Categories", con);

        cmd.CommandType = CommandType.Text;

        lblCount.Text = cmd.ExecuteScalar().ToString();
        con.Close();
    }

    //Categories 출력
    private void DisplayData()
    {
        //[1] 커넥션
        SqlConnection con = new SqlConnection
             (ConfigurationManager.ConnectionStrings
               ["ConnectionString"].ConnectionString);
        con.Open(); //열기

        //[2] 커맨드
        SqlCommand cmd = new SqlCommand
                           ("Select *From Categories", con);
        cmd.CommandType = CommandType.Text;

        //[3]데이터 리더
        SqlDataReader dr = cmd.ExecuteReader();

        //[4]바인딩
        ctlCategoryList.DataSource = dr;
        ctlCategoryList.DataBind();

        //[닫기]
        dr.Close();
        con.Close();
    }
}

CategoryView 카테고리 View.Cs

public partial class Category_FrmCategoryView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    }
    protected void btnCategoryID_Click(object sender, EventArgs e)
    {
        //커넥연 (데이터베이스연결)
        SqlConnection conn = new SqlConnection
                 (ConfigurationManager.ConnectionStrings
                   ["ConnectionString"].ConnectionString);
        conn.Open();

        //커맨드 : 단일 레코드
        SqlCommand cmd = new SqlCommand(
            "Select *From Categories Where
                        CategoryID = @CategoryID", conn);
        cmd.CommandType = CommandType.Text;

//파라미터 추가
cmd.Parameters.AddWithValue("@CategoryID", txtCategoryID.Text);

        //데이터 리더
        SqlDataReader dr = cmd.ExecuteReader();

        //Read() --> 문자열 정수형 인덱서 또는 GetXXX() 메서드로 읽어오기
        while (dr.Read())
        {
            //문자열 인덱서
            this.txtCategoryID.Text = dr["CategoryID"].ToString();

           //정수(서수)형 처리
            this.lblCategoryName.Text = dr[1].ToString();

            if (dr["SuperCategory"] != null) //널 값처리
            {
                this.lblSuperCategoryName.Text = dr
                                ["SuperCategory"].ToString();
            }
            this.lblCategoryAlign.Text = dr["Align"].ToString();
        }
        dr.Close();
        conn.Close();
   }
}