ADO.NET 데이터베이스 프로그래밍
ADO.NET은 응용 프로그램에서 데이터베이스 프로그래밍을 도와주는 클래스들의 집합을 의미합니다. 닷넷 기반에서 데이터베이스 처리를 위한 가장 기본이 되는 ADO.NET의 주요 클래스들에 대한 사용법을 공부해보겠습니다.
ADO.NET Fundamentals
박용준 강사가 작성한 ADO.NET 관련 학습 자료는 다음 링크를 참고하세요.
ADO.NET 강의 소스 모음
다음 GitHub 경로에서 ADO.NET 강의 관련 소스를 다운로드 받을 수 있습니다.
1. ADO.NET 개요
.NET 개발 환경에서 SQL Server, Access, MySQL, Postgres 같은 관계형 DBMS(DataBase Management System)에 접근해서 데이터를 입력, 출력(조회), 수정, 삭제 등의 처리를 하고자 할 때 이미 만들어 놓은 데이터베이스 처리 관련 클래스들의 집합인 ADO.NET을 사용하면 손쉽게 처리할 수 있습니다.
ADO.NET도 .NET Framework의 .NET 4.8과 마찬가지로 그 버전이 4.8로 향상되었습니다. 닷넷코어 환경에서는 각각의 버전에 맞게 계속 버전이 업데이트되고 있습니다. 각 데이터베이스마다 완전히 다른 명령어를 사용하는게 아니라 일괄적으로 같은 패턴의 클래스를 사용해서 프로그래밍을 할 수 있도록 도와줍니다.
NOTE
ORM 도구인 Entity Framework Core는 ADO.NET을 기반으로 합니다. 즉, ADO.NET을 좀 더 사용하기 편하도록 만들어진 기술이 Dapper와 Entity Framework입니다.
2. System.Data.SqlClient 네임스페이스와 System.Data.OleDb 네임스페이스
ADO.NET은 데이터 공급자(Data Provider)라는 개념을 통해서 서로 다른 데이터베이스와 연동하는 프로그래밍을 할 수 있는데 각각의 DBMS와 관련된 명령어들을 서로 다른 네임스페이스로 관리합니다.
System.Data.SqlClient네임스페이스- ASP.NET에서 SQL Server 데이터베이스에 대한 모든 처리를 담당하는 클래스들을 모아 놓은 네임스페이스입니다. 학습은 이걸로 하되 실제 사용은 최근 새롭게 개발된
Microsoft.Data.SqlClient패키지 사용을 추천합니다.
- ASP.NET에서 SQL Server 데이터베이스에 대한 모든 처리를 담당하는 클래스들을 모아 놓은 네임스페이스입니다. 학습은 이걸로 하되 실제 사용은 최근 새롭게 개발된
System.Data.OleDb네임스페이스- ASP.NET에서 SQL Server 데이터베이스를 포함하고 Microsoft Access 같은 데이터베이스 관련 클래스들을 묶어 놓은 네임스페이스입니다.
System.Data.Odbc네임스페이스- 텍스트파일 또는 ODBC 호환 데이터 원본에 접근하기 위한 클래스들을 제공합니다.
System.Data.OracleClient네임스페이스- Oracle 데이터베이스에 접근하기 위한 주요 클래스들을 제공합니다. 최근에는 ODP.NET으로 대체되었습니다.
- ODP.NET
- 오라클에서는 따로 ODP.NET(Oracle Data Provider for .NET) 이름으로 자사의 Oracle 데이터베이스를 .NET에서 사용하도록 관련 클래스들을 제공합니다. 이와 관련해서는 검색 엔진에서 "ODP.NET"을 검색해보겠습니다. 참고로 .NET Core 전용 ODP.NET Core도 있습니다.
ADO.NET의 주요 클래스들은 다음 샘플 명령처럼 NuGet 패키지를 사용하여 프로젝트에 포함하여 사용될 수 있습니다.
dotnet add package Microsoft.Data.SqlClient
dotnet add package Microsoft.Data.Sqlite
IMPORTANT
박용준 강사의 강의에서는 SQL Server를 사용하지만, ADO.NET은 SQL Server, MySQL, SQLite, Postgres, XML 등의 많은 종류의 데이터를 사용합니다.
3. Microsoft SQL Server
마이크로소프트사의 데이터베이스 관리 시스템(DBMS)은 SQL Server입니다. SQL Server의 최신 버전은 현재 SQL Server 2019(2014, 2016, 2017)입니다. 단, 강의의 모든 내용은 SQL Server 2012 이상(SQL Server 2008 R2 가능)에서 실행됩니다. 이 강의에서 데이터베이스 관련 처리는 모두 SQL Server를 사용합니다. ASP.NET을 설명하는 강의이므로 SQL Server에 대한 설명은 따로 하지 않았습니다.
다음 경로에서 SQL Server에 대한 정보를 얻을 수 있습니다.
SQL Server 관련해서는 로컬 컴퓨터에 직접 설치하는 대신 마이크로소프트사의 클라우드 서비스인 애저(Azure)의 SQL 데이터베이스 또는 SQL Server 가상 컴퓨터를 사용할 수도 있습니다.
4. SQL Server LocalDB
Visual Studio 설치 시 함께 설치되는 SQL LocalDB를 사용하면 SQL Server 정식 버전이 없어도 Visual Studio의 개발환경에서 DB 프로그래밍을 할 수 있습니다. 웹 사이트가 모두 개발된 후에 정식 SQL Server에 올려서 서비스를 진행하면 됩니다.
SQL Server는 .NET 프로그래밍을 위한 최적의 데이터베이스입니다.
이 문서(강의)는 SQL Server LocalDB를 기준으로 설명합니다.
참고로, SQL Server LocalDB 대신에 SQLite 등의 다른 데이터베이스를 사용해도 됩니다.
SQL Server LocalDB 인스턴스(데이터베이스 엔진) 이름
SQL Server LocalDB는 여러 가지 이름으로 구성될 수 있습니다. 간단히 정리해보면 다음과 같습니다.
- (localdb)\MSSQLLocalDB – SQL Server LocalDB의 기본 인스턴스 이름
- (localdb)\Projects13 – SQL Server Data Tools(SSDT)에 의해서 생성된 인스턴스 이름
- (localdb)\v11.0 – SQL Server 2012 LocalDB 기본 인스턴스 이름
위에서 기준이 되는 인스턴스 이름은 (localdb)\MSSQLLocalDB입니다.
참고로 다음 그림은 나의 PC에서 명령 프롬프트를 열고 sqllocaldb info 명령어를 입력했을 때 나타나는 인스턴스 이름 목록입니다. 여러가지 버전의 툴이 설치되어 있는데 MSSQLLocalDB 항목만 나오면 정상적입니다.
그림: 설치된 LocalDB 정보
C:\>sqllocaldb info
MSSQLLocalDB
ProjectsV13
C:\>sqllocaldb info mssqllocaldb
이름: MSSQLLocalDB
버전: ## 12.0.2000.8
공유 이름:
소유자: ALIENAURORA\redpl
자동 생성: 예
상태: 중지됨
마지막 시작 시간: 2016-07-24 오후 4:56:34
인스턴스 파이프 이름:
C:\>
참고: SQL LocalDb 관련 명령어
- sqllocaldb
- sqllocaldb info
- sqllocaldb info mssqllocaldb
로컬 데이터베이스와 원격(리모트) 데이터베이스
응용 프로그램을 개발하는 단계에서는 일반적으로 로컬에 설치된 데이터베이스를 사용하고 완성된 응용 프로그램을 게시해서 운영할 때에는 리모트 데이터베이스를 사용합니다.
IMPORTANT
이 문서의 모든 내용은 데브렉(http://www.devlec.com) 사이트에서 동영상 강의로 제공되고 있습니다.
5. SQL Server 개체 탐색기
Visual Studio는 SQL Server Management Studio의 개체 탐색기(Object Explorer)에 해당하는 SQL Server 개체 탐색기를 제공합니다. Visual Studio의 보기 - SQL Server 개체 탐색기를 선택하면 됩니다. 이 도구는 SQL Server 정식 버전의 SQL Server Managerment Studio(SSMS)의 개체 탐색기와 기능이 같습니다. SSMS에 익숙하다면 Visual Studio의 SQL Server 개체 탐색기 대신에 SSMS를 사용해 데이터베이스를 관리해도 무관합니다.
다음 그림은 Visual Studio의 SQL Server 개체 탐색기에서 SQL Server LocalDB에 연결한 모습입니다.
그림: SQL Server 개체 탐색기에서 LocalDB에 연결
로컬 PC에 설치된 LocalDB의 버전과 인스턴스 정보를 얻으려면 다음과 같은 명령어를 실행합니다.
그림: SQL Server LocalDB 버전과 정보 얻기
C:\>sqllocaldb v
Microsoft SQL Server 2014 (## 12.0.2000.8)
Microsoft SQL Server 2016 (13.0.1601.5)
C:\>sqllocaldb i
MSSQLLocalDB
ProjectsV13
C:\>
6. 데이터베이스 연결 문자열: Connection String
닷넷(C#, ASP.NET, Windows Forms, WPF, ...) 프로그래밍 계층에서 SQL Server와 같은 데이터베이스 계층에 접근해서 데이터를 사용하고자 할 때는 데이터베이스 연결 문자열(Connection String)이 필요합니다. 따라서 정확한 데이터를 넘겨주어야 해당 데이터베이스에 접근할 수 있습니다.
데이터베이스 연결 문자열의 필수 구성 요소 네 가지
데이터베이스 연결 문자열의 필수 구성 요소 네 가지는 다음과 같습니다.
- Server(Data Source): 데이터베이스 서버의 위치를 나타냅니다. IP 주소 또는 도메인 정보를 입력합니다.
- 각각의 항목은 Server 또는 Data Source 중 필요한 것을 선택적으로 사용 가능합니다.
- Database(DB, Initial Catalog): 데이터베이스의 이름을 지정합니다.
- User ID(UID): 데이터베이스에 대한 권한에 있는 사용자 아이디를 입력합니다.
- Password(PWD): 데이터베이스에 대한 권한이 있는 사용자 비밀번호를 입력합니다. 비밀번호에 입력하는 값은 대소문자를 구별하니 주의합니다.
로컬 PC에 설치된 데이터베이스 엔진에 접근할 때에는 User ID와 Password 대신에 Integrated Security=True 또는 Trusted_Connection=True 값을 사용하면 됩니다.
NOTE
데이터베이스 연결 문자열 가져오기
SQL Server 개체 탐색기를 열고 특정 데이터베이스 선택 후 속성 창(F4 단축키)을 열면 Connection string 항목을 볼 수 있습니다. 이 값을 복사해서 사용하면 좀 더 빠르게 데이터베이스 연결 문자열을 이용할 수 있습니다.
다음 그림은 SQL Server 개체 탐색기로 LocalDB에 접속 후 VisualAcademy 이름의 데이터베이스를 만들고 속성 창에서 데이터베이스 연결 문자열을 확인하는 내용을 보여줍니다.
System.Data.SqlClient를 위한 데이터베이스 연결 문자열 샘플
SQL Server에 접근할 수 있는 데이터베이스 연결 문자열은 다음과 같은 스타일로 지정합니다.
- Server=localhost; Initial Catalog=master; Integrated Security=true;
- Server=localhost; Database=master; User ID=UserId; Password=xyz;
로컬 DB와 같이 Windows에 설치되어 있는 자신의 DB 엔진에 접근할 때에는 데이터베이스 서버의 아이디와 암호를 입력할 필요없이 Integrated Security=true; 항목을 넣어주면 됩니다. 원격 DB에 접근할 때에는 해당 서버에 접근할 수 있는 권한을 User ID와 Password 항목으로 정확히 입력해 주어야 합니다.
NOTE
데이터베이스 연결 문자열은 Password(pwd)의 값만 빼고 대소문자를 구분하지 않습니다.
가장 많이 사용하는 데이터베이스 연결 문자열은 아래와 같은 형태를 가진다.
- server=127.0.0.1;database=Board;uid=sa;pwd=1234;
서버를 지정할 때는 다음과 같이 프로토콜, 서버명(IP 주소), 인스턴스명, 포트번호 등을 설정할 수 있습니다.
- Server=tcp:ServerName\InstanceName,1433
예를들어 로컬 DB의 DotNetNote 데이터베이스에 Windows 인증으로 접속할 때 사용할 수 있는 데이터베이스 연결 문자열은 다음과 같이 표현할 수 있습니다.
- Server=(localdb)\mssqllocaldb;Database=DotNetNote;Trusted_Connection=True;
추가적인 정보는 아래 링크의 Micorosft Docs를 참고하면 됩니다.
ADO.NET의 연결 문자열:
참고로, Azure SQL Database 등을 사용할 때에는 Azure Portal의 관리 도구에서 제공해주는 데이터베이스 연결 문자열을 그대로 가져다가 암호만 추가로 입력해서 사용하면 됩니다. 이때에는 4가지 필수 정보 이외에 추가적인 옵션이 제공됩니다.
7. 닷넷에서 데이터베이스 처리 관련 주요 클래스
닷넷 프레임워크 또는 닷넷 코어에서 데이터베이스 처리 관련 주요 클래스 이름들은 다음과 같습니다. SqlConnection 또는 OleDbConnection 등의 클래스는 공통으로 Connection 클래스로 표현하겠습니다.
- Connection 클래스: 데이터베이스 연결 및 종료
- Command 클래스: 데이터베이스에 명령(DDL + DML) 실행
- DDL: Create, Alter, Drop
- DML: Insert, Select, Update, Delete
- Parameter 클래스: Command 클래스의 명령에 매개 변수 전달
- DataReader 클래스: Select 구문의 실행 결괏값 받기
- DataSet 클래스: 메모리 상의 데이터베이스(Database)로 Select와 같은 결괏값을 저장
- DataAdapter 클래스: 명령어 전달 및 실행 후 값을 DataSet 클래스에게 전달
- DataTable 클래스: DataSet 안에 들어 있는 메모리 상의 테이블(Table)
- DataView 클래스: DataSet 안에 들어 있는 메모리 상의 뷰(View)
Connection 클래스들은 IDbConnection 인터페이스를 상속합니다. SQL Server에 접속할 때 사용하는 SqlConnection 클래스와 Microsoft Access에 접속할 때 사용하는 OleDbConnection은 이름은 다르지만 두 클래스의 멤버들은 모두 IDbConnection 인터페이스를 상속하므로 동일합니다.
콘솔에서 ADO.NET 사용하여 데이터베이스에 데이터 입력과 출력 데모
// TODO: 라이브 데모 진행 영역
8. 앱 제작에 필요한 데이터베이스 처리 패턴 여섯 가지
앱(Application)을 제작할 때 다음 여섯 가지 패턴만 정확히 이해하고 있으면 모든 페이지 제작에 응용해서 사용할 수 있습니다. 웹 페이지 작성시 파일명 또는 기능명에 많이 사용하는 단어는 Empty, Create, Edit, Delete, Details, List 등을 많이 사용합니다.
- 입력(Write, Add, Create): 게시판 글쓰기, 상품 등록 등의 페이지에 적용, Insert문 사용
- 출력(List, Get, Index): 게시판 리스트, 상품 진열 등의 페이지에 적용, Select문 사용
- 상세(View, Details): 게시판 상세 보기, 상품 상세 보기 등의 페이지에 적용, Select문 사용
- 수정(Modify, Edit): 게시판 수정, 회원 정보 수정 등의 페이지에 적용, Update문 사용
- 삭제(Delete, Remove): 게시판 삭제, 회원 탈퇴 등의 페이지에 적용, Delete문 사용
- 검색(Search, Find): 게시판 검색, 상품 검색 등의 페이지에 적용, Select문 사용
9. [실습] DB 프로그래밍 학습을 위한 프로젝트 및 데이터베이스 구성
소개
ADO.NET를 학습하기에 앞서 DB를 구성하는 과정을 진행해보겠습니다. DBMS는 SQL Server LocalDB를 사용할 것입니다. SQL Server 2014, 2016, 2017, 2019 같은 정식 버전에 대한 경험이 많은 독자라면 해당 버전을 사용해도 무관합니다. 책에서는 SQL Server Management Studio를 사용하지 않고 Visual Studio에 포함된 SQL Server Object Explorer(개체 탐색기)를 사용할 것입니다.
NOTE
이번 실습 과정을 "ADO.NET 데이터베이스 프로그래밍 학습을 위한 프로젝트 생성 및 데이터베이스 구성" 제목의 동영상 강좌로도 마련하였으니 참고하기 바랍니다.
따라하기
(1) Visual Studio를 실행하고 ASP.NET 웹 응용 프로그램을 다음과 같이 생성합니다. 생성된 프로젝트에 T-SQL 구문(*.sql)을 모아 넣을 공간인 Documents 폴더를 하나 생성합니다. 참고로 16장을 배우고나면 이러한 SQL 파일들은 모두 "SQL Server 데이터베이스 프로젝트"에 모아서 보관하게 됩니다.
| 이름 | 위치 | ASP.NET 템플릿 선택 | 폴더 및 핵심 참조 추가 |
|---|---|---|---|
| DevADONET | C:\ASP.NET | ASP.NET 4.8 템플릿-Empty | Web Forms |
(2) Visual Studio 메뉴 중 <보기 > SQL Server 개체 탐색기>를 선택하면 SQL Server 개체 탐색기(Object Explorer)를 화면에 나타난다. Visual Studio의 SQL 개체 탐색기는 정식 DBMS, 즉 SQL Server 2014나 2016, 2017의 SQL Server Management Studio(SSMS)와 같은 역할을 합니다. 처음 SQL Server 개체 탐색기를 프로젝트 단위로 열면 데이터베이스 인스턴스가 여러 개 연결되어 보이는데 이 책에서는 Visual Studio 2017의 기본 내장 LocalDB 인스턴스명인 MSSQLLocalDB를 사용하겠습니다. 다음 그림은 기본 LocalDB 인스턴스가 연결된 SQL Server 개체 탐색기다.
그림: SQL Server 개체 탐색기 실행
(3) SQL Server 개체 탐색기의 <데이터베이스> 폴더에 마우스 오른쪽 버튼을 클릭하여 <새 데이터베이스 추가>를 선택합니다. 그러면 다음 그림과 같이 DevADONET으로 데이터베이스를 생성합니다.
그림 12-5 DevADONET 데이터베이스 만들기
데이터베이스 만들기 창이 뜨면 데이터베이스의 이름과 생성 위치를 지정합니다. 참고로 내가 설정한 값은 다음과 같습니다.
- 데이터베이스 이름: DevADONET
- 데이터베이스 생성 위치(각 컴퓨터마다 사용 환경이 다름):
- C:\Users\용준\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB
그림: 데이터베이스 이름과 생성 위치 지정
(4) SQL Server 개체 탐색기에서 DevADONET 데이터베이스에 마우스 오른쪽 버튼을 클릭하고 <새 쿼리> 메뉴를 선택하면 쿼리 입력할 수 있는 쿼리 편집기 창이 열린다. 쿼리 편집기에서 [Ctrl]+[S]을 입력하여 저장합니다. 앞에서 만든 DevADONET 프로젝트의 Documents 폴더에 Memos.sql이라는 이름으로 저장합니다. 다음 그림은 Documents 폴더에 Memos.sql 파일이 저장된 모습을 나타냅니다. 프로젝트 이외의 환경에서 저장되었을 때는 솔루션 탐색기에 나타나지 않기 때문에 솔루션 탐색기 상단의 <모든 파일 표시> 아이콘을 클릭해야 나타난다. Memos.sql 파일에 마우스 오른쪽 버튼을 클릭해서 <프로젝트에 포함> 메뉴를 클릭하면 DevADONET 프로젝트에서 사용되는 SQL 구문을 Documents 폴더에 모아서 관리할 수 있습니다. 뒤에서 다루겠지만, SQL Server 데이터베이스 프로젝트란 이름의 프로젝트를 솔루션에 추가해서 관리할 수도 있습니다. 이 프로젝트는 SQL 구문들을 모두 한 프로젝트 단위로 관리하고 배포할 수 있습니다.
그림: Memos.sql 파일을 프로젝트에 추가하기
(5) 다시 SQL Server 개체 탐색기의 쿼리 편집기 영역으로 돌아와서 Memos.sql 파일에 다음의 긴 SQL 구문을 작성합니다. Memos 테이블을 만들고, 테이블에 데이터를 입력, 출력, 상세보기, 수정, 삭제, 검색하는 로직에서 사용할 만한 예시 구문을 만들어보고, 이에 대한 저장 프로시저 구문을 함께 만드는 작업입니다.
코드: Memos.sql
--[1] 한줄 메모장(Memos) 테이블 설계
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 주소
)
Go
--[2] SQL 예시문 6가지 작성
--[a] 입력 예시문: Insert문: FrmMemoWrite.aspx
Insert Memos
Values
(
N'레드플러스', N'redplus@devlec.com', N'레드플러스입니다.'
, GetDate(), '127.0.0.1'
)
Go
--[b] 출력 예시문: Select문: FrmMemoList.aspx
Select Num, Name, Email, Title, PostDate, PostIP
From Memos Order By Num Desc
Go
--[c] 상세 예시문: Select문: FrmMemoView.aspx
Select Num, Name, Email, Title, PostDate, PostIP
From Memos Where Num = 1
Go
--[d] 수정 예시문: Update문: FrmMemoModify.aspx
Begin Tran
Update Memos
Set
Name = N'백두산',
Email = N'admin@devlec.com',
Title = N'백두산입니다.',
PostIP = N'127.0.0.1'
Where
Num = 1
--RollBack Tran
Commit Tran
Go
--[e] 삭제 예시문: Delete문: FrmMemoDelete.aspx
Begin Tran
Delete Memos
Where Num = 10
--RollBack Tran
Commit Tran
Go
--[f] 검색 예시문: Select문: FrmMemoSearch.aspx
--Memos에서 이름이 레드플러스이거나 또는
--이메일에 'r'가 들어가는 자료의 모든 필드
--번호의 역순으로 검색
Select Num, Name, Email, Title, PostDate
From Memos
Where
Name = '레드플러스'
Or
Email Like '%r%'
Order By Num Desc
Go
--[3] SQL 저장 프로시저 6가지 작성
--[a] 메모 입력용 저장 프로시저
Create Procedure dbo.WriteMemo
(
@Name NVarChar(25),
@Email NVarChar(100),
@Title NVarChar(150),
@PostIP NVarChar(15)
)
As
Insert Memos(Name, Email, Title, PostIP)
Values(@Name, @Email, @Title, @PostIP)
Go
--[b] 메모 출력용 저장 프로시저
Create Proc dbo.ListMemo
As
Select Num, Name, Email, Title, PostDate, PostIP
From Memos Order By Num Desc
Go
--[c] 메모 상세 보기용 저장 프로시저
Create Proc dbo.ViewMemo
(
@Num Int
)
As
Select Num, Name, Email, Title, PostDate, PostIP
From Memos
Where Num = @Num
Go
--[d] 메모 데이터 수정용 저장 프로시저
Create Proc dbo.ModifyMemo
(
@Name NVarChar(25),
@Email NVarChar(100),
@Title NVarChar(150),
@Num Int
)
As
Begin Transaction
Update Memos
Set
Name = @Name,
Email = @Email,
Title = @Title
Where Num = @Num
Commit Transaction
Go
--[e] 메모 데이터 삭제용 저장 프로시저
Create Proc dbo.DeleteMemo
(
@Num Int
)
As
Delete Memos
Where Num = @Num
Go
--[f] 메모 데이터 검색용 저장 프로시저(동적SQL 사용)
Create Proc dbo.SearchMemo
(
@SearchField NVarChar(10),
@SearchQuery NVarChar(50)
)
--With Encryption -- 현재 SP문 암호화
As
Declare @strSql NVarChar(150) --변수 선언
Set @strSql =
'
Select Num, Name, Email, Title, PostDate, PostIP
From Memos
Where ' + @SearchField + ' Like
N''%' + @SearchQuery + '%''
Order By Num Desc
'
--Print @strSql
Exec (@strSql) --완성된 SQL문 실행
Go
(6) 쿼리 편집기 좌측 상단의 실행([Ctrl]+[Shift]+[E]) 버튼을 클릭해서 작성한 T-SQL 구문을 실행합니다. 단일 구문만을 실행하려면 해당 구문만 블록을 씌운 뒤 실행 버튼을 클릭하면 됩니다.
그림: T-SQL 구문 실행
(7) 위 T-SQL 구문 실행 후 SQL Server 개체 탐색기에서 테이블 항목을 열어보면 다음과 같이 Memos 테이블이 생성된 것을 확인할 수 있습니다.
그림: 생성된 Memos 테이블 확인
이렇게 생성된 Memos 테이블은 이번 강의에서 여러 번 사용될 것입니다. 앞서 언급했지만, 만약 LocalDB가 아닌 SQL Server 정식 버전이나 SQL Azure 같이 다른 SQL Server를 사용하려면 해당 SQL Server에 데이터베이스와 테이블을 구성하면 됩니다.
(8) LocalDB에 생성된 데이터베이스를 ADO.NET 기술을 사용해서 ASP.NET 페이지에서 사용하려면 해당 데이터베이스에 연결해야 하는데 이때 여러가지 정보가 필요합니다. 이러한 정보를 데이터베이스 연결 문자열(Connection String)이라고 합니다. 이에 대한 정보는 SQL Server 개체 탐색기에서 해당 데이터베이스를 선택한 후 속성(F4) 창을 열고 <연결 문자열> 항목을 선택하면 다음 그림과 같이 데이터베이스 연결 문자열 정보를 쉽게 얻을 수 있습니다.
그림: 데이터베이스 연결 문자열 확인
(9) 위에서 얻은 데이터베이스 연결 문자열 정보를 DevADONET 프로젝트 루트에 있는 Web.config 파일을 열고 다음과 같이 연결 문자열 정보를 기록합니다. 이렇게 기록된 정보는 현재 프로젝트의 모든 페이지에서 읽어갈 수 있어서 데이터베이스 연결 문자열 정보가 변경될 때 일괄적으로 한곳에서 변경할 수 있습니다.
코드: Web.config 파일에 설정된 데이터베이스 연결 문자열
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<!--[!] 데이터베이스 연결 문자열 지정 -->
<connectionStrings>
<add name="ConnectionString"
connectionString=
"Data Source=(LocalDB)\MSSQLLocalDB;
Initial Catalog=DevADONET;
Integrated Security=True;
Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;
ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
</configuration>
참고로 다음 그림은 나의 컴퓨터 환경에서 데이터베이스 연결 문자열을 설정한 모습입니다. 연결 문자열 정보가 모두 다 필요한 것은 아닙니다. 주로 "Data Source", "Initial Catalog", "Integrated Security=True;"정도의 항목만 넣어도 LocalDB를 연결하는 데는 문제 없습니다.
그림: Web.config 파일의 데이터베이스 연결 문자열
최소한의 항목만 사용한 데이터베이스 연결 문자열은 다음과 같습니다.
코드: Web.config 파일에 설정된 데이터베이스 연결 문자열
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<!--[!] 데이터베이스 연결 문자열 지정 -->
<connectionStrings>
<add name="ConnectionString"
connectionString=
"Data Source=(LocalDB)\MSSQLLocalDB;
Initial Catalog=DevADONET;
Integrated Security=True;"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
</configuration>
마무리
SQL Server 개체 탐색기를 통해서 SQL Server LocalDB에 DevADONET이라는 이름의 데이터베이스를 생성하고, 이 데이터베이스에 Memos란 이름의 테이블을 만들었습니다. Memos 테이블과 여섯 가지 저장 프로시저는 이번 강의 이후로도 계속 사용할 예정입니다.
참고: 행정표준용어
데이터베이스 프로그래밍할 때 데이터베이스 및 테이블 이름 그리고 컬럼 이름 등을 정할 때 개인적으로 정해도 되고 회사 정책에 따라 정해도 됩니다. 만약, 이름 짓기가 어렵다면 공공데이터포털(https://data.go.kr)에서 제공하는 행정표준용어를 참고하면 좋습니다. 다음 링크의 행정표준용어.xls 파일을 참고해보세요.
10. SqlConnection 및 OleDbConnection 클래스
데이터베이스 연결 및 종료
SQL Server에 연결해서 어떤 작업을 수행하려면 SqlConnection 클래스를 사용해야 하고, Access DB에 연결해서 작업을 할 때는 OleDbConnection 클래스를 사용해야 합니다. 앞으로 배울 다른 클래스도 이와 마찬가지로 데이터베이스에 따라서 Sql 접두어 또는 OleDb 접두어로 구분됩니다. 다만, 이 책(강의)은 SQL Server를 기본 데이터베이스로 사용할 것입니다.
SqlConnection 클래스의 주요 속성 및 메서드
| 속성 또는 메서드 | 설명 |
|---|---|
| ConnectionString | 데이터베이스 연결 문자열을 설정하여 SQL Server를 연결합니다. |
| State | 데이터베이스와의 연결 상태를 나타내는 ConnectionState 열거형을 반환합니다. * ConnectionState.Open : 연결된 상태 * ConnectionState.Closed : 닫혀 있는 상태 |
| Open() | 설정된 데이터베이스 연결 문자열을 사용하여 데이터베이스를 연결합니다. |
| Close() | 현재 연결된 데이터베이스의 연결을 해제합니다. |
다시 한 번 설명하지만, SqlConnection 클래스 같이 .NET에서 제공하는 클래스의 모든 멤버를 나열하기에는 지면이 많이 부족합니다. 이 책에서 제시하는 주요 속성 또는 메서드들을 먼저 읽어보고 개발하다가 새로운 유형의 코드가 나타나면 해당 코드를 알아가기를 권합니다.
NOTE
SqlConnection 클래스에서 제공하는 모든 멤버들에 대한 정보는 Microsoft Learn를 참고합니다.
11. [실습] SqlConnection 클래스를 사용하여 SQL Server 연결하기
소개
SQL Server에 접속/연결하는 구문을 학습합니다.
NOTE
이번 실습 과정을 "SqlConnection 클래스를 사용하여 SQL Server 연결하기" 제목의 동영상 강좌로도 마련하였으니 참고하기 바랍니다.
따라하기
(1) Visual Studio를 실행한 후 <파일 > 열기 > 프로젝트/솔루션> 메뉴를 클릭하여 DevADONET 웹 사이트를 불러옵니다.
(2) 솔루션 탐색기에서 DevADONET 웹 프로젝트에 마우스 오른쪽 버튼을 클릭하여 <추가 > 새 항목> 메뉴를 클릭한 후 다음 FrmSqlConnection.aspx라는 이름으로 웹 프로젝트에 웹 폼을 추가합니다.
| 템플릿 이름 | 이름 |
|---|---|
| 웹 폼 | FrmSqlConnection.aspx |
(3) FrmSqlConnection.aspx 페이지에 다음과 같이 입력합니다.
코드: FrmSqlConnection.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="FrmSqlConnection.aspx.cs" Inherits="DevADONET.FrmSqlConnection" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SqlConnection 클래스</title>
</head>
<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>
</html>
웹 폼에 등록된 컨트롤의 주요 속성은 다음과 같습니다.
| 컨트롤 | 속성 | 값 |
|---|---|---|
| Button | ID | btnSqlConnection |
| Text | SQL Server에 연결하기 | |
| Label | ID | lblDisplay |
| Text | (빈 문자열) |
(4) FrmSqlConnection.aspx.cs 파일을 열고 다음과 같이 코드를 작성합니다.
코드: 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 속성 지정
// [a] 직접 연결 문자열 지정
// con.ConnectionString =
// "server=(localdb)\\MSSQLLocalDB;"
// + "database=DevADONET;Integrated Security=True;";
// [b] Web.config에 설정된 값 가져와서 지정
con.ConnectionString =
ConfigurationManager.ConnectionStrings[
"ConnectionString"].ConnectionString;
// [3] Open() 메서드 실행: 데이터베이스 연결
con.Open();
// [!] 실행
lblDisplay.Text = "데이터베이스 연결 성공";
// [4] Close() 메서드 실행: 데이터베이스 연결 종료
con.Close();
}
}
}
Web.config 파일에 설정한 데이터베이스 연결 문자열에 대한 정보는 ConfigurationManager 클래스의 ConnectionStrings속성으로 읽어올 수 있습니다. 이렇게 읽어오면 매번 코드에서 데이터베이스 연결 문자열을 기록할 필요없이 Web.config 파일에서 딱 한번만 관리하면 되기에 편리합니다.
(5) FrmSqlConnection.aspx에 마우스 오른쪽 버튼을 클릭하여 시작 페이지로 설정 메뉴를 선택하고 [Ctrl]+[F5]를 눌러 실행하면 웹 브라우저에 다음과 같이 출력됩니다. SQL Server에 연결하기 버튼을 누르면 아래에 데이터베이스 연결 성공이라는 텍스트가 나타난다.
그림: SqlConnection 클래스 웹브라우저 실행 결과
데이터베이스 설정 정보가 정확하다면 버튼을 클릭했을 때 정상 메시지가 출력됩니다. 만약에 에러가 발생한다면 데이터베이스 연결 문자열을 다시 한 번 확인하기 바랍니다. 또는 에러 방지 차원으로 con.Open()과 같은 구문은 try~catch~finally 구문으로 묶어 주면 좋을 것입니다.
NOTE
Web.config 파일의 데이터베이스 연결 문자열을 일부러 틀리게 고쳐 놓은 후 현재 페이지를 다시 실행하면 에러가 발생합니다. 이러한 에러 메시지를 살펴보는 것도 나중에 오류를 만났을 때 도움이 될 수 있습니다.
마무리
이번 실습에 사용한 커넥션 개체는 앞으로 사용될 모든 코드의 기본이 됩니다. 즉, 어떤 명령어를 수행해도 반드시 데이터베이스에 연결되어야 하기에 반드시 커넥션 개체를 사용하여 데이터베이스를 연결해 놓고 추가 작업을 해야 합니다.
12. SqlCommand 및 OleDbCommand 클래스
데이터베이스 명령 실행
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 : Access DB 전용(테이블명) | |
| ExecuteNonQuery() | Select문 이외의 구문을 실행하고자 할 때 주로 사용합니다. |
| – 테이블의 행에 영향을 미친 개수만큼 정숫값을 반환 | |
| ExecuteReader() | Select문을 실행하고 그 결과를 SqlDataReader 개체로 반환합니다. |
| – 다중 값 반환 : 레코드의 집합을 반환 | |
| ExecuteScalar() | Select문을 실행하고 첫 번째 열(필드) 값을 반환합니다. |
| – 단일 값 반환 : 주로 집계 함수(Sum, Count 등)의 결과를 반환 |
13. [실습] SqlCommand 클래스를 사용하여 데이터베이스 명령어 실행하기
소개
SQL Server에 인라인 SQL문 또는 저장 프로시저와 같은 구문을 실행하는 방법을 학습합니다.
따라하기
(1) Visual Studio를 실행한 후 <파일 > 열기 > 프로젝트/솔루션> 메뉴를 클릭하여 DevADONET 웹 프로젝트를 불러옵니다.
(2) 솔루션 탐색기에서 DevADONET 웹 프로젝트에 마우스 오른쪽 버튼을 클릭하여 <추가 > 새 항목> 메뉴를 클릭한 다음 FrmSqlCommand.aspx라는 이름으로 웹 프로젝트에 웹 폼을 추가합니다.
|템플릿 이름| 이름| |웹 폼| FrmSqlCommand.aspx|
(3) FrmSqlCommand.aspx 페이지에 다음과 같이 입력합니다.
코드: FrmSqlCommand.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="FrmSqlCommand.aspx.cs" Inherits="DevADONET.FrmSqlCommand" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SqlCommand 클래스</title>
</head>
<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>
</html>
웹 폼에 등록된 컨트롤의 주요 속성은 다음과 같습니다.
| 컨트롤 | 속성 | 값 |
|---|---|---|
| Button | ID | btnSqlCommand |
| Text | 입력 예시문 실행 | |
| Label | ID | lblDisplay |
| Text | (빈 문자열) |
(4) FrmSqlCommand.aspx.cs 파일을 열고 다음과 같이 코드를 작성합니다. 데이터베이스 연결 문자열을 읽어오기 위한 네임스페이스와 SQL Server 관련 SqlConnection과 SqlCommand 클래스를 위한 네임스페이스를 추가해야 합니다.
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'h@h.com', N'홍길동입니다.', GetDate(), '127.0.0.1')
";
// [4] CommandType 속성 지정
cmd.CommandType = CommandType.Text;
// [5] ExecuteXXX() 메서드로 실행
cmd.ExecuteNonQuery();
// [6] 마무리
lblDisplay.Text = "데이터 저장 완료";
con.Close();
}
}
}
Insert문 이외에 모든 구분이 이번 코드의 cmd.CommandText 속성에 지정되어 사용할 수 있습니다. 이 부분에 저장 프로시저를 사용하는 코드는 뒤에서 다시 다루겠습니다.
NOTE
SqlCommand.ExecuteNonQuery() 메서드의 반환값 SqlCommand 클래스의 ExecuteNonQuery() 메서드는 그 결과값으로 int 형을 반환합니다. 이 int 형의 내용은 Insert, Update 등의 구문에 의해서 발생된 레코드의 건수를 의미합니다. 즉, Insert가 하나 되면 1이 반환됩니다. 그 값을 사용해서 저장이 되었는지를 확인하는 목적으로 사용됩니다.
(5) FrmSqlCommand.aspx에 마우스 오른쪽 버튼을 클릭하여 <시작 페이지로 설정> 메뉴를 선택하고 [Ctrl]+[F5]를 눌러 실행하면 웹 브라우저에 다음과 같이 출력됩니다. <입력 예시문 실행> 버튼을 클릭하면 Memos 테이블에 데이터가 저장되고 레이블에 데이터 저장 완료 메시지가 출력됩니다.
그림 12-13 SqlCommand 클래스 웹브라우저 실행 결과
데이터가 저장되었는지는 Visual Studio의 <보기 > SQL Server 개체 탐색기>를 열고 DevADONET 데이터베이스의 Memos 테이블에 마우스 오른쪽 버튼 클릭 후 <데이터 보기>로 데이터를 살펴볼 수 있습니다.
13.3 마무리
이번 실습의 커멘드 개체는 앞으로 사용할 모든 명령어를 실행합니다. 커넥션 개체와 커멘드 개체는 모든 코드에서 계속 사용할 것입니다.
13.4 코드조각: ADO.NET으로 테이블 직접 만들기
ADO.NET을 사용하여 특정 테이블이 존재하지 않으면 생성할 수 있습니다.
SqlConnection con = new SqlConnection();
con.ConnectionString = connectionString; //[!] 데이터베이스 연결 문자열 지정
con.Open();
// [1] SqlCommand의 인스턴스 생성
SqlCommand cmd = new SqlCommand();
// [2] Connection 속성 지정
cmd.Connection = con;
// [3] CommandText 속성 설정
cmd.CommandText = @"
IF OBJECT_ID(N'[dbo].[ZeroTables]', N'U') IS NULL
BEGIN
CREATE TABLE dbo.ZeroTables (
Id Int Identity(1, 1) Not Null Primary Key,
Name NVarChar(255) NOT NULL
)
END;
";
// [4] CommandType 속성 지정
cmd.CommandType = System.Data.CommandType.Text;
// [5] ExecuteXXX() 메서드로 실행
await cmd.ExecuteNonQueryAsync();
con.Close();
13.5 sql server alter table add column if not exists
#컬럼 추가, #열 추가
***코드:
SqlConnection con = new SqlConnection();
con.ConnectionString = connectionString; //[!] 데이터베이스 연결 문자열 지정
con.Open();
// [1] SqlCommand의 인스턴스 생성
SqlCommand cmd = new SqlCommand();
// [2] Connection 속성 지정
cmd.Connection = con;
// [3] CommandText 속성 설정
cmd.CommandText = @"
IF NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Sublocation' AND COLUMN_NAME = N'LocationId'
)
BEGIN
ALTER TABLE Sublocation ADD LocationId Int NULL
END;
";
// [4] CommandType 속성 지정
cmd.CommandType = System.Data.CommandType.Text;
// [5] ExecuteXXX() 메서드로 실행
await cmd.ExecuteNonQueryAsync();
con.Close();
14 SqlDataReader 클래스
14.1 데이터베이스 명령 실행
ASP.NET에서 커멘드 개체를 사용하여 SQL Server에 Select문을 실행한 결과를 받아올 때 SqlDataReader 클래스를 사용합니다.
14.2 SqlDataReader 클래스의 주요 속성 및 메서드
데이터를 조회(Select)할 때 사용되는 SqlDataReader의 주요 속성과 메서드는 다음과 같습니다.
| 속성 또는 메서드 | 설명 |
|---|---|
| FieldCount | Select문의 실행 결과에서 필드(열)의 개수 |
| HasRows | Select문을 실행한 후 반환되는 레코드가 있으면 참 |
| Read() | 레코드가 있는 만큼 반복합니다. |
| Close() | 데이터 리더 개체를 닫는다. |
| GetXXX() | XXX 형식으로 필드(컬럼) 값을 반환합니다. |
15 [실습] SqlDataReader 클래스로 데이터 받아 출력하기
15.1 소개
커멘드 개체에서 Select문을 실행하여 그 결과를 받습니다. 받은 결과를 데이터리더 개체를 사용하여 화면에 출력해보겠습니다.
15.2 따라하기
(1) Visual Studio를 실행한 후 <파일 > 열기 > 프로젝트/솔루션> 메뉴를 클릭하여 DevADONET 웹 프로젝트를 불러옵니다.
(2) 솔루션 탐색기에서 DevADONET 웹 프로젝트에 마우스 오른쪽 버튼을 클릭하여 <추가 > 새 항목> 메뉴를 클릭한 다음 FrmSqlDataReader.aspx라는 이름으로 웹 프로젝트에 웹 폼을 추가합니다.
| 템플릿 이름 | 이름 |
|---|---|
| 웹 폼 | FrmSqlDataReader.aspx |
(3) FrmSqlDataReader.aspx 페이지에 다음과 같이 입력합니다.
코드: FrmSqlDataReader.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="FrmSqlDataReader.aspx.cs" Inherits="DevADONET.FrmSqlDataReader" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SqlDataReader 클래스</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Literal ID="tblOutput" runat="server"></asp:Literal>
</div>
</form>
</body>
</html>
웹 폼에 등록된 컨트롤의 주요 속성은 다음과 같습니다. 리터럴 컨트롤에 표 형태로 데이터를 만들어 출력할 것입니다.
| 컨트롤 | 속성 | 값 |
|---|---|---|
| Literal | ID | tblOutput |
| Text | (빈 문자열) |
(4) FrmSqlDataReader.aspx.cs 파일을 열고 다음과 같이 코드를 작성합니다.
코드: 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)
{
DisplayData();
}
}
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 border=\"1\">" +
"<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.GetDateTime(4).ToShortDateString()}</td></tr>";
}
strOutput += "</table>";
// [3] Close() 메서드로 연결된 리더 개체 종료
dr.Close();
// [!] 출력
tblOutput.Text = strOutput;
con.Close();
} // end of DisplayData()
}
}
이 코드는 strOutput 변수에 문자열 연결로 테이블을 만들어서 출력합니다. 컬럼 값 하나는 dr[], dr.GetString(), dr.GetXXX() 형태로 출력해서 사용될 수 있습니다.
NOTE
SqlDataReader 개체의 값을 필드명으로 출력하기 SqlDataReader의 값을 필드명을 기준으로 출력한다면, dr["Name"].ToString(), dr["Title"].ToString() 식으로 문자열로 변경하여 출력할 수 있습니다. 이 때 SQL 테이블의 데이터 형식이 날짜형(DateTime)이라면 추가적인 형식 변환이 필요한데Convert.ToDateTime(dr["PostDate"]).ToString() 형식으로 dr 개체를 날짜형으로 변환 후 다시 이를 문자열로 변경해서 사용하면 됩니다. 여기서 한가지 더 주의할 점은 필드에 들어 있는 값이 null 값이라면 널 값에 대한 예외처리를 아래와 같이 추가적으로 해주어야 합니다.
if (dr["PostDate"] != null)
{
// 데이터 가져오기
}
dr 개체의 null 값 비교는 DBNull.Value로도 비교할 수 있습니다.
NOTE
참고: 날짜 형식 가져오기 PostDate 속성은 C#의 DateTime 형식을 받습니다. 그러므로, null 값 예외 처리 후 해당 값을 받을 때에는
- Convert.ToDateTime(objDr["PostDate"])
- 또는 -
- objDr.GetDateTime(4); 형태로 받아야 합니다. GetString()으로 받으면 에러가 발생합니다.
(5) FrmSqlDataReader.aspx에 마우스 오른쪽 버튼을 클릭하여 <시작 페이지로 설정> 메뉴를 선택하고 [Ctrl]+[F5]를 눌러 실행하면 웹 브라우저에 다음과 같이 출력됩니다.
그림 12-14 SqlDataReader 클래스 웹브라우저 실행 결과
NOTE
Identity 컬럼
이 실행 결과는 샘플로 데이터를 더 입력한 내용을 출력한 것입니다. 참고로 Num 필드는 Identity 속성으로 자동 증가값으로 설정되어 있지만, SQL Server의 Identity는 반드시 그 다음 숫자값이 입력된다는 보증은 없습니다. 위 그림처럼 번호가 갑자기 1,000단위로 증가할 수도 있습니다.
15.3 마무리
Select문의 결과를 화면에 출력하려고 반드시 SqlDataReader 클래스를 사용해야 하는 것은 아닙니다. 코드 또한 이처럼 복잡하지 않고, 다음 장에 나오는 Data 컨트롤을 사용하여 쉽게 구성할 수 있습니다. 하지만 이 모든 출력의 기본은 SqlDataReader 개체임을 기억하기 바랍니다.
16 SqlDataAdapter 클래스와 DataSet 클래스
16.1 DataSet 클래스 개요
앞서 살펴본 DataSet 클래스는 ‘메모리의 데이터베이스’라고 정의할 수 있습니다. 즉, 메모리에 필드와 레코드로 이루어진 테이블을 여러 개 올려 놓는다. 그리고 해당 테이블 간의 관계를 설정하고, 각각의 테이블에서 데이터를 입력, 출력, 수정, 삭제, 검색하는 일반적인 데이터베이스 처리 로직을 구현합니다.
NOTE
.NET이 처음 나왔을 때 DataSet 클래스를 알아가는데 수많은 시간을 투자했었습니다. 하지만, 실제로 현업 응용 프로그램을 작성시 그렇게까지 큰 의미를 두지 않고도 개발하는데 전혀 상관이 없는 걸 알게 되었습니다. Microsoft Docs에서 DataSet 클래스를 검색하여 관련된 글들을 읽어보면 정말로 너무나 많은 기능을 제공함에 놀랄 수 있습니다. 하지만, 이 책에서는 이 DataSet의 기능은 몇몇 예제로만 알아보고 실제 구현은 무거운 DataSet 대신에 List<T> 형태의 제네릭 클래스를 사용하는 방향으로 진행됩니다.
16.2 SqlDataAdapter 클래스
DataSet 클래스가 Select문의 실행 결과를 담아 놓는 그릇 역할을 한다면 SqlDataAdapter 클래스는 Select문을 실행시키고 실행된 결괏값을 가져다가 DataSet 개체에게 채워주는 중간 매개체 역할을 합니다. 즉, DataSet 클래스와 SqlDataAdapter 클래스는 떼려야 땔 수 없는 관계다.
17 [실습] SqlDataAdapter와 DataSet 클래스로 데이터 출력하기
17.1 소개
데이터를 출력하는 두 번째 방법인 SqlDataAdapter와 DataSet를 알아봅니다. 그리고 GridView 컨트롤을 사용하여 데이터를 출력하는 방법도 알아보겠습니다.
17.2 따라하기
(1) Visual Studio를 실행한 후 <파일 > 열기 > 프로젝트/솔루션> 메뉴를 클릭하여 DevADONET 웹 프로젝트를 불러옵니다.
(2) 솔루션 탐색기에서 DevADONET 웹 프로젝트에 마우스 오른쪽 버튼을 클릭하여 <추가 > 새 항목> 메뉴를 클릭한 다음 FrmSqlDataAdapter.aspx라는 이름으로 웹 프로젝트에 웹 폼을 추가합니다.
| 템플릿 이름 | 이름 |
|---|---|
| 웹 폼 | FrmSqlDataAdapter.aspx |
(3) FrmSqlDataAdapter.aspx 페이지에 다음과 같이 입력합니다.
코드: FrmSqlDataAdapter.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="FrmSqlDataAdapter.aspx.cs" Inherits="DevADONET.FrmSqlDataAdapter" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SqlDataAdapter와 DataSet 클래스</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="ctlMemoLists" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
웹 폼에 등록된 컨트롤의 주요 속성은 다음과 같습니다. 단순히 도구 상자의 데이터 범주에 있는 GridView 컨트롤을 기본값으로 등록한 후 이름만 변경하였습니다. 다음 장에서 자세히 다루겠지만, GridView 컨트롤은 표 형태로 데이터를 출력할 때 사용하는 아주 유용한 컨트롤입니다.
| 컨트롤 | 속성 | 값 |
|---|---|---|
| GridView | ID | ctlMemoList |
(4) FrmSqlDataAdapter.aspx.cs 파일을 열고 다음과 같이 코드를 작성합니다.
코드: 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 채우기
da.Fill(ds, "Memos");
// [!] 출력
ctlMemoLists.DataSource = ds.Tables[0].DefaultView; // ds만 지정가능
ctlMemoLists.DataBind();
con.Close();
}
}
}
위 코드의 마지막 출력 부분에는 다음과 같이 원하는 방식으로 DataSet 개체 또는 DataSet 개체 중 하나의 테이블만 의미하는 DataTable 개체 중 한가지를 사용하면 됩니다.
- ctlMemoLists.DataSource = ds.Tables[0].DefaultView;
- ctlMemoLists.DataSource = ds.Tables[0];
- ctlMemoLists.DataSource = ds;
(5) FrmSqlDataAdapter.aspx에 마우스 오른쪽 버튼을 클릭하여 <시작 페이지로 설정> 메뉴를 선택하고 [Ctrl]+[F5]를 눌러 실행하면 웹 브라우저에 다음과 같이 출력됩니다.
그림 12-15 SqlDataAdapter와 DataSet 클래스 웹브라우저 실행 결과
17.3 마무리
ASP.NET에서 데이터베이스 프로그래밍할 때의 주요 개체인 Connection 개체로 사용할 데이터베이스를 연결하고 Command 개체로 Select, Insert 등의 SQL 명령어를 담아서 실행하거나 SqlDataAdapter 개체로 SQL 명령어를 실행 후 DataSet 개체에 그 결괏값을 담고 DataSet 개체에 담은 데이터를 GridView 컨트롤을 사용하여 웹 페이지에 출력하는 전형적인 예를 보여줍니다.
18 다중 레코드 반환(QueryMultiple)
다음은 하나 이상의 Select 문의 결괏값을 반환하는 참고용 코드 샘플입니다.
***코드: ***
string commandText = @"
SELECT Id, ContactId FROM dbo.Subscriptions;
SELECT Id, [Name] FROM dbo.Contacts;";
List<Subscription> subscriptions = new List<Subscription>();
List<Contact> contacts = new List<Contact>();
using (SqlConnection dbConnection =
new SqlConnection(@"Data Source=server;Database=database;Integrated Security=true;"))
using (SqlCommand dbCommand = new SqlCommand(commandText, dbConnection))
{
dbCommand.CommandType = CommandType.Text;
dbConnection.Open();
SqlDataReader reader = dbCommand.ExecuteReader();
while (reader.Read())
{
subscriptions.Add(
new Subscription()
{
Id = (int)reader["Id"],
ContactId = (int)reader["ContactId"]
});
}
reader.NextResult();
while (reader.Read())
{
contacts.Add(
new Contact()
{
Id = (int)reader["Id"],
Name = (string)reader["Name"]
});
}
dbConnection.Close();
}
19 참고: 트랜잭션
다음 코드 조각은 트랜잭션에 대한 내용입니다. 지금은 몰라도 상관없습니다.
// System.Transaction.dll
// SqlTransaction 클래스로 트랜잭션 처리하기
SqlTransaction st = con.BeginTransaction();
SqlTransaction transaction = con.BeginTransaction();
cmd.Transaction = st;
st.Commit();
catch {
st.Rollback
}
using (TransactionScope scope = new TransactionScope())
{
scope.Complete();
}
20 퀴즈
퀴즈 1. ADO.NET에서 Microsoft Access 데이터베이스를 연결할 때 사용하는 클래스들을 포함하는 네임스페이스는 무엇인가요? 단, 가장 알맞은 것을 고르세요.
a. System.Data.SqlClient 네임스페이스
b. System.Data.OleDb 네임스페이스
c. System.Data.Odbc 네임스페이스
d. System.Data.OracleClient 네임스페이스
정답: b
Microsoft Access 데이터베이스 연결할 때에는 기본적으로 System.Data.OleDb 네임스페이스에 있는 클래스들을 사용합니다.
퀴즈 2. 데이터베이스 연결 문자열을 구성하는 구성 요소가 아닌 것을 고르세요.
a. Server
b. Data
c. User ID
d. Password
정답: b
데이터베이스를 지정하는 부분은 Data가 아닌 Database, DB, Initial Catalog 중 하나가 들어와야 합니다.
퀴즈 3. SqlCommand 클래스는 인라인 SQL 또는 저장 프로시저의 결괏값을 반환하는 여러 메서드를 제공합니다. 합계(SUM), 건수(COUNT) 등의 단일 값을 반환 받을 때 사용하는 메서드는 무엇인지 고르세요.
a. ExecuteNonQuery()
b. ExecuteReader()
c. ExecuteScalar()
d. ExecuteDataSet()
정답: c
단일값 즉, 스칼라값을 반환받을 때에는 ExecuteScalar() 메서드를 사용합니다.
참고: ADO.NET 기초 강의 소스 모음
ADO.NET 2023 Fundamentals 재생 목록
DotNetSale
DotNetSale.Database 프로젝트
코드: LogBases.sql
-- 로그 정보 기록
CREATE TABLE [dbo].[LogBases]
(
[Id] INT Identity(1, 1) NOT NULL PRIMARY KEY, -- 일련번호
Message NVarChar(Max) Not Null, -- 로그 메시지
Timestamp DateTimeOffset(7)
Default(GetDate() AT TIME ZONE 'Korea Standard Time') -- 시간
)
Go
코드: CategoryBases.sql
-- 카테고리 테이블: Categories, CategoryBases
CREATE TABLE [dbo].[CategoryBases]
(
CategoryId Int Identity(1, 1) Primary Key Not Null, -- 카테고리 번호
CategoryName NVarChar(50) Not Null -- 카테고리 이름
)
Go
Dul.Data 프로젝트
코드: SortOrder.cs
using System;
namespace Dul.Data
{
/// <summary>
/// SortOrder 열거형: 행의 데이터 정렬 방법을 지정합니다.
/// </summary>
[Obsolete("OrderOption 열거형을 사용하세요.")]
public enum SortOrder
{
/// <summary>
/// 오름차순
/// </summary>
Ascending,
/// <summary>
/// 내림차순
/// </summary>
Descending,
/// <summary>
/// 기본값
/// </summary>
None
}
}
코드: OrderOption.cs
namespace Dul.Data
{
/// <summary>
/// OrderOption 열거형: 행의 데이터 정렬 방법을 지정합니다.
/// </summary>
public enum OrderOption
{
/// <summary>
/// 오름차순
/// </summary>
Ascending,
/// <summary>
/// 내림차순
/// </summary>
Descending,
/// <summary>
/// 기본값
/// </summary>
None
}
}
코드: IBreadShop.cs
using System.Collections.Generic;
/// <summary>
/// Development Utility Library
/// </summary>
namespace Dul.Data
{
/// <summary>
/// 제네릭 인터페이스: 공통 CRUD 코드 => BREAD SHOP 패턴 사용
/// </summary>
/// <typeparam name="T">모델 클래스</typeparam>
public interface IBreadShop<T> where T : class
{
/// <summary>
/// 상세
/// </summary>
T Browse(int id);
/// <summary>
/// 출력
/// </summary>
List<T> Read();
/// <summary>
/// 수정
/// </summary>
bool Edit(T model);
/// <summary>
/// 입력
/// </summary>
T Add(T model);
/// <summary>
/// 삭제
/// </summary>
bool Delete(int id);
/// <summary>
/// 검색
/// </summary>
List<T> Search(string query);
/// <summary>
/// 건수
/// </summary>
int Has();
/// <summary>
/// 정렬
/// </summary>
IEnumerable<T> Ordering(OrderOption orderOption);
/// <summary>
/// 페이징
/// </summary>
List<T> Paging(int pageNumber, int pageSize);
}
}
DotNetSale.Models.Categories 프로젝트
코드: CategoryBase.cs
namespace DotNetSale.Models.Categories
{
/// <summary>
/// 카테고리 모델 클래스
/// </summary>
public class CategoryBase
{
/// <summary>
/// 카테고리 고유 일련번호
/// </summary>
public int CategoryId { get; set; }
/// <summary>
/// 카테고리 이름
/// </summary>
public string CategoryName { get; set; }
}
}
코드: ICategoryBaseRepository.cs
using Dul.Data;
namespace DotNetSale.Models.Categories
{
/// <summary>
/// 리포지토리 인터페이스 => BREAD SHOP 패턴 사용
/// </summary>
public interface ICategoryBaseRepository : IBreadShop<CategoryBase>
{
// 로그 메서드 추가
void Log(string message);
}
}
코드: CategoryBaseRepository.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dul.Data;
namespace DotNetSale.Models.Categories
{
/// <summary>
/// CategoryBases 테이블에 대한 CRUD 코드 구현 리포지토리 클래스
/// </summary>
public class CategoryBaseRepository : ICategoryBaseRepository
{
// [00] 데이터베이스 연결 문자열
private static string _connectionString;
// 생성자의 매개 변수로 데이터베이스 연결 문자열 받기
public CategoryBaseRepository(string connectionString)
{
_connectionString = connectionString;
}
/// <summary>
/// [01] SqlConnection 클래스를 사용하여 데이터베이스 연결
/// </summary>
public bool Open()
{
//[1] SqlConnection 클래스의 인스턴스 생성
SqlConnection con = new SqlConnection();
//[2] ConnectionString 속성 지정
con.ConnectionString = _connectionString;
//[3] Open() 메서드 실행: 데이터베이스 연결
con.Open();
//[!] 실행
bool result = false;
if (con.State == ConnectionState.Open)
{
result = true;
}
//[4] Close() 메서드 실행: 데이터베이스 연결 종료
con.Close();
return result;
}
/// <summary>
/// [02] SqlCommand 클래스를 사용해 데이터베이스 명령어 실행하기: ExecuteNoQuery()
/// 로그: 문자열 연결 방식으로 동적 쿼리 만들기(시스템 내부에서만 호출)
/// </summary>
/// <param name="message">로그 테이블에 저장될 문자열</param>
public void Log(string message)
{
var table = "LogBases";
SqlConnection con = new SqlConnection();
con.ConnectionString = _connectionString;
con.Open();
//[1] SqlCommand의 인스턴스 생성
SqlCommand cmd = new SqlCommand();
//[2] Connection 속성 지정
cmd.Connection = con;
//[3] CommandText 속성 설정: 모든 SQL(DDL + DML) 또는 저장 프로시저 이름
cmd.CommandText = // 동적으로 모든 SQL 문을 만들어 호출 가능함
$"Insert Into {table} (Message) Values(N'" + message + "');";
//[4] CommandType 속성 지정
cmd.CommandType = CommandType.Text;
//[5] ExecuteXXX() 메서드로 실행
cmd.ExecuteNonQuery(); // 실행 완료
con.Close();
}
/// <summary>
/// [03] SqlCommand 클래스를 사용해 데이터베이스 명령어 실행하기: ExecuteScalar()
/// ExecuteScalar() 메서드로 단일 값 받아 사용하기
/// </summary>
public int Has()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = _connectionString;
con.Open();
//[1] SqlCommand의 인스턴스 생성
SqlCommand cmd = new SqlCommand();
//[2] Connection 속성 지정
cmd.Connection = con;
//[3] CommandText 속성 설정: DDL + DML
cmd.CommandText = "Select Count(*) From CategoryBases";
//[4] CommandType 속성 지정
cmd.CommandType = CommandType.Text;
//[5] ExecuteXXX() 메서드로 실행
object result = cmd.ExecuteScalar(); // 스칼라 값(단일 값) 반환
con.Close();
if (int.TryParse(result.ToString(), out int count))
{
return count;
}
return 0;
}
/// <summary>
/// [04] SqlParameter 클래스를 사용해 매개 변수 전달하기
/// </summary>
public CategoryBase Add(CategoryBase model)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = _connectionString;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Insert Into CategoryBases Values(@CategoryName);";
cmd.CommandType = CommandType.Text;
//[1] SqlParameter 클래스의 인스턴스 생성
SqlParameter categoryName =
new SqlParameter("@CategoryName", SqlDbType.NVarChar, 50);
//[2] Value 속성으로 값 지정
categoryName.Value = model.CategoryName;
//[3] 커멘드 개체에 매개 변수 추가
cmd.Parameters.Add(categoryName);
cmd.ExecuteNonQuery();
con.Close();
return model;
}
/// <summary>
/// [05] SqlDataAdapter와 DataSet 클래스로 데이터 출력하기
/// </summary>
public List<CategoryBase> Read()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = _connectionString;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText =
"Select Top 1000 CategoryId, CategoryName From CategoryBases";
cmd.CommandType = CommandType.Text;
//[1] DataAdapter
SqlDataAdapter da = new SqlDataAdapter();
//[2] SelectCommand 지정
da.SelectCommand = cmd;
//[3] DataSet: 메모리상의 데이터베이스
DataSet ds = new DataSet();
//[4] Fill() 메서드로 DataSet 채우기
da.Fill(ds, "CategoryBases");
//[!] 출력
List<CategoryBase> categories = new List<CategoryBase>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
var categoryId =
Convert.ToInt32(ds.Tables[0].Rows[i]["CategoryId"].ToString());
var categoryName = ds.Tables[0].Rows[i]["CategoryName"].ToString();
categories.Add(new CategoryBase
{
CategoryId = categoryId,
CategoryName = categoryName
});
}
con.Close(); // DataAdapter + DataSet 클래스 사용할 때에는 생략 가능
return categories;
}
/// <summary>
/// [06] SqlDataReader 클래스로 데이터 받아 출력하기
/// </summary>
public CategoryBase Browse(int id)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = _connectionString;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = @"
Select CategoryId, CategoryName
From CategoryBases
Where CategoryId = @CategoryId
";
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CategoryId", id);
//[1] SqlDataReader 형식의 개체로 결괏값 받기
SqlDataReader dr = cmd.ExecuteReader();
//[2] Read() 메서드로 데이터 있는만큼 반복
CategoryBase category = null;
if (dr.Read())
{
var categoryId = dr.GetInt32(0);
var categoryName =
(dr["CategoryName"] == DBNull.Value) ? "" : dr.GetString(1);
// 개체 리터럴을 통해서 값 채우기
category = new CategoryBase
{
CategoryId = categoryId,
CategoryName = categoryName
};
}
//[3] Close() 메서드로 연결된 리더 개체 종료
dr.Close();
con.Close();
return category;
}
/// <summary>
/// [07] using () {} 구문 사용하여 커넥션 개체 해제하기
/// </summary>
public bool Edit(CategoryBase model)
{
bool result = false;
string sql = @"
Update CategoryBases
Set CategoryName = @CategoryName
Where CategoryId = @CategoryId
";
using (SqlConnection con = new SqlConnection(_connectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CategoryName", model.CategoryName);
cmd.Parameters.AddWithValue("@CategoryId", model.CategoryId);
int count = cmd.ExecuteNonQuery(); // 영향받은 레코드 수 반환
if (count > 0)
{
result = true; // 업데이트 완료
}
}
return result;
}
/// <summary>
/// [08] SqlTransaction 클래스(또는 TransactionScope 클래스)로 트랜잭션 처리하기
/// </summary>
public bool Delete(int id)
{
bool result = false;
using (SqlConnection con = new SqlConnection(_connectionString))
{
con.Open();
//[1] 트랜잭션(묶음) 개체 생성
SqlTransaction transaction = con.BeginTransaction();
try
{
//[a] 카테고리 삭제
#region 카테고리 삭제
using (SqlCommand cmd = con.CreateCommand())
{
string sql =
"Delete CategoryBases Where CategoryId = @CategoryId";
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CategoryId", id);
//[2] 커멘드 개체에 트랜잭션 개체 지정
cmd.Transaction = transaction;
int count = cmd.ExecuteNonQuery(); // 영향받은 레코드 수 반환
if (count > 0)
{
result = true; // 삭제 완료: 삭제되었습니다.
}
}
#endregion
//[b] 로그 삭제: 제일 오래된 로그 하나 삭제
#region 로그 삭제
using (SqlCommand cmd = con.CreateCommand())
{
string sql =
"Declare @OldLogId Int; " +
"Select @OldLogId = Min(Id) From LogBases; " +
"Delete LogBases Where Id = @OldLogId; ";
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
//[2] 커멘드 개체에 트랜잭션 개체 지정
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
#endregion
//[3] 트랜잭션 커밋: [a]와 [b] 둘 다 성공 시
transaction.Commit();
}
catch (Exception)
{
//[4] 트랜잭션 롤백: 실패 시
transaction.Rollback();
result = false; // 삭제되지 못했습니다.
}
}
return result;
}
/// <summary>
/// [09] 검색
/// </summary>
public List<CategoryBase> Search(string query)
{
List<CategoryBase> categories = new List<CategoryBase>();
using (SqlConnection con = new SqlConnection(_connectionString))
{
con.Open();
string sql = @"
Select * From CategoryBases
Where CategoryName Like '%' + @SearchQuery + '%'
";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@SearchQuery", query);
SqlDataReader dr = cmd.ExecuteReader();
//[!] Read() 메서드로 데이터 있는만큼 반복
while (dr.Read())
{
var categoryId = dr.GetInt32(0);
var categoryName =
(dr["CategoryName"] == DBNull.Value) ? "" : dr.GetString(1);
// 개체 리터럴을 통해서 값 채우기
categories.Add(new CategoryBase
{
CategoryId = categoryId,
CategoryName = categoryName
});
}
dr.Close();
}
return categories;
}
/// <summary>
/// [10] 페이징
/// </summary>
public List<CategoryBase> Paging(int pageNumber, int pageSize)
{
List<CategoryBase> categories = new List<CategoryBase>();
string sql = " Select * From CategoryBases Order By CategoryId Asc ";
// 페이징 처리 구문 추가:
// TOP 쿼리, ROW_NUMBER, OFFSET~FETCH(SQL Server 2012 이상)
// sql += " Offset ((" + pageNumber.ToString() + " - 1) * " +
// pageSize.ToString() + ") Rows Fetch Next "
// + pageSize.ToString() + " Rows Only ";
sql += @"
Offset ((@PageNumber - 1) * @PageSize) Rows
Fetch Next @PageSize Rows Only
";
using (SqlConnection con = new SqlConnection(_connectionString))
{
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.SelectCommand.Parameters.AddWithValue("@PageNumber", pageNumber);
da.SelectCommand.Parameters.AddWithValue("@PageSize", pageSize);
DataSet ds = new DataSet();
da.Fill(ds, "CategoryBases");
//[!] 출력
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
var categoryId = Convert.ToInt32(
ds.Tables[0].Rows[i]["CategoryId"].ToString());
var categoryName =
ds.Tables[0].Rows[i]["CategoryName"].ToString();
categories.Add(new CategoryBase
{
CategoryId = categoryId,
CategoryName = categoryName
});
}
}
return categories;
}
/// <summary>
/// [11] 정렬
/// </summary>
public IEnumerable<CategoryBase> Ordering(OrderOption orderOption)
{
List<CategoryBase> categories = new List<CategoryBase>();
using (SqlConnection con = new SqlConnection(_connectionString))
{
con.Open();
string sql = @" Select Top 1000 * From CategoryBases ";
switch (orderOption)
{
case OrderOption.Ascending:
sql += " Order By CategoryName Asc ";
break;
case OrderOption.Descending:
sql += " Order By CategoryName Desc ";
break;
default:
sql += "";
break;
}
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
// CommandBehavior.CloseConnection: 데이터리더 닫힐 때 커넥션도 닫힘
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
//[!] Read() 메서드로 데이터 있는만큼 반복
while (dr.Read())
{
var categoryId = dr.GetInt32(0);
var categoryName =
(dr["CategoryName"] == DBNull.Value) ? "" : dr.GetString(1);
// 개체 리터럴을 통해서 값 채우기
categories.Add(new CategoryBase
{
CategoryId = categoryId,
CategoryName = categoryName
});
}
dr.Close();
}
return categories;
}
}
}
DotNetSale.Models.Categories.Tests 프로젝트
코드: CategoryBaseRepositoryTest.cs
using Dul.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.Linq;
namespace DotNetSale.Models.Categories.Tests
{
[TestClass]
public class CategoryBaseRepositoryTest
{
private readonly CategoryBaseRepository _repository;
public CategoryBaseRepositoryTest()
{
var connectionString =
"server=(localdb)\\mssqllocaldb;" +
"database=DotNetSale;integrated security=true;";
_repository = new CategoryBaseRepository(connectionString);
}
/// <summary>
/// [00] 카테고리 컬렉션 출력 메서드
/// </summary>
private static void PrintCategories(List<CategoryBase> categories)
{
if (categories != null)
{
foreach (var category in categories)
{
Console.WriteLine(
$"{category.CategoryId} - {category.CategoryName}");
}
}
}
/// <summary>
/// [01] Open() 메서드 테스트
/// </summary>
[TestMethod]
public void OpenMethodTest()
{
var result = _repository.Open();
if (result)
{
Console.WriteLine("데이터베이스 연결 성공");
}
else
{
Console.WriteLine("데이터베이스 연결 실패");
}
}
/// <summary>
/// [02] Log() 메서드 테스트
/// </summary>
[TestMethod]
public void LogMethodTest()
{
string message = "로그 메서드 테스트";
_repository.Log(message);
}
/// <summary>
/// [03] Has() 메서드 테스트
/// </summary>
[TestMethod]
public void HasMethodTest()
{
int count = _repository.Has();
Console.WriteLine($"{count}개의 데이터가 있습니다.");
}
/// <summary>
/// [04] Add() 메서드 테스트
/// </summary>
[TestMethod]
public void AddMethodTest()
{
CategoryBase model = new CategoryBase();
model.CategoryName = "생활용품";
var r = _repository.Add(model);
Assert.AreEqual(r.CategoryName, model.CategoryName);
}
/// <summary>
/// [05] Read() 메서드 테스트
/// </summary>
[TestMethod]
public void ReadMethodTest()
{
var categories = _repository.Read();
PrintCategories(categories);
}
/// <summary>
/// [06] Browse() 메서드 테스트
/// </summary>
[TestMethod]
public void BrowseMethodTest()
{
int categoryId = 1;
var category = _repository.Browse(categoryId);
if (category != null)
{
Console.WriteLine(
$"{category.CategoryId} - {category.CategoryName}");
}
else
{
Console.WriteLine($"{categoryId}번 카테고리가 없습니다.");
}
}
/// <summary>
/// [07] Edit() 메서드 테스트
/// </summary>
[TestMethod]
public void EditMethodTest()
{
var model = new CategoryBase { CategoryId = 0, CategoryName = "BOOKS" };
var isEdited = _repository.Edit(model);
if (isEdited)
{
Console.WriteLine("수정했습니다.");
}
else
{
Console.WriteLine("수정하지 못했습니다.");
}
}
/// <summary>
/// [08] Delete() 메서드 테스트
/// </summary>
[TestMethod]
public void DeleteMethodTest()
{
var number = 3;
var isDeleted = _repository.Delete(number);
if (isDeleted)
{
Console.WriteLine("삭제했습니다.");
}
else
{
Console.WriteLine("삭제하지 못했습니다.");
}
}
/// <summary>
/// [09] Search() 메서드 테스트
/// </summary>
[TestMethod]
public void SearchMethodTest()
{
var categories = _repository.Search("용");
PrintCategories(categories);
}
/// <summary>
/// [10] Paging() 메서드 테스트
/// </summary>
[TestMethod]
public void PagingMethodTest()
{
var categories = _repository.Paging(2, 3);
PrintCategories(categories);
}
/// <summary>
/// [11] Ordering() 메서드 테스트
/// </summary>
[TestMethod]
public void OrderingMethodTest()
{
var categories = _repository.Ordering(OrderOption.None);
PrintCategories(categories.ToList());
}
}
}
VideoAppCore
VideoAppCore.SqlServer 프로젝트
코드: Videos.sql
--[1] 테이블: 비디오 테이블: 강좌에 대한 리스트 제공
CREATE TABLE [dbo].[Videos]
(
[Id] Int Not Null Identity(1, 1) Primary Key,
[Title] NVarChar(255) Not Null, -- 제목
[Url] NVarChar(Max) Null, -- URL
[Name] NVarChar(50) Null, -- 이름
[Company] NVarChar(255) Null, -- 회사
--[Created] DateTimeOffset(7)
-- Default(SysDateTimeOffset() AT TIME ZONE 'Korea Standard Time'),
[CreatedBy] NVarChar(255) Null, -- 등록자(Creator)
[Created] DateTime Default(GetDate()), -- 생성일
[ModifiedBy] NVarChar(255) Null, -- 수정자(LastModifiedBy)
[Modified] DateTime Null, -- 수정일(LastModified)
)
Go
VideoAppCore.Models 프로젝트
코드: Video.cs
using System;
namespace VideoAppCore.Models
{
public class Video
{
/// <summary>
/// 일련번호
/// </summary>
public int Id { get; set; }
/// <summary>
/// 동영상 제목
/// </summary>
public string Title { get; set; }
/// <summary>
/// 동영상 제공 URL
/// </summary>
public string Url { get; set; }
/// <summary>
/// 동영상 작성자
/// </summary>
public string Name { get; set; }
/// <summary>
/// 회사
/// </summary>
public string Company { get; set; }
/// <summary>
/// 등록자: CreatedBy, Creator
/// </summary>
public string CreatedBy { get; set; }
/// <summary>
/// 등록일: Created
/// </summary>
//public DateTimeOffset Created { get; set; }
public DateTime Created { get; set; }
/// <summary>
/// 수정자: LastModifiedBy, ModifiedBy
/// </summary>
public string ModifiedBy { get; set; }
/// <summary>
/// 수정일: LastModified, Modified
/// </summary>
public DateTime? Modified { get; set; }
}
}
코드: VideoRepositoryAdoNet.cs
// Install-Package System.Data.SqlClient
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace VideoAppCore.Models
{
public class VideoRepositoryAdoNet
{
private readonly string _connectionString;
public VideoRepositoryAdoNet(string connectionString)
{
this._connectionString = connectionString;
}
// 동기 방식
public Video AddVideo(Video model)
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query =
"Insert Into Videos(Title, Url, Name, Company, CreatedBy) " +
"Values(@Title, @Url, @Name, @Company, @CreatedBy);" +
"Select Cast(SCOPE_IDENTITY() As Int);";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Title", model.Title);
cmd.Parameters.AddWithValue("@Url", model.Url);
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Company", model.Company);
cmd.Parameters.AddWithValue("@CreatedBy", model.CreatedBy);
con.Open();
model.Id = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
return model;
}
// 비동기 방식
public async Task<Video> AddVideoAsync(Video model)
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query =
"Insert Into Videos(Title, Url, Name, Company, CreatedBy) " +
"Values(@Title, @Url, @Name, @Company, @CreatedBy);" +
"Select Cast(SCOPE_IDENTITY() As Int);";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Title", model.Title);
cmd.Parameters.AddWithValue("@Url", model.Url);
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Company", model.Company);
cmd.Parameters.AddWithValue("@CreatedBy", model.CreatedBy);
con.Open();
object result = await cmd.ExecuteScalarAsync();
if (int.TryParse(result.ToString(), out int id))
{
model.Id = id;
}
con.Close();
}
return model;
}
// 상세: 동기
public Video GetVideoById(int id)
{
Video video = new Video();
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = "Select * From Videos Where Id = @Id";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
video.Id = dr.GetInt32(0);
video.Title = dr["Title"].ToString();
video.Url = dr["Url"].ToString();
video.Name = dr["Name"].ToString();
video.Company = dr["Company"].ToString();
video.CreatedBy = dr["CreatedBy"].ToString();
video.Created = Convert.ToDateTime(dr["Created"]);
}
con.Close();
}
return video;
}
// 상세: 비동기
public async Task<Video> GetVideoByIdAsync(int id)
{
Video video = new Video();
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = "Select * From Videos Where Id = @Id";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
SqlDataReader dr = await cmd.ExecuteReaderAsync();
if (dr.Read())
{
video.Id = dr.GetInt32(0);
video.Title = dr["Title"].ToString();
video.Url = dr["Url"].ToString();
video.Name = dr["Name"].ToString();
video.Company = dr["Company"].ToString();
video.CreatedBy = dr["CreatedBy"].ToString();
video.Created = Convert.ToDateTime(dr["Created"]);
}
con.Close();
}
return video;
}
// 출력
public List<Video> GetVideos()
{
List<Video> videos = new List<Video>();
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = "Select * From Videos;";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Video video = new Video
{
Id = dr.GetInt32(0),
Title = dr["Title"].ToString(),
Url = dr["Url"].ToString(),
Name = dr["Name"].ToString(),
Company = dr["Company"].ToString(),
CreatedBy = dr["CreatedBy"].ToString(),
Created = Convert.ToDateTime(dr["Created"])
};
videos.Add(video);
}
con.Close();
}
return videos;
}
// 출력: 비동기 방식
public async Task<List<Video>> GetVideosAsync()
{
List<Video> videos = new List<Video>();
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = "Select * From Videos;";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
con.Open();
SqlDataReader dr = await cmd.ExecuteReaderAsync();
while (dr.Read())
{
Video video = new Video
{
Id = dr.GetInt32(0),
Title = dr["Title"].ToString(),
Url = dr["Url"].ToString(),
Name = dr["Name"].ToString(),
Company = dr["Company"].ToString(),
CreatedBy = dr["CreatedBy"].ToString(),
Created = Convert.ToDateTime(dr["Created"])
};
videos.Add(video);
}
con.Close();
}
return videos;
}
// 삭제
public void RemoveVideo(int id)
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = "Delete Videos Where Id = @Id";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
// 삭제: 비동기 방식
public async Task RemoveVideoAsync(int id)
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = "Delete Videos Where Id = @Id";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
await cmd.ExecuteNonQueryAsync();
con.Close();
}
}
// 수정
public Video UpdateVideo(Video model)
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = @"
Update Videos
Set
Title = @Title,
Url = @Url,
Name = @Name,
Company = @Company,
ModifiedBy = @ModifiedBy
Where Id = @Id";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Id", model.Id);
cmd.Parameters.AddWithValue("@Title", model.Title);
cmd.Parameters.AddWithValue("@Url", model.Url);
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Company", model.Company);
cmd.Parameters.AddWithValue("@ModifiedBy", model.ModifiedBy);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
return model;
}
// 수정: 비동기 방식
public async Task<Video> UpdateVideoAsync(Video model)
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
const string query = @"
Update Videos
Set
Title = @Title,
Url = @Url,
Name = @Name,
Company = @Company,
ModifiedBy = @ModifiedBy
Where Id = @Id";
SqlCommand cmd = new SqlCommand(query, con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Id", model.Id);
cmd.Parameters.AddWithValue("@Title", model.Title);
cmd.Parameters.AddWithValue("@Url", model.Url);
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Company", model.Company);
cmd.Parameters.AddWithValue("@ModifiedBy", model.ModifiedBy);
con.Open();
await cmd.ExecuteNonQueryAsync();
con.Close();
}
return model;
}
}
}
VideoAppCore.Models.Tests 프로젝트
코드: VideoAppCoreModelsTest.cs
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Threading.Tasks;
namespace VideoAppCore.Models.Tests
{
[TestClass]
public class VideoAppCoreModelsTest
{
private readonly VideoRepositoryAdoNet _repository;
public VideoAppCoreModelsTest()
{
var connectionString =
"server=(localdb)\\mssqllocaldb;" +
"database=VideoAppCore;integrated security=true;";
_repository = new VideoRepositoryAdoNet(connectionString);
}
[TestMethod]
public async Task AddVideoAsyncTest()
{
//Video video = new Video
//{
// Title = "ADO.NET",
// Url = "URL",
// Name = "Park",
// Company = "VisualAcademy",
// CreatedBy = "Park"
//};
Video video = new Video
{
Title = "Dapper",
Url = "URL",
Name = "Park",
Company = "VisualAcademy",
CreatedBy = "Park"
};
Video newVideo = await _repository.AddVideoAsync(video);
Assert.AreEqual(1, newVideo.Id);
}
[TestMethod]
public async Task GetVideosAsyncTest()
{
var videos = await _repository.GetVideosAsync();
foreach (var video in videos)
{
Console.WriteLine($"{video.Id} - {video.Title}");
}
}
[TestMethod]
public async Task GetVideoByIdAsyncTest()
{
var video = await _repository.GetVideoByIdAsync(1);
Console.WriteLine($"{video.Id} - {video.Title}");
}
[TestMethod]
public async Task RemoveVideoAsyncTest()
{
await _repository.RemoveVideoAsync(1);
}
[TestMethod]
public async Task UpdateVideoAsyncTest()
{
Video video = new Video
{
Id = 4,
Title = "EF Core",
Url = "URL",
Name = "Park",
Company = "VisualAcademy",
ModifiedBy = "Park"
};
await _repository.UpdateVideoAsync(video);
}
}
}