moving around using a SqlDataReader

[Jeremy]

I execute a SqlDataReader to reader some data and am using
a while loop to read the data into a DataTable, and
essentially what I'm doing is making one call to the
database and reading the data into two seperate data
tables using a condition that I put into the while loop.
For example:

While SQLDataReader.Read And SQLDataReader("field") = false
'fill in false data table
End While

After this statement I want to move back the beginning and
read everything into a seperate data table with something
like:

While SQLDataReader.Read And SQLDataReader("field") = true
'fill in true data table
End While

However, I can't seem to figure out how to do something
like SQLDataReader.MoveFirst() before I try to fill in the
second data table. I get an error that says "Invalid
attempt to read when no data is present." However, I know
the data I want is in the database, so I am pretty sure I
need to move to the first record and read through it again.

Can somebody please help me figure out the best way I
should approach this? Would it be better to use two
seperate calls to the database?

Thanks,
Jeremy

 

[Lucas Tam]

However, I can't seem to figure out how to do something
like SQLDataReader.MoveFirst() before I try to fill in the
second data table. I get an error that says "Invalid
attempt to read when no data is present." However, I know
the data I want is in the database, so I am pretty sure I
need to move to the first record and read through it again.

The Datareader is read-only, forward-only.

If you wish to go back to the beginning of the data, consider using a
dataset instead : )

 

[Jeremy]

Uhh.. What a gotcha there with that DataReader...

Thanks for the reply. Can you please reference me to a
good source that tells how to use a dataset for what I'd
like to do?

I'm still pretty new to all this .NET stuff. For the most
part I like it, it's just all really different from
classic ASP (and JScript, which is the languaged I used.
In .NET I'm using VB as you may have noticed.) So I get
pretty "lost" when people recommend using "different"
things than what I'm talking about. I'm sure it will all
come together after a few projects though.

Thanks,
Jeremy

 

[MS News \(MS ILM\)]

If you don't want to use a dataset, reconnect your datareader so you can go
throught the data again.
Otherwise use the dataset.

 

[Lucas Tam]

Uhh.. What a gotcha there with that DataReader...

That's the advantage of the DataReader - It's faster (well... if you only
want to read once)

Thanks for the reply. Can you please reference me to a
good source that tells how to use a dataset for what I'd
like to do?

Here is a comparison of Dataset vs Datareader:

http://www.fawcette.com/vsm/2003_02/online/aspnet_jgoodyear_02_28_03/


As for example, I think asp.net or 4guysfromrolla.com has some. MSDN
might have some too. But basically here is what you do.

1. Connect to Database with a Connection object
2. Create Command object assign Connection object and SQL text to it
3. Create Data Adapter assign SelectCommand to Command object
4. Create Dataset, Fill with dataAdapter.Fill(objDS, "SomeName")

To access the elements in the Dataset, you would go:

Dataset.Tables("SomeName").Rows(i).Item("Column")

To check if the Dataset is empty, you can check the DataSet.Tables
("somename").Rows.Count value.

Since you can access the rows individually with a dataset, you can now go
forwards/backwards or even skip by 2s ; )

 

[Jeremy]

I guess I probably won't mind using the dataset, I just
don't know how. The more I think about it, the more I
think it would be the optimal choice for me to use.

Can you please provide some info on how to use it, move
around in it, bind it to a data table, etc.?

Don't laugh to hard at my thinking, but I thought a
dataset was basically a container for all of the
datatables you used, and you referenced your datatables
through your dataset. After this thread I think I am
wrong, so I should probably get some accurate info. Can
you help clear up my confusion?

Thanks,
Jeremy

 

[Jeremy]

Thanks for the info. Will definately check it out.

In the mean time, do you think there is anything wrong
with doing something like:

While SQLDataReader.Read
If SQLDataReader("isScheduled") = False Then
dr_Row = dt_List1.NewRow()
dr_Row("ID") = SQLDataReader("ID")
dt_List1.Rows.Add(dr_Row)
Else
dr_Row = dt_List2.NewRow()
dr_Row("ID") = SQLDataReader("ID")
dt_List2.Rows.Add(dr_Row)
End If
End While

