using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using Model;
using IDAL;

namespace DAL
{
    /// <summary>
    /// Access留言本數(shù)據(jù)訪問層
    /// </summary>
    public class AccessGuestBook : Iguestbook //繼承Iguestbook接口
    {
        private string connectionString =  System.Configuration.ConfigurationSettings.AppSettings["accessConectionString"] + System.Web.HttpContext.Current.Server.MapPath("/App_Data/myPage.mdb");
        

        /// <summary>
        /// 讀取數(shù)據(jù)庫(kù)中所有留言
        /// </summary>
        /// <returns>返回guestbool數(shù)組</returns>
        public guestbook[] getListGuestBook()
        {
            using (OleDbConnection oleConn = new OleDbConnection(connectionString))
            {
                OleDbCommand oleCmd = new OleDbCommand("select * from guestbook order by id desc", oleConn);
                oleConn.Open();
                
                ArrayList guest = new ArrayList();
                OleDbDataReader oleDdr = oleCmd.ExecuteReader();
                while (oleDdr.Read())
                {
                    guestbook gb = new guestbook();
                    gb.Id = oleDdr["id"].ToString();
                    gb.Name = oleDdr["name"].ToString();
                    gb.Time =DateTime.Parse (oleDdr["times"].ToString());
                    gb.Ip = oleDdr["ip"].ToString();
                    gb.Content = oleDdr["content"].ToString();
                    gb.Recontent = oleDdr["recontent"].ToString();
                    gb.Pic = oleDdr["pic"].ToString();
                    gb.Email = oleDdr["email"].ToString();
                    guest.Add(gb);
                }
                oleDdr.Close();
            
            return (guestbook[])guest.ToArray(typeof(guestbook));
            }

        }

        /// <summary>
        /// 讀取一條留言內(nèi)容
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public guestbook[] getOnly(string id)
        {
            using (OleDbConnection oleConn = new OleDbConnection(connectionString))
            {
                OleDbCommand oleCmd = new OleDbCommand("select * from guestbook where id=" + id, oleConn);
                oleConn.Open();

                ArrayList guest = new ArrayList();
                OleDbDataReader oleDdr = oleCmd.ExecuteReader();
                while (oleDdr.Read())
                {
                    guestbook gb = new guestbook();
                    gb.Id = oleDdr["id"].ToString();
                    gb.Name = oleDdr["name"].ToString();
                    gb.Time =DateTime .Parse ( oleDdr["times"].ToString());
                    gb.Ip = oleDdr["ip"].ToString();
                    gb.Content = oleDdr["content"].ToString();
                    gb.Recontent = oleDdr["recontent"].ToString();
                    gb.Pic = oleDdr["pic"].ToString();
                    gb.Email = oleDdr["email"].ToString();
                    guest.Add(gb);
                }
                oleDdr.Close();

                return (guestbook[])guest.ToArray(typeof(guestbook));
            }
        }

        /// <summary>
        /// 添加留言內(nèi)容
        /// </summary>
        /// <param name="gb">留言實(shí)體對(duì)像</param>
       public  void setGuestBook(guestbook gb)
        {
           using(OleDbConnection oleCon=new OleDbConnection (this.connectionString ))
           {
               string sql = "insert into guestbook (name,times,ip,content,pic,email) values ( @name , @times , @ip , @content , @pic ,@email)";
               OleDbParameter oleParam0 = new OleDbParameter("@name", gb.Name);
               OleDbParameter oleParam1 = new OleDbParameter("@times", gb.Time);
               OleDbParameter oleParam2 = new OleDbParameter("@ip", gb.Ip);
               OleDbParameter oleParam3 = new OleDbParameter("@content", gb.Content );
               OleDbParameter oleParam4 = new OleDbParameter("@pic",gb.Pic );
               OleDbParameter oleParam5 = new OleDbParameter("@email", gb.Email );
               OleDbCommand oleCmd = new OleDbCommand();
               oleCmd.CommandText = sql;

               oleCmd.Parameters.Add(oleParam0);
               oleCmd.Parameters.Add(oleParam1);
               oleCmd.Parameters.Add(oleParam2);
               oleCmd.Parameters.Add(oleParam3);
               oleCmd.Parameters.Add(oleParam4);
               oleCmd.Parameters.Add(oleParam5);
               oleCon.Open();
               oleCmd.Connection = oleCon;
               oleCmd.ExecuteNonQuery();
           }
        }

       /// <summary>
       /// 回復(fù)留言
       /// </summary>
       /// <param name="Regb"></param>
       public void setReGuestBook(guestbook Regb)
        {
            using (OleDbConnection oleCon = new OleDbConnection(this.connectionString))
            {
                string sql = "update guestbook set  recontent=@recontent where id=" + Regb.Id;
                OleDbParameter oleParam0 = new OleDbParameter("@recontent", Regb.Recontent);
                OleDbCommand oleCmd = new OleDbCommand();
                oleCmd.CommandText = sql;
                oleCmd.Parameters.Add(oleParam0);
                oleCon.Open();
                oleCmd.Connection = oleCon;
                oleCmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 刪除留言
        /// </summary>
        /// <param name="id">留言id號(hào)</param>
        /// <returns></returns>
       public  bool delGuestBook(string id)
       {
           using (OleDbConnection oleCon = new OleDbConnection(this.connectionString))
           {
               string sql = "delete from [guestbook] where id=@id";
               OleDbParameter oleParam0 = new OleDbParameter("@id", id);
               OleDbCommand oleCmd = new OleDbCommand();
               oleCmd.CommandText = sql;
               oleCmd.Parameters.Add(oleParam0);
               oleCon.Open();
               oleCmd.Connection = oleCon;
               try
               {
                   oleCmd.ExecuteNonQuery();
                   return true;
               }
               catch
               {
                   return false;
               }
           }
           
       }
    }
}