form1.cs

数据库的图书管理系统 由C#开发环境开发 功能：搜索

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:         //藏书种数
                        SQL = "SELECT count(bno) FROM book;";
                        break;
                    case 1:         //藏书总册数
                        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:         //目前已借出的册数
                        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:         //平均每本借书证的借书册数
                        SQL = "SELECT count(*) / count(distinct cno) FROM borrow;";
                        break;
                    case 10:        //今年未借过书的借书证, 查询要求和目前的数据库版本不符合
                        SQL = "";
                        break;
                    case 11:        //平均借书最多的系， 查询要求和目前的数据库版本不符合
                        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 = "借书卡创建成功!";              
            }
            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();
            }                        
        
        }



    }
}