C# - Ex03 - MSSQL 과 C# 연동 - 불러오기 와 삭제 (윈폼 버젼)

DB 내용 불러오기 와 삭제
DataLib -> customer.cs, NEWData.cs 는 소스 드래고 해서 만들어주세요~!

20101014.zip

=================     EX03     ===============

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DataLib.Persistence;
using DataLib.Entity;

namespace Ex03
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void LoadCustomers()
        {
            NWData data = new NWData();
            List<Customer> Customers = data.GetCustomers();
            //1. Control에 데이터 직접 적용
            //lbxCustomers.Items.Clear();
            //foreach(Customer Customer in Customers)
            //{
            //    //리스트박스에 항목 추가
            //    lbxCustomers.Items.Add(Customer.CompanyName);
            //}

            //2. Data Binding (데이터를 컨트롤에 자동으로 적용)  Binding 사용 하는것이 좋다
            lbxCustomers.DataSource = Customers;
            lbxCustomers.DisplayMember = "ContactName"; // 표시할 객체의 속성 이름
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            LoadCustomers();
        }

        private void lbxCustomers_SelectedIndexChanged(object sender, EventArgs e)
        {
            //리스트박스에서 선택된 항목을 가져와서
            //항목의 내용을 우측 그룹박스내의 텍스트박스에 표시

            //형변환 실패하면 오류 발생
            // Customer Customer = (Customer)lbxCustomers.SelectedItem;

            //형변환 실패하면 null 반환
            Customer Customer = lbxCustomers.SelectedItem as Customer;

            txtCustomerID.Text = Customer.CustomerID;
            txtCompanyName.Text = Customer.CompanyName;
            txtContactTitle.Text = Customer.ContactTitle;
            txtContactName.Text = Customer.ContactName;
            txtCountry.Text = Customer.Country;
            txtPhone.Text = Customer.Phone;
            txtFax.Text = Customer.Fax;
        }

        private void btnRegister_Click(object sender, EventArgs e)
        {
            InsertForm form = new InsertForm();
            form.ShowDialog();
        }

        private void btnOrderList_Click(object sender, EventArgs e)
        {
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if(lbxCustomers.SelectedIndex < 0)
            {
                MessageBox.Show("선택된 항목이 없습니다.");
                return;
            }
            Customer Customer = lbxCustomers.SelectedItem as Customer;

            //선택된 객체를 DB에서 삭제
            NWData data = new NWData();
            bool result = data.DeleteCustomer(Customer);

            if (result)
            {
                MessageBox.Show("삭제했습니다.");
                LoadCustomers();
            }
            else
            {
                MessageBox.Show("삭제실패");
            }

        }
    }
}

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

 

namespace Ex03
{
    public partial class InsertForm : Form
    {
        public InsertForm()
        {
            InitializeComponent();
        }

        private void btnRegister_Click(object sender, EventArgs e)
        {
            //Insert
            //Customer customer = new Customer();
            //customer.CustomerID = txtCustomerID.Text;
            //customer.CompanyName = txtCompanyName.Text;
            //customer.ContactTitle = txtContactTitle.Text;
            //customer.ContactName = txtContactName.Text;
            //customer.Country = txtCountry.Text;
            //customer.Phone = txtPhone.Text;
            //customer.Fax = txtFax.Text;

        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
        }

    }
}


------------------------------------------------------------------
App.config 추가

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

 

namespace Ex03
{
    public partial class EditForm : Form
    {
        public EditForm()
        {
            InitializeComponent();
        }

        //public EditForm(Customer customer)
        //{
        //    InitializeComponent();

        //    txtCustomerID.Text = customer.CustomerID;
        //    txtCompanyName.Text = customer.CompanyName;
        //    txtContactTitle.Text = customer.ContactTitle;
        //    txtContactName.Text = customer.ContactName;
        //    txtCountry.Text = customer.Country;
        //    txtPhone.Text = customer.Phone;
        //    txtFax.Text = customer.Fax;
        //}

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            //Customer customer = new Customer();
            //customer.CustomerID = txtCustomerID.Text;
            //customer.CompanyName = txtCompanyName.Text;
            //customer.ContactTitle = txtContactTitle.Text;
            //customer.ContactName = txtContactName.Text;
            //customer.Country = txtCountry.Text;
            //customer.Phone = txtPhone.Text;
            //customer.Fax = txtFax.Text;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {

        }
    }
}
---------------------------------------------------------------------

<?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>

=================     DataLib     ==================
DataLib 추가

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using DataLib.Entity; //추가

namespace DataLib.Persistence
{
    public class NWData  // 다른 Assembly에서 사용 가능한 클래스
    {
        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;

 

            //2. 명령 객체 생성 및 [SQL, 연결객체] 설정
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT customerid, companyname, contactname, " +
            "contacttitle, country, phone,fax  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();
                customer.ContactTitle =
                    reader["ContactTitle"].ToString();
                customer.Country =
                    reader["country"].ToString();
                customer.Phone =
                    reader["phone"].ToString();
                customer.Fax =
                    reader["fax"].ToString();
                customers.Add(customer);
            }

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

            return customers;

           

        }
        public bool DeleteCustomer(Customer customer)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "DELETE FROM customers WHERE customerid = "
                +"'"+ customer.CustomerID + "'";
            bool result = false;
            try
            {
                conn.Open();

                cmd.ExecuteNonQuery(); //for  insert, update, delete query 을 위한 명령문

                result = true;
            }
            catch (Exception) { }
            finally
            {
                conn.Close();
            }

            return result;

        }
    }
}
/////////////////////////////////////////////////////////////////////////////////
//
// 보기 -> 서버 탐색기 -> 서버에서 테이블 선택 마우스 오른쪽 -> 테이블 데이터 표시
// SQL SERVER 실행을 안하고 DB의 내용을 볼수 있다 수정도 가능
//
/////////////////////////////////////////////////////////////////////////////////

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

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

namespace DataLib.Entity
{
    public class Customer // public 추가
    {
        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; }

    }
}