.Net Project & 재태크 재무 관리

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; //재바인딩
        }
    }
}

namespace MakeDataSet
{
    public partial class Form1 : Form
    {
        //필드 선언(전역변수)
        private DataSet DbADOTest;
        private DataTable tblPeople, tblSale;
        private DataRelation RelBuy;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DbADOTest = new DataSet("DbADOTest");

            tblPeople = MakePeopleTable();
            tblSale = MakeSaleTable();
            DbADOTest.Tables.Add(tblPeople);
            DbADOTest.Tables.Add(tblSale);

            //관계 객체 생성
            RelBuy = new DataRelation("Buy",
                DbADOTest.Tables["tblPeople"].Columns["Name"],
                DbADOTest.Tables["tblSale"].Columns["Customer"]);
            DbADOTest.Relations.Add(RelBuy);

            dataGridView1.DataSource = DbADOTest.Tables["tblPeople"];
            dataGridView2.DataSource = DbADOTest.Tables["tblSale"];
                 
        }

        //MakeSaleTable()
        private DataTable MakeSaleTable()
        {
            DataTable tblSale = new DataTable("tblSale");

            DataColumn col;
            DataRow row;

            // 열 등록
            col = new DataColumn("OrderNo", typeof(Int32));
            col.AllowDBNull = false;
            col.Unique = true;
            col.AutoIncrement = true;
            col.ReadOnly = true;
            tblSale.Columns.Add(col);

            tblSale.PrimaryKey = new DataColumn[] { col };

            col = new DataColumn("Customer", typeof(String));
            col.MaxLength = 10;
            col.AllowDBNull = false;
            tblSale.Columns.Add(col);

            col = new DataColumn("Item", typeof(String));
            col.MaxLength = 20;
            col.AllowDBNull = false;
            tblSale.Columns.Add(col);

            col = new DataColumn("ODate", typeof(DateTime));
            col.AllowDBNull = false;
            tblSale.Columns.Add(col);

            // 행 삽입
            row = tblSale.NewRow();
            row["Customer"] = "정우성";
            row["Item"] = "면도기";
            row["ODate"] = new DateTime(2008, 1, 1);
            tblSale.Rows.Add(row);

            row = tblSale.NewRow();
            row["Customer"] = "고소영";
            row["Item"] = "화장품";
            row["ODate"] = new DateTime(2008, 1, 2);
            tblSale.Rows.Add(row);

            row = tblSale.NewRow();
            row["Customer"] = "김태희";
            row["Item"] = "핸드폰";
            row["ODate"] = new DateTime(2008, 1, 3);
            tblSale.Rows.Add(row);

            row = tblSale.NewRow();
            row["Customer"] = "김태희";
            row["Item"] = "휘발유";
            row["ODate"] = new DateTime(2008, 1, 4);
            tblSale.Rows.Add(row);

            tblSale.AcceptChanges();
            return tblSale;
        }

        //MakePeopleTable
        private DataTable MakePeopleTable()
        {
            DataTable tblPeople = new DataTable("tblPeople");

            DataColumn col;
            DataRow row;

            // 열 등록
            col = new DataColumn("Name", typeof(String));
            col.MaxLength = 10;
            col.AllowDBNull = false;
            col.Unique = true;
            tblPeople.Columns.Add(col);

            tblPeople.PrimaryKey = new DataColumn[] { col };

            col = new DataColumn("Age", typeof(Int32));
            col.AllowDBNull = false;
            tblPeople.Columns.Add(col);

            col = new DataColumn("Male", typeof(bool));
            col.AllowDBNull = false;
            tblPeople.Columns.Add(col);

            // 행 삽입
            row = tblPeople.NewRow();
            row["Name"] = "정우성";
            row["Age"] = 36;
            row["Male"] = true;
            tblPeople.Rows.Add(row);

            row = tblPeople.NewRow();
            row["Name"] = "고소영";
            row["Age"] = 32;
            row["Male"] = false;
            tblPeople.Rows.Add(row);

            row = tblPeople.NewRow();
            row["Name"] = "배용준";
            row["Age"] = 37;
            row["Male"] = true;
            tblPeople.Rows.Add(row);

            row = tblPeople.NewRow();
            row["Name"] = "김태희";
            row["Age"] = 29;
            row["Male"] = false;
            tblPeople.Rows.Add(row);

            tblPeople.AcceptChanges();
            return tblPeople;
        
        }
        //구입한 상품 보기
        private void btnGetChild_Click(object sender, EventArgs e)
        {
            DataRow[] Items;
            DataRow[] Customer;

            string Name = (string)dataGridView1.CurrentRow.Cells[0].Value;

            Customer = tblPeople.Select("Name = '" + Name + "'");
            Items =
               Customer[0].GetChildRows(DbADOTest.Relations["Buy"]);

            listBox1.Items.Clear();
            foreach (DataRow R in Items)
            {
                listBox1.Items.Add(R["Item"]);
            }
        }