DataList1.DataSource = DataSet.Tables("List1")
DataList1.DataBind()

DataList2.DataSource = DataSet.Tables("List2")
DataList2.DataBind()

Honestly, I only really NEED to read through the data I
get from SQL Server once and put the information I read
into seperate datalists to seperate the different types
out. The above seems to work okay, but if something like
that is considered a "no-no" I'll do something else. Like
you said, using a data reader is faster, so I'd like to
stick with that if at all possible.

Thanks,
Jeremy

 

[MS News \(MS ILM\)]

Code below is form MS.[Visual Basic] See C# Code Below:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.ComponentModel
Imports System.Windows.Forms

public class DataGridSample
Inherits Form
Private ds As DataSet
Private myGrid As DataGrid


Private Sub ConnectToData()
' Create the ConnectionString and create a SqlConnection.
' Change the data source value to the name of your computer.
Dim cString As string = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer"
Dim cnNorthwind As SqlConnection = new SqlConnection(cString)
' Create a SqlDataAdapter for the Suppliers table.
Dim adpSuppliers As SqlDataAdapter = new SqlDataAdapter()
' A table mapping tells the adapter what to call the table.
adpSuppliers.TableMappings.Add("Table", "Suppliers")
cnNorthwind.Open()
Dim cmdSuppliers As SqlCommand = _
new SqlCommand("SELECT * FROM Suppliers", cnNorthwind)
cmdSuppliers.CommandType = CommandType.Text

adpSuppliers.SelectCommand = cmdSuppliers
Console.WriteLine("The connection is open.")
ds = New DataSet("Customers")
adpSuppliers.Fill(ds)
' Create a second SqlDataAdapter and SqlCommand to get
' the Products table, a child table of Suppliers.
Dim adpProducts As SqlDataAdapter = new SqlDataAdapter()
adpProducts.TableMappings.Add("Table", "Products")
Dim cmdProducts As SqlCommand = _
new SqlCommand("SELECT * FROM Products", cnNorthwind)
adpProducts.SelectCommand = cmdProducts
adpProducts.Fill(ds)
cnNorthwind.Close()
Console.WriteLine("The connection is closed.")
' You must create a DataRelation to link the two tables.
Dim dr As DataRelation
Dim dc1 As DataColumn
Dim dc2 As DataColumn
' Get the parent and child columns of the two tables.
dc1 = ds.Tables("Suppliers").Columns("SupplierID")
dc2 = ds.Tables("Products").Columns("SupplierID")
dr = new System.Data.DataRelation("suppliers2products", dc1, dc2)
ds.Relations.Add(dr)
End Sub
End Class

[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

public class DataGridSample:Form{
DataSet ds;
DataGrid myGrid;

void ConnectToData(){
// Create the ConnectionString and create a SqlConnection.
// Change the data source value to the name of your computer.

string cString = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer";
SqlConnection myConnection = new SqlConnection(cString);
// Create a SqlDataAdapter.
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.TableMappings.Add("Table", "Suppliers");
myConnection.Open();
SqlCommand myCommand = new SqlCommand("SELECT * FROM Suppliers",
myConnection);
myCommand.CommandType = CommandType.Text;

myAdapter.SelectCommand = myCommand;
Console.WriteLine("The connection is open");
ds = new DataSet("Customers");
myAdapter.Fill(ds);
// Create a second Adapter and Command.
SqlDataAdapter adpProducts = new SqlDataAdapter();
adpProducts.TableMappings.Add("Table", "Products");
SqlCommand cmdProducts = new SqlCommand("SELECT * FROM Products",
myConnection);
adpProducts.SelectCommand = cmdProducts;
adpProducts.Fill(ds);
myConnection.Close();
Console.WriteLine("The connection is closed.");
System.Data.DataRelation dr;
System.Data.DataColumn dc1;
System.Data.DataColumn dc2;
// Get the parent and child columns of the two tables.
dc1 = ds.Tables["Suppliers"].Columns["SupplierID"];
dc2 = ds.Tables["Products"].Columns["SupplierID"];
dr = new System.Data.DataRelation("suppliers2products", dc1, dc2);
ds.Relations.Add(dr);
}
}