I am completely new to programming and I am trying to create a small app as a school project. I want it to be able to register and login users. I have already figured out how to create login part, but I am stuck on registration. I have created Insert function inside pgAdmin and it works but I cant make it work with my windows forms app.

This is my code so far:

using Npgsql;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ProjektV0._0._2
{
    public partial class frmRegister : Form
    {
        public frmRegister()
        {
            InitializeComponent();
        }

        private NpgsqlConnection conn;
        string connstring = String.Format("Server={0}; Port={1};"  
                "User Id = {2}; Password={3};Database={4};",
                "localhost", "5432", "postgres", "23112001", "demo2");
        private NpgsqlCommand cmd;
        private string sql = null;

        private void frmRegister_Load(object sender, EventArgs e)
        {
            conn = new NpgsqlConnection(connstring);
        }

        private void Register_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }


        private void btnRegister_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                sql = @"select * from u_insert(:_username,:_password)";
                cmd = new NpgsqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("_username", txtEmail.Text);
                cmd.Parameters.AddWithValue("_password", txtPswrd.Text);
                if ((int)cmd.ExecuteScalar() == 1)
                {
                    conn.Close();
                    MessageBox.Show("Registered successfuly", "Well done", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    txtEmail.Text = txtPswrd.Text = txtConPswrd.Text = null;
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                MessageBox.Show("Error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

    }
}

pgAdmin part:

create function u_insert
(
    _username character varying,
    _password character varying
)returns int as
$$
begin
    insert into tbl_users
    (
        username,
        password
    )values
    (
        _username,
        _password
    );
    if found then
        return 1;-----success-----
    else
        return 0;-----fail-----
    end if;
end
$$
language plpgsql

As I said my login part work even through my program and all other functions (insert,update) work only inside pgAdmin.

CodePudding user response：

To clarify my comment... why the function/procedure? It seems dramatic overkill for an insert. I would put something like this outside of my form (in a CRUD class somewhere):

public static int UpdateUser(string UserId, string Password, out string ErrorMessage)
{
    int result = 0;
    ErrorMessage = null;

    NpgsqlConnectionStringBuilder sb = new NpgsqlConnectionStringBuilder();
    sb.Host = "localhost";
    sb.Port = 5432;
    sb.Username = "postgres";
    sb.Password = "23112001";
    sb.Database = "demo2";

    using (NpgsqlConnection conn = new NpgsqlConnection(sb.ToString()))
    {
        conn.Open();

        string dml = "insert into tbl_users (username, password) values (:USER, :PW)";

        using (NpgsqlCommand cmd = new NpgsqlCommand(dml, conn))
        {
            cmd.Parameters.AddWithValue("USER", UserId);
            cmd.Parameters.AddWithValue("PW", Password);

            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
            }
        }
    }

    return result;
}

And then within the Button Click event you can simplify it to this:

private void btnRegister_Click(object sender, EventArgs e)
{
    string error;
    int insertedRows = CrudClass.UpdateUser(txtEmail.Text, txtPassword.Text, out error);

    if (insertedRows == 1)
    {
        MessageBox.Show("Registered successfuly", "Well done", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
        txtEmail.Text = txtPswrd.Text = txtConPswrd.Text = null;
    }
    else
    {
        MessageBox.Show("Error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

}

CodePudding user response：

The connection string is invalid for Postgres. It should rather be "Host={0}; Port={1}; Username= {2}; Password={3};Database={4};"

Then the parameter placeholder should be @, not :

Tip 1: read the doc of the proper database

Note that the connection string is invalid, and opening the connection likely throws an error. In the catch section, you start by closing the connection then you display the error message. If closing the connection fails and throws an error, the code after it will never be executed.

Tip 2: don't do anything that can throw an error within a catch section. You can nest another try..catch though.