C# - EX02 - MSSQL 과 C# 연동 효율적인 방법 ( 콘솔버젼 )

using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace Ex02
{
    class Program
    {
        static void Main(string[] args)
        {

            ////1. 연결 객체 생성 및 연결 정보
            //SqlConnection conn = new SqlConnection();

            ////conn.ConnectionString = "SERVER=.;DATABASE=Northwind;INTEGRATED SECURITY=SSPI;";

            //// 추가 -> 새 항목 -> 응용 프로그램 구성 파일( App1.config) 생성후 DB정보 입력
            //// 참조추가 System.ConfigurationManager  그리고 네임스페이스 추가
            //conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;           

            ////2. 명령 객체 생성 및 [SQL, 연결객체] 설정
            //SqlCommand cmd = new SqlCommand();
            //cmd.Connection = conn;
            //cmd.CommandText = "SELECT customerid, companyname, contactname FROM customers ";

            ////  cmd.CommandText = "SELECT customerid FROM customers ";

            ////3. 연결 열기
            //conn.Open();

            ////4. 명령 실행
            //SqlDataReader reader = cmd.ExecuteReader();

            //5. 결과가 있다면 결과를 처리

            NWData data = new NWData();
            List<Customer> Customers = data.GetCustomers();

            foreach(Customer Customer in Customers)
            {
                Console.WriteLine("[{0}][{1}][{2}]",
                               Customer.CustomerID,
                               Customer.CompanyName,
                               Customer.ContactName);
            }

            //while (reader.Read())
            //{
            //    Console.WriteLine("[{0}][{1}][{2}]",
            //                   reader.GetString(0),
            //                   reader[1],
            //                   reader["companyname"]);
            //}

            ////6. 연결 닫기
            //reader.Close();
            //conn.Close();
        }
    }
}
-----------------------------------------------------------------------------------------------------------
클래스 추가 NWData

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace Ex02
{
    class NWData
    {
        public List<Customer> GetCustomers()
        {

            //1. 연결 객체 생성 및 연결 정보
            SqlConnection conn = new SqlConnection();

            //--------------- 다른 방법  ---------------------------------------------------------
            //conn.ConnectionString = "SERVER=.;DATABASE=Northwind;INTEGRATED SECURITY=SSPI;";
            //--------------- 다른 방법 ----------------------------------------------------------
            //conn.ConnectionString = "SERVER=172.16.7.1,1433;" +      // 서버 정보
            //                        "DATABASE=Northwind;" +      // DB 정보
            //                        "INTEGRATED SECURITY=SSPI;";           // 계정 정보(윈도우 계정)
            //                        "UID=sa;PWD=knit";         // 아아뒤 / 비번 (SQL 계종)
            //--------------- 다른 방법 ----------------------------------------------------------

            //conn.ConnectionString = "DATA SOURCE=172.16.7.1,1433;" +      // 서버 정보
            //                      "INITIAL CATALOG=Northwind;" +      // DB 정보
            //    //    "INTEGRATED SECURITY=SSPI;";           // 계정 정보(윈도우 계정)
            //                       "USER ID=sa;PASSWORD=knit";         // 아아뒤 / 비번 (SQL 계종)

            //-----------------------------------------------------------------------------------
           
            conn.ConnectionString = 
                ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;  // Northwind 테이블 이름

 

            //2. 명령 객체 생성 및 [SQL, 연결객체] 설정
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT customerid, companyname, contactname FROM customers ";

            //  cmd.CommandText = "SELECT customerid FROM customers ";

            //3. 연결 열기
            conn.Open();

            //4. 명령 실행
            SqlDataReader reader = cmd.ExecuteReader(); // 제공된 SQL 실행

            //5. 결과가 있다면 결과를 처리
            List<Customer> customers = new List<Customer>();
            while (reader.Read())
            {
                Customer Customer = new Customer();
                Customer.CustomerID = reader["customerid"].ToString();
                Customer.CompanyName = reader["companyname"].ToString();
                Customer.ContactName = reader["contactname"].ToString();
                customers.Add(Customer);
            }

            //6. 연결 닫기
            reader.Close();
            conn.Close();

            return customers;
        }
    }
}

-----------------------------------------------------------------------------------------------------------
클랙스 추가 Customer

using System;
using System.Collections.Generic;
using System.Text;

namespace Ex02
{
    class Customer
    {
        //private string CustomerID;
        //public string CustomerID1
        //{
        //    get { return CustomerID; }
        //    set { CustomerID = value; }
        //}  이와 같은 걸 아래와같이 간단하게 표기 가능

        public string CustomerID { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string Country { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }

    }
}

----------------------------------------------------------------------------------------------------------------
//// 추가 -> 새 항목 -> 응용 프로그램 구성 파일( App1.config )생성후 DB정보 입력

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <connectionStrings>

    <add name ="Northwind"
         connectionString="SERVER=.;DATABASE=Northwind;INTEGRATED SECURITY=SSPI;"
         providerName="System.Data.SqlClient" />
    <add name ="Pubs"
         connectionString="SERVER=.;DATABASE=Northwind;INTEGRATED SECURITY=SSPI;"
         providerName="System.Data.SqlClient" />
   
  </connectionStrings>
 
 
</configuration>

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