Exemplo n.º 1
0
        //TODO Либо генерировать у пользователя GUID и передавать его, либо генерировать его здесь и возвращать как результат операции
        //TODO Соответственно, MessageInChat либо генерируется по запросам клиента, либо "вшить" генерацию внутрь контроллера
        //TODO (когда посылаем и создаем сообщениеб тогда же и создаем MsgInChat)
        public CMessageInfo CreateMessage(CMessageInfo message)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(CreateMessage)}({message})' is called");

            #region SQL

            var sql = @"
INSERT INTO messages (Id, DispatchDate, MessageText, Type, ContentUri)
    VALUES (
@Id, @DispatchDate, @MessageText, @Type, @ContentUri
);
SELECT USN 
FROM messages 
    WHERE Id = @Id;
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var outputId = Guid.NewGuid();

                        var result = executor.ExecuteScalar <Int64>(sql,
                                                                    SSqlParameterCreator.Create(
                                                                        "@Id", outputId, SqlDbType.UniqueIdentifier, false, ParameterDirection.Input
                                                                        ),
                                                                    SSqlParameterCreator.Create(
                                                                        "@DispatchDate", message.DispatchDate, SqlDbType.DateTimeOffset, false, ParameterDirection.Input
                                                                        ),
                                                                    SSqlParameterCreator.Create(
                                                                        "@MessageText", message.MessageText, SqlDbType.NVarChar, true, ParameterDirection.Input, 2038
                                                                        ),
                                                                    SSqlParameterCreator.Create(
                                                                        "@Type", message.Type, SqlDbType.TinyInt, false
                                                                        ),
                                                                    SSqlParameterCreator.Create(
                                                                        "@ContentUri", message.ContentUri, SqlDbType.NVarChar, true, ParameterDirection.Input, 2038
                                                                        )
                                                                    );

                        executor.Commit();

                        return(new CMessageInfo(outputId, message.DispatchDate, message.MessageText, message.Type,
                                                message.ContentUri, message.FromUserId, message.IsRead, message.Login, result));
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(CreateMessage)}({message}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(CreateMessage)}({message}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(CreateMessage)}({message}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
        //TODO Just search chats with type = 0 (Dialog)
        public IList <CContactInfo> GetHasDialogContacts(Guid ownerId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(DeleteContact)}({ownerId})' is called");

            #region SQL Query
            var sql = @"
SELECT 
    [Id] = c.Id,
    [OwnerId] = c.OwnerId,
    [UserId] = c.UserId,
    [IsBlocked] = c.IsBlocked
FROM (
    SELECT DISTINCT 
        p.ChatId
    FROM chatsParticipants p 

    INNER JOIN chatsParticipants p2 

    ON p.ChatId = p2.ChatId
        WHERE p.UserId = @OwnerId
        GROUP BY p.ChatId
        HAVING COUNT(p.ChatId) = 2
    ) as res

INNER JOIN chatsParticipants p 
ON res.ChatId = p.ChatId

INNER JOIN contactsLists c
ON p.UserId = c.UserId
    WHERE p.UserId != @OwnerId
    AND c.OwnerId = @OwnerId
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CContactInfoMapper(), sql,
                                                      SSqlParameterCreator.Create(
                                                          "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
                                                          )
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(DeleteContact)}({ownerId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(DeleteContact)}({ownerId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(DeleteContact)}({ownerId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 3
0
        public Guid CreateUser(CUserInfo user)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(CreateUser)}({user})' is called");

            #region SQL Query
            var sql = @"
INSERT INTO users (Id, Login, Password, LastActiveDate, ActivityStatus, Avatar)
    VALUES (
@Id, @Login, @Password, @LastActiveDate, @ActivityStatus, @Avatar
)
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var newId = Guid.NewGuid();

                        var result = executor.CreateItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@Id", newId, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@Login", user.Login, System.Data.SqlDbType.NVarChar, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@Password", user.Password, System.Data.SqlDbType.NVarChar, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@LastActiveDate", user.LastActiveDate, System.Data.SqlDbType.DateTimeOffset, true
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@ActivityStatus", user.ActivityStatus, System.Data.SqlDbType.TinyInt, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@Avatar", user.Avatar, System.Data.SqlDbType.NVarChar, true
                                                             )
                                                         );

                        executor.Commit();
                        return(newId);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(CreateUser)}({user}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(CreateUser)}({user}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(CreateUser)}({user}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(default(Guid));
                    }
                }
            }
        }
Exemplo n.º 4
0
        public CMessageInfo GetLastMessageFromChat(Guid chatId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetLastMessageFromChat)}({chatId})' is called");

            #region Sql

            var sql = @"
SELECT DISTINCT
    [Id] = m.Id,
    [DispatchDate] = m.DispatchDate,
    [MessageText] = m.MessageText,
    [Type] = m.Type,
    [ContentUri] = m.ContentUri,
    [FromUserId] = c.FromUserId,
    [IsRead] = c.IsRead,
    [Login] = u.Login,
    [USN] = m.USN
FROM messagesInChats c

INNER JOIN messages m
ON c.MessageId = m.Id

INNER JOIN users u
ON c.FromUserId = u.Id
    WHERE c.ChatId = @ChatId
