[SOLVED] .Net Core Npgsql Prepared Statement

Issue

I've been trying to change some SQL statements in a .Net Core app to be more reusable by using Prepared Statements but I'm having trouble with the NpgsqlDbType.

I've tried to follow the documentations instructions.

NpgsqlCommand command = new NpgsqlCommand (
    " select * from computers where com_phys = @com_phys ",
    dbconnection
);
command.Parameters.Add("com_phys", NpgsqlDbType.Varchar);
command.Prepare();

But it is failing to compile, saying

The name 'NpgsqlDbType' does not exist in the current context

Am I missing something? How do I use the NpgsqlDbType?

UPDATE

I'm just putting the final working thing here in case it can benefit anyone else

// prepare

NpgsqlCommand command = new NpgsqlCommand (
    " select * from computers where com_phys = @com_phys ",
    dbconnection
);
var param01 = command.Parameters.Add("com_phys", NpgsqlDbType.Varchar);
command.Prepare();

// execute 01

param01.Value = "value01";
var results = command.ExecuteReader();
while(results.Read()) {
   // nothing
}
command.Close();

// execute 02

param01.Value = "value02";
var results = command.ExecuteReader();
while(results.Read()) {
   // nothing
}
command.Close();

Solution

NpgsqlDbType is in the NpgsqlTypes namespace. Make sure you have a using NpgsqlTypes at the top.

If you want to set the value at the same time, use AddWithValue instead of Add

NpgsqlCommand command = new NpgsqlCommand (
    " select * from computers where com_phys = @com_phys ",
    dbconnection
);
command.Parameters.AddValue("com_phys", NpgsqlDbType.Varchar, value);
// OR command.Parameters.AddValue("com_phys", NpgsqlDbType.Varchar, size, value);
// OR command.Parameters.AddValue("com_phys", value);
command.Prepare();

If you want to add the parameter once and execute multiple times, you can keep a reference to the parameter

var parameter = command.Parameters.Add("com_phys", NpgsqlDbType.Varchar);

// Later, in a loop
parameter.Value = "someValue";

Answered By - ESG

Answer Checked By - Mary Flores (WPSolving Volunteer)