DataBase Access 기술동향

:::::[DataBase Access 기술동향]:::::

등장배경 :

• 처음에는 Data를 Access하는 공통 인터페이스가 존재하지 않았기 때문에 Oracle, IBM, Infomix 같은 각 DB 벤더들이 자신들만의 API 같은 데이터접근 방법을 제공하였다. 그렇기에 DB를 Access 해야 하는 개발자들은 이러한 각 벤더들 각각의 API들에 맞게 학습이 필요하였었다. 그렇기에 이러한 불편을 해결하고자 DB ACCESS 기술이 등장하였다.

   

DB ACCESS 기술의 변천사 :

ODBC(Open DataBase Connectivity)

다른 DB 공급업체가 특정 DBMS와 관련된 ODBC 드라이버를 통해 구현하는 API를 제공. 사용자가 만든 프로그램은 이 API를 사용하여 ODBC 드라이버 관리자를 호출하며 적절한 드라이버로 호출됨.   단점 : 관계형 데이터에만 적용 가능.

OLE DB(Object Linking and Embedding DataBase)

MS에서 개발한 고성능 COM기반 DB ACCESS 기술로서, 오늘날에도 많이 사용되어진다. Universal Data Access를 제공하며, 데이터의 저장형식(File System, 순차색인파일, Access, Excel, Outlook, Oracle)에 상관없이 Data에 접근을 제공하는 기술. 다양한 저장 형식과 관련된 다양한 Application 개발에 사용하여 이러한 Data에 Access하면 병목현상(Toutle Neck)이 발생하게 되는데, 현재 대부분의 기업이 이러한 병목현상으로 인해 많은 비용과 시간을 소비하고 있고, 이러한 문제의 대안으로 OLE DB가 선호되어진다.

ADO(Active Data Objects)

High Level 기술로서, OLE DB를 쉽게 사용할 수 있도록 한다. 현재로서는 몇몇 문제점들을 노출하고 있어 문제로 대두되고 있다.

ADO.NET ADO.Net은 OLE DB및 ODBC를 통해 노출되는 Data Source 뿐만 아니라 MS SQL server 및 XML과 같은 Data source에 대해 일관성 있는 접근을 제공한다.   응용 프로그램은 ADO.NET을 통해 이러한 Data Source에 연결하여 데이터를 검색, 조작 및 업데이트한다. ADO.NET 클래스는 System.Data.dll에 있으며 System.Xml.dll에 있는 XML 클래스와 통합되어 있다.   위 그림에서 Application단 과 ADO 사이에 위치하게 되며, 'SQL Client'를 통해 바로 DataBase로 접근이 가능하며, OLE DB나 ODBC를 통해 접근도 가능하다.     ADO.NET의 특징과 그 구조 ADO.NET의 특징 이전 버전인 ADO에서 제공하던 Server와의 연결을 항상 유지한 채 작동하는 연결형 DB 접근방식과 DB 서버와 연결이 끊어진 상태에서도 동작하는 비연결DB 접근 방식을 제공   연결형 DB 접근방식 Db서버에 연결된 상태에서 필요한 SQL문을실행하거나 저장 프로시저(SP: Stored Procedure)를 호출하는 방식으로 어플리케이션 단에서는 사용자의 요청만 처리하고 서버는 요청에 응답하는 지극히 단순한 방식.. 모든 데이터를 서버가 관리하므로 애플리케이션 단의 메모리 사용량이 적고 별다른 준비가 필요없음   반대로 다수의 애플리케이션단이 동시에 접속요청을 보내면 서버에 엄청난 부담을 준다. 따라서 다수의 클라이언트가 DB서버에서 무리없이 동작하려면 고성능의 서버, 네트웍 장비가 요구되고 대부분의 사용ㅇ DB서버는 연결 클라이언트 수에 따라 라이선스 비용을 지불하므로 그 비용도 만만치 않음.   비연결DB접근 방식 DB서버에 DB 서버 연결하며 필요한 데이터만 받고 바로 연결을 끊는 방식. 최초 연결시 DB서버에 완전한 복사본을 받아와서 오프라인에서 필요한 작업을 수행한 후 그 결과를 DB 서버로 보낼 때 다시 연결하여 그 결과를 처리하는 방식.   이 방식을 이용하면 동시 접속자수가 증가되는 장점이 있음. 이 비연결형 DB의 핵심은 메모리에 구축되는 DataSet이다.   단점 -애플리케이션단에 대량의 데이터를 메모리에 계속 로드한 채 작업을 수행하므로 엄청난 리소스를 소모하게 된다. 보통 DB의 용량이 수 MB~ 수백 MB이므로 이 크기만큼 memory를 점유하게 된다. -가져오는 시간도 연결형보다 많이 걸린다. -가져온 사본을 조작하므로 새로운 개체를 생성하는 DDL 명령은 사용불가.   ADO.NET의 구조 ADO.Net은 구조적으로 .NET Data Provider와 DataSet으로 나뉜다. .NET Provider 연결형 DB접근 방식 제공. DB와 지속적인 연결된 상태에서 동적 작업을 수행 대표적인 연결지향형 Class에는 "Command"가 있다. DataSet 비연결형 DB접근 방식 제공. 초기에 DB와 연결하여 정보를 Memory로 Load하여 작업을 수행하며, 필요시 DB에 다시 접근하여 Update를 시도한다.       .Net Data Provider는 DB를 연결하고 SQL문을 실행시킨다. DataSet은 데이터베이스의 데이터를 DataSet으로 쉽게 이식할 수 있으며, DB와 분리되어 DataSet만으로도 대부분 작업을 할 수 있다.

 

 

