using System;
using System.Data;
using System.Data.SqlClient ;
using System.Data.Common  ;

using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// GuestBookManager 的摘要说明
/// </summary>
public class GuestBookManager
{
    public static GuestBookList GetList() 
    {
        GuestBookList tempList = null;

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);
                    
        string mySql="select * from GuestBook  ";
        SqlCommand myCommand = new SqlCommand(mySql, myConnection);
       
        myConnection.Open();
        SqlDataReader myReader = null;

        myReader = myCommand.ExecuteReader();
        
        if (myReader.HasRows) 
        {
            tempList = new GuestBookList();
            
            while (myReader.Read())
                tempList.Add(FillDataRecord (myReader )); 
        }

        myReader.Close();
        myConnection.Close(); 

        return tempList;

    }

    
    public static int Save(GuestBook myGuestBook)
    {
        int result = 0;

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        SqlCommand myCommand = new SqlCommand("sprocGuestBookUpdateSingleItem", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;

        if (myGuestBook.Id == -1)
            myCommand.Parameters.AddWithValue("@id", DBNull.Value);
        else
            myCommand.Parameters.AddWithValue("@id",myGuestBook.Id );

        myCommand.Parameters.AddWithValue("@email", myGuestBook.Email );
        myCommand.Parameters.AddWithValue("@userName", myGuestBook.UserName );
        myCommand.Parameters.AddWithValue("@subject", myGuestBook.Subject) ;
        myCommand.Parameters.AddWithValue("@message", myGuestBook.Message );
        myCommand.Parameters.AddWithValue("@reply", myGuestBook.Reply );
        myCommand.Parameters.AddWithValue("@imageUrl", myGuestBook.ImageUrl );
        myCommand.Parameters.AddWithValue("@postTime", System.DateTime.Now   );

        DbParameter returnValue = myCommand.CreateParameter();
        returnValue.Direction = ParameterDirection.ReturnValue;
        myCommand.Parameters.Add(returnValue );
        
        myConnection.Open();

        myCommand.ExecuteNonQuery();
        result = Convert.ToInt32(returnValue.Value  ); 

        myConnection.Close(); 

        return result;

    }

    public static bool Delete(int id)
    {
        int result = 0;

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        SqlCommand myCommand = new SqlCommand("sprocGuestBookDeleteItem", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@id", id);

        myConnection.Open();

        result = myCommand.ExecuteNonQuery();

        myConnection.Close(); 

        return result > 0;
    }

    public static bool Reply(int id, string reply) 
    {
        bool result;
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        SqlCommand myCommand = new SqlCommand();

        myCommand.Connection = myConnection;
        myCommand.CommandText=" update GuestBook set reply=@reply where id=@id";

        myCommand.Parameters.AddWithValue("@id", id);
        myCommand.Parameters.AddWithValue("@reply", reply );  

        myConnection.Open();

        if (Convert.ToInt32(myCommand.ExecuteNonQuery()) > 0)
            result  = true;
        else 
            result  = false;
        
        myConnection.Close(); 
        
        return result ;
    }

    public static PagedDataSource SetPage()
    {
        PagedDataSource pagedDS = new PagedDataSource();
        pagedDS.AllowPaging = true;
        pagedDS.PageSize = 3;
        pagedDS.DataSource =GetList ();
       
        return pagedDS ;
    }

    public static int GetPageCount()
    {       
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        string mySql = "select count(*) from GuestBook ";
        SqlCommand myCommand = new SqlCommand(mySql, myConnection);

        myConnection.Open();

        int tempInt =Convert.ToInt32 ( myCommand.ExecuteScalar());

        return tempInt;

    }

    private static GuestBook FillDataRecord(IDataRecord myDataRecord) 
    {
        GuestBook myGuestBook = new GuestBook();

         
        myGuestBook.Id = myDataRecord.GetInt32(myDataRecord.GetOrdinal ("Id") );

        int indexUserName = myDataRecord.GetOrdinal("UserName");
        if (myDataRecord.IsDBNull(indexUserName))
            myGuestBook.UserName = "";
        else
            myGuestBook.UserName = myDataRecord.GetString(indexUserName);

        int indexSubject = myDataRecord.GetOrdinal("Subject");
        if ( myDataRecord.IsDBNull (indexSubject )  )
            myGuestBook.Subject ="" ;
        else
            myGuestBook.Subject = myDataRecord.GetString(indexSubject );


        int indexEmail = myDataRecord.GetOrdinal("Email");
        if (myDataRecord.IsDBNull(indexEmail))
            myGuestBook.Email  = "";
        else
            myGuestBook.Email  = myDataRecord.GetString(indexEmail);

        int indexMessage = myDataRecord.GetOrdinal("Message");
        if (myDataRecord.IsDBNull(indexMessage))
            myGuestBook.Message  = "";
        else
            myGuestBook.Message  = myDataRecord.GetString(indexMessage);


        int indexReply = myDataRecord.GetOrdinal("Reply");
        if (myDataRecord.IsDBNull(indexReply))
            myGuestBook.Reply  = "";
        else
            myGuestBook.Reply  = myDataRecord.GetString(indexReply);


        myGuestBook.PostTime  = myDataRecord.GetValue (myDataRecord.GetOrdinal("PostTime")).ToString ();
        myGuestBook.ImageUrl  = myDataRecord.GetString(myDataRecord.GetOrdinal("ImageUrl"));

        return myGuestBook;
    }
}