ORDER BY m.DispatchDate DESC
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetItem(new CMessageInfoMapper(), sql,
                                                      SSqlParameterCreator.Create("@ChatId", chatId, SqlDbType.UniqueIdentifier, false)
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetLastMessageFromChat)}({chatId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetLastMessageFromChat)}({chatId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetLastMessageFromChat)}({chatId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
        public CChatInfo CreateChat(CChatInfo chatInfo)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(CreateChat)}({chatInfo})' is called");
            #region SQL Query
            var sqlQuery = @"
INSERT INTO 
    chats
VALUES (
   @Id, @Title, @OwnerId, @IsPersonal, @Type
)
";
            #endregion


            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var id     = Guid.NewGuid();
                        var result = executor.CreateItem(sqlQuery,
                                                         SSqlParameterCreator.Create(
                                                             "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@Title", chatInfo.Title, System.Data.SqlDbType.NVarChar, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@OwnerId", chatInfo.OwnerId, System.Data.SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@IsPersonal", chatInfo.IsPersonal, System.Data.SqlDbType.Bit, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@Type", chatInfo.Type, System.Data.SqlDbType.TinyInt, false
                                                             )
                                                         );

                        executor.Commit();

                        return(new CChatInfo(id, chatInfo.Title, chatInfo.OwnerId, chatInfo.IsPersonal, chatInfo.Type));
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(CreateChat)}({chatInfo}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(CreateChat)}({chatInfo}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(CreateChat)}({chatInfo}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
        //        #region Static
        //        public static Int32 CreateMessageInChat(CMessageInChatInfo messageInChat)
        //        {
        //            #region Sql

        //            var sql = @"
        //INSERT INTO messagesInChats (Id, MessageId, ChatId, FromUserId, ToUserId, IsRead)
        //    VALUES (
        //DEFAULT, @MessageId, @ChatId, @FromUserId, @ToUserId, @IsRead
        //)
        //";

        //            #endregion

        //            return CDbQueryExecutor.CreateItemParametrized(sql,
        //                SSqlParameterCreator.Create(
        //                    "@MessageId", messageInChat.MessageId, SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@ChatId", messageInChat.ChatId, SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@FromUserId", messageInChat.FromUserId, SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@ToUserId", messageInChat.ToUserId, SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@IsRead", messageInChat.IsRead, SqlDbType.Bit, false
        //                    )
        //            );
        //        }

        //        public static Int32 UpdateReadMessageInChat(CMessageInChatInfo messageInChat)
        //        {
        //            #region Sql

        //            var sql = @"
        //UPDATE messagesInChats
        //SET IsRead = @IsRead
        //    WHERE ToUserId = @ToUserId
        //    AND MessageId = @MessageId
        //";

        //            #endregion

        //            return CDbQueryExecutor.CreateItemParametrized(sql,
        //                SSqlParameterCreator.Create(
        //                    "@MessageId", messageInChat.MessageId, SqlDbType.UniqueIdentifier, false
        //                ),
        //                SSqlParameterCreator.Create(
        //                    "@ToUserId", messageInChat.ToUserId, SqlDbType.UniqueIdentifier, false
        //                ),
        //                SSqlParameterCreator.Create(
        //                    "@IsRead", messageInChat.IsRead, SqlDbType.Bit, false
        //                )
        //            );
        //        }
        //        #endregion

        public Int32 CreateMessageInChat(CMessageInChatInfo messageInChat)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(CreateMessageInChat)}({messageInChat})' is called");

            #region Sql

            var sql = @"
INSERT INTO messagesInChats (Id, MessageId, ChatId, FromUserId, ToUserId, IsRead)
    VALUES (
DEFAULT, @MessageId, @ChatId, @FromUserId, @ToUserId, @IsRead
)
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.CreateItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@MessageId", messageInChat.MessageId, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@ChatId", messageInChat.ChatId, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@FromUserId", messageInChat.FromUserId, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@ToUserId", messageInChat.ToUserId, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@IsRead", messageInChat.IsRead, SqlDbType.Bit, false
                                                             )
                                                         );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(CreateMessageInChat)}({messageInChat}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(CreateMessageInChat)}({messageInChat}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(CreateMessageInChat)}({messageInChat}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
Exemplo n.º 7
0
        public IList <CUserInfo> GetContactsLastActiveDate(Guid ownerId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetContactsLastActiveDate)}({ownerId})' is called");

            #region SQL Query

            var sql = @"
SELECT 
    [Id] = u.Id,
    [Login] = u.Login,
    [Password] = u.Password,
    [LastActiveDate] = u.LastActiveDate,
    [ActivityStatus] = u.ActivityStatus,
    [Avatar] = u.Avatar
FROM contactsLists c 

INNER JOIN users u

