24장 ToDoList 프로그램 만들기

- 오늘 / 앞으로 해야할 일 목록을 나열
- 기록일 / 완료일
- 우선순위 결정 

- ToDoList  SQL 쿼리문 작성

Create Table Items (  ID Int Identity(1,1) Primary Key, --일련번호  Description VarChar(8000) Not Null, --설명  Opened DateTime Default(GetDate()), --등록일  Closed DateTime Null, --완료일  Priority TinyInt Default(1) --우선순위(1:높음, 2:보통, 3:낮음) ) Go --[1] 6가지 예시문 : 입력, 출력, 상세, 수정, 삭제, 검색 --ToDo --입력 Insert Into Items Values('안녕하세요',GETDATE(),GETDATE(),'1') Insert Into Items Values('시간 어때요?',GETDATE(),GETDATE(),'2') Insert Into Items Values('감사 합니다',GETDATE(),GETDATE(),'3') --출력 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

- ToList WinForm에서 DB불러오기

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(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); //업데이트         }     } }

---

ToList10월1일.zip

---