Mainly the problem appears when you used a plpgsql stored procedure that returns the type Record - meaning that you need to cast the results (eg select * from storedprocedure(value) as ( value int4, value text, value float8) etc... In these cases it casts them to DBNULL and NpgsqlDataReader.HasRows will be true, even though the values are all DBNULL. This isn't that suprising and I should've thought of it before.
Anyways the problem can be fixed by checking NpgsqlDataReader.ISDBNULL (Value) I think.
-Scott
Hi Scott,
first, i open and checked if opened successfull before i create any Command-Object like this:
try
{
NpgSqlConnection oConn = new NpgsqlConnection();
oConn.ConnectionString = "…";
oConn.Open();
if ( oConn.State == ConnectionState.Open )
{ throw new Exception(„Connection not open…"); }
// may be start Transaction…
Try
{
NpgSqlCommand oCmnd = oConn.CreateCommand();
// …
// …
// …
// here: TransactionEnd if started…
}
Catch ( Exception e )
{
// here: TransactionRollback if started…
Throw e;
}
Finally
{
oConn.Close();
}
}
Catch ( Exception e )
{
// your Handling with e
}
That's my "normal" Procedure to work with Npgsql/Sql. Idont know what happens, when creating a Command-Object before Connection is open.
We didn´t work with StoredProcedures, so I did not know if your Way is correct. Best will be to make by a normal Statement like:
NpgSqlCommand oCmnd = oConn.CreateCommand();
oCmnd.CommandText = "SELECT * FROM FactoryUsers";
NpgsqlDataReader oRead = oCmnd.ExecuteReader();
oRead.Read()
? oRead:HasRows
If that will work, maybe the Problem isn't in Npgsql – so you can concentrade to your StoredProcedures.
Be so kind and let me know what happens and how you get on!
HTH
Jan Waiz
Von: Scott Schulthess [mailto:[EMAIL PROTECTED]]
Gesendet: Donnerstag, 26. Januar 2006 15:45
An: [EMAIL PROTECTED]; npgsql[EMAIL PROTECTED]
Betreff: Re: [Npgsql-general] Npgsqldatareader.HasRows
Jan -
Npgsql.NpgsqlCommand myCommand = new Npgsql.NpgsqlCommand("select * from getsubscription('[EMAIL PROTECTED]','password') as (Sublevel int, Subversion int, TZPro date);",myConnection);
Npgsql.NpgsqlDataReader myReader;
myConnection.Open();
myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
if(myReader.HasRows)
{
Npgsql.NpgsqlCommand myCommand = new Npgsql.NpgsqlCommand ("select * from getsubscription('[EMAIL PROTECTED]','password') as (Sublevel int, Subversion int, TZPro date);",myConnection);
Npgsql.NpgsqlDataReader myReader;
myConnection.Open();
myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
if(myReader.HasRows)
{
while(myReader.Read())
{
Sublevel = myReader.GetInt32 (0);
SubscriptionVersion = myReader.GetInt32(1);
TZPro = Convert.ToDateTime(myReader["TZPro"]);
}
}
}
...stored procedure
CREATE OR REPLACE FUNCTION getsubscription(text, text)
RETURNS record AS
$BODY$
declare myview record;
begin
select SubscriptionLevel, SubscriptionVersion, TZPro into myview from factoryusers where lower(email)=$1 and lower(userpassword)=$2;
return myview;
end; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getsubscription(text, text) OWNER TO tzmaster;
Basically, whether or not myview is null, myReader.hasrows = true. I don't know if this has something to do with me returning a record from a stored procedure or what - I haven't thought that was part of the problem, so I haven't tested it with just a normal query yet.
-Scott
On 1/26/06, Jan Waiz <[EMAIL PROTECTED]> wrote:
Hi Scott,
i am workinig with the Npgsql, too. And I did not have any Problems about that. HasRows is working correct for me.
How did you use it? Did you have some Lines of Code ?
Regards
Jan Waiz
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] stgresql.org] Im Auftrag von Scott Schulthess
Gesendet: Mittwoch, 25. Januar 2006 22:42
An: npgsql-general@gborg.postgresql.org
Betreff: [Npgsql-general] Npgsqldatareader.HasRows
Everyone,
Thanks for reading.
The NpgsqlDataReader.HasRows property does not work correctly. By that I mean, checking if(DataReader.HasRows) does not return false if there are no rows. It seems to always return true.
Please provide info.
--
Sincerely,
Scott D. Schulthess
Phone: (203) 910-7368
[EMAIL PROTECTED]
--
Sincerely,
Scott D. Schulthess
Phone: (203) 910-7368
[EMAIL PROTECTED]
--
Sincerely,
Scott D. Schulthess
Phone: (203) 910-7368
[EMAIL PROTECTED]
_______________________________________________ Npgsql-general mailing list Npgsql-general@gborg.postgresql.org http://gborg.postgresql.org/mailman/listinfo/npgsql-general