ON c.UserId = u.Id 
    WHERE c.OwnerId = @OwnerId
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CUserInfoMapper(), sql,
                                                      SSqlParameterCreator.Create(
                                                          "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
                                                          )
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetContactsLastActiveDate)}({ownerId}): Error occured during SQL query execution",
                                       e);
                        s_log.LogInfo(
                            $@"{nameof(GetContactsLastActiveDate)}({ownerId}): Operation was rolled back because of error");
                        Console.WriteLine(
                            $@"{nameof(GetContactsLastActiveDate)}({ownerId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 8
0
        //        #region Static
        //        public static Int32 CreateChatParticipant(CChatsParticipantInfo chatParticipantInfo)
        //        {
        //            #region Sql

        //            var sql = @"
        //INSERT INTO chatsParticipants (Id, ChatId, UserId)
        //    VALUES (
        //@Id, @ChatId, @UserId
        //)
        //";

        //            #endregion

        //            var id = Guid.NewGuid();

        //            return CDbQueryExecutor.CreateItem(sql,
        //                SSqlParameterCreator.Create(
        //                    "@Id", id, SqlDbType.UniqueIdentifier, false
        //                ),
        //                SSqlParameterCreator.Create(
        //                    "@ChatId", chatParticipantInfo.ChatId, SqlDbType.UniqueIdentifier, false
        //                ),
        //                SSqlParameterCreator.Create(
        //                    "@UserId", chatParticipantInfo.UserId, SqlDbType.UniqueIdentifier, false
        //                )
        //            );
        //        }
        //        #endregion

        public Int32 CreateChatParticipant(CChatsParticipantInfo chatParticipantInfo)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(CreateChatParticipant)}({chatParticipantInfo})' is called");

            #region Sql

            var sql = @"
