닷넷 게시판 만들기 Part 25 - DB에서 받은자료 마음대로 다루기

2011/02/16 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 20
2011/02/18 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 21
2011/02/18 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 22
2011/02/28 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 23
2011/03/02 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 24

1. 닷넷 개발환경 준비, 테스트
2. 닷넷 알아보기 [7/7]
3. asp.net 컨트롤 [10/10]
4. 데이터베이스(DB) [6/..]

닷넷에서 DB서버로 쿼리를 날리는 클래스는 SqlCommand 이다.
쿼리 형식은 결과값이 없는 INSERT/UPDATE/DELETE 와 결과 값이 있는 SELECT 문으로 나눌 수 있는데, 결과값은 형태가 2가지가 있다고 했다. 한 개 필드값, 그러니까 1개의 레코드에 1개의 컬럼만 받을 수 있는 ExecuteScalar 메서드를 이전 시간에 테스트했다.

이번 시간에는 DB결과를 받아서 처리할 수 있는 SqlCommand.ExecuteReader 메서드를 살펴보자.

닷넷에서 DB서버의 결과값을 받는다는 의미는 DB관리툴에서 SELECT 쿼리문의 결과를 그대로 asp.net 에서 사용할 수 있게 한다는 것이다. 소스는 db2.aspx 파일을 그대로 사용하기로 한다.

SQL Server DB관리툴에서 테이블의 모든 자료를 가져오는 쿼리를 날려보자.

SELECT * FROM member_table

여러 레코드의 결과가 모두 나오는가?
이제 이 자료들을 aspx 에서 가져와서 모두 출력해보자.

SqlCommand.ExecuteReader 메서드의 리턴은 SqlDataReader (클래스)형식이다. 그냥 SqlDataReader 로 받으면 되겠다.

// DB연동작업 시작
string QUERY = "SELECT * FROM member_table";
SqlCommand cmd = new SqlCommand(QUERY, conn);
SqlDataReader data = cmd.ExecuteReader();
  

#참고
db2.aspx 파일의 SqlCommand 클래스의 사용 형식은 모두 동일하다. 전에도 언급했었지만 해당 쿼리(QUERY)를 서버(conn)로 날려주는 기능을 하는 것이 SqlCommand 클래스이다.

위처럼 실행하면 오류가 발생하지 않는다. 쿼리에 별 문제가 없으니 DB서버에서도 쿼리를 잘 실행했을 것이고, 그 결과를 SqlDataReader 형식인 data 에 지정하였다.
이제 데이터를 읽어봐야 할 차례인데 어떻게 할 것인가? 
당연하겠지만 MSDN에서 SqlDataReader 클래스의 멤버를 뒤져보면 답이 나온다.

SqlDataReader에서 우리가 눈여겨봐야 할 것은 Read() 메서드이다.

소스에서 SqlDataReader인 data 는 현재 그림에서 빨간 동그라미 위치이다. 이 위치는 데이터를 읽을 수 없다. data.Read() 를 1회 실행하면 SqlDataReader 는 결과값의 첫번째 레코드로 이동한다. 화살표가 보이는가? 여기서 각각의 컬럼인 user_id, user_regdate 등을 읽을 수 있다. 그런 후 또 Read() 를 실행하면 다음 레코드로 이동하여 읽을 수 있게 된다.

MSDN에 나와있는 Read() 메서드의 리턴은 bool 이다. 읽을 수 있는 행(레코드)가 있으면 true, 없으면 false 이다. 그러니까, 모든 데이터를 읽기 위해서는 SqlCommand.Read() 가 false 일 때 까지 루프(loop)를 돌려주면 되는 것이다.

즉, SqlCommand.ExecuteReader 메서드는 모든 자료를 읽기는 하는데 정방향(forward) 전용이다. 읽은 자료를 다시 되돌아가 읽을 수 없다. Read() 메서드로 다음 레코드로 이동할 뿐이다. 사용에 조금 불편할 수도 있다. 그러나 처리하는 속도가 빠르다.

이제 간단한 예제를 만들어보자. 필자의 DB의 모든 내용을 출력해보겠다.

(..생략)

  string QUERY = "SELECT * FROM member_table";
  SqlCommand cmd = new SqlCommand(QUERY, conn);
  SqlDataReader data = cmd.ExecuteReader();
  
  while( data.Read() )
  {
   Response.Write(String.Format("사용자 : {0} (이름:{1}, 비밀번호:{2})<br>등록일:{3}<hr>", data[0], data["user_name"], data[1], data[3] ));
  }

(후략..)

일반적으로, DB의 모든 자료를 조회해서 차례로 읽기위해 사용하는 것이 ExecuteReader 메서드이다. data.Read() 의 결과가 false, 즉 자료가 없을 때 까지 while() { .. } 을 이용해서 루프를 돌려서 모든 자료를 출력한다.

