public static string Searchtext; //用户输入的搜索词
private void BindData()
{
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
//创建一个SqlDataAdapter对象
SqlDataAdapter sda = new SqlDataAdapter("select B_ID,NAME,AUTHOR,PUBLISHER,IMAGES from BOOKS WHERE B_ID IN (SELECT B_ID FROM CUR_BORROW WHERE U_ID = '" + U_ID + "')", sqlConn);
//创建一个DataSet对象
DataSet ds = new DataSet();
//使用SqlDataAdapter对象的Fill方法填充DataSetS
sda.Fill(ds, "BOOKS");
//设置dataGridView1控件数据源
currentdataGridView1.DataSource = ds.Tables[0];
//select BOOKS.NAME,HIS_BORROW.BOR_TIME,HIS_BORROW.BACK_TIME,HIS_BORROW.U_ID FROM BOOKS inner join HIS_BORROW ON BOOKS.B_ID = HIS_BORROW.B_ID AND HIS_BORROW.U_ID = '201601';
//创建一个SqlDataAdapter对象
SqlDataAdapter sda1 = new SqlDataAdapter("select BOOKS.B_ID,BOOKS.NAME,BOOKS.AUTHOR,BOOKS.PUBLISHER,HIS_BORROW.BOR_TIME,HIS_BORROW.BACK_TIME FROM BOOKS inner join HIS_BORROW ON BOOKS.B_ID = HIS_BORROW.B_ID AND HIS_BORROW.U_ID = '" + U_ID + "'", sqlConn);
//创建一个DataSet对象
DataSet ds1 = new DataSet();
//使用SqlDataAdapter对象的Fill方法填充DataSetS
sda1.Fill(ds1, "BOOKS2");
//设置dataGridView1控件数据源
dataGridView1.DataSource = ds1.Tables[0];
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
//实例化SqlConnection变量sqlConn，连接数据库
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
SqlCommand cmd1 = new SqlCommand("select NAME from BOOK_TYPES WHERE ID =" + dataGridView1.SelectedCells[6].Value.ToString().Trim(), sqlConn);
SqlDataReader sdr = cmd1.ExecuteReader();
sdr.Read();
string Type = sdr["NAME"].ToString().Trim();
sqlConn.Close();
BOOKIDtextBox.Text = dataGridView1.SelectedCells[0].Value.ToString();
BOOKNAMEtextBox.Text = dataGridView1.SelectedCells[1].Value.ToString();
AUTHORtextBox.Text = dataGridView1.SelectedCells[2].Value.ToString();
PUBLISHERtextBox.Text = dataGridView1.SelectedCells[3].Value.ToString().Trim();
numtextBox.Text = dataGridView1.SelectedCells[4].Value.ToString().Trim();
dateTimePicker1.Text = dataGridView1.SelectedCells[5].Value.ToString().Trim();
TYPEcomboBox.Text = Type;
STORAGEtextBox.Text = dataGridView1.SelectedCells[7].Value.ToString().Trim();
tempfilepath = dataGridView1.SelectedCells[8].Value.ToString().Trim();
FileStream pFileStream = new FileStream(tempfilepath, FileMode.Open, FileAccess.Read);
pictureBox1.Image = Image.FromStream(pFileStream);
pFileStream.Close();
pFileStream.Dispose();
changebutton.Enabled = true;
deletebutton.Enabled = true;
// MessageBox.Show(dataGridView1.SelectedCells[5].Value.ToString().Trim());
}
private void deletebutton_Click(object sender, EventArgs e)
{
if (BOOKIDtextBox.Text == "")
{
MessageBox.Show("还没有图书被选中哦！无法进行操作");
}
else
{
//实例化SqlConnection变量sqlConn，连接数据库
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
string sql = "delete from books where B_ID='" + dataGridView1.SelectedCells[0].Value.ToString() + "'";
string sql1 = "delete from BOOK_COMMENT where BOOK_ID = '" + BOOKIDtextBox.Text + "'";
string sql2 = "delete from CUR_BORROW where B_ID = '" + BOOKIDtextBox.Text + "'";
string sql3 = "delete from HIS_BORROW where B_ID = '" + BOOKIDtextBox.Text + "'";
MessageBox.Show(dataGridView1.SelectedCells[0].Value.ToString() + sql);
SqlCommand cmd = new SqlCommand(sql1, sqlConn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(sql2, sqlConn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(sql3, sqlConn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(sql, sqlConn);
cmd.ExecuteNonQuery();
sqlConn.Close();
pictureBox1.Image = null;
FileInfo delete = new FileInfo(tempfilepath);
delete.Delete();
MessageBox.Show("删除成功！" + tempfilepath);
BindData();
}
}
private void lend_Click(object sender, EventArgs e)
{
if (idtextBox2.Text == "")
{
MessageBox.Show("还没有图书被选中哦！无法进行操作");
}
else
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
long timeStamp = (long)(DateTime.Now - startTime).TotalMilliseconds; // 相差毫秒数
//SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
//首先查询本书的借阅时间
SqlCommand cmd1 = new SqlCommand("select BOR_TIME from CUR_BORROW where U_ID ='" + U_ID + "' AND B_ID ='" + idtextBox2.Text + "'", sqlConn);
SqlDataReader sdr1 = cmd1.ExecuteReader();
sdr1.Read();
string BOR_TIME = sdr1["BOR_TIME"].ToString().Trim();
sqlConn.Close();
//将本书的信息插入到历史借阅表
string sql = "insert into HIS_BORROW(H_ID,BOR_TIME,BACK_TIME,U_ID,B_ID) values('" + timeStamp + "','" + BOR_TIME + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + U_ID + "','" + idtextBox2.Text + "')";
// MessageBox.Show(sql);
sqlConn.Open(); //打开连接
SqlCommand cmd = new SqlCommand(sql, sqlConn);
int a = cmd.ExecuteNonQuery(); //影响行数
sqlConn.Close();
//然后从当前借阅表中删除本条书的记录
string sql2 = "delete from CUR_BORROW where U_ID ='" + U_ID + "' AND B_ID ='" + idtextBox2.Text + "'";
// MessageBox.Show(sql2);
sqlConn.Open(); //打开连接
SqlCommand cmd2 = new SqlCommand(sql2, sqlConn);
int a2 = cmd2.ExecuteNonQuery(); //影响行数
sqlConn.Close();
//将用户评论存入数据库
if (richTextBox2.Text != "") //判断用户是否有输入评论，有才插入数据库
{
string sql3 = "insert into BOOK_COMMENT(BC_ID,CONTENT,TIME,BOOK_ID,USERID) values('" + timeStamp + "','" + richTextBox2.Text + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + idtextBox2.Text + "','" + U_ID + "')";
MessageBox.Show(sql3);
sqlConn.Open(); //打开连接
SqlCommand cmd3 = new SqlCommand(sql3, sqlConn);
int a3 = cmd3.ExecuteNonQuery(); //影响行数
}
MessageBox.Show("还书成功！");
BindData(); //更新数据
sqlConn.Close();
}
}
private void treeView2_AfterSelect(object sender, TreeViewEventArgs e)
{
//在AfterSelect事件中获取控件中选中节点显示的文本
// label1.Text = "当前选中的节点：" + e.Node.Text;
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
SqlCommand cmd = new SqlCommand("select B_ID,NAME,AUTHOR,PUBLISHER,NUMBER,PUB_TIME,TYPE,STORAGE,IMAGES from BOOKS where NAME ='" + e.Node.Text + "'", sqlConn);
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
if (sdr.HasRows)
{
idtextBox.Text = sdr["B_ID"].ToString().Trim();
nametextBox.Text = sdr["NAME"].ToString().Trim();
authortextBox.Text = sdr["AUTHOR"].ToString().Trim();
publishertextBox.Text = sdr["PUBLISHER"].ToString().Trim();
timetextBox.Text = sdr["PUB_TIME"].ToString().Trim();
storagetextBox.Text = sdr["STORAGE"].ToString().Trim();
NUMtextBox.Text = sdr["NUMBER"].ToString().Trim();
FileStream pFileStream = new FileStream(sdr["IMAGES"].ToString().Trim(), FileMode.Open, FileAccess.Read);
pictureBox1.Image = Image.FromStream(pFileStream);
pFileStream.Close();
pFileStream.Dispose();
}
sqlConn.Close();
//查询评论
//SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
sqlConn.Open(); //打开连接
string SQL = "select BOOK_COMMENT.CONTENT,BOOK_COMMENT.TIME,USERS.NAME from BOOK_COMMENT,USERS WHERE USERS.U_ID = BOOK_COMMENT.USERID AND BOOK_COMMENT.BOOK_ID = (SELECT B_ID FROM BOOKS WHERE NAME ='" + e.Node.Text.Trim() + "')";
//SQL = "select *from BOOK_COMMENT";
SqlCommand cmd4 = new SqlCommand(SQL, sqlConn);
// MessageBox.Show(SQL);
SqlDataReader sdr4 = cmd4.ExecuteReader();
string main = "";
commentrichTextBox.Text = "";
while (sdr4.Read())
{
main += sdr4["NAME"] + " " + sdr4["TIME"] + "\n" + sdr4["CONTENT"] + "\n\n";
commentrichTextBox.Text = main;
}
sqlConn.Close();
}
string tempfilepath = null; //保存点击数据表时的图片路径
private void BindData()
{
//实例化SqlConnection变量sqlConn，连接数据库
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
//创建一个SqlDataAdapter对象
SqlDataAdapter sda = new SqlDataAdapter("select B_ID,NAME,AUTHOR,PUBLISHER,NUMBER,PUB_TIME,TYPE,STORAGE,IMAGES from BOOKS", sqlConn);
//创建一个DataSet对象
DataSet ds = new DataSet();
//使用SqlDataAdapter对象的Fill方法填充DataSetS
sda.Fill(ds, "BOOKS");
//设置dataGridView1控件数据源
dataGridView1.DataSource = ds.Tables[0];
}
private void Refrensh()
{
//初始化图书导航
//SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
SqlCommand cmd = new SqlCommand("select NAME from BOOK_TYPES ", sqlConn);
SqlDataReader sdr = cmd.ExecuteReader();
int i = 0;
TreeNode[] sup = new TreeNode[20]; //父节点
string[] savesup = new string[20]; //存放数据库查询到的数据
TreeNode[] sub = new TreeNode[50];
string sql;
while (sdr.Read())
{
//添加父节点
sup[i] = treeView2.Nodes.Add(sdr["NAME"].ToString().Trim());
savesup[i] = sdr["NAME"].ToString().Trim();
i++;
}
sqlConn.Close();
//添加当前查询到的父节点的子节点
int k = 0;
for (int j = 0; j < i; j++)
{
sqlConn.Open();
sql = "select NAME from BOOKS WHERE BOOKS.TYPE=(SELECT ID FROM BOOK_TYPES WHERE NAME ='" + savesup[j] + "')";
SqlCommand cmd1 = new SqlCommand(sql, sqlConn);
SqlDataReader sdr1 = cmd1.ExecuteReader();
while (sdr1.Read())
{
sub[k] = new TreeNode(sdr1["NAME"].ToString().Trim());
//将以上子节点添加到父节点中
sup[j].Nodes.Add(sub[k]);
k++;
}
sqlConn.Close();
}
//载入用户借书信息
BindData();
}
private void brrow_Click(object sender, EventArgs e)
{
if (idtextBox.Text == "")
{
MessageBox.Show("还没有选择图书哦！请选好图书再借阅");
}
else
{
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
long timeStamp = (long)(DateTime.Now - startTime).TotalMilliseconds; // 相差毫秒数
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
//检查用户是否正在借阅该书
SqlCommand cmd1 = new SqlCommand("select C_ID from CUR_BORROW where U_ID ='" + U_ID + "' AND B_ID ='" + idtextBox.Text + "'", sqlConn);
SqlDataReader sdr1 = cmd1.ExecuteReader();
sdr1.Read();
if (sdr1.HasRows)
{
MessageBox.Show("您已经借阅了该书籍了，一次只能借一本哦！");
sqlConn.Close();
}
else
{
sqlConn.Close();
string sql = "insert into CUR_BORROW(C_ID,BOR_TIME,U_ID,B_ID) values('" + timeStamp + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + U_ID + "','" + idtextBox.Text + "')";
//MessageBox.Show(sql);
sqlConn.Open(); //打开连接
SqlCommand cmd = new SqlCommand(sql, sqlConn);
int a = cmd.ExecuteNonQuery(); //影响行数
MessageBox.Show("借阅成功！");
BindData(); //更新数据
sqlConn.Close();
}
}
}
private void submitbutton_Click(object sender, EventArgs e)
{
if (addtypestextBox.Text != "")
{
try
{
//实例化数据库连接对象，并设置连接到数据库的参数
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
if (sqlConn.State == ConnectionState.Open)
{
//将注册信息写入数据库
string sql = "insert into BOOK_TYPES (NAME) VALUES('" + addtypestextBox.Text + "')";
MessageBox.Show(sql);
SqlCommand cmd = new SqlCommand(sql, sqlConn);
cmd.ExecuteNonQuery();
MessageBox.Show("插入数据成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
//写入数据库完毕，清空输入框
addtypestextBox.Text = "";
}
else
{
MessageBox.Show("数据库连接失败！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("输入不能为空！");
}
}
private void Manage_Load(object sender, EventArgs e)
{
addbutton.Enabled = true;
changebutton.Enabled = false;
deletebutton.Enabled = false;
searchbutton.Enabled = true;
savebutton.Enabled = false;
cancelbutton.Enabled = false;
BOOKIDtextBox.Enabled = false;
BOOKNAMEtextBox.Enabled = false;
AUTHORtextBox.Enabled = false;
PUBLISHERtextBox.Enabled = false;
STORAGEtextBox.Enabled = false;
numtextBox.Enabled = false;
dateTimePicker1.Enabled = false;
TYPEcomboBox.Enabled = false;
PICTUREbutton.Enabled = false;
//从数据库中读取图书类型，并放入下拉菜单中
TYPEcomboBox.DropDownStyle = ComboBoxStyle.DropDownList;
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
SqlCommand cmd = new SqlCommand("select NAME from BOOK_TYPES ", sqlConn);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
TYPEcomboBox.Items.Add(sdr["NAME"].ToString().Trim());
}
//TYPEcomboBox.SelectedIndex = 0;
}
private void button1_Click(object sender, EventArgs e)
{
if (NametextBox.Text == "")
{
MessageBox.Show("请输入用户名", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (PasswordtextBox.Text == "")
{
MessageBox.Show("请输入密码", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
try
{
//实例化数据库连接对象，并设置连接到数据库的参数
//SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
if (sqlConn.State == ConnectionState.Open)
{
string sql = "select * from USERS where NAME='" + NametextBox.Text + "' and PASSWORDS='" + PasswordtextBox.Text + "'";
// MessageBox.Show(sql);
SqlCommand cmd = new SqlCommand(sql, sqlConn);
SqlDataReader sdr = cmd.ExecuteReader(); //使用ExecuteReader创建SqlDataReader对象
sdr.Read(); //读取/前进到下一条记录
if (sdr.HasRows) //查询结果集中是否有值
{
// MessageBox.Show("恭喜！登录成功");
sdr.Close();
cmd = new SqlCommand("select * from USERS where NAME='" + NametextBox.Text + "'", sqlConn);
SqlDataReader sdr1 = cmd.ExecuteReader();
sdr1.Read();
string UserPower = sdr1["POWERS"].ToString().Trim();
string UserId = sdr1["U_ID"].ToString().Trim();
sqlConn.Close();
Mainform main = new Mainform();
main.power = UserPower;
main.U_ID = UserId;
main.Names = NametextBox.Text;
main.Times = DateTime.Now.ToShortDateString();
main.Show();
this.Hide();
}
else
{
MessageBox.Show("用户名或密码错误");
}
}
else
{
MessageBox.Show("数据库连接失败！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
private void savebutton_Click(object sender, EventArgs e)
{
if (BOOKIDtextBox.Text == "")
{
MessageBox.Show("还没有图书被选中哦！无法进行操作");
}
else
{
//实例化SqlConnection变量sqlConn，连接数据库
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
SqlCommand cmd = new SqlCommand("select count(*) from BOOKS where NAME='" + BOOKNAMEtextBox.Text + "'", sqlConn);
int i = Convert.ToInt32(cmd.ExecuteScalar());
SqlCommand cmd1 = new SqlCommand("select ID from BOOK_TYPES WHERE NAME ='" + TYPEcomboBox.Text + "'", sqlConn);
SqlDataReader sdr = cmd1.ExecuteReader();
sdr.Read();
string Type = sdr["ID"].ToString().Trim();
// MessageBox.Show(Type);
sqlConn.Close();
// MessageBox.Show( Convert.ToString(i));
if (i > 0)
{
if (tempfilepath != oldfilepath) //当用户更改了图片时才执行此步操作
{ //tempfilepath为用户点击dategridview时，传过来的路径，即原来数据库中保存的那个;
//oldfilepath为用户点击选择图片按钮时选择的图片路径
//如果用户点击了选择图片按钮，则oldfilepat会发生改变
MessageBox.Show(tempfilepath);
Filedealing upfile = new Filedealing();
newfilepath = upfile.uploadfile(oldfilepath); //将用户选定图片上传至系统制定目录后，将其完整的路径赋给一个变量
this.pictureBox1.Load(newfilepath);
FileInfo delete = new FileInfo(tempfilepath);
delete.Delete(); //删除记录对于的图片文件
MessageBox.Show("图片更新成功！" + tempfilepath);
sqlConn.Open(); //打开连接
string sql = "update BOOKS set NAME='" + BOOKNAMEtextBox.Text.Trim() + "',AUTHOR='" + AUTHORtextBox.Text.Trim() + "',PUBLISHER='" + PUBLISHERtextBox.Text.Trim() + "',NUMBER=" + numtextBox.Text.Trim() + ",TYPE='" + Type.Trim() + "',PUB_TIME='" + dateTimePicker1.Text.Trim() + "',STORAGE='" + STORAGEtextBox.Text.Trim() + "',IMAGES='" + newfilepath.Trim() + "' where B_ID='" + dataGridView1.SelectedCells[0].Value.ToString().Trim() + "'";
SqlCommand cmd2 = new SqlCommand(sql, sqlConn);
cmd2.ExecuteNonQuery();
MessageBox.Show(newfilepath);
sqlConn.Close();
BindData();
}
else
{
MessageBox.Show(tempfilepath);
sqlConn.Open(); //打开连接
string sql = "update BOOKS set NAME='" + BOOKNAMEtextBox.Text.Trim() + "',AUTHOR='" + AUTHORtextBox.Text.Trim() + "',PUBLISHER='" + PUBLISHERtextBox.Text.Trim() + "',NUMBER=" + numtextBox.Text.Trim() + ",TYPE='" + Type.Trim() + "',PUB_TIME='" + dateTimePicker1.Text.Trim() + "',STORAGE='" + STORAGEtextBox.Text.Trim() + "',IMAGES='" + tempfilepath.Trim() + "' where B_ID='" + dataGridView1.SelectedCells[0].Value.ToString().Trim() + "'";
MessageBox.Show(sql);
SqlCommand cmd2 = new SqlCommand(sql, sqlConn);
int a = cmd2.ExecuteNonQuery();
MessageBox.Show("affect---" + a);
sqlConn.Close();
BindData();
}
addbutton.Enabled = true;
deletebutton.Enabled = false;
changebutton.Enabled = false;
searchbutton.Enabled = true;
savebutton.Enabled = false;
cancelbutton.Enabled = true;
BOOKNAMEtextBox.Enabled = false;
}
else
{
Filedealing upfile = new Filedealing();
newfilepath = upfile.uploadfile(oldfilepath); //将用户选定图片上传至系统制定目录后，将其完整的路径赋给一个变量
MessageBox.Show(newfilepath);
sqlConn.Open(); //打开连接
string sql = "insert into BOOKS(B_ID,NAME,AUTHOR,PUBLISHER,NUMBER,TYPE,PUB_TIME,STORAGE,IMAGES ) values('" + BOOKIDtextBox.Text.Trim() + "','" + BOOKNAMEtextBox.Text.Trim() + "','" + AUTHORtextBox.Text.Trim() + "','" + PUBLISHERtextBox.Text.Trim() + "','" + numtextBox.Text.Trim() + "','" + Type.Trim() + "','" + dateTimePicker1.Text.Trim() + "','" + STORAGEtextBox.Text.Trim() + "','" + newfilepath.Trim() + "')";
MessageBox.Show(sql);
cmd = new SqlCommand(sql, sqlConn);
int a = cmd.ExecuteNonQuery();
MessageBox.Show("affect---" + a);
sqlConn.Close();
BindData();
addbutton.Enabled = true;
deletebutton.Enabled = false;
changebutton.Enabled = false;
searchbutton.Enabled = true;
savebutton.Enabled = false;
cancelbutton.Enabled = true;
BOOKNAMEtextBox.Enabled = false;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (IDtextBox.Text == "")
{
MessageBox.Show("请输入ID", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (NametextBox.Text == "")
{
MessageBox.Show("请输入用户名", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (PasswordtextBox.Text == "")
{
MessageBox.Show("请输入密码", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (DeparttextBox.Text == "")
{
MessageBox.Show("请输入您所在的班级", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
} //用户输入规范检查完毕，开始连接数据库
try
{
//实例化数据库连接对象，并设置连接到数据库的参数
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
if (sqlConn.State == ConnectionState.Open)
{
//检查用户是否正在借阅该书
SqlCommand cmd1 = new SqlCommand("select U_ID from USERS where U_ID ='" + IDtextBox.Text + "'", sqlConn);
SqlDataReader sdr1 = cmd1.ExecuteReader();
sdr1.Read();
if (sdr1.HasRows)
{
MessageBox.Show("sorry！该用户ID 已经存在了哦！请换个ID 号进行注册");
sqlConn.Close();
}
else
{
sqlConn.Close();
//将注册信息写入数据库
string sql = "insert into USERS (U_ID,NAME,DEPARTMENT,PASSWORDS) VALUES('" + IDtextBox.Text + "','" + NametextBox.Text + "','" + DeparttextBox.Text + "','" + PasswordtextBox.Text + "')";
// MessageBox.Show(sql);
SqlCommand cmd = new SqlCommand(sql, sqlConn);
sqlConn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("注册成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
//写入数据库完毕，将用户信息初始化到系统
Mainform main = new Mainform();
main.power = "0";
main.Names = NametextBox.Text;
main.Times = DateTime.Now.ToShortDateString();
main.Show();
this.Hide();
sqlConn.Close();
}
}
else
{
MessageBox.Show("数据库连接失败！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "";
string where = " WHERE ";
if (NAMEtextBox1.Text == "")
{
sql += "";
}
else
{
sql = "NAME LIKE '%" + NAMEtextBox1.Text + "%' ";
}
if (AUTHORtextBox2.Text == "")
{
sql += "";
}
else if (NAMEtextBox1.Text != "")
{
sql += " AND AUTHOR LIKE '%" + AUTHORtextBox2.Text + "%' ";
}
else
{
sql += "AUTHOR LIKE '%" + AUTHORtextBox2.Text + "%' ";
}
if (PUBLISHERtextBox3.Text == "")
{
sql += "";
}
else if (AUTHORtextBox2.Text != "" && NAMEtextBox1.Text != "")
{
sql += " AND PUBLISHER LIKE '%" + PUBLISHERtextBox3.Text + "%' ";
}
else
{
sql += "PUBLISHER LIKE '%" + PUBLISHERtextBox3.Text + "%' ";
}
if (NAMEtextBox1.Text == "" && AUTHORtextBox2.Text == "" && NAMEtextBox1.Text == "")
{ //如果三个输入框都为空则将所有数据都查出来
where = "";
}
// SqlConnection sqlConn = new SqlConnection("server=.;database=BOOK_MANAGE_SYSTEM;Trusted_Connection=SSPI");
SqlConnection sqlConn = DBconnect.BooksystemCon();
sqlConn.Open(); //打开连接
// MessageBox.Show("select B_ID,NAME,AUTHOR,PUBLISHER,NUMBER,PUB_TIME,TYPE,STORAGE,IMAGES from BOOKS " + where + sql.Trim());
//创建一个SqlDataAdapter对象
SqlDataAdapter sda = new SqlDataAdapter("select B_ID,NAME,AUTHOR,PUBLISHER,NUMBER,PUB_TIME,TYPE,STORAGE,IMAGES from BOOKS " + where + sql.Trim(), sqlConn);
//创建一个DataSet对象
DataSet ds = new DataSet();
//使用SqlDataAdapter对象的Fill方法填充DataSetS
sda.Fill(ds, "BOOKS");
//设置dataGridView1控件数据源
dataGridView1.DataSource = ds.Tables[0];
}