Microsoft .NET

16 January 2011

GridView Tips and Tricks using ASP.NET 2.0

The GridView control is quiet a handy control and is the most commonly used control when building an ASP.NET site. The more you work with it, the more you realize how powerful it can be while presenting data. In this article, we will explore some of the most frequently asked questions about the GridView control. The article discusses ten tips and tricks that you can use while using the GridView control.

Tip 1: Add, Update, Delete Records in a Gridview using SqlDataSource

By default, the GridView control doesn’t have support for inserting new records. However you can use the built-in edit or delete functionality of the GridView control. Let us explore how to insert new records and Update and Delete existing records in Gridview. Just copy and paste the code in your project. We will be using the ‘Categories’ table in the ‘Northwind’ database.

GridView.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridView.aspx.cs" Inherits="GridView" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Grid View Add Update Delete</title>

</head>

<body>

<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"

DataSourceID="SqlDataSource1" ShowFooter="true" AllowPaging="True" AllowSorting="True" OnRowCommand="GridView1_RowCommand">

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True"/>

<asp:TemplateField HeaderText="CategoryID" InsertVisible="False" SortExpression="CategoryID">

<asp:Label ID="Label1" runat="server" Text='<%# Eval("CategoryID") %>'></asp:Label>

</EditItemTemplate>

<asp:Label ID="Label1" runat="server" Text='<%# Bind("CategoryID") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="CategoryName" SortExpression="CategoryName">

<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("CategoryName") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label2" runat="server" Text='<%# Bind("CategoryName") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="CategoryNameTextBox" Runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Description" SortExpression="Description">

<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Description") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label3" runat="server" Text='<%# Bind("Description") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="DescriptionTextBox" Runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:templatefield>

<asp:linkbutton id="btnNew" runat="server" commandname="New" text="New" />

</footertemplate>

</asp:templatefield>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=SUPROTIM;Initial Catalog=Northwind;Integrated Security=True"

DeleteCommand="DELETE FROM [Categories] WHERE [CategoryID] = @CategoryID" InsertCommand="INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)"

ProviderName="System.Data.SqlClient" SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"

UpdateCommand="UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description WHERE [CategoryID] = @CategoryID">

<asp:Parameter Name="CategoryID" Type="Int32" />

</DeleteParameters>

<asp:Parameter Name="CategoryName" Type="String" />

<asp:Parameter Name="Description" Type="String" />

<asp:Parameter Name="CategoryID" Type="Int32" />

</UpdateParameters>

<asp:Parameter Name="CategoryName" Type="String" />

<asp:Parameter Name="Description" Type="String" />

</InsertParameters>

</asp:SqlDataSource>

</div>

</form>

</body>

</html>

GridView.aspx.cs

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

SqlConnection conn = new SqlConnection(

ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

try

{

if (e.CommandName.Equals("New"))

{

LinkButton btnNew = e.CommandSource as LinkButton;

GridViewRow row = btnNew.NamingContainer as GridViewRow;

if (row == null)

{

return;

}

TextBox txtCatName = row.FindControl("CategoryNameTextBox") as TextBox;

TextBox txtDescription = row.FindControl("DescriptionTextBox") as TextBox;

SqlCommand cmd = new SqlCommand(

"INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)",

conn);

cmd.Parameters.AddWithValue("CategoryName", txtCatName.Text);

cmd.Parameters.AddWithValue("Description",txtDescription.Text);

conn.Open();

if (cmd.ExecuteNonQuery() == 1)

{

GridView1.DataBind();

}

catch (Exception ex)

{

}

finally

{

conn.Close();

}

Web.config

<addname="NorthwindConnectionString"connectionString="Data Source =.;Integrated Security = SSPI; Initial Catalog=Northwind;"/>

</connectionStrings>

Tip 2: Paging and Sorting a GridView without Refreshing a Page

If you have created a GridView and have bound it to a data source control, you can avoid postback during sorting and paging by setting ‘EnableSortingAndPagingCallbacks’ property of the GridView to True.

Just remember that when you set the 'EnableSortingAndPagingCallbacks' property to true, you cannot use Template Fields in the GridView.

Tip 3: Pop-up a Confirmation box before Deleting a row in GridView

Add a template field and drop a button in it, using which the user will delete the record. In the OnClientClick event, call the confirm() function as mentioned below:

<asp:TemplateField>

<asp:Button ID="btnDel" runat="server" Text="Delete"

CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete the record?');" />

</ItemTemplate>

</asp:TemplateField>

Tip 4: Display details of the Row selected in the GridView

Assuming you have a button called ‘Select’ in your GridView with CommandName ‘Select’, to find out the row clicked and display the row’s details, use this code:

private void GridView1_RowCommand(Object sender,

GridViewCommandEventArgs e)

{

if (e.CommandName == "Select")

{

int idx = Convert.ToInt32(e.CommandArgument);

GridViewRow selrow = GridView1.Rows[idx];

string fstCell = selrow.Cells[0].Text;

string scndCell = selrow.Cells[1].Text;

// and so on

// Thanks to Mark Rae (MVP) for pointing the typo. Earlier it was Cells[1] and Cells [2]

}

Tip 5: Retrieve Details of the Row being Modified in GridView

void GridView1_RowUpdated(Object sender, GridViewUpdatedEventArgs e)

{

// Retrieve the row being edited.

int index = GridView1.EditIndex;

GridViewRow row = GridView1.Rows[index];

// Retrieve the value of the first cell

lblMsg.Text = "Updated record " + row.Cells[1].Text;

}

Tip 6: Retrieve Details of the Row being Deleted in GridView

The ID of the row being deleted must be in the GridView.DataKeyNames collection.

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

int ID = (int)GridView1.DataKeys[e.RowIndex].Value;

// Query the database and get the values based on the ID

}

Tip 7: Cancelling Update and Delete in a GridView

RowUpdating - Occurs when a row's Update button is clicked, but before the GridView control updates the row.

RowDeleting – Occurs when a row's Delete button is clicked, but before the GridView control deletes the row.

protected void gvDetail_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

e.Cancel = true;

}

void GridView1_RowDeleting(Object sender, GridViewDeleteEventArgs e)

{

// Check for a condition and cancel the delete

// There should be atleast one row left in the GridView

if (GridView1.Rows.Count <= 1)

{

e.Cancel = true;

}

Tip 8: Paging and Sorting in GridView without using Datasource control

<asp:GridView ID="gridView" OnPageIndexChanging="gridView_PageIndexChanging"

OnSorting="gridView_Sorting" runat="server" />

private string ConvertSortDirectionToSql(SortDirection sortDireciton)
{
string newSortDirection = String.Empty;

   switch (sortDirection)
   {
      case SortDirection.Ascending:
         newSortDirection = "ASC";
         break;

      case SortDirection.Descending:
         newSortDirection = "DESC";
         break;
   }

return newSortDirection
}

protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gridView.PageIndex = e.NewPageIndex;
gridView.DataBind();
}

protected void gridView_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable dataTable = gridView.DataSource as DataTable;

   if (dataTable != null)
   {
      DataView dataView = new DataView(dataTable);
      dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);

      gridView.DataSource = dataView;
      gridView.DataBind();
   }
}

Tip 9: Delete Multiple rows in a GridView

If you have used Hotmail or any other similar email client, you might have observed that we have the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them. In this article, we will replicate this scenario for a gridview. A gridview allows us to delete only a single row at a time. We will extend this functionality to select multiple rows and delete all of the selected rows in a single stroke. In this article, I assume that you are aware of creating asp.net web applications and have worked with gridview.

The sample makes use of the Northwind database. We will be pulling data from the Employee table. For this sample to work, drop all the Foreign Key relationships on the Employee Table. To do so, in Sql Server Management Studio, browse to the Northwind database and open the Employee table in design view. Right click in the Table designer on the right hand side and choose ‘Relationships’. Select all the relationships like FK_Orders_Employees, FK_EmployeeTerritories_Employees etc and delete them. This step is necessary as we will get a constraint violation exception if we do not do so.

