Need Help with the Next Step

[Bernie Hunt]

I'm working on the learning curve between ADO and ADO.net. I'm stuck at how
to work with unbound data, vs data bound to a grid. All the start up
references I've found show how to connect data to a grid. I need to process
individual rows, row by row.

I have this working as my sample;
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
' Create a connection string ...
Dim ConnectionString As String = "DSN=Eclipse"

' Create a Connection object and open it with the connection string
....
Dim conn As Connection = New Connection
Dim connMode As Integer = ConnectModeEnum.adModeUnknown
conn.CursorLocation = CursorLocationEnum.adUseClient

conn.Open(ConnectionString, "", "", connMode)
Dim recAffected As Object
Dim cmdType As Integer = CommandTypeEnum.adCmdText

' Execute sql statement to create recordset
Dim sql As String = "SELECT * From Payors ORDER BY PayerID ASC"
Dim rs As _Recordset = conn.Execute(sql)

' Create dataset and data adapter objects
Dim ds As DataSet = New DataSet("Recordset")
Dim da As OleDbDataAdapter = New OleDbDataAdapter

' Call data adapter's Fill method to fill data from ADO
' Recordset to the dataset

da.Fill(ds, rs, "OutputData")

' Now use dataset
DataGrid1.DataSource = ds.DefaultViewManager

End Sub

What I'd like to do is something like this;
' I want to do something like this
Do While Not rs.EOF
txbOutputBox.Text = rs("PayorID")
rs.MoveNext()
Loop

in place of binding the data to they DataGrid.

Any guidance or suggestions? Any references on the net where I can see this
done?

Thanks,
Bernie

 

[Darren Kopp]

i can't remember VB.Net worth junk so i'm gonna do this in pseudo
code... hope it helps

'loop through each datarow in selected datatable in dataset
'tbxoutputbox.text = column information
'end loop

in c# it would be something like

foreach(DataRow dr in ds.Tables["RecordSet"])
{
tbxOutputBox.Text = dr["PayorID"].Value // where PayorID is the name
of the column
}

don't quote me, but i think the vb would be something like this...

Dim i as integer = 0
for i = 0 to ds.Tables("RecordSet").Rows.Count

Dim dr as DataRow = ds.Tables("RecordSet").Rows(i)
tbxOutputBox.Text = dr("PayorID")

next i

Hope this helps,
Darren Kopp
http://blog.secudocs.com/

 

[Mike]

What kind of processing on each record? Is it really simple, or it is
complex? The reason I ask is that you don't want to loop through data
on an active connection if there is processing overhead. The reason
being that you are taking up database resources while you are doing
non-database operations.

If you do anything complex for each record make it two separate steps.
Step 1, copy all the data into some kind of custom entity, adding each
one to a collection. The collection could be either a simple
arraylist, or it could be a custom collection.

Once you have your data, close the reader and connection. Then do your
processing on the entities. If you go this route you can add the
processing logic to your custom collection, or to the entity class
depending on where each part of logic makes sense.

I have an open source data component where you can see how to populate
a custom entity collection and close the connection asap.

http://sourceforge.net/projects/xqs-data/
here is a sample of a factory that uses the data provider
http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.IDataFactory.html

I would not fill a dataset and then loop through that. here is my
typical code to execute a reader, and loop through it...

using (SqlConnection conn = <instantiate here>)
{
using (SqlCommand cmd = new SqlCommand("YourProcedure", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(...) //add each parameter on a line

conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
ArrayList al = new ArrayList(); //or a custom entity collection
while (reader.Read())
{
//TODO: do your custom logic on the record
//with customentities, instantiate new instance here
//with custom entities, set each property to each column here and
add to collection.
al.Add( reader["YourColumn"] ); //this adds each row of a column
value to an arraylist
}
return al; //or return custom collection here
//using statements will handle disposing the command and connection.
}
}
}
I don't have the VB.NET syntax on hand. You can find a site to convert
it, if necassary.

If you really need a dynamic query instead of a stored procedure, you
can keep the commandtype as "Text" and set the CommandText property to
your query. You'd also use a different constructor to the command.
See the MSDN documentation for details.

here is the MSDN code to use a reader (C# and VB.NET):
http://msdn.microsoft.com/library/d...rfsystemdatasqlclientsqlcommandclasstopic.asp

Michael Lang
XQuiSoft LLC
http://www.xquisoft.com/