db_book.cs

这是一个很好的图书管理系统 数据库SQL2005

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PublicClass;

namespace DAL
{
    public class DB_Book
    {
        private BookModel _Book;

        public BookModel Book
        {
            get { return _Book; }
            set { _Book = value; }
        }
        /// <summary>
        /// 根据管理编号查询图书
        /// </summary>
        /// <returns>返回查询结果DataTable</returns>
        public DataTable Search()
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@ManID", _Book.ManID) };

            DataTable dt = SqlHelper.Select(sqlp, "T_Book_SelectManID");

            return dt;
        }

        /// <summary>
        /// 查询所有图书信息
        /// </summary>
        /// <returns>返回DataTable</returns>
        public DataTable Select()
        {
            DataTable dt = SqlHelper.Select("T_Book_Select");

            return dt;
        }

        /// <summary>
        /// 根据管理编号查询图书信息
        /// </summary>
        /// <param name="id">管理编号</param>
        /// <returns>返回DataTable</returns>
        public DataTable Select(string id)
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@ManID", id) };

            DataTable dt = SqlHelper.Select(sqlp, "T_Book_SelectID");

            return dt;
        }

        /// <summary>
        /// 根据管理编号或图书名查询图书信息
        /// </summary>
        /// <param name="key">关键字</param>
        /// <param name="se">条件</param>
        /// <returns>返回查询结果</returns>
        public DataTable Select(string key,string se)
        {
            SqlConnection conn = new SqlConnection(DAL.SqlConnStr.ReturnConnStr());
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = conn;
            sda.SelectCommand.CommandText = "select * from T_Book where " + se + " like '%'+@key+'%'";
            sda.SelectCommand.Parameters.Add("@key", SqlDbType.NVarChar, 12).Value = key;
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                sda.Fill(dt);
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("检索数据发生错误", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return dt;
        }

        public int Insert()
        {
            SqlParameter[] sqlp = new SqlParameter[] { 
                new SqlParameter("@ManID", _Book.ManID),
                new SqlParameter("@BookName", _Book.BookName),
                new SqlParameter("@Writer", _Book.Writer) };

            int row = SqlHelper.Insert(sqlp, "T_Book_Insert");

            return row;
       }

        /// <summary>
        /// 根据图书ID查询图书信息
        /// </summary>
        /// <returns>返回DataTable</returns>
        public DataTable SearchBookID()
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@BookID", _Book.BookID) };

            DataTable dt = SqlHelper.Select(sqlp, "T_Book_SelectBookID");

            return dt;
        }

        /// <summary>
        /// 根据图书编号更新图书信息
        /// </summary>
        /// <returns>返回受影响行数</returns>
        public int Update()
        {
            SqlParameter[] sqlp = new SqlParameter[] { 
                new SqlParameter("@BookID", _Book.BookID),
            new SqlParameter("@BookName", _Book.BookName),
            new SqlParameter("@Writer", _Book.Writer)};

            int row = SqlHelper.Update(sqlp, "T_Book_Update");

            return row;
        }

        /// <summary>
        /// 根据图书编号删除图书
        /// </summary>
        /// <returns>返回受影响行数</returns>
        public int Delete()
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@BookID", _Book.BookID) };

            int row = SqlHelper.Delete(sqlp, "T_Book_Delete");

            return row;
        }

    }
}