ADO.NET 데이터베이스 프로그래밍

ADO.NET은 응용 프로그램에서 데이터베이스 프로그래밍을 도와주는 클래스들의 집합을 의미한다.

System.Data.SqlClient 네임스페이스와 System.Data.OleDb 네임스페이스

ADO.NET은 데이터 공급자라는 개념을 통해서 서로 다른 데이터베이스와 연동하는 프로그래밍을 할 수 있는데 각각의 DBMS와 관련된 명령들을 서로 다른 네임스페이스로 관리한다.

  • System.Data.SqlClient 네임스페이스

    ASP.NET에서 데이터베이스에 대한 모든 처리를 담당하는 클래스들을 모아 놓은 네임스페이스이다.

  • System.Data.OleDb 네임스페이스

    ASP.NET에서 SQLServer 데이터베이스를 포함하고 Microsoft Access 또는 Oracle과 같은 데이터베이스 관련 크래스들을 묶어 놓은 네임스페이스이다.

  • System.Data.Odbc 네임스페이스

    텍스트 파일 또는 ODBC 호환 데이터 원본에 접근하기 위한 클래스들을 제공한다.

  • System.Data.OracleClient 네임스페이스

    Oracle 데이터베이스에 접근하기 위한 주요 클래스들을 제공한다.

데이터베이스 연결문자열 : Connection String

C#, ASP.NET과 같은 프로그래밍 계층에서 SQL Server와 같은 데이터베이스 계층에 접근해서 데이터를 사용하고자 할 때는 데이터베이스 연결 문자열이 필요하다.
(정확한 데이터를 넘겨주어야 해당 데이터베이스에 접근가능함)

데이터베이스 연결 문자열의 필수 구성 요소 네가지는 다음과 같다.

  • Server(DataSource)

    데이터베이스 서버의 위치를 나타낸다. IP 주소 혹은 도메인 정보를 입력한다.

  • Database (DB, Initial Catalog)

    데이터베이스의 이름을 지정한다.

  • User ID (UID)

    데이터베이스에 대한 권한에 있는 사용자 아이디를 입력한다.

  • Password (PWD)

    데이터베이스에 대한 권한이 있는 사용자 비밀번호를 입력한다.

SQL Server에 접근할 수 있는 데이터베이스 연결 문자열은 다음과 같은 스타일로 지정한다.

  • Server=localhost; Initial Catalog=master; Integrated Security=true;
  • Server=localhost; Database=master; User ID=UserId; Passsword=xyz;

서버를 지정할 때는 다음과 같이 프로토콜, 서버명(IP 주소), 인스턴스명, 포트번호 등을 지정할 수 있다 .

  • Server=tcp;ServerName\InstanceName,1433

닷넷에서 데이터베이스 처리 관련 주요 클래스

  • Connection 클래스

    : DB 연결 및 종료

  • Command 클래스

    : DB 명령 실행

  • DataReader 클래스

    : Select 구문의 실행결과값 받기

  • DataSet 클래스

    : 메모리 상의 데이터베이스(DB)로 Select와 같은 결과값 저장

  • DataAdapter 클래스

    : 명령어 전달 및 실행 후 값을 DataSet 클래스에 전달

  • DataTable 클래스

    : DataSet 안에 들어있는 메모리 상의 테이블 (Table)

  • DataView 클래스

    : DataSet 안에 들어있는 메모리 상의 뷰(View)

SqlConnection 클래스

SQL Server에 연결해서 어떤 작업을 수행하려면, SqlConnection 클래스를 사용해야 하고, Access DB에 연결해서 작업을 할 때는 OldDbConnection 클래스를 사용해야 한다.

SqlConnection 클래스의 주요 속성 및 메서드는 다음과 같다.

  • ConnectionString

    데이터베이스 연결 문자열을 설정해 SQL Server를 연결한다.

  • State

    데이터베이스와의 연결 상태를 나타내는 ConnectionState 열거형을 반환하다.
    ConnectionState.Open : 연결된 상태
    ConnectionState.Closed : 닫혀있는 상태

  • Open()

    설정된 데이터베이스 연결 문자열을 사용해 데이터베이스를 연결한다.

  • Close()

    현재 연결된 데이터베이스의 연결을 해제한다.

SQL Server 연결 테스트 예제

FrmSqlConnection.aspx 

<body>
	<form id="form1" runat="server">
		<div>
			<asp:Button ID="btnSqlConnection" runat="server" Text="SQL Server에 연결" OnClick="btnSqlConnection_Click" />
			<hr />
			<asp:Label ID="lblDisplay" runat="server"> </asp:Label>
		</div>
	</form>