각 공급자별 관련 Namespace 살펴보기

 

공급자(Provider)	NameSpace
SQL Server Provider	System.Data.SqlClient
Oracle Provider	System.Data.OracleClient
ODBC Provider	System.Data.Odbc
OLE DB Provider	System.Data.OleDb

 

:::::[ 실습 ]:::::

실습환경 구성하기

MS SQL management Studio가 설치되어 있지 않다면, MS에서 DOWN받아 설치한다.

 

SQL 접속 인증방식 변경하기

기본적으로 MSSQL을 설치하게 되면 WIDOWS 인증(=OS인증)으로 접속된다.

이를 SQL 인증으로 변경하여 보자.

 

MSSQL 2005 인증방법
WINDOWS 인증	SQL 인증	혼합인증(Windows 인증+SQL인증)

 

MSSQL SM Studio를 실행하여 아래 그림과 같이 오른쪽 Pop UP 메뉴에서 '속성'을 선택한다. 새롭게 뜬 창에서 '보안' Tab으로 이동하여 서버인증에서 'SQL Server 및 Windows 인증 모드(S)'로 변경한다.

 

 

그런 다음 아래와 같이 '컴퓨터관리'로 이동하여 사용자 'sa'를 생성하여 준다.

 

MS server Management Studio에서 생성된 사용자 sa를 선택 후, 아래와 같이 '일반' Tab에서 인증모드의 변경 및 암호를 설정하여 준다. 그리고 '상태'Tab에서 '로그인'을 사용으로 변경하여 계정을 활성화시켜준다.

 

 

 

이제 SQL 서버를 재 구동시켜주거나 아래와 같이 서비스를 아래와 같이 변경시켜 준 후, 로그인을 한다.

 

 

 

 

 

:::::[SqlClient의 MSSQL 2005 연결(Connection)]:::::

 

SQL SERVER PROVIDER(SQL CLIENT)는 OleDB, ODBC와 같은 중간 계층없이 MSSQL 서버에 직접 연결하여 처리하므로 속도가 빠르다.

 

주요 순서

-관련 네임스페이스명시

Using System.Data;

Using System.Data.SqlClient;

 

-Connection 객체 생성

연결문자열 strConn="server=localhost;database=NorthWind; user id=sa; password=dba007$'

SqlConnection Conn=new SqlConnection(strConn); //Connection 객체 생성

 

