C # Connection with PostgreSQL through Npgsql provider

Question

C # Connection with PostgreSQL through Npgsql provider

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

Hello the problem is that the connection is fine only when I am using the ODBC , but when I change it to the provider of PostgreSQL Npgsql , it does not let me connect it tells me that syntax error, I think the error is in the connection line:

try {

-->  NpgsqlConnection cn = new NpgsqlConnection("SERVER=localhost; UID=postgres;PWD=damaris;DATABASE=ProyectoAsistenciaCCB;");

    cn.Open();

    NpgsqlCommand cmd = new  NpgsqlCommand("INSERT INTO reportes VALUES(?, ?, ?, ?, ?)",cn);

    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        cmd.Parameters.Clear();

        cmd.Parameters.Add("@p1", NpgsqlDbType.Numeric).Value = Convert.ToInt32(row.Cells[0].Value);

        cmd.ExecuteNonQuery();
        cn.Close();
    }
}

c# postgresql

asked by karol 22.10.2016 в 18:18

source

2 answers

1

According to the Npgsl documentation, the format is as follows:

NpgsqlConnection("Host=myserver;User Id=mylogin;Password=mypass;Database=mydatabase"))

Try replacing UID and PWD with Username and Password.

link

answered by 22.10.2016 в 19:57

Calculate distance between two android coordinates PHPExcel comment in black

score 2 · Accepted Answer

This question is a good example of why it is extremely important that you include all the relevant information in your question, including the exact error message, and also the stack trace .

If it had been done, it would have been clear from the beginning that the error has nothing to do with the database connection. Rather, your error happens when executing:

cmd.ExecuteNonQuery(); // verifica que de aquí viene tu error.

And the reason for the error is that you do not like SQL, particularly the ? symbol that you use for the parameters:

INSERT INTO reportes VALUES(?, ?, ?, ?, ?)

With Npgsql , the parameters must be passed using the following syntax (the names you choose are not important, as long as you use @ in front):

INSERT INTO reportes VALUES(@p1, @p2, @p3, @p4, @p5)

The next problem you have is that you only pass a parameter to the statement ( @p1 ). But what about the 4 other values that the INSERT need?

And finally, it does not make sense for you to execute cn.Close() within foreach . Think about it, and you'll see why. In fact, there is no need to run cn.Close() explicitly if you use using to instantiate the connection to the database. This is best practice, and it protects you from situations of errors that can result in the connection never being closed.

I leave you a model to what your code should look like to work correctly:

using(var cn = new NpgsqlConnection("SERVER=localhost; UID=postgres;PWD=damaris;DATABASE=ProyectoAsistenciaCCB;"))
{
    cn.Open();

    using(var cmd = new NpgsqlCommand("INSERT INTO reportes VALUES(@p1, @p2, @p3, @p4, @p5)", cn))
    {
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@p1", NpgsqlDbType.Numeric).Value = Convert.ToInt32(row.Cells[0].Value);

            // Solo un ejemplo, pero necesitas pasar los 4 siguientes parámetros también...
            cmd.Parameters.Add("@p2", NpgsqlDbType.Numeric).Value = ???;
            cmd.Parameters.Add("@p3", NpgsqlDbType.Numeric).Value = ???;
            cmd.Parameters.Add("@p4", NpgsqlDbType.Numeric).Value = ???;
            cmd.Parameters.Add("@p5", NpgsqlDbType.Numeric).Value = ???;

            cmd.ExecuteNonQuery();
        }
    }
}