图书管理操作类（C#）

 using System;
using System.Data.SqlClient;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;

namespace book.DAL
{
 /// <summary>
 /// DBController 的摘要说明。
 /// </summary>
 public class DBController
 {
  
  private string connectingstring;
  private SqlConnection myConn;
  private DataSet ds;
  private SqlDataAdapter myAdapter;
  private SqlCommand myCmd;
  
  public DBController()
  {
   connectingstring = System.Configuration.ConfigurationSettings.AppSettings[ "ConnectionString" ];
   myConn = new SqlConnection(connectingstring);
  }
          //打开关闭连接
  public void open()
  {
      myConn.Open();
  }

  public void Close()
  {
   myConn.Close();
  }
  public DataSet GetDS()
  {
   return ds;
  }
//*****************************************************DAL*****************************************************//
 
         //input:sqlstr，SQL字符串,output:null
  public void Fill(string sqlstr)
  {
   myAdapter = new SqlDataAdapter(sqlstr, myConn);
   ds = new DataSet();
   myAdapter.Fill(ds);
  }
        //input:SQL字符串.output:null
  public void ExecNonSql(string sqlstr)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   myCmd = new SqlCommand(sqlstr, myConn);
   myCmd.ExecuteNonQuery();
   myCmd.Dispose();
   Close();
  }
         // 输入参数：的SQL字符串.返回SqlDataReader对象
  public SqlDataReader ExecReaderSql(string sqlstr)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   myCmd = new SqlCommand(sqlstr, myConn);
   SqlDataReader reader;
   reader = myCmd.ExecuteReader();
   myCmd.Dispose();
   return reader;
  }
         //input:SQL字符串；myDBGrd，需数据绑定的DataGrid控件.output:null
  public void BindDBGrd(string sqlstr, DataGrid myDBGrd)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   Fill(sqlstr);
   myDBGrd.DataSource = ds.Tables[0].DefaultView;
   myDBGrd.DataBind();
  }
  //input:SQL字符串；myDBGrd，需数据绑定的DataGrid控件 ,SortExp：排序方式,output:null
  public void BindDBGrd(string sqlstr, DataGrid myDBGrd, object SortExp)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   Fill(sqlstr);
   DataView dv = ds.Tables[0].DefaultView;
   dv.Sort =(string)SortExp;
   myDBGrd.DataSource = dv;
   myDBGrd.DataBind();
  }
      //input:sqlstr，查询的SQL字符串；myListBox：需数据绑定的ListBox控件.output:null
  public void BindListBox(string sqlstr, ListBox myListBox)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   Fill(sqlstr);
   DataView dv = ds.Tables[0].DefaultView;
   myListBox.DataSource = dv;
   myListBox.DataBind();
  }
     //input:sqlstr，查询的SQL字符串；DrpDownList：需数据绑定的DropDownList控件.output:null
  public void BindDrpDownList(string sqlstr, DropDownList DrpDownList)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   Fill(sqlstr);
   DrpDownList.DataSource = ds.Tables[0].DefaultView;
   DrpDownList.DataBind();
   Close();
  }
        //input:string name,pwd.output:1 or -1
  public int CheckUserberInfo(string Userstr, string Pwdstr)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   string sqlstr;
   if (Pwdstr == "")
   {
    sqlstr = "select * from UserInfo where UserID='" + Userstr.Trim() + "' and Pass is null";
   }
   else
   {
    sqlstr = "select * from UserInfo where UserID='" + Userstr.Trim() + "' and Pass='" + Pwdstr + "'";
   }
   Fill(sqlstr);
   if (ds.Tables[0].Rows.Count == 0)
   {
    Close();
    return -1;
   }
   ds.Clear();
   Close();
   return 1;
  }
          //获取SQL查询记录条数
  public int GetRowsNum(string sqlstr)
  {
   if (myConn.State == ConnectionState.Closed)
   {
    myConn.Open();
   }
   Fill(sqlstr);
   Close();
   return ds.Tables[0].Rows.Count;
  }
 }
 
//*****************************************************BLL******************************************************************//

 public class BookController
 {
  private DBController obj;

  public BookController()
  {
   obj = new DBController();
  }
         //获取用户最大的借书量
  public int GetMax(string UserID)
  {
   string sqlstr = "select BookNum from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID";
   SqlDataReader myreader = obj.ExecReaderSql(sqlstr);
   int num = 0;
   if (myreader.Read())
   {
    num =(int) myreader["BookNum"];
   }
   obj.Close();
   return num;
  }
          //获取该用户允许借阅时间
  public int GetBookDate(string UserID)
  {
   string sqlstr = "select BookDate from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and UserID='" + UserID + "'";
   SqlDataReader myreader = obj.ExecReaderSql(sqlstr);
   int num = 0;
   if (myreader.Read())
   {
    num =(int) myreader["BookDate"];
   }
   obj.Close();
   return num;
  }
         //判断当前书的状态,-2：表示该书不存在或不在流通中,-1：表示被借,0：已经被续借状态,1：可借
  public int GetBookState(string BookID)
  {
   string sqlstr = "select * from BookInfo where BookState=1 and BookID=" + BookID;
   if (obj.GetRowsNum(sqlstr) == 0)
   {
    return -2;
   }
   sqlstr = "select * from LendInfo where IsBack=0 and BookID =" + BookID;
   if (obj.GetRowsNum(sqlstr) != 0)
   {
    return -1;
   }
   sqlstr = "select * from BookingInfo where BookID=" + BookID;
   if (obj.GetRowsNum(sqlstr) != 0)
   {
    return 0;
   }
   return 1;
  }
           //判断用户是否预定该本书
  public bool IsUserBooking(string UserID, string BookID)
  {
   string sqlstr = "select * from BookingInfo where UserID='" + UserID + "' and BookID=" + BookID;
   if (obj.GetRowsNum(sqlstr) == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
         //获取用户当前借书数量
  public int GetCurNum(string UserID)
  {
   string sqlstr = "select count(*) as Num from LendInfo where UserID='" + UserID + "' and IsBack=0 ";
   SqlDataReader myreader = obj.ExecReaderSql(sqlstr);
   int num = 0;
   if (myreader.Read())
   {
    num =(int) myreader["Num"];
   }
   obj.Close();
   return num;
  }
          //进行借书操作，如果借书成功，返回true，否则返回false
  public bool LendBook(string UserID, string BookID)
  {
   int datenum = GetBookDate(UserID);
   System.DateTime nowdate = System.DateTime.Now;
   System.DateTime ReturnDate = nowdate.AddDays(datenum);
   string sqlstr = "insert into LendInfo(BookID,UserID,LendDate,ReturnDate,IsBack)values( ";
   sqlstr += "'" + BookID + "'";
   sqlstr += ",'" + UserID + "'";
   sqlstr += ",'" + nowdate.ToShortDateString() + "'";
   sqlstr += ",'" + ReturnDate.ToShortDateString() + "'";
   sqlstr += ",0)";
   obj.ExecNonSql(sqlstr);
   if (IsUserBooking(UserID, BookID))
   {
    sqlstr = "delete from BookingInfo where BookID=" + BookID + " and UserID='" + UserID + "'";
    obj.ExecNonSql(sqlstr);
   }
   return true;
  }
           //取消预约书信息
  public bool CancelBooking(string UserID, string BookID)
  {
   if (IsUserBooking(UserID, BookID))
   {
    string sqlstr = "delete from BookingInfo where BookID=" + BookID + " and UserID='" + UserID + "'";
    obj.ExecNonSql(sqlstr);
   }
   return true;
  }
             //续借图书
  public bool ReLendBook(string UserID, string BookID)
  {
   int bookdate = GetBookDate(UserID);
   string sqlstr = "update LendInfo set ReturnDate=ReturnDate+" + bookdate.ToString() + " where IsBack=0 and BookID=" + BookID + " and UserID='" + UserID + "'";
   obj.ExecNonSql(sqlstr);
   return true;
  }
           //还书操作
  public bool ReturnBook(string UserID, string BookID)
  {
   string sqlstr = "update LendInfo set IsBack=1 where BookID=" + BookID + " and UserID='" + UserID + "'";
   obj.ExecNonSql(sqlstr);
         //修改预约状态
   if (this.GetBookState(BookID) == 0)
   {
    sqlstr = "update BookingInfo set BookingState=1 where BookID=" + BookID;
    obj.ExecNonSql(sqlstr);
   }
   return true;
  }
          //预约图书信息
  public void BookingBook(string userID, string BookID)
  {
   string sqlstr = "insert into BookingInfo(BookID,UserID,BookingState)values( ";
   sqlstr += BookID + ",'" + userID + "',0)";
  }
           //是否允许预约
  public bool AllowBooking(string UserID)
  {
   string sqlstr = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and AllowBooking=1 and UserID='" + UserID + "'";
   if (obj.GetRowsNum(sqlstr) == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
           //是否允许续借
  public bool AllowReLend(string UserID)
  {
   string sqlstr = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and ReLend=1 and UserID='" + UserID + "'";
   if (obj.GetRowsNum(sqlstr) == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
          //是否为图书管理员
  public bool IsBookManager(string UserID)
  {
   string sqlstr = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and IsBookManager=1 and UserID='" + UserID + "'";
   if (obj.GetRowsNum(sqlstr) == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
          //是否为系统管理员
  public bool IsSysManager(string UserID)
  {
   string sqlstr = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and IsSysManager=1 and UserID='" + UserID + "'";
   if (obj.GetRowsNum(sqlstr) == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
 }
}