Grouping in Asp.net gridview control

When displaying data, we sometimes would like to group data for better user experience.When displaying a long list of sorted data where there are only a handful of different values in the sorted column, an end user might find it hard to discern where, exactly, the difference boundaries occur. For example, there are 81 products in the database, but only nine different category choices. To help highlight the boundaries between sorted groups, many Web sites employ a user interface that adds a separator between such groups. In this example, that is what exactly we are going to do.

I am not going to cover any basics in this article. If you are new to using grid view or if you would like know more about grid view, I would recommend you to reach following tutorials:
Using GridView in ASP.NET & C# —PART 1
Using GridView in ASP.NET & C# —PART 2

Overview:
I am going to use Adventure Works as datasource. Every product in Production.Product table belongs to a product sub category. We fetch handful of products and the sub categories they belong to from the database. These products are sorted by ProductSubCategoryID. On the web page, we will add a group header row at the starting of every subcategory.

Database Connection
Added following entry under connectionStrings element in web.config.

<add name="Sql" connectionString="Data Source=(local);

Initial Catalog=AdventureWorks; User=testuser; Password=testuser;"

providerName="System.Data.SqlClient"/>

Page Design
I have created a ASP.NET web application project and added a new web page “NestedProductsView.aspx”. I have added the Gridview control to the web page and applied some simple formatting to make it look nice. We are interested in ProdutID, ProductName, ProductNumber and LisPrice attributes of the product. I have added four BoundColumns to the GridView to render these attributes. I have additional formatting to format the Price column as a currency column. Finally, I have added a hidden field to the grid rendered in a TemplateField. The hidden field is mapped to the subcategory name of the data source. We use this hidden field in the code-behind class to figure out the start/end position of the group header row.

<asp:gridview id="gvProducts"

autogeneratecolumns="False"

emptydatatext="No data available."

GridLines="None"

runat="server" DataKeyNames="ProductID"

CssClass="GridStyle">

<AlternatingRowStyle CssClass="AlternatingRowStyle" />

<HeaderStyle CssClass="ColumnHeaderStyle" />

<Columns>

<asp:BoundField DataField="ProductID" HeaderText="Product ID">

<ItemStyle Width="200px"/>

</asp:BoundField>

<asp:BoundField DataField="Name" HeaderText="Product Name”>

<HeaderStyle HorizontalAlign="Left"/>

<ItemStyle Width="200px" HorizontalAlign="Left"/>

</asp:BoundField>

<asp:BoundField DataField="ProductNumber" HeaderText="Product Number" />

<asp:BoundField HeaderText="Price"

DataField="ListPrice"

DataFormatString="{0:c}">

<ItemStyle HorizontalAlign="Right"></ItemStyle>

</asp:BoundField>

<asp:TemplateField>

<ItemTemplate>

<asp:HiddenField ID="hfSubCategory" runat="server"

Value='<%#Eval("SubCategoryName")%>' />

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:gridview>

Source Code:
First and foremost thing to do is loading the grid with list of products that are ordered by subcategory. I did that in the Page_Load event of the page and saved products list in the ViewState for any page refreshes.

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

BindData();

}

private void BindData()

{

//Bind the grid view

gvProducts.DataSource = RetrieveProducts();

gvProducts.DataBind();

}

private DataSet RetrieveProducts()

{

if (ViewState["Products"] != null)

return (DataSet)ViewState["Products"];

//fetch the connection string from web.config

string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;

//SQL statement to fetch sorted entries from products

string sql = @"Select top 20 P.*,PS.ProductSubCategoryID,PS.Name as

SubCategoryName from Production.Product P

inner join Production.ProductSubCategory PS

on P.ProductSubCategoryID = PS.ProductSubCategoryID

order by PS.ProductSubCategoryID desc";

DataSet dsProducts = new DataSet();

//Open SQL Connection

using (SqlConnection conn = new SqlConnection(connString))

{

conn.Open();

//Initialize command object

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

//Fill the result set

adapter.Fill(dsProducts);

}

}

return dsProducts;

}

When the GridView is bound to a data source, it creates a GridViewRow for each record returned by the data source. Therefore, we can inject the needed separator rows by adding “separator records” to the data source before binding it to the GridView. Because we only want to add the separator rows to the GridView‘s control hierarchy after its control hierarchy has been created and created for the last time on that page visit, we want to perform this addition at the end of the page lifecycle, but before the actual GridView control hierarchy has been rendered into HTML. The latest possible point at which we can accomplish this is the Page class’s Render event, which we can override in our code-behind class.

We get the reference to the GridView’s Table object. We start by iterating through all the rows in the grid view. For each row, we get the reference to the hidden field control. We store the value of the field in the currSubCategory field. I have created another string variable lastSubCategory, which holds the last row’s sub category value. Comparing these two variables would tell you whether to insert the seperator row. This is accomplished by determining the index of the GridViewRow in the Tableobject’s Rows collection, creating new GridViewRow and TableCell instances, and then adding the TableCell and GridViewRow to the control hierarchy.

Note that the separator row’s lone TableCell is formatted so that it spans the entire width of the GridView, is formatted using the GroupHeaderRowStyle CSS class, and has its Text property such that it shows both the group name (such as “SubCategory”) and the group’s value (such as “Tires and Tubes”). Finally, lastSubCategory is updated to the value of currSubCategory.

protected override void Render(HtmlTextWriter writer)

{

string lastSubCategory = String.Empty;

Table gridTable = (Table)gvProducts.Controls[0];

foreach (GridViewRow gvr in gvProducts.Rows)

{

HiddenField hfSubCategory = gvr.FindControl("hfSubCategory") as

HiddenField;

string currSubCategory = hfSubCategory.Value;

if (lastSubCategory.CompareTo(currSubCategory) != 0)

{

int rowIndex = gridTable.Rows.GetRowIndex(gvr);

// Add new group header row

GridViewRow headerRow = new GridViewRow(rowIndex, rowIndex,

DataControlRowType.DataRow, DataControlRowState.Normal);

TableCell headerCell = new TableCell();

headerCell.ColumnSpan = gvProducts.Columns.Count;

headerCell.Text = string.Format("{0}:{1}", "SubCategory",

currSubCategory);

headerCell.CssClass = "GroupHeaderRowStyle";

// Add header Cell to header Row, and header Row to gridTable

headerRow.Cells.Add(headerCell);

gridTable.Controls.AddAt(rowIndex, headerRow);

// Update lastValue

lastSubCategory = currSubCategory;

}

}

base.Render(writer);

}

I have used the following CSS style sheet for formatting the grid and its rows.

body {

margin: 0;

background-color: #FFFFFF;

color: #000000;

font-family: Verdana, Arial, Helvetica, sans-serif;

}

.GridStyle

{

font-size: 90%;

}

.ColumnHeaderStyle

{

background-color: #000000;

color: White;

font-weight: bold;

}

.AlternatingRowStyle

{

background-color: #66CCFF;

}

.RowStyle

{

background-color: #66CCFF;

}

.GroupHeaderRowStyle

{

background-color: Blue;

text-align: left;

font-weight: bold;

color: White;

}

Page Output:

source : http://technico.qnownow.com/grouping-gridview-aspnet/

Table valued function using cross apply

able Valued Function using cross apply

If you want to pass the parameter of table column in the table value function we can use the cross apply. Also if you want to pass dynamic value to table valued function we can use the cross apply

Example

Consider a table with employee leave status, which has employee id, from date, to date and approved. We can use cross apply to return a particular date of the employee leave status using table valued function as explained in below code.

Now, create a table leave and insert records

1. createtable leave
2. (
3. LeaveId INT IDENTITY(1,1)
4. ,EmployeeId INT
5. ,FromDate Datetime
6. ,ToDate Datetime
7. ,[Status] VARCHAR(50)
8. )
9. Go
10. insertinto leave values(1,'2011-10-25','2011-10-27','Approved')
11. insertinto leave values(2,'2011-10-26','2011-10-27','Approved')
12. insertinto leave values(3,'2011-10-27','2011-10-27','Rejected')
13. insertinto leave values(1,'2011-11-01','2011-11-01','Approved')

The result of the leave table is as follows

LEAVE ID	EMPLOYEEID	FROM DATE	TO DATE	STATUS
1	1	2011-10-25	2011-10-27	Approved
2	2	2011-10-26	2011-10-27	Approved
3	3	2011-10-27	2011-10-27	Rejected
4	1	2011-11-01	2011-11-01	Approved

But as per our requirement we need a result as shown below

Employee Id	Date	Status
1	011-10-25 00:00:00.000	Approved
1	2011-10-26 00:00:00.000	Approved
1	2011-10-27 00:00:00.000	Approved
2	2011-10-26 00:00:00.000	Approved
2	2011-10-27 00:00:00.000	Approved
3	2011-10-27 00:00:00.000	Rejected
1	2011-11-01 00:00:00.000	Approved

We can use Table valued function with CROSS APPLY to resolve the issue.

Step: 1

The first step is to create function

1. CREATEFUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
2. returnstableas
3. return (
4. WITH date_range (startdate) AS (
5. select @startdate
6. UNIONALLSELECT DATEADD(DAY, 1, startdate)
7. FROM date_range
8. WHERE DATEADD(DAY, 1, startdate) <= @enddate
9. )
10. SELECT startdate FROM date_range
11. );

The above function will retrieve dates between two dates.

1. SELECT * FROM dbo.ExplodeDates ('2011-10-25', '2011-10-27')

If we run the above select query it will display result as follows

Start date
2011-10-25 00:00:00.000
2011-10-26 00:00:00.000
2011-10-27 00:00:00.000

Step: 2

We need to use cross apply to pass data to the function to get the specified result. It won’t work if we pass an argument statically.

1. GO
2. SELECT EmployeeId, startdate, [Status] FROM leave
3. CROSS APPLY
4. DBO.ExplodeDates(leave.FromDate, leave.ToDate)
5. GO

If you execute the above the result is achived.

Source : https://sql-programmers.com/table-valued-function-using-cross-apply

How to conditionally set the number of decimal places to display for a number, in Crystal Reports

Symptom

• How to conditionally change the number of decimals?
     
• In the Crystal Reports, a number field that returns decimal places is inserted into a report.
  When previewing the report, the decimal places in this number field contain unnecessary zeroes.
  How can you suppress the unnecessary zeroes in these fields and still leave up to two decimal places for fields that require them?
       
  For example:
     
  A number field is placed on a Crystal Report and is not formatted the way you want.
    
  The field displays: 
    
     1.25 
     2.50 
     8.00
    
  But you want the numeber to display like:
      
     1.25
     2.5
     8

Environment

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016

Resolution

• To conditionally suppress unnecessary zero values to the right of the decimal for numeric field in Crystal Reports:
      
1. Right-click the number field and select: 'Format Field'
     
2. In the 'Format Editor' dialog box, under the 'Number' tab,  click the 'Customize' button.
      
3. In the 'Decimals' drop-down box, select the maximum number of decimal places to be displayed. 
       
   If you are not sure what the maximum number of decimals will be, click the maximum (1.0000000000).   
       
4. In the 'Rounding' drop-down box, select the same number of decimal places chosen in the 'Decimal' drop-down box.
         
5. Click the 'X+2' button to the right of the `Decimals` drop-down box and enter the following formula:
     
      WhilePrintingRecords;
      numberVar counter := 0;
      numberVar numericValue := <INSERT YOUR NUMERIC FIELD HERE>;
      While truncate(numericValue) < numericValue do
      (
          numericValue := numericValue * 10;
          counter := counter + 1
      );
      counter;
       
       
6. Save this formula. Click 'OK' to close the 'Custom Style' dilaog box, and then click 'OK' to close the 'Format Editor' dialog box.
      
   Now, when you preview the report, unnecessary zeroes to the right of the decimal will not appear. 
       
   For examples:
         
• 12.30 will display as: 12.3 
• 12.38 will display as: 12.38 
• 12.00 will display as: 12

source : https://apps.support.sap.com/sap/support/knowledge/public/en/1212821

StripNonNumerics

USE [TSWDATA_ClientCustom]
GO

/****** Object:  UserDefinedFunction [dbo].[StripNonNumerics]    Script Date: 6/28/2018 11:57:18 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[StripNonNumerics]
(
  @Temp varchar(255)
)
RETURNS varchar(255)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

GO