public void CreateThreadAnswer(ThreadAnswer answer)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter threadIdParameter = command.CreateParameter();
threadIdParameter.DbType = System.Data.DbType.Int32;
threadIdParameter.IsNullable = false;
threadIdParameter.ParameterName = "@Thread_id";
threadIdParameter.Value = answer.ThreadId;
DbParameter textParameter = command.CreateParameter();
textParameter.DbType = System.Data.DbType.String;
textParameter.IsNullable = false;
textParameter.ParameterName = "@Text";
textParameter.Value = answer.Text;
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.IsNullable = false;
userIdParameter.ParameterName = "@UserId";
userIdParameter.Value = answer.UserId;
command.Parameters.AddRange(new DbParameter[] { threadIdParameter, textParameter, userIdParameter });
command.CommandText = @"INSERT INTO [dbo].[thread_answers]([thread_id],[user_id],[text]) VALUES
(@Thread_id, @UserId, @Text)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void CreateGroupComment(GroupComments groupComment)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter groupIdParameter = command.CreateParameter();
groupIdParameter.DbType = System.Data.DbType.Int32;
groupIdParameter.IsNullable = false;
groupIdParameter.ParameterName = "@GroupId";
groupIdParameter.Value = groupComment.GroupId;
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.IsNullable = false;
userIdParameter.ParameterName = "@UserId";
userIdParameter.Value = groupComment.UserId;
DbParameter textParameter = command.CreateParameter();
textParameter.DbType = System.Data.DbType.String;
textParameter.IsNullable = false;
textParameter.ParameterName = "@Text";
textParameter.Value = groupComment.Text;
command.Parameters.AddRange(new DbParameter[] { groupIdParameter, userIdParameter, textParameter });
command.CommandText = @"INSERT INTO [dbo].[group_comments]([group_id],[user_id],[comment_text]) VALUES
(@GroupId, @UserId, @Text)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void CreateThread(Thread thread)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter nameParameter = command.CreateParameter();
nameParameter.DbType = System.Data.DbType.String;
nameParameter.IsNullable = false;
nameParameter.ParameterName = "@Name";
nameParameter.Value = thread.Name;
DbParameter textParameter = command.CreateParameter();
textParameter.DbType = System.Data.DbType.String;
textParameter.IsNullable = false;
textParameter.ParameterName = "@Text";
textParameter.Value = thread.Text;
DbParameter creatorIdParameter = command.CreateParameter();
creatorIdParameter.DbType = System.Data.DbType.Int32;
creatorIdParameter.IsNullable = false;
creatorIdParameter.ParameterName = "@CreatorId";
creatorIdParameter.Value = thread.Creator_id;
command.Parameters.AddRange(new DbParameter[] { nameParameter, textParameter, creatorIdParameter });
command.CommandText = @"INSERT INTO [dbo].[threads]([name],[text],[creator_id]) VALUES
(@Name, @Text, @CreatorId)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void CreateUsers(User user)
{
using (var connection = SqlConncetionHelper.Connection)
{
connection.Open();
DbCommand command = connection.CreateCommand();
DbParameter nicknameParametr = command.CreateParameter();
nicknameParametr.DbType = System.Data.DbType.String;
nicknameParametr.IsNullable = false;
nicknameParametr.ParameterName = "@Nickname";
nicknameParametr.Value = user.Nickname;
DbParameter passwordParametr = command.CreateParameter();
passwordParametr.DbType = System.Data.DbType.String;
passwordParametr.IsNullable = false;
passwordParametr.ParameterName = "@password";
passwordParametr.Value = user.Password;
command.Parameters.AddRange(new DbParameter[] { nicknameParametr, passwordParametr });
command.CommandText = @"INSERT INTO [dbo].[users]
([nickname],[password]) VALUES (@Nickname, @password)";
SqlConncetionHelper.ExecuteCommands(command);
}
}
public void CreateFriendUser(User userSender, User userReciever)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
//создаем 2 параметра
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.IsNullable = false;
userIdParameter.ParameterName = "@UserId";
userIdParameter.Value = userSender.Id;
DbParameter friendIdParameter = command.CreateParameter();
friendIdParameter.DbType = System.Data.DbType.Int32;
friendIdParameter.IsNullable = false;
friendIdParameter.ParameterName = "@FriendId";
friendIdParameter.Value = userReciever.Id;
command.Parameters.AddRange(new DbParameter[] { userIdParameter, friendIdParameter });
command.CommandText = @"INSERT INTO [dbo].[friends]
([user_id]
,[friend_id])
VALUES
(@UserId, @FriendId)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void CreateFriendMsg(FriendMessage friendMessage)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
//создаем параметры
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.IsNullable = false;
userIdParameter.ParameterName = "@UserId";
userIdParameter.Value = friendMessage.UserId;
DbParameter friendIdParameter = command.CreateParameter();
friendIdParameter.DbType = System.Data.DbType.Int32;
friendIdParameter.IsNullable = false;
friendIdParameter.ParameterName = "@FriendId";
friendIdParameter.Value = friendMessage.FriendId;
DbParameter messageParameter = command.CreateParameter();
messageParameter.DbType = System.Data.DbType.String;
messageParameter.IsNullable = false;
messageParameter.ParameterName = "@Message";
messageParameter.Value = friendMessage.Message;
command.Parameters.AddRange(new DbParameter[] { userIdParameter, friendIdParameter, messageParameter });
command.CommandText = @"INSERT INTO [dbo].[friend_messages]
([user_id], [friend_id], [message]) VALUES
(@UserId, @FriendId, @Message)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void DeleteThread(Thread thread)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
command.CommandText = $@"UPDATE [dbo].[threads] SET IsDeleted = 1 WHERE thread_id = {thread.Id}";
SqlConncetionHelper.ExecuteCommands(command);
}
public void DeleteDeveloper(Developer developer)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
command.CommandText = $@"UPDATE [dbo].[developers] SET IsDeleted = 1 WHERE developers_id = {developer.Id}";
SqlConncetionHelper.ExecuteCommands(command);
}
//---------------------------------------------------------------------------------------------------------------
#region DELETE
public void DeleteFriends(User userFriend)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
command.CommandText = $@"DELETE [dbo].[friends]
WHERE friend_id = {userFriend.Id}";
SqlConncetionHelper.ExecuteCommands(command);
}
public bool DeleteGroup(group group)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter groupIdParameter = command.CreateParameter();
groupIdParameter.DbType = DbType.Int32;
groupIdParameter.ParameterName = "@GroupId";
groupIdParameter.Value = group.Group_id;
command.Parameters.Add(groupIdParameter);
command.CommandText = "update groups set IsDeleted = 1 where group_id = @GroupId";
return(SqlConncetionHelper.ExecuteCommands(command));
}
public void DeleteUser(User user)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.Value = user.Id;
userIdParameter.ParameterName = "@UserId";
command.Parameters.Add(userIdParameter);
command.CommandText = @"UPDATE [dbo].[users]
SET IsDeleted = 1 WHERE user_id = @UserId";
SqlConncetionHelper.ExecuteCommands(command);
}
public bool DeleteProduct(Product product)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter productIdParameter = command.CreateParameter();
productIdParameter.DbType = System.Data.DbType.Int32;
productIdParameter.IsNullable = false;
productIdParameter.ParameterName = "@ProductId";
productIdParameter.Value = product.ProductId;
command.Parameters.Add(productIdParameter);
command.CommandText = "UPDATE dbo.Products SET IsDelted = 1 WHERE ProductID = @ProductId";
return(SqlConncetionHelper.ExecuteCommands(command));
}
public bool CreateProduct(Product product)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter nameParameter = command.CreateParameter();
nameParameter.DbType = System.Data.DbType.String;
nameParameter.IsNullable = false;
nameParameter.ParameterName = "@Name";
nameParameter.Value = product.Name;
DbParameter descriptionParameter = command.CreateParameter();
descriptionParameter.DbType = System.Data.DbType.String;
descriptionParameter.IsNullable = true;
descriptionParameter.ParameterName = "@Description";
descriptionParameter.Value = product.Description;
DbParameter developerIdParameter = command.CreateParameter();
developerIdParameter.DbType = System.Data.DbType.Int32;
developerIdParameter.IsNullable = false;
developerIdParameter.ParameterName = "@DeveloperId";
developerIdParameter.Value = product.DeveloperId;
DbParameter priceParameter = command.CreateParameter();
priceParameter.DbType = System.Data.DbType.Decimal;
priceParameter.IsNullable = false;
priceParameter.ParameterName = "@Price";
priceParameter.Value = product.Price;
command.Parameters.AddRange(new DbParameter[] { nameParameter, descriptionParameter, developerIdParameter,
priceParameter });
command.CommandText = @"insert into dbo.Developers
([Name], [Description], [Developer_Id], [Price]) values
(@Name, @Descrtiption, @DeveloperId, @Price)";
return(SqlConncetionHelper.ExecuteCommands(command));
}
public void UpdatePassword(User user, string password)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.Value = user.Id;
userIdParameter.ParameterName = "@UserId";
DbParameter passwordParameter = command.CreateParameter();
passwordParameter.DbType = System.Data.DbType.String;
passwordParameter.Value = password;
passwordParameter.ParameterName = "@Password";
command.Parameters.AddRange(new DbParameter[] { userIdParameter, passwordParameter });
command.CommandText = @"UPDATE [dbo].[users]
SET [password] = @Password WHERE [user_id] = @UserId";
SqlConncetionHelper.ExecuteCommands(command);
}
public bool CreateProductComment(ProductComment comment)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter productIdParameter = command.CreateParameter();
productIdParameter.DbType = System.Data.DbType.Int32;
productIdParameter.IsNullable = false;
productIdParameter.ParameterName = "@productId";
productIdParameter.Value = comment.ProductId;
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.IsNullable = false;
userIdParameter.ParameterName = "@userId";
userIdParameter.Value = comment.UserId;
DbParameter textParameter = command.CreateParameter();
textParameter.DbType = System.Data.DbType.String;
textParameter.IsNullable = false;
textParameter.ParameterName = "@text";
textParameter.Value = comment.Text;
DbParameter markIdParameter = command.CreateParameter();
markIdParameter.DbType = System.Data.DbType.Int32;
markIdParameter.IsNullable = false;
markIdParameter.ParameterName = "@markId";
markIdParameter.Value = comment.MarkId;
command.Parameters.AddRange(new DbParameter[] { productIdParameter, userIdParameter, textParameter, markIdParameter });
command.CommandText = @"insert into dbo.product_comments
([product_id],[user_id], [text], [mark_id]) values
(@productId, @userId, @text, @markId)";
return(SqlConncetionHelper.ExecuteCommands(command));
}
public void AddUserToGroup(group group, user user)
{
using (var connection = SqlConncetionHelper.Connection)
{
connection.Open();
DbCommand command = connection.CreateCommand();
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = DbType.Int32;
userIdParameter.Value = user.user_id;
userIdParameter.ParameterName = "@userId";
DbParameter groupIdParameter = command.CreateParameter();
groupIdParameter.DbType = DbType.Int32;
groupIdParameter.Value = group.Group_id;
groupIdParameter.ParameterName = "@groupId";
command.Parameters.AddRange(new DbParameter[] { userIdParameter, groupIdParameter });
command.CommandText = @"insert into groups_users values (@userId, @groupId)";
SqlConncetionHelper.ExecuteCommands(command);
}
}
public void DeleteUserFromGroup(group group, user user)
{
using (var connection = SqlConncetionHelper.Connection)
{
connection.Open();
DbCommand command = connection.CreateCommand();
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = DbType.Int32;
userIdParameter.Value = user.user_id;
userIdParameter.ParameterName = "@userId";
DbParameter groupIdParameter = command.CreateParameter();
groupIdParameter.DbType = DbType.Int32;
groupIdParameter.Value = group.Group_id;
groupIdParameter.ParameterName = "@groupId";
command.Parameters.AddRange(new DbParameter[] { userIdParameter, groupIdParameter });
command.CommandText = @"delete groups_users where group_id = @groupId and user_id = @userId";
SqlConncetionHelper.ExecuteCommands(command);
}
}
public bool UpdateProduct(Product product)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter nameParameter = command.CreateParameter();
nameParameter.DbType = System.Data.DbType.String;
nameParameter.IsNullable = false;
nameParameter.ParameterName = "@Name";
nameParameter.Value = product.Name;
DbParameter descriptionParameter = command.CreateParameter();
descriptionParameter.DbType = System.Data.DbType.String;
descriptionParameter.IsNullable = true;
descriptionParameter.ParameterName = "@Description";
descriptionParameter.Value = product.Description;
DbParameter priceParameter = command.CreateParameter();
priceParameter.DbType = System.Data.DbType.Decimal;
priceParameter.IsNullable = false;
priceParameter.ParameterName = "@Price";
priceParameter.Value = product.Price;
DbParameter productIdParameter = command.CreateParameter();
productIdParameter.DbType = System.Data.DbType.Int32;
productIdParameter.IsNullable = false;
productIdParameter.ParameterName = "@productId";
productIdParameter.Value = product.DeveloperId;
command.Parameters.AddRange(new DbParameter[] { nameParameter, descriptionParameter, priceParameter, productIdParameter });
command.CommandText = "UPDATE dbo.Products SET Name = @Name Description = @Description Price = @Price WHERE ProductID = @productId";
return(SqlConncetionHelper.ExecuteCommands(command));
}
public void CreateWallet(Wallet wallet)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter balanceParameter = command.CreateParameter();
balanceParameter.DbType = System.Data.DbType.Decimal;
balanceParameter.IsNullable = false;
balanceParameter.ParameterName = "@balance";
balanceParameter.Value = wallet.Balance;
DbParameter idParameter = command.CreateParameter();
idParameter.DbType = System.Data.DbType.Int32;
idParameter.IsNullable = false;
idParameter.ParameterName = "@Id";
idParameter.Value = wallet.Id;
command.Parameters.AddRange(new DbParameter[] { balanceParameter, idParameter });
command.CommandText = @"INSERT INTO [dbo].[wallets]
([balance],[wallet_id]) VALUES (@balance, @Id)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void CreateDeveloper(Developer developer)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter nameParameter = command.CreateParameter();
nameParameter.DbType = DbType.String;
nameParameter.IsNullable = false;
nameParameter.ParameterName = "@Name";
nameParameter.Value = developer.Name;
DbParameter webSiteParameter = command.CreateParameter();
webSiteParameter.DbType = DbType.String;
webSiteParameter.IsNullable = true;
webSiteParameter.ParameterName = "@WebSite";
webSiteParameter.Value = developer.WebSite;
command.Parameters.AddRange(new DbParameter[] { webSiteParameter, webSiteParameter });
command.CommandText = @"INSERT INTO [dbo].[developers]([name],[website]) VALUES
(@Name, @WebSite)";
SqlConncetionHelper.ExecuteCommands(command);
}
public void UpdateWallet(Wallet wallet)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter balanceParameter = command.CreateParameter();
balanceParameter.DbType = System.Data.DbType.Decimal;
balanceParameter.IsNullable = false;
balanceParameter.ParameterName = "@balance";
balanceParameter.Value = wallet.Balance;
DbParameter idParameter = command.CreateParameter();
idParameter.DbType = System.Data.DbType.Int32;
idParameter.IsNullable = false;
idParameter.ParameterName = "@Id";
idParameter.Value = wallet.Id;
command.Parameters.AddRange(new DbParameter[] { balanceParameter, idParameter });
command.CommandText = @"Update [dbo].[wallets]
Set balance = @balance where id = @Id";
SqlConncetionHelper.ExecuteCommands(command);
}
public bool CreateGroup(Group group)
{
DbCommand command = SqlConncetionHelper.Connection.CreateCommand();
DbParameter nameParameter = command.CreateParameter();
nameParameter.DbType = System.Data.DbType.String;
nameParameter.IsNullable = false;
nameParameter.ParameterName = "@Name";
nameParameter.Value = group.Name;
DbParameter userIdParameter = command.CreateParameter();
userIdParameter.DbType = System.Data.DbType.Int32;
userIdParameter.IsNullable = false;
userIdParameter.ParameterName = "@UserId";
userIdParameter.Value = group.UserId;
command.Parameters.AddRange(new DbParameter[] { nameParameter, userIdParameter });
command.CommandText = @"INSERT INTO [dbo].[groups]([group_name],[user_id]) VALUES
(@Name, @UserId)";
return(SqlConncetionHelper.ExecuteCommands(command));
}