loop through dataset

[igotyourdotnet]

How can i loop through my dataset if if colum 1 is there more then once
group them together

example:
dataset looks like this
BMW
BMW
BMW
LEXUS
LEXUS

I want to show BMW once, and Lexus 1 but count the number of time they are
there
so

like this as my output
Cars Sales
BMW 3
LEXUS 2

 

[pintu]

Hi..I hav one approach which somehow finds the result..dont know
whether it is the right way or not..i am writing down the code for it..
Hashtable hs = new Hashtable();
ArrayList arr = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
try
{
hs.Add(dr["Brand"],string.Empty); //only one BMW
will be added here
}
catch
{
arr.Add(dr["Brand"]); //duplicate values will be
added here
}
}
Response.Write(hs.Count); //will show the no of duplication
items for each product
Response.WriteLine(arr.Count);

If u will find any other solution just post it n group..
Thanks..

 

[Steven Cheng[MSFT]]

Hello Mike,

If you're forced to do this in .net code layer with a given Dataset in the
below data format:

=========
BMW
BMW
BMW
LEXUS
LEXUS
==========

I think you may need to manually loop each DataRow in the DataTable and
store the calculated(grouped result set) into a new DataTable. e.g

=====test page code=====
protected void btnClick_Click(object sender, EventArgs e)
{
DataTable tb = GetTargetTable(GetSourceTable());

GridView1.DataSource = tb;

GridView1.DataBind();


}

//calculate the group result
private DataTable GetTargetTable(DataTable dt)
{
DataTable newtb = new DataTable("target");
newtb.Columns.Add("Cars");
newtb.Columns.Add("Sales", typeof(Int32));



string currentCar = null;

foreach (DataRow row in dt.Rows)
{
string car = row["Cars"] as string;

DataRow newrow = null;

if (currentCar == null || currentCar != car)
{

currentCar = car;


newrow = newtb.NewRow();
newrow["Cars"] = car;
newrow["Sales"] = 1;
newtb.Rows.Add(newrow);
}
else
{
DataRow currentrow = newtb.Rows[newtb.Rows.Count - 1];
currentrow["Sales"] = (int)currentrow["Sales"] + 1;
}
}

return newtb;

}


//simulate the source table
private DataTable GetSourceTable()
{
DataTable dt = new DataTable("source");
dt.Columns.Add("Cars");

dt.Rows.Add("BMW");
dt.Rows.Add("BMW");
dt.Rows.Add("BMW");
dt.Rows.Add("LEXUS");
dt.Rows.Add("LEXUS");
dt.Rows.Add("PASSAT");
dt.Rows.Add("PASSAT");
dt.Rows.Add("LEXUS");
dt.Rows.Add("TOYOTA");
dt.Rows.Add("TOYOTA");

return dt;

}
==================================

BTW, if this is possible to do at database layer, it will be much easier to
use SQL to group the original data.

Hope this helps some.


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[sloan]

HOW TO: Implement a DataSet GROUP BY Helper Class
http://support.microsoft.com/kb/325685

I think there is a C# counterpart there also.

 

[Mohsin Rizvi]

Hello sloan!

Have you tried dataview for that purpose?
Develope an expression using DataColumn.Expression property.

Hope that would solve your problem & do let me know in any case.

Regards,
Mohsin Rizvi