db_reader.cs

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

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

namespace DAL
{
    public class DB_Reader
    {
        private ReaderModel _Reader;

        public ReaderModel Reader
        {
            get { return _Reader; }
            set { _Reader = value; }
        }

        /// <summary>
        /// 根据图书管理编号查询图书信息
        /// </summary>
        /// <returns>返回查询结果DataTable</returns>
        public DataTable Search()
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@CardID", _Reader.CardID) };

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

            return dt;
        }

        public DataTable Select()
        {
            DataTable dt = SqlHelper.Select("T_Reader_SelectAll");

            return dt;
        }

        /// <summary>
        /// 根据借书证号查询读者信息
        /// </summary>
        /// <param name="id">借书证号(CardID)</param>
        /// <returns>返回查询结果DataTable</returns>
        public DataTable Select(string id)
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@CardID", id) };

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

            return dt;
        }

        /// <summary>
        /// 根据借书证号或者姓名查询读者信息
        /// </summary>
        /// <param name="key">条件</param>
        /// <param name="se">字段</param>
        /// <returns>返回查询结果DataTable</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_Reader 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;
        }

        /// <summary>
        /// 插入读者信息
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int Insert()
        {
            SqlParameter[] sqlp = new SqlParameter[] { 
                new SqlParameter("@CardID", _Reader.CardID),
                new SqlParameter("@ReaderName", _Reader.ReaderName),
                new SqlParameter("@Tel", _Reader.Tel) };

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

            return row;
        }

        /// <summary>
        /// 根据读者ID查询读者信息
        /// </summary>
        /// <returns>返回查询结果DataTable</returns>
        public DataTable SearchReaderID()
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@ReaderID", _Reader.ReaderID) };

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

            return dt;
        }

        /// <summary>
        /// 根据读者ID更新读者信息
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int Update()
        {
            SqlParameter[] sqlp = new SqlParameter[] { 
                new SqlParameter("@ReaderID", _Reader.ReaderID),
            new SqlParameter("@ReaderName", _Reader.ReaderName),
            new SqlParameter("@Tel", _Reader.Tel)};

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

            return row;
       }

        /// <summary>
        /// 删除读者信息
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int Delete()
        {
            SqlParameter[] sqlp = new SqlParameter[] { new SqlParameter("@ReaderID", _Reader.ReaderID) };

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

            return row;
        }
    }
}