.Net Project & 재태크 재무 관리

--출력
Select *From Items Order by Description Asc

--상세
Select *From Items Where Description='안녕하세요'

--수정
Begin Tran
 Update Items
   Set Description = '반갑습니다' Where Description = '안녕하세요'
 
--RollBack Tran
Commit Tran

--삭제
Begin Tran
 Delete Items
 Where 1 = 1
 --RollBack Tran
Commit Tran

--검색  
Select *From Items
   Where Description Like '%하%'
Go

--[2] 6가지 저장 프로시저
--입력 저장 프로시저
Create Proc dbo.AddItem
    @Description VarChar(8000),
    @Priority TinyInt
As
   Insert Into Items(Description, Priority) Values(@Description, @Priority)
Go

--출력 저장 프로시저
Create Proc dbo.GetItems
As
   Select *From Items Order by ID Asc
Go

--상세 저장 프로시저
Create Proc dbo.GetItem
   @ID Int
As
   Select *From Items Where ID = @ID
Go

--수정 저장 프로시저
Create  Proc dbo.UpdateItem
   @Description VarChar(8000),
   @Opened DateTime,
   @Closed DateTime,
   @Priority TinyInt,
   @ID Int
As
 Update Items Set Description = @Description, Opened = @Opened,
        Closed=@Closed, Priority=@Priority Where ID = @ID
Go

--삭제  저장 프로시저
Create Proc dbo.DeleteItem
   @ID Int
As
 Delete Items Where ID = @ID
Go

--검색 저장 프로시저 
Create Proc dbo.GetItemsByDescription
   @SearchQuery VarChar(25)
 
As
 Declare @str VarChar(500)
 Set @str = '
 Select *From Items Where Description Like ''%%' + @SearchQuery + '%''
 '
Exec(@str) 
Go

--예시 데이터 입력
Exec AddItem '오늘은 ToDoList를 만들자',1
Exec AddItem '오늘은 ToDoList를 분석하자',2

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

        private DataSet ds; //데이터셋
        private SqlDataAdapter da; //데이터 어댑터
        private DataTable dt;

        private void btnUpdate_Click(object sender, EventArgs e)
        {

        }

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

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "Select *From Items";
            cmd.CommandType = CommandType.Text;

            da = new SqlDataAdapter(cmd);

            ds = new DataSet();

            da.Fill(ds, "Items");

            dt = ds.Tables[0];

            //2가지 방법을 사용하여 출력하는 방법으로 표현 
            //this.ctlToDoList.DataSource = ds.Tables[0].DefaultView;
              this.ctlToDoList.DataSource = dt;
        }
    }
}

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

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

        private DataSet ds; //데이터셋
        private SqlDataAdapter da; //데이터 어댑터
        private DataTable dt;

        private void btnUpdate_Click(object sender, EventArgs e)
        {

        }

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

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "Select *From Items";
            cmd.CommandType = CommandType.Text;

            da = new SqlDataAdapter
            ("Select *From Items",con); //SelectCommand

            //InsertCommand
            cmd = new SqlCommand();//아..객체생성..
            cmd.Connection = con;
            cmd.CommandText = "AddItem";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add
                  ("@Description", SqlDbType.VarChar, 8000, "Description");
            cmd.Parameters.Add
                  ("@Priority", SqlDbType.TinyInt, 8, "Priority");
           
            //UpdateCommand
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "UpdateItem";
            cmd.CommandType = CommandType.StoredProcedure;
           
            cmd.Parameters.Add
            ("@Description", SqlDbType.VarChar, 8000, "Description");
           
            cmd.Parameters.Add
            ("@Priority", SqlDbType.TinyInt, 8, "Priority");
           
             cmd.Parameters.Add
             ("@Opened", SqlDbType.DateTime, 32, "Opened");
           
            cmd.Parameters.Add
            ("@Closed", SqlDbType.DateTime, 32, "Closed");
           
            cmd.Parameters.Add("@ID", SqlDbType.Int, 32, "ID");
            da.UpdateCommand = cmd;

            //DeleteCommand
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "DeleteItem";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.DateTime, 10, "ID");
            da.DeleteCommand = cmd;

            ds = new DataSet();

            da.Fill(ds, "Items");

            dt = ds.Tables[0];

            //this.ctlToDoList.DataSource = ds.Tables[0].DefaultView;
            this.ctlToDoList.DataSource = dt;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            da.Update(dt); //업데이트
        }
    }
}

Program.Cs
---------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Linq;
using System.Data.Linq.Mapping;

namespace WinLinqToSQL
{
    /// <summary>
    /// Addressbook 테이블과 일대일 매칭되는 클래스 생성 
    /// </summary>
    //Table 특성을 사용해서 테이블로 보자
    [Table(Name="AddressBook")] 
    class AddressBook
    {
        private int _Num;
        [Column(IsPrimaryKey=true, Storage="_Num")]
        public int Num   //Num속성은 Num컬럼과 매칭, 기본키 설정
        {
            get { return _Num;}
            set {_Num = value;}
       }
----------------------------------------------------------------------
        private string _Name;
        [Column(IsPrimaryKey = true, Storage = "_Name")]
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
----------------------------------------------------------------------        private string _Mobile;
        [Column(IsPrimaryKey = true, Storage = "_Mobile")]
        public string Mobile
        {
            get { return _Mobile; }
            set { _Mobile = value; }
        }
----------------------------------------------------------------------------        private string _Email;
        [Column(IsPrimaryKey = true, Storage = "_Email")]
        public string Email
        {
            get { return _Email; }
            set { _Email = value; }
        }

    }
}

 WinLinqToSQL.Form
----------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Linq;

namespace WinLinqToSQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            //초기화자
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Display(); //함수호출
        }

        private void Display()
        {
            // 출력
            // Linq to SQL방법으로 Table과 동일한 구조의
            // Entity 클래스가 만들어졌다면..
            // [1]
            DataContext context = new DataContext

     //[2] Context의 GetTable<T> 팩터리 메서드로 테이블 매핑해서 가져오기 
          Table<AddressBook> addr = context.GetTable<AddressBook>();

            //[3] LINQ Query 날리기
            var q = from a in addr
                    select a;
           
            //[4]출력
            foreach (var item in q)
            {
                listBox1.Items.Add(item.Name + "," + item.Mobile);
            }
        }
    }
}

namespace WinLinqDataSet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }        
        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection
          
         ("server=.;database=AddressBook;uid=AddressBook;pwd=1234;");
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select *From AddressBook";

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

            DataSet ds = new DataSet();
            da.Fill(ds, "AddressBook");

       //LINQ를 사용해서 Listbox에 아이템 추가해보자

            foreach (var item in addr)
            {
                listBox1.Items.Add(item.ItemArray[1] + ","
                + item.ItemArray[2] + "," + item.ItemArray[3]);
            }
              con.Close();
        }
    }
}

            //DataSet반복
            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                listBox2.Items.Add(dt.Rows[i]["Name"]
                            + "," + dt.Rows[i]["Mobile"]);
            }

            //DataReader로 출력
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string s = dr["Name"].ToString() + "," + dr.GetString(2);
                listBox3.Items.Add(s);
            }
            con.Close();

namespace Winbinding
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            //초기화자
            InitializeComponent();
        }

        private DataSet ds; //주수록 담을 그릇 (전역변수) 필드 선언
        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);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            ds = new DataSet();
            da.Fill(ds, "AddressBook"); //채우기

//[!]바인딩
//text1의 Text속성을  ds 에 담겨 있는 AddressBook 테이블의 Name필드 연결 
         textBox1.DataBindings.Add("Text", ds, "AddressBook.Name");
         textBox2.DataBindings.Add("Text", ds, "AddressBook.Mobile");
         textBox3.DataBindings.Add("Text", ds, "AddressBook.Email");

        }
        //이전
        private void button1_Click(object sender, EventArgs e)
        {
           //감소
            BindingContext[ds, "AddressBook"].Position--; //레코드 이동

        }
        //다음
        private void button2_Click(object sender, EventArgs e)
        {
            //증가 
            BindingContext[ds, "AddressBook"].Position++; //다음 레코드
        }
    }
}