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