OleDb를 이용한 DataBase연결

[:::::OleDb를 이용한 DataBase연결:::::]

목차

MSSQL과 OLEDB를 통한 연동

MS ACCESS와 OLEDB를 통한 연동

ORACLE Server와 OLEDB를 통한 연동

 

MSSQL과 OLEDB를 통한 연동

개략적 Map

NameSpace 추가

NameSpace : System.Data & System.Data.OleDb

연결문자열

strOleConn="Provider=SQLOLEDB.1;Server=localhost;database=mytest; user id=sa; password=dba007$"; or strOleConn="Provider=SQLOLEDB.1;Server=localhost;database=mytest; Integrated Security=SSPI; Initial Catalog=mytest; Data Source=localhost";

연결 객체 생성 및 열기

OleDbConnection OleConn=new OleDbConnection(strOleConn); OleConn.Open();

처리해야 할 작업 수행

연결닫기

OleConn.Close();

 

실습1

using System; using System.Collections.Generic; using System.Text;   //1 namespace 추가   using System.Data; using System.Data.OleDb;   namespace OleDBConnectionEX { class Program { static void Main(string[] args) { //2 Try Catch try { //3. Connection String 생성(Data Source부의 내용은 HOSTNAME) //(IP나 Localhost)를 입력시 오류발생. 추후 살펴볼만한 사항. string strOleConn = "Provider = SQLOLEDB; Data Source=VMWIN2003; Initial Catalog=mytest; User id=sa; password=dba007$";   //4. Connection OleDbConnection OleConn = new OleDbConnection(strOleConn); OleConn.Open();   //5. 필요작업수행 Console.WriteLine("1. OleConn.Database=" + OleConn.Database); Console.WriteLine("2. OleConn.ServerVersion=" + OleConn.ServerVersion); Console.WriteLine("3. OleConn.Provider=" + OleConn.Provider); Console.WriteLine("4. OleConn.Data Source=" + OleConn.DataSource);   //6.연결끊기 OleConn.Close();     } catch(Exception E) { Console.WriteLine(E.ToString()); } Console.ReadLine(); } } }

 

 

 

 

 

