Indy: Great question. Here I have been avoiding one of the richest areas of dot.NET, i.e., capitilizing on the use and functionality of DataSets, DataTables and DataViews.
DataTables, manipulated in code-behind, are rich and dynamic data objects that allow for a wide spectrum of filtering, sorting, updating, deleting and otherwise maintaing records. These objects prevent excessive hitting of tables, can be sorted, filtered, use to update the database tables, and can be maintained in Cache, Session, etc... In addition, parent-child relationships between tables can be maintained (DataTables are subsets of DataSets, while DataViews are subsets of DataTables).
I am posting one solution, hardly a candidate for a final product, but perhaps reliable. I look forward to a more polished approach but for now this will do.
The initial aggregate function that you tried to execute:
Code:
ds.Tables("myTable").Select("Sum(Name)")
demonstrates one of the many functions of DataTables, q.v., the aggragate functions (also utilizes calculated expressions)
On first glance you might think a DataTable wouldn't have a problem recognizing a contraction of records, but the aggregate functions appear to work best in parent-child relations where the many table is counted, and the one table has a count column added to it.
The approach I took, a far cry from a one-liner, is probably overkill -- yet I cannot see how to contract this, hence the reason I became more interested in the details your question brought up.
Brief of current solution:
* Caputre DataTable with datadapter (required to create a "DataSet")
* Use the fill command (internal connection mgmnt) to populate DataSet
* Name the DataSet and assign Table name (da.fill(ds,"mytable"))
* Create dataview 1 (sorting not necessary)
* Create dataview 2 by copying dataview 1 (LV thread855-854036)
* Loop through dataview 1
* Strip dataview 2 in process of creating unique new DataTable
* Use new unique name table in parent-child count (Option 1)
* Capture name count during stripping of dataview 2 (Option 2 - used)
* Bind
Code:
<%@ Page Language="VB"%>
<%@Import Namespace = "Microsoft.VisualBasic"%>
<%@Import Namespace = "System"%>
<%@Import Namespace = "System.Web"%>
<%@Import Namespace = "System.Web.UI.WebControls"%>
<%@Import Namespace = "System.Web.UI.HtmlControls"%>
<%@Import Namespace = "System.Data"%>
<%@Import Namespace = "System.Data.OleDb"%>
<script runat="server">
Dim ds As New DataSet
Dim dt As New DataTable
Dim i, j As Integer
Dim strName As String
Dim intRowIndex As Integer
Dim dtNew As DataTable
Dim colName As DataColumn
Dim colCount As DataColumn
Dim dRow As DataRow
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
'open database...
Dim cmdSelect As OLEDbCommand
Dim dbconn As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath("fpdb\Sites.mdb;"))
Dim daName = New OLEDbDataAdapter("SELECT Name FROM tblName", dbconn)
daName.Fill(ds, "myNames") <-- note conn management is automatic...
dt = ds.Tables("myNames") <-- add datatable myNames to dataset id is dt...
Dim dv As DataView = ds.Tables("myNames").DefaultView '<-- create dataview...
Dim dv2 As DataView = dv.Table.Copy().DefaultView '<-- LV's copy datatable code...
'2 dataviews are to be used here. In the first dv, a loop is created
'while a second dv is having its records sripped while creating yet
'a third datatable with unique names.
'after creating a unique table of names, one could create a parent
'child relationship between the original table storing many names
'while the newly created table has the name only once....
'an alternative way, which I employed below, was just to capture
'the Name count while strippping dataview 2...
dv.Sort="Name" '<-- sort dv sorting was initially required...
dv2.Sort="Name" '<-- sort dv 2 and is not needed here...
'create new DataTable and add two columns, one for Names, the other for Count...
dtNew = New DataTable("UniqueNames")
colName = New DataColumn("Name", GetType(String))
dtNew.Columns.Add(colName)
colCount = New DataColumn("decCount", GetType(Decimal))
dtNew.Columns.Add(colCount)
'loop with FIND dataview 2 and strip the names, populating new DataTable
'dtNew, which will create table of unique Names...
For i = 0 to dv.Count-1
strName = dv(i).row("Name")
j=0
'using the dataview Find property...
Do While dv2.Find(strName) <> -1 '<-- use dv.FIND in Loop...
intRowIndex = dv2.Find(strName) '<-- assign index to variable...
dv2(intRowIndex).delete '<--- utilizing the delete property of datasets...
j += 1
Loop
If j <> 0 Then 'want 1st occurrence...
dRow = dtNew.NewRow() '<-- add unique name as one row to new table...
dRow("Name") = strName
dRow("decCount") = j '<-- add count from above loop through dv2...
dtNew.Rows.Add(dRow) '<-- stick row...
End If
Next
'parent-chield routine would fit here (see below)
'bind datagrid...
With dgName
.dataSource = dtNew '<-- bind DataTable directly
.dataMember = "UniqueNames" '<-- name of DataTable
.dataBind()
End With
End If
End Sub
</script>
<HTML>
<HEAD>
<TITLE>Test DataSets</TITLE>
</HEAD>
<body>
<form id="Form1" runat="server">
<asp:DataGrid id="dgName" AutogenerateColumns="false" runat="server">
<Columns>
<asp:BoundColumn DataField="Name" HeaderText="Name"/>
<asp:BoundColumn DataField="decCount" HeaderText="Occurrence"/>
</Columns>
</asp:DataGrid>
</form>
</BODY>
</HTML>
...a parent-child approach to this problem, applied above might look like:
Code:
'establish the relationships between the new unique table and the original many table...
ds.Relations.Add("tblUnique", ds.Tables("UniqueNames").Columns("Name"), ds.Tables("myNames").Columns("Name"))
'Add the aggregate column...
colCount = New DataColumn("Occurrence", GetType(Decimal))
colCount.Expression = "Count(Child.Name)"
ds.Tables("UniqueNames").Columns.Add(colCount)
This is a fundamental advantage with DataTables, being able
to maintain and establish parent-child relations -- it is in these relations that one can take most advantage of the "aggragate" functions (although not exclusively).
A few examples since I have them at my fingertips (a few posts on this subject that might help you along).
1. SQL in parent-child tables: qwert321 (thread855-611367)
Code:
dsDataMatch.Orders.Compute("Count(recNum)", "(P1 NOT LIKE 'SL' OR P1 Is Null) AND (Child.Grade NOT LIKE 'f*' AND Child.Grade NOT LIKE 's*' AND Child.Grade NOT LIKE 't*')")
which is an excellent example of using a DataSets parent-child relationships in order to carry out an aggragate operation. Note that qwert uses the relations as part of the DataTables filtering SQL. What's the difference between doing a Count using "Compute" and "Select"?
2. Looping through DataTable (calculated values): arpan (thread855-437571)
Code:
objDapter=New SQLDataAdapter("SELECT ID, Name",objConn)
objDS=new DataSet()
objDapter.Fill(objDS,"PFRegister")
dTable=objDS.Tables("PFRegister")
For Each dRow In dTable.Rows
i=i+CType(dRow("Basic"),Double)
j=j+CType(dRow("PF"),Double)
Next
3. Filtering - using Import, databinding dataview: faithfull (thread855-682341)
Code:
Filter = " intEmployeeID > 0 "
Sort = " intEmployeeID DESC"
tempdt = CType(Session("Results"), DataTable)
drs = tempdt.Select(Filter, Sort)
filterdt = New DataTable()
Dim row As DataRow
For Each row In drs
filterdt.ImportRow(row)
Next
Dim dv As DataView = filterdt.DefaultView
dgSearch.DataSource = dv
dgSearch.DataBind()
So, Indy, I think there is a message here, at least an academic one, and that is to use more of the DataSets, DataTables and DataViews to better manage server resources and make the site more scalable. There is also I am sure an efficiency gain as well. I guess I never realized how critical this area really is (I think it plays a central role in shopping cart design).
Thanks, if you can contract the above, have at it....!
Oh, and one final note.
A thread posted by SJG0526 (Programmer) (thread855-810671) posed an interesting question regarding DataViews and DataSets. See if you can answer his question -- to date nobody has.