</body>

FrmSqlConnection.aspx.cs 

using System;
using System.Configuration;
using System.Data.SqlClient;
namespace DevADONET
{
	public partial class FrmSqlConnection : System.Web.UI.Page
	{
	}
	protected void Page_Load(object sender, EventArgs e)
	{
	}
	protected void btnSqlConnection_Click(object sender, EventArgs e)
	{
		// [1] SqlConnection 클래스의 인스턴스 생성
		SqlConnection con = new SqlConnection();
		// [2] ConnectionString 속성 지정
		conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectinString;
		// [3] Open() 메서드 실행 : 데이터베이스 연결
		con.Open();
		// [!] 실행
		lblDisplay.Text = "데이터베이스 연결 성공";
		// [4] Close() 메서드 실행 : 데이터베이스 연결 종료
		con.Close();
	}
}

SqlCommand 클래스

ASP.NET에서 SQL Server에 Create, Alter, Drop, Insert, Select, Update, Delete 같은 구문을 전달하고 실행하고자 할 때는 SqlCommand 클래스를 사용한다.

SqlCommand 클래스의 주요 속성 및 메서드는 다음과 같다.

  • Connection

    미리 설정되어 있는 커넥션 개체를 지정한다.

  • CommandText

    실행할 SQL문이나 SP(저장 프로시저)문을 설정한다.

  • CommandType

    CommandText 속성에서 지정한 구문의 형식을 CommandType 열거형으로 반환
    CommandType.Text : 일반적인 SQL문
    CommandType.StoredProcedure : 저장 프로시저 구문
    CommandType.TableDirect : Accss DB 전용 (테이블명)

  • ExecuteNonQuery()

    Select문 이외의 구문을 실행하고자 할 때 주로 사용
    테이블의 행에 영향을 미친 개수만큼 정수값을 반환한다

  • ExecuteReader()

    Select문을 실행하고 그 결과를 SqlDataReader 개체로 반환한다
    다중값 반환 : 레코드의 집합을 반환

  • ExecuteScalar()

    Select문을 실행하고 첫번째 열(필드) 값을 반환한다.
    단일값 반환 : 주로 집계 함수의 결과를 반환

SqlCommand 사용 예제

Memos.sql

Create Table dbo.Memos
(
	Num		  Int Identity(1,1)	   Primary Key,         -- 번호
	Name	  NVarChar(25)		   Not Null,	        -- 이름
	Email	  NVarChar(100)		   Null,		        -- 이메일
	Title	  NVarChar(150)		   Not Null,	        -- 메모
	PostDate  DateTime			   Default(GetDate()),  -- 작성일
	PostIP	  NVarChar(15)		   Null					-- IP 주소
)

FrmSqlCommand.aspx

<body>
	<form id="form1" runat="server">
		<div>
			<asp:Button ID="btnSqlCommand" runat="server" Text="입력 예시문 실행" OnClick="btnSqlCommand_Click" />
			<hr />
			<asp:Label ID="lblDisplay" runat="server"> </asp:Label>
		</div>
	</form>
</body>

FrmSqlCommand.aspx.cs 

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DevADONET
{
	public partial class FrmSqlCommand : System.Web.UI.Page
	{
		protected void Page_Load(object sender, EventArgs e)
		{
	
		}
	
		protected void BtnSqlCommand_Click(object sender, EventArgs e)
		{
			SqlConnection con = new SqlConnection();
			con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			con.Open();
		
			// [1] SqlCommand의 인스턴스 생성
			SqlCommand cmd = new SqlCommand();
			// [2] Connection 속성 지정
			cmd.Connection = con;
			// [3] CommandText 속성 설정
			cmd.CommandText = @"Insert Into Memos Values(N'이름', N'email@github.om', N'Hello', GetDate(), '127.0.0.1')";	
			// [4] CommandType 속성 지정
			cmd.CommandType = CommandType.Text;
			// [5] ExecuteXXX() 메서드로 실행
			cmd.ExecuteNonQuery();
			// [6] 마무리
			lblDisplay.Text = "데이터 저장 완료";
			con.Close();
		}
	}
}

SqDataReader 클래스

ASP.NET에서 커맨드 개체를 사용해 SQL Server에 Select문을 실행한 결과를 받아올 때 SqlDataReader를 사용한다.
Select 문의 결과를 화면에 출력하려고 반드시 SqlDataReader 클래스를 사용해야하는 것은 아니다. (Data 컨트롤을 사용해 쉽게 구현하는 방법도 있음!)

