/**
* 还书
*/
private void btnGiveBack_Click(object sender, EventArgs e)
{
string book_id = "";
string id = "";
try
{
id = gridBook.SelectedRows[0].Cells[0].Value.ToString();
string give_time = gridBook.SelectedRows[0].Cells[4].Value.ToString();
Console.WriteLine(give_time);
if (give_time.Length != 0)
{
DialogResult box = MessageBox.Show("本书已还，不可重复还~", "Tip", MessageBoxButtons.OK);
return;
}
//book_id = gridBook.SelectedRows[0].Cells[1].Value.ToString();
}catch (Exception ex)
{
DialogResult box = MessageBox.Show("没有选中任何书籍！", "Tip", MessageBoxButtons.OK);
return;
}
DialogResult dr = MessageBox.Show("确定要归还<<" + gridBook.SelectedRows[0].Cells[1].Value.ToString() + ">>这本书？", "Tip", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
string updateUserCmd = "update user set num=num+1 where id=" + user_id;
string searchBook = "select book_id from user_book where id=" + id;
MySqlDataReader reader = MysqlUtil.search(searchBook);
if (reader.Read())
{
book_id = reader[0].ToString();
}
else
{
DialogResult d = MessageBox.Show("发生未知错误", "Tip", MessageBoxButtons.OK);
return;
}
string updateBookCmd = "update book set ramined=ramined+1 where id=" + book_id;
string updateUserBookCmd = "update user_book set give_time = '{0}' where id=" + id;
DateTime give_time = DateTime.Now;
updateUserBookCmd = string.Format(updateUserBookCmd, give_time);
Console.WriteLine(updateBookCmd);
Console.WriteLine(updateUserCmd);
Console.WriteLine(updateUserBookCmd);
//修改数据库
if (MysqlUtil.operation(updateUserCmd) != -1 && MysqlUtil.operation(updateBookCmd) != -1 && MysqlUtil.operation(updateUserBookCmd) != -1)
{
MessageBox.Show("还书成功", "Tip", MessageBoxButtons.OK);
this.Reload();
}
}
}
/**
* 根据书名搜索书籍
*/
private void FillList(string title, string author)
{
MySqlConnection conn = MysqlUtil.createConn();
string sqlcmd = "";
try
{
if (author.Length != 0)
{
sqlcmd = "select * from book where title like '%" + title + "%' and author like '%" + author + "%'";
}
else
{
sqlcmd = "select * from book where title like '%" + title + "%'";
}
//加载图书数据
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(sqlcmd, conn);
DataSet dataset = new DataSet();
//使用SqlDataAdapter对象adapter将查新结果填充到DataSet对象ds中
adapter.Fill(dataset);
//设置DataSource属性
gridBook.DataSource = dataset.Tables[0];
//设置表头
gridBook.Columns[0].HeaderText = "id";
gridBook.Columns[1].HeaderText = "书名";
gridBook.Columns[2].HeaderText = "作者";
gridBook.Columns[3].HeaderText = "出版社";
gridBook.Columns[4].HeaderText = "馆藏";
gridBook.Columns[5].HeaderText = "剩余";
gridBook.Columns[6].HeaderText = "位置";
gridBook.ReadOnly = true;
//不允许添加行
gridBook.AllowUserToAddRows = false;
//背景为白色
gridBook.BackgroundColor = Color.White;
//只允许选中单行
gridBook.MultiSelect = false;
//整行选中
gridBook.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误", "Tip", MessageBoxButtons.OK);
Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
private void btnChange_Click(object sender, EventArgs e)
{
string sqlcmd = "update book set title='{0}',author='{1}',press='{2}',num='{3}',ramined='{4}',place='{5}' where id='{6}'";
sqlcmd = string.Format(sqlcmd, txtName.Text.ToString(), txtAuthor.Text.ToString(), txtPress.Text.ToString(), txtNum.Text.ToString(), txtRemained.Text.ToString()
, txtPlace.Text.ToString(), id);
Console.WriteLine(sqlcmd);
if (MysqlUtil.operation(sqlcmd) == -1)
{
MessageBox.Show("更新时发生了未知错误，请重试", "error", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("修改成功");
this.DialogResult = DialogResult.OK;
this.Close();
superForm.Reload();
}
}
/*
* 登录时检测用户合法性
*/
private void btnLogin(object sender, EventArgs e)
{
string id = txtUserId.Text;
string password = txtPassword.Text;
int identity = radioAdmin.Checked ? 0 : 1;
Console.WriteLine(id + " " + password + "" + identity);
string sqlcmd = "select * from user where id=" + id + " and password="******" and super=" + identity;
Console.WriteLine(sqlcmd);
MySqlDataReader dr = MysqlUtil.search(sqlcmd);
//用户合法时的事件
if (dr.Read())
{
//Console.WriteLine("success");
//Console.WriteLine(String.Format("{0}{1}", dr[0],dr[1]));
var frm = new IndexForm(String.Format("{0}", dr[0]), String.Format("{0}", dr[1]), this, identity);
this.Hide();
frm.Show();
/* if(identity == 1)
* {
* //将id和姓名传递给下一个窗口
* var frm = new IndexForm(String.Format("{0}", dr[0]),String.Format("{0}", dr[1]), this);
* this.Hide();
* frm.Show();
* }
* else
* {
* //将id和姓名传递给下一个窗口
* var frm = new AdminForm(String.Format("{0}", dr[0]), String.Format("{0}", dr[1]), this);
* this.Hide();
* frm.Show();
* }*/
}
else
{
MessageBox.Show("用户名或密码错误，请重新输入!", "Tip", MessageBoxButtons.OK);
}
}
private void btnDel_Click(object sender, EventArgs e)
{
DialogResult res = MessageBox.Show("确定删除此条记录？", "删除", MessageBoxButtons.YesNo);
if (res == DialogResult.Yes)
{
string id = gridBook.SelectedRows[0].Cells[0].Value.ToString();
string sqlcmd = "delete from book where id=" + id;
Console.WriteLine(sqlcmd);
if (MysqlUtil.operation(sqlcmd) == -1)
{
MessageBox.Show("删除失败，请重试!", "error", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("删除成功!", "Tip", MessageBoxButtons.OK);
this.Reload();
}
}
}
private void borrow_Click(object sender, EventArgs e)
{
string book_id = "";
string ramined = "";
int remained = 0;
try
{
book_id = gridBook.SelectedRows[0].Cells[0].Value.ToString();
ramined = gridBook.SelectedRows[0].Cells[5].Value.ToString();
remained = int.Parse(ramined);
}catch (Exception ex)
{
DialogResult dr = MessageBox.Show("没有选中任何书籍！", "Tip", MessageBoxButtons.OK);
return;
}
if (remained == 0)
{
DialogResult dr = MessageBox.Show("余量不足，暂时无法借阅！", "Tip", MessageBoxButtons.OK);
}
else
{
DialogResult dr = MessageBox.Show("确定要借阅<<" + gridBook.SelectedRows[0].Cells[1].Value.ToString() + ">>这本书？", "Tip", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
string updateUserCmd = "update user set num=num-1 where id=" + id;
string updateBookCmd = "update book set ramined=ramined-1 where id=" + book_id;
string insertCmd = "INSERT INTO user_book (user_id, book_id, borrow_time, revert_time) VALUES ('{0}', '{1}', '{2}', '{3}')";
DateTime borrow_time = DateTime.Now;
DateTime revert_time = borrow_time.AddMonths(1);
insertCmd = string.Format(insertCmd, id, book_id, borrow_time, revert_time);
//修改数据库
if (MysqlUtil.operation(updateUserCmd) != -1 && MysqlUtil.operation(updateBookCmd) != -1 && MysqlUtil.operation(insertCmd) != -1)
{
MessageBox.Show("借阅成功", "Tip", MessageBoxButtons.OK);
this.Reload();
}
}
}
}
private void btnChange_Click(object sender, EventArgs e)
{
string title = txtName.Text.ToString();
string author = txtAuthor.Text.ToString();
string press = txtPress.Text.ToString();
string num = txtNum.Text.ToString();
string remained = txtRemained.Text.ToString();
string place = txtPlace.Text.ToString();
string sqlcmd = "INSERT INTO book (title, author, press, num, ramined, place) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')";
sqlcmd = string.Format(sqlcmd, title, author, press, num, remained, place);
Console.WriteLine(sqlcmd);
if (MysqlUtil.operation(sqlcmd) == -1)
{
MessageBox.Show("插入时发生了未知错误，请重试", "error", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("插入成功");
this.DialogResult = DialogResult.OK;
this.Close();
superForm.Reload();
}
}
private void FillList()
{
gridBook.ColumnCount = 6;
gridBook.Columns[0].Name = "id";
gridBook.Columns[1].Name = "借书人";
gridBook.Columns[2].Name = "书名";
gridBook.Columns[3].Name = "借书日期";
gridBook.Columns[4].Name = "应还日期";
gridBook.Columns[5].Name = "还书日期";
gridBook.ReadOnly = true;
//不允许添加行
gridBook.AllowUserToAddRows = false;
//背景为白色
gridBook.BackgroundColor = Color.White;
//只允许选中单行
gridBook.MultiSelect = false;
//整行选中
gridBook.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
MySqlConnection conn = MysqlUtil.createConn();
try
{
//加载图书数据
string sqlcmd = "select * from user_book";
Console.WriteLine(sqlcmd);
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(sqlcmd, conn);
DataSet dataset = new DataSet("User_Book");
//使用SqlDataAdapter对象adapter将查新结果填充到DataSet对象ds中
adapter.Fill(dataset);
conn.Close();
Console.WriteLine(dataset.Tables[0].Rows.Count);
// 填充表格
foreach (DataRow row in dataset.Tables[0].Rows)
{
string id = row["id"].ToString();
string bookId = row["book_id"].ToString();
string sqlcmd2 = "select title from book where id=" + bookId;
string sqlname = "select name from user where id=" + row["user_id"].ToString();
string book_name = "";
string user_name = "";
MySqlDataReader dr = MysqlUtil.search(sqlcmd2);
Console.WriteLine(row["book_id"].ToString());
if (!dr.Read())
{
MessageBox.Show("未知错误", "error", MessageBoxButtons.OK);
this.Close();
return;
}
else
{
book_name = dr[0].ToString();
Console.WriteLine(book_name);
}
MySqlDataReader namereader = MysqlUtil.search(sqlname);
if (!namereader.Read())
{
MessageBox.Show("未知错误", "error", MessageBoxButtons.OK);
this.Close();
return;
}
else
{
user_name = namereader[0].ToString();
}
string[] item = { id, user_name, book_name, row["borrow_time"].ToString(), row["revert_time"].ToString(), row["give_time"].ToString() };
gridBook.Rows.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("查询错误", "Tip", MessageBoxButtons.OK);
Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
/**
* 测试函数
*/
public static void test()
{
MySqlConnection conn = MysqlUtil.createConn();
Console.WriteLine("连接成功");
}