|
xxx
DataTable: Uniquing a Column
by Charles Carroll
This example illustrates how to created a sorted
DataView and then use that to create an unique datatable for a specific column.
filename=/experiments/datatableunique/reusable.aspx
<%@ trace="true"%>
<%@ Import Namespace="System.Data.sqlclient" %>
<%@ Import Namespace="System.Configuration.ConfigurationSettings" %>
<%@ Import Namespace="System.Data" %>
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
trace.tracemode=TraceMode.SortByTime
Dim Conn as SQLConnection
Dim Rdr as SQLDatareader
TRY
Dim strConn as string =AppSettings("LearnaspSamples")
Dim strSQL as string ="select * from publishers where city<>''" ' where state='CA'
' DataTable for Publishers from New York
dim dt1 as new datatable("temp")
conn=new SQLconnection(strConn)
Dim adapter As New SQLDataAdapter(strSQL,Conn)
adapter.Fill(dt1)
conn.Close()
dim ds1 as new DataSet
ds1.Tables.Add (dt1)
ds1.Tables.Add(DataTableUniquer(ds1.Tables(0),"City"))
myDataGrid.DataSource= ds1.Tables(1)
myDataGrid.DataBind()
CATCH exc1 as exception
litExc.text=exc1.tostring()
FINALLY
If not(conn is Nothing)
IF Conn.State=System.Data.Connectionstate.Open THEN Conn.Close()
End If
END TRY
End Sub
function DataTableUniquer(parmDt as dataTable,parmColumnName as string) as dataTable
' make the datatable
dim dtUnique as new datatable()
with dtUnique
.Columns.Add(new DataColumn(parmColumnName,GetType(String)))
end with
' loop through data add Uniques to newly created datatable
Dim strCurrent as string=""
Dim strPrev as string=""
Dim dv1 As new DataView(parmDt)
dv1.Sort = parmColumnName
Dim datarow1 As DataRowView
FOR EACH datarow1 IN dv1 ' ds1.tables(0).Rows
strCurrent=datarow1(parmColumnName)
trace.write("strCurrent",strCurrent)
IF strCurrent=strPrev
' nothing to do
ELSE
DIM dtblrowTemp as datarow
dtblrowTemp = dtUnique.NewRow()
dtblrowTemp(0) = strCurrent
dtUnique.Rows.Add(dtblrowTemp)
END IF
strPrev=strCurrent
trace.write("strPrev",strPrev)
NEXT
return (dtUnique)
end function
</script>
<html><head>
<title>DbTableUnique</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>DataTable Unique</h3></font>
<asp:literal id="litexc" EnableViewState="false" runat="server" />
<ASP:DataGrid id="MyDataGrid" EnableViewState="false" runat="server" />
</body></html>
|