실습2

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb;   namespace ExecuteNonQueryex { class Program { static void Main(string[] args) { try { //연결문자열생성 string strOledbConn = "Provider=SQLOLEDB.1;server=VMWIN2003;database=mytest;user id=sa;password=dba007$";   //OledbConnection 객체생성및열기 OleDbConnection oledbConn = new OleDbConnection(strOledbConn); oledbConn.Open();   //oleCommand 객체생성 string strqry = "Create Table Oledbtest(name varchar(12),cellphone varchar(15))"; OleDbCommand oledbComm = new OleDbCommand(strqry, oledbConn);     //ExecuteNonQuery 실행 int intreturn= oledbComm.ExecuteNonQuery();   if (intreturn == -1) { Console.WriteLine("정상적으로처리되었습니다"); //db연결끊기 oledbConn.Close(); } else { Console.WriteLine("뭔가문제가있군요"); oledbConn.Close(); }     } catch (Exception es) { Console.WriteLine(es.ToString()); } finally { Console.ReadLine(); }   } } }

 

 

 

 

 

 

실습3

Mytest 데이터베이스에 아래와 같이 SEMIPROJECT 테이블을 생성하고 해당 데이터를 입력하고 그 결과를 출력하시오.   name Note 홍길동 홍길동이 하는 일 향단이 향단이가 하는 일

using System; using System.Collections.Generic; using System.Text;   //NameSpace add using System.Data; using System.Data.OleDb;   namespace OleDbEx3 { class Program { static void Main(string[] args) { try { //OLEDB Connection string strOleDbConn = "PROVIDER=SQLOLEDB.1; server=VMWIN2003; database=mytest; user id=sa; password=dba007$"; OleDbConnection OleDbConn = new OleDbConnection(strOleDbConn); OleDbConn.Open();   string createquery = "CREATE TABLE SEMIPROJECT(name varchar(12),note varchar(50))"; string query1 = "INSERT INTO SEMIPROJECT VALUES('유우종','ALL Around Player1')"; string query2 = "INSERT INTO SEMIPROJECT VALUES('박현선','ALL Around Player2')"; string query3 = "INSERT INTO SEMIPROJECT VALUES('김태진','ALL Around Player3')"; string query4 = "INSERT INTO SEMIPROJECT VALUES('정병화','ALL Around Player4')"; string query5 = "INSERT INTO SEMIPROJECT VALUES('DionysosJH','ALL Around Player5')";   OleDbCommand comm = new OleDbCommand(createquery, OleDbConn); OleDbCommand comm1 = new OleDbCommand(query1, OleDbConn); OleDbCommand comm2 = new OleDbCommand(query2, OleDbConn); OleDbCommand comm3 = new OleDbCommand(query3, OleDbConn); OleDbCommand comm4 = new OleDbCommand(query4, OleDbConn); OleDbCommand comm5 = new OleDbCommand(query5, OleDbConn);   comm.ExecuteNonQuery(); comm1.ExecuteNonQuery(); comm2.ExecuteNonQuery(); comm3.ExecuteNonQuery(); comm4.ExecuteNonQuery(); comm5.ExecuteNonQuery();   OleDbConn.Close();   Console.WriteLine("정상적으로입력되었습니다"); } catch (Exception E) { Console.WriteLine(E.ToString()); } Console.ReadLine(); } } }

 

사용자가 콘솔에서 직접 컬럼값 입력하기(충무형꺼)

using System; using System.Collections.Generic;   using System.Text; using System.Data; using System.Data.OleDb; using System.IO;   namespace mysql_13 { class Program { static void Main(string[] args) { StreamWriter se = new StreamWriter(@"c:\semiproject.txt", true); se.WriteLine("\n" + "---------------------------------------------------------------------------------------------------------"); se.WriteLine("프로그램시작" + "\t" + DateTime.Now + "\n"); se.WriteLine("---------------------------------------------------------------------------------------------------------" + "\n");   try { string strConn = "PROVIDER=SQLOLEDB.1; server=VMWIN2003; database=mytest; user id=sa; password=dba007$"; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open();     string order1 = "CREATE TABLE semiproject(name varchar(12),note varchar(50))"; OleDbCommand orderCom = new OleDbCommand(order1, OleConn);   int reint; reint = orderCom.ExecuteNonQuery(); if (reint == -1) { do { string name, note; string order2 = "INSERT INTO semiproject values(?, ?)";   OleDbCommand orderCom2 = new OleDbCommand(order2, OleConn);   orderCom2.Parameters.Add("name", OleDbType.VarChar); orderCom2.Parameters.Add("note", OleDbType.VarChar);   Console.WriteLine("이름을입력하세요"); name = Console.ReadLine(); orderCom2.Parameters["name"].Value = name.Trim(); se.WriteLine("이름입력" + "\t" + name + "\t" + DateTime.Now); se.WriteLine("\n");   Console.WriteLine("하는일을간단히입력하세요"); note = Console.ReadLine(); orderCom2.Parameters["note"].Value = note.ToString(); se.WriteLine("하는일입력시간" + "\t" + note + "\t" + DateTime.Now); se.WriteLine("\n");   int reord = orderCom2.ExecuteNonQuery(); if (reord == -1) { Console.WriteLine("정상입력완료"); } else { } Console.WriteLine("입력이다끝났으면q를누르세요");   }   while (Console.ReadLine() != "q"); Console.WriteLine("정상처리완료"); se.WriteLine("정상처리완료" + "\t" + DateTime.Now); se.WriteLine("\n" + "---------------------------------------------------------------------------------------------------------" + "\n"); OleConn.Close(); } else { Console.WriteLine("프로그램이상발생"); se.WriteLine("프로그램이상발생" + "\t" + DateTime.Now); se.WriteLine("\n" + "---------------------------------------------------------------------------------------------------------" + "\n"); OleConn.Close(); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); se.WriteLine(ex.ToString()); se.WriteLine("발생시간" + "\t" + DateTime.Now); se.WriteLine("\n" + "---------------------------------------------------------------------------------------------------------" + "\n"); se.Flush(); se.Close(); Console.ReadLine(); } se.WriteLine(DateTime.Now + "\t" + "작업종료"); se.WriteLine("\n" + "---------------------------------------------------------------------------------------------------------" + "\n"); se.Flush(); se.Close(); } } }

 

 

 

 

 

 

 

 

 

 

 

MS_Access 2007과 OLEDB를 통한 연동

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb;   namespace AccessPlay { class Program { static void Main(string[] args) { try { //연결문자열 //MS 2003 일 경우 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\testaccess\\mytestAccess.accdb; Persist Security Info=false"; //MS2007 일 경우 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\testaccess\\mytestAccess.accdb; Persist Security Info=false";   //연결객체생성 OleDbConnection Conn = new OleDbConnection(strConn); Conn.Open();   //command 객체생성 string strqry = "CREATE TABLE Book(bname text not null, Editor text not null, notes text)"; string strqry1 = "INSERT INTO Book VALUES('부의미래','엘빈토플러','돈이최고다')"; OleDbCommand Comm = new OleDbCommand(strqry, Conn);   //ExecuteNonQuery() 실행 Comm.ExecuteNonQuery();   int intreturn; OleDbCommand comm1 = new OleDbCommand(strqry1, Conn); intreturn = comm1.ExecuteNonQuery();   if (intreturn > 0) { Console.Write("정상적으로입력되었습니다"); } Conn.Close(); } catch (Exception E) { Console.WriteLine(E.ToString()); } Console.ReadLine(); } } }

 

ORACLE 과 OLEDB를 통한 연동

우선 Client ORACLE 서버의 tnsnames.ora 파일에서 자신이 연결해줄 상대방 오라클 서버의 설정을 추가하여 준다.

그리고 lsnrctl 서비스를 재시작 시켜준다.

(Oracle Client Program이 설치되어 있다는 전제하이며, 없다면 CD를 통해서나 다운을 통해 설치되어져 있어야 한다.)

Tnsnames.ora 파일

Temp = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 211.254.138.224)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ocp181.iei.or.kor) ) )

HOST=Destination Oracle Server IP SERVICE_NAME=ORACLE SID와 DOMAIN 정보

 

 

그런 다음 C#.NET에서 아래와 같이 CS 파일을 Coding 하여 실행하여 보면 잘 접속되는 것을 확인할 수 있다.

 

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.OleDb;

 

namespace oracleConnexreder

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

 

private void button1_Click(object sender, EventArgs e)

{

try

{

string OracleConn = "Provider=MSDAORA;Data Source=Temp;user id=scott;password=tiger";

// tnsnames.ora 파일에서 지정한 이름을 Data Source에서 기입하여 준다!

OleDbConnection OledbConn = new OleDbConnection(OracleConn);

 

OledbConn.Open();

 

//Command 객체생성

string strqry = "Select * from dept";

OleDbCommand OledbComm = new OleDbCommand(strqry, OledbConn);

 

//OleDbDataReader사용하기

OleDbDataReader sdr = OledbComm.ExecuteReader();

 

while (sdr.Read())

{

for (int i = 0; i < sdr.FieldCount; i++)

{

textBox1.AppendText(sdr.GetValue(i)+"\t");

}

}

sdr.Close();

OledbConn.Close();

}

catch (Exception es)

{

Console.WriteLine(es.ToString());

}

finally

{

Console.ReadLine();

}

}

}

}