1. 도서 페이지
- 사용자 페이지를 만들고 나서 인지 구현하는데에는 크게 어려움은 없었습니다!
- 귀찮았던건 아무래도 도서 정보이다 보니 입력하고 저장하고 해야되는 데이터가 많다보니 시간이 걸렸네요!
01. DB Table Logic
- 테이블은 총 6 테이블 입니다
- 세분화할거랑 그런거는 최대한 놔눴습니다!
-- 책 정보 테이블
CREATE TABLE Book_Book_Info(
book_no INT NOT NULL PRIMARY KEY IDENTITY(10000,1)
,book_title NVARCHAR(30) NULL
,book_writer NVARCHAR(20) NULL
,book_category INT NULL
,book_description TEXT NULL
,book_price INT NULL
,book_company NVARCHAR(20) NULL
,book_date DATETIME NULL
,book_pages INT NULL
,book_originalImagePath NVARCHAR(MAX) NULL
,book_saveImagePath NVARCHAR(MAX) NULL
,book_rentalcounts INT NULL DEFAULT 0
);
-- 한국소설, 외국소설, 시/에세이, 경제/경영, 자기개발, 인문학, 컴퓨터/IT, 건강, 취미
CREATE TABLE Book_Book_Category(
category_no INT NOT NULL PRIMARY KEY IDENTITY(1,1)
,category_name NVARCHAR(10) NULL
);
-- 책 상태 테이블
CREATE TABLE Book_Book_State(
book_no INT NULL
,book_title NVARCHAR(30) NULL
,book_location INT NULL
,book_rental INT NULL
);
-- 1 재고, 2 렌탈 중
CREATE TABLE Book_Book_RentalState(
rental_no INT NOT NULL PRIMARY KEY IDENTITY(1,1)
,rental_name NVARCHAR(5) NULL
);
-- [A]한국소설, [B]외국소설, [C]시/에세이, [D]경제/경영, [E]자기개발, [F]인문학, [G]컴퓨터/IT, [H]건강, [I]취미
CREATE TABLE Book_Book_Location(
location_no INT NOT NULL PRIMARY KEY IDENTITY(1,1)
,location_name NVARCHAR(10) NULL
);
-- 책렌탈 테이블
CREATE TABLE Book_Book_Rental(
book_no INT NULL
,book_title NVARCHAR(30) NULL
,rental_date DATETIME NULL
,return_date DATETIME NULL
);02. DB Procedure
-1. BOOK_BOOK_S1
- 전체 도서 조회 프로시저
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_Book_S1] Script Date: 2021-08-23 오후 4:56:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 전체 도서 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S1]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT I.book_no as '책 번호', I.book_title as '책 제목', I.book_writer as '책 저자', C.category_name as '카테고리'
, I.book_description as '책 설명', I.book_price as '가격', I.book_company as '출판사', I.book_date as '출판일'
, I.book_pages as '총 페이지수', I.book_originalImagePath as '원본 이미지 경로', I.book_saveImagePath as '저장 이미지 경로'
, I.book_rentalcounts as '총 렌탈 횟수', RS.rental_name as '렌탈 상태', L.location_name AS '책 위치'
FROM Book_Book_Info AS I
INNER JOIN Book_Book_Category AS C
ON I.book_category = C.category_no
INNER JOIN Book_Book_State AS S
ON I.book_no = S.book_no
INNER JOIN Book_Book_RentalState AS RS
ON S.book_rental = RS.rental_no
INNER JOIN Book_Book_Location AS L
ON S.book_location = L.location_no;
END-2. BOOK_BOOK_S2
- 카테고리 조회
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_S2] Script Date: 2021-08-23 오후 4:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.20>
-- Description: <도서관리프로그램, 카테고리 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S2]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Book_Book_Category;
END-3. BOOK_BOOK_S3
- 도서 위치 리스트
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_S3] Script Date: 2021-08-23 오후 4:58:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 도서 위치 리스트>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S3]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Book_Book_Location;
END-4. BOOK_BOOK_S4
- 도서 렌탈 상태 선택 리스트
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_S4] Script Date: 2021-08-23 오후 4:58:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 도서 렌탈 상태 리스트>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S4]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Book_Book_RentalState;
END-5. BOOK_BOOK_S5
- 특정 도서 조회 프로시저
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_S5] Script Date: 2021-08-23 오후 4:58:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 특정 도서 조회>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_S5]
@BOOK_NO INT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT I.book_no, I.book_title, I.book_writer, C.category_no
, I.book_description, I.book_price, I.book_company, CONVERT(char(10), I.book_date, 102)
, I.book_pages, I.book_originalImagePath, I.book_saveImagePath
, RS.rental_no, L.location_no
FROM Book_Book_Info AS I
INNER JOIN Book_Book_Category AS C
ON I.book_category = C.category_no
INNER JOIN Book_Book_State AS S
ON I.book_no = S.book_no
INNER JOIN Book_Book_RentalState AS RS
ON S.book_rental = RS.rental_no
INNER JOIN Book_Book_Location AS L
ON S.book_location = L.location_no
WHERE I.book_no = @BOOK_NO;
END-6. BOOK_BOOK_I1
- 도서 등록
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_I1] Script Date: 2021-08-23 오후 4:59:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 도서 등록>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_I1]
@BOOK_TITLE NVARCHAR(30)
,@BOOK_WRITER NVARCHAR(20)
,@BOOK_CATEGORY INT
,@BOOK_DESCRIPTION TEXT
,@BOOK_PRICE INT
,@BOOK_COMPANY NVARCHAR(20)
,@BOOK_DATE DATETIME
,@BOOK_PAGES INT
,@BOOK_ORIGINALPATH NVARCHAR(MAX)
,@BOOK_SAVEPATH NVARCHAR(MAX)
,@BOOK_LOCATION INT
,@BOOK_STATE INT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
INSERT INTO Book_Book_Info(book_title, book_writer, book_category, book_description, book_price, book_company, book_date, book_pages, book_originalImagePath, book_saveImagePath)
VALUES (@BOOK_TITLE
, @BOOK_WRITER
, @BOOK_CATEGORY
, @BOOK_DESCRIPTION
, @BOOK_PRICE
, @BOOK_COMPANY
, @BOOK_DATE
, @BOOK_PAGES
, @BOOK_ORIGINALPATH
, @BOOK_SAVEPATH);
DECLARE @BOOK_INDEX INT;
SELECT @BOOK_INDEX = book_no
FROM Book_Book_Info
WHERE book_title = @BOOK_TITLE
AND book_saveImagePath = @BOOK_SAVEPATH;
INSERT INTO Book_Book_State VALUES(@BOOK_INDEX, @BOOK_TITLE, @BOOK_LOCATION, @BOOK_STATE);
IF(@BOOK_STATE = 2)
INSERT INTO Book_Book_Rental(book_no, book_title, rental_date)
VALUES(
@BOOK_INDEX
,@BOOK_TITLE
,GETDATE()
);
UPDATE Book_Book_Info SET book_rentalcounts = book_rentalcounts + 1 WHERE book_no = @BOOK_INDEX;
END
-7. BOOK_BOOK_U1
- 도서 수정 프로시저
- 수정 시 렌탈에서 재고로 갔는지 재고에서 렌탈로 갔는지 각 상황에 따라 다른 테이블에도 영향을 줄 수 있습니다
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_U1] Script Date: 2021-08-23 오후 5:00:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 도서 수정>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_U1]
@BOOK_NO INT
,@BOOK_TITLE NVARCHAR(30)
,@BOOK_WRITER NVARCHAR(20)
,@BOOK_CATEGORY INT
,@BOOK_DESCRIPTION TEXT
,@BOOK_PRICE INT
,@BOOK_COMPANY NVARCHAR(20)
,@BOOK_DATE DATETIME
,@BOOK_PAGES INT
,@BOOK_ORIGINALPATH NVARCHAR(MAX)
,@BOOK_SAVEPATH NVARCHAR(MAX)
,@BOOK_LOCATION INT
,@BOOK_STATE INT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
UPDATE Book_Book_Info
SET book_title = @BOOK_TITLE
,book_writer = @BOOK_WRITER
,book_category = @BOOK_CATEGORY
,book_description = @BOOK_DESCRIPTION
,book_price = @BOOK_PRICE
,book_company = @BOOK_COMPANY
,book_date = @BOOK_DATE
,book_pages = @BOOK_PAGES
,book_originalImagePath = @BOOK_ORIGINALPATH
,book_saveImagePath = @BOOK_SAVEPATH
WHERE book_no = @BOOK_NO;
UPDATE Book_Book_State
SET book_title = @BOOK_TITLE
,book_location = @BOOK_LOCATION
,book_rental = @BOOK_STATE
WHERE book_no = @BOOK_NO;
DECLARE @COUNT INT;
SELECT @COUNT = COUNT(*) FROM Book_Book_Rental WHERE book_no = 10003;
IF(@BOOK_STATE = 2 AND @COUNT <= 0)
INSERT INTO Book_Book_Rental(book_no, book_title, rental_date)
VALUES(
@BOOK_NO
,@BOOK_TITLE
,GETDATE()
);
UPDATE Book_Book_Info SET book_rentalcounts = book_rentalcounts + 1 WHERE book_no = @BOOK_NO;
IF(@BOOK_STATE = 1 AND @COUNT > 0)
UPDATE Book_Book_Rental
SET return_date = GETDATE();
END-8. BOOK_BOOK_D1
- 도서 삭제 프로시저
- 삭제 시에는 관련된 테이블의 자료를 전부 삭제해줄게요!
- 만약에 처음에 테이블 만들 때 연결해두면 이 부분은 깔끔해지겠네요!
USE [BookManagement]
GO
/****** Object: StoredProcedure [dbo].[BOOK_BOOK_D1] Script Date: 2021-08-23 오후 5:01:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <문민승>
-- Create date: <2021.08.23>
-- Description: <도서관리프로그램, 특정 도서 삭제>
-- =============================================
ALTER PROCEDURE [dbo].[BOOK_BOOK_D1]
@BOOK_NO INT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DELETE FROM Book_Book_Info WHERE book_no = @BOOK_NO;
DELETE FROM Book_Book_State WHERE book_no = @BOOK_NO;
DELETE FROM Book_Book_Rental WHERE book_no = @BOOK_NO;
END03. Main.cs
- 추가, 삭제 버튼은 없애고 조회에 다 넣는 방식을 채택했어요!
-1. code
- 제일 난잡한 코드 인것 같아요!
using BookManagementProgram.XtraForm;
using BookManagementProgram.Model;
using BookManagementProgram.Repository;
using System;
using System.Windows.Forms;
using DevExpress.XtraTabbedMdi;
using System.Reflection;
using BookManagementProgram.UserControls;
using BookManagementProgram.BookControls;
namespace BookManagementProgram
{
public partial class Main : DevExpress.XtraEditors.XtraForm
{
User user = BookUserRepository.user;
XtraTabbedMdiManager mdiManager;
public Main()
{
InitializeComponent();
this.StartPosition = FormStartPosition.CenterScreen;
switch(user.authorityNo)
{
case 0:
user_page.Visible = true;
book_page.Visible = true;
rental_page.Visible = true;
break;
case 1:
book_page.Visible = true;
rental_page.Visible = true;
break;
case 2:
rental_page.Visible = true;
break;
}
}
// 유저 페이지 유저 조회 버튼
private void user_reset_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
MenuRun("UserSelectForm", user_reset_btn.Hint);
}
// 폼 종료
private void Main_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}
// 폼 종료 버튼
private void home_close_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
Application.Exit();
}
// 비밀번호 변경 버튼
private void home_changePassword_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
(new FindByPassword()).ShowDialog();
}
// 유저 페이지 유저 등록 버튼
private void user_add_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
(new UserAddForm()).ShowDialog();
}
// 유저 페이지 유저 검색 버튼
private void user_search_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
(new UserSearchForm()).ShowDialog();
}
// 도서 페이지 유저 조회 버튼
private void book_reset_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
MenuRun("BookSelectForm", book_reset_btn.Hint);
}
// 도서 추가 버튼
private void book_add_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
(new BookAddForm()).ShowDialog();
}
// 도서 조회 버튼
private void book_search_btn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
(new BookSearchForm()).ShowDialog();
}
// 메인 폼 로드
private void Main_Load(object sender, EventArgs e)
{
ChildFormAdd();
Home();
}
// MID Child Form추가
private void ChildFormAdd()
{
mdiManager = new XtraTabbedMdiManager();
mdiManager.MdiParent = this;
mdiManager.HeaderButtons = DevExpress.XtraTab.TabButtons.Close;
mdiManager.HeaderLocation = DevExpress.XtraTab.TabHeaderLocation.Top;
mdiManager.PageAdded += MdiManager_ChildFormAdd;
mdiManager.ClosePageButtonShowMode = DevExpress.XtraTab.ClosePageButtonShowMode.InActiveTabPageAndTabControlHeader;
}
// MDI 설정
private void MdiManager_ChildFormAdd(object sender, MdiTabPageEventArgs e)
{
XtraMdiTabPage page = e.Page;
}
// 첫 로드할때 보여줄 메인 페이지
private void Home()
{
foreach (Form item in this.MdiChildren)
{
if (item.Name == "HOME") //Notice
{
item.Close();
}
}
Assembly asm = Assembly.GetExecutingAssembly();
Form frm = (Form)asm.CreateInstance(string.Format("{0}.{1}", "BookManagementProgram.MainControls", "MainPageForm"));
if (frm != null)
{
frm.Text = "메인화면";
frm.MdiParent = this;
frm.Dock = DockStyle.Fill;
frm.Show();
}
}
// 메뉴 아이템에 따라 MID Child 생성
private void MenuRun(string Menu, string FormTitle)
{
string nameSpace;
foreach (Form item in this.MdiChildren)
{
if (Menu == item.Name)
{
item.Close();
}
}
Form frm = null;
if (Menu.Equals("UserSelectForm"))
{
nameSpace = "BookManagementProgram.UserControls";
System.Reflection.Assembly assembly = System.Reflection.Assembly.Load("BookManagementProgram");
string FileName = "UserSelectForm";
frm = (Form)assembly.CreateInstance(string.Format("{0}.{1}", nameSpace, FileName));
}
else if(Menu.Equals("BookSelectForm"))
{
nameSpace = "BookManagementProgram.BookControls";
System.Reflection.Assembly assembly = System.Reflection.Assembly.Load("BookManagementProgram");
string FileName = "BookSelectForm";
frm = (Form)assembly.CreateInstance(string.Format("{0}.{1}", nameSpace, FileName));
}
if (frm != null)
{
frm.Text = FormTitle;
frm.MdiParent = this;
frm.Show();
}
else
{
MessageBox.Show("개발중이거나 존재하지 않는 화면입니다.", "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
}04. Model / Book.cs
- User Model처럼 도서와 관련된 모델입니다!
- 이 모델은 다음에 작업할 렌트와 관련되어 있습니다!
namespace BookManagementProgram.Model
{
class Book
{
private int book_no; // 책 번호
private string book_title; // 책 제목
private string book_writer; // 저자
private int book_category_no; // 카테고리
private string book_category_name; // 카테고리
private string book_description; // 설명
private int book_price; // 가격
private string book_company; // 출판사
private string book_date; // 출판일
private int book_pages; // 총 페이지 수
private string book_originalImagePath; // 파일 원래 이름
private string book_savenameImaegPath; // 파일 저장 이름
private int book_rentalcounts; // 책 렌탈한 수
private int book_location_no; // 책 위치
private string book_location_name; // 책 위치
private int book_rental_no; // 책 렌탈 상태
private string book_rental_name; // 책 렌탈 상태
private string rental_date; // 렌탈 한 날
private string return_date; // 반납 한 날
public int bookNo { get { return book_no; } set { book_no = value; } }
public string bookTitle { get { return book_title; } set { book_title = value; } }
public string bookWriter { get { return book_writer; } set { book_writer = value; } }
public int bookCategory_no { get { return book_category_no; } set { book_category_no = value; } }
public string bookCategory_name { get { return book_category_name; } set { book_category_name = value; } }
public string bookDescription { get { return book_description; } set { book_description = value; } }
public int bookPrice { get { return book_price; } set { book_price = value; } }
public string bookCompany { get { return book_company; } set { book_company = value; } }
public string bookDate { get { return book_date; } set { book_date = value; } }
public int bookPages { get { return book_pages; } set { book_pages = value; } }
public string bookOriginalImagePath { get { return book_originalImagePath; } set { book_originalImagePath = value; } }
public string bookSavenameImaegPath { get { return book_savenameImaegPath; } set { book_savenameImaegPath = value; } }
public int bookRentalcounts { get { return book_rentalcounts; } set { book_rentalcounts = value; } }
public int bookLocationNo { get { return book_location_no; } set { book_location_no = value; } }
public string bookLocationName { get { return book_location_name; } set { book_location_name = value; } }
public int bookRentalNo { get { return book_rental_no; } set { book_rental_no = value; } }
public string bookRental_name { get { return book_rental_name; } set { book_rental_name = value; } }
public string rentalDate { get { return rental_date; } set { rental_date = value; } }
public string returnDate { get { return return_date; } set { return_date = value; } }
// 전체 도서 리스트 조회 시 사용할 생성자
public Book(int book_no, string book_title, string book_writer, string book_category_name, string book_description, int book_price, string book_company, string book_date, int book_pages, string book_originalImagePath, string book_savenameImaegPath, int book_rentalcounts, string book_location_name, string book_rental_name)
{
this.book_no = book_no;
this.book_title = book_title;
this.book_writer = book_writer;
this.book_category_name = book_category_name;
this.book_description = book_description;
this.book_price = book_price;
this.book_company = book_company;
this.book_date = book_date;
this.book_pages = book_pages;
this.book_originalImagePath = book_originalImagePath;
this.book_savenameImaegPath = book_savenameImaegPath;
this.book_rentalcounts = book_rentalcounts;
this.book_location_name = book_location_name;
this.book_rental_name = book_rental_name;
}
public Book()
{
}
}
}05. Repository
-1. IBookRepository.interface
- 솔직히 굳이 크게 도움이 된다는 생각은 안들거에요!
- 왜냐하면 실제로 이 정도 규모면 크게 도움이 안되는게 맞아요!
- 그리고 도움이 된다하면 그건 나중에 User Interface와 합쳐서 정말 하나의 인터페이스를 만들때겠죠!
using BookManagementProgram.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BookManagementProgram.Repository
{
interface IBookRepository
{
DataTable GetAllBooks();
Dictionary<string, object> GetCategoryList();
Dictionary<string, object> GetBookLocationList();
Dictionary<string, object> GetBookRentalStateList();
bool AddBook(Book book);
Book GetBookByNumber(string bookNo);
bool DeleteBookByNumber(string bookNo);
bool UpdateBookInfo(Book book);
}
}-2. BookRepository.cs
- 옆으로 너무 길어서 '@'를 사용해서 멀티라인으로 작성했습니다
- 세로로 길어졌지만 가독성이 훨씬 좋아 졌죠!
using BookManagementProgram.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BookManagementProgram.Repository
{
class BookRepository : IBookRepository
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
public static List<Book> bookList = null;
public BookRepository()
{
conn = DBConn.DBConn.getConn();
cmd = new SqlCommand();
cmd.Connection = conn;
}
// 도서 전체 리스트
public DataTable GetAllBooks()
{
DataTable dt = new DataTable();
try
{
string sql = "EXEC BOOK_Book_S1;";
cmd.CommandText = sql;
conn.Open();
// 데이터 출력
using (SqlDataReader SR = cmd.ExecuteReader())
{
dt.Load(SR);
}
return dt;
}
catch (SqlException e)
{
return dt;
}
finally
{
conn.Close();
}
}
// 카테고리 리스트
public Dictionary<string, object> GetCategoryList()
{
Dictionary<string, object> dic = new Dictionary<string, object>();
try
{
string sql = "EXEC BOOK_BOOK_S2;";
cmd.CommandText = sql;
conn.Open();
// 데이터 출력
using (SqlDataReader SR = cmd.ExecuteReader())
{
while (SR.Read())
{
dic.Add(SR[1].ToString(), (int)SR[0]);
}
}
return dic;
}
catch (SqlException e)
{
return dic;
}
finally
{
conn.Close();
}
}
// 책 위치 리스트
public Dictionary<string, object> GetBookLocationList()
{
Dictionary<string, object> dic = new Dictionary<string, object>();
try
{
string sql = "EXEC BOOK_BOOK_S3;";
cmd.CommandText = sql;
conn.Open();
// 데이터 출력
using (SqlDataReader SR = cmd.ExecuteReader())
{
while (SR.Read())
{
dic.Add(SR[1].ToString(), (int)SR[0]);
}
}
return dic;
}
catch (SqlException e)
{
return dic;
}
finally
{
conn.Close();
}
}
// 책 렌탈 상태 리스트
public Dictionary<string, object> GetBookRentalStateList()
{
Dictionary<string, object> dic = new Dictionary<string, object>();
try
{
string sql = "EXEC BOOK_BOOK_S4;";
cmd.CommandText = sql;
conn.Open();
// 데이터 출력
using (SqlDataReader SR = cmd.ExecuteReader())
{
while (SR.Read())
{
dic.Add(SR[1].ToString(), (int)SR[0]);
}
}
return dic;
}
catch (SqlException e)
{
return dic;
}
finally
{
conn.Close();
}
}
// 도서 등록
public bool AddBook(Book book)
{
try
{
string sql = $@"EXEC BOOK_Book_I1 @BOOK_TITLE = '{book.bookTitle}'
,@BOOK_WRITER = '{book.bookWriter}'
,@BOOK_CATEGORY = {book.bookCategory_no}
,@BOOK_DESCRIPTION = '{book.bookDescription}'
,@BOOK_PRICE = {book.bookPrice}
,@BOOK_COMPANY = '{book.bookCompany}'
,@BOOK_DATE = '{book.bookDate}'
,@BOOK_PAGES = {book.bookPages}
,@BOOK_ORIGINALPATH = '{book.bookOriginalImagePath}'
,@BOOK_SAVEPATH = '{book.bookSavenameImaegPath}'
,@BOOK_LOCATION = {book.bookLocationNo}
,@BOOK_STATE = {book.bookRentalNo};";
cmd.CommandText = sql;
conn.Open();
return cmd.ExecuteNonQuery() > 0 ? true : false;
}
catch (SqlException e)
{
return false;
}
finally
{
conn.Close();
}
}
// 도서 조회
public Book GetBookByNumber(string bookNo)
{
Book book = null ;
try
{
string sql = $"EXEC BOOK_BOOK_S5 @BOOK_NO = {bookNo};";
cmd.CommandText = sql;
conn.Open();
// 데이터 출력
using (SqlDataReader SR = cmd.ExecuteReader())
{
while (SR.Read())
{
book = new Book();
book.bookNo = (int)SR[0];
book.bookTitle = SR[1].ToString();
book.bookWriter = SR[2].ToString();
book.bookCategory_no = (int)SR[3];
book.bookDescription = SR[4].ToString();
book.bookPrice = (int)SR[5];
book.bookCompany = SR[6].ToString();
book.bookDate = SR[7].ToString();
book.bookPages = (int)SR[8];
book.bookOriginalImagePath = SR[9].ToString();
book.bookSavenameImaegPath = SR[10].ToString();
book.bookRentalNo = (int)SR[11];
book.bookLocationNo = (int)SR[12];
}
}
return book;
}
catch (SqlException e)
{
return book;
}
finally
{
conn.Close();
}
}
// 도서 삭제
public bool DeleteBookByNumber(string bookNo)
{
try
{
string sql = $"EXEC BOOK_BOOK_D1 @BOOK_NO = {bookNo};";
cmd.CommandText = sql;
conn.Open();
return cmd.ExecuteNonQuery() > 0 ? true : false;
}
catch (SqlException e)
{
return false;
}
finally
{
conn.Close();
}
}
// 사용자 정보 수정
public bool UpdateBookInfo(Book book)
{
try
{
string sql = $@"EXEC BOOK_BOOK_U1 @BOOK_NO = {book.bookNo}
,@BOOK_TITLE = '{book.bookTitle}'
,@BOOK_WRITER = '{book.bookWriter}'
,@BOOK_CATEGORY = {book.bookCategory_no}
,@BOOK_DESCRIPTION = '{book.bookDescription}'
,@BOOK_PRICE = {book.bookPrice}
,@BOOK_COMPANY = '{book.bookCompany}'
,@BOOK_DATE = '{book.bookDate}'
,@BOOK_PAGES = {book.bookPages}
,@BOOK_ORIGINALPATH = '{book.bookOriginalImagePath}'
,@BOOK_SAVEPATH = '{book.bookSavenameImaegPath}'
,@BOOK_LOCATION = {book.bookLocationNo}
,@BOOK_STATE = {book.bookRentalNo};";
cmd.CommandText = sql;
conn.Open();
return cmd.ExecuteNonQuery() > 0 ? true : false;
}
catch (SqlException e)
{
return false;
}
finally
{
conn.Close();
}
}
}
}06. BookControls
- Controls는 UserControls랑 동일 합니다!
-1. BookSelectForm.cs
- Controls
- GridControl
using BookManagementProgram.Repository;
using DevExpress.XtraGrid.Views.Grid;
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;
namespace BookManagementProgram.BookControls
{
public partial class BookSelectForm : Form
{
BookRepository bookRepository = null;
public BookSelectForm()
{
InitializeComponent();
bookRepository = new BookRepository();
InitGridControl();
this.gridControl1.DataSource = bookRepository.GetAllBooks();
}
private void InitGridControl()
{
GridView gv = this.gridControl1.MainView as GridView;
gv.OptionsView.ShowGroupPanel = false;
gv.OptionsBehavior.Editable = false;
}
}
}
-2. BookAddForm.cs
- 도서 정보를 입력하다 보니 확실히 양은 맞죠
- 하지만 유저 정보처럼 API를 갖고와서 인증을 할 필요가 없죠!
- 이미지 파일을 불러오면 이미지 파일 하단에있는 picturebox에 이미지가 보여집니다!
- Controls
- label
- textbox
- book_title_txt
- book_writer_txt
- book_description_txt
- book_price_txt
- book_company_txt
- book_date_txt
- book_pages_txt
- book_image_txt
- combobox
- book_category_cmb
- book_location_cmb
- book_state_cmb
- button
- book_image_btn
- simplebutton
- cancel_btn
- add_btn
- picturebox
using BookManagementProgram.Model;
using BookManagementProgram.Repository;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookManagementProgram.BookControls
{
public partial class BookAddForm : Form
{
BookRepository bookRepository = null;
public BookAddForm()
{
InitializeComponent();
bookRepository = new BookRepository();
this.StartPosition = FormStartPosition.CenterParent;
// 카테고리 콤보 박스
book_category_cmb.DataSource = new BindingSource(bookRepository.GetCategoryList(), null);
book_category_cmb.DisplayMember = "Key";
book_category_cmb.ValueMember = "Value";
// 위치 콤보 박스
book_location_cmb.DataSource = new BindingSource(bookRepository.GetBookLocationList(), null);
book_location_cmb.DisplayMember = "Key";
book_location_cmb.ValueMember = "Value";
// 도서 상태 콤보 박스
book_state_cmb.DataSource = new BindingSource(bookRepository.GetBookRentalStateList(), null);
book_state_cmb.DisplayMember = "Key";
book_state_cmb.ValueMember = "Value";
}
// 취소 버튼
private void cancel_btn_Click(object sender, EventArgs e)
{
this.Close();
}
// 도서 등록
private void add_book_btn_Click(object sender, EventArgs e)
{
if(CheckInput())
{
Book book = CreateBookInstance();
if (bookRepository.AddBook(book))
{
MessageBox.Show("도서 등록에 성공하셨습니다.");
this.Close();
return;
}
MessageBox.Show("도서 등록에 실패하셨습니다.");
}
}
// Book 객체 생성
private Book CreateBookInstance()
{
Book book = new Book();
book.bookTitle = book_title_txt.Text;
book.bookWriter = book_writer_txt.Text;
book.bookCategory_no = (int)book_category_cmb.SelectedValue;
book.bookDescription = book_description_txt.Text;
book.bookPrice = Convert.ToInt32(book_price_txt.Text);
book.bookCompany = book_company_txt.Text;
book.bookDate = book_date_txt.Text;
book.bookPages = Convert.ToInt32(book_pages_txt.Text);
book.bookOriginalImagePath = book_image_txt.Text;
book.bookSavenameImaegPath = SaveBookImageFIle();
book.bookLocationNo = (int)book_location_cmb.SelectedValue;
book.bookRentalNo = (int)book_state_cmb.SelectedValue;
return book;
}
// 텍스트박스 입력 확인
private bool CheckInput()
{
bool check = false;
if (string.IsNullOrEmpty(book_title_txt.Text))
{
MessageBox.Show("책 제목을 입력해주세요.");
book_title_txt.Focus();
}
else if (string.IsNullOrEmpty(book_writer_txt.Text))
{
MessageBox.Show("책 저자를 입력해주세요.");
book_writer_txt.Focus();
}
else if (string.IsNullOrEmpty(book_description_txt.Text))
{
MessageBox.Show("도서 설명을 입력해주세요.");
book_description_txt.Focus();
}
else if (string.IsNullOrEmpty(book_price_txt.Text) || CheckNumeric(book_price_txt.Text))
{
MessageBox.Show("가격을 입력해주세요.");
book_price_txt.Focus();
}
else if (string.IsNullOrEmpty(book_company_txt.Text))
{
MessageBox.Show("출판사를 입력해주세요.");
book_company_txt.Focus();
}
else if (string.IsNullOrEmpty(book_date_txt.Text) || CheckDatePattern(book_date_txt.Text))
{
MessageBox.Show("출판날짜를 입력해주세요.(2021.01.01)");
book_date_txt.Focus();
}
else if (string.IsNullOrEmpty(book_pages_txt.Text) || CheckNumeric(book_pages_txt.Text))
{
MessageBox.Show("총 페이지 수를 입력해주세요.");
book_pages_txt.Focus();
}
else if (string.IsNullOrEmpty(book_image_txt.Text))
{
MessageBox.Show("이미지를 선택해주세요.");
book_image_btn.Focus();
}
else
{
check = true;
}
return check;
}
// 출판일 확인 정규식
private bool CheckDatePattern(string date)
{
string pattern = @"^(1[0-9][0-9][0-9]|20\d{2})\.(0[0-9]|1[0-2])\.(0[1-9]|[1-2][0-9]|3[0-1])$";
if (!Regex.IsMatch(date, pattern))
{
return true;
}
else
{
return false;
}
}
// 숫자인지 확인
private bool CheckNumeric(string value)
{
int result = -1;
if(Int32.TryParse(value, out result))
{
if(result > 0)
{
return false;
}
}
return true;
}
// 책 이미지 불러오기 버튼
private void book_image_btn_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "이미지 불러오기";
openFileDialog.FileName = "";
openFileDialog.Filter = "Image files (*.jpg, *.jpeg, *.jpe, *.jfif, *.png) | *.jpg; *.jpeg; *.jpe; *.jfif; *.png";
book_image_txt.Text = string.Empty;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
book_image_txt.Text = openFileDialog.FileName;
pictureBox1.Image = new Bitmap(openFileDialog.FileName);
pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;
}
}
// 이미지 파일 저장
private string SaveBookImageFIle()
{
string dir = @"C:\Users\jjh\Desktop\mms\BookManagerForm\BookImages";
Image image = Image.FromFile(book_image_txt.Text);
bool bExist = true;
int fileCount = 0;
string fileName = string.Empty;
string dirMapPath = string.Empty;
if (image != null)
{
fileName = book_image_txt.Text.Split('\\').Last();
string strName = fileName.Substring(0, fileName.LastIndexOf("."));
string strExt = fileName.Substring(fileName.LastIndexOf("."));
while (bExist)
{
dirMapPath = dir;
string pathCombine = System.IO.Path.Combine(dirMapPath, fileName);
if (System.IO.File.Exists(pathCombine))
{
fileCount++;
fileName = $"{strName}({fileCount}){strExt}";
}
else
{
bExist = false;
}
}
image.Save($@"{dir}\{fileName}");
MessageBox.Show("이미지 저장이 되었습니다");
}
return $@"{dir}\{fileName}";
}
}
}
-3. BookSearchForm.cs
- BookAddFOrm과 양식은 동일하고 수정, 삭제, 초기화만 추가되었다고 보시면 됩니다
- 그리고 도서 번호 조회두요!
using BookManagementProgram.Model;
using BookManagementProgram.Repository;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BookManagementProgram.BookControls
{
public partial class BookSearchForm : Form
{
BookRepository bookRepository = null;
private string originalFile = string.Empty;
private string saveFile = string.Empty;
public BookSearchForm()
{
InitializeComponent();
bookRepository = new BookRepository();
this.StartPosition = FormStartPosition.CenterParent;
// 카테고리 콤보 박스
book_category_cmb.DataSource = new BindingSource(bookRepository.GetCategoryList(), null);
book_category_cmb.DisplayMember = "Key";
book_category_cmb.ValueMember = "Value";
// 위치 콤보 박스
book_location_cmb.DataSource = new BindingSource(bookRepository.GetBookLocationList(), null);
book_location_cmb.DisplayMember = "Key";
book_location_cmb.ValueMember = "Value";
// 도서 상태 콤보 박스
book_state_cmb.DataSource = new BindingSource(bookRepository.GetBookRentalStateList(), null);
book_state_cmb.DisplayMember = "Key";
book_state_cmb.ValueMember = "Value";
}
// 취소 버튼
private void cancel_btn_Click(object sender, EventArgs e)
{
this.Close();
}
// 조회버튼
private void search_btn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(book_no_txt.Text))
{
MessageBox.Show("도서 번호를 입력해주세요.");
book_no_txt.Focus();
return;
}
Book book = bookRepository.GetBookByNumber(book_no_txt.Text);
if (book == null)
{
MessageBox.Show("도서 번호를 확인해주세요!");
book_no_txt.Focus();
return;
}
book_no_txt.ReadOnly = true;
book_title_txt.Text = book.bookTitle;
book_writer_txt.Text = book.bookWriter;
book_category_cmb.SelectedValue = book.bookCategory_no;
book_description_txt.Text = book.bookDescription;
book_price_txt.Text = book.bookPrice.ToString();
book_company_txt.Text = book.bookCompany;
book_date_txt.Text = book.bookDate;
book_pages_txt.Text = book.bookPages.ToString();
pictureBox1.Load(book.bookSavenameImaegPath);
pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;
book_location_cmb.SelectedValue = book.bookLocationNo;
book_state_cmb.SelectedValue = book.bookRentalNo;
originalFile = book.bookOriginalImagePath;
saveFile = book.bookSavenameImaegPath;
}
// 초기화버튼
private void reset_btn_Click(object sender, EventArgs e)
{
book_no_txt.ReadOnly = false;
book_title_txt.Text = string.Empty;
book_writer_txt.Text = string.Empty;
book_category_cmb.SelectedValue = string.Empty;
book_description_txt.Text = string.Empty;
book_price_txt.Text = string.Empty;
book_company_txt.Text = string.Empty;
book_date_txt.Text = string.Empty;
book_pages_txt.Text = string.Empty;
pictureBox1.Image = null;
book_location_cmb.SelectedValue = string.Empty;
book_state_cmb.SelectedValue = string.Empty;
}
// 삭제 버튼
private void delete_btn_Click(object sender, EventArgs e)
{
if (!book_no_txt.ReadOnly)
{
MessageBox.Show("도서를 조회 후 삭제해주세요.");
return;
}
if (MessageBox.Show($"{book_no_txt.Text}번의 도서를 정말 삭제하시겠습니까?", null, MessageBoxButtons.YesNo) == DialogResult.Yes)
{
if (bookRepository.DeleteBookByNumber(book_no_txt.Text))
{
MessageBox.Show("정상적으로 삭제 되었습니다.");
this.Close();
return;
}
MessageBox.Show("없는 도서 이거나 번호를 확인해주세요.");
return;
}
MessageBox.Show("취소 하셨습니다.");
}
// 도서 수정 버튼
private void add_btn_Click(object sender, EventArgs e)
{
if (CheckInput())
{
if (MessageBox.Show($"{book_no_txt.Text}번의 사용자를 수정하시겠습니까?", null, MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Book book = new Book();
book.bookNo = Convert.ToInt32(book_no_txt.Text);
book.bookTitle = book_title_txt.Text;
book.bookWriter = book_writer_txt.Text;
book.bookCategory_no = (int)book_category_cmb.SelectedValue;
book.bookDescription = book_description_txt.Text;
book.bookPrice = Convert.ToInt32(book_price_txt.Text);
book.bookCompany = book_company_txt.Text;
book.bookDate = book_date_txt.Text;
book.bookPages = Convert.ToInt32(book_pages_txt.Text);
if (!string.IsNullOrEmpty(book_image_txt.Text))
{
originalFile = book_image_txt.Text;
saveFile = SaveBookImageFIle();
}
book.bookOriginalImagePath = originalFile;
book.bookSavenameImaegPath = saveFile;
book.bookLocationNo = (int)book_location_cmb.SelectedValue;
book.bookRentalNo = (int)book_state_cmb.SelectedValue;
if (bookRepository.UpdateBookInfo(book))
{
MessageBox.Show("정상적으로 수정 되었습니다.");
this.Close();
return;
}
MessageBox.Show("없는 도서 이거나 번호를 확인해주세요.");
return;
}
MessageBox.Show("취소 하셨습니다.");
}
}
// 텍스트박스 입력 확인
private bool CheckInput()
{
bool check = false;
if (!book_no_txt.ReadOnly)
{
MessageBox.Show("도서를 조회 후 수정해주세요.");
return false;
}
if (string.IsNullOrEmpty(book_title_txt.Text))
{
MessageBox.Show("책 제목을 입력해주세요.");
book_title_txt.Focus();
}
else if (string.IsNullOrEmpty(book_writer_txt.Text))
{
MessageBox.Show("책 저자를 입력해주세요.");
book_writer_txt.Focus();
}
else if (string.IsNullOrEmpty(book_description_txt.Text))
{
MessageBox.Show("도서 설명을 입력해주세요.");
book_description_txt.Focus();
}
else if (string.IsNullOrEmpty(book_price_txt.Text) || CheckNumeric(book_price_txt.Text))
{
MessageBox.Show("가격을 입력해주세요.");
book_price_txt.Focus();
}
else if (string.IsNullOrEmpty(book_company_txt.Text))
{
MessageBox.Show("출판사를 입력해주세요.");
book_company_txt.Focus();
}
else if (string.IsNullOrEmpty(book_date_txt.Text) || CheckDatePattern(book_date_txt.Text))
{
MessageBox.Show("출판날짜를 입력해주세요.(2021.01.01)");
book_date_txt.Focus();
}
else if (string.IsNullOrEmpty(book_pages_txt.Text) || CheckNumeric(book_pages_txt.Text))
{
MessageBox.Show("총 페이지 수를 입력해주세요.");
book_pages_txt.Focus();
}
else
{
check = true;
}
return check;
}
// 출판일 확인 정규식
private bool CheckDatePattern(string date)
{
string pattern = @"^(1[0-9][0-9][0-9]|20\d{2})\.(0[0-9]|1[0-2])\.(0[1-9]|[1-2][0-9]|3[0-1])$";
if (!Regex.IsMatch(date, pattern))
{
return true;
}
else
{
return false;
}
}
// 숫자인지 확인
private bool CheckNumeric(string value)
{
int result = -1;
if (Int32.TryParse(value, out result))
{
if (result > 0)
{
return false;
}
}
return true;
}
// 책 이미지 불러오기 버튼
private void book_image_btn_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "이미지 불러오기";
openFileDialog.FileName = "";
openFileDialog.Filter = "Image files (*.jpg, *.jpeg, *.jpe, *.jfif, *.png) | *.jpg; *.jpeg; *.jpe; *.jfif; *.png";
book_image_txt.Text = string.Empty;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
book_image_txt.Text = openFileDialog.FileName;
pictureBox1.Image = new Bitmap(openFileDialog.FileName);
pictureBox1.SizeMode = PictureBoxSizeMode.Zoom;
}
}
// 이미지 저장
private string SaveBookImageFIle()
{
string dir = @"C:\Users\jjh\Desktop\mms\BookManagerForm\BookImages";
Image image = Image.FromFile(book_image_txt.Text);
bool bExist = true;
int fileCount = 0;
string fileName = string.Empty;
string dirMapPath = string.Empty;
if (image != null)
{
fileName = book_image_txt.Text.Split('\\').Last();
string strName = fileName.Substring(0, fileName.LastIndexOf("."));
string strExt = fileName.Substring(fileName.LastIndexOf("."));
while (bExist)
{
dirMapPath = dir;
string pathCombine = System.IO.Path.Combine(dirMapPath, fileName);
if (System.IO.File.Exists(pathCombine))
{
fileCount++;
fileName = $"{strName}({fileCount}){strExt}";
}
else
{
bExist = false;
}
}
image.Save($@"{dir}\{fileName}");
MessageBox.Show("이미지 저장이 되었습니다");
}
return $@"{dir}\{fileName}";
}
}
}
아직까지는 코린이!