자료의 출력방법은 예제처럼 SqlDataReader 를 컬렉션으로 사용하면 된다. 컬럼순서에 따라 data[0]~data[3] 까지 이용하거나, 컬럼명을 data["컬럼명"] 이런 형식으로 지정하면 되겠다.

그리고 이 자료는 모두 각각 objet 형이기 때문에, 따로 가공하려면 해당 자료형으로 변환을 하여 사용해야 한다.

string user_name = data["user_name"];              - 오류발생. object 를 string 으로 변환불가
string user_name = (string)data["user_name"]   - 잘 동작함
string user_name = data["user_name"] + "님"       - 잘 동작함 (문자열결합)

다시 말하지만 DB의 컬럼 형식은 우리가 이미 알고 있기 때문에 아무 문제 없이 변환하여 사용할 수 있을 것이다.

실행 결과는 위와 같이 출력이 되겠다.

이와 같이, 닷넷의 역할은 별거 없다고 생각하자.
DBMS에서 명령은 쿼리문으로 넣는다. 쿼리문이 SELECT 일 때는 결과 값을 닷넷에서 어떻게 처리할 것인지가 관건이다. 

4장 asp.net 과 DB연동을 정리해보자

다음 이미지는 지난 강의 때 한번 슥~보고 지나갔었다.

닷넷 프레임워크 플랫폼에서 우리는 ASP.NET 을 하고 있다. (결과물이 웹이다)
그리고 DB연동을 위해 ADO.NET 을 이용한다. ADO.NET 을 이용한다 하면 System.Data 에 들어있는 클래스들을 이용한다고 생각하자.

연결하기 위해서는 (1) SqlConnection 클래스를 이용하고. 해당 연결 정보를 SqlCommand 클래스에서 참조하게 된다. (2) SqlCommand 는 실제 DB Server 에 쿼리문을 날리는 역할을 한다.

그래서 우리는 SqlCommand 로 ExecuteNonQuery, ExecuteScalar, ExecuteReader 세 가지의 메서드를 이용해서 쿼리문의 성격에 맞게 실행을 해보았다.

각종 게시판이나 쇼핑몰 물건 리스트들은 데이터베이스 테이블의 자료를 가져와서 뿌려주는 기능을 한다. 앞서 해본 SqlCommand.ExecuteReader() 메서드로 SqlDataReader 형식을 만들어 차례대로 뿌려줄 수면 되겠지만, 불편한 감이 없지 않아 있다. 한번 Read() 한 후에는 다시 돌아갈 수 없기 때문이다. 가장 큰 문제점은 연결지향 방식이기 때문에 자료를 처리하는 동안에는 DB와 연결을 끊을 수 없다는 점이다. 

그래서 ADO.NET 에서는 SqlDataAdapter 라는 클래스를 제공해준다.
SqlDataAdapter는 쿼리의 결과를 DataSet 클래스에 담아주는 역할을 한다. 이 말은, DBMS의 DB의 모습을 그대로 옮겨왔다고 생각을 하면 쉽다.

 

위의 그림을 참고하자. DataRelation 은 무시하자. DataSet 은 위와 같은 모습을 하고 있다.

DBMS에서 DB는 Table의 묶음이라고 했으며, Table은 Column을 하나 이상 가지고 있다고 했다.

DataSet 클래스가 이와 거의 같은 모습을 하고 있다.
DataSet 은 DataTable 클래스의 묶음이고, DataTable 클래스는 DataRow 를 가지고 있다.
DataRow 는 DataColumn 을 가지고 있게 된다. 이들은 각각 해당 컬렉션으로 동작한다.

이런 형식인 DataSet 에 쿼리문의 결과 값을 SqlDataAdapter 클래스를 이용하여, DataSet 에 그대로 판막이처럼 넣을 수 있다. 

쉽게 여러분들이 DB관리툴에서 SELECT 한 결과를 그대로 DataSet에 찍어낸다 생각하자.

SqlDataReader 대신 DataSet 을 이용하는 이유는, 찍어낸 후에 바로 DB연결을 끊을 수 있기 때문이다. 단지 자료를 보여주기 위한 기능으로서는 매우 적합하다.

실제 사용해보기 위해서, 다음 코드를 보도록 하자.

db3.aspx

<%@ Page Language="C#" runat="server" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>

<script language="C#" runat="server">

 void Page_Load()
 {
  string str_conn = "server=notebook-PC\\STUDY;user id=sa;password=1121;database=aspnet;";
  SqlConnection conn = new SqlConnection(str_conn);
  conn.Open();

  
  // DB연동작업 시작
  string QUERY = "SELECT * FROM member_table";
  SqlDataAdapter da = new SqlDataAdapter(QUERY, conn);
  DataSet ds = new DataSet();
  da.Fill(ds);
  
  // DB연동작업 끝
  conn.Close();
 }

