Wednesday, 11 June 2014

Merge GridView Cells or Columns in a Row in ASP.Net using C# VB.Net

HTML Markup
The HTML Markup consists of an ASP.Net GridView with three columns populated from the Customers Table of the Northwind Database.
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
    runat = "server" AutoGenerateColumns="false" OnDataBound="OnDataBound">
    <Columns>
        <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
        <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />
        <asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150" />
    </Columns>
</asp:GridView>

You will notice that we have specified the OnDataBound event of the GridView, this event will be used to write the logic for merging the GridView Columns or Cells.

Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration


Binding the GridView
Below is the code to populate the ASP.Net GridView with the records from the Customers Table of the Northwind Database.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        GridView1.DataSource = GetData("SELECT ContactName, Country, City FROM Customers GROUP BY Country, City, ContactName");
        GridView1.DataBind();
    }
}
private DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As New DataTable()
        GridView1.DataSource = GetData("SELECT ContactName, Country, City FROM Customers GROUP BY Country, City, ContactName")
        GridView1.DataBind()
    End If
End Sub

Private Function GetData(query As String) As DataTable
    Dim dt As New DataTable()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                sda.SelectCommand = cmd
                sda.Fill(dt)
            End Using
        End Using
        Return dt
    End Using
End Function

Merge GridView Cells or Columns in a Row in ASP.Net using C# VB.Net


Merge Common Cells or Columns in a Row in ASP.Net GridView
The OnDataBound event of the GridView is executed after the GridView is populated with records. Here a reverse loop is executed over the GridView Rows and then the common Cells are identified and merged into single cell.
C#
protected void OnDataBound(object sender, EventArgs e)
{
    for (int i = GridView1.Rows.Count - 1; i > 0; i--)
    {
        GridViewRow row = GridView1.Rows[i];
        GridViewRow previousRow = GridView1.Rows[i - 1];
        for (int j = 0; j < row.Cells.Count; j++)
        {
            if (row.Cells[j].Text == previousRow.Cells[j].Text)
            {
                if (previousRow.Cells[j].RowSpan == 0)
                {
                    if (row.Cells[j].RowSpan == 0)
                    {
                        previousRow.Cells[j].RowSpan += 2;
                    }
                    else
                    {
                        previousRow.Cells[j].RowSpan = row.Cells[j].RowSpan + 1;
                    }
                    row.Cells[j].Visible = false;
                }
            }
        }
    }
}

VB.Net
Protected Sub OnDataBound(sender As Object, e As EventArgs)
    For i As Integer = GridView1.Rows.Count - 1 To 1 Step -1
        Dim row As GridViewRow = GridView1.Rows(i)
        Dim previousRow As GridViewRow = GridView1.Rows(i - 1)
        For j As Integer = 0 To row.Cells.Count - 1
            If row.Cells(j).Text = previousRow.Cells(j).Text Then
                If previousRow.Cells(j).RowSpan = 0 Then
                    If row.Cells(j).RowSpan = 0 Then
                        previousRow.Cells(j).RowSpan += 2
                    Else
                        previousRow.Cells(j).RowSpan = row.Cells(j).RowSpan + 1
                    End If
                    row.Cells(j).Visible = False
                End If
            End If
        Next
    Next
End Sub


Merge GridView Cells or Columns in a Row in ASP.Net using C# VB.NetHow to Marge Gridview adjacent cells depending on cells value in ASP.NET


INTRODUCTION
In this post I am explain How to Marge Gridview adjacent cells depending on cells value in ASP.NET
My previous post was : How to group columns in gridview header row in ASP.NET (programmer friendly way)



STEPS :
STEP - 1 : CREATE NEW PROJECT.
Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

STEP-2: ADD A DATABASE.
Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.


STEP-3: CREATE TABLE FOR FETCH DATA.
Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok. 
In this example, I have used one tables as below

STEP-4: ADD ENTITY DATA MODEL.
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish. 


STEP-5: ADD A WEBPAGE AND DESIGN FOR SHOW DATA IN GRIDVIEW WITH MERGED TWO OR MORE ADJACENT CELLS
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code 
?
1
2
3
4
5
6
7
8
9
<h3>Merge adjacent cells in Gridview depanding on Cells value in ASP.NET </h3><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"  CellPadding="5"
    BackColor="#ffffff" HeaderStyle-BackColor="#f3f3f3" OnDataBound="GridView1_DataBound">
    <Columns>
        <asp:BoundField HeaderText="Country" DataField="Country" />
        <asp:BoundField HeaderText="State / Zone" DataField="State" />
        <asp:BoundField HeaderText="City" DataField="City" />
    </Columns>
</asp:GridView>


STEP-6: WRITE FOLLOWING CODE IN PAGE_LOAD EVENT FOR SHOW DATA IN GRIDVIEW WITH MERGED TWO OR MORE ADJACENT CELLS.

?
1
2
3
4
5
6
7
protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         PopulateData();
     }
 }
and here is the function...
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
     private void PopulateData()
      {
          // Populate Data from Server and show in Gridview
          List<citymaster> allCity = new List<citymaster>();
          using (MyDatabaseEntities dc = new MyDatabaseEntities())
          {
              // Here order by is very required for merge gridview cell based on value in database query
              allCity = dc.CityMasters.OrderBy(a => a.Country).ThenBy(a => a.State).ThenBy(a => a.City).ToList();
              //Here thenby is used for sort as first based on country then by state and then city

              GridView1.DataSource = allCity;
              GridView1.DataBind();

          }
      }
</citymaster></citymaster>

STEP-7: WRITE FOLLOWING CODE IN GRIDVIEW1_DATABOUND EVENT FOR MERGE GRIDVIEW CELLS DEPENDING ON ITS VALUE .
Here for merge gridview cells I have used GridView1_DataBound event. This event is very suitable for this purpose as its fired after data binding complete. 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// Here this event I have used for merge cells as This event is fired after complete data binding
   protected void GridView1_DataBound(object sender, EventArgs e)
   {
       for (int rowIndex = GridView1.Rows.Count -2 ; rowIndex >= 0; rowIndex--)
       {
           GridViewRow gvRow = GridView1.Rows[rowIndex];
           GridViewRow gvNextRow = GridView1.Rows[rowIndex + 1];

           // compare cell value if found duplicate value then marge cell
           for (int cellIndex = 0; cellIndex < gvRow.Cells.Count; cellIndex++)
           {
               if (gvRow.Cells[cellIndex].Text == gvNextRow.Cells[cellIndex].Text)
               {
                   if (gvNextRow.Cells[cellIndex].RowSpan < 2)
                   {
                       gvRow.Cells[cellIndex].RowSpan = 2;
                   }
                   else
                   {
                       gvRow.Cells[cellIndex].RowSpan = gvNextRow.Cells[cellIndex].RowSpan + 1;
                   }
                   gvNextRow.Cells[cellIndex].Visible = false;
               }
           }
       }
   }
STEP-8: RUN APPLICATION.
public string RemoveDuplicates(string items)
        {
            StringBuilder Result = new StringBuilder();


            String[] newArray = items.Split(',');


            for (int i = 0; i < newArray.Length; i++)
            {
                if (Result.ToString().IndexOf(newArray[i]) == -1)
                {
                    Result.Append(newArray[i].ToString()).Append(",");
                }
            }
            return Result.ToString().Substring(0, Result.ToString().LastIndexOf(','));

        }


at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

CREATE PROCEDURE [CSMSDVLP].[DiskSpaceMonitor] @mailProfile nvarchar(500), @mailto nvarchar(4000), @threshold INT, @logfile nvarchar(40...