        //구입한 고객 조사
        private void btnGetParent_Click(object sender, EventArgs e)
        {
            DataRow[] Items;
            DataRow Customer;

            string Name = (String)dataGridView2.CurrentRow.Cells[1].Value;

            Items = tblSale.Select("Customer = '" + Name + "'");
            Customer =
                 Items[0].GetParentRow(DbADOTest.Relations["Buy"]);

            textBox1.Text = String.Format("이름 : {0}, 나이 : {1}, 성별 : {2} ",
                Customer["Name"], Customer["Age"], Customer["Male"]);
        }
    }
}

                                               ↓

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

         /// <summary>
        /// 오름차순,내림차순 정렬 후 리스트 박스 출력
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        ///
        private void DisplayResult(DataRow[] Result)
        {
            lstDisplay.Items.Clear();
            foreach (DataRow R in Result)
            {
                lstDisplay.Items.Add(R["Name"]);              
            }
        }

        //살 이상인 사람들 조사 
        private void btnName_Click(object sender, EventArgs e)
        {
            DataRow[] Result = dt.Select("Age >=" + txtName.Text);
            DisplayResult(Result);
        }

        /// <summary>
        /// 삭제, Accept, 새로변경, Reject 클릭이벤드
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
       
        ///삭제
        private void btnDelete_Click(object sender, EventArgs e)
        {
            DataRow[] arr = dt.Select("Name ='" + txtDelete.Text + "'");
            if (arr.Length != 0)
            {
                arr[0].Delete(); //삭제
            }
            this.dataGridView1.DataSource = dt;

        }

        //Accept
        private void btnAccept_Click(object sender, EventArgs e)
        {
            dt.AcceptChanges(); //변경완료

        }

        //새로변경
        private void btnNewChange_Click(object sender, EventArgs e)
        {
            DataRow[] arr = dt.Select("Name = '" + txtChangeAge.Text + "'");
            if (arr.Length != 0)
            {
                arr[0]["Age"] = Convert.ToInt32(txtNewChange.Text);
            }
        }
        //Reject
        private void btnReject_Click(object sender, EventArgs e)
        {
            dt.RejectChanges(); //변경취소
        }    
         
    }
}

 using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Category_FrmDataTableSelect : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Display();
        }
    }

    private void Display()
    {
        SqlConnection con = new SqlConnection
                     (ConfigurationManager.ConnectionStrings
                      ["ConnectionString"].ConnectionString);

        SqlCommand cmd = new SqlCommand
           ("Select CategoryID, CategoryName From Categories",con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
       
        DataSet ds = new DataSet();
        da.Fill(ds, "Categories");
        DataTable dt = ds.Tables[0];

        //가전이면서, categoryID에 내림 차순으로 정렬
        DataRow[] arr = dt.Select
                ("CategoryName Like '%가전%'","CategoryID Desc");
       
        //전체출력
        //dt.Select("", "CategoryID Desc"); //RowFilter속성과 Sort속성의 기능 
        
        //반복하면서 출력
        for (int i = 0; i < arr.Length; i++)
        {
            this.ListBox1.Items.Add(
                    new ListItem(
                            arr[i]["CategoryName"].ToString(), arr[i]
                            ["CategoryID"].ToString()));
        }        
    }
}

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

        private void tblPeople_Click(object sender, EventArgs e)
        {
            DataTable tblPeople = MakePeopleTable();
            dataGridView1.DataSource = tblPeople;
        }
        //DataTable클래스의 생성자를 호출하여 빈 테이블 객체를 생성
        //인수로 테이블 이름만 전달
        private DataTable MakePeopleTable()
        {
            DataTable tblPeople = new DataTable("tblPeople");

            DataColumn col;
            DataRow row;

            //열등록
            col = new DataColumn("Name", typeof(String));
            col.MaxLength = 10;
            col.AllowDBNull = false; //중복값
            col.Unique = true;
            tblPeople.Columns.Add(col);

            tblPeople.PrimaryKey = new DataColumn[] { col };

            col = new DataColumn("Age", typeof(Int32));
            col.AllowDBNull = false;
            tblPeople.Columns.Add(col);

            col = new DataColumn("Male", typeof(bool));
            col.AllowDBNull = false;
            tblPeople.Columns.Add(col);

            //행삽입
            row = tblPeople.NewRow();
            row["Name"] = "정우성";
            row["Age"] = 36;
            row["Male"] = true;
            tblPeople.Rows.Add(row);

            row = tblPeople.NewRow();
            row["Name"] = "고소영";
            row["Age"] = 32;
            row["Male"] = false;
            tblPeople.Rows.Add(row);

            row = tblPeople.NewRow();
            row["Name"] = "배용준";
            row["Age"] = 37;
            row["Male"] = true;
            tblPeople.Rows.Add(row);

            row = tblPeople.NewRow();
            row["Name"] = "김태희";
            row["Age"] = 36;
            row["Male"] = false;
            tblPeople.Rows.Add(row);

            tblPeople.AcceptChanges();
            return tblPeople;
                       
        }

