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

Olukayode Oluwole
Olukayode Oluwole used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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 Analyst

Author

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

Commented:
Add a reference to System.Data.

-saige-
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Olukayode OluwoleSystems Analyst

Author

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 Analyst

Author

Commented:
Waiting for your response

Thanks


Oluwole

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

-saige-
Olukayode OluwoleSystems Analyst

Author

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

Commented:
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 Analyst

Author

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
Commented:
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-
Olukayode OluwoleSystems Analyst

Author

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

Commented:
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 Analyst

Author

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 Analyst

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial