NpgsqlCommand.Parameters.AddWithValue strange behaviour

[ivodo...@gmail.com]

Hello All,

I upgraded my Npgsql.dll from 2.0.11.91 to 2.2.3.0 (I upgraded tp PG 9.3 and I had to workaround the bug with incorrect error message handling (and app hangs)) and here I noticed strange behaviour. In my app I have the simple loop for "insert" db commands, so I created one NpgsqlCommand object with two parameters and in the loop I populated these two parameters by cmd.Parameters.AddWithValue("ParName", ParValue)

In 2.0.11 it worked correctly, but in 2.2.3 the cmd.ExecuteNonQuery() statements throws "duplicity value" exception. In 2.0.11.91 I expected, that AddWithValue command worked this way:
- if "ParName" exists in the collection then replace the Value at this existing row;
- if "ParName does not exist in the collection then add new row into the parameter collection;

but in 2.2.3 the first "if" does not perform - first loop iteration inserts 2 parameters into the collection, second loop iteration adds another two parameters (and here my code crashes because Npgsql gets first parameter pair)

So here is my question - is it bug or feature?

Thank you for your response

Best regards

ID

[Francisco Figueiredo Jr.]

Hi, ID.

I'll check this.
My current understanding is that you couldn't add two or more parameters with the same name to the collection. And in this case, Npgsql 2.0 behavior is incorrect.

I'll check the implementation of 2.0 to tell you what it was doing.
I'll also check how sqlclient handles that.
I'm not at my dev machine right now but as soon as I am, I'll check those information and will let you know.

Thank you for your feedback!

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/45a5cef9-d6a2-4cb8-85f9-a0702926e90f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[ivodo...@gmail.com]

Hello Francisco,

I did some small research:

I made two small apps (the same code) and first app I connect to 2.0.11.91, second one to 2.2.3.0:

int parCount = 0;

int rowCount = 0;

using (NpgsqlConnection conn = new NpgsqlConnection("Server=10.140.96.45;Port=5432;Database=szgdb;User Id=dba;Password=sql;")) {

conn.Open();

using (NpgsqlTransaction trans = conn.BeginTransaction()) {

using (NpgsqlCommand cmd = new NpgsqlCommand("insert into public.\"Test\" values (:Par1)", conn, trans)) {

for (int i = 0; i < 3; i++) {

cmd.Parameters.AddWithValue("Par1", i);

int cnt = cmd.ExecuteNonQuery();

}

parCount = cmd.Parameters.Count;

}

using (NpgsqlCommand cmd = new NpgsqlCommand("select count(*) from public.\"Test\"", conn, trans)) {

rowCount = Convert.ToInt32(cmd.ExecuteScalar());

}

trans.Rollback();

}

}

Console.WriteLine("Parameters: {0}\t\tRows: {1}", parCount, rowCount);

Behavior of both apps was the same - "Parameters: 3     Rows: 3" and no exception!

Then I returned to my real code (I apologise for Czech characters) - primary key for table poz.JPV is "CONSTRAINT "PK_JPV" PRIMARY KEY ("IdParcely" , "Kod" )", Npgsql driver is 2.2.3.0:

int PočetZprac;

using (NpgsqlCommand CmdInsPar = new NpgsqlCommand("insert into poz.\"JPV\" (\"IdParcely\",\"Kod\") values(:aIdParcely, :aKod)", connPg, transPg)) {

using (SqlCommand CmdParCnt = new SqlCommand("select count(*) from m_parcely_JPV", connMs)) {

ts_ProgressBar.Maximum = PočetZprac = Convert.ToInt32(CmdParCnt.ExecuteScalar());

}

using (SqlCommand CmdPar = new SqlCommand("SELECT distinct ID_PARCELY,TYPRAV_KOD FROM m_parcely_JPV", connMs)) {

using (SqlDataReader RdrPar = CmdPar.ExecuteReader()) {

int i = 0;

while (RdrPar.Read()) {

int IdParcely;

if (Parcely.TryGetValue(Convert.ToInt64(RdrPar["ID_PARCELY"]), out IdParcely)) {

CmdInsPar.Parameters.AddWithValue("aIdParcely", IdParcely);

CmdInsPar.Parameters.AddWithValue("aKod", Convert.ToInt32(RdrPar["TYPRAV_KOD"]));

int počet = CmdInsPar.ExecuteNonQuery();

}

i++;

}

}

}

}

