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.OleDb;

namespace Test09
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        OleDbConnection conn = new OleDbConnection(" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Library.mdb");       
        private void Form1_Load(object sender, EventArgs e)
        {
            cbbBookType.Text = "请选择--->";
            txtBuyDate.Text = DateTime.Today.ToShortDateString();
            txtStatus.Text = "在库";

            conn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM BOOKTYPE", conn);
            OleDbDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                cbbBookType.Items.Add(dr["类型"]);
            }
            dr.Close();
            conn.Close();

        }

        private void btnInStore_Click(object sender, EventArgs e)
        {
            string bookId = newBookId(txtISBN.Text);
            string sql = String.Format("INSERT INTO BOOKINFO (图书编号,ISBN,书名,作者,出版社,单价,类型,购买日期,状态) VALUES('{0}','{1}','{2}','{3}','{4}',{5},'{6}',#{7}#,'{8}')", bookId, txtISBN.Text, txtBookName.Text, txtAuthor.Text, txtPublisher.Text, txtPrice.Text, cbbBookType.Text, txtBuyDate.Text, txtStatus.Text);

            OleDbCommand cmd = new OleDbCommand(sql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("入库操作成功,图书编号:" + bookId);

        }
        //根据ISBN为图书生成一个图书编号
        private string newBookId(string isbn)
        {
            string sql = "SELECT COUNT(*) FROM BOOKINFO WHERE ISBN='" + isbn + "'";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            conn.Open();
            int number = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
            conn.Close();
            return isbn + "-" + number.ToString();
        }

        private void btnLendOut_Click(object sender, EventArgs e)
        {
            //修改图书信息
            string sql1 = "UPDATE BOOKINFO SET 状态='借出' WHERE 图书编号='" + txtBookId.Text + "'";
            OleDbCommand cmd1 = new OleDbCommand(sql1, conn);

            //添加借书记录
            string sql2 = "INSERT INTO BOOKLENDED (图书编号,读者ID,借出日期) VALUES ('" + txtBookId.Text + "','" + txtReaderId.Text + "',Now())";
            OleDbCommand cmd2 = new OleDbCommand(sql2, conn);

            conn.Open();
            cmd1.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("借书记录已经添加成功,图书编号:" + txtBookId.Text+"。借书人："+txtReaderId.Text);
        }

        private void txtBookId_TextChanged(object sender, EventArgs e)
        {

            string sql = "SELECT * FROM BOOKINFO WHERE 图书编号='" + txtBookId.Text + "'";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            conn.Open();

            OleDbDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                lblISBN.Text = dr["ISBN"].ToString();
                lblBookName.Text = dr["书名"].ToString();
                lblAuthor.Text = dr["作者"].ToString();
                lblPublisher.Text = dr["出版社"].ToString();
                lblPrice.Text = dr["单价"].ToString();
                lblBookType.Text = dr["类型"].ToString();
                lblBuyDate.Text = dr["购买日期"].ToString();
            }
            dr.Close();
            conn.Close();

        }

    }
}