반응형
진정한 답은 https://github.com/hugoMGSung 를 참고하자.
DB 쿼리문(한 덩어리씩 따로실행)
CREATE TABLE userTbl (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
userID varchar(12) NOT NULL,
password varchar(max) NOT NULL,
lastLoginDt datetime NULL,
loginIpAddr varchar(30) NULL
)
-- 구분 테이블
CREATE TABLE divtbl (
Division CHAR(4) NOT NULL PRIMARY KEY,
Names NVARCHAR(45)
)
-- 책 테이블
CREATE TABLE bookstbl (
Idx INT NOT NULL IDENTITY PRIMARY KEY,
Author VARCHAR(45),
Division CHAR(4) NOT NULL
FOREIGN KEY REFERENCES divtbl(Division),
Names VARCHAR(100),
ReleaseDate DATE,
ISBN VARCHAR(200),
Price DECIMAL(10,0))
-- 회원테이블
CREATE TABLE membertbl (
Idx INT NOT NULL IDENTITY PRIMARY KEY,
Names VARCHAR(45) NOT NULL,
Levels CHAR(1),
Addr VARCHAR(100),
Mobile VARCHAR(13),
Email VARCHAR(50))
-- 대여테이블
CREATE TABLE rentaltbl (
Idx INT NOT NULL IDENTITY PRIMARY KEY,
memberIdx INT
FOREIGN KEY REFERENCES membertbl(Idx),
bookIdx INT
FOREIGN KEY REFERENCES bookstbl(Idx),
rentalDate DATE,
returnDate DATE)
-- 회원테이블
INSERT INTO membertbl VALUES
('이동욱', 'A', '부산시 사하구', '010-2967-1016', 'ldw@naver.com'),
('방용혁', 'B', '부산시 남구', '010-9291-4419', 'byh@daum.com'),
('황동주', 'B', '부산시 북구', '010-8956-7423', 'hdj@gmail.com'),
('김효용', 'D', '부산시 영도구', '010-8736-2919', 'khy@hotmail.com'),
('박현수', 'A', '부산시 강서구', '010-9295-6600', 'phs@yahoo.co.kr'),
('서동우', 'C', '부산시 동래구', '010-5341-0128', 'sdw@naver.com'),
('강다은', 'A', '부산시 중구', '010-2244-0675', 'kde@empal.com'),
('김문성', 'D', '부산시 수영구', '010-6318-2590', 'kms@hotmail.com'),
('김영환', 'A', '부산시 강서구', '010-5615-1344', 'kyh@nate.com'),
('최원영', 'C', '김해시 삼안동', '010-9291-0882', 'cwy@dreamwiz.com'),
('전대한', 'D', '부산시 남구', '010-8956-6008', 'jdh@korea.com'),
('이우영', 'A', '부산시 금정구', '010-2923-2919', 'lwy@hotmail.com'),
('이준호', 'B', '부산시 부산진구', '010-9295-5718', 'ljh@gmail.com'),
('이창수', 'D', '부산시 동구', '010-9341-0128', 'lcs@naver.com'),
('이하응', 'A', '부산시 사상구', '010-5436-0675', 'lhe@hotmail.com'),
('장국빈', 'C', '부산시 남구', '010-6318-4654', 'jgb@freechal.com'),
('김종훈', 'A', '부산시 남구', '010-5615-7437', 'kjh@nate.com'),
('김한진', 'B', '부산시 수영구', '010-6566-4419', 'khj@daum.com'),
('박지윤', 'C', '부산시 사상구', '010-8956-1508', 'pjy@gmail.com'),
('김효정', 'B', '부산시 연제구', '010-5667-2919', 'kimhj@hotmail.com'),
('박효민', 'A', '부산시 해운대구', '010-9295-0341', 'phm@yahoo.com'),
('정재민', 'A', '부산시 사하구', '010-5341-4736', 'jjm@naver.com'),
('정성권', 'A', '부산시 금정구', '010-2244-5121', 'jsg@empal.com'),
('유혜진', 'B', '부산시 수영구', '010-6318-3734', 'yhj@hotmail.com');
INSERT INTO membertbl VALUES
('성명건', 'D', '부산시 해운대구', '010-7625-0677', 'smg@naver.com');
-- 책 구분
INSERT INTO divtbl VALUES ('B001', '공포/스릴러'), ('B002', '로맨스'), ('B003', '무협'), ('B004', '전쟁/역사'),
('B005', '추리'), ('B006', 'SF/판타지');
-- 책정보
INSERT INTO bookstbl VALUES
('넬레 노이하우스', 'B001', '잔혹한 어머니의 날 1', '2019-10-07', '9791158791179', 11520),
('넬레 노이하우스', 'B001', '잔혹한 어머니의 날 2', '2019-10-07', '9791158791186', 11520),
('매뉴 라인하트', 'B006', '월드 오브 워크래프트 팝업북', '2019-10-21', '9788959527779', 52200),
('묵향동후', 'B003', '마도조사 2', '2019-09-03', '9791127852122', 12600),
('오코제키 다이', 'B005', '루팡의 딸', '2019-09-25', '9788998274412', 13500),
('조엘 디케르', 'B001', '스테파니 메일러 실종사건', '2019-08-12', '9788984373761', 16200),
('이지환', 'B002', '닥터 퀸 1-2세트', '2019-09-20', '9791164664122', 27000),
('김수지', 'B002', '희란국 연가', '2019-11-01', '9791131594100', 14000),
('알파타르트', 'B002', '재혼 황후 1', '2019-10-18', '9791164790289', 14000),
('안나 토드', 'B002', '애프터 7', '2019-08-30', '9791188253166', 14000),
('안타 토드', 'B002', '애프터 8', '2019-08-30', '9791188253173', 14000),
('남혜인', 'B002', '아도니스 11', '2019-08-26', '9791163022237', 11800),
('안드레아스 빙겔만', 'B001', '쉐어하우스', '2019-09-27', '9791186809792', 13320),
('비프케 로렌츠', 'B001', '너도 곧 쉬게 될 거야', '2019-09-18', '9791162834930', 12600),
('전건우', 'B001', '어위크', '2019-09-02', '9791188660353', 12600),
('토머스 해리스', 'B005', '카리 모라', '2019-09-11', '9791158511470', 15000),
('토머스 해리스', 'B005', '한니발', '2019-09-11', '9791158511500', 15000),
('정준', 'B003', '화산전생 17', '2019-08-23', '9791128394683', 8000),
('묵향동후', 'B003', '마도조사 1', '2019-07-30', '9791127851446', 14000),
('용대운', 'B003', '군림천사 35', '2019-07-26', '9788926706763', 9000),
('정준', 'B003', '화산전생 15', '2019-04-30', '9791128394669', 8000),
('김석진', 'B003', '삼류무사 2부16', '2019-04-02', '9791135413698', 8000),
('히가시노 게이고', 'B006', '기도의 막이 내릴 때', '2019-08-06', '9788990982780', 16800),
('히가시노 게이고', 'B006', '악의', '2019-07-25', '9788972750031', 14000),
('서철원', 'B004', '최후의 만찬', '2019-09-25', '9791130625843', 15000),
('마이 셰발, 페르 발뢰', 'B004', '어느 끔찍한 남자', '2019-09-20', '9788954657648', 12800),
('마이 셰발, 페르 발뢰', 'B004', '폴리스, 폴리스, 포타티스모스!', '2019-09-20', '9788954656535', 13800),
('김진명', 'B004', '살수 1', '2019-09-16', '9788925567716', 14800),
('김진명', 'B004', '살수 2', '2019-09-16', '9788925567723', 14800),
('손정미', 'B004', '도공 서란', '2019-09-16', '9788965708575', 14000),
('요안나', 'B002', '순수하지 않은 감각', '2019-10-02', '9791135445705', 12500),
('노승아', 'B002', '오늘부터 천생연분 1', '2019-09-18', '9791130039480', 12800),
('노승아', 'B002', '오늘부터 선생연분 2', '2019-09-18', '9791130039497', 12800),
('김이랑', 'B002', '조선혼담공작소 꽃파당', '2019-09-06', '9791159099724', 138000),
('전민석', 'B004', '감치', '2019-08-15', '9788947545075', 15000),
('나관중', 'B004', '삼국지 세크', '2019-07-25', '9788936479497', 60000),
('에리크 뷔야르', 'B004', '그날의 비밀', '2019-07-20', '9788932919751', 12800),
('요 네스뵈', 'B004', '폴리스', '2019-07-08', '9788934996699', 16000),
('T. M. 로건', 'B005', '29초', '2019-09-18', '9788950983208', 15000),
('토머스 해리스', 'B005', '양들의 침묵', '2019-09-11', '9791158511494', 15000),
('송시우', 'B005', '대나무가 우는 섬', '2019-09-06', '9788952739087', 14000),
('A. J. 핀', 'B005', '우먼 인 윈도', '2019-09-03', '9788934998952', 15800),
('이정명', 'B005', '밤의 양들 1', '2019-08-30', '9791189982461', 11500),
('이정명', 'B005', '밤의 양들 2', '2019-08-30', '9791189982478', 11500),
('정해연', 'B005', '내가 죽였다', '2019-08-21', '9791160748604', 14000),
('정준', 'B003', '화산전생 16', '2019-07-19', '9791128394676', 8000),
('무공진', 'B003', '화중매 상하 세트', '2019-07-15', '9791162764428', 32000),
('촌부', 'B003', '천애협로 10', '2019-06-03', '9791104920066', 8000),
('손선영', 'B003', '소암, 바람의 노래', '2019-05-17', '9791187440475', 13800),
('전민희', 'B006', '룬의 아이들 블러디드 2', '2019-09-25', '9788954657556', 14500),
('요나스 요나손', 'B006', '핵을 들고 도망친 101세 노인', '2019-09-25', '9788932919874', 14800),
('닐 셔스터먼, 재러드 셔스터먼', 'B006', '드라이', '2019-09-20', '9788936477783', 15800),
('스테파니 버지스', 'B006', '초콜릿 하트 드래곤', '2019-09-04', '9791135443947', 14800),
('브누아 필리퐁', 'B001', '루거 총을 든 할머니', '2019-07-30', '9791190182591', 13500),
('캐서린 스테드먼', 'B001', '썸씽 인 더 워터', '2019-07-24', '9788950982164', 13500),
('에이미 몰로이', 'B001', '퍼펙트 마더', '2019-07-22', '9791130623177', 14220),
('지건, 콕콕', 'B001', '잔혹동화', '2019-07-20', '9791161950938', 12420),
('류츠신', 'B006', '삼체 3', '2019-09-25', '9788954439923', 17500),
('히가시노 게이고', 'B006', '방과 후', '2019-07-10', '9791163898078', 14800)
;
-- 대여 목록
INSERT INTO rentaltbl VALUES
(22, 30, '2020-01-03', '2020-01-15'),
(10, 10, '2020-02-01', NULL),
(1, 12, '2020-02-01', '2020-02-03'),
(25, 34, '2020-02-02', NULL),
(23, 22, '2020-02-11', '2020-02-17'),
(7, 30, '2020-02-14', NULL),
(9, 31, '2020-02-14', '2020-02-17'),
(21, 15, '2020-02-18', '2020-02-18'),
(22, 17, '2020-02-20', '2020-02-25'),
(14, 7, '2020-02-26', '2020-02-28'),
(15, 9, '2020-03-01', '2020-03-05'),
(19, 44, '2020-03-02', NULL),
(20, 59, '2020-03-03', '2020-03-08'),
(24, 24, '2020-03-08', '2020-03-09')
;
Commons.cs (DB와 윈폼 연동시켜줌) // 한글에 본인의 정보를 넣어주자
//Commons
namespace BookRentalShop20
{
public static class Commons
{
//공용 연결 문자열
public static string CONNSTRING =
"Data Source=본인의아이피;Initial Catalog=sqlDB;Persist Security Info=True;User ID=SSMS아이디;Password=SSMS비번";
public static string LOGINUSERID = "";
}
}
//BooksForm
using MetroFramework;
using MetroFramework.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookRentalShop20
{
public partial class BooksForm : MetroForm
{
string mode = "";
public BooksForm()
{
InitializeComponent();
}
private void UpdateData()
{
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open(); // DB 열기
//string strQuery = "SELECT Idx, Author, Division, Names, ReleaseDate, ISBN, Price "
// + " FROM bookstbl ";
string strQuery = "SELECT b.Idx, b.Author, b.Division, d.Names AS 'DivNames', b.Names, b.ReleaseDate, b.ISBN "
+ " , REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, b.Price), 1), '.00', '') AS 'price' "
+ " FROM bookstbl AS b "
+ " INNER JOIN divtbl AS d ON d.Division = b.Division";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
dataAdapter.Fill(ds, "bookstbl");
GrdBooksTbl.DataSource = ds;
GrdBooksTbl.DataMember = "bookstbl";
}
DataGridViewColumn column = GrdBooksTbl.Columns[2];
column.Visible = false;// Division 비활성화를 통한 화면에 안보이게 하기.
}
private void GrdDivTbl_CellClick(object sender, DataGridViewCellEventArgs e)
{
if(e.RowIndex > -1)
{
DataGridViewRow data = GrdBooksTbl.Rows[e.RowIndex];
TxtIdx.Text = data.Cells[0].Value.ToString();
TxtIdx.ReadOnly = true;
TxtAuthor.Text = data.Cells[1].Value.ToString();
//아래의 두 소스는 같은 역할을 한다.
//CboDivision.SelectedIndex = CboDivision.FindString(data.Cells[3].Value.ToString());
CboDivision.SelectedValue = data.Cells[2].Value;
TxtNames.Text = data.Cells[4].Value.ToString();
DtpReleaseDate.CustomFormat = "yyyy-MM-dd";
DtpReleaseDate.Format = DateTimePickerFormat.Custom;
DtpReleaseDate.Value = DateTime.Parse(data.Cells[5].Value.ToString());
TxtISBN.Text = data.Cells[6].Value.ToString();
TxtPrice.Text = data.Cells[7].Value.ToString();
mode = "UPDATE"; // 수정은 UPDATE
}
}
/// <summary>
/// 새로운 데이터 저장
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnNew_Click(object sender, EventArgs e)
{
ClearTextControls();
mode = "INSERT"; // 신규는 INSERT
}
/// <summary>
/// 데이터 수정
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnSave_Click(object sender, EventArgs e)
{
if(string.IsNullOrEmpty(TxtNames.Text) || string.IsNullOrEmpty(TxtAuthor.Text)
|| string.IsNullOrEmpty(TxtISBN.Text) || string.IsNullOrEmpty(TxtNames.Text) ||string.IsNullOrEmpty(TxtPrice.Text))
{
MetroMessageBox.Show(this, "빈값은 저장할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
SaveProcess();
UpdateData();
ClearTextControls();
}
/// <summary>
/// 입력창 초기화
/// </summary>
private void ClearTextControls()
{
TxtIdx.Text = "";
TxtAuthor.Text = "";
TxtNames.Text = "";
TxtISBN.Text = "";
TxtPrice.Text = "";
CboDivision.SelectedIndex = -1;// 아무것도 선택안함
DtpReleaseDate.CustomFormat = " ";// 공백이 한칸 들어가야함
DtpReleaseDate.Format = DateTimePickerFormat.Custom;
TxtAuthor.Focus();
}
/// <summary>
/// DB 저장 프로세스
/// </summary>
private void SaveProcess()
{
if(string.IsNullOrEmpty(mode))
{
MetroMessageBox.Show(this, "신규버튼을 누르고 데이터를 저장하십시오.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string strQuery = "";
if (mode == "UPDATE")
{
strQuery = "UPDATE bookstbl SET Author = @Author, Division = @Division, Names = @Names, ReleaseDate = @ReleaseDate, "
+ " ISBN = @ISBN, Price = @Price "
+ " WHERE Idx = @Idx";
}
else if (mode == "INSERT")
{
strQuery = "INSERT INTO bookstbl(Author, Division, Names, ReleaseDate, ISBN, Price) "
+ " VALUES(@Author, @Division, @Names, @ReleaseDate, @ISBN, @Price)";
}
cmd.CommandText = strQuery;
SqlParameter parmAuthor = new SqlParameter("@Author", SqlDbType.NVarChar, 45);
parmAuthor.Value = TxtAuthor.Text;
cmd.Parameters.Add(parmAuthor);
SqlParameter parmDivision = new SqlParameter("@Division", SqlDbType.Char, 4);
parmDivision.Value = CboDivision.SelectedValue;
cmd.Parameters.Add(parmDivision);
SqlParameter parmNames = new SqlParameter("@Names", SqlDbType.VarChar, 100);
parmNames.Value = TxtNames.Text;
cmd.Parameters.Add(parmNames);
SqlParameter parmReleaseDate = new SqlParameter("@ReleaseDate", SqlDbType.Date);
parmReleaseDate.Value = DtpReleaseDate.Value;
cmd.Parameters.Add(parmReleaseDate);
SqlParameter parmISBN = new SqlParameter("@ISBN", SqlDbType.VarChar, 200);
parmISBN.Value = TxtISBN.Text;
cmd.Parameters.Add(parmISBN);
SqlParameter parmPrice = new SqlParameter("@Price", SqlDbType.Decimal, 10);
parmPrice.Value = TxtPrice.Text;
cmd.Parameters.Add(parmPrice);
if (mode == "UPDATE")
{
SqlParameter parmIdx = new SqlParameter("@Idx", SqlDbType.Int);
parmIdx.Value = TxtIdx.Text;
cmd.Parameters.Add(parmIdx);
}
cmd.ExecuteNonQuery();
}
}
private void TxtNames_KeyPress(object sender, KeyPressEventArgs e)
{
if(e.KeyChar == 13)
{
BtnSave_Click(sender,new EventArgs());
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(TxtIdx.Text) || string.IsNullOrEmpty(TxtAuthor.Text))
{
MetroMessageBox.Show(this, "빈값은 삭제할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DeleteProcess();
UpdateData();
ClearTextControls();
}
private void DeleteProcess()
{
using(SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM divtbl "
+ " WHERE Division = @Division";
SqlParameter parmDivision = new SqlParameter("@Division", SqlDbType.Char,4);
parmDivision.Value = TxtIdx.Text;
cmd.Parameters.Add(parmDivision);
cmd.ExecuteNonQuery();
}
}
private void MemberForm_Load(object sender, EventArgs e)
{
DtpReleaseDate.CustomFormat = "yyyy-MM-dd";//
DtpReleaseDate.Format = DateTimePickerFormat.Custom;
UpdateData(); // 데이터그리드 DB 데이터 로딩하기
UpdateCboDivision();
}
private void UpdateCboDivision()
{
using(SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT Division, Names FROM divtbl ";
SqlDataReader reader = cmd.ExecuteReader();
Dictionary<string, string> temps = new Dictionary<string, string>();
while(reader.Read())
{
temps.Add(reader[0].ToString(), reader[1].ToString());
}
CboDivision.DataSource = new BindingSource(temps, null);
CboDivision.DisplayMember = "Value";
CboDivision.ValueMember = "Key";
CboDivision.SelectedIndex = -1;
}
}
private void DtpReleaseDate_ValueChanged(object sender, EventArgs e)
{
DtpReleaseDate.CustomFormat = "yyyy-MM-dd";
DtpReleaseDate.Format = DateTimePickerFormat.Custom;
}
}
}
//UserForm
using MetroFramework;
using MetroFramework.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookRentalShop20
{
public partial class UserForm : MetroForm
{
string mode = "";
public UserForm()
{
InitializeComponent();
}
private void DivForm_Load(object sender, EventArgs e)
{
UpdateData(); // 데이터그리드 DB 데이터 로딩하기
}
/// <summary>
/// 사용자 데이터 가져오기
/// </summary>
private void UpdateData()
{
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open(); // DB 열기
string strQuery = "SELECT id,userID,password,lastLoginDt,loginIpAddr "
+ " FROM userManagement ";
//SqlCommand cmd = new SqlCommand(strQuery, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
dataAdapter.Fill(ds, "userTbl");
GrdUserTbl.DataSource = ds;
GrdUserTbl.DataMember = "userTbl";
}
DataGridViewColumn column = GrdUserTbl.Columns[0]; // id컬럼
column.Width = 40;
column.HeaderText = "순번";
column = GrdUserTbl.Columns[1]; // userID 컬럼
column.Width = 80;
column.HeaderText = "아이디";
column = GrdUserTbl.Columns[2]; // Password 컬럼
column.Width = 100;
column.HeaderText = "패스워드";
column = GrdUserTbl.Columns[3]; // 최종 접속 시간
column.Width = 120;
column.HeaderText = "최종접속시간";
column = GrdUserTbl.Columns[4]; // 접속 아이피 주소
column.Width = 150;
column.HeaderText = "접속아이피주소";
}
// 그리드 셀클릭 이벤트
private void GrdDivTbl_CellClick(object sender, DataGridViewCellEventArgs e)
{
if(e.RowIndex > -1)
{
DataGridViewRow data = GrdUserTbl.Rows[e.RowIndex];
TxtId.Text = data.Cells[0].Value.ToString();
TxtUserID.Text = data.Cells[1].Value.ToString();
TxtPassword.Text = data.Cells[2].Value.ToString();
mode = "UPDATE"; // 수정은 UPDATE
}
}
/// <summary>
/// 새로운 데이터 저장
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnNew_Click(object sender, EventArgs e)
{
ClearTextControls();
mode = "INSERT"; // 신규는 INSERT
}
/// <summary>
/// 데이터 수정
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnSave_Click(object sender, EventArgs e)
{
if(string.IsNullOrEmpty(TxtUserID.Text) || string.IsNullOrEmpty(TxtPassword.Text))
{
MetroMessageBox.Show(this, "빈값은 저장할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
SaveProcess();
UpdateData();
ClearTextControls();
}
/// <summary>
/// 입력창 초기화
/// </summary>
private void ClearTextControls()
{
TxtId.Text = "";
TxtUserID.Text = "";
TxtPassword.Text = "";
TxtUserID.Focus();
}
/// <summary>
/// DB 저장 프로세스
/// </summary>
private void SaveProcess()
{
if(string.IsNullOrEmpty(mode))
{
MetroMessageBox.Show(this, "신규버튼을 누르고 데이터를 저장하십시오.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string strQuery = "";
if (mode == "UPDATE")
{
strQuery = "UPDATE dbo.userManagement "
+ " SET userID = @userID, password = @password "
+ " WHERE id = @id";
}
else if (mode == "INSERT")
{
strQuery = "INSERT INTO userManagement (userID, password) "
+ " VALUES(@userID, @password)";
}
cmd.CommandText = strQuery;
SqlParameter parmUserID = new SqlParameter("@userID", SqlDbType.VarChar, 12);
parmUserID.Value = TxtUserID.Text;
cmd.Parameters.Add(parmUserID);
SqlParameter parmPassword = new SqlParameter("@password", SqlDbType.VarChar, 20);
parmPassword.Value = TxtPassword.Text;
cmd.Parameters.Add(parmPassword);
if(mode == "UPDATE")
{
SqlParameter parmId = new SqlParameter("@id", SqlDbType.Int);
parmId.Value = TxtId.Text;
cmd.Parameters.Add(parmId);
}
cmd.ExecuteNonQuery();
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
}
private void DeleteProcess()
{
using(SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM divtbl "
+ " WHERE Division = @Division";
SqlParameter parmDivision = new SqlParameter("@Division", SqlDbType.Char,4);
parmDivision.Value = TxtUserID.Text;
cmd.Parameters.Add(parmDivision);
cmd.ExecuteNonQuery();
}
}
}
}
//RentalForm
using MetroFramework;
using MetroFramework.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookRentalShop20
{
public partial class RentalForm : MetroForm
{
string mode = "";
//완료
public RentalForm()
{
InitializeComponent();
}
//완료
private void UpdateData()
{
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open(); // DB 열기
//string strQuery = "SELECT Idx, Author, Division, Names, ReleaseDate, ISBN, Price "
// + " FROM bookstbl ";
string strQuery = "SELECT r.idx AS '대여번호', m.Names AS '대여회원', "
+ " t.Names AS '장르', "
+ " b.Names AS '대여책제목' ,b.ISBN, "
+ " r.rentalDate AS '대여일' , r.returnDate AS '반납일'"
+ " FROM rentaltbl AS r "
+ " INNER JOIN membertbl AS m ON r.memberIdx = m.Idx "
+ " INNER JOIN bookstbl AS b ON r.bookIdx = b.Idx "
+ " INNER JOIN divtbl AS t ON b.division = t.division";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
dataAdapter.Fill(ds, "rentaltbl");
GrdRentalTbl.DataSource = ds;
GrdRentalTbl.DataMember = "rentaltbl";
}
}
//완료
private void GrdDivTbl_CellClick(object sender, DataGridViewCellEventArgs e)
{
if(e.RowIndex > -1)
{
DataGridViewRow data = GrdRentalTbl.Rows[e.RowIndex];
TxtIdx.Text = data.Cells[0].Value.ToString();
TxtIdx.ReadOnly = true;
CboMemberIdx.SelectedIndex = CboMemberIdx.FindString(data.Cells[1].Value.ToString());
CboBookIdx.SelectedIndex = CboBookIdx.FindString(data.Cells[3].Value.ToString());
DtpRentalDate.CustomFormat = "yyyy-MM-dd";
DtpRentalDate.Format = DateTimePickerFormat.Custom;
DtpRentalDate.Value = DateTime.Parse(data.Cells[5].Value.ToString());
if(string.IsNullOrEmpty(data.Cells[6].Value.ToString()))
{
DtpReturnDate.CustomFormat = " ";
DtpReturnDate.Format = DateTimePickerFormat.Custom;
}
else
{
DtpReturnDate.CustomFormat = "yyyy-MM-dd";
DtpReturnDate.Format = DateTimePickerFormat.Custom;
DtpReturnDate.Value = DateTime.Parse(data.Cells[6].Value.ToString());
}
mode = "UPDATE"; // 수정은 UPDATE
}
}
// 새로운 데이터 저장
private void BtnNew_Click(object sender, EventArgs e)
{
ClearTextControls();
mode = "INSERT"; // 신규는 INSERT
}
//데이터 수정
private void BtnSave_Click(object sender, EventArgs e)
{
if(CboMemberIdx.SelectedIndex == -1 || CboBookIdx.SelectedIndex == -1)
{
MetroMessageBox.Show(this, "빈값은 저장할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
SaveProcess();
UpdateData();
ClearTextControls();
}
// 입력창 초기화
private void ClearTextControls()
{
TxtIdx.Text = "";
CboMemberIdx.SelectedIndex = -1;
CboBookIdx.SelectedIndex = -1;
DtpRentalDate.CustomFormat = " ";
DtpRentalDate.Format = DateTimePickerFormat.Custom;
DtpReturnDate.CustomFormat = " ";
DtpReturnDate.Format = DateTimePickerFormat.Custom;
CboMemberIdx.Focus();
}
//DB 저장
private void SaveProcess()
{
if(string.IsNullOrEmpty(mode))
{
MetroMessageBox.Show(this, "신규버튼을 누르고 데이터를 저장하십시오.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string strQuery = "";
if (mode == "UPDATE")
{
strQuery = "UPDATE rentaltbl "
+ " SET memberIdx = @memberIdx, bookIdx = @bookIdx, rentalDate = @rentalDate, returnDate = @returnDate"
+ " WHERE Idx = @Idx";
}
else if (mode == "INSERT")
{
strQuery = "INSERT INTO rentaltbl(memberIdx, bookIdx, rentalDate, returnDate) "
+ " VALUES(@memberIdx, @bookIdx, @rentalDate, @returnDate)";
}
cmd.CommandText = strQuery;
SqlParameter parmMemberIdx = new SqlParameter("@memberIdx", SqlDbType.Int);
parmMemberIdx.Value = CboMemberIdx.SelectedValue;
cmd.Parameters.Add(parmMemberIdx);
SqlParameter parmBookIdx = new SqlParameter("@bookIdx", SqlDbType.Int);
parmBookIdx.Value = CboBookIdx.SelectedValue;
cmd.Parameters.Add(parmBookIdx);
SqlParameter parmRentalDate = new SqlParameter("@rentalDate", SqlDbType.Date);
parmRentalDate.Value = DtpRentalDate.Value;
cmd.Parameters.Add(parmRentalDate);
SqlParameter parmReturnDate = new SqlParameter("@returnDate", SqlDbType.Date);
parmReturnDate.Value = DtpReturnDate.Value;
cmd.Parameters.Add(parmReturnDate);
if (mode == "UPDATE")
{
SqlParameter parmIdx = new SqlParameter("@Idx", SqlDbType.Int);
parmIdx.Value = TxtIdx.Text;
cmd.Parameters.Add(parmIdx);
}
cmd.ExecuteNonQuery();
}
}
private void TxtNames_KeyPress(object sender, KeyPressEventArgs e)
{
if(e.KeyChar == 13)
{
BtnSave_Click(sender,new EventArgs());
}
}
private void MemberForm_Load(object sender, EventArgs e)
{
DtpRentalDate.CustomFormat = " ";
DtpRentalDate.Format = DateTimePickerFormat.Custom;
DtpReturnDate.CustomFormat = " ";
DtpReturnDate.Format = DateTimePickerFormat.Custom;
//DtpRentalDate.CustomFormat = "yyyy-MM-dd";//
//DtpRentalDate.Format = DateTimePickerFormat.Custom;
//DtpReturnDate.CustomFormat = "yyyy-MM-dd";//
//DtpReturnDate.Format = DateTimePickerFormat.Custom;
UpdateData(); // 데이터그리드 DB 데이터 로딩하기
UpdateCboDivision();
}
//완료
private void UpdateCboDivision()
{
using(SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT Idx, Names, Levels, Addr, Mobile, Email "
+ " FROM membertbl";
SqlDataReader reader = cmd.ExecuteReader();
Dictionary<string, string> temps = new Dictionary<string, string>();
Dictionary<string, string> temps2 = new Dictionary<string, string>();
while(reader.Read())
{
temps.Add(reader[0].ToString(), reader[1].ToString());
}
cmd.CommandText = "SELECT Idx, Author, Division, Names, ReleaseDate, ISBN, Price "
+ " FROM dbo.bookstbl";
reader.Close();
reader = cmd.ExecuteReader();
while(reader.Read())
{
temps2.Add(reader[0].ToString(), reader[3].ToString());
}
CboMemberIdx.DataSource = new BindingSource(temps, null);
CboMemberIdx.DisplayMember = "Value";
CboMemberIdx.ValueMember = "Key";
CboMemberIdx.SelectedIndex = -1;
CboBookIdx.DataSource = new BindingSource(temps2, null);
CboBookIdx.DisplayMember = "Value";
CboBookIdx.ValueMember = "Key";
CboBookIdx.SelectedIndex = -1;
}
}
private void DtpReleaseDate_ValueChanged(object sender, EventArgs e)
{
DtpRentalDate.CustomFormat = "yyyy-MM-dd";
DtpRentalDate.Format = DateTimePickerFormat.Custom;
}
private void BtnCancel_Click(object sender, EventArgs e)
{
ClearTextControls();
}
private void DtpReturnDate_ValueChanged(object sender, EventArgs e)
{
DtpReturnDate.CustomFormat = "yyyy-MM-dd";
DtpReturnDate.Format = DateTimePickerFormat.Custom;
}
}
}
//MemberForm
using MetroFramework;
using MetroFramework.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookRentalShop20
{
public partial class MemberForm : MetroForm
{
string mode = "";
public MemberForm()
{
InitializeComponent();
}
private void UpdateData()
{
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open(); // DB 열기
string strQuery = "SELECT Idx, Names, Levels, Addr, Mobile, Email "
+ " FROM membertbl";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
dataAdapter.Fill(ds, "membertbl");
GrdMemberTbl.DataSource = ds;
GrdMemberTbl.DataMember = "membertbl";
}
}
private void GrdDivTbl_CellClick(object sender, DataGridViewCellEventArgs e)
{
if(e.RowIndex > -1)
{
DataGridViewRow data = GrdMemberTbl.Rows[e.RowIndex];
TxtIdx.Text = data.Cells[0].Value.ToString();
TxtNames.Text = data.Cells[1].Value.ToString();
CboLevels.SelectedIndex = CboLevels.FindString(data.Cells[2].Value.ToString());
TxtAddr.Text = data.Cells[3].Value.ToString();
TxtMobile.Text = data.Cells[4].Value.ToString();
TxtEmail.Text = data.Cells[5].Value.ToString();
mode = "UPDATE"; // 수정은 UPDATE
}
}
// 새로운 데이터 저장
private void BtnNew_Click(object sender, EventArgs e)
{
ClearTextControls();
mode = "INSERT"; // 신규는 INSERT
}
/// <summary>
/// 데이터 수정
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnSave_Click(object sender, EventArgs e)
{
if(string.IsNullOrEmpty(TxtAddr.Text) || string.IsNullOrEmpty(TxtMobile.Text) || string.IsNullOrEmpty(TxtNames.Text)
|| string.IsNullOrEmpty(TxtEmail.Text))
{
MetroMessageBox.Show(this, "빈값은 저장할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
SaveProcess();
UpdateData();
ClearTextControls();
}
/// <summary>
/// 입력창 초기화
/// </summary>
private void ClearTextControls()
{
TxtIdx.Text = "";
TxtNames.Text = "";
TxtAddr.Text = "";
TxtEmail.Text = "";
TxtMobile.Text = "";
CboLevels.SelectedIndex = -1; // 아무것도 선택안함
TxtNames.Focus();
}
/// <summary>
/// DB 저장 프로세스
/// </summary>
private void SaveProcess()
{
if(string.IsNullOrEmpty(mode))
{
MetroMessageBox.Show(this, "신규버튼을 누르고 데이터를 저장하십시오.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string strQuery = "";
if (mode == "UPDATE")
{
strQuery = "UPDATE membertbl "
+ " SET Names = @Names, Levels = @Levels, Addr = @Addr, Mobile = @Mobile, Email = @Email "
+ " WHERE Idx = @Idx";
}
else if (mode == "INSERT")
{
strQuery = "INSERT INTO membertbl(Names, Levels, Addr, Mobile, Email) "
+ " VALUES(@Names, @Levels, @Addr, @Mobile, @Email)";
}
cmd.CommandText = strQuery;
SqlParameter parmNames = new SqlParameter("@Names", SqlDbType.NVarChar, 45);
parmNames.Value = TxtNames.Text;
cmd.Parameters.Add(parmNames);
SqlParameter parmLevels = new SqlParameter("@Levels", SqlDbType.Char, 1);
parmLevels.Value = CboLevels.SelectedItem;
cmd.Parameters.Add(parmLevels);
SqlParameter parmAddr = new SqlParameter("@Addr", SqlDbType.VarChar, 100);
parmAddr.Value = TxtAddr.Text;
cmd.Parameters.Add(parmAddr);
SqlParameter parmMobile = new SqlParameter("@Mobile", SqlDbType.VarChar, 13);
parmMobile.Value = TxtMobile.Text;
cmd.Parameters.Add(parmMobile);
SqlParameter parmEmail = new SqlParameter("@Email", SqlDbType.VarChar, 50);
parmEmail.Value = TxtEmail.Text;
cmd.Parameters.Add(parmEmail);
if(mode == "UPDATE")
{
SqlParameter parmIdx = new SqlParameter("@Idx", SqlDbType.Int);
parmIdx.Value = TxtIdx.Text;
cmd.Parameters.Add(parmIdx);
}
cmd.ExecuteNonQuery();
}
}
private void TxtNames_KeyPress(object sender, KeyPressEventArgs e)
{
if(e.KeyChar == 13)
{
BtnSave_Click(sender,new EventArgs());
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(TxtIdx.Text) || string.IsNullOrEmpty(TxtNames.Text))
{
MetroMessageBox.Show(this, "빈값은 삭제할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DeleteProcess();
UpdateData();
ClearTextControls();
}
private void DeleteProcess()
{
using(SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM divtbl "
+ " WHERE Division = @Division";
SqlParameter parmDivision = new SqlParameter("@Division", SqlDbType.Char,4);
parmDivision.Value = TxtIdx.Text;
cmd.Parameters.Add(parmDivision);
cmd.ExecuteNonQuery();
}
}
private void MemberForm_Load(object sender, EventArgs e)
{
UpdateData(); // 데이터그리드 DB 데이터 로딩하기
}
}
}
//MainForm
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MetroFramework;
using MetroFramework.Forms;
namespace BookRentalShop20
{
public partial class MainForm : MetroForm
{
public MainForm()
{
InitializeComponent();
}
private void MainForm_Load(object sender, EventArgs e)
{
LoginForm loginForm = new LoginForm();
loginForm.ShowDialog();
}
private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
{
if (MetroMessageBox.Show(this, "정말 종료하시겠습니까?", "종료", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
foreach (Form item in this.MdiChildren)
{
item.Close();
}
e.Cancel = false;
}
else
{
e.Cancel = true;
}
}
private void InitChildForm(Form form, string strFormTitle)
{
form.Text = strFormTitle;
form.Dock = DockStyle.Fill;
form.MdiParent = this;
form.Show();
form.WindowState = FormWindowState.Maximized;
}
private void MnuItemDivMng_Click(object sender, EventArgs e)
{
DivForm form = new DivForm();
InitChildForm(form, "구분코드 관리");
}
private void 사용자관리UToolStripMenuItem_Click(object sender, EventArgs e)
{
UserForm form = new UserForm();
InitChildForm(form, "사용자 관리");
}
private void 회원관리MToolStripMenuItem_Click(object sender, EventArgs e)
{
MemberForm form = new MemberForm();
InitChildForm(form, "회원관리");
}
private void MainForm_Activated(object sender, EventArgs e)
{
LblUserID.Text = Commons.LOGINUSERID;
}
private void 책관리BToolStripMenuItem_Click(object sender, EventArgs e)
{
BooksForm form = new BooksForm();
InitChildForm(form, "책 관리");
}
private void 대여책관리RToolStripMenuItem_Click(object sender, EventArgs e)
{
RentalForm form = new RentalForm();
InitChildForm(form, "대여책 관리");
}
}
}
//LoginForm
using MetroFramework.Forms;
using System;
using System.Data;
using System.Windows.Forms;
using MetroFramework;
using System.Data.SqlClient;
namespace BookRentalShop20
{
public partial class LoginForm : MetroForm
{
public LoginForm()
{
InitializeComponent();
}
// 취소버튼 클릭이벤트
private void BtnCancel_Click(object sender, EventArgs e)
{
//Application.Exit();// 정확하게 메모리 해제가 안되는 경우가 있음
Environment.Exit(0);// 0(FLASE): 에러가 없이 정상종료 // 1(TRUE) : 에러가 있어 종료가 안됨
}
// 로그인 처리버튼 클릭이벤트
private void BtnOK_Click(object sender, EventArgs e)
{
LoginProcess();
}
private void TxtUserID_KeyPress(object sender, KeyPressEventArgs e)
{
if(e.KeyChar == (char)13) // 엔터
{
TxtPassWord.Focus();
}
}
private void TxtPassWord_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)13) // 엔터
{
LoginProcess();
}
}
private void LoginProcess()
{
if(string.IsNullOrEmpty(TxtUserID.Text) || string.IsNullOrEmpty(TxtPassWord.Text) )
{
MetroMessageBox.Show(this, "아이디/패스워드를 입력하세요!", "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string strUserId = string.Empty;
try
{
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT userID FROM userManagement"
+ " WHERE userID = @userID"
+ " AND password = @password";
// DB 해킹(SQL Injection)을 방지하기 위해 변수를 사용하여 접속
SqlParameter parmUserID = new SqlParameter("@userID", SqlDbType.VarChar, 12);
parmUserID.Value = TxtUserID.Text;
cmd.Parameters.Add(parmUserID);
SqlParameter parmPassword = new SqlParameter("@password", SqlDbType.VarChar, 20);
parmPassword.Value = TxtPassWord.Text;
cmd.Parameters.Add(parmPassword);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
strUserId = reader["userID"] != null ? reader["userID"].ToString() : "";
if (strUserId != "")
{
Commons.LOGINUSERID = strUserId;
MetroMessageBox.Show(this, "접속성공", "로그인성공");
this.Close();
}
else
{
MetroMessageBox.Show(this, "접속실패", "로그인실패", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
//Debug.WriteLine("On the Debug");
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, $"Error : {ex.StackTrace}", "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
}
//DivForm
using MetroFramework;
using MetroFramework.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookRentalShop20
{
public partial class DivForm : MetroForm
{
string mode = "";
public DivForm()
{
InitializeComponent();
}
private void DivForm_Load(object sender, EventArgs e)
{
UpdateData(); // 데이터그리드 DB 데이터 로딩하기
}
private void UpdateData()
{
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open(); // DB 열기
string strQuery = "SELECT Division, Names FROM divtbl ";
//SqlCommand cmd = new SqlCommand(strQuery, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
dataAdapter.Fill(ds, "divtbl");
GrdDivTbl.DataSource = ds;
GrdDivTbl.DataMember = "divtbl";
}
}
private void GrdDivTbl_CellClick(object sender, DataGridViewCellEventArgs e)
{
if(e.RowIndex > -1)
{
DataGridViewRow data = GrdDivTbl.Rows[e.RowIndex];
TxtDivision.Text = data.Cells[0].Value.ToString();
TxtNames.Text = data.Cells[1].Value.ToString();
TxtDivision.ReadOnly = true;
TxtDivision.BackColor = Color.Beige;
mode = "UPDATE"; // 수정은 UPDATE
}
}
/// <summary>
/// 새로운 데이터 저장
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnNew_Click(object sender, EventArgs e)
{
ClearTextControls();
mode = "INSERT"; // 신규는 INSERT
}
/// <summary>
/// 데이터 수정
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnSave_Click(object sender, EventArgs e)
{
if(string.IsNullOrEmpty(TxtDivision.Text) || string.IsNullOrEmpty(TxtNames.Text))
{
MetroMessageBox.Show(this, "빈값은 저장할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
SaveProcess();
UpdateData();
ClearTextControls();
}
/// <summary>
/// 입력창 초기화
/// </summary>
private void ClearTextControls()
{
TxtDivision.Text = "";
TxtNames.Text = "";
TxtDivision.ReadOnly = false;
TxtDivision.BackColor = Color.White;
TxtDivision.Focus();
}
/// <summary>
/// DB 저장 프로세스
/// </summary>
private void SaveProcess()
{
if(string.IsNullOrEmpty(mode))
{
MetroMessageBox.Show(this, "신규버튼을 누르고 데이터를 저장하십시오.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string strQuery = "";
if (mode == "UPDATE")
{
strQuery = "UPDATE divtbl"
+ " SET Names = @Names"
+ " WHERE Division = @Division";
}
else if (mode == "INSERT")
{
strQuery = "INSERT INTO divtbl (Division,Names) "
+ " VALUES(@Division, @Names)";
}
cmd.CommandText = strQuery;
SqlParameter parmDivision = new SqlParameter("@Division", SqlDbType.Char, 4);
parmDivision.Value = TxtDivision.Text;
cmd.Parameters.Add(parmDivision);
SqlParameter parmNames = new SqlParameter("@Names", SqlDbType.NVarChar, 45);
parmNames.Value = TxtNames.Text;
cmd.Parameters.Add(parmNames);
cmd.ExecuteNonQuery();
}
}
private void TxtNames_KeyPress(object sender, KeyPressEventArgs e)
{
if(e.KeyChar == 13)
{
BtnSave_Click(sender,new EventArgs());
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(TxtDivision.Text) || string.IsNullOrEmpty(TxtNames.Text))
{
MetroMessageBox.Show(this, "빈값은 삭제할 수 없습니다.", "경고", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DeleteProcess();
UpdateData();
ClearTextControls();
}
private void DeleteProcess()
{
using(SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM divtbl "
+ " WHERE Division = @Division";
SqlParameter parmDivision = new SqlParameter("@Division", SqlDbType.Char,4);
parmDivision.Value = TxtDivision.Text;
cmd.Parameters.Add(parmDivision);
cmd.ExecuteNonQuery();
}
}
}
}
반응형
'개발자과정준비 > WinForm' 카테고리의 다른 글
| [Winform] 윈폼 템플릿 없이 윈폼 프로그램을 만드는 방법 (0) | 2020.08.19 |
|---|---|
| [WinForm] 윈폼 소코반 만들기(푸쉬푸쉬 게임 만들기) (0) | 2020.07.20 |
| [WinForm] 6. 윈폼과 SSMS 연동(3) (0) | 2020.06.23 |
| [WinForm] 5. 윈폼과 SSMS 연동(2) 로그인폼 구현 (0) | 2020.06.21 |
| [WinForm] 4. Chart 컨트롤, 윈폼과 SSMS 연동하기 (0) | 2020.06.18 |
