gistfile1.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;

using ChessOk.ModelFramework;
using ChessOk.ModelFramework.Web.ClientEvents;

namespace ChessOk.ChessSchool.Web.ClientEvents.Storage
{
    public class ChessSchoolClientEventsStore : IClientEventsStore
    {
        private static readonly string ConnectionStringName = "DBConnection";

        public void Add(string name, string data, DateTime createdAt, int? correlationId, int targetId, bool guaranteed)
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            using (var command = connection.CreateCommand())
            {
                command.CommandText =
                    "INSERT INTO [ClientEvents] ([Name], [Data], [CreatedAt], [CorrelationId], [TargetId], [IsGuaranteedAndDelivered]) VALUES " +
                    "(@Name, @Data, @CreatedAt, @CorrelationId, @TargetId, @IsGuaranteedAndDelivered)";

                command.Parameters.Add(new SqlParameter("@Name", name));
                command.Parameters.Add(new SqlParameter("@Data", data));
                command.Parameters.Add(new SqlParameter("@CreatedAt", createdAt));
                command.Parameters.Add(
                    correlationId.HasValue
                        ? new SqlParameter("@CorrelationId",  correlationId.Value)
                        : new SqlParameter("@CorrelationId", DBNull.Value));
                command.Parameters.Add(new SqlParameter("@TargetId", targetId));
                command.Parameters.Add(
                    guaranteed
                        ? new SqlParameter("@IsGuaranteedAndDelivered", false)
                        : new SqlParameter("@IsGuaranteedAndDelivered", DBNull.Value));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public ClientEventsCollection Get(DateTime startDate, DateTime endDate, int targetId)
        {
            var events = new List<ClientEventDto>();

            using (var connection = new SqlConnection(GetConnectionString()))
            {
                // Сперва запросим события
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT [Name], [CorrelationId], [CreatedAt], [Data] FROM [ClientEvents] "
                                          + "WHERE [CreatedAt] > @StartDate AND [CreatedAt] <= @EndDate "
                                          + "AND [TargetId] = @TargetId " + "UNION "
                                          + "SELECT [Name], [CorrelationId], [CreatedAt], [Data] FROM [ClientEvents] "
                                          + "WHERE [IsGuaranteedAndDelivered] = 0 "
                                          + "AND [TargetId] = @TargetId "
                                          + "ORDER BY [CreatedAt]";

                    command.Parameters.Add(new SqlParameter("@StartDate", startDate));
                    command.Parameters.Add(new SqlParameter("@EndDate", endDate));
                    command.Parameters.Add(new SqlParameter("@TargetId", targetId));

                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        var nameIndex = reader.GetOrdinal("Name");
                        var correlationIdIndex = reader.GetOrdinal("CorrelationId");
                        var createdAtIndex = reader.GetOrdinal("CreatedAt");
                        var dataIndex = reader.GetOrdinal("Data");

                        while (reader.Read())
                        {
                            events.Add(
                                new ClientEventDto
                                {
                                    Name = reader.GetString(nameIndex),
                                    CorrelationId = reader.IsDBNull(correlationIdIndex) ? (int?)null : reader.GetInt32(correlationIdIndex),
                                    CreatedAt = reader.GetDateTime(createdAtIndex),
                                    Data = reader.IsDBNull(dataIndex) ? string.Empty : reader.GetString(dataIndex)
                                });
                        }
                    }
                }

                // Потом проставим факт доставки гарантированным
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "UPDATE [ClientEvents] SET [IsGuaranteedAndDelivered] = 1 "
                        + "WHERE [CreatedAt] > @StartDate AND [CreatedAt] <= @EndDate AND [TargetId] = @TargetId "
                        + "AND [IsGuaranteedAndDelivered] = 0";
                    command.Parameters.Add(new SqlParameter("@StartDate", startDate));
                    command.Parameters.Add(new SqlParameter("@EndDate", endDate));
                    command.Parameters.Add(new SqlParameter("@TargetId", targetId));

                    command.ExecuteNonQuery();
                }
            }

            return new ClientEventsCollection { Events = events.ToArray(), FetchDate = endDate };
        }

        private string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
        }
    }
}