Re: Microsoft .Net Provider does not return all Rows

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 02/10/04 

Date: Tue, 10 Feb 2004 17:05:32 -0500

DelC:

I've used this provider extensively and haven't noticed this problem. Just
to test a few things...use the same query but use a DataAdapter to fill a
datatable and verify DataTable.Rows.Count. I expect the number will match
SQL+ if the query is the same. Next, take out all the code in your While
dr.Read loop and just debug.writeline (i) and then i++. I expect it will
be the same here too. I'm not sure what Limit is, but since you don't know
what the rowcount of a reader is without walking through it (or sending a
Count(*) query with the exact same where clause prior to executing your
reader and assuming the row count hasn't changed in the interim)..it's quite
likely that you are knocking yourself out of the loop with the Exit While
like Miha pointed out.

I can't say for sure that the Oracle provider is bug free, but I've used it
a ton and never seen this, and if it were a bug this serious, I think
there'd be a lot more talk of it.

HTH,

Bill
"DelC" <anonymous@discussions.microsoft.com> wrote in message
news:2D918E18-787D-4962-8F6A-13547C499E39@microsoft.com...
> We are experiencing a problem in using the Microsoft .Net Data Provider
For
> Oracle. For certain more complex SQL queries it does not return the
correct
> number of rows. The behaviour can take two forms
> 1 the DataReader returns no rows back to the calling program, on the
> database one fetch has been done and a fraction of the expected rows has
> been processed.
> 2 The reader returns rows but the amount of rows is less than total
returned
> if the SQL is run directly, on the database a number of fetches have been
> done.
>
> In both cases the SQL is valid and can be executed from SQL+, so I am
happy
> that there is not a problem with the data itself or the database.
>
> We tried this provider as we wanted to stay with the Oracle 8.1.7 client.
We
> are currently deploying on .Net 1.0 Framework using MDAC 2.7.
>
> Has anyone else had any problems with this data provider ?
>
> Example SQL that does work
>
> SELECT B.F01_143,A.F00_025,A.F00_026 FROM SEG_01 B, SEG_00 A WHERE
A.F00_002
> = B.F01_145
>
> Example SQL that doesn't work
>
> SELECT B.F01_143,A.F00_025,A.F00_026 FROM SEG_01 B, SEG_00 A WHERE
A.F00_002
> = B.F01_145 AND ( ( 1 = ( SELECT COUNT( F00_030 ) FROM SEG_01 B1, SEG_00
A1
> WHERE A1.F00_002 = B1.F01_145 AND B1.F01_003 = B.F01_003 GROUP BY
> B1.F01_003) ) )
>
> Example Code showing basic connection objects
>
> Imports SDO = system.Data.OracleClient
>
> mConnection = New SDO.OracleConnection(mConnectionString)
> mySQL = TextBox1.Text
>
> mConnection.Open()
>
> mCommand = New SDO.OracleCommand(mySQL, mConnection)
> mCommand.CommandType = CommandType.Text
>
> Dim startTime As Date = Now
> aRdr = mCommand.ExecuteReader()
> While aRdr.Read
> PrintLine(theFile, aRdr.Item(0).ToString)
> linecount += 1
> If linecount > limit Then
> Exit While
> End If
> End While
> aRdr.Close()
> Dim stopTime As Date = Now
> Label2.Text = Now.ToLocalTime + " It took " +
> CStr(DifferenceInSeconds) + " seconds and returned " + CStr(linecount) +
> "rows"
>
> mConnection.Close()
>

Relevant Pages

  • Re: VS.net installs sql express as default datastore for role/membersh
    ... I can use sql or access databases for dataaccess, ... Asp.net web site administration tool. ... Select provider configuration ... it seems easy to select an other database. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: application design problem
    ... Actual action is depends on your Provider which provide service for a particual database or data store. ... When I would click Button1 this sql would execute: ... I dont think its a good idea to put all your client spcific logic ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Website Administration Tool not working from published applica
    ... provider, whom most of them can't afford the fancy of dedicated servers - ... SQL Server 2000 instead of SQL Express ... The first mistake was using ... that the Web App Admin Tool shows when it cannot connect to the database. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: How to secure SQL website
    ... That depends on the way you fire up your query to the server and the ... Are ou building up Select query via some dynamic sql or do you use stored ... what method and what provider are you using? ...
    (microsoft.public.sqlserver.security)
  • Re: .NET data provider or OLEDB provider?
    ... I don't see where you see a built-in query engine. ... There are third party tools that provider SQL parsing IIRC, ... >> database) is that the .NET model doesn't support scrollable, ...
    (microsoft.public.data.oledb)