</script>

 

db2.aspx 와 다른 것은 없다. 대신, SqlCommand 를 사용하지 않고 SqlDataAdapter를 사용하는 것이다. 이 결과는 DataSet 인 ds에 쿼리문의 결과가 담아지게 된다.

DataSet 자체로는 자료를 담고 있지는 않다. DataSet 은 DataTable의 묶음이라고 했다. 그리고 이것은 컬렉션(Collection)이므로,

ds.Tables[0]

이렇게 DataTable 을 지정하여 사용할 수 있다.

DataTable은 DataRows 라는 컬렉션(컬렉션은 클래스라고 했다)으로 레코드를 읽어온다. 그 이름은 Rows 이다.

// DB연동작업 시작
string QUERY = "SELECT * FROM member_table";
SqlDataAdapter da = new SqlDataAdapter(QUERY, conn);
DataSet ds = new DataSet();
da.Fill(ds);

Response.Write("1. DataTable -- " + ds.Tables[0] + "<br>");
Response.Write("2. DataTable.Rows.Count -- " + ds.Tables[0].Rows.Count + "<br>");
Response.Write("3. DataTable.Rows -- " + ds.Tables[0].Rows + "<br>");
Response.Write("4. DataTable.Rows[0] -- " + ds.Tables[0].Rows[0] + "<br>");
Response.Write("5. DataTable.Columns.Count -- " + ds.Tables[0].Columns.Count + "<br>");
Response.Write("6. DataTable.Columns -- " + ds.Tables[0].Columns + "<br>");
Response.Write("7. DataTable.Columns[0] -- " + ds.Tables[0].Columns[0] + "<br>");


// DB연동작업 끝
conn.Close();

형식을 살펴보기 위해, 파란색 항목을 넣어서 실행해보자.
실제 자료는 테이블, 그러니까 DataTable 에 들어있다. 

이 그름은 앞서 본 그림에서 현재 코드에 해당되는 부분을 네모로 묶은 것이다.
DataTable 에는 DataRow, DataColumn 이렇게 두 가지로 실제 자료에 접근이 가능하다.

위의 그림이 DataTable 으로 생각해보자.
SELECT 쿼리문의 결과가 4개의 컬럼이다. 그리고 총 5개의 레코드를 가지고 있음을 알 수 있다. 이 자료들은 위와 같은 컬렉션으로 접근이 가능하다. 모든 컬렉션에는 .Count 프로퍼티가 있으므로 갯수도 쉽게 가져올 수 있을 것이다. 또한 Rows[n][m] 형식을 이용해 DataTable의 자료를 다이렉트로 접근하여 가져올 수 있다.

그렇다면 SqlDataReader 처럼 똑같은 결과로 DataTable의 모든 값을 뿌려주는 예제를 해보자.

 void Page_Load()
 {
  string str_conn = "server=notebook-PC\\STUDY;user id=sa;password=1121;database=aspnet;";
  SqlConnection conn = new SqlConnection(str_conn);
  conn.Open();

  string QUERY = "SELECT * FROM member_table";
  SqlDataAdapter da = new SqlDataAdapter(QUERY, conn);
  DataSet ds = new DataSet();
  da.Fill(ds);

  // DB연동작업 끝
  conn.Close();

  DataTable dt = ds.Tables[0];  
  for (int i=0; i<dt.Rows.Count; i++ )
  {
   Response.Write(String.Format("사용자 : {0} (이름:{1}, 비밀번호:{2})<br>등록일:{3}<hr>",
    dt.Rows[i][0], dt.Rows[i]["user_name"], dt.Rows[i][1], dt.Rows[i][3] ));
  }

}

파란색은 DB서버에 연결하여 쿼리를 날린 결과를 DataSet 인 ds에 채우는 부분이다. 그리고 채운 후 바로 DB연결을 닫아주었다. 이미 결과는 DataSet 인 ds 에 담겨져 있기 때문에 DB연결은 더 이상 필요없기 때문이다.

빨간색이 실제 자료를 처리하는 코드이다. DataTable 인 dt 에 테이블을 참조해서(짧게 코딩하기 위해) SqlDataReader 처럼 똑같이 뿌려주는데 달라진 것은 루프를 for(;;) 로 돌리는 것이다.

DataTable 은 DataTable.Rows 라는 컬렉션이 있기 때문에 모든 자료를 훑어오는 방법은 현재 dt.Rows.Count (가지고 있는 레코드 수)만큼 루프를 돌리면 된다. 그리고 각각의 컬럼은 간단하게 dt.Rows[현재레코드번호][컬럼컬렉션] 형식으로 읽어오는 것을 볼 수 있다.