SqlDataReader 클래스의 주요 속성 및 메서드는 다음과 같다.

  • FieldCount

    Select 문의 실행 결과에서 필드(열)의 개수

  • HasRows

    Select 문을 실행한 후 반환될 레코드가 있다면 true

  • Read()

    레코드가 있는 만큼 반복

  • Close()

    데이터 리더 개체를 닫는다

  • GetXXX()

    XXX 형식으로 필드(칼럼)값을 반환한다

SqlDataReader 사용 예제

FrmSqlDataReader.aspx

<body>
	<form id="form1" runat="server">
		<div>
			<asp:Literal ID="tblOutput" runat="server"> </asp:Literal>
		</div>
	</form>
</body>

FrmSqlDataReader.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
namespace DevADONET
{
	public partial class FrmSqlDataReader : System.Web.UI.Page
	{
		protected void Page_Load(object sender, EventArgs e)
		{
			if (!Page.IsPostBack)
			{
				DsiplayData();
			}
		}
		private void DisplayData()
		{
			SqlConnection con = new SqlConnection();
			con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			con.Open();
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = con;
			cmd.CommandText = @"Select Num, Name, Email, Title, PostDate, PostIp From Memos Order By Num Desc";
			cmd.CommandType = CommandType.Text;
			// [1] SqlDataReader 형식의 개체로 결과값 받기
			SqlDataReader dr = cmd.ExecuteReader();
			// [2] Read() 메서드로 데이터 있는만큼 반복
			string strOutput = "<table> <tr> <td> 번호 </td> <td> 이름 </td> <td> 메모 </td> <td> 작성일 </td> </tr> ";
			while (dr.Read())
			{
				strOutput += $"<tr> <td> {dr["Num"]} </td>" + $"<td> {dr[1]} </td>" + $"<td> {dr.GetString(3)} </td>"
								+ $"<td> {dr.GetDataTime(4).ToShortDataString()}</td>";
			}	
			strOutput += "</table>";
			// [3] Close() 메서드로 연결된 리더 개체 종료
			dr.close();
			// [!] 출력
			tblOutput.Text = strOutput;
			con.Close();
			// end of DisplayData()
		}
	}
}

SqlDataAdapter 클래스와 DataSet 클래스

DataSet 클래스는 ‘메모리 데이터베이스’로 정의할 수 있다.
즉, 메모리에 필드와 레코드로 이루어진 테이블을 여러개 올려놓는다. 그리고 해당 테이블 간의 관계를 설정하고, 각각의 테이블에서 데이터를 입력, 출력, 수정, 삭제, 검색하는 일반적인 데이터베이스 처리 로직을 구현한다.

DataSet 클래스가 Select문의 실행 결과를 담아 놓는 그릇 역할을 한다면, SqlDataAdapter 클래스를 Select문을 실행시키고 실행된 결과값을 가져다가 DataSet 개체에게 채워주는 중간 매개체 역할을 한다.

SqlDataAdapter와 DataSet 클래스로 데이터 출력하기

FrmSqlDataAdapter.aspx

<body>
	<form id="form1" runat="server">
		<div>
			<asp:GridView ID="ctlMemoLists" runat="server"> </asp:GridView>
		</div>
	</form>
</body>

FrmSqlDataAdapter.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DevADONET
{
	public partial class FrmSqlDataAdapter : System.Web.UI.Page
	{
		protected void Page_Load(object sender, EventArgs e)
		{
			SqlConnection con = new SqlConnection();
			con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			con.Open();
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = con;
			cmd.CommandText = @"Select Num, Name, Email, Title, PostDate, PostIP From Memos Order By Num Desc";
			cmd.CommandType = CommandType.Text;
			// [1] DataAdapter
			SqlDataAdapter da = new SqlDataAdapter();
			// [2] SelectCommand 지정
			da.SelectCommand = cmd;
			// [3] DataSet : 메모리상의 데이터베이스
			DataSet ds = new DataSet();
			// [4] Fill() 메서드로 DataSet 채우기
			ds.Fill(ds, "Memos");
			// [!] 출력 
			ctlMemoLists.DataSource = ds.Table[0].DefaultView;	// ds만 지정가능
			ctlMemoLists.DataBind();
			con.Close();
		}
	}
}

참고(ASP.NET & Core를 다루는 기술 )