addUserToDB function is partialy working, if i comment creating user and privilege adding section, the program is adding my data to DB, but as soon as i uncomment the code, there is a problem and I have no idea how to solve it on the c# side nor the pgAdmin side.
I tried not using transaction, changing email to login as a sql username in case @ or . doesn’t want to work.
public static void addUserToDB(string userName, string userSurname, string userEmail, string userPassword, string userLogin)
{
//var con1 = new NpgsqlConnection(GlobalData.connectionString);
int result;
string insertUserDataCommandText = "insert into klienci (name, surname, email, password, login) VALUES (@name, @surname, @email, @password, @login)";
string createUserCommandText = "create user @userLogin with password @password";
string addPrivilegesCommandText = "GRANT ALL PRIVILEGES ON DATABASE klienci TO @login";
using (var con1 = new NpgsqlConnection(GlobalData.connectionString))
{
con1.Open();
using (var transaction = con1.BeginTransaction())
{
try
{
using (var insertUserDataCommand = new NpgsqlCommand(insertUserDataCommandText, con1))
{
insertUserDataCommand.Parameters.AddWithValue("name", userName);
insertUserDataCommand.Parameters.AddWithValue("surname", userSurname);
insertUserDataCommand.Parameters.AddWithValue("email", userEmail);
insertUserDataCommand.Parameters.AddWithValue("password", userPassword);
insertUserDataCommand.Parameters.AddWithValue("login", userLogin);
insertUserDataCommand.ExecuteNonQuery();
}
using (var createUserCommand = new NpgsqlCommand(createUserCommandText, con1))
{
createUserCommand.Parameters.AddWithValue("login", userLogin);
createUserCommand.Parameters.AddWithValue("password", userPassword);
createUserCommand.ExecuteNonQuery();
}
using (var addPrivilegesCommand = new NpgsqlCommand(addPrivilegesCommandText, con1))
{
addPrivilegesCommand.Parameters.AddWithValue("login", userLogin);
addPrivilegesCommand.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}
Jan Jankowski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.