DataTable은 SqlDataReader 와 다르게 특정 자료를 그대로 읽어올 수 있다.

dt.Rows[2][3] - 세 번째 레코드의 세 번째 컬럼인 "닷넷초보"
dt.Rows[0][1] - 첫 번째 레코드의 첫 번째 컬럼인 "test_id" 
dt.Rows[1]["user_password"] - "1234" 

DataTable 에 대해 조금은 이해가 되는가?

우리가 주목해야 될 것은, 이 모든 자료들은 쿼리문의 결과라는 점이다. 누차 말하지만 닷넷에서 특별하게 자료를 생성한 것이 아니다. 그저 닷넷에서는 DB서버로 쿼리를 날리고, 그 결과를 몇 가지 방법으로 읽을 수 있다.

이번 시간의 목적은 SELECT 로 리턴되는 결과가 1개가 아닌 2개 이상인 것을 처리해보는 것인데, 결론을 말하자면 SqlDataAdapter를 이용하여 DataSet 을 만들어 DataTable을 사용하자는 것이다. 이유는 사용하기 쉽고 확장성이 뛰어나기 때문이다. 또한 비연결 지향으로 자원 낭비가 적다는 것이다. 계속 열려있는 상태라면 아무래도 그 동안에는 서버 리소스(메모리 등)가 소모될 것이다.

다음 소스를 작성하여 실해보자. db4.aspx 로 파일명을 변경하기로 한다.

db4.aspx

<%@ Page Language="C#" runat="server" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>

<script language="C#" runat="server">

 void Page_Load()
 {
  string str_conn = "server=notebook-PC\\STUDY;user id=sa;password=1121;database=aspnet;";
  SqlConnection conn = new SqlConnection(str_conn);
  conn.Open();

  
  // DB연동작업 시작
  string QUERY = "SELECT * FROM member_table";
  SqlDataAdapter da = new SqlDataAdapter(QUERY, conn);
  DataSet ds = new DataSet();
  da.Fill(ds);

  // DB연동작업 끝
  conn.Close();

  dg1.DataSource = ds.Tables[0];
  dg1.DataBind();
 
 }

</script>

<form runat="server">
<ASP:DataGrid id="dg1" runat="server" />
</form>

별 다른 것 없지만, 진하게 표시된 부분을 보자.

디자인 단에 웹폼인 DataGrid 서버 컨트롤을 dg1 이라는 ID로 배치했다.
그리고 dg1에 DataSource 속성으로 우리가 가져온 자료의 DataTable 을 지정하여 DataBind() 메서드를 호출하여 실제 자료를 바인드(결속, 묶어준다는 의미)해준다.

일단 결과를 보자.

이렇게 쉽게 자료가 그대로 박히는 것을 볼 수 있다. DataGrid 도 디테일하게 디자인을 지정할 수 있으므로 지금은 허접해 보이지만 화장빨 좀 받으면 이뻐질 것이다. ^^

asp.net 에는 이런 DataGrid 처럼 비슷한 자료처리를 위한 웹폼이 많이 존재한다. 그리고 이들은 DataTable 과 같은 자료 소스에 직접 DataItem으로 지정 할 수 있는 장점이 있다.
사실 앞서 해본 SqlCommand.ExecuteReader() 메서드의 결과인 SqlDataReader 자체로도 데이터아이템으로 사용할 수 있다. 그러나 말했듯이 DB가 연결중이어야 한다는 치명적인 단점이 있다. DB가 연결중일 때는 모듈화를 시킬 수가 없다. 모듈은 현재 프로그램과는 분리된 다른 곳에 있는 녀석이기 때문이다.


마지막 정리를 해보자.

1. SqlConnection 으로 DB를 연결/연결종료.
2. INSERT, DELETE, UPDATE 쿼리문은 SqlCommand.ExecuteNonQuery() 메서드를 이용.
3. SELECT 쿼리 중 단일(1개)값 리턴에는 SqlCommand.ExecuteScalar() 메서드 이용.
4. 이외의 SELECT 쿼리결과는 SqlDataAdapter 로 DataSet 을 만들어 DataTable을 사용.

이제 여러분의 노력이 결실을 맺을 시간이 온 것 같다.
닷넷 서적을 보시든 MSDN을 보시든 타 사이트를 참고하시든 위에 나열된 클래스를 최대한 자유롭게 이용할 수 있어야 한다. asp.net 와 DB(서버)는 아~무 관련없다는 것을 꼭 상기하자. 필요에 의해서 연동할 뿐이다. DB는 저 혼자 가만히 앉아 있는 느티나무 같은 서버 프로그램일 뿐이다.


다음시간에..