INSERT INTO chatsParticipants (Id, ChatId, UserId)
    VALUES (
@Id, @ChatId, @UserId
)
";

            #endregion

            var id = Guid.NewGuid();

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.CreateItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@Id", id, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@ChatId", chatParticipantInfo.ChatId, SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@UserId", chatParticipantInfo.UserId, SqlDbType.UniqueIdentifier, false
                                                             )
                                                         );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(CreateChatParticipant)}({chatParticipantInfo}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(CreateChatParticipant)}({chatParticipantInfo}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(CreateChatParticipant)}({chatParticipantInfo}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
Exemplo n.º 9
0
        //        public static CUserInfo GetUserByAuthData(String login, String password)
        //        {
        //            s_log.LogInfo($"{System.Reflection.MethodBase.GetCurrentMethod().ToString()}({login}, {password}) is called");

        //            #region SQL Query
        //            var sql = @"
        //SELECT
        //    [Id] = u.Id,
        //    [Login] = u.Login,
        //    [Password] = u.Password,
        //    [LastActiveDate] = u.LastActiveDate,
        //    [ActivityStatus] = u.ActivityStatus,
        //    [Avatar] = u.Avatar
        //FROM users AS u
        //    WHERE u.Login = @Login
        //    AND u.Password = @Password
        //";
        //            #endregion

        //            var result = CDbQueryExecutor.GetItemParametrized(new CUserInfoMapper(), sql,
        //                SSqlParameterCreator.Create(
        //                    "@Login", login, System.Data.SqlDbType.NVarChar, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@Password", password, System.Data.SqlDbType.NVarChar, false
        //                    )
        //                );
        //            return result;
        //        }

        //        public static Int32 UpdateUserLastActiveDate(Guid userId, DateTimeOffset lastActiveDate)
        //        {
        //            var sql = @"
        //UPDATE users

        //SET LastActiveDate = (CASE
        //        WHEN LastActiveDate < @LastActiveDate
        //        THEN @LastActiveDate
        //        ELSE LastActiveDate
        //    END)
        //    WHERE Id = @UserId
        //";


        //            return CDbQueryExecutor.UpdateItemParametrized(sql,
        //                SSqlParameterCreator.Create(
        //                    "@Id", userId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@LastActiveDate", lastActiveDate, SqlDbType.DateTimeOffset, true
        //                    )
        //                );
        //        }

        //        public static CUserInfo GetUserData(Guid id)
        //        {
        //            #region Sql

        //            var sql = @"
        //SELECT
        //    [Id] = u.Id,
        //    [Login] = u.Login,
        //    [Password] = u.Password,
        //    [LastActiveDate] = u.LastActiveDate,
        //    [ActivityStatus] = u.ActivityStatus,
        //    [Avatar] = u.Avatar
        //FROM users AS u
        //    WHERE u.Id = @Id
        //";

        //            #endregion

        //            return CDbQueryExecutor.GetItemParametrized(new CUserInfoMapper(), sql,
        //                SSqlParameterCreator.Create(
        //                    "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
        //                    )
        //                );
        //        }

        //        public static Int32 UpdateUserStatus(Guid userId, Int32 currentStatus)
        //        {
        //            #region Sql

        //            var sql = @"
        //UPDATE users
        //SET ActivityStatus = @CurrentStatus
        //    WHERE Id = @UserId
        //";

        //            #endregion

        //            return CDbQueryExecutor.UpdateItemParametrized(sql,
        //                SSqlParameterCreator.Create(
        //                    "@Id", userId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@CurrentStatus", (Byte)currentStatus, SqlDbType.TinyInt, false
        //                    )
        //                );
        //        }

        //        //TODO Куда этот метод убрать? Или оставить здесь?
        //        public static IList<CUserInfo> GetAllChatParticipantsByChatId(Guid chatId)
        //        {
        //            #region Sql

        //            var sql = @"
        //SELECT
        //    [Id] = u.Id,
        //    [Login] = u.Login,
        //    [Password] = u.Password,
        //    [LastActiveDate] = u.LastActiveDate,
        //    [ActivityStatus] = u.ActivityStatus,
        //    [Avatar] = u.Avatar
        //FROM chatsParticipants c

        //INNER JOIN users u

        //ON c.UserId = u.Id
        //    WHERE c.ChatId = @ChatId
        //";

        //            #endregion

        //            return CDbQueryExecutor.GetDataParametrized(new CUserInfoMapper(), sql,
        //                SSqlParameterCreator.Create(
        //                    "@ChatId", chatId, SqlDbType.UniqueIdentifier, false
        //                    )
        //            );
        //        }

        //        public static Guid CreateUser(CUserInfo user)
        //        {
        //            #region SQL Query
        //            var sql = @"
        //INSERT INTO users (Id, Login, Password, LastActiveDate, ActivityStatus, Avatar)
        //    VALUES (
        //@Id, @Login, @Password, @LastActiveDate, @ActivityStatus, @Avatar
        //)
        //";
        //            #endregion

        //            var newId = Guid.NewGuid();

        //            try
        //            {
        //                var result = CDbQueryExecutor.CreateItemParametrized(sql,
        //                    SSqlParameterCreator.Create(
        //                        "@Id", newId, SqlDbType.UniqueIdentifier, false
        //                        ),
        //                    SSqlParameterCreator.Create(
        //                        "@Login", user.Login, System.Data.SqlDbType.NVarChar, false
        //                        ),
        //                    SSqlParameterCreator.Create(
        //                        "@Password", user.Password, System.Data.SqlDbType.NVarChar, false
        //                        ),
        //                    SSqlParameterCreator.Create(
        //                        "@LastActiveDate", user.LastActiveDate, System.Data.SqlDbType.DateTimeOffset, true
        //                        ),
        //                    SSqlParameterCreator.Create(
        //                        "@ActivityStatus", user.ActivityStatus, System.Data.SqlDbType.TinyInt, false
        //                        ),
        //                    SSqlParameterCreator.Create(
        //                        "@Avatar", user.Avatar, System.Data.SqlDbType.NVarChar, true
        //                        )
        //                    );
        //            }
        //            catch (InvalidOperationException e)
        //            {
        //                Console.WriteLine(e);
        //                throw;
        //            }

        //            return newId;
        //        }

        //        public static Int32 DeleteUser(Guid id)
        //        {
        //            #region SQL Query
        //            var sql = @"
        //DELETE FROM users
        //    WHERE users.Id = @Id
        //";
        //            #endregion

        //            return CDbQueryExecutor.DeleteItemParametrized(sql,
        //                SSqlParameterCreator.Create(
        //                    "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
        //                    )
        //                );
        //        }

        //        public static IList<CUserInfo> GetAllNotOfflineUsers()
        //        {
        //            #region Sql

        //            var sql = @"
        //SELECT
        //    [Id] = Id,
        //    [Login] = Login,
        //    [Password] = Password,
        //    [LastActiveDate] = LastActiveDate,
        //    [ActivityStatus] = ActivityStatus,
        //    [Avatar] = Avatar
        //FROM users
        //    WHERE ActivityStatus != 0
        //";

        //            #endregion

        //            return CDbQueryExecutor.GetDataParametrized(new CUserInfoMapper(), sql);
        //        }

        //        public static IList<CUserInfo> SearchContacts(Guid ownerId, String q)
        //        {
        //            #region SQL Query
        //            //            var sql = @"
        //            //SELECT DISTINCT
        //            //    [Id] = c.Id,
        //            //    [OwnerId] = c.OwnerId,
        //            //    [UserId] = c.UserId,
        //            //    [IsBlocked] = c.IsBlocked
        //            //FROM contactsLists c

        //            //INNER JOIN users u
        //            //ON c.UserId = u.Id
        //            //    WHERE c.OwnerId != @OwnerId
        //            //    AND CONTAINS (u.Login, @SearchQuery)
        //            //";

        //            var sql = @"
        //SELECT
        //    [Id] = u.Id,
        //    [Login] = u.Login,
        //    [Password] = u.Password,
        //    [LastActiveDate] = u.LastActiveDate,
        //    [ActivityStatus] = u.ActivityStatus,
        //    [Avatar] = u.Avatar
        //FROM users u
        //    WHERE NOT EXISTS (
        //        SELECT *
        //        FROM contactsLists c
        //            WHERE c.OwnerId = @OwnerId
        //            AND u.Id = c.UserId
        //            OR u.Id = @OwnerId
        //    )
        //    AND u.Login LIKE @SearchQuery
        //";
        //            #endregion

        //            var result = CDbQueryExecutor.GetDataParametrized(new CUserInfoMapper(), sql,
        //                SSqlParameterCreator.Create(
        //                    "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
        //                ),
        //                SSqlParameterCreator.Create(
        //                    "@SearchQuery", "%" + q + "%", System.Data.SqlDbType.NVarChar, false
        //                )
        //                );

        //            return result;
        //        }
        #endregion

        public CUserInfo GetUserByAuthData(String login, String password)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetUserByAuthData)}({login}, {password})' is called");

            #region SQL Query
            var sql = @"
