0

I need help on connecting to my friend's PostgreSQL server through my team's project using C# and WinForms in VS Community with Npgsql as the NuGet Package. The problem always shows when we try to connect to his server and it will not allow us to login or register new users to the database, but on the other note, my friend has his server installed in his laptop, and when he executes the program he can login using the data from database and register new users on it.

Here is our code

public static class ConnectionManager
{
    private static NpgsqlConnection connection;

    public static NpgsqlConnection Connection
    {
        get { return connection; }
    }

    public static NpgsqlConnection GetNpgsqlConnection()
    {
        string connectionString = "Host=localhost;Username=postgres;Password=Kirkdave123;Database=MSAD_Project";

        connection = new NpgsqlConnection(connectionString);
        connection.Open();

        return connection;
    }

    public static void TurnoffNpgConnection()
    {
        connection.Close(); 
    }
}

(Login Form Code)
public partial class LoginForm : Form
{
    private bool isFirstClick = true;
    public LoginForm()
    {
        InitializeComponent();
    }

    private void LoginBtn_Click(object sender, EventArgs e)
    {
        // Will check in database if username and password is a user, or an admin.

        string username = UserName_txtbx.Text;
        string password = Password_txtbx.Text;

        if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
        {
            MessageBox.Show("Please enter both username and password.", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return;
        }

        try
        {
            using (NpgsqlConnection connection = ConnectionManager.GetNpgsqlConnection())
            {
                string query = "SELECT COUNT(*) FROM public.\"User_Account\" WHERE \"Username\" = @username AND \"Password\" = @password";
                using (NpgsqlCommand command = new NpgsqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@username", username);
                    command.Parameters.AddWithValue("@password", password);

                    int count = Convert.ToInt32(command.ExecuteScalar());
                    if (count > 0)
                    {
                        MessageBox.Show("Login successful!", "Login", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        var dashboard = new DashboardForm();
                        dashboard.Show();
                        this.Hide();
                    }
                    else
                    {
                        MessageBox.Show("Invalid username or password.", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("An error occurred during login: " + ex.Message, "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void ForgotPasswordLinkLbl_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    {
        var forgotpassword = new ForgotPasswordForm();
        forgotpassword.Show();
        this.Hide();
    }

    private void RegisterLinkLbl_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    {
        var register = new RegisterForm();
        register.Show();
        this.Hide();
    }

    private void UserName_txtbx_Click(object sender, EventArgs e)
    {
        UserName_txtbx.Clear();
    }

    private void Password_txtbx_Click(object sender, EventArgs e)
    {
        Password_txtbx.Clear();
    }
}

(Register Form Code)
public partial class RegisterForm : Form
{
    private Random random = new Random();

    public RegisterForm()
    {
        InitializeComponent();
    }

    private void GobackBtn_Click(object sender, EventArgs e)
    {
        var login = new LoginForm();
        login.Show();
        this.Hide();
    }


    private void RegisterBtn_Click(object sender, EventArgs e)
    {
        bool CheckBox1Checked = CheckBox1.Checked;

        if (!CheckBox1Checked)
        {
            MessageBox.Show("You need to know the Policy and Term's and Condition's" + "before registering");
        }

        else if (!ValidateFormFields())
        {
            MessageBox.Show("Please fill in all the required fields.", "Registration Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        else
        {
            string username = UsernameTbx.Text;
            string password = PasswordTbx.Text;
            string email = EmailTbx.Text;
            string phone = PhoneTbx.Text;
            string lastName = LNametxtbx.Text;
            string firstName = Fnametxtbx.Text;
            int age = Convert.ToInt32(AgecomboBox.SelectedItem);
            string gender = GcomboBox.SelectedItem.ToString();

            int userID = GenerateRandomUserID();

            try
            {
                using (NpgsqlConnection connection = ConnectionManager.GetNpgsqlConnection())
                {
                    string query = "INSERT INTO public.\"User_Account\" (\"UserID\", \"Lastname\", \"Firstname\", \"Gender\", \"Age\", \"Email\", \"Phonenumber\", \"Username\", \"Password\") " +
                        "VALUES (@userID, @lastname, @firstname, @gender, @age, @email, @phone, @username, @password)";

                    using (NpgsqlCommand command = new NpgsqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@userID", userID);
                        command.Parameters.AddWithValue("@lastname", lastName);
                        command.Parameters.AddWithValue("@firstname", firstName);
                        command.Parameters.AddWithValue("@gender", gender);
                        command.Parameters.AddWithValue("@age", age);
                        command.Parameters.AddWithValue("@email", email);
                        command.Parameters.AddWithValue("@phone", phone);
                        command.Parameters.AddWithValue("@username", username);
                        command.Parameters.AddWithValue("@password", password);

                        int rowsAffected = command.ExecuteNonQuery();

                        if (rowsAffected > 0)
                        {
                            MessageBox.Show("Registration successful!", "Will Direct You to Login Form", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            var login = new LoginForm();
                            login.Show();
                            this.Hide();
                        }
                        else
                        {
                            MessageBox.Show("Registration failed!", "Registration Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred during registration: " + ex.Message, "Registration Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                ConnectionManager.TurnoffNpgConnection();
            }
        }

    }

    private int GenerateRandomUserID()
    {
        return random.Next(1000000, 9999999);
    }

    private bool ValidateFormFields()
    {
        if (string.IsNullOrWhiteSpace(UsernameTbx.Text) ||
            string.IsNullOrWhiteSpace(PasswordTbx.Text) ||
            string.IsNullOrWhiteSpace(EmailTbx.Text) ||
            string.IsNullOrWhiteSpace(PhoneTbx.Text) ||
            string.IsNullOrWhiteSpace(LNametxtbx.Text) ||
            string.IsNullOrWhiteSpace(Fnametxtbx.Text) ||
            AgecomboBox.SelectedItem == null ||
            GcomboBox.SelectedItem == null)
        {
            return false;
        }
        return true;
    }

    private void pictureBox6_Click(object sender, EventArgs e)
    {

    }
}

and when they run it shows this error error

We already tried on changing the pg_hba.conf and postgresql.conf and also set up the Windows Defender Firewall for inbound rule on his laptop, but we still cannot connect to the PostgreSQL server, is this because of the C# code or is it more on network connectivity?

7
  • Are you sure the database is running at all? Or that it runs locally? 127.0.0.1 is the local machine. Log the actual full exception text returned by Exception.ToString() not just the message. The full text contains any inner exceptions and a stack trace with the chain of calls that led to the exception. If an inner exception says something like no response received, the database isn't running on that port Commented Jul 19, 2023 at 6:40
  • As for the code itself, it has serious problems and leaks connections. ADO.NET connections shouldn't be cached. They're meant to be short-lived, created inside using blocks so they get closed and disposed as soon as possible. ADO.NET and NpgSQL use connection pools internally to eliminate the cost of establishing new connections. There's a reason all tutorials and examples show using (var connection = new NpgsqlConnection(...)). The connection string should be loaded from settings too, not hard-coded in the code. What if the database runs on another machine? Commented Jul 19, 2023 at 6:43
  • Connection string looks OK. So I suggest to download and install a program such as heidisql to test your local connectivity to the DB. Also, in general, you can add " Include Error Detail=true;" into your connection string to help with debugging errors (not sure it helps in this case mind). BUT this must not be enabled on production systems, only for debug & testing. Commented Jul 19, 2023 at 6:44
  • @jason.kaisersmith read the code more carefully because there are several problems. The cached global connection guarantees errors, either because of leaks, or because of attempts to use a disposed connection. Or trying to close an already disposed connection, as this code does. The exception isn't logged or displayed so including error details won't help. Exception.ToString() probably contains enough information already. Commented Jul 19, 2023 at 6:48
  • @PanagiotisKanavos I agree that are problems with the code. Better to address them with a full and complete answer, as the user probably doesn't know how to fix them. The connection string however should be OK, and the problem they are experiencing is something else. Commented Jul 19, 2023 at 7:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.