[ASP.NET] 직접 데이터 접근(2)

INSERT, UPDATE, DELETE문으로 데이터 갱신하기

위 세 가지 명령문들의 실행 과정은 SELECT문과 동일하지만 DataReader 객체를 사용하지 않는다는 것과 Command 객체를 사용하여 SQL문을 실행할 때 ExecuteNonQuery 메서드를 쓴다는 것이 다르다.
아래의 코드는 DataReader 객체를 사용하고, Command 객체를 사용하여 ExecuteQuery 메서드를 사용하는 SELECT문의 코드이다.

// SELECT문 SqlDataReader rd = cmd.ExecuteReader();

ExcuteNonQuery 메서드는 질의문의 실행 결과로 질의문에 의해 영향을 받은 행의 개수를 반환하는데, 예상과 결과를 비교해야 할 경우에 사용할 수 있다.

① INSERT문 실행 예

string insertSQL = "INSERT INTO saleslt.product(name, productnumber, standardcost, "; insertSQL += "listprice.productcategoryid, sellstartdate)";
insertSQL += "VALUES ('Touring-4500 Black, 50', 'BK-T45U-50', 450.450, ";
insertSQL += "850.850, 7, GETDATE())";

// Command 클래스의 생성자를 이용하여 SQL문과 연결 설정
SqlCommand cmd = new SqlCommand(insertSQL, conn);
conn.Open();
cmd.ExecuteNonQuery();

② UPDATE문 실행 예

string updateSQL = "UPDATE saleslt.product SET color = 'Black' size = 50, WHERE productid=1000";
SqlCommand cmd = new SqlCommand(updateSQL, conn);
conn.Open();
cmd.ExecuteNonQuery();

③ DELETE문 실행 예

string deleteSQL = "DELETE FROM saleslt.product WHERE productid = 1000";
SqlCommand cmd = new SqlCommand(deleteSQL, conn);
conn.Open();
cmd.ExcuteNonQuery();

 

매개변수가 있는 질의문 사용하기

txtColor라는 TextBox 컨트롤이 웹 폼에 있다고 가정한다.

cmd.CommandText = "UPDATE saleslt.product SET " cmd.CommandText += "color = '" + txtColor.Text + "', size = 50 WHERE productid = 1000";

SQL문에서는 문자열임을 나타날 때 작은 따옴표를 사용한다. 사용자가 입력한 데이터에 작은 따옴표가 있을 경우, SQL문을 실행하면 오류가 발생한다.