SELECT 
    [Id] = u.Id,
    [Login] = u.Login,
    [Password] = u.Password,
    [LastActiveDate] = u.LastActiveDate,
    [ActivityStatus] = u.ActivityStatus,
    [Avatar] = u.Avatar
FROM users AS u 
    WHERE u.Login = @Login
    AND u.Password = @Password
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetItem(new CUserInfoMapper(), sql,
                                                      SSqlParameterCreator.Create(
                                                          "@Login", login, System.Data.SqlDbType.NVarChar, false
                                                          ),
                                                      SSqlParameterCreator.Create(
                                                          "@Password", password, System.Data.SqlDbType.NVarChar, false
                                                          )
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetUserByAuthData)}({login}, {password}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetUserByAuthData)}({login}, {password}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetUserByAuthData)}({login}, {password}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 10
0
        public Int32 UpdateUserLastActiveDate(Guid userId, DateTimeOffset lastActiveDate)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(UpdateUserLastActiveDate)}({userId}, {lastActiveDate})' is called");

            #region SQL

            var sql = @"
UPDATE users

SET LastActiveDate = (CASE 
        WHEN LastActiveDate < @LastActiveDate 
        THEN @LastActiveDate 
        ELSE LastActiveDate 
    END) 
    WHERE Id = @UserId
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.UpdateItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@LastActiveDate", lastActiveDate, SqlDbType.DateTimeOffset, true
                                                             )
                                                         );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(UpdateUserLastActiveDate)}({userId}, {lastActiveDate}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(UpdateUserLastActiveDate)}({userId}, {lastActiveDate}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(UpdateUserLastActiveDate)}({userId}, {lastActiveDate}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
//        #region Static methods

//        [Obsolete("Use instance method instead of static")]
//        public static IList<CChatInfo> GetChatsByParticipantId(Guid userId)
//        {
//            #region SQL Query
//            var sqlQuery = @"
//SELECT DISTINCT
//    [Id] = c.Id,
//    [Title] = c.Title,
//    [OwnerId] = c.OwnerId,
//    [IsPersonal] = c.IsPersonal,
//    [Type] = c.Type
//FROM chatsParticipants p

//INNER JOIN chats c

//ON p.ChatId = c.Id
//    WHERE p.UserId = @UserId
//";
//            #endregion

//            var result = CDbQueryExecutor.GetDataParametrized(new CChatInfoMapper(), sqlQuery,
//                SSqlParameterCreator.Create(
//                    "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
//                    )
//                );
//            return result;
//        }

//        [Obsolete("Use instance method instead of static")]
//        public static Int32 GetUnreadMessagesCount(Guid userId, Guid chatId)
//        {
//            #region SQL Query
//            var sqlQuery = @"
//SELECT DISTINCT
//    COUNT(*)
//FROM messagesInChats
//    WHERE ChatId = @ChatId
//    AND ToUserId = @UserId
//    AND IsRead = 0
//";
//            #endregion

//            Int32 result = CDbQueryExecutor.GetScalar<Int32>(sqlQuery,
//                SSqlParameterCreator.Create("@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false),
//                SSqlParameterCreator.Create("@ChatId", chatId, System.Data.SqlDbType.UniqueIdentifier, false)
//                );
//            return result;
//        }

//        [Obsolete("Use instance method instead of static")]
//        public static IList<CChatInfo> GetChatsByOwnerId(Guid userId)
//        {
//            #region SQL Query
//            var sqlQuery = @"
//SELECT
//    [Id] = Id,
//    [Title] = Title,
//    [OwnerId] = OwnerId,
//    [IsPersonal] = IsPersonal,
//    [Type] = Type
//FROM chats AS c
//    WHERE c.OwnerId = @OwnerId
//";
//            #endregion

//            var result = CDbQueryExecutor.GetData(new CChatInfoMapper(), sqlQuery);
//            return result;
//        }

//        [Obsolete("Use instance method instead of static")]
//        public static CChatInfo GetChatById(Guid chatId)
//        {
//            #region SQL Query
//            var sqlQuery = @"
//SELECT
//    [Id] = Id,
//    [Title] = Title,
//    [OwnerId] = OwnerId,
//    [IsPersonal] = IsPersonal,
//    [Type] = Type
//FROM chats AS c
//    WHERE c.Id = @Id
//";
//            #endregion

//            var result = CDbQueryExecutor.GetItem(new CChatInfoMapper(), sqlQuery);
//            return result;
//        }

//        [Obsolete("Use instance method instead of static")]
//        public static CChatInfo GetDialog(Guid userId, Guid participantId)
//        {
//            #region SQL Query
//            var sqlQuery = @"
//SELECT
//    [Id] = c.Id,
//    [Title] = c.Title,
//    [OwnerId] = c.OwnerId,
//    [IsPersonal] = c.IsPersonal,
//    [Type] = c.Type
//FROM (
//    SELECT DISTINCT
//        p.ChatId
//    FROM chatsParticipants p

//    INNER JOIN chatsParticipants p2

//    ON p.ChatId = p2.ChatId
//        WHERE p.UserId = @UserId
//        GROUP BY p.ChatId
//        HAVING COUNT(p.ChatId) = 2
//    ) as res

//INNER JOIN chatsParticipants p
//ON res.ChatId = p.ChatId

//INNER JOIN chats c
//ON p.ChatId = c.Id
//    WHERE p.UserId = @ParticipantId
//";
//            #endregion

//            var result = CDbQueryExecutor.GetItemParametrized(new CChatInfoMapper(), sqlQuery,
//                SSqlParameterCreator.Create(
//                    "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
//                    ),
//                SSqlParameterCreator.Create(
//                    "@ParticipantId", participantId, System.Data.SqlDbType.UniqueIdentifier, false
//                    )
//                );
//            return result;
//        }

//        [Obsolete("Use instance method instead of static")]
//        public static CChatInfo CreateChat(CChatInfo chatInfo)
//        {
//            #region SQL Query
//            var sqlQuery = @"
//INSERT INTO
//    chats
//VALUES (
//   @Id, @Title, @OwnerId, @IsPersonal, @Type
//)
//";
//            #endregion

//            var id = Guid.NewGuid();

//            CChatInfo output = null;

//            var result = CDbQueryExecutor.CreateItem(sqlQuery,
//                SSqlParameterCreator.Create(
//                    "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
//                ),
//                SSqlParameterCreator.Create(
//                    "@Title", chatInfo.Title, System.Data.SqlDbType.NVarChar, false
//                ),
//                SSqlParameterCreator.Create(
//                    "@OwnerId", chatInfo.OwnerId, System.Data.SqlDbType.UniqueIdentifier, false
//                    ),
//                SSqlParameterCreator.Create(
//                    "@IsPersonal", chatInfo.IsPersonal, System.Data.SqlDbType.Bit, false
//                    ),
//                SSqlParameterCreator.Create(
//                    "@Type", chatInfo.Type, System.Data.SqlDbType.TinyInt, false
//                )
//                );

//            output = new CChatInfo(id, chatInfo.Title, chatInfo.OwnerId, chatInfo.IsPersonal, chatInfo.Type);

//            return output;
//        }

//        #endregion


        public IList <CChatInfo> GetChatsByParticipantId(Guid userId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetChatsByParticipantId)}({userId})' is called");
            #region SQL Query

            var sqlQuery = @"
