using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

using FourLayer.BusinessEntity;

namespace FourLayer.DataAccessObject
{

   /// <summary>
   /// Ogólny opis PublisherDAO
   /// </summary>
   public class AuthorDAO : AbstractDAO<Author>
   {
      public AuthorDAO()
      {
      }

      protected override string SelectStatement
      {
         get { return "SELECT AuthorId, AuthorName FROM Authors"; }
      }
      protected override string PrimaryKeyName
      {
         get { return "AuthorId"; }
      }
      public override bool IsGetAllCached
      {
         get { return true; }
      }
      public override string CacheName
      {
         get { return "Authors"; }
      }

      protected override Author CreateAndFillEntity(DbDataReader recordJustRead)
      {
         // pobierz wartości rekordów i umieść je w zmiennych tymczasowych
         int id = recordJustRead.GetInt32(0);
         string name = recordJustRead.GetString(1);

         // utwórz i wypełnij obiekt na podstawie danych
         Author author = new Author(id, name);
         return author;
      }

      public EntityCollection<Author> GetAuthorsForIsbn(string isbn)
      {
         EntityCollection<Author> collection = new EntityCollection<Author>();

         using (DbConnection conn = DatabaseActions.Factory.CreateConnection())
         {
            try
            {
               conn.ConnectionString = DatabaseActions.ConnectionSetting.ConnectionString;
               conn.Open();

               DbCommand cmd = DatabaseActions.Factory.CreateCommand();
               cmd.Connection = conn;
               cmd.CommandText = "SELECT AuthorBooks.AuthorId,AuthorName,ISBN FROM Authors INNER JOIN AuthorBooks ON Authors.AuthorId = AuthorBooks.AuthorId ";
               cmd.CommandText += " WHERE ISBN=@ISBN";
               cmd.CommandType = CommandType.Text;

               // dodaj parametry
               cmd.Parameters.Add(DatabaseActions.MakeParameter("@ISBN",isbn));

               DbDataReader reader = cmd.ExecuteReader();
               if (reader != null)
               {
                  while (reader.Read())
                  {
                     collection.Add(CreateAndFillEntity(reader));
                  }
                  reader.Close();
                  conn.Close();
               }

            }
            catch (Exception ex)
            {
               // wszystkie błędy będą obsługiwane przez niestandardową procedurę obsługi wyjątków
               TwoLayer.DataAccess.DataAccessExceptionHandler.HandleException(ex.Message);
            }
         }
         return collection;
      }


      public override DataTable AdaptCollectionToDataTable(EntityCollection<Author> collection)
      {
         DataTable dt = new DataTable();
         dt.Columns.Add(new DataColumn("Id", Type.GetType("System.Int32")));
         dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));

         foreach (Author a in collection)
         {
            DataRow dr = dt.NewRow();
            dr[0] = a.Id;
            dr[1] = a.Name;
            dt.Rows.Add(dr);
         }
         return dt;
      }

      public override void Update(Author author)
      {
         if (author == null)
            return;

         if (!author.IsValid)
            return;

         string sql = "UPDATE Authors SET AuthorName=@AuthorName";
         sql += " WHERE AuthorId=@AuthorId";

         // utwórz tablicę parametrów
         DbParameter[] parameters = new DbParameter[] {
            DatabaseActions.MakeParameter("@AuthorId", author.Id, DbType.Int32),
            DatabaseActions.MakeParameter("@AuthorName", author.Name, DbType.String)
			};

         // uruchom określoną instrukcję
         DatabaseActions.RunNonQuery(sql, parameters);
      }

      public override void Insert(Author author)
      {
         if (author == null)
            return;

         if (!author.IsValid)
            return;

         string sql = "INSERT INTO Authors (AuthorName) ";
         sql += " VALUES (@AuthorName)";

         / utwórz tablicę parametrów
         DbParameter[] parameters = new DbParameter[] {
            DatabaseActions.MakeParameter("@AuthorName", author.Name, DbType.String)
			};

         // uruchom określoną instrukcję
         DatabaseActions.RunNonQuery(sql, parameters);
      }

      public override void Delete(Author author)
      {
         string sql = "DELETE FROM Authors WHERE AuthorId=@AuthorId";

         / utwórz tablicę parametrów
         DbParameter[] parameters = new DbParameter[] {
            DatabaseActions.MakeParameter("@AuthorId", author.Id, DbType.Int32)
			};

         // uruchom określoną instrukcję
         DatabaseActions.RunNonQuery(sql, parameters);
      }
   }
}