//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); } } } }
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); } } } }
// #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); } } } }
// #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); } } } }
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 IList <CChatInfo> GetChatsByOwnerId(Guid userId) { s_log.LogInfo($@"Data provider's method '{nameof(GetChatsByOwnerId)}({userId})' is called"); #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 using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString)) { using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection)) { try { var result = executor.GetData(new CChatInfoMapper(), sqlQuery); executor.Commit(); return(result); } catch (SqlException e) { s_log.LogError($@"{nameof(GetChatsByOwnerId)}({userId}): Error occured during SQL query execution", e); s_log.LogInfo($@"{nameof(GetChatsByOwnerId)}({userId}): Operation was rolled back because of error"); Console.WriteLine($@"{nameof(GetChatsByOwnerId)}({userId}): Error occured during SQL query execution"); Console.WriteLine("Operation was rolled back because of error"); return(null); } } } }
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); } } } }
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); } } } }