Connection 객체 생성시 사용되는 연결문자열

DB서버에서 중요한 정보가 존재하므로 서버가 요구하는 인증절차를 거쳐서 DB서버에 접근하게 되는데 이 때 필요한 것이 바로 연결문자열이다.   속성 기능 Server, Datasource 연결할 서버의 instance Database, Initial Catalog 연결할 DB 이름 Trusted_Connection,Integrated Security Default값은 False이며, user id, password 같은 것을 추가하고 만약에 True이면윈도우 인증모드 사용. User id DB 서버 사용자지정 Password DB 서버 사용자 계정 암호

 

-DB에 연결

Conn.Open();

 

-필요한 작업 수행

 

-DB연결 끊기

Conn.Close();

 

Console 기반에서 작성된 Source

using System; using System.Collections.Generic; using System.Linq; using System.Text;   //1.NameSpace 추가 using System.Data; using System.Data.SqlClient;   namespace sqlclientex1 { class Program { static void Main(string[] args) { //2.문자열 & 연결객체 생성 { try { //mssql2005서버에 연결할 연결문자열 string strConn="server=localhost;database=NorthWind;user id=sa;password=dba007$";   //Conn이라는 SqlConnection 객체 생성 SqlConnection Conn=new SqlConnection(strConn);   //데이터베이스 연결 Conn.Open();   //필요한 작업 실행 Console.WriteLine("Database : " + Conn.Database);//연결하는 DB 알려주기 Console.WriteLine("Datasource : " + Conn.DataSource);//연결하는 DB 서버 인스턴스 Console.WriteLine("Database server version : " + Conn.ServerVersion); //서버 버전보기 Console.WriteLine("Connection state : " + Conn.State);   //데이터베이스 연결 종료 Conn.Close(); } catch (SqlException se) { Console.WriteLine(se.ToString()); } } } } }

 

 

SqlCommand 클래스

DB에 연결된 상태에서 DB에 필요한 명령을 전달하거나 그 결과를 받아올 때 사용되는 클래스

 

SqlCommand에서 할 수 있는 일들

1. DML(Data Manipulation Language) Select, Delete, UPDATE, INSERT
2. DCL(Data Control Language) Grant, Revoke, ….
3. DDL(Data Definition Language) Create, Alter, Drop, Rename, …..

 

SqlCommand 클래스의 주요 메서드

메서드(함수)	기능
ExecuteNonQuery()	SQL문을 실행하고 그 처리된 행수 반환
ExecuteReader()	쿼리를 Connection으로 보내고 SqlDataReader를 생성
ExecuteScalar()	쿼리를 실행하고 반환된 결과 집합의 첫 번째 행의 첫 번째 열 변환
ExecuteXmlReader()	쿼리를 Connection으로 보내고 XmlReader를 생성

 

SqlCommand 클래스의 주요 속성

속성	기능
CommandText	데이터 소스에서 실행할 sql문이나 저장 프로시저를 가져오거나 설정
CommandType	CommandText이 해석될 방법을 나타냄
Connection	SqlCommand 객체에서 사용하는 SqlConnection을 가져오거나 설정
Parameters	SqlParameterCollection을 가져옴.
Transaction	SqlCommand가 실행하는 트랜잭션을 가져오거나 설정

 

SqlCommand 클래스의 ExcuteNonQuery() 메서드 이용하는 방법

 

실습을 위해 아래와 같이 테이블을 하나 생성

create table ExecuteNonQuery( name varchar(12) ,tel varchar(15) )

