INSERT data from Textbox to Postgres SQL

Question

I just learn how to connect C# and PostgresSQL. I want to INSERT data from tb1(Textbox) and tb2 to database. But I don't know how to code My previous code is SELECT from database. this is my code

private void button1_Click(object sender, EventArgs e)
    {
        bool blnfound = false;
        NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=admin123;Database=Login");
        conn.Open();
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM login WHERE name='" + tb1.Text + "' and password = '" + tb2.Text + "'",conn);
        NpgsqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            blnfound = true;
            Form2 f5 = new Form2();
            f5.Show();
            this.Hide();
        }

        if (blnfound == false)
        {
            MessageBox.Show("Name or password is incorrect", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            dr.Close();
            conn.Close();
        }
    }

So please help me the code.

Solution

First off, you need to use the ExecuteNonQuery method rather than ExecuteReader since you're executing an INSERT rather than a SELECT statement. So, something like:

NpgsqlCommand cmd = new NpgsqlCommand("insert into table1 values(1, 1)", conn);
cmd.ExecuteNonQuery();

The ExecuteNonQuery method will also return the number of rows affected if that's important for you.

Second, you need to use SQL parameters rather than building an unsafe SQL string.

Use:

cmd.Parameters.Add(new NpgsqlParameter("name", tb1.Text));
cmd.Parameters.Add(new NpgsqlParameter("pw", tb2.Text));

To add a parameter to your query. You can now refer to it in your INSERT statement with :name or :pw, for example:

NpgsqlCommand cmd = new NpgsqlCommand("insert into login (Name, Password) values(:name, :pw)", conn);
cmd.ExecuteNonQuery();

Lastly, you might be interested in using an ORM rather than executing raw SQL statements. I'd check into the .NET Entity Framework or Castle Active Record, which is built on NHibernate. These libraries will allow you to query, update, create and delete data within your database without writing the actual SQL statements involved. It's a great way to get started, and will simply your code quite a bit!

Comments

[Francisco Junior]

You may also want to have a look at Npgsql manual which has a lot of working examples: manual.npgsql.org I hope it helps

[will]

Thanks for mentioning Castle Active Record, Haven't heard of it before,