private void BindDateGrdView()
{
string sql = "select Book.ID,Book_name,Book_ISBN,Book_Price,Book_Publish,Book_Publist_Time,Book_Status,Book_category.Book_Category from [Book] join Book_category on Book.Book_Category_id=Book_category.ID";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
dataadapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder sqlcmdbuilder = new SqlCommandBuilder(dataadapter);
//dataadapter.InsertCommand = sqlcmdbuilder.GetInsertCommand();
//dataadapter.UpdateCommand = sqlcmdbuilder.GetUpdateCommand();
//dataadapter.DeleteCommand = sqlcmdbuilder.GetDeleteCommand();
Book = new DataSet();
dataadapter.Fill(Book, "Book");
this.dataGridView1.DataSource = Book.Tables["Book"];
DataGridViewRow t = this.dataGridView1.Rows[0];
//this.dataGridView1.ColumnHeadersDefaultCellStyle = dataGridView1.ColumnHeadersDefaultCellStyle;
this.dataGridView1.Columns[0].ReadOnly = true;
this.dataGridView1.Columns[1].HeaderText = "书名";
this.dataGridView1.Columns[2].HeaderText = "ISBN号";
this.dataGridView1.Columns[3].HeaderText = "价格";
this.dataGridView1.Columns[4].HeaderText = "出版社";
this.dataGridView1.Columns[5].HeaderText = "出版日期";
this.dataGridView1.Columns[6].HeaderText = "是否已借";
this.dataGridView1.Columns[7].HeaderText = "类目";
//this.dataGridView1.Columns[7].CellTemplate = new DataGridViewComboBoxCell();
conn.Close();
}
private void borrowbook_button_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow t in this.dataGridView1.SelectedRows)
{
if (t.Index != -1)
{
if ((bool)t.Cells[6].Value == false)
{
int id = (int)t.Cells[0].Value;
string updatesql = "update [Book] set Book_Status='true',User_id=@userid where ID=@ID";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
SqlCommand sqlcmd = new SqlCommand(updatesql, conn);
sqlcmd.Parameters.AddWithValue("@ID", id);
sqlcmd.Parameters.AddWithValue("userid", LoginedInfo.Userid);
sqlcmd.ExecuteNonQuery();
dataadapter.Update(Book.Tables[0]);
conn.Close();
}
else
{
MessageBox.Show("存在别人已经借阅的图书，请重新选择");
}
}
}
BindDateGrdView();
}
catch (ArgumentOutOfRangeException ex)
{
MessageBox.Show("请选择要借的书");
}
}
private void combox_SelectedIndexChanged(object sender, EventArgs e)
{
dataGridView1.CurrentCell.Value = combox.SelectedValue;
for (int j = 0; j < this.dataGridView1.RowCount - 1; j++)
{
int x = 0;
if (int.TryParse(this.dataGridView1.Rows[j].Cells[7].Value.ToString(), out x) == true)
{
string updatesql = "update [Book] set Book_name=@bookname,Book_ISBN=@bookisbn,Book_Price=@bookprice,Book_Publish=@bookpublish,Book_Publist_Time=@bookpublictime,Book_Category_id=@bookcategoryid where ID=@ID";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
SqlCommand sqlcmd = new SqlCommand(updatesql, conn);
sqlcmd.Parameters.AddWithValue("@ID", this.dataGridView1.Rows[j].Cells[0].Value);
sqlcmd.Parameters.AddWithValue("@bookname", this.dataGridView1.Rows[j].Cells[1].Value);
sqlcmd.Parameters.AddWithValue("@bookisbn", this.dataGridView1.Rows[j].Cells[2].Value);
sqlcmd.Parameters.AddWithValue("@bookprice", this.dataGridView1.Rows[j].Cells[3].Value);
sqlcmd.Parameters.AddWithValue("@bookpublish", this.dataGridView1.Rows[j].Cells[4].Value);
sqlcmd.Parameters.AddWithValue("@bookpublictime", this.dataGridView1.Rows[j].Cells[5].Value);
sqlcmd.Parameters.AddWithValue("@bookcategoryid", this.dataGridView1.Rows[j].Cells[7].Value);
sqlcmd.ExecuteNonQuery();
conn.Close();
}
}
MessageBox.Show("更新成功");
}
private void myrebookbutton_Click(object sender, EventArgs e)
{
string sql = "select ID,Book_name,Book_ISBN,Book_Price,Book_Publish,Book_Publist_Time from [Book] where User_id='" + LoginedInfo.Userid + "'";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
dataadapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder sqlcmdbuilder = new SqlCommandBuilder(dataadapter);
dataadapter.InsertCommand = sqlcmdbuilder.GetInsertCommand();
dataadapter.UpdateCommand = sqlcmdbuilder.GetUpdateCommand();
dataadapter.DeleteCommand = sqlcmdbuilder.GetDeleteCommand();
Book = new DataSet();
dataadapter.Fill(Book, "Book");
this.dataGridView1.DataSource = Book.Tables[0];
DataGridViewRow t = this.dataGridView1.Rows[0];
this.dataGridView1.Columns[0].ReadOnly = true;
this.dataGridView1.Columns[1].HeaderText = "书名";
this.dataGridView1.Columns[2].HeaderText = "ISBN号";
this.dataGridView1.Columns[3].HeaderText = "价格";
this.dataGridView1.Columns[4].HeaderText = "出版社";
this.dataGridView1.Columns[5].HeaderText = "出版日期";
conn.Close();
}
private void BindDateGrdView()
{
if (logintype == 0)
{
string sql = "select ID,Book_name,Book_ISBN,Book_Price,Book_Publish,Book_Publist_Time,Book_Status from [Book]";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
dataadapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder sqlcmdbuilder = new SqlCommandBuilder(dataadapter);
dataadapter.InsertCommand = sqlcmdbuilder.GetInsertCommand();
dataadapter.UpdateCommand = sqlcmdbuilder.GetUpdateCommand();
dataadapter.DeleteCommand = sqlcmdbuilder.GetDeleteCommand();
Book = new DataSet();
dataadapter.Fill(Book, "Book");
this.dataGridView2.DataSource = Book.Tables[0];
DataGridViewRow t = this.dataGridView2.Rows[0];
//this.dataGridView1.ColumnHeadersDefaultCellStyle = dataGridView1.ColumnHeadersDefaultCellStyle;
this.dataGridView2.Columns[0].ReadOnly = true;
this.dataGridView2.Columns[1].HeaderText = "书名";
this.dataGridView2.Columns[2].HeaderText = "ISBN号";
this.dataGridView2.Columns[3].HeaderText = "价格";
this.dataGridView2.Columns[4].HeaderText = "出版社";
this.dataGridView2.Columns[5].HeaderText = "出版日期";
this.dataGridView2.Columns[6].HeaderText = "是否已借";
conn.Close();
}
else
{
string sql = "select Book.ID,Book_name,Book_ISBN,Book_Price,Book_Publish,Book_Publist_Time,Book_Status,[User].Username from [Book] join [User] on [Book].User_id=[User].ID";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
dataadapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder sqlcmdbuilder = new SqlCommandBuilder(dataadapter);
//dataadapter.InsertCommand = sqlcmdbuilder.GetInsertCommand();
//dataadapter.UpdateCommand = sqlcmdbuilder.GetUpdateCommand();
//dataadapter.DeleteCommand = sqlcmdbuilder.GetDeleteCommand();
Book = new DataSet();
dataadapter.Fill(Book, "Book");
this.dataGridView2.DataSource = Book.Tables[0];
DataGridViewRow t = this.dataGridView2.Rows[0];
//this.dataGridView1.ColumnHeadersDefaultCellStyle = dataGridView1.ColumnHeadersDefaultCellStyle;
this.dataGridView2.Columns[0].ReadOnly = true;
this.dataGridView2.Columns[1].HeaderText = "书名";
this.dataGridView2.Columns[2].HeaderText = "ISBN号";
this.dataGridView2.Columns[3].HeaderText = "价格";
this.dataGridView2.Columns[4].HeaderText = "出版社";
this.dataGridView2.Columns[5].HeaderText = "出版日期";
this.dataGridView2.Columns[6].HeaderText = "是否已借";
this.dataGridView2.Columns[7].HeaderText = "借书用户";
conn.Close();
}
}
private void dataGridView1_CurrentCellChanged(object sender, EventArgs e)
{
try
{
int col = this.dataGridView1.CurrentCell.ColumnIndex;
if (col == 7)
{
DataSet ds;
//绘制combox
System.Drawing.Rectangle rect = dataGridView1.GetCellDisplayRectangle(dataGridView1.CurrentCell.ColumnIndex, dataGridView1.CurrentCell.RowIndex, false);
combox = new ComboBox();
combox.DropDownStyle = ComboBoxStyle.DropDownList;
string sql = "select * from Book_category";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
SqlDataAdapter sqladapter = new SqlDataAdapter(sql, conn);
ds = new DataSet();
this.combox.SelectedIndexChanged -= new System.EventHandler(this.combox_SelectedIndexChanged);
sqladapter.Fill(ds, "Book_category");
combox.DataSource = ds.Tables["Book_category"];
//需要显示的字段名这是text值
combox.DisplayMember = ds.Tables["Book_category"].Columns[1].ColumnName.ToString();
//选项ID的字段名这是value值
combox.ValueMember = ds.Tables["Book_category"].Columns[0].ColumnName.ToString();
combox.Left = rect.Left;
combox.Top = rect.Top;
combox.Width = rect.Width;
combox.Height = rect.Height;
combox.Visible = true;
this.dataGridView1.Controls.Add(combox);
combox.SelectedIndex = -1;
//combox.SelectedIndex = combox.Items.IndexOf("计算机");
dataGridView1.CurrentCell.Value = combox.SelectedValue;
this.combox.SelectedIndexChanged += new System.EventHandler(this.combox_SelectedIndexChanged);
}
else
{
combox.Visible = false;
}
}
catch
{ }
}
private void BindDateGrdView()
{
string sql = "select * from Book_Category";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
dataadapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder sqlcmdbuilder = new SqlCommandBuilder(dataadapter);
dataadapter.InsertCommand = sqlcmdbuilder.GetInsertCommand();
dataadapter.UpdateCommand = sqlcmdbuilder.GetUpdateCommand();
dataadapter.DeleteCommand = sqlcmdbuilder.GetDeleteCommand();
Book_category = new DataSet();
dataadapter.Fill(Book_category, "Book_category");
this.dataGridView1.DataSource = Book_category.Tables[0];
this.dataGridView1.Columns[0].ReadOnly = true;
this.dataGridView1.Columns[1].HeaderText = "类名";
conn.Close();
}
public static bool Login_charge(string user, string password, int logintype)
{
string sql, sql1;
if (logintype == 0)
{
sql = "select * from Admin where Username=@user and Password=@password";
sql1 = "update Admin set LoginTime=@time where Username=@user";
}
else
{
//屏蔽掉和系统一样的关键词
sql = "select * from [User] where Username=@user and Password=@password";
sql1 = "update [User] set LoginTime=@time where Username=@user";
}
//获取数据库连接字符串
string connectionstring = Connectionsql.getconstring();
SqlConnection conn = new SqlConnection(connectionstring);
SqlCommand sqlcmd = new SqlCommand(sql, conn);
conn.Open();
sqlcmd.Parameters.AddWithValue("@user", user);
sqlcmd.Parameters.AddWithValue("@password", password);
//如过查询结果没有,返回空Convert.ToInt32不会导致异常
if (Convert.ToInt32(sqlcmd.ExecuteScalar()) > 0)
{
string sql3 = "select ID from [User] where Username=@username";
sqlcmd.CommandText = sql3;
sqlcmd.Parameters.AddWithValue("@username", LoginedInfo.Loginname);
LoginedInfo.Userid = Convert.ToInt32(sqlcmd.ExecuteScalar());
//LoginedInfo.Userid = Convert.ToInt32(sqlcmd.ExecuteScalar());
sqlcmd.CommandText = sql1;
sqlcmd.Parameters.AddWithValue("@time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqlcmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
private void adduser_butto_Click(object sender, EventArgs e)
{
if (this.username_textBox.Text != "")
{
if (this.password_textBox.Text != "")
{
//添加用户名和密码
string username, password;
username = this.username_textBox.Text;
password = this.password_textBox.Text;
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
string sql = "insert into [User](Username,Password) values(@user,@password)";
conn.Open();
SqlCommand sqlcmd = new SqlCommand(sql, conn);
sqlcmd.Parameters.AddWithValue("@user", username);
sqlcmd.Parameters.AddWithValue("@password", password);
if (sqlcmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("添加成功");
//重新显示数据
BindDateGrdView();
}
else
{
MessageBox.Show("添加失败");
}
}
else
{
MessageBox.Show("请输入读者密码");
}
}
else if (this.username_textBox.Text == "" && this.password_textBox.Text != "")
{
MessageBox.Show("请输入读者名");
}
else
{
MessageBox.Show("请输入要添加的读者名和密码");
}
}
private void BindDateGrdView()
{
string sql = "select * from [User]";
SqlConnection conn = new SqlConnection(Connectionsql.getconstring());
conn.Open();
dataadapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder sqlcmdbuilder = new SqlCommandBuilder(dataadapter);
dataadapter.InsertCommand = sqlcmdbuilder.GetInsertCommand();
dataadapter.UpdateCommand = sqlcmdbuilder.GetUpdateCommand();
dataadapter.DeleteCommand = sqlcmdbuilder.GetDeleteCommand();
User = new DataSet();
dataadapter.Fill(User, "User");
this.dataGridView1.DataSource = User.Tables[0];
this.dataGridView1.Columns[0].ReadOnly = true;
this.dataGridView1.Columns[1].HeaderText = "读者名";
this.dataGridView1.Columns[2].HeaderText = "密码";
this.dataGridView1.Columns[3].HeaderText = "登陆时间";
conn.Close();
}