using System; using System.Collections.Generic; using System.Linq; using System.Text;   //네임스페이스 추가 using System.Data; using System.Data.SqlClient;   namespace excutenonqueryex { class Program { static void Main(string[] args) { try { //연결문자열 string strCon = "server=localhost;database=mytest;user id=sa;password=dba007$"; //처리할 SQL 문자열 생성 string strqry1 = "INSERT INTO ExecuteNonQuery VALUES('송병구','0114443333')"; string strqry2 = "INSERT INTO ExecuteNonQuery VALUES('이윤열','0113333423')"; string strqry3 = "INSERT INTO ExecuteNonQuery VALUES('마재윤','0197683423')";   //ExecuteNonQeury()로 리턴받을 변수 선언 int count;   //Connection 객체를생성과 열기 SqlConnection conn = new SqlConnection(strCon); conn.Open();   //SqlCommand 클래스 이용 객체 생성 SqlCommand comm = new SqlCommand(strqry1, conn); SqlCommand comm1 = new SqlCommand(strqry2, conn); SqlCommand comm2 = new SqlCommand(strqry3, conn);   //결과값 받기 count = comm.ExecuteNonQuery(); Console.WriteLine("ExecuteNonQeury()실행 결과 누적분 : {0}", count); count = count+comm1.ExecuteNonQuery(); Console.WriteLine("ExecuteNonQeury()실행 결과 누적분 : {0}", count); count = count+comm2.ExecuteNonQuery(); Console.WriteLine("ExecuteNonQeury()실행 결과 누적분 : {0}", count);   //연결해제 conn.Close();   } catch (SqlException se) {Console.WriteLine(se.ToString()); } } } }

 

 

 

 

SqlCommand 클래스의 Parameters 속성

• 반복적인 쿼리작업이나 쿼리가 복잡한 경우 사용

 

Parameter 변수를 선언하는 방법

String strqry="INSERT INTO TableName (@name, @Tel) ";

 

Parameter 들어갈 변수의 데이터 타입 설정하는 방법

Comm.Parameters.Add("@name",SqlDbType.Varchar,12);

,SqlDbType.Varchar,12); able)

Parameter의 값을 설정하는 방법

Comm.Parameters["@name"].Value="송미리';

 

실행

