CODEWITHSUNDEEP
c#.netpostgresqlnpgsql
Ignacio
Ignacio

Reputation: 950

AddWithValue() in PostgreSQL from .Net

I have been reading about this topic but haven´t found any particular documentation related to Npgsql, so I thought it would be a good idea to post a question in the great Stack Overflow :-).

We know that using parameters in queries and functions (or stored procedures) calls through command.Parameters.AddWithValue(...) or command.Parameters.Add(...) prevents SQL injection (as stated in the Parameters Section of the official Npgsql documentation).

However, I have found some relevant information about the type conversion that SQL Server needs to do when .Net does not infer the correct data type. There is a great article about this here.

So, when calling a function from C# through Npgsql and adding parameters with AddWithValue(), the conversion happens in every row or in the call? If there is a type mismatch between the data type of the parameter and the C# code, could still be affecting results and/or performance? It would be a good idea to use Add() explicitly specifying the data type instead of AddWithValue()?

I hope anyone can shed some light on this! Thank you in advanced!

Upvotes: 2

Views: 1249

Answers (1)

kokojamba
kokojamba

Reputation: 41

From docs:

There are three rules that determine the PostgreSQL type sent for a parameter:

  1. If the parameter's NpgsqlDbType is set, it is used.
  2. If the parameter's DataType is set, it is used.
  3. If the parameter's DbType is set, it is used.
  • If none of the above is set, the backend type will be inferred from the CLR - value type.


If you set dataType to AddWithValue it will work a little bit faster because not will be inferred from the CLR. Example:

command.Parameters.AddWithValue("@length", NpgsqlDbType.Bigint, length);

But if you want to insert many records you can use one of fastests methods from here. It is binary writer for write and binary reader for read

Upvotes: 2

Related Questions