---

        private void tblSale_Click(object sender, EventArgs e)
        {
            DataTable tblSale = MakeSaleTable();
            dataGridView1.DataSource = tblSale;
        }

        private DataTable MakeSaleTable()
        {
            DataTable tblSale = new DataTable("tblSale");

            DataColumn col;
            DataRow row;

            //열등록
            col = new DataColumn("OrderNo", typeof(Int32));
            col.AllowDBNull = false;
            col.Unique = true;
            col.AutoIncrement = true;
            col.ReadOnly = true;
            tblSale.Columns.Add(col);

            tblSale.PrimaryKey = new DataColumn[] { col };

            col = new DataColumn("Customer", typeof(String));
            col.MaxLength = 10;
            col.AllowDBNull = false;
            tblSale.Columns.Add(col);

            col = new DataColumn("Item", typeof(String));
            col.MaxLength = 20;
            col.AllowDBNull = false;
            tblSale.Columns.Add(col);

            col = new DataColumn("ODate", typeof(DateTime));
            col.AllowDBNull = false;
            tblSale.Columns.Add(col);

            //행삽입
            row = tblSale.NewRow();
            row["Customer"] = "정우성";
            row["Item"] = "면도기";
            row["ODate"] = new DateTime(2008, 1, 1);
            tblSale.Rows.Add(row);

            row = tblSale.NewRow();
            row["Customer"] = "고소영";
            row["Item"] = "화장품";
            row["ODate"] = new DateTime(2008, 1, 2);
            tblSale.Rows.Add(row);

            row = tblSale.NewRow();
            row["Customer"] = "김태희";
            row["Item"] = "핸드폰";
            row["ODate"] = new DateTime(2008, 1, 3);
            tblSale.Rows.Add(row);

            row = tblSale.NewRow();
            row["Customer"] = "김태희";
            row["Item"] = "휘발류";
            row["ODate"] = new DateTime(2008, 1, 4);
            tblSale.Rows.Add(row);

            tblSale.AcceptChanges();
            return tblSale;        

        }

---

        //연습       
        private void button1_Click(object sender, EventArgs e)
        {
            //1행 2열짜리 테이블 만들기 : 홍길동, 백두산
            DataTable dt = new DataTable();

            //에러 발생하므로 Factory메서드 사용
            //DataRow dr = dt.NewRow();
            DataRow dr = dt.NewRow();

            //열 하나 만들기
            DataColumn dc = new DataColumn
                     ("Name",typeof(String)); //VarChar선언
            dc.MaxLength = 25;
            dc.AllowDBNull = false;

            //테이블에 행을 추가
            dt.Columns.Add(dc);
           
            //행 하나 만들기
            dr["Name"] = "홍길동";
            dt.Rows.Add(dr);

            this.dataGridView1.DataSource = dt; //데이터 테이블 바인딩
        }