dbreader.cs

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

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

namespace DAL
{
    public class DBReader
    {
        private ReaderModel _Reader;

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

        public DataTable Search()
        {
            SqlConnection conn = DAL.SqlConnStr.ReturnStrConn();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = conn;
            sda.SelectCommand.CommandText = "select * from T_Reader where CardID=@CardID";
            sda.SelectCommand.Parameters.Add("@CardID", SqlDbType.NVarChar, 12).Value = _Reader.CardID;
            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 DataTable Select()
        {
            SqlConnection conn = DAL.SqlConnStr.ReturnStrConn();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = conn;
            sda.SelectCommand.CommandText = "select * from T_Reader where CardID like '%'+@CardID+'%'";
            sda.SelectCommand.Parameters.Add("@CardID", SqlDbType.NVarChar, 12).Value = _Reader.CardID;
            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()
        {
            SqlConnection conn = DAL.SqlConnStr.ReturnStrConn();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.InsertCommand = new SqlCommand();
            sda.InsertCommand.Connection = conn;
            sda.InsertCommand.CommandText = "INSERT INTO T_READER VALUES(@CardID,@ReaderName,@Tel)";
            sda.InsertCommand.Parameters.Add("@CardID", SqlDbType.NVarChar, 12).Value = _Reader.CardID;
            sda.InsertCommand.Parameters.Add("@ReaderName", SqlDbType.NVarChar, 10).Value = _Reader.ReaderName;
            sda.InsertCommand.Parameters.Add("@Tel", SqlDbType.NVarChar, 16).Value = _Reader.Tel;
            int row = 0;
            try
            {
                conn.Open();
                row = sda.InsertCommand.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw new Exception("插入数据出错", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return row;

        }

        public DataTable SearchReaderID()
        {
            SqlConnection conn = DAL.SqlConnStr.ReturnStrConn();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = conn;
            sda.SelectCommand.CommandText = "select * from T_Reader where ReaderID=@ReaderID";
            sda.SelectCommand.Parameters.Add("@ReaderID", SqlDbType.NVarChar, 12).Value = _Reader.ReaderID;
            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 Update()
        {
            using (SqlConnection conn = SqlConnStr.ReturnStrConn())
            {
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.UpdateCommand = new SqlCommand();
                sda.UpdateCommand.Connection=conn;
                sda.UpdateCommand.CommandText = "update T_Reader set ReaderName=@ReaderName,Tel=@Tel where ReaderID=@ReaderID";
                sda.UpdateCommand.Parameters.Add("@ReaderID", SqlDbType.Int).Value = _Reader.ReaderID;
                sda.UpdateCommand.Parameters.Add("@ReaderName", SqlDbType.NVarChar, 10).Value = _Reader.ReaderName;
                sda.UpdateCommand.Parameters.Add("@Tel", SqlDbType.NVarChar, 16).Value = _Reader.Tel;
                int row = 0;
                try
                {
                    conn.Open();
                    row = sda.UpdateCommand.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw new Exception("更新数据出错", ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return row;
            }
        }

        public int Delete()
        {
            using (SqlConnection conn = SqlConnStr.ReturnStrConn())
            {
             SqlDataAdapter sda = new SqlDataAdapter();
                sda.DeleteCommand = new SqlCommand();
                sda.DeleteCommand.Connection = conn;
                sda.DeleteCommand.CommandText = "delete T_Reader where ReaderID=@ReaderID";
                sda.DeleteCommand.Parameters.Add("@ReaderID", SqlDbType.Int).Value = _Reader.ReaderID;
                int row = 0;
                try
                {
                    conn.Open();
                    row = sda.DeleteCommand.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw new Exception("更新数据出错", ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return row;
            }
        }
    }
}