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