?? form1.cs
字號:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Library
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button_query_book_Click(object sender, EventArgs e)
{
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
listView_book.Columns.Clear();
listView_book.Items.Clear();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_query_title";
SqlParameter parInput = cmd.Parameters.Add("@title", SqlDbType.VarChar);
parInput.Direction = ParameterDirection.Input;
parInput.Value = Convert.ToString(textBox_title.Text);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);
listView_book.Columns.Add(ch);
}
ListViewItem itemX;
while (dr.Read())
{
itemX = new ListViewItem();
itemX.Text = dr.GetValue(0).ToString();
for (int i = 1; i < dr.FieldCount; i++)
{
itemX.SubItems.Add(dr.GetValue(i).ToString());
}
listView_book.Items.Add(itemX);
}
dr.Close();
cn.Close();
}
catch (SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
private void borrowbook_Click(object sender, EventArgs e)
{
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_borrowbook";
SqlParameter parInput1 = cmd.Parameters.Add("@cno", SqlDbType.VarChar);
SqlParameter parInput2 = cmd.Parameters.Add("@bno", SqlDbType.VarChar);
SqlParameter parOutput = cmd.Parameters.Add("@returnDate", SqlDbType.DateTime);
SqlParameter parOutput1 = cmd.Parameters.Add("@title", SqlDbType.VarChar, 60);
parInput1.Direction = ParameterDirection.Input;
parInput2.Direction = ParameterDirection.Input;
parOutput.Direction = ParameterDirection.Output;
parOutput1.Direction = ParameterDirection.Output;
parInput1.Value = Convert.ToString(textBoxcno.Text);
parInput2.Value = Convert.ToString(textBoxbno.Text);
cn.Open();
cmd.ExecuteNonQuery();
labelmess.Text = Convert.ToString(parOutput1.Value) + " 已借出，請再" + Convert.ToString(parOutput.Value) + "前歸還!";
}
catch(SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
private void returnbook_Click(object sender, EventArgs e)
{
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_returnbook";
SqlParameter parInput1 = cmd.Parameters.Add("@cno", SqlDbType.VarChar);
SqlParameter parInput2 = cmd.Parameters.Add("@bno", SqlDbType.VarChar);
SqlParameter parOutput = cmd.Parameters.Add("@title", SqlDbType.VarChar, 60);
parInput1.Direction = ParameterDirection.Input;
parInput2.Direction = ParameterDirection.Input;
parOutput.Direction = ParameterDirection.Output;
parInput1.Value = Convert.ToString(textBoxcno.Text);
parInput2.Value = Convert.ToString(textBoxbno.Text);
cn.Open();
cmd.ExecuteNonQuery();
labelmess.Text = "你所借的 " + Convert.ToString(parOutput.Value) + " 已歸還!";
}
catch (SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
private void queryOther_Click(object sender, EventArgs e)
{
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
listView1.Columns.Clear(); // clear listview
listView1.Items.Clear();
string SQL = "SELECT count(bno) FROM book;"; // TommandText, default
switch(listBox.SelectedIndex) // set SQL according defferent SelectedIndex in listBox
{
case 0: //藏書種數(shù)
SQL = "SELECT count(bno) FROM book;";
break;
case 1: //藏書總冊數(shù)
SQL = "SELECT sum(total) FROM book;";
break;
case 2: //藏書最高價
SQL = "SELECT max(price) FROM book;";
break;
case 3: //藏書最低價
SQL = "SELECT min(price) FROM book;";
break;
case 4: //藏書在十本以上的書
SQL = "SELECT author, title, press, year FROM book WHERE total >= 10;";
break;
case 5: //總藏書超過100種的出版社
SQL = "SELECT press, sum(total) FROM book GROUP BY press HAVING sum(total) > 100;";
break;
case 6: //目前已借出的冊數(shù)
SQL = "SELECT count(*) FROM borrow;";
break;
case 7: //年份最久遠的書
SQL = "SELECT * FROM book WHERE year = (SELECT min(year) FROM book);";
break;
case 8: //圖書最多的年份
SQL = "SELECT year, sum(total) FROM book GROUP BY year HAVING (sum(total) >= all (SELECT sum(total) FROM book GROUP BY year));";
break;
case 9: //平均每本借書證的借書冊數(shù)
SQL = "SELECT count(*) / count(distinct cno) FROM borrow;";
break;
case 10: //今年未借過書的借書證, 查詢要求和目前的數(shù)據(jù)庫版本不符合
SQL = "";
break;
case 11: //平均借書最多的系， 查詢要求和目前的數(shù)據(jù)庫版本不符合
SQL = "";
break;
}
SqlCommand cmd = new SqlCommand(SQL, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);
listView1.Columns.Add(ch);
}
ListViewItem itemX;
while (dr.Read())
{
itemX = new ListViewItem();
itemX.Text = dr.GetValue(0).ToString();
for (int i = 1; i < dr.FieldCount; i++)
{
itemX.SubItems.Add(dr.GetValue(i).ToString());
}
listView1.Items.Add(itemX);
}
dr.Close();
cn.Close();
}
catch(SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
// add card
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_addcard";
SqlParameter parInput1 = cmd.Parameters.Add("@cno", SqlDbType.VarChar);
SqlParameter parInput2 = cmd.Parameters.Add("@name", SqlDbType.VarChar);
SqlParameter parInput3 = cmd.Parameters.Add("@department", SqlDbType.VarChar);
SqlParameter parInput4 = cmd.Parameters.Add("@class", SqlDbType.Char);
//SqlParameter parReturn = cmd.Parameters.Add("Return Value", SqlDbType.Int);
parInput1.Direction = ParameterDirection.Input;
parInput2.Direction = ParameterDirection.Input;
parInput3.Direction = ParameterDirection.Input;
parInput4.Direction = ParameterDirection.Input;
//parReturn.Direction = ParameterDirection.ReturnValue;
parInput1.Value = Convert.ToString(textBox1.Text);
parInput2.Value = Convert.ToString(textBox2.Text);
parInput3.Value = Convert.ToString(textBox3.Text);
parInput4.Value = Convert.ToChar(textBox4.Text);
cn.Open();
cmd.ExecuteNonQuery();
labeladd.Text = "借書卡創(chuàng)建成功!";
}
catch(SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
// query card information
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
listView2.Columns.Clear();
listView2.Items.Clear();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_querycard";
SqlParameter parInput1 = cmd.Parameters.Add("@cno", SqlDbType.VarChar);
SqlParameter parOutput1 = cmd.Parameters.Add("@bnum", SqlDbType.Int);
SqlParameter parOutput2 = cmd.Parameters.Add("@name", SqlDbType.VarChar, 10);
parInput1.Direction = ParameterDirection.Input;
parOutput1.Direction = ParameterDirection.Output;
parOutput2.Direction = ParameterDirection.Output;
parInput1.Value = Convert.ToString(textBox1.Text);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);
listView2.Columns.Add(ch);
}
ListViewItem itemX;
while (dr.Read())
{
itemX = new ListViewItem();
itemX.Text = dr.GetValue(0).ToString();
for (int i = 1; i < dr.FieldCount; i++)
{
itemX.SubItems.Add(dr.GetValue(i).ToString());
}
listView2.Items.Add(itemX);
}
dr.Close();
cn.Close();
labelresult.Text = "歡迎" + Convert.ToString(parOutput2.Value).Trim() + ",你已借" + Convert.ToString(parOutput1.Value) + "本書.";
}
catch (SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
// delete card
string cnStr = @"server = (local)\SQLEXPRESS;" + "Integrated Security = true;" + "database = Library;";
SqlConnection cn = new SqlConnection(cnStr);
try
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_deletecard";
SqlParameter parInput1 = cmd.Parameters.Add("@cno", SqlDbType.VarChar);
parInput1.Direction = ParameterDirection.Input;
parInput1.Value = Convert.ToString(textBox1.Text);
cn.Open();
cmd.ExecuteNonQuery();
labeladd.Text = "借書卡已刪除!";
}
catch(SqlException e1)
{
MessageBox.Show("There war an error in executing the SQL Command." + "Error Message:" + e1.Message);
}
finally
{
cn.Close();
}
}
}
}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -