allquery.cs

用C#与SQL2005写的一个简单的图书管理系统.主要功能:图书及学生信息的添加修改与删除 图书的借出与归还 图书信息的模糊查询.

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 BooksM.InforQuery
{
    public partial class AllQuery : Form
    {
        public AllQuery()
        {
            InitializeComponent();
        }

        private void radioButton13_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void AllQuery_Load(object sender, EventArgs e)
        {
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MyClass.ConnData myConnData = new BooksM.MyClass.ConnData();
            DataSet myDataSet = new DataSet();
            if (radioButton1.Checked)
            {
                textBox1.Text = "";
                string temp = "select sum(ActualNum),sum(LoanNum) from BookCardInfor ";
                try
                {
                    myDataSet = myConnData.MyDataSet(temp);
                    textBox1.Text ="共"+Convert.ToString(Convert.ToInt32(myDataSet.Tables[0].Rows[0][0])+Convert.ToInt32(myDataSet.Tables[0].Rows[0][1]))+"本书";
                    button1.Focus();
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
            if (radioButton2.Checked)
            {
                try
                {
                    double money=0;
                    textBox1.Text = "";
                    string temp = "select ActualNum,LoanNum,ListPrice from BookCardInfor ";
                    SqlCommand mySqlCommand = new SqlCommand(temp,myConnData.ConnectDB());
                    SqlDataReader myDataReader = mySqlCommand.ExecuteReader();
                    while (myDataReader.Read())
                    {
                        money = (Convert.ToDouble(myDataReader.GetValue(0)) + Convert.ToDouble(myDataReader.GetValue(1))) * Convert.ToDouble(myDataReader.GetValue(2));
                        button1.Focus();
                    }
                    textBox1.Text = "总金额为：" + money.ToString() + "元";
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
            if (radioButton3.Checked)
            {
                textBox1.Text = "";
                string temp = "select sum(ActualNum) from BookCardInfor ";
                try
                {
                    myDataSet = myConnData.MyDataSet(temp);
                    textBox1.Text = "库存" + Convert.ToString(Convert.ToInt32(myDataSet.Tables[0].Rows[0][0])) + "本书";
                    button1.Focus();
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
            if (radioButton4.Checked)
            {
                textBox1.Text = "";
                string temp = "select max(ListPrice) from BookCardInfor ";
                try
                {
                    myDataSet = myConnData.MyDataSet(temp);
                    textBox1.Text = "最高价为：" + Convert.ToString(Convert.ToInt32(myDataSet.Tables[0].Rows[0][0])) + "元";
                    button1.Focus();
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
            if (radioButton5.Checked)
            {
                textBox1.Text = "";
                string temp = "select min(ListPrice) from BookCardInfor ";
                try
                {
                    myDataSet = myConnData.MyDataSet(temp);
                    textBox1.Text = "最低价为：" + Convert.ToString(Convert.ToInt32(myDataSet.Tables[0].Rows[0][0])) + "元";
                    button1.Focus();
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
            if (radioButton6.Checked)
            {
                textBox1.Text = "";
                string temp = "select sum(LoanNum) from BookCardInfor ";
                try
                {
                    myDataSet = myConnData.MyDataSet(temp);
                    textBox1.Text = "库存" + Convert.ToString(Convert.ToInt32(myDataSet.Tables[0].Rows[0][0])) + "本书";
                    button1.Focus();
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
            if (radioButton7.Checked)
            {
                textBox1.Text = "";
                string temp = "select year(PublicationDate),count(PublicationDate) from BookCardInfor group by year(PublicationDate) ";
                try
                {
                    myDataSet = myConnData.MyDataSet(temp);
                    textBox1.Text = Convert.ToString(myDataSet.Tables[0].Rows[0][0]) + "年的书最多，为"+Convert.ToString(myDataSet.Tables[0].Rows[0][1])+"本书";
                    button1.Focus();
                }
                catch
                {
                    MessageBox.Show("统计失败！");
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (radioButton8.Checked)
            {

                try
                {
                    MyClass.ConnData myConnData = new BooksM.MyClass.ConnData();
                    string temp = "select * "
                    + "from BookCardInfor"
                    + " where (ActualNum+LoanNum)>=10 ";
                    myConnData.ShowDataGrid(ref temp, ref dataGridView1);
                }
                catch
                {
                    MessageBox.Show("查询失败！");
                }
            }
            if (radioButton9.Checked)
            {

                try
                {
                    MyClass.ConnData myConnData = new BooksM.MyClass.ConnData();
                    string temp = "select Publisher 出版社,count(BookCategoryID) 图书种类 "
                        +"from BookCardInfor"
                        +" group by Publisher "
                        +"having count(BookCategoryID)>100 ";
                    myConnData.ShowDataGrid(ref temp, ref dataGridView1);
                }
                catch
                {
                    MessageBox.Show("查询失败！");
                }
            }
            if (radioButton11.Checked)
            {

                try
                {
                    MyClass.ConnData myConnData = new BooksM.MyClass.ConnData();
                    string temp = "select * "
                        + "from ReaderInfor "
                        + "where not exists (select * from LoanInfor where LoanInfor.ReaderID=ReaderInfor.ReaderID and DateDiff(year,DueReturnDate,getdate())<1) ";
                    myConnData.ShowDataGrid(ref temp, ref dataGridView1);
                }
                catch
                {
                    MessageBox.Show("查询失败！");
                }
            }
            if (radioButton12.Checked)
            {

                try
                {
                    MyClass.ConnData myConnData = new BooksM.MyClass.ConnData();
                    string temp = "select * "
                        + "from BookCardInfor a "
                        + "where not exists (select * from BookCardInfor b where year(b.PublicationDate)<year(a.PublicationDate)) ";
                    myConnData.ShowDataGrid(ref temp, ref dataGridView1);
                }
                catch
                {
                    MessageBox.Show("查询失败！");
                }
            }
            if (radioButton13.Checked)
            {

                try
                {
                    MyClass.ConnData myConnData = new BooksM.MyClass.ConnData();
                    string temp = "select * "
                        + "from BookCardInfor "
                        + "where not exists (select * from LoanInfor where LoanInfor.BookID=BookCardInfor.BookID and DateDiff(year,DueReturnDate,getdate())<2) ";
                    myConnData.ShowDataGrid(ref temp, ref dataGridView1);
                }
                catch
                {
                    MessageBox.Show("查询失败！");
                }
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}