// 작은 따옴표를 큰 따옴표로 바꿈
string color = txtColor.Text.Replay("'", """);
cmd.CommandText = "UPDATE saleslt.product SET ";
cmd.CommandText += "color = '" + color + "', size = 50 WHERE productid = 1000";

작은 따옴표를 오류 없이 코딩하는 것도 어려울 뿐만 아니라 사용자의 예기치 못한 입력에 대응하는 것도 쉬운 문제는 아니다. 이 문제를 해결하기 위해서는 매개변수가 있는 명령문을 사용해야 한다.

cmd.CommandText = "UPDATE saleslt.product SET "; cmd.CommandText += "color = @color, size=@size WHERE productid=@productid;

@변수 이름 형태로 표현된 부분은 매개변수가 있는 명령문이 실행될 때 설정된 값으로 대체된다. 대체될 값을 설정하기 위해 Command.Parameters 컬렉션에 매개변수 객체를 추가한다.

cmd.Parameters.AddWithValue("@color", txtColor.Text);
cmd.Parameters.AddWithValue("@size", int.Parse(txtSize.Text));
cmd.Parameters.AddWithValue("@productid", int.Parse(txtProductID.Text));

매개변수가 있는 명령문은 SELECT문에서도 사용할 수 있다.

직접 데이터 접근을 이용하여 페이지 만들기

· 상품 정보 검색 : Touring 자전거 상품 목록이 드롭다운리스트 컨트롤에 등록되어 있으며, 상품을 선택하면 해당 상품의 정보가 페이지에 표시된다.
· 상품 정보 수정 : 표준 원가, 판매 가격, 판매일 등을 변경해야 할 겨웅, 화면에 표시된 상품 정보를 수정하고 [상품 정보 수정] 버튼을 클릭한다.
· 상품 정보 삭제 : 현재 화면에 정보가 표시된 상품 정보를 삭제하려면 [상품 정보 삭제] 버튼을 클릭한다.
· 새 상품 등록 : [새 상품 등록을 위한 초기화] 버튼을 클릭하여 화면에 표시된 정보를 초기화한 후 새 상품의 정보를 입력하고 [새 상품 등록] 버튼을 클릭한다. 상품 번호 필드는 IDENTITY 속성으로 삽입이 이루어질 때마다 1씩 자동 증가한다. 따라서 임의의 값이 삽입되지 못하도록 읽기 전용으로 만들었다. 그러므로 새 상품을 등록할 대는 상품 번호 입력란은 비워 둔 채로 [새 상품 등록] 버튼을 클릭해야 한다.

① 새 웹 폼 만들기

솔루션 탐새기에서 코드 숨김 파일 페이지 모델 웹 폼인 TouringBikeProductManager.aspx를 만든다.

② 페이지 디자인

다음 사항을 참고하여 페이지를 구성한다. 페이지를 구성할 때는 <div>나 <table> 태그를 적절하게 이용해야 한다.

번호	컨트롤	속성	속성 값
①	DropDownList	ID	ddlProducts
		AutoPostBack	True
②	Button	ID	btnUpdate
		Text	상품 정보 수정
③	Button	ID	btnDelete
		Text	상품 정보 삭제
④	Button	ID	btnClearInfomation
		Text	새 상품 등록을 위한 초기화
⑤	Button	ID	btnInsertNewProduct
		Text	새 상품 등록
⑥	TextBox	ID	txtProductID
		ReadOnly	True
⑦	TextBox	ID	txtProductName
⑧	TextBox	ID	txtProductNumber
⑨	TextBox	ID	txtStanardCost
⑩	TextBox	ID	txtListPrice
⑪	TextBox	ID	txtSellStartDate
⑫	Label	ID	lblStatus

 

③ 코드 숨김 파일 작성하기

① 네임스페이스 추가와 연결 문자열을 위한 클래스 변수 선언
TouringBikeProductManager 클래스에서는 연결 문자열과 SQL 서버 공급자를 이용한다.

// TouringBikeProductManager.aspx.cs
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

...

public partial calss TouringBikeProductManager : System.Web.UI.Page {
  private string connectionString = WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
}

② Page_Load 이벤트 처리기와 FillAllProductList 메서드 작성하기
페이지가 Load될 때 데이터 소스에서 Touring 자전거 상품의 목록을 얻어와 드롭다운리스트에 추가한다. 드롭다운 리스트의 상품 목록은 새로운 상품이 등록되거나 삭제될 경우 갱신되어야 한다. 따라서 드롭다운 리스트에 상품 목록을 작성하는 작업을 별도의 메서드(FillAllProductList)로 만들어 활용한다.

· Page_Load 이벤트 처리기에서는 처음 페이지가 요구되면 드롭다운 리스트에 상품 목록을 작성하는 메서드를 호출하면 된다.

protected void Page_Load(object sender, EventArgs e) {
  if (!this.IsPostBack) {
    FillAllProductList();
  }
}

· 상품 목록을 작성하는 메서드는 상품 테이블(Production.Product)에서 Touring 자전거 상품(ProductSubcategoryID=1)을 읽어와 드롭다운 리스트에 상품을 등록한다. 새로운 상품이 등록되거나 상품 정보가 삭제될 경우 목록을 갱신하기 위해 기존 항목을 삭제하고 상품 목록을 갱신하는 메서드는 다음과 같이 작성한다.

private void FillAllProductList() { 
  // 기존 항목을 모두 삭제
  ddlProducts.Items.Clear();

  // Touring 자전거 상품(ProductSubcategoryID=1)의 이름과 상품 고유번호 검색을 위한 SQL문 작성
  string selectSQL = "SELECT name, productid from production.product where productsubcategoryid=1";
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand(selectSQL, conn);

  SqlDataReader rd; 
  try {
    conn.Open();
    rd = cmd.ExecuteReader();

    // 결과 집합에서 더 이상 읽을 행이 없을 때까지
    while (rd.Read()) {
      ListItem item = new ListItem();
      item.Text = rd["Name"].ToString();
      // 상품의 고유 번호(ID)를 항목의 Value 값에 저장
      item.Value = rd["ProductID"].ToString();
      ddlProducts.Items.Add(item);
    }
    rd.Close();
  }
  catch (Exception e) {
    lblStatus.Text = "데이터베이스를 읽는 동안 오류가 발생하였습니다.<br/>";
    lblStatus.Text += e.Message;
  }
  finally {
    conn.Close();
  }
}

③ 드롭다운 리스트 컨트롤의 SelectedIndexChanged 이벤트 처리기 작성하기
드롭다운 리스트에서 상품을 선택하면 상품의 고유 번호(ID)를 이용하여 해당 상품의 모든 열 정보를 검색하지만, 보여줄 정보만을 TextBox 컨트롤에 할당한다.

protected void ddlProducts_SelectedIndexChanged(object sender, EventArgs e)
{
  lblStatus.Text = "";

  // 상품의 고유 번호로 상품의 모든 열 정보 검색을 위한 매개변수가 있는 SQL문 사용
  string selectSQL = "SELECT * FROM production.product WHERE productid=@productid";
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand(selectSQL, conn);

  // 드롭다운리스트 컨트롤 항목의 Value 속성에 상품 ID 속성이 저장되어 있다.
  cmd.Parameters.AddWithValue("@productid", ddlProducts.SelectedValue);
  SqlDataReader rd;

  try
  {
    conn.Open();
    rd = cmd.ExecuteReader();

    // 하나의 레코드만이 검색되므로 Read 메서드를 한 번만 사용
    rd.Read();

    // 읽어 들인 행의 열 데이터를 열 이름으로 참조하여 읽어온다.
    txtProductID.Text = rd["ProductID"].ToString();
    txtProductName.Text = rd["Name"].ToString();
    txtProductNumber.Text = rd["ProductNumber"].ToString();
    txtStanardCost.Text = rd["StandardCost"].ToString();
    txtListPrice.Text = rd["ListPrice"].ToString();
    txtSellStartDate.Text = rd["SellStartDate"].ToString();

    rd.Close();
  }
  catch (Exception error)
  {
    lblStatus.Text = "데이터베이스를 읽는 동안 오류가 발생했습니다.<bt/>";
    lblStatus.Text += error.Message;
  }
  finally {
    conn.Close();
  }
}

④ [상품 정보 수정] 버튼 클릭 이벤트 처리기 작성하기
상품 정보를 수정해야 할 경우에는 TextBox 컨트롤에 있는 상품 정보의 내용을 수정한 후 [상품 정보 수정] 버튼을 클릭한다. [상품 정보 수정] 버튼 클릭 이벤트 처리기에서는 TextBox 컨트롤의 내용을 이용하여 UPDATE문을 작성하고, 이를 실행하여 데이터베이스의 정보를 변경한다.

protected void btnUpdate_Click(object sender, EventArgs e)
{
  // 매개변수가 있는 명령문을 사용하는 SQL문 작성
  string updateSQL = "UPDATE production.product SET Name=@pname, ";
  updateSQL += "ProductNumber=@pnum, StandardCost=@sc, ";
  updateSQL += "ListPrice=@lp, SellStartDate=@ssd WHERE productid=@pid";
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand(updateSQL, conn);

  // Command 객체에 매개변수와 매개변수 값 추가
  // 단, 매개변수의 값은 TextBox 컨트롤에서 읽어온다.
  cmd.Parameters.AddWithValue("@pname", txtProductName.Text);
  cmd.Parameters.AddWithValue("@pnum", txtProductNumber.Text);
  cmd.Parameters.AddWithValue("@sc", double.Parse(txtStanardCost.Text));
  cmd.Parameters.AddWithValue("@lp", double.Parse(txtListPrice.Text));
  cmd.Parameters.AddWithValue("@ssd", DateTime.Parse(txtSellStartDate.Text));
  cmd.Parameters.AddWithValue("@pid", txtProductID.Text);

  // Command 객체를 실행한 후 수정된 레코드 수를 저장하기 위한 변수
  int updated = 0;
  try
  {
    conn.Open();
    updated = cmd.ExecuteNonQuery();
    lblStatus.Text = updated.ToString() + " 개의 레코드가 수정되었습니다.";
  }
  catch (Exception error)
  {
    lblStatus.Text = "데이터베이스에 쓰는 동안 오류가 발생했습니다.<br/>";
    lblStatus.Text += error.Message;
  }
  finally {
    conn.Close();
  }
}

⑤ [상품 정보 삭제] 버튼 클릭 이벤트 처리기 작성하기
[상품 정보 삭제] 버튼을 클릭하면 화면에 표시된 상품을 삭제하기 위해 DELETE문을 실행한다. 삭제가 완료되면 화면에 표시된 정보를 모두 삭제한다.

protected void btnDelete_Click(object sender, EventArgs e)
{
  // 데이터 삭제를 위한 SQL문 작성
  string deleteSQL = "DELETE FROM production.product WHERE productid=@pid";
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand(deleteSQL, conn);

  // command 객체에 매개변수와 매개변수 값 추가
  cmd.Parameters.AddWithValue("@pid", txtProductID.Text);

  // command 객체를 실행 후 삭제된 레코드 수를 저장하기 위한 변수
  int deleted = 0;
  try
  {
    conn.Open();
    deleted = cmd.ExecuteNonQuery();
    lblStatus.Text = deleted.ToString() + " 개의 레코드가 삭제되었습니다.";

    // 화면에 표시된 정보도 삭제
    txtProductID.Text = "";
    txtProductName.Text = "";
    txtProductNumber.Text = "";
    txtStanardCost.Text = "";
    txtListPrice.Text = "";
    txtStanardCost.Text = "";
    txtSellStartDate.Text = "";
  }
  catch (Exception error)
  {
    lblStatus.Text = "데이터를 삭제하는 동안 오류가 발생했습니다.<br/>";
    lblStatus.Text += error.Message;
  }
  finally {
    conn.Close();
  }

  // 삭제된 상품 정보가 있다면 상품 목록을 갱신
  if (deleted > 0) { 
    FillAllProductList();
  }
}

⑥ [새 상품 등록을 위한 초기화] 버튼 클릭 이벤트 처리기 작성하기
[새 상품 등록을 위한 초기화] 버튼을 클릭하며 새 상품의 정보를 입력받기 위해 TextBox 컨트롤의 내용을 비운다.

protected void btnClearInfo_Click(object sender, EventArgs e)
{
  txtProductID.Text = "";
  txtProductName.Text = "";
  txtProductNumber.Text = "";
  txtStanardCost.Text = "";
  txtListPrice.Text = "";
  txtStanardCost.Text = "";
  txtSellStartDate.Text = "";

  lblStatus.Text = "제품 정보를 모두 입력한 후 [새 상품 등록] 버튼을 클릭하세요.";
}

⑦ [새 상품 등록] 버튼 클릭 이벤트 처리기 작성하기
TextBox 컨트롤에 새 상품에 대한 정보를 입력한 후 [새 상품 등록] 버튼을 클릭하면 TextBox 컨트롤의 내용을 이용하여 삽입하기 위한 SQL문을 작성한다. 작성된 INSERT문을 실행하여 데이터베이스에 새로운 레코드를 삽입한다.

protected void btnInsert_Click(object sender, EventArgs e)
{
  // 매개변수가 있는 명령문을 사용하여 삽입하기 위한 SQL문 작성
  string insertSQL = "INSERT INTO production.product ";
  insertSQL += "(name, productnumber, standardcost, listprice, ";
  insertSQL += "productsubcategoryid, sellstartdate, safetystocklevel, reorderpoint, daysTomanufacture) VALUES ";

  // 상품 카테고리 번호 1(Touring 자전거를 의미)은 직접 값을 입력
  // SellStartDate는 현재 날짜를 이용하여 값을 할당
  // GETDATE() 함수는 SQL로 삽입되는 날짜를 반환한다.
  // safetystocklevel은 null이 들어갈 수 없어 임의적으로 1000을 할당
  // reorderpoint는 null이 들어갈 수 없어 임의적으로 750을 할당
  // daysTomanufacture는 null이 들어갈 수 없어 임의적으로 0을 할당
  insertSQL += "(@pname, @pnum, @sc, @lp, 1, GETDATE(), 1000, 750, 0)";

  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand(insertSQL, conn);
  cmd.Parameters.AddWithValue("@pname", txtProductName.Text);
  cmd.Parameters.AddWithValue("@pnum", txtProductNumber.Text);
  cmd.Parameters.AddWithValue("@sc", double.Parse(txtStanardCost.Text));
  cmd.Parameters.AddWithValue("@lp", double.Parse(txtListPrice.Text));

  int inserted = 0;
  try
  {
    conn.Open();
    inserted = cmd.ExecuteNonQuery();
    lblStatus.Text = inserted.ToString() + " 개의 레코드가 삽입되었습니다.";
  }
  catch (Exception error)
  {
    lblStatus.Text = "데이터베이스에 쓰는 동안 오류가 발생했습니다.<br/>";
    lblStatus.Text += error.Message;
  }
  finally { 
    conn.Close();
  }

  // 추가된 상품 정보가 있다면 상품 목록을 갱신
  if (inserted > 0) { 
    FillAllProductList();
  }
}

상품 정보 수정 완료

새 상품 등록 완료

상품 정보 삭제 완료

java로 웹을 만드는 것과 사용하는 방법만 다른 것 같아서 비교적 이해가 잘 되는 것 같았다.