first iteration performs OK, second iteration throws "duplicate key value violates unique constraint "PK_JPV"" exception. (CmdInsPar.Parameters contains 4 items at this moment).

Therefore I rewrote the code to:

int PočetZprac;

using (NpgsqlCommand CmdInsPar = new NpgsqlCommand("insert into poz.\"JPV\" (\"IdParcely\",\"Kod\") values(:aIdParcely, :aKod)", connPg, transPg)) {

CmdInsPar.Parameters.AddWithValue("aIdParcely", -1);

CmdInsPar.Parameters.AddWithValue("aKod", -1);

using (SqlCommand CmdParCnt = new SqlCommand("select count(*) from m_parcely_JPV", connMs)) {

ts_ProgressBar.Maximum = PočetZprac = Convert.ToInt32(CmdParCnt.ExecuteScalar());

}

using (SqlCommand CmdPar = new SqlCommand("SELECT distinct ID_PARCELY,TYPRAV_KOD FROM m_parcely_JPV", connMs)) {

using (SqlDataReader RdrPar = CmdPar.ExecuteReader()) {

int i = 0;

while (RdrPar.Read()) {

int IdParcely;

if (Parcely.TryGetValue(Convert.ToInt64(RdrPar["ID_PARCELY"]), out IdParcely)) {

CmdInsPar.Parameters["aIdParcely"].Value = IdParcely;

CmdInsPar.Parameters["aKod"].Value = Convert.ToInt32(RdrPar["TYPRAV_KOD"]);

int počet = CmdInsPar.ExecuteNonQuery();

}

i++;

}

}

}

}

and now it runs perfectly.

Both Npgsql drivers allow to insert more parameters with the same name (and different value) and maybe there is some minor difference in the way they obtain the parameter value - behavoiur of two Npgsql drivers was slightly different (2.0 retrieved correct values for the second round, 2.2.3 retrieved probably the first values again; but in my simple test (research) code both drivers work well). I never checked the "Parameter.Count" value before, now I see that my (old) approach is very dangerous. ParameterCollection is not like Dictionary<string, object> and therefore duplicities are erratic.

So - thank you Francesco for your effort, do not waste your time, maybe someone can add some code to the Parameters.Add (or Parameters.AddWithValue) code that checks the parameter name and in case of duplicity throws an exception "Why do you populate the same parameter, are you stupid?" - it teaches programers like me to make better and more robust code :-)

Thank you again and I wish you a nice weekend.

Best regards

ID

Dne pátek 13. února 2015 11:52:12 UTC+1 Francisco Figueiredo Jr. napsal(a):

[Francisco Figueiredo Jr.]

Hi, ID.

Indeed you can add many parameters with the same name. I was thinking about the same parameter object reference which isn't possible. Sorry for that.

I checked 2.0 source and the code which handles parameters when sending the query string translates Parameters.Collection to a Dictionary<string, NpgsqlParameter>:
https://github.com/npgsql/Npgsql2-From-CVS/blob/master/src/Npgsql/NpgsqlCommand.cs#L1043

Dictionary<string, NpgsqlParameter> parameterIndex = new Dictionary<string, NpgsqlParameter>(parameters.Count, StringComparer.InvariantCultureIgnoreCase);
foreach (NpgsqlParameter parameter in parameters)
    parameterIndex[parameter.CleanName] = parameter;


That's why you were getting this behavior. It was indeed overwriting the last parameter with the most recent one.

In the recent 2.2 code, we changed how we handle parameter translation when sending the query string (in fact, we fixed this bug) and now the parameter isn't replaced anymore. That's why you don't get the values changed.

I agree with you that we should add a notice about duplicated parameter names, but we didn't do that because sqlclient doesn't do it and there is nothing in the documentation which says it isn't allowed. But I think we definitely could add this checking as you said because it would help a lot of developers to catch this possible problem.


Your second usage pattern (setting the Value of the parameter directly in each iteration) is the correct way to use the parameters collection.

I'm glad to hear you could change your code without many problems. (This way it will be error free in future Npgsql releases. )

Have a nice weekend too. And thank you for your feedback and for using Npgsql.

If you have any other questions, please, let us know.