//加载窗口
private void A_returnBook_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SqlCommand selectCmd = new SqlCommand("select * from returntable", conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
string RID, BID, Bname;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
RID = datareader["RID"].ToString();
Bname = datareader["Bname"].ToString();
BID = datareader["BID"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = RID.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = BID.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Bname.Trim();
}
}
catch
{
MessageBox.Show("操作错误！");
}
}
//加载窗口函数
private void R_historyRecord_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string RID = LogWindow.logname;
SqlCommand selectCmd = new SqlCommand("select BID,BorrowDate,ReturnDate,RealDate,Note from Borrow where RID='" + RID + "'", conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
string Bid, Bname, BorrowDate, ReturnDate, RealDate, Note;
int rowindex = 0;
this.dataGridView1.Rows.Clear();
while (datareader.Read())
{
Bid = datareader["BID"].ToString();
BorrowDate = datareader["BorrowDate"].ToString();
ReturnDate = datareader["ReturnDate"].ToString();
RealDate = datareader["RealDate"].ToString();
Note = datareader["Note"].ToString();
rowindex = this.dataGridView1.Rows.Add();
//trim函数同于去除字符串两头的空格
this.dataGridView1.Rows[rowindex].Cells[0].Value = Bid.Trim();
this.dataGridView1.Rows[rowindex].Cells[2].Value = BorrowDate.Trim();
this.dataGridView1.Rows[rowindex].Cells[3].Value = ReturnDate.Trim();
this.dataGridView1.Rows[rowindex].Cells[4].Value = RealDate.Trim();
this.dataGridView1.Rows[rowindex].Cells[5].Value = Note.Trim();
}
datareader.Close();
SQLbase.close(selectCmd);
int n = this.dataGridView1.RowCount;
for (int i = 0; i < n; i++)
{
Bid = this.dataGridView1.Rows[i].Cells[0].Value.ToString();
Bname = select(conn, Bid);
this.dataGridView1.Rows[i].Cells[1].Value = Bname.Trim();
}
}
catch
{
MessageBox.Show("操作错误！");
}
SQLbase.close(conn);
}
//查询按钮
private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
this.datagridview.Rows.Clear(); //清空表格
//打开连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
string selectStr = "select * from Borrow";
//按读者号查询
if (comboBox_QueryStyle.SelectedIndex == 0)
{
string rid = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from Borrow where RID=" + rid;
}
//按书号查询
else if (comboBox_QueryStyle.SelectedIndex == 1)
{
string bid = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from Borrow where BID=" + bid;
}
//没有选择下拉框，则查询全部
SqlCommand selectCmd = new SqlCommand(selectStr, conn);
SqlDataReader datareader = selectCmd.ExecuteReader();
string BID, Bname, RID, Rname, BorrowDate, RealDate, Note;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
RID = datareader["RID"].ToString();
BID = datareader["BID"].ToString();
BorrowDate = datareader["BorrowDate"].ToString();
RealDate = datareader["RealDate"].ToString();
Note = datareader["Note"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = BID.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = RID.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = BorrowDate.Trim();
this.datagridview.Rows[rowindex].Cells[5].Value = RealDate.Trim();
this.datagridview.Rows[rowindex].Cells[6].Value = Note.Trim();
}
datareader.Close();
SQLbase.close(selectCmd);
int n = this.datagridview.RowCount;
for (int i = 0; i < n; i++)
{
//填充书名
BID = this.datagridview.Rows[i].Cells[0].Value.ToString();
Bname = selectBook(conn, BID);
this.datagridview.Rows[i].Cells[1].Value = Bname.Trim();
//填充作者名
RID = this.datagridview.Rows[i].Cells[2].Value.ToString();
Rname = selectReader(conn, RID);
this.datagridview.Rows[i].Cells[3].Value = Rname.Trim();
}
}
catch
{
MessageBox.Show("查无此记录！");
}
SQLbase.close(conn);
}
//图书查询
private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
this.datagridview.Rows.Clear();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
//打开连接
conn.Open();
string selectStr = "select * from books";
//按书号查询
if (comboBox_QueryStyle.SelectedIndex == 0)
{
string bid = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where BID=" + bid;
}
//按书名查询
else if (comboBox_QueryStyle.SelectedIndex == 1)
{
string bname = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where Bname= '" + bname + "'";
}
//按作者查询
else if (comboBox_QueryStyle.SelectedIndex == 2)
{
string bauthor = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where Bauthor= '" + bauthor + "'";
}
//按出版社查询
else if (comboBox_QueryStyle.SelectedIndex == 3)
{
string bpublisher = txt_query.Text.ToString();
//建立查询命令
selectStr = "select * from books where Bpublisher= '" + bpublisher + "'";
}
//没有选择下拉框，则查询全部
SqlCommand SelectCmd = new SqlCommand(selectStr, conn);
SqlDataReader datareader = SelectCmd.ExecuteReader();
string Bid, Bname, Bauthor, Bpublisher, Btype, Bstate;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader.Read())
{
Bid = datareader["BID"].ToString();
Bname = datareader["Bname"].ToString();
Bauthor = datareader["Bauthor"].ToString();
Bpublisher = datareader["Bpublisher"].ToString();
Btype = datareader["Btype"].ToString();
Bstate = datareader["Bstate"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = Bid.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = Bname.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Bauthor.Trim();
this.datagridview.Rows[rowindex].Cells[3].Value = Bpublisher.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = Btype.Trim();
this.datagridview.Rows[rowindex].Cells[5].Value = Bstate.Trim();
}
}
catch
{
MessageBox.Show("查无此书！");
}
SQLbase.close(conn);
}
//窗口加载函数
private void R_BooksReturn_Load(object sender, EventArgs e)
{
SqlConnection conn = SQLbase.getConn();
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
String RID = LogWindow.logname;
//查询书籍信息
string selectStr = "select BID,Bname,Bauthor,Bpublisher,Btype from Books where BID IN(select BID from Borrow where RID='" + RID + "'and Note in('借阅中','已续借','待审核')) ORDER BY BID ASC";
SqlCommand selectCmd1 = conn.CreateCommand();
selectCmd1.CommandText = selectStr;
SqlDataReader datareader1 = selectCmd1.ExecuteReader();
string Bid, Bname, Bauthor, Bpublisher, Btype;
int rowindex = 0;
this.datagridview.Rows.Clear();
while (datareader1.Read())
{
Bid = datareader1["BID"].ToString();
Bname = datareader1["Bname"].ToString();
Bauthor = datareader1["Bauthor"].ToString();
Bpublisher = datareader1["Bpublisher"].ToString();
Btype = datareader1["Btype"].ToString();
rowindex = this.datagridview.Rows.Add();
//trim函数同于去除字符串两头的空格
this.datagridview.Rows[rowindex].Cells[0].Value = Bid.Trim();
this.datagridview.Rows[rowindex].Cells[1].Value = Bname.Trim();
this.datagridview.Rows[rowindex].Cells[2].Value = Bauthor.Trim();
this.datagridview.Rows[rowindex].Cells[3].Value = Bpublisher.Trim();
this.datagridview.Rows[rowindex].Cells[4].Value = Btype.Trim();
}
SQLbase.close(selectCmd1);
//必须关闭当前的dataReader才能继续
datareader1.Close();
//查询应还日期和借阅状态
string selectStr2 = "select BID,ReturnDate,Note from Borrow where RID='" + RID + "'order by BID ASC";
SqlCommand selectCmd2 = conn.CreateCommand();
selectCmd2.CommandText = selectStr2;
SqlDataReader datareader2 = selectCmd2.ExecuteReader();
string return_date;
string note;
int index = 0;
while (datareader2.Read())
{
if (index < this.datagridview.RowCount)
{
return_date = datareader2["ReturnDate"].ToString();
note = datareader2["Note"].ToString();
this.datagridview.Rows[index].Cells[5].Value = return_date.Trim();
this.datagridview.Rows[index].Cells[6].Value = note.Trim();
index++;
}
}
datareader2.Close();
SQLbase.close(selectCmd2);
}
catch
{
MessageBox.Show("操作异常", "错误提示");
}
SQLbase.close(conn);
}