Merge GridView Cells

September 25, 2012

In this example i am going to describe how to Merge GridView Cells Or Columns In Gridview Rows Using C# and VB.NET in ASP.NET Containing Same Data or content. For this i m using DataBound Event of gridview, counting total rows and then checking each cells value against value of same cell in previous row and then setting the RowSpan of cells.

Merge GridView Cells Or Columns

For this i have created a table containing Counties ,states and respective cities and country and state cells / columns are merged in rows having same country or states.

Html source of the page look like this 

<asp:GridView ID="GridView1" runat="server" 
    AutoGenerateColumns="False"  
    BorderStyle="None" BorderWidth="1px" CellPadding="4" 
    GridLines="Horizontal" ForeColor="Black" 
    Height="119px" DataSourceID="SqlDataSource1" 
    OnDataBound="GridView1_DataBound1"> 
            <Columns>
            <asp:BoundField DataField="Country" 
                            HeaderText="Country" 
                            SortExpression="Country" />
            <asp:BoundField DataField="State" 
                            HeaderText="State" 
                            SortExpression="State" />
            <asp:BoundField DataField="City" 
                            HeaderText="City" 
                            SortExpression="City" />
        </Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Country], [State], [City] 
               FROM [Details] ORDER BY [State]">
</asp:SqlDataSource>
C# code behind
protected void GridView1_DataBound1(object sender, EventArgs e)
{
  for (int rowIndex = GridView1.Rows.Count - 2; 
                                     rowIndex >= 0; rowIndex--)
  {
    GridViewRow gvRow = GridView1.Rows[rowIndex];
    GridViewRow gvPreviousRow = GridView1.Rows[rowIndex + 1];
    for (int cellCount = 0; cellCount < gvRow.Cells.Count; 
                                                  cellCount++)
    {
     if (gvRow.Cells[cellCount].Text == 
                            gvPreviousRow.Cells[cellCount].Text)
     {
       if (gvPreviousRow.Cells[cellCount].RowSpan < 2)
       {
         gvRow.Cells[cellCount].RowSpan = 2;
       }
       else
       {
        gvRow.Cells[cellCount].RowSpan = 
            gvPreviousRow.Cells[cellCount].RowSpan + 1;
       }
       gvPreviousRow.Cells[cellCount].Visible = false;
    }
   }
 }
}
VB.NET code behind
Protected Sub GridView1_DataBound1
           (ByVal sender As Object, ByVal e As EventArgs)

For rowIndex As Integer = GridView1.Rows.Count - 2 To 0 Step -1
    Dim gvRow As GridViewRow = GridView1.Rows(rowIndex)
    Dim gvPreviousRow As GridViewRow = GridView1.Rows(rowIndex + 1)
    For cellCount As Integer = 0 To gvRow.Cells.Count - 1
    If gvRow.Cells(cellCount).Text = 
                         gvPreviousRow.Cells(cellCount).Text Then
    If gvPreviousRow.Cells(cellCount).RowSpan < 2 Then
    gvRow.Cells(cellCount).RowSpan = 2
    Else
    gvRow.Cells(cellCount).RowSpan = 
                       gvPreviousRow.Cells(cellCount).RowSpan + 1
    End If
    gvPreviousRow.Cells(cellCount).Visible = False
    End If
    Next
  Next
End Sub
Happy Coding !!


0

DataTableReader in ADO.NET

August 28, 2012

Introduction
This article explores the concept of using DataTableReader in the place of SqlDataReader and DataTable, also explains the methods of creating, reading and using the DataTableReader in the web applications.

In this fast and furious world, everyone needs everything to be faster. Once upon a time Pentium MMX was considered as faster system, but now we don’t accept even Pentium Dual core as the fastest system. If this is the situation for a computer system, then what will be the situation for the data we managed in our web application? Readers need data to be load faster into their webpage irrespective of the size of data that page contains. So someone in someway needs a solution to overcome this problem. Let us go straight to the concept.

What is DataTableReader? As briefly explained in MSDN, a DataTableReader obtains the contents of one or more DataTable objects in the form of one or more read-only, forward-only result sets. As the name suggests, it is a combination of both DataTable and SqlDataReader. In a DataTable, we can store a single database table records, with all constraints, in a disconnected mode from the database server. And we can perform all sorts of database manipulations in it. A SqlDataReader can contain single database table records, with read-only and forward-only record sets, for which we need an active connection with the database server. And we cannot perform other database manipulations in a SqlDataReader. A DataTableReader can contain more than one DataTable(s), in a disconnected mode, as a read-only and forward-only record sets.

