SqlConnection을 이용한 간단한 C# 콘솔 응용 프로그램 작성

FSP 0 341 03.16 10:48

 

[실습 데이터]

SSMS를 실행 후 test 라는 DB를 생성, 데이블 및 데이터 생성

 

 CREATE TABLE EMP

        (EMPNO int CONSTRAINT PK_EMP PRIMARY KEY,

  ENAME VARCHAR(10),

    JOB  VARCHAR(9),

        SAL int

);

 

INSERT INTO EMP(empno, ename, job, sal) VALUES (7369,'SMITH','CLERK',800);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7499,'ALLEN','SALESMAN', 1600);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7521,'WARD','SALESMAN', 1250);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7566,'JONES','MANAGER',2975);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7654,'MARTIN','SALESMAN',1250);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7698,'BLAKE','MANAGER',2850);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7782,'CLARK','MANAGER',2450);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7788,'SCOTT','ANALYST',3000);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7839,'KING','PRESIDENT',5000);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7844,'TURNER','SALESMAN',1500);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7876,'ADAMS','CLERK',1100);

INSERT INTO EMP(empno, ename, job, sal) VALUES (7900,'JAMES','CLERK',950);

 

select * from emp;

 

create table member (

          id varchar(20) primary key,

          pwd varchar(20)

);

 

insert into member (id, pwd) values ('onj','onj');

 

SqlConnection을 이용한 간단한 C# 콘솔 응용 프로그램 작성

 

 

프로젝트명 : SqlConnectionExample

 

using System;

using System.Data;

using System.Data.SqlClient;

 

namespace SqlConnectionExample

{

    class Program

    {

        static void Main()

        {

            string connectionString = @"Data Source=localhost\OJC;Initial Catalog=test;Integrated Security=true";

 

            SqlConnection sqlConn = new SqlConnection(connectionString);

            SqlCommand sqlComm = new SqlCommand();

            sqlComm.Connection = sqlConn;

 

            sqlComm.CommandText = "select top 10 empno, ename, job, sal from emp where job=@Job order by empno asc";

            sqlComm.Parameters.AddWithValue("@Job", "Clerk");

            sqlConn.Open();

 

            SqlDataReader SqlRs;

            using (SqlRs = sqlComm.ExecuteReader(CommandBehavior.CloseConnection))

            {

                Console.WriteLine("Empno | Ename | Job  | Sal");

                Console.WriteLine("-----------------------------");

                while (SqlRs.Read())

                {

                    Console.WriteLine($"{SqlRs[0]} | {SqlRs[1]} | {SqlRs[2]} | {SqlRs[3]}");

                }

            }

            SqlRs.Close();

            //sqlConn.Close();

        }

    }

}

/*

Empno | Ename | Job  | Sal

---------------------------

7369 | SMITH | CLERK | 800

7876 | ADAMS | CLERK | 1100

7900 | JAMES | CLERK | 950

*/

 

위 예제를 OleDbConnection을 이용한 방식으로 변경해보자.

 

using System;

using System.Data;

using System.Data.OleDb;

 

namespace OleDbConnectionExample

{

    class Program

    {

        static void Main()

        {

            string conStr = @"Provider=SQLOLEDB;Data Source=localhost\OJC;Initial Catalog=test;Integrated Security=SSPI";

        

            OleDbConnection sqlConn = new OleDbConnection(conStr);

            OleDbCommand sqlComm = new OleDbCommand();

            sqlComm.Connection = sqlConn;

 

            sqlComm.CommandText = "select top 10 empno, ename, job, sal from emp " +

                                                "where job = ? " +

                                                "and sal > ? order by empno asc";

            sqlComm.Parameters.AddWithValue("@Job", "Clerk");

            sqlComm.Parameters.AddWithValue("@Sal", 800);

            sqlConn.Open();

 

            OleDbDataReader SqlRs;

            using (SqlRs = sqlComm.ExecuteReader(CommandBehavior.CloseConnection))

            {

                Console.WriteLine("Empno | Ename | Job  | Sal");

                Console.WriteLine("-----------------------------");

                while (SqlRs.Read())

                {

                    Console.WriteLine($"{SqlRs[0]} | {SqlRs[1]} | {SqlRs[2]} | {SqlRs[3]}");

                }

            }

            SqlRs.Close();

            //sqlConn.Close();

        }

    }

}

/*

Empno | Ename | Job  | Sal

---------------------------

7369 | SMITH | CLERK | 800

7876 | ADAMS | CLERK | 1100

7900 | JAMES | CLERK | 950

*/


 

Comments

KONA ELEVEN
-->