Comm.ExecuteNonQeury();

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient;   namespace _512 { class Programd { static void Main(string[] args) { try { //문자열 생성 string strConn = "server=localhost;database=mytest;user id=sa;password=dba007$";   //sqlConnection 객체 생성 및 열기 SqlConnection Conn = new SqlConnection(strConn);   //DB연결 Conn.Open();   //parameter변수 선언 string strqry = "insert into ExecuteNonQuery Values(@name,@Tel)";   //sqlCommand객체 생성 SqlCommand comm = new SqlCommand(strqry, Conn);   //sqlCommand 데이터 타입 설정 comm.Parameters.Add("@name", SqlDbType.VarChar, 12); comm.Parameters.Add("@Tel", SqlDbType.VarChar, 15);   comm.Parameters["@name"].Value = "송미리"; comm.Parameters["@Tel"].Value = "010-546-5410";   comm.ExecuteNonQuery();   Console.WriteLine("데이터가 정상적으로 입력되었습니다"); Conn.Close();   } catch (SqlException e) { Console.WriteLine(e.ToString()); } finally { Console.ReadLine(); }   } } } /* using System; using System.Collections.Generic; using System.Linq; using System.Text;   //NameSpace 추가 using System.Data; using System.Data.SqlClient;   namespace ParameterEx1 { class Program { static void Main(string[] args) { try { //연결문자열 생성 string strCon = "server=localhost;database=mytest;user id=sa;password=dba007$";   //SqlConnection객체 생성 및 OPEN SqlConnection Conn = new SqlConnection(strCon); Conn.Open();   string strqry = "INSERT INTO ExecuteNonQuery VALUES(@name,@tel)"; //SqlCommand 객체 생성 SqlCommand Comm = new SqlCommand(strqry, Conn);   //SqlCommand 데이터 타입 설정 Comm.Parameters.Add("@name", SqlDbType.VarChar, 12); Comm.Parameters.Add("@Tel", SqlDbType.VarChar, 15);   //SqlCommand Parameter에 값 설정하기 Comm.Parameters["@name"].Value = "송미리"; Comm.Parameters["@tel"].Value = "012-345-6789";   //실행하기 Comm.ExecuteNonQuery();   Console.WriteLine("정상적으로 데이터가 입력되었습니다");   //DB 연결 닫기 Conn.Close(); } catch(SqlException se) { Console.WriteLine(se.ToString()); } Console.ReadLine(); } } } */

 

응용 문제

콘솔 환경에서 사용자로부터 이름과 전화번호를 입력받아 앞서 예제 테이블인 ExecuteNonQuery 테이블 데이터를 넣는 프로그램을 작성하라(단, Parameter 속성을 이용해서~)

재현이 방법>

using System; using System.Collections.Generic; using System.Text; using System.Data; // 추가 using System.Data.SqlClient; // 추가   namespace Parameter_Test { class Program { static void Main(string[] args) {   try { // 문자열 설정 string strCon = "server=.\\SQLEXPRESS;database=mytest;user id=sa; password=dba007$";   // sqlConnection 객체 생성 및 연결 SqlConnection Conn = new SqlConnection(strCon); Conn.Open();   string strqry = "INSERT INTO ExecuteNonQuery Values(@name, @Tel)"; // 여기서의 ExecuteNonQuery는 테이블 명이다.   // sqlCommand 객체 생성 SqlCommand Comm = new SqlCommand(strqry, Conn);   // sqlCommand 데이터 타입 설정 Comm.Parameters.Add("@name", SqlDbType.VarChar, 12); Comm.Parameters.Add("@Tel", SqlDbType.VarChar, 15);   // 입력받은 데이터를 저장할 변수 선언 string inName; string inTel;   // 데이터 입력받기 Console.WriteLine("이름과 전화번호를 입력하세요"); Console.Write("이름 : "); inName = Convert.ToString(Console.ReadLine()); Console.Write("전화번호 : "); inTel = Convert.ToString(Console.ReadLine());   // sqlCommand 데이터 입력 Comm.Parameters["@name"].Value = inName; Comm.Parameters["@Tel"].Value = inTel;   // sqlCommand 실행 Comm.ExecuteNonQuery(); // 여기서의 ExecuteNonQuery 는 메서드다 } catch (SqlException se) { Console.WriteLine(se.ToString()); }   } } }

 

SqlCommand 클래스의 ExecuteReader

 

Select 문을 처리할 때 사용하는 유용한 메서드

 

데이터를 읽어오는 방법

SqlDataReader rd=Comm.ExecuteReader();

 

필요한 네임스페이스 명시

Using System.Data; Using System.Data.SqlClient;

연결 문자열을 만들고 Connection 객체 생성 및 열기

strCon ~~ SqlConnnection Con=new SqlConnection(strCon); Con.Open();

Command 클래스 객체 생성

SqlCommand comm.=new SqlCommand("SELECT * FROM mytest",Con);

실제 데이터 가져오기

SqlDataReader rd=new comm..ExecuteReader();

연결 끊기

Con.Close();

using System; using System.Collections.Generic; using System.Text; using System.Data; //1. Namespace 추가 using System.Data.SqlClient; //1. Namespace 추가   namespace ExecuteReaderEx { class Program { static void Main(string[] args) { //2. Try~ Catch 구조생성 try { //3.연결문자열생성 string DBconnect = "server=localhost;database=mytest;user id=sa;password=dba007$";   //4. SqlConnection 객체생성및열기 SqlConnection Conn = new SqlConnection(DBconnect); Conn.Open();   //5.SqlCommand 객체생성 //string strqry = "SELECT * FROM ExecuteQuery"; SqlCommand Comm = new SqlCommand("SELECT * FROM ExecuteNonQuery", Conn);   //6.실제데이터가져오기 SqlDataReader sdr = Comm.ExecuteReader();   //7. 가져온데이터의출력유형지정하기 //sdr.Read() -> (다음레코드를이동해서데이터가더있으면True, 없으면False) while (sdr.Read()) { //7-1 GetString(첨자) 첨자는각테이블의각column 순서와일치시켜야한다. Trim-> 공백제거함수 Console.WriteLine(sdr.GetString(0).Trim() ); Console.WriteLine(sdr.GetString(1).Trim()); }   //8. 연결닫기 sdr.Close(); Conn.Close(); } catch(SqlException se) { Console.WriteLine(se.ToString()); } Console.ReadLine(); } } }

 

 

 

 

ReaderListViewEx

 

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms;     using System.Data.SqlClient;   namespace ReaderListViewEx { public partial class Form1 : Form { public Form1() { InitializeComponent(); }   private void listView1_SelectedIndexChanged(object sender, EventArgs e) {   }   private void Form1_Load(object sender, EventArgs e) { //1. ListView Header 초기화 listView1.Columns.Add("Name", listView1.Width / 4, HorizontalAlignment.Left); listView1.Columns.Add("Tel", listView1.Width / 2, HorizontalAlignment.Right);   //2.Try ~ cahtch 구문생성 try { //3.연결문자열생성 string DBconnect = "server=localhost;database=mytest;user id=sa;password=dba007$"; ; string strqry = "SELECT * FROM ExecuteNonQuery";   //4. SqlConnection 객체생성 SqlConnection Conn = new SqlConnection(DBconnect); Conn.Open();   //5.SqlCommand 객체생성 SqlCommand Command = new SqlCommand(strqry,Conn);   //6.데이터읽어오기 SqlDataReader sdr = Command.ExecuteReader();   //7. 읽어온데이터를ListView에출력하기 int count = 1; while (sdr.Read()) { listView1.Items.Add(sdr["name"].ToString()); listView1.Items[count- 1].SubItems.Add(sdr["Tel"].ToString()); count = count + 1;   }   //8. 연결해제 sdr.Close(); Conn.Close();   } catch(RankException se) { Console.WriteLine(se.ToString()); } } } }

 

위에서 실습한

Parameter 속성과 ExecuteReader 속성을 병행해서 사용하기

using System; using System.Collections.Generic; using System.Text;   using System.Data; using System.Data.SqlClient;   namespace ParaAndExcuteReaderEx1 { class Program { static void Main(string[] args) { try { //(1) 연결문자열생성   //__양자택일하여아래두가지방법중하나로인증한다. //string DBConnect = "server=localhost;database=mytest;user id=sa;password=dba007$"; //OS 인증모드로접속을하려면 string DBConnect = "server=localhost;database=mytest;Integrated Security=true"; string strqry="SELECT * FROM ExecuteNonQuery where name= @name OR Tel=@tel";   //(2) SqlConnection 객체생성 SqlConnection SqlConn = new SqlConnection(DBConnect); SqlConn.Open();   //(3) SqlCommand 객체생성 SqlCommand SqlComm=new SqlCommand(strqry,SqlConn);   //(4) Parameter 데이터타입설정 SqlComm.Parameters.Add("@name",SqlDbType.VarChar,12); SqlComm.Parameters.Add("@tel",SqlDbType.VarChar,15);   //(5) Where 절에들어갈값을설정하기 SqlComm.Parameters["@name"].Value="이윤열"; SqlComm.Parameters["@tel"].Value="0113333433";   //(6) ExecuteReader()로필요한결과SET 받아오기 SqlDataReader sdr=SqlComm.ExecuteReader();   //(7) Console창에출력하기 while(sdr.Read()) { Console.WriteLine(sdr.GetString(0)+"\t"); Console.WriteLine(sdr.GetString(1)); }   //(8) 사용완료된객체닫아주기 sdr.Close();   SqlConn.Close();     } catch(SqlException e) { Console.WriteLine(e.ToString()); } Console.ReadLine(); } } }

 

실습문제

Hakgwa(학과) 라는 테이블을 만든다. Create Table Hakgwa ( Name varchar2(12), Hakbun char(6) not null, Subject varchar(20) ) Console에서 아래와 같은 데이터를 입력 이재현,90001,컴퓨터공학 허사장,90002,부동산학과 김현진,90003,연극영화과   위에 저장된 데이터 중 학과가 컴퓨터공학과인 사람을 출력하시오