Insert data into Npgsql/Postgresql database with C# windows forms

Question

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.

I'm wondering as to if found then where did it come from? Does the function even work? — Rickless
– Rickless, Commented Apr 20, 2022 at 21:38
When i try to test the function (select * from u_insert('x','y')) inside pgAdmin it does work. — Skolia
– Skolia, Commented Apr 20, 2022 at 21:51
I can run the app and insert the values but clicking the button does nothing. — Skolia
– Skolia, Commented Apr 20, 2022 at 21:52

Hambone · Accepted Answer · 2022-04-21 12:56:30Z

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

}

JGH · Accepted Answer · 2022-04-20 22:11:01Z

0

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.

answered Apr 20, 2022 at 22:11

JGH

18.2k5 gold badges40 silver badges57 bronze badges

2 Comments

Hambone Over a year ago

Npgsql is pretty forgiving and will accept either @ or :. We use : because of portability with Oracle, which is the other primary DBMS here.

Skolia Over a year ago

Thank you. This actually helped. Now I at least get an error

Collectives™ on Stack Overflow

Insert data into Npgsql/Postgresql database with C# windows forms

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related