C#
미니 프로젝트 - 도서대출프로그램 수정
구르는 돌멩이
2024. 2. 26. 21:49
최근에 MYSQL을 사용하게 되면서 기존에 만들었던 것도 수정을 해 봤습니다.
public LogIn_Fm()
{
InitializeComponent();
this.FormBorderStyle = FormBorderStyle.FixedSingle;
this.WindowState = FormWindowState.Normal;
this.MaximizedBounds = Screen.FromHandle(this.Handle).WorkingArea;
this.MaximizeBox = false;
}
private void LogIn_Bt_Click(object sender, EventArgs e)
{
try
{
string LOGIN = "Serve = 내 sql";
using (MySqlConnection LOGINCON = new MySqlConnection(LOGIN))
{
LOGINCON.Open();
string id = Id_Tb.Text;
string password = Password_Tb.Text;
if (string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(password))
{
MessageBox.Show("아이디와 비밀번호를 모두 입력하세요.");
return;
}
string query = $"SELECT name FROM user WHERE id='{id}' AND password='{password}'";
using (MySqlCommand LOGINCMD = new MySqlCommand(query, LOGINCON))
{
object result = LOGINCMD.ExecuteScalar();
using (MySqlDataReader reader = LOGINCMD.ExecuteReader())
{
if (result != null)
{
string name = result.ToString();
MessageBox.Show($"{name}님, 환영합니다!");
Main_Fm fm = new Main_Fm(id, name);
fm.ShowDialog();
}
else
{
MessageBox.Show("아이디 또는 비밀번호가 잘못되었습니다.");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show($"오류 발생: {ex.Message}");
}
}
로그인 기능은 제가 user 테이블에 넣어둔 id, password 가 일치하면 name이 뜨면서 다음 화면으로 넘어가는 기능만 있습니다.
회원가입은 넣지 않았습니다.
private void BookList_Gr_Show()
{
string List = "Server =내 sql";
try
{
using (MySqlConnection connection = new MySqlConnection(List))
{
connection.Open();
string Listquery = "SELECT * FROM booklist";
MySqlCommand command = new MySqlCommand(Listquery, connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataTable AllListDt = new DataTable();
adapter.Fill(AllListDt);
BookList_Gr.DataSource = AllListDt;
}
}
catch (Exception ex)
{
MessageBox.Show($"오류 발생: {ex.Message}");
}
}
private void Rentallist_Gr_Show()
{
string List = "Server = 내 sql";
try
{
using (MySqlConnection connection = new MySqlConnection(List))
{
connection.Open();
string RentalListquery = $"SELECT bookname, code, Author, rental_date, return_date, count, publisher FROM rental_list WHERE user_id = @userId";
MySqlCommand command = new MySqlCommand(RentalListquery, connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
command.Parameters.AddWithValue("@userId", loggedInUserId);
DataTable RentalDt = new DataTable();
adapter.Fill(RentalDt);
Rentallist_Gr.DataSource = RentalDt;
}
}
catch (Exception ex)
{
MessageBox.Show($"오류 발생: {ex.Message}");
}
}Booklist_Gr는 도서 목록 그리드 뷰, Rentallist_Gr는 대출 내역 그리드 뷰입니다.
로그인 한 id가 rental_list 테이블의 user_id 와 일치하는 행만 가져오기
private void Search_Bt_Click(object sender, EventArgs e)
{
string List = "Server = 내 sql";
string bookName = BookName_Tb.Text.Trim();
if (string.IsNullOrEmpty(bookName))
{
MessageBox.Show("도서명을 입력해주세요.", "도서 검색");
return;
}
try
{
using (MySqlConnection connection = new MySqlConnection(List))
{
connection.Open();
string searchQuery = $"SELECT * FROM booklist WHERE BookName LIKE '%{bookName}%'";
using (MySqlCommand cmd = new MySqlCommand(searchQuery, connection))
{
DataTable SearchDt = new DataTable();
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
adapter.Fill(SearchDt);
}
// 검색 결과 출력
if (SearchDt.Rows.Count > 0)
{
BookList_Gr.DataSource = SearchDt;
}
else
{
MessageBox.Show("검색 결과가 없습니다.", "도서 검색");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show($"오류 발생: {ex.Message}", "오류");
}
}검색 버튼
private void List_Bt_Click(object sender, EventArgs e)
{
BookList_Gr_Show();
}
도서 목록 버튼은 간단하고 귀여운 코드
private void Rental_Bt_Click(object sender, EventArgs e)
{
if (BookList_Gr.SelectedCells.Count > 0)
{
int selectedRowIndex = BookList_Gr.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = BookList_Gr.Rows[selectedRowIndex];
string availableQuantityAsString = selectedRow.Cells["count"].Value.ToString();
if (int.TryParse(availableQuantityAsString, out int availableQuantity) && availableQuantity > 0)
{
if (availableQuantity > 0)
{
selectedRow.Cells["count"].Value = (availableQuantity - 1).ToString();
string bookName = selectedRow.Cells["BookName"].Value.ToString();
string code = selectedRow.Cells["BookCode"].Value.ToString();
string author = selectedRow.Cells["name"].Value.ToString();
DateTime rentalDate = DateTime.Today;
DateTime returnDate = rentalDate.AddDays(14); // 대여 날짜로부터 2주 후
string connectionString = "Server = 내 서버 스키마!!";
bool bookAlreadyExists = false;
foreach (DataGridViewRow row in Rentallist_Gr.Rows)
{
if (row.Cells["도서명"].Value.ToString() == bookName)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
int rentedQuantity = int.Parse(row.Cells["rental_count"].Value.ToString());
string increaseQuery = $"UPDATE rental_list SET count = count + 1 WHERE bookname = @bookName";
MySqlCommand command = new MySqlCommand(increaseQuery, connection);
command.Parameters.AddWithValue("@bookName", bookName);
try
{
connection.Open();
command.ExecuteNonQuery();
bookAlreadyExists = true;
UpdateBookQuantity(bookName);
MessageBox.Show($"{bookName} 대출");
}
catch (Exception ex)
{
MessageBox.Show($"수량 증가 오류: {ex.Message}");
}
}
break;
}
}
if (!bookAlreadyExists)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string insertQuery = $"INSERT INTO rental_list (user_id, bookname, code, Author, rental_date, return_date, count, publisher) " +
$"VALUES (@userId, @bookName, @code, @author, @rentalDate, @returnDate, @count, @publisher)";
MySqlCommand command = new MySqlCommand(insertQuery, connection);
command.Parameters.AddWithValue("@userId", loggedInUserId);
command.Parameters.AddWithValue("@bookName", bookName);
command.Parameters.AddWithValue("@code", code);
command.Parameters.AddWithValue("@author", author);
command.Parameters.AddWithValue("@rentalDate", rentalDate);
command.Parameters.AddWithValue("@returnDate", returnDate);
command.Parameters.AddWithValue("@count", 1); // 대여한 책 1권
command.Parameters.AddWithValue("@publisher", selectedRow.Cells["publisher"].Value.ToString());
try
{
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
MessageBox.Show($"{bookName} 대출");
UpdateBookQuantity(bookName);
}
else
{
MessageBox.Show("대출 실패!");
}
}
catch (Exception ex)
{
MessageBox.Show($"대출 오류: {ex.Message}");
}
}
}
}
}
else
{
MessageBox.Show("대여 불가능, 현재 남아있는 재고가 없습니다.");
}
Rentallist_Gr_Show();
}
}
private void UpdateBookQuantity(string bookName)
{
string connectionString = "Server = 내 서버 스키마!!";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string updateQuery = $"UPDATE booklist SET count = count - 1 WHERE BookName = @bookName";
MySqlCommand command = new MySqlCommand(updateQuery, connection);
command.Parameters.AddWithValue("@bookName", bookName);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show($"수량 감소 오류: {ex.Message}");
}
}
}대출 코드
private void Return_Bt_Click(object sender, EventArgs e)
{
if (Rentallist_Gr.SelectedCells.Count > 0)
{
int selectedRowIndex = Rentallist_Gr.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = Rentallist_Gr.Rows[selectedRowIndex];
string RentalCount = selectedRow.Cells["rental_count"].Value.ToString();
string connectionString = "Server = 내 sql";
if (int.TryParse(RentalCount, out int ReturnQuantity) && ReturnQuantity > 0)
{
string bookName = selectedRow.Cells["도서명"].Value.ToString();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string ListUpdateQuery = $"UPDATE booklist SET count = count + 1 WHERE BookName = @bookName";
MySqlCommand command = new MySqlCommand(ListUpdateQuery, connection);
command.Parameters.AddWithValue("@bookName", bookName);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show($"수량 증가 오류: {ex.Message}");
}
}
if (ReturnQuantity == 1) // 수량이 1일 때만 행을 삭제합니다.
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string deleteQuery = "DELETE FROM rental_list WHERE bookname = @bookName";
using (MySqlCommand command = new MySqlCommand(deleteQuery, connection))
{
command.Parameters.AddWithValue("@bookName", bookName);
try
{
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
MessageBox.Show($"{bookName} 반납");
}
else
{
MessageBox.Show("반납오류");
}
}
catch (Exception ex)
{
MessageBox.Show($"반납오류: {ex.Message}");
}
}
}
}
else
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string decreaseQuery = $"UPDATE rental_list SET count = count - 1 WHERE bookname = @bookName";
MySqlCommand command = new MySqlCommand(decreaseQuery, connection);
command.Parameters.AddWithValue("@bookName", bookName);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show($"수량 감소 오류: {ex.Message}");
}
}
}
}
}
BookList_Gr_Show();
Rentallist_Gr_Show();
}반납 코드
본인 id로 대출한 내역만 확인 가능
뭔가 코드가 엉성하기도 하고, 허점이 있을 것 같습니다.
C#은 2주 배웠는데?
배웠다고 하기에도 애매하다. 거의 독학인데
잘해보려고 노력하는 중입니다.