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 OracleDataReader, 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 ReadAllExistingReader() As
OracleClient.OracleDataReader
Dim sqlCommand As String = "SELECT * FROM THTEST"

' DataSet that will hold the returned results

Dim pReader As OracleClient.OracleDataReader
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim pOraConnection As OracleClient.OracleConnection
pOraConnection = CType(db.GetConnection(),
OracleClient.OracleConnection)
Dim comm As OracleClient.OracleCommand = New
OracleClient.OracleCommand(sqlCommand, pOraConnection)

Try
pOraConnection.Open()
pReader =
comm.ExecuteReader(CommandBehavior.CloseConnection )

Catch ex As Exception

Throw New Exception(ex.Message)
Finally

End Try

Return pReader
End Function

And this is bound this way:
Dim reader As System.Data.OracleClient.OracleDataReader =
DAL.THTest.ReadAllExistingReader

..........
With Me.grdTHTest
.RowHeadersVisible = 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
OracleDataAdapter 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.com

"gillcleeren@google mail DOT com" <gi*********@gmail.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.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 OracleDataReader, 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 ReadAllExistingReader() As
OracleClient.OracleDataReader
Dim sqlCommand As String = "SELECT * FROM THTEST"

' DataSet that will hold the returned results

Dim pReader As OracleClient.OracleDataReader
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim pOraConnection As OracleClient.OracleConnection
pOraConnection = CType(db.GetConnection(),
OracleClient.OracleConnection)
Dim comm As OracleClient.OracleCommand = New
OracleClient.OracleCommand(sqlCommand, pOraConnection)

Try
pOraConnection.Open()
pReader =
comm.ExecuteReader(CommandBehavior.CloseConnection )

Catch ex As Exception

Throw New Exception(ex.Message)
Finally

End Try

Return pReader
End Function

And this is bound this way:
Dim reader As System.Data.OracleClient.OracleDataReader =
DAL.THTest.ReadAllExistingReader

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

Anyone has a solution?

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

Hi,

"gillcleeren@google mail DOT com" <gi*********@gmail.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.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@google mail DOT com" <gi*********@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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