/src/BurnSystems.EntityConnector/EntityPool.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using BurnSystems.AdoNet.Queries;

using System.Data.SqlClient;

using System.Data;



namespace BurnSystems.EntityConnector

{

    /// <summary>

    /// Gives access to a bunch of entity

    /// </summary>

    public class EntityPool

    {

        /// <summary>

        /// Defines the connectionstring

        /// </summary>

        private string connectionString;



        /// <summary>

        /// Gets the poolname

        /// </summary>

        public string PoolName

        {

            get;

            private set;

        }



        /// <summary>

        /// Initializes a new instance of EntityPool class.

        /// </summary>

        /// <param name="connectionString">Connectionstring to be used for database access</param>

        public EntityPool(string poolName, string connectionString)

        {

            this.PoolName = poolName;

            this.connectionString = connectionString;

        }



        /// <summary>

        /// Adds an entity to pool

        /// </summary>

        /// <param name="entity">Entity to be added</param>

        public void Add(Entity entity)

        {

            using (var db = new SqlConnection(this.connectionString))

            {

                db.Open();



                var data = new Dictionary<string, object>();

                data["Pool"] = this.PoolName;



                if (entity.Id == 0)

                {

                    var selectQuery = new FreeQuery("SELECT MAX(Id) FROM Entities WHERE Pool=@Pool GROUP BY Pool");

                    selectQuery.AddParameter("@Pool", this.PoolName);

                    long maxId = db.ExecuteScalar<long>(selectQuery);

                    data["Id"] = maxId + 1;

                    entity.Id = maxId + 1;

                }

                else

                {

                    data["Id"] = entity.Id;

                }



                foreach (var entry in entity.Data)

                {

                    data["DataKey"] = entry.Key;

                    data["DataValue"] = entry.Value;

                    var query = new InsertQuery("Entities", data);

                    db.ExecuteNonQuery(query);

                }

            }

        }



        public void Delete(Entity entity)

        {

            this.Delete(entity.Id);

        }



        private void Delete(long id)

        {

            using (var db = new SqlConnection(this.connectionString))

            {

                db.Open();



                var where = new Dictionary<string, object>();

                where["Pool"] = this.PoolName;

                where["Id"] = id;

                var deleteQuery = new DeleteQuery("Entities", where);

                db.ExecuteNonQuery(deleteQuery);

            }

        }



        public void Update(Entity entity)

        {

            // Quite simple

            this.Delete(entity);

            this.Add(entity);

        }



        /// <summary>

        /// Gets by datareader

        /// </summary>

        /// <param name="reader">Datareader to be used</param>

        /// <returns>List of entities</returns>

        public IList<Entity> Get(IDataReader reader)

        {

            var entities = new Dictionary<long, Entity>();



            while (reader.Read())

            {

                var id = Convert.ToInt64(reader["Id"]);

                var poolName = reader["Pool"].ToString();

                var key = reader["DataKey"].ToString();

                var value = reader["DataValue"].ToString();



                if (poolName != this.PoolName)

                {

                    continue;

                }



                Entity found;

                if (!entities.TryGetValue(id, out found))

                {

                    found = new Entity();

                    found.Id = id;

                    entities[id] = found;

                }



                found[key] = value;

            }



            return entities.Select(x => x.Value).ToList();

        }



        public IList<Entity> Get(Query query)

        {

            using (var db = new SqlConnection(this.connectionString))

            {

                db.Open();



                using (var reader = db.ExecuteReader(query))

                {

                    return this.Get(reader);

                }

            }

        }



        public IList<Entity> GetAll()

        {

            var where = new Dictionary<string, object>();

            where["Pool"] = this.PoolName;



            var query = new SelectQuery("Entities", where);

            query.OrderBy = "Id";

            return this.Get(query);

        }



        public Entity GetById(long id)

        {

            var where = new Dictionary<string, object>();

            where["Pool"] = this.PoolName;

            where["Id"] = id;



            return this.Get(new SelectQuery("Entities", where)).FirstOrDefault();

        }



        public IList<Entity> GetAllWith(string key, string value)

        {

            var freeQuery = new FreeQuery(

                @"SELECT [t0].[Id], [t0].[Pool], [t0].[DataKey], [t0].[DataValue]

                FROM [Entities] AS [t0]

                WHERE (EXISTS(

                    SELECT NULL AS [EMPTY]

                    FROM [Entities] AS [t1]

                    WHERE ([t1].[Id] = [t0].[Id]) AND ([t1].[DataKey] = @p0) AND ([t1].[DataValue] = @p1) AND ([t1].[Pool] = @p2)

                    )) AND ([t0].[Pool] = @p3)");

            freeQuery.AddParameter("@p0", key);

            freeQuery.AddParameter("@p1", value);

            freeQuery.AddParameter("@p2", this.PoolName);

            freeQuery.AddParameter("@p3", this.PoolName);

            return this.Get(freeQuery);

        }

    }

}