Once we are through with the task of removing the relationships in the Employee table, let us explore the steps to create a gridview with functionality to delete multiple rows at a time.

Perform the following steps :

Step 1: Create an .aspx page and add a GridView and a SqlDataSource control to it.

Step 2: Configure the connection of SqlDataSource to point to the Northwind database. Create queries for the Select and Delete commands. The resultant code will look similar as given below :

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

<asp:Parameter Name="EmployeeID" />

</DeleteParameters>

</asp:SqlDataSource>

Step 3: Once the SqlDataSource has been configured, bind the gridview with this data source.

Step 4: To create a checkbox in each row, follow these steps:

1. Create a TemplateField inside the <Columns> to add custom content to each column.

2. Inside the TemplateField, create an ItemTemplate with a CheckBox added to it.

<asp:TemplateField>

<asp:CheckBox ID="chkRows" runat="server"/>

</ItemTemplate>

</asp:TemplateField>

This will add a checkbox to each row in the grid.

Step 5: Add a button control, and rename it to btnMultipleRowDelete.

The resultant markup in the design view will look similar to the code below :

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">

<asp:TemplateField>

<asp:CheckBox ID="cbRows" runat="server"/>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />

<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />

<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

<asp:Parameter Name="EmployeeID" />

</DeleteParameters>

</asp:SqlDataSource>

<asp:Button

ID="btnMultipleRowDelete"

OnClick="btnMultipleRowDelete_Click"

runat="server"

Text="Delete Rows" />

In Code behind file (.cs) for C# and (.vb) for VB.NET, code the button click event. Our code will first loop through all the rows in the GridView. If a row is checked, the code retrieves the EmployeeID and passes the selected value to the Delete Command.

protected void btnMultipleRowDelete_Click(object sender, EventArgs e)

