Filling a datagrid from Oracle

[gillcleeren]

I have a datagrid that has to be filled in a WinForms application. The
data comes from an Oracle database.

The normal procedure would be filling a dataset, but I have the problem
that there are over 100.000 records in the tables, so this has very low
performance when executing.

I then tried using an OracleDataReade r, but for some reason, I cannot
bind my reader to the datagrid, I get the following error:
"Complex DataBinding accepts as a data source either an IList or an
IListSource"

I use the following code for this reader:
Public Shared Function ReadAllExisting Reader() As
OracleClient.Or acleDataReader
Dim sqlCommand As String = "SELECT * FROM THTEST"

' DataSet that will hold the returned results

Dim pReader As OracleClient.Or acleDataReader
Dim db As Database = DatabaseFactory .CreateDatabase ()
Dim pOraConnection As OracleClient.Or acleConnection
pOraConnection = CType(db.GetCon nection(),
OracleClient.Or acleConnection)
Dim comm As OracleClient.Or acleCommand = New
OracleClient.Or acleCommand(sql Command, pOraConnection)

Try
pOraConnection. Open()
pReader =
comm.ExecuteRea der(CommandBeha vior.CloseConne ction)

Catch ex As Exception

Throw New Exception(ex.Me ssage)
Finally

End Try

Return pReader
End Function

And this is bound this way:
Dim reader As System.Data.Ora cleClient.Oracl eDataReader =
DAL.THTest.Read AllExistingRead er

..........
With Me.grdTHTest
.RowHeadersVisi ble = False
.DataSource = reader
.CaptionText = "TH Test"
.ReadOnly = True
End With

Anyone has a solution?

[Nicholas Paldino [.NET/C# MVP]]

You don't want to use a data reader. Rather, you want to take your
command that performs the select and set it as the SelectCommand on the
OracleDataAdapt er class. Then, you can call the Fill method, passing a
DataSet which is filled with the result set.

You can then bind that result set to your data grid.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"gillcleeren@go ogle mail DOT com" <gi*********@gm ail.com> wrote in message
news:11******** ************@o1 3g2000cwo.googl egroups.com...

I have a datagrid that has to be filled in a WinForms application. The
data comes from an Oracle database.

The normal procedure would be filling a dataset, but I have the problem
that there are over 100.000 records in the tables, so this has very low
performance when executing.

I then tried using an OracleDataReade r, but for some reason, I cannot
bind my reader to the datagrid, I get the following error:
"Complex DataBinding accepts as a data source either an IList or an
IListSource"

I use the following code for this reader:
Public Shared Function ReadAllExisting Reader() As
OracleClient.Or acleDataReader
Dim sqlCommand As String = "SELECT * FROM THTEST"

' DataSet that will hold the returned results

Dim pReader As OracleClient.Or acleDataReader
Dim db As Database = DatabaseFactory .CreateDatabase ()
Dim pOraConnection As OracleClient.Or acleConnection
pOraConnection = CType(db.GetCon nection(),
OracleClient.Or acleConnection)
Dim comm As OracleClient.Or acleCommand = New
OracleClient.Or acleCommand(sql Command, pOraConnection)

Try
pOraConnection. Open()
pReader =
comm.ExecuteRea der(CommandBeha vior.CloseConne ction)

Catch ex As Exception

Throw New Exception(ex.Me ssage)
Finally

End Try

Return pReader
End Function

And this is bound this way:
Dim reader As System.Data.Ora cleClient.Oracl eDataReader =
DAL.THTest.Read AllExistingRead er

.........
With Me.grdTHTest
.RowHeadersVisi ble = False
.DataSource = reader
.CaptionText = "TH Test"
.ReadOnly = True
End With

Anyone has a solution?

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"gillcleeren@go ogle mail DOT com" <gi*********@gm ail.com> wrote in message
news:11******** ************@o1 3g2000cwo.googl egroups.com...

I have a datagrid that has to be filled in a WinForms application. The
data comes from an Oracle database.

The normal procedure would be filling a dataset, but I have the problem
that there are over 100.000 records in the tables, so this has very low
performance when executing.

You need to retrieve only those records that you are going to use, depending
of how your records are organized you can create a SP that returns N records
with IDs less than/greater than a particular ID that represent the top
record.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[gillcleeren]

Do you mean some kind of grid paging like in ASP.net?

Do you have an example on how this works in winforms?

Thanks

gill

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"gillcleeren@go ogle mail DOT com" <gi*********@gm ail.com> wrote in message
news:11******** *************@g 47g2000cwa.goog legroups.com...

Do you mean some kind of grid paging like in ASP.net?

Do you have an example on how this works in winforms?

Thanks

Well, I did it in ASP.NET but the idea is the same, you have your grid, two
buttons "next" "previous" and depending of which you click you go one page
down/one page up
yuo just bind the grid in each situation

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation