Data Rendering in ASP.NET Data Controls

[balindus]

Hello All,

Need help in the following:

Assume that a SQL query has 3 columns the data is shown something as:

Content1 Country USA
Content1 Country UK
Content1 Alias MyName
Content2 Country Canada
Content2 Alias OtherName

---keeping in mind the above way of the records, i would like to know how we can display the same on ASP.NET page using any data control in the following way??

CONTENT ALIAS COUNTRY
Content1 MyName USA, UK
Content2 OtherName Canada

Here CONTENT, ALIAS, COUNTRY are the column headers in the data control (can be datagrid etc..), keep in mind ALIAS and COUNTRY are records in the sql query mentioned above

[zmbharmal]

Take your input from the Database as a Dataset.
The DataSet.Tables[0] will have a DataTable.
Iterate through this old DataTable

Create a new DataTable. Create DataColumns. Create a new Row. Wherever you have two items seprated by a ',' , you create your own String and stick it in that new column/row.

Keep adding New Rows to new DataTable. Iterate through all the rows in old datatable.

Basically, you take your old DataTable returned from SQL Stored Procedure (or query), modify it to the way you want it , put it in another DataTable. You have to constcurtc the Columns and Rows of the second DataTable manually.

something like this:
private MakeTable(){
// Create a DataTable. DataTable myTable = new DataTable("myTable");
// Create a DataColumn and set various properties.
DataColumn myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType("System.Decimal");
myColumn.AllowDBNull = false;
myColumn.Caption = "Price";
myColumn.ColumnName = "Price";
myColumn.DefaultValue = 25;
// Add the column to the table.
myTable.Columns.Add(myColumn);
// Add 10 rows and set values.
DataRow myRow;
for(int i = 0; i < 10; i++){
myRow = myTable.NewRow(); myRow["Price"] = i + 1;
// Be sure to add the new row to the DataRowCollection.
myTable.Rows.Add(myRow);
}
}

Once you get a DataTable, you can bind it to the DataGrid. Use BoundColumns.
<asp:datagrid id="DataGridInTransit" runat="server" AlternatingItemStyle-BackColor="#999999"
AutoGenerateColumns="False" CssClass="Arial10-Black" ShowHeader=true BorderWidth=0 AllowSorting=True OnSortCommand="SortInTransit" Width="100%" >
<AlternatingItemStyle BackColor="#999999"></AlternatingItemStyle>
<HeaderStyle CssClass="Freezing" BackColor=#FFFFFF ></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="Transaction_ID" SortExpression="Transaction_ID" ItemStyle-CssClass="Arial9-Black" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Trade ID</span>" ></asp:BoundColumn>
<asp:BoundColumn DataField="Year" SortExpression="Year" ItemStyle-CssClass="Arial9-Black" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Yr</span>" ></asp:BoundColumn>
<asp:BoundColumn DataField="Make" SortExpression="Make" ItemStyle-Width="5%" ItemStyle-Wrap=True ItemStyle-CssClass="Arial9-Black" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Make</span>"></asp:BoundColumn>
<asp:BoundColumn DataField="Model" SortExpression="Model" ItemStyle-Width="5%" ItemStyle-Wrap=true ItemStyle-CssClass="Arial9-Black" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Model</span>" ></asp:BoundColumn>
<asp:BoundColumn DataField="Series" ItemStyle-Wrap=True ItemStyle-Width=80 SortExpression="Series" ItemStyle-CssClass="Arial9-Black" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Type/Series</span>" ></asp:BoundColumn>
<asp:HyperLinkColumn Target="_blank" DataNavigateUrlField="Condition_URL" DataTextField="Vin" HeaderText="VIN" ></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Est_Delivery_Date" ItemStyle-CssClass="Arial10-Black" HeaderText="Est. Delivery&lt;br&gt;Date"></asp:BoundColumn>
<asp:BoundColumn DataField="turn_time" SortExpression="turn_time" ItemStyle-CssClass="Arial10-Black" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Turn<br>Time</span>"></asp:BoundColumn>
<asp:HyperLinkColumn Target="_blank" DataNavigateUrlField="LIKE_HISTORY_URL" DataTextField="LIKE_HISTORY" HeaderText="<span style='cursor:hand;' class='SortingTableHead'>60-DAYS <BR> HISTORY</span>" ItemStyle-CssClass="Arial11-Black"></asp:HyperLinkColumn>

<asp:BoundColumn DataField="Total_Purchase_Amount" ItemStyle-CssClass="Arial9-Black" SortExpression="Total_Purchase_Amount" DataFormatString="{0:$0,0}" ItemStyle-HorizontalAlign=Right HeaderStyle-HorizontalAlign=Right HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Purchase<br>Amount</span>"></asp:BoundColumn>
<asp:BoundColumn DataField="target_retail_amount" ItemStyle-CssClass="Arial9-Black" SortExpression="target_retail_amount" DataFormatString="{0:$0,0}" ItemStyle-HorizontalAlign=Right HeaderStyle-HorizontalAlign=Right HeaderText="<span style='cursor:hand;' class='SortingTableHead'>Retail<br> Amount</span>"></asp:BoundColumn>
</Columns>
</asp:datagrid>