{

// Looping through all the rows in the GridView

foreach (GridViewRow row in GridView1.Rows)

{

CheckBox checkbox = (CheckBox)row.FindControl("cbRows");

//Check if the checkbox is checked.

//value in the HtmlInputCheckBox's Value property is set as the //value of the delete command's parameter.

if (checkbox.Checked)

{

// Retreive the Employee ID

int employeeID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

// Pass the value of the selected Employye ID to the Delete //command.

SqlDataSource1.DeleteParameters["EmployeeID"].DefaultValue = employeeID.ToString();

SqlDataSource1.Delete();

}

Run the code, and select a few rows in the grid. ‘Delete Rows’ button, the selected rows get deleted. Rather than deleting rows one at a time, deleting them in a batch is a good practice.

Tip 10: Export GridView To Excel

protected void Button1_Click(object sender, EventArgs e)

{

Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

Response.Charset = String.Empty;

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter sw = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.RenderControl(hw);

Response.Write(sw.ToString());

Response.End();

}

Well that was a quick overview of some of the most frequently used features of the GridView control. I hope you liked the article and I thank you for viewing it.

10 January 2011

Distinct operator in Linq

Linq operator provides great flexibility and easy way of coding. Let’s again take one more example of distinct operator. As name suggest it will find the distinct elements from IEnumerable. Let’s take an example of array in console application and then we will again print array to see is it working or not. Below is the code for that. In this application I have integer array which contains duplicate elements and then I will apply distinct operator to this and then I will print again result of distinct operators to actually see whether its working or not.

using System;
using  System.Collections.Generic;
using  System.Linq;
using System.Text;
namespace Experiment
{
   class Program
   {
       static void Main(string[] args)
       {
           int[] intArray = { 1, 2, 2,  3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 5, 5 };
           var uniqueIntegers = intArray.Distinct();
           foreach (var  uInteger in uniqueIntegers)
           {
               Console.WriteLine(uInteger);
           }
           Console.ReadKey();
       }
   }
}

Below is output as expected..

03 January 2011

Enum.HasFlag method in C# 4.0

Enums in dot net programming is a great facility and we all used it to increase code readability. In earlier version of .NET framework we don’t have any method anything that will check whether a value is assigned to it or not. In C# 4.0 we have new static method called HasFlag which will check that particular value is assigned or not. Let’s take an example for that. First I have created a enum called PaymentType which could have two values Credit Card or Debit Card. Just like following.

public enum PaymentType
{
    DebitCard=1,
    CreditCard=2
}

Now We are going to assigned one of the value to this enum instance and then with the help of HasFlag method we are going to check whether particular value is assigned to enum or not like following.

protected void Page_Load(object sender, EventArgs e)
{
    PaymentType paymentType = PaymentType.CreditCard;

    if (paymentType.HasFlag(PaymentType.DebitCard))
    {
        Response.Write("Process Debit Card");
    }
    if (paymentType.HasFlag(PaymentType.CreditCard))
    {
        Response.Write("Process Credit Card");
    }

}

Now Let’s check out in browser as following.

As expected it will print process Credit Card as we have assigned that value to enum.

ASP.NET Error Handling: Creating an extension method to send error email

Error handling in asp.net required to handle any kind of error occurred. We all are using that in one or another scenario. But some errors are there which will occur in some specific scenario in production environment in this case We can’t show our programming errors to the End user. So we are going to put a error page over there or whatever best suited as per our requirement. But as a programmer we should know that error so we can track the scenario and we can solve that error or can handle error. In this kind of situation an Error Email comes handy. Whenever any occurs in system it will going to send error in our email.
Here I am going to write a extension method which will send errors in email. From asp.net 3.5 or higher version of .NET framework its provides a unique way to extend your classes. Here you can fine more information about extension method. So lets create extension method via implementing a static class like following. I am going to use same code for sending email via my Gmail account from here. Following is code for that.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net.Mail;

namespace Experiement
{
    public static class MyExtension
    {
        public static void SendErrorEmail(this Exception ex)
        {
            MailMessage mailMessage = new MailMessage(new MailAddress("from@gmail.com")
                                       , new MailAddress("to@gmail.com"));
            mailMessage.Subject = "Exception Occured in your site";
            mailMessage.IsBodyHtml = true;

            System.Text.StringBuilder errorMessage = new System.Text.StringBuilder();

            errorMessage.AppendLine(string.Format("<B>{0}</B>:{1}","Exception",ex.Message));
            errorMessage.AppendLine(string.Format("<B>{0}</B>:{1}", "Stack Trace", ex.StackTrace));

            if (ex.InnerException != null)
            {
                errorMessage.AppendLine(string.Format("<B>{0}</B>:{1}", " Inner Exception", ex.InnerException.Message));
                errorMessage.AppendLine(string.Format("<B>{0}</B>:{1}", "Inner Stack Trace", ex.InnerException.StackTrace));
            }

            mailMessage.Body = errorMessage.ToString();

            System.Net.NetworkCredential networkCredentials = new
            System.Net.NetworkCredential("youraccount@gmail.com", "password");
            
            SmtpClient smtpClient = new SmtpClient();
            smtpClient.EnableSsl = true;
            smtpClient.UseDefaultCredentials = false;
            smtpClient.Credentials = networkCredentials;
            smtpClient.Host = "smtp.gmail.com";
            smtpClient.Port = 587;
            smtpClient.Send(mailMessage);

            
        }
    }
}

After creating an extension method let us that extension method to handle error like following in page load event of page.

using System;

namespace Experiement
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender,System.EventArgs e)
        {
            try
            {
                throw new Exception("My custom Exception");
            }
            catch (Exception ex)
            {
                ex.SendErrorEmail();
                Response.Write(ex.Message);
            }
        }

    }
}

Now in above code I have generated custom exception for example but in production It can be any Exception. And you can see I have use ex.SendErrorEmail() function in catch block to send email. That’s it.. Now it will throw exception and you will email in your email box like below.

02 December 2010

How to Display Line Numbers in Visual Studio

Show Line Numbers in Visual Studio

It is always better to have Line numbers displayed in Visual Studio when you debug your VB or C# Project. It is not turned on by default. Select tools form the tool bar and select options tab and then you can turn it on as shown below.