Need Help with the Next Step

21 Feb 2006 6:29 PM

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

21 Feb 2006 9:13 PM

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/

22 Feb 2006 3:22 PM

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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclasstopic.asp

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