SELECT DISTINCT 
    [Id] = c.Id, 
    [Title] = c.Title, 
    [OwnerId] = c.OwnerId, 
    [IsPersonal] = c.IsPersonal,
    [Type] = c.Type
FROM chatsParticipants p 

INNER JOIN chats c 

ON p.ChatId = c.Id
    WHERE p.UserId = @UserId
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CChatInfoMapper(), sqlQuery,
                                                      SSqlParameterCreator.Create(
                                                          "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
                                                          )
                                                      );
                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetChatsByParticipantId)}({userId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetChatsByParticipantId)}({userId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetChatsByParticipantId)}({userId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 12
0
        public Int32 UpdateUserStatus(Guid userId, Int32 currentStatus)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(UpdateUserStatus)}({userId}, {currentStatus})' is called");

            #region Sql

            var sql = @"
UPDATE users
SET ActivityStatus = @CurrentStatus
    WHERE Id = @UserId
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.UpdateItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@CurrentStatus", (Byte)currentStatus, SqlDbType.TinyInt, false
                                                             )
                                                         );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(UpdateUserStatus)}({userId}, {currentStatus}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(UpdateUserStatus)}({userId}, {currentStatus}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(UpdateUserStatus)}({userId}, {currentStatus}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
        //TODO https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?redirectedfrom=MSDN&view=netframework-4.7.2
        //TODO https://stackoverflow.com/questions/36815927/inserting-multiple-records-into-sql-server-database-using-for-loop
        //TODO https://stackoverflow.com/questions/8106789/how-to-insert-multiple-rows-into-an-database-in-c-ado-net
        //TODO https://stackoverflow.com/questions/2972974/how-should-i-multiple-insert-multiple-records

        public Int32 CreateContact(Guid ownerId, Guid userId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(CreateContact)}({ownerId}, {userId})' is called");

            #region SQL Query
            var sql = @"
INSERT INTO contactsLists (Id, OwnerId, UserId, IsBlocked)
    VALUES (
DEFAULT, @OwnerId, @UserId, 0
)
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.CreateItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
                                                             ),
                                                         SSqlParameterCreator.Create(
                                                             "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
                                                             )
                                                         );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(CreateContact)}({ownerId}, {userId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(CreateContact)}({ownerId}, {userId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(CreateContact)}({ownerId}, {userId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
        //        #region Static
        //        public static IList<CContactInfo> GetAllContactsByOwnerId(Guid ownerId)
        //        {
        //            #region SQL Query
        //            var sql = @"
        //SELECT
        //    [Id] = c.Id,
        //    [OwnerId] = c.OwnerId,
        //    [UserId] = c.UserId,
        //    [IsBlocked] = c.IsBlocked
        //FROM contactsLists AS c
        //    WHERE c.OwnerId = @OwnerId
        //";
        //            #endregion


        //            return CDbQueryExecutor.GetDataParametrized(new CContactInfoMapper(), sql,
        //                SSqlParameterCreator.Create(
        //                    "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    )
        //                );
        //        }

        //        public static Int32 CreateContact(Guid ownerId, Guid userId)
        //        {
        //            #region SQL Query
        //            var sql = @"
        //INSERT INTO contactsLists (Id, OwnerId, UserId, IsBlocked)
        //    VALUES (
        //DEFAULT, @OwnerId, @UserId, 0
        //)
        //";
        //            #endregion

        //            return CDbQueryExecutor.CreateItemParametrized(sql,
        //                SSqlParameterCreator.Create(
        //                    "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    )
        //                );
        //        }

        //        public static Int32 DeleteContact(Guid ownerId, Guid userId)
        //        {
        //            #region SQL Query
        //            var sqlQuery = @"
        //DELETE FROM contactsLists
        //    WHERE OwnerId = @OwnerId
        //        AND UserId = @UserId
        //";
        //            #endregion

        //            return CDbQueryExecutor.DeleteItemParametrized(sqlQuery,
        //                SSqlParameterCreator.Create(
        //                    "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    ),
        //                SSqlParameterCreator.Create(
        //                    "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
        //                    )
        //                );
        //        }

        //        public static IList<CContactInfo> GetHasDialogContacts(Guid ownerId)
        //        {
        //            #region SQL Query
        //            var sql = @"
        //SELECT
        //    [Id] = c.Id,
        //    [OwnerId] = c.OwnerId,
        //    [UserId] = c.UserId,
        //    [IsBlocked] = c.IsBlocked
        //FROM (
        //    SELECT DISTINCT
        //        p.ChatId
        //    FROM chatsParticipants p

        //    INNER JOIN chatsParticipants p2

        //    ON p.ChatId = p2.ChatId
        //        WHERE p.UserId = @OwnerId
        //        GROUP BY p.ChatId
        //        HAVING COUNT(p.ChatId) = 2
        //    ) as res

        //INNER JOIN chatsParticipants p
        //ON res.ChatId = p.ChatId

        //INNER JOIN contactsLists c
        //ON p.UserId = c.UserId
        //    WHERE p.UserId != @OwnerId
        //    AND c.OwnerId = @OwnerId
        //";
        //            #endregion

        //            return CDbQueryExecutor.GetDataParametrized(new CContactInfoMapper(), sql,
        //                SSqlParameterCreator.Create(
        //                    "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
        //                )
        //            );
        //        }
        //        #endregion

        public IList <CContactInfo> GetAllContactsByOwnerId(Guid ownerId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetAllContactsByOwnerId)}({ownerId})' is called");

            #region SQL Query
            var sql = @"
SELECT 
    [Id] = c.Id,
    [OwnerId] = c.OwnerId,
    [UserId] = c.UserId,
    [IsBlocked] = c.IsBlocked
FROM contactsLists AS c 
    WHERE c.OwnerId = @OwnerId
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CContactInfoMapper(), sql,
                                                      SSqlParameterCreator.Create(
                                                          "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
                                                          )
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetAllContactsByOwnerId)}({ownerId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetAllContactsByOwnerId)}({ownerId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetAllContactsByOwnerId)}({ownerId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 15
0
        public IList <CUserInfo> GetAllNotOfflineUsers()
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetAllNotOfflineUsers)}()' is called");

            #region Sql

            var sql = @"
SELECT 
    [Id] = Id,
    [Login] = Login,
    [Password] = Password,
    [LastActiveDate] = LastActiveDate,
    [ActivityStatus] = ActivityStatus,
    [Avatar] = Avatar
FROM users
    WHERE ActivityStatus != 0
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CUserInfoMapper(), sql);

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetAllNotOfflineUsers)}(): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetAllNotOfflineUsers)}(): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetAllNotOfflineUsers)}(): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
        public Int32 GetUnreadMessagesCount(Guid userId, Guid chatId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetUnreadMessagesCount)}({userId}, {chatId})' is called");
            #region SQL Query
            var sqlQuery = @"
SELECT DISTINCT
    COUNT(*)
FROM messagesInChats
    WHERE ChatId = @ChatId
    AND ToUserId = @UserId
    AND IsRead = 0
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.ExecuteScalar <Int32>(sqlQuery,
                                                                    SSqlParameterCreator.Create("@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false),
                                                                    SSqlParameterCreator.Create("@ChatId", chatId, System.Data.SqlDbType.UniqueIdentifier, false)
                                                                    );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetUnreadMessagesCount)}({userId}, {chatId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetUnreadMessagesCount)}({userId}, {chatId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetUnreadMessagesCount)}({userId}, {chatId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
        public CChatInfo GetChatById(Guid chatId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetChatById)}({chatId})' is called");
            #region SQL Query
            var sqlQuery = @"
SELECT 
    [Id] = Id, 
    [Title] = Title, 
    [OwnerId] = OwnerId, 
    [IsPersonal] = IsPersonal,
    [Type] = Type
FROM chats AS c 
    WHERE c.Id = @Id
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetItem(new CChatInfoMapper(), sqlQuery);

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetChatById)}({chatId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetChatById)}({chatId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetChatById)}({chatId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 18
0
        public Int32 DeleteUser(Guid userId)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(DeleteUser)}({userId})' is called");

            #region SQL Query
            var sql = @"
DELETE FROM users 
    WHERE users.Id = @Id
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.DeleteItem(sql,
                                                         SSqlParameterCreator.Create(
                                                             "@Id", userId, System.Data.SqlDbType.UniqueIdentifier, false
                                                             )
                                                         );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(DeleteUser)}({userId}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(DeleteUser)}({userId}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(DeleteUser)}({userId}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(0);
                    }
                }
            }
        }
