DataReader in recursiv algorithms

[Frank Schumacher]

Hi NG,

I'd like to try a nested db query, that looks like this:

....
DataReader dr = _myCon.ExecuteReader("SELECT id from tableX where
parentID=1");

while (dr.Read())
{
DataReader dr2 = _myCon.ExecuteReader("SELECT id from tableX where
parentID=" + dr.GetInt32(0).ToString());
while (dr2.Read())
{
...
}
}

The probles is, as you will have recognized, that I can't open another
DataReader, before the first one is closed. The workaround for me till
now is to 1.) read all the entries from dr into a ArrayList and 2.)
close the dr and 3.) foreach the elements of the ArrayList to open the
next dr

This is ... well ... not quite sexy. There have to be another way 'round.
BTW: Is it possible to close a dr without having access to the
instancevariable? like: _myCon.CurrentDataReader.Close()?

Thanks in advance,
Frank

 

[William \(Bill\) Vaughn]

Ah, first I would execute this as query on the server using a WHERE clause
that includes both conditions or a JOIN.
If you MUST use this approach, open a second connection.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Grzegorz Danowski]

Hi NG,

I'd like to try a nested db query, that looks like this:

...
DataReader dr = _myCon.ExecuteReader("SELECT id from tableX where
parentID=1");

while (dr.Read())
{
DataReader dr2 = _myCon.ExecuteReader("SELECT id from tableX where
parentID=" + dr.GetInt32(0).ToString());
while (dr2.Read())
{
...
}
}

The probles is, as you will have recognized, that I can't open another
DataReader, before the first one is closed. The workaround for me till now
is to 1.) read all the entries from dr into a ArrayList and 2.) close the
dr and 3.) foreach the elements of the ArrayList to open the next dr

I'm not sure if it is so trivial problem as suggest Bill Vaughn.
However, in my opinion, there are some solutions (better then actual):

1. use dataadapter to copy all records from to DataTable and make all your
job on client side - it may be a problem if you have many rows in your
database table,
2. change structure to some other, more adequate to sql language, for
example:
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=996814
3. read data by DataReader in recursive way, but witch some corrected
algorithm, for example:

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestTreeDataCsharp
{
public class MyTreeData
{
private SqlConnection con = new SqlConnection(
"Persist Security Info=False;Integrated Security=SSPI;" +
"database=Testy;server=(local)");
private DataTable helpDt = new DataTable();
private SqlCommand com = new SqlCommand();

public MyTreeData()
{
helpDt.Columns.Add("Id", typeof(int));
helpDt.Columns.Add("ParentId", typeof(int));
helpDt.Columns.Add("Explored", typeof(bool));
helpDt.Columns["Explored"].DefaultValue = false;

DataColumn[] pk = new DataColumn []{helpDt.Columns["Id"]};

com.CommandText = "SELECT id from tableX " +
"Where ParentID=@parentId";
com.Connection = con;
com.Parameters.Add("@parentId", System.Data.SqlDbType.Int);
}

public int NoOfBranches
{
get{return helpDt.Rows.Count;}
}

public void FindAllBelongBranches(int id)
{
//add tree root
AddOneRec(id, -1); //-1 is a dummy value

while(true)
{
DataRow[] rows = helpDt.Select("Explored = false");
if(rows.Length == 0) return; //no free branches

foreach(DataRow row in rows)
{
id = (int)row["Id"];
com.Parameters["@parentId"].Value = id;
con.Open();

SqlDataReader dr = com.ExecuteReader();

while(dr.Read())
{
int child = dr.GetInt32(0);
AddOneRec(child, id);
}
con.Close();

row["Explored"] = true;
}
}
}

//helper method
private void AddOneRec(int id, int parentId)
{
DataRow newRow = helpDt.NewRow();
newRow["Id"] = id;
newRow["ParentId"] = parentId;
helpDt.Rows.Add(newRow);
}
}
}

I hope it helps.
Grzegorz

 

[Grzegorz Danowski]

Uzytkownik "Grzegorz Danowski" <[email protected]> napisal w
wiadomosci (...)

However, in my opinion, there are some solutions (better then actual): (...)
3. read data by DataReader in recursive way, but witch some corrected
algorithm, for example:

(...)

Another version (queue instead of datatable):

using System;
using System.Data.SqlClient;
using System.Collections;

namespace TestTreeDataCsharp
{
public class MyTreeDataNew
{
private SqlConnection con = new SqlConnection(
"Persist Security Info=False;Integrated Security=SSPI;" +
"database=Testy;server=(local)");
private SqlCommand com = new SqlCommand();

public MyTreeDataNew()
{
com.CommandText = "SELECT id from tableX " +
"Where ParentID = @parentId";
com.Connection = con;
com.Parameters.Add("@parentId", System.Data.SqlDbType.Int);
}

private int noOfBranches = 1;
public int NoOfBranches
{
get{return noOfBranches;}
}

public void FindAllBelongBranches(int id)
{
Queue toDo = new Queue();
//tree root
toDo.Enqueue(id);

do
{
int parentId = (int)toDo.Dequeue();

com.Parameters["@parentId"].Value = parentId;
con.Open();

SqlDataReader dr = com.ExecuteReader();

while(dr.Read())
{
int child = dr.GetInt32(0);
noOfBranches++;
//another job with tree branch
toDo.Enqueue(child);
}
con.Close();
}
while(toDo.Count != 0);
}
}
}

Regards,
Grzegorz

 

[Frank Schumacher]

Hi Grzegorz,

thanks for all the input, you have given me something to think about ...
I don't know, wheter I will be able to change tha database-structure to
the nested-tables model, because that would mean to change quite a few
lines of code that already had been done.
The datatable sounds like an object worth of dealing with and I will
have a closer look to your algorithms.

Thanks,
Frank