Advantages of using DataTableReaderSqlDataReader are much faster than DataSet and consume less memory. But the major drawback of using SqlDataReader is that it always required an open connection to operate, that is, it is connection oriented. Hence we needed to explicitly close the database connections when we were done using it.In ADO.NET 2.0, DataTableReader class has been developed similar to it but with one exception – it works in a disconnected mode. Clearly opening and closing of database server connection is taken care by the DataTableReader itself. The iteration of rows is done from the cache. The cached data can be modified while the DataTableReader is active, and the reader automatically maintains its position.

Creating a simple DataTableReader

DataTableReader can be created from any DataTable’s CreateDataReader method. Let us see the syntax to create a simple DataTableReader and iterate the records in it.

private void FetchDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dt = new DataTable(); da.Fill(dt);
DataTableReader dtr = dt.CreateDataReader();
if (dtr.HasRows)
{
while (dtr.Read())
{
Response.Write(dtr[“Cus_Name”].ToString() + "<br/>");
}
}
else
Response.Write("No Data");
}

From the above block of statement, you are familiar with the first 4 lines of code. The next line, we are creating a DataTableReader object, by using CreateDataReader method. Then we can check if rows exists in the DataTableReader with HasRows and the Read method will advances the DataTableReader to the next record. The output is as follows

Alex
Flintoff
Mark
Jeniffer
Christiana
Leverlock

Creating a DataTableReader with more than one DataTablesOne of the nice features of DataTableReader is that it can contain more than one DataTables, as read-only and forward-only recordsets. When you load more than one DataTables in a DataTableReader, it is really faster to iterate and it will automatically deals with the unwanted records during the iteration. You can load bunches of DataTables by creating an object of DataTableReader to contain an array of DataTables. Let us see the sample code to create it.

private void FetchTwoDataTablesInDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus); string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry); DataTableReader dtr = new DataTableReader(new DataTable[] 
{dtCus, dtCountry});
if (dtr.HasRows)
{
do
{
while (dtr.Read())
{
Response.Write(dtr[1].ToString() + "<br/>");
}
} while (dtr.NextResult());
}
else
Response.Write("No Data");
}

In the above block of code, we are loading two datatables to the DataTableReader. So we are creating two datatables from a customer and country tables. Then we are creating an object of the DataTableReader from an array of DataTables. The method NextResult helps to advances between the datatables in the DataTableReader. One thing to be noted is that we are using the column index to retrieve the data instead of using column name, since it contains different tables with different field names. The result for the above code will be as follows

Alex
Flintoff
Mark
Jeniffer
Christiana
Leverlock
Andorra
United Arab Emirates
Afghanistan
Antigua and Barbuda
Anguilla
Albania
Armenia
Netherlands Antilles
Angola
Antarctica

Iterating DataTableReader with Multiple DataTables and display all Columns In the above section, we pick only a single column from the DataTableReader. We can also iterate through all the columns in the DataTableReader with the help of FieldCount property. The code is as follows

private void FetchTwoDataTablesInDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus); string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry); DataTableReader dtr = new DataTableReader(new DataTable[] 
{dtCus, dtCountry});
if (dtr.HasRows)
{
do
{
while (dtr.Read())
{
for (int i = 0; i < dtr.FieldCount; i++)
{
Response.Write(dtr[i]);
}
Response.Write("<br/>");
}
} while (dtr.NextResult());
}
else
Response.Write("No Data");
}

Run the application will result the following output.

1 Alex M Oxford Alabama Retailer
2 Flintoff M Southside Alabama Retailer
3 Mark M Avon Park Florida Retailer
4 Jeniffer F Destin Florida Wholesale
5 Christiana F Chester New York Retailer
6 Leverlock M Baytown Texas Wholesale


1 Andorra AD
2 United Arab Emirates AE
3 Afghanistan AF
4 Antigua and Barbuda AG
5 Anguilla AI
6 Albania AL
7 Armenia AM
8 Netherlands Antilles AN
9 Angola AO
10 Antarctica AQ

Binding DataTableReader with GridView control

Another great feature of the DataTableReader class is that you can use it as the data source to populate Dataset or DataTable information into an Asp.Net 2.0 GridView control in a very easy way. This is done by using the Load method of the DataTableReader. Let us see the sample code for this.

private void LoadGridViewWithDataTableReader()
{
DataSet ds = new DataSet(); string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus);
ds.Tables.Add(dtCus); string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry);
ds.Tables.Add(dtCountry); DataTable dtGrid = new DataTable();
DataTableReader dtr = new DataTableReader(ds.Tables[0]);
dtGrid.Load(dtr); GridView1.DataSource = dtGrid;
GridView1.DataBind();
}

Eventhough DataTableReader is an excellent concept and got lot of advantages when compared to SqlDataReader, it is not yet widely used in web application development. Sure this article will bring and give the knowledge of DataTableReader to the readers and encourage them to use it.

0

Whats new in SQL Server 2012 Execution Plans

June 4, 2012

Execution plans show you what’s going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by
SQL Server, including:

• Which indexes are being used, and where no indexes are being used at all.
• How the data is being retrieved, and joined, from the tables defi ned in your query.
• How aggregations in GROUP BY queries are put together.
• The anticipated load, and the estimated cost, that all these operations place upon
the system.

All this information makes the execution plan a vitally important part for database administrators, developers, report writers, and pretty much anyone who writes TSQL to access data in a SQL Server database.

In SQL Server 2012, there are new operators such as Window Spools (for the new windowing functions), Offsets/Sequences and ColumnStore Index Scans (including whether execution mode was row or batch). There is also information about hint changes first introduced in SQL Server 2008 R2 SP1 (FORCESCAN, and FORCESEEK with columns).

In SQL Server 2012, there are also new warnings at the operator level, such as spills to tempdb (from sort operations or aggregates), and more details about implicit conversion warnings (for example, whether a conversion affects cardinality or seek choice). At the statement level, we can see information about things like whether the plan was retrieved from cache. And at the plan level, we can see many new properties, including the reason a plan was not parallel (for example, MAXDOP), detailed memory grant information (including, in some cases, how much memory was requested and how long the grant took), and deeper information about parallel queries (for example, thread usage across NUMA nodes).

To get an idea of all the information that is coming to a ShowPlan near you in SQL Server 2012, and even to get some insight into future features that have yet to be announced, perform a file comparison between the latest XSD and the 2008/2008 R2 version. You can find the SQL Server 2012 XSD at the following location:

C:\Program Files (x86)\
Microsoft SQL Server\110\Tools\Binn\schemas\
sqlserver\2004\07\showplan\showplanxml.xsd

(For SQL Server 2008/R2, change 110 to 100.)

0

Columnstore Index Restrictions

March 20, 2012

Although columnstore indexes work with the majority of the data types, components, and features found in SQL Server 2012, columnstore indexes have the following restrictions and cannot be leveraged in the following situations:

• You can enable PAGE or ROW compression on the base table, but you cannot enable PAGE or ROW compression on the columnstore index.

• Tables and columns cannot participate in a replication topology.

• Tables and columns using Change Data Capture are unable to participate in a columnstore index.

• Create Index: You cannot create a columnstore index on the following data types:

      o decimal greater than 18 digits
      o binary and varbinary
      o BLOB
      o CLR
      o (n)varchar(max)
      o datetimeoffset with precision greater than 2

• Table Maintenance: If a columnstore index exists, you can read the table but you cannot directly update it. This is because columnstore indexes are designed for data-warehouse workloads that are typically read based. Rest assured that there is no need to agonize. The upcoming “Columnstore Index Design Considerations and Loading Data” section articulates strategies on how to load new data when using columnstore indexes.

• Process Queries: You can process all read-only T-SQL queries using the columnstore index, but because batch processing works only with certain operators, you will see that some queries are accelerated more than others.

• A column that contains filestream data cannot participate in a columnstore index.

• INSERT, UPDATE, DELETE, and MERGE statements are not allowed on tables using columnstore indexes.

• More than 1024 columns are not supported when creating a columnstore index.

• Only nonclustered columnstore indexes are allowed. Filtered columnstore indexes are not allowed.

• Computed and sparse columns cannot be part of a columnstore index.

• A columnstore index cannot be created on an indexed view.

0

Columnstore Index Support and SQL Server 2012

March 20, 2012

Columnstore indexes and batch-query execution mode are deeply integrated in SQL Server 2012, and they work in conjunction with many of the Database Engine features found in SQL Server 2012. For example, database administrators can implement a columnstore index on a table and still successfully use AlwaysOn Availability Groups (AG), AlwaysOn failover cluster instances (FCI), database mirroring, log shipping, and SQL Server Management Studio administration tools. Here are the common business data types supported by columnstore indexes:
• char and varchar
• All Integer types (int, bigint, smallint, and tinyint)
• real and float
• string
• money and small money
• All date, time, and DateTime types, with one exception (datetimeoffset with precision greater than 2)
• Decimal and numeric with precision less than or equal to 18 (that is, with less than or exactly 18 digits)
• Only one columnstore index can be created per table.

0