Exemplo n.º 19
0
        public IList <CMessageInfo> GetNewMessagesFromChat(Guid userId, Guid chatId, DateTimeOffset lastRequestDate, Int32 limit, Int32 offset, Int64 usn)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset})' is called");
            //TODO Надо добавить информацию об авторе сообщения
            #region Sql

            var sql = @"
SELECT
    [Id] = res.Id,
    [DispatchDate] = res.DispatchDate,
    [MessageText] = res.MessageText,
    [Type] = res.Type,
    [ContentUri] = res.ContentUri,
    [FromUserId] = res.FromUserId,
    [IsRead] = res.IsRead,
    [Login] = res.Login,
    [USN] = res.USN
FROM 
(
    SELECT DISTINCT
        m.Id,
        m.DispatchDate,
        m.MessageText,
        m.Type,
        m.ContentUri,
        c.FromUserId,
        c.IsRead,
        u.Login,
        m.USN
    FROM messagesInChats c

    INNER JOIN messages m
    ON c.MessageId = m.Id

    INNER JOIN users u
    ON c.FromUserId = u.Id
        WHERE c.ChatId = @ChatId
        AND c.ToUserId = @UserId 
        AND c.FromUserId != @UserId
        AND m.DispatchDate > @LastRequestDate
        AND m.USN > @USN
    ORDER BY m.DispatchDate DESC
    OFFSET @Offset ROWS
    FETCH NEXT @Limit ROWS ONLY
) res
ORDER BY res.DispatchDate
";

            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CMessageInfoMapper(), sql,
                                                      SSqlParameterCreator.Create("@UserId", userId, SqlDbType.UniqueIdentifier, false),
                                                      SSqlParameterCreator.Create("@ChatId", chatId, SqlDbType.UniqueIdentifier, false),
                                                      SSqlParameterCreator.Create("@LastRequestDate", lastRequestDate, SqlDbType.DateTimeOffset, false),
                                                      SSqlParameterCreator.Create("@Offset", offset, SqlDbType.Int, false),
                                                      SSqlParameterCreator.Create("@Limit", limit, SqlDbType.Int, false),
                                                      SSqlParameterCreator.Create("@USN", usn, SqlDbType.BigInt, false)
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }
Exemplo n.º 20
0
        public IList <CUserInfo> SearchContacts(Guid ownerId, String q)
        {
            s_log.LogInfo($@"Data provider's method '{nameof(SearchContacts)}({ownerId}, {q})' is called");

            #region SQL Query
            //            var sql = @"
            //SELECT DISTINCT
            //    [Id] = c.Id,
            //    [OwnerId] = c.OwnerId,
            //    [UserId] = c.UserId,
            //    [IsBlocked] = c.IsBlocked
            //FROM contactsLists c

            //INNER JOIN users u
            //ON c.UserId = u.Id
            //    WHERE c.OwnerId != @OwnerId
            //    AND CONTAINS (u.Login, @SearchQuery)
            //";

            var sql = @"
SELECT
    [Id] = u.Id,
    [Login] = u.Login,
    [Password] = u.Password,
    [LastActiveDate] = u.LastActiveDate,
    [ActivityStatus] = u.ActivityStatus,
    [Avatar] = u.Avatar
FROM users u
    WHERE NOT EXISTS (
        SELECT * 
        FROM contactsLists c 
            WHERE c.OwnerId = @OwnerId 
            AND u.Id = c.UserId
            OR u.Id = @OwnerId 
    )
    AND u.Login LIKE @SearchQuery
";
            #endregion

            using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
            {
                using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
                {
                    try
                    {
                        var result = executor.GetData(new CUserInfoMapper(), sql,
                                                      SSqlParameterCreator.Create(
                                                          "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
                                                          ),
                                                      SSqlParameterCreator.Create(
                                                          "@SearchQuery", "%" + q + "%", System.Data.SqlDbType.NVarChar, false
                                                          )
                                                      );

                        executor.Commit();
                        return(result);
                    }
                    catch (SqlException e)
                    {
                        s_log.LogError($@"{nameof(SearchContacts)}({ownerId}, {q}): Error occured during SQL query execution", e);
                        s_log.LogInfo($@"{nameof(SearchContacts)}({ownerId}, {q}): Operation was rolled back because of error");
                        Console.WriteLine($@"{nameof(SearchContacts)}({ownerId}, {q}): Error occured during SQL query execution");
                        Console.WriteLine("Operation was rolled back because of error");
                        return(null);
                    }
                }
            }
        }