dataoperation.cs

图书馆管理系统的一个代码 很好的学习借鉴

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Drawing ;
namespace BookManagementSystem
{
    public class DataOperation
    {
        SqlConnection sqlconn;
        SqlCommand cmd;
        SqlParameter para;
        public DataOperation()
        {
            try
            {

                sqlconn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Mydata2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
                sqlconn.Open();
                cmd = sqlconn.CreateCommand();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void CloseConnection()
        {
            sqlconn.Close();
        }
        public Boolean CheckAdmin(string account, string password)
        {
            cmd.CommandText = "select * from admin where account='" + account + "' and password='" + password + "'";
            SqlDataReader thisReader = cmd.ExecuteReader();
            if (thisReader.Read())
            {
                thisReader.Close();
                return true;
            }
            else
            {
                thisReader.Close();
                return false;
            }
        }
        public DataSet GetAllBookData()
        {
            SqlDataAdapter thisAdapter = new SqlDataAdapter("Select * from Book", sqlconn);
            DataSet thisDataSet=new DataSet();
            thisAdapter.Fill(thisDataSet, "Book");
            return thisDataSet;
           // thisAdapter.Fill(thisDataSet,"Boo
        }
        public ArrayList GetBigClassOfBook()
        {
            cmd.CommandText = "select *  from BigClassOfBook";
            SqlDataReader thisReader = cmd.ExecuteReader();
            ArrayList p = new ArrayList();
            while (thisReader.Read())
                p.Add(thisReader["大类"]);
            thisReader.Close();
            return p;
        }
        public ArrayList GetSmallClassOfBook(string BigClassOfBook)
        {
            cmd.CommandText = "select * from SmallClassOfBook where 小类 LIKE '"+BigClassOfBook+"%'";
            SqlDataReader thisReader = cmd.ExecuteReader();
            ArrayList p = new ArrayList();
            while (thisReader.Read())
                p.Add(thisReader["小类"].ToString().Split(new Char[] { '+' })[1]);
            thisReader.Close();
            return p;
        }
        //public 
        public ArrayList GetSmallClassOfBook()
        {
            cmd.CommandText = "select * from SmallClassOfBook ";
            SqlDataReader thisReader = cmd.ExecuteReader();
            ArrayList p = new ArrayList();
            while (thisReader.Read())
                p.Add(thisReader["小类"].ToString());
            thisReader.Close();
            return p;
        }

        public int IssueBook(string []Insert)
        {
            try
            {
                cmd.CommandText = "select * from IssueBook where 图书编号='"+Insert[0]+"' and "+"借书人编号='"+Insert[1]+"' and "+"还书日期='None'";
                SqlDataReader thisReader = cmd.ExecuteReader();
                if (thisReader.Read())
                {
                    thisReader.Close();
                    return 1;
                }
                thisReader.Close();
                cmd.CommandText = "insert into IssueBook values(@图书编号,@借书人编号,@借书人姓名,@借书日期,@还书日期)";
                para = new SqlParameter("@图书编号", SqlDbType.NVarChar,50);
                para.Value = Insert[0];
                cmd.Parameters.Add(para);
                para = new SqlParameter("@借书人编号", SqlDbType.NVarChar,50);
                para.Value = Insert[1];
                cmd.Parameters.Add(para);
                para = new SqlParameter("@借书人姓名", SqlDbType.NVarChar,50);
                para.Value = Insert[2];
                cmd.Parameters.Add(para);
                para = new SqlParameter("@借书日期", SqlDbType.NVarChar,100);
                para.Value = Insert[3];
                cmd.Parameters.Add(para);
                para = new SqlParameter("@还书日期", SqlDbType.NVarChar, 100);
                para.Value = "未还";
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
                cmd.CommandText="UPDATE Book set 现有数量=现有数量-1 where 书籍编号='"+Insert[0]+"'";
                cmd.ExecuteNonQuery();
                return 2;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return 3; }

        }
        public int ReturnBook(string[] updata)
        {
            try
            {
                cmd.CommandText = "select * from IssueBook where 图书编号='" + updata[0] + "' and " + "借书人编号='" + updata[1]+"'";
                SqlDataReader thisReader = cmd.ExecuteReader();
                if (!thisReader.Read())
                {
                    thisReader.Close();
                    return 1;
                }
                thisReader.Close();
                cmd.CommandText = "UPDATE IssueBook set 还书日期='" + updata[2] + "' where 图书编号='" + updata[0] + "' and 借书人编号='" + updata[1]+"'";
                //MessageBox.Show(cmd.CommandText);
                cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE Book set 现有数量=现有数量+1 where 书籍编号='" + updata[0]+"'";
                cmd.ExecuteNonQuery();
                return 2;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return 3; }
            
        }
        public string[] ReturnBookNameContenAndCount(string no)
        {
            try
            {
                cmd.CommandText = "select * from Book " + " where 书籍编号='" + no+"'";
                SqlDataReader thisReader = cmd.ExecuteReader();
                string[] sdata = new string[3];
                thisReader.Read();
                sdata[0] = thisReader["书名"].ToString();
                sdata[1] = thisReader["内容简介"].ToString();
                sdata[2] = thisReader["现有数量"].ToString();
                thisReader.Close();
                return sdata;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return null; }
;
        }
        public ArrayList GetBookNamesFromPersonalNo(string no)
        {
            cmd.CommandText = "select 书名 from IssueBook left outer join Book on IssueBook.图书编号=Book.书籍编号"
                + " where IssueBook.借书人编号='" + no + "'  and IssueBook.还书日期='未还'";
            SqlDataReader thisReader = cmd.ExecuteReader();
            ArrayList p = new ArrayList();
            try
            {  
                while (thisReader.Read())
                {
                    p.Add(thisReader["书名"].ToString());
                }
                return p;
            }
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message); return null; 
            }
            finally
            {
                thisReader.Close();
            }
        }
        public string GetBookNoFromName(string name)
        {
            cmd.CommandText = "select 书籍编号 from Book where 书名='" + name + "'";
            SqlDataReader thisReader = cmd.ExecuteReader();
            try
            {
                thisReader.Read();
                return thisReader["书籍编号"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message); return null;
            }
            finally
            {
                thisReader.Close();
            }
        }
        public string GetPersonalNameFromNo(string no)
        {
            cmd.CommandText = "select 借书人姓名 from IssueBook where 借书人编号='" + no + "'";
            SqlDataReader thisReader = cmd.ExecuteReader();
            try
            {
                thisReader.Read();
                return thisReader["借书人姓名"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message); return null;
            }
            finally
            {
                thisReader.Close();
            }
        }
        public void InsertImage(string FilePath)
        {
            cmd.CommandText = "insert into temptable values (@t)";
            FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
            byte[] ib = new Byte[fs.Length];
            fs.Read(ib, 0, Convert.ToInt32(fs.Length));
            cmd.Parameters.Add("@t", SqlDbType.Image, (int)fs.Length);
            cmd.Parameters["@t"].Value = ib;
            cmd.ExecuteNonQuery();
            fs.Close();
        }
        public Image GetImage()
        {
            cmd.CommandText = "select t from temptable";
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            MemoryStream buf = new MemoryStream((byte[])reader[0]);
            Image image = Image.FromStream(buf, true);
            return image;
        }
        public Image GetBookImageFromBookNo(string no)
        {
            cmd.CommandText = "select 图片 from Book where 书籍编号='"+no+"'";
            SqlDataReader reader = cmd.ExecuteReader();
            try
            {
                reader.Read();
                MemoryStream buf = new MemoryStream((byte[])reader["图片"]);
                Image image = Image.FromStream(buf, true);
                return image;
            }
            catch (Exception ex) { return null; }
            finally
            {
                reader.Close();
            }
            
        }
        public Boolean AddSmallClass(string sdata)
        {
            try
            {
                cmd.CommandText = "insert into SmallClassOfBook values('" + sdata + "')";
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex) { return false; }
        }
        public Boolean DeleteSmallClass(string SmallClass)
        {
            try
            {
                cmd.CommandText = "Delete from SmallClassOfBook where 小类='" + SmallClass + "'";
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex) { return false; }
        }
        public Boolean UpdataSmallClass(string OldSmallClass,string NewSmallClass)
        {
            try
            {
                cmd.CommandText = "update SmallClassOfBook set  小类='" + NewSmallClass + "'" + " where 小类='" + OldSmallClass + "'";
                //MessageBox.Show(cmd.CommandText);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message); 
                return false; 
            }
        }
        public Boolean CheckUser(string account, string password)
        {
            cmd.CommandText = "select * from Reader where acount='" + account + "' and password='" + password + "'";
            SqlDataReader thisReader = cmd.ExecuteReader();
            if (thisReader.Read())
            {
                thisReader.Close();
                return true;
            }
            else
            {
                thisReader.Close();
                return false;
            }
        }
    //    public string GetNameFromAccount(string account,
    }
}