Why is my Stored Procedure executing withing Query Analyser but not within C# Application

Normally you would do something like this:

IList<AbseCodeModel> GetAllAbseCodes()
{
    List<AbseCodeModel> results = new List<AbseCodeModel>();
    using (var conn = new NpgsqlConnection("somevalidconnectionstring"))
    {
        using (var command = new NpgsqlCommand("public.spgetabsecode_all", conn))
        {
            command.CommandType = CommandType.StoredProcedure;
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                AbseCodeModel model = new AbseCodeModel();
                // fill in the properties from the returned row
                results.Add(model);
            }
        }
    }
    return results;
}

Select allOpen in new window

-saige-

Thanks for your response.

I have tried  your suggestion  and i seem to have some unexplainable errors

Please  see attached

When i hovered the mouse over the line  it suggested that the second var be changed to NpgsqlCommand

However changing it did not remove the error

See  attached

What do  you suggest i try next

Olukay
newerror261118.PNG

Add a reference to System.Data.

-saige-

There is already a system.data  defined in the using  statement

even at the time i reported the error

Or should it be in a different place

See attached
systemdata261118.PNG

Waiting for your response

Thanks


Oluwole

Looks like you are missing the using statement for Npgsql.

-saige-

The using Npgsql line is being flagged (see attached)

and i am sure  i have Npgsql installed from Nuget

So what should i do to make it accept  the using Npgsql line ??


Olukay
UsingNpgsql271118.PNG

To me that indicates that the Npgsql dependency reference is not added to the project or cannot be located.

Double check your references to see if you have the Npgsql dependency added to the project this class is a part of.  If anything, removing the reference(s) and reinstalling via the Nuget Package Manager wouldn't hurt either.

-saige-

Great advise  on referencing Npgsql.
For a strange  reason I did not have it . now i have install it from
Nuget  and it shows in my references

However I still have a problem. I can not  get the records read from postgresql to show in my ListBox
I know the application gets to the database  because i only have 6 records  in the Db and it loops there
6 times (ie records 0 to 5)  see record  5  values displayed

Also in my ListBox on screen the blue strip in the listbox can be selected  6 times and no more suggesting
to me that my problem is getting the correct syntax / construct  to get the records to show in the list box


As a background:

My Listbox name   is:    absecodelistBox

My model is defined as below:

  public int Id { get; set; }
        public string Absecode { get; set; }
        public string Absedesc { get; set; }
        public DateTime Datecreated { get; set; }
        public AbseCodeModel()
        {

        }
        public AbseCodeModel(string id, string absecode, string absedesc, string datecreated)
        {
            Absecode = absecode;
        }

        public AbseCodeModel(string absecode, string absedesc)
        {
            this.Absecode = absecode;
            this.Absedesc = absedesc;
        }
        public string FullCodeName => $"{Absecode} {Absedesc}";


I want to use the FullCodeName Construct/ definition to load the listbox but just  dont
know how to go about it. In other words i only want to list the code and the description NOT other fields

I attached the debugger  screen showing the 6 records selected and the fact the strings were all empty

I also attach  my screen  which was empty but show the blue strip  whcih  confirms the 6 records records should

have been written  cause the blue lines can be selected  exactly 6 times (The no of records in my postgre database)

Can you please help with the correct contruct to get the records into the list after  the line

 results.Add(model);

Thanks
EmptyString281118.PNG
EmptyScreen281118.PNG

You have been terrific  with this support  I am very very grateful.

While it is already implemented and working can you please  commented on  what i read in the manuals

That this method  of specifying stored procedure is obsolete and is being offered by Npgsql  only for compatibility

purpose.

In the document they specified  using the  CALL  statement to call  stored procedure .  Is this correct

and would i need to be changing this method  in the future

Thanks

Olukay

In all honesty, I wouldn't put too much emphasis on this consideration.  CALL is a functional keyword which is essentially an alias for EXEC.  EXEC is what is translated by the ODBC drivers when you indicate that your command is a StoredProcedure.

That being said, CALL is not always available as a function depending upon your DB version.

-saige-

ok thanks
I will very soon open a new thread  on the postgresql syntax for
inserts , updates and delete  of records using stored procedures

I hope you will be able to help

regards

olukay

I am getting back  just to notify you that i have a question i have just asked
on the C# syntax of Inserts, Update and Delete   into a postgresql database  .

This is very  close to the exercise above and i just thought  you might  wish to assist me
once again

Thanks

Olukay