//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);
}
}
}
}
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));
}
}
}
}
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);
}
}
}
}
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
// 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);
}
}
}
}
// 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 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);
}
}
}
}
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);
}
}
}
}
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);
}
}
}
}
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);
}
}
}
}
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);
}
}
}
}
