.Net Project & 재태크 재무 관리

namespace WinCommandBuilder
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private SqlDataAdapter da;
        private DataTable dt;

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection
             
        ("server=.;database=AddressBook;uid=AddressBook;pwd=1234;");

            SqlCommand cmd = new SqlCommand
                              ("Select *From AddressBook", con);

            da = new SqlDataAdapter(cmd);
            dt = new DataTable("Addressbook");

    //Select를 기준으로 Insert,Update,Delete문 자동 생성 :PK가 있어야 한다
            SqlCommandBuilder scb = new SqlCommandBuilder(da);

            this.dataGridView1.DataSource = dt;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            da.Update(dt); //CRUD 실행
        }
    }
}

namespace WinDataAdapter
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Display(); //DataReader이용
            Display1(); //DataSet - Fill 이용
        }
        //Connection - command - DataReader - DataSet
        // DataTable 윈폼만... 패턴 출력
        private void Display()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = 
           "server=.;database=AddressBook;uid=AddressBook;pwd=1234;";
            con.Open();
                       
            SqlCommand cmd = new SqlCommand
                              ("Select *From AddressBook", con);
            cmd.CommandType = CommandType.Text;

        SqlDataReader dr = cmd.ExecuteReader();//팩터리 메서드로 리더생성
            
        DataTable dt = new DataTable();
        dt.Load(dr);

            this.dataGridView1.DataSource = dt; //출력

            dr.Close();
            con.Close();
        }

        //Connection - Command - DataSet - Fill 패턴 출력
        private SqlDataAdapter da2;//필드레벨로 재선언
        private DataTable dt2;

        private void Display1()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString =            
            server=.;database=AddressBook;uid=AddressBook;pwd=1234;";
            con.Open();

            SqlCommand cmd = new SqlCommand
                       
            da2 = new SqlDataAdapter(cmd);
            da2.SelectCommand = cmd;
            
      //삽입
      cmd = new SqlCommand
      ("Insert Into AddressBook Values
                      (@Name, @Mobile, @Email)", con);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 25, "Name");
        cmd.Parameters.Add
                              ("@Mobile",SqlDbType.VarChar, 25, "Mobile");
        cmd.Parameters.Add("@Email", SqlDbType.VarChar, 25, "Email");
       
        da2.InsertCommand = cmd;

            //수정
           cmd = new SqlCommand
            ("Update AddressBook Set
               Name = @Name, Mobile=@Mobile,
               Email=@Email Where Num = @Num", con);
           cmd.Parameters.Add
               ("@Name", SqlDbType.VarChar, 25, "Name");
           cmd.Parameters.Add
               ("@Mobile", SqlDbType.VarChar, 25,"Mobile");
           cmd.Parameters.Add
                  ("@Email", SqlDbType.VarChar, 25,"Email");
            cmd.Parameters.Add("@Num", SqlDbType.Int, 32, "Num");

            da2.UpdateCommand = cmd;
           
            //삭제
            cmd = new SqlCommand
                     ("Delete AddressBook Where Num = @Num",con);
            cmd.Parameters.Add("@Num",SqlDbType.Int,32,"Num");
            da2.DeleteCommand = cmd;
       
            dt2 = new DataTable("Addressbook");
            da2.Fill(dt2);
                                           
            this.dataGridView2.DataSource = dt2;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            da2.Update(dt2); //변경 내역 적용하기
        }
    }
}

namespace ConnectionEvent
{
    public partial class Form1 : Form
    {
        //전역변수로 선언
        private SqlConnection con;
        public Form1()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                con.Open();
            }
            catch (Exception ex)
            {
               MessageBox.Show(ex.Message);
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
     }

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection();
            con.ConnectionString =
                   "server=.;database=Market;uid=Market;pwd=1234;";
         
            con.StateChange += new
                  StateChangeEventHandler(con_StateChange);
            con.InfoMessage += new
                  SqlInfoMessageEventHandler(con_InfoMessage);
        }

     void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            listBox1.Items.Add(e.Message);
        }

        void con_StateChange(object sender, StateChangeEventArgs e)
        {
            string Mes;
            Mes = string.Format("원래 상태 : {0}, 현재 상태 {1}",
                e.OriginalState, e.CurrentState);
            listBox1.Items.Add(Mes);
        }

        private void Form1_FormClosed
                                         (object sender, FormClosedEventArgs e)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}

namespace WinDataRelation
{
    public partial class Form1 : Form
    {
        private DataSet ds; //전역변수로 사용

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString =
                      "server=.;database=Market;uid=Market;pwd=1234;";
            con.Open();         

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText =
                     "Select CategoryID, CategoryName From Categories ";
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            ds = new DataSet();

            da.Fill(ds, "Categories");

            cmd.CommandText =
                  "Select ProductID, CategoryID, ModelName
                   From Products";
            da.SelectCommand = cmd;
            da.Fill(ds, "Products");

            //1번과 같은 방식을 사용할 경우, DB에서는 삭제 되지 않음
            //[1] Categories와 Products간에 관계설정(메모리상)
            //ds.Relations.Add(new DataRelation(
            //    "CP",
            //    ds.Tables[0].Columns["CategoryID"],
            //    ds.Tables[1].Columns["CategoryID"]));

            //[2]제약조건 걸기
            //부모 레코드 삭제시 자식이 참조하고 있으면 에러 

            //[3]삭제 규칙 적용
            ((ForeignKeyConstraint)
           ds.Tables[1].Constraints["PK_Pro"])
                .DeleteRule = Rule.None;
           
            this.dataGridView1.DataSource = ds.Tables[0];
            this.dataGridView2.DataSource = ds.Tables[1];
                       
            con.Close();

        }

            this.dataGridView3.DataSource = read; //재바인딩
        }
    }
}