Monday, April 14, 2008

How to Export Gridview data to Excel

The following line of codes will help you export data in Gridview to Excel without using Excel COM or third party components:

private void ExportGridtoExcel()
{

Response.Clear();
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=OverviewReport.xls");

System.IO.StringWriter objStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter objHTMLTextWriter = new HtmlTextWriter
(objStringWriter);

GridView objGridView = new GridView();
// set the page size to a number that will show all data of the gridview in one page since this
// functionality is dependend on how many data are visible on the first page on the gridview
objGridView.PageSize = 1000;
objGridView.AllowPaging = false;
objGridView.DataSource = dt;
objGridView.DataBind();
objGridView.RenderControl(objHTMLTextWriter);

Response.Write(objStringWriter.ToString());
Response.End();

}

Thursday, September 13, 2007

How To Add Confirmation Box Before Deleting Data in DataGrid

You can add confirmation box before deleting data in your datagrid. Use the ItemCreated event of Datagrid. This event is raised when an item in the DataGrid control is created, both during round-trips and at the time data is bound to the control. See below example for your reference.


private void Datagrid1_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.AlternatingItem e.Item.ItemType == ListItemType.Item)
{
LinkButton lb = (LinkButton)e.Item.Cells[0].Controls[0];
lb.Attributes.Add("Onclick","return confirm('Are you sure you want to delete this item?');");
}
}

Tuesday, September 11, 2007

SQL: ROW_NUMBER()

Last time I encountered problem on my ASP.NET web application because I need to optimize the response time of the paging of my Gridview displaying 18000+ records from the database. While researching for good solutions, I came across with this SQL statement ROW_NUMBER().

Definition:Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax: ROW_NUMBER() OVER([-partition_by_clause-] -order_by_clause-)
Arguments:"partition_by_clause" - Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
"order_by_clause" - Determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the is used in a ranking function.

Return Type: bigint

Remarks:The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Note:
The ORDER BY in the OVER clause orders ROW_NUMBER. If you add an ORDER BY clause to the SELECT statement that orders by a column(s) other than 'Row Number' the result set will be ordered by the outer ORDER BY.
For examples regarding the usage of this statement, you can refer to this link: http://msdn2.microsoft.com/en-us/library/ms186734.aspx

The sad thing on my end is that I cannot use this as part of my solution since it is only comatible with MS SQL Server 2005 and I am using the 2000 version. :(

Hello World

Since I'm a VS.Net and MS SQL Developer, it came across to my mind to create a blog which is related to my work and on which I know I can excel most and eventually share my knowledge. This blog also tends to help fellow developers out there who seeks solutions on problems related to VS.NET and MS SQL programming on which I know some solutions will face criticisms since it may not be considered as best practice. But I am very much thankful to those who will give criticisms or refute any ideas that I have for I know I will learn from them and be able to give more efficient and effective solutions. It will also make my blog an interactive one. So see you guys here!