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

I have a stored procedure ( spgetabsecode_all )  in postgresql  that returns values when executed  
from the query  analyser

When I execute same from a  C#  application  it returns no values

What is wrong with my output statement  and why is the output reporting   zero records

Please note that  I have confirmed separately  that my connection string
to postgresql  using Npgsql  provider  is correct.


see the 2 screens attached

1.  The select executed locally from query analyser  with the result

2. error screen from the debugger within visual studio
storedprocedure261118.PNG
debugscreen261118.PNG
Olukayode OluwoleSystems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
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;
}

Open in new window

-saige-
Olukayode OluwoleSystems AnalystAuthor Commented:
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
it_saigeDeveloperCommented:
Add a reference to System.Data.

-saige-
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Olukayode OluwoleSystems AnalystAuthor Commented:
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
Olukayode OluwoleSystems AnalystAuthor Commented:
Waiting for your response

Thanks


Oluwole
it_saigeDeveloperCommented:
Looks like you are missing the using statement for Npgsql.

-saige-
Olukayode OluwoleSystems AnalystAuthor Commented:
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
it_saigeDeveloperCommented:
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-
Olukayode OluwoleSystems AnalystAuthor Commented:
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
it_saigeDeveloperCommented:
This is because you need to translate the records (which are DataRow objects) into your class object (for each row).  Usually, this is accomplished by doing something like:
while (reader.Read())
{
    AbseCodeModel model = new AbseCodeModel();
    model.id = reader["id"]; //id is the column name in the database
    results.Add(model);
}

Open in new window

If column name is not supported, then use the column ordinal; e.g. -
[code]while (reader.Read())
{
    AbseCodeModel model = new AbseCodeModel();
    model.id = reader[0]; //0 is the ordinal for the id column in the reader/row
    results.Add(model);
}

Open in new window

Do the same for each one of your properties.  Of course, you will have to ensure that you cast or convert your column data to the expected type.  And you may also need to perform a null check in case the column you are reading supports a null value.

-saige-

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olukayode OluwoleSystems AnalystAuthor Commented:
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
it_saigeDeveloperCommented:
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-
Olukayode OluwoleSystems AnalystAuthor Commented:
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
Olukayode OluwoleSystems AnalystAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.