Gridview in asp.net

by
Gridview in asp.net

This asp.net tutorial explains, gridview in asp.net. How to bind gridview using data reader in asp.net. How to add Eval for a hyperlink in gridview in asp.net? And, how to apply color in gridview rows based dynamically.

Then we will discuss how to export gridview data to excel sheet in Asp.net? And, how to export gridview to word in asp.net c#.net. We will also discuss how to display a confirmation message in gridview for delete in Asp.net. We will also see, how to send or pass girdview row value to the next page using a hyperlink in asp.net.

Gridview in asp.net

Gridview is a control in asp.net which displays data in a tabular format. Gridview control displays the values of a data source in a table where each column represents a field and each row represents a record in asp.net. The asp.net GridView control enables you to select, sort, and edit these items.

There are different ways we can bind data to gridview in asp.net.

Bind gridview using datareader in asp.net

Now, we will see how to bind gridview using data reader in asp.net.

Below is the full code to bind gridview using a data reader in Asp.Net. Here we have used a stored procedure to retrieve data from the database:

Stored Procedure:

CREATE PROCEDURE Select_Employees

AS
BEGIN
Select * from Employees
END
GO

HTML Code:

<asp:GridView ID="GridView1" runat="server" >
</asp:GridView>

.cs code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
string conn = "Data Source=localhost;database=sample;Integrated Security=true";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindGridview();
}
}

private void bindGridview()
{
SqlConnection con = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand("Select_Employees", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
reader.Close();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
}

But we can not implement paging in gridview with data reader in asp.net.

Bind GridView Using Dataset in Asp.Net

Now, we will see how to bind gridview using dataset in asp.net.

<asp:GridView ID="GridView1" runat="server" >
</asp:GridView>

Below is the C#.Net code to bind gridview using dataset in asp.net.

SqlConnection con = new SqlConnection(@"Data Source=localhost;database=sample;Integrated Security=true");  
SqlCommand cmd = new SqlCommand("select * from Employees", con);  
SqlDataAdapter da = new SqlDataAdapter(cmd);  
DataSet ds = new DataSet();  
da.Fill(ds);  
GridView1.DataSource = ds;  
GridView1.DataBind();

How to add Eval for hyperlink in gridview in asp.net?

Now, we will see how to add Eval property into hyperlink field in gridview in Asp.Net.

In this scenario, we will see how a user can navigate to a different page with a query string parameter. In the below code it will see a list of usernames which are binding from the database. Then whenever a user clicks on a particular username, then it will navigate to a UserDetails page with taking UserID as a query string parameter.

<asp:TemplateField HeaderText="Name">

<ItemTemplate>
<asp:HyperLink ID="hylMachineNum" runat="server" NavigateUrl='<%# "~/UserDetails.aspx?UserID=" + Eval("UserID")%>' Text='<%#Eval("UserName")%>'>
</asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>

Here the Text value is coming from the Eval property by the following syntax.

Text='<%#Eval("UserName")%>'

And the NavigateURL property is coming from the below property:

NavigateUrl='<%# "~/UserDetails.aspx?UserID=" + Eval("UserID")%>'

Apply color in gridview rows based dynamically

Now, we will see how to fill colors in gridview based on some conditions and values in asp.net.

Say for an example: if a gridview has different departments in different locations. Need to color the gridview values based on the locations and display the top scorers of the winner’s department wise.

Add OnRowDataBound EVENT for the gridview and the find

protected void GvLeaderBorad_OnRowDataBound(object sender, GridViewRowEventArgs e)
        {
if (e.Row.RowType == DataControlRowType.DataRow)
            {
  Label lblNett = e.Row.FindControl("lbl_Net") as Label;
                if (lblNett.Text == Locations.Chennai.ToString())
                {
                    e.Row.Cells[0].BackColor = System.Drawing.Color.DarkGreen;
                }
}
}

When the color code is fetched from label placed inside the row . It will be applied to the entire row. Thus in code itself when we bind the grid. Provide row color in the label and fetch the color code from it.

Apply the color code(Html hex code ex: #fffff ) to the gridview row.

Export gridview data to excel sheet in Asp.net

Now, we will see how to export gridview data to excel sheet in asp.net.

Below is the full code to export gridview data to excel sheet in Asp.net.

protected void btnExport_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = GetDataForGridView();
string attachment = attachment = "attachment; filename=MyExcelSheetName_" + DateTime.Now.ToString() + ".xls";
if (ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
//This will give you the number of columns present in gridview
for (int coulumns = 0; coulumns < GridView1.Columns.Count; coulumns++)
{
Response.Write(tab + GridView1.Columns[coulumns].HeaderText);
tab = "\t";
}
Response.Write("\n");

//Here we will visit each row of datatable and bind the corresponding column data.
foreach (DataRow dr in dt.Rows)
{
tab = "";
Response.Write(tab + dr["ColumnName1"].ToString());
tab = "\t";
Response.Write(tab + dr["ColumnName2"].ToString());
tab = "\t";
Response.Write(tab + dr["ColumnName3"].ToString());
tab = "\t";
Response.Write("\n");
}
Response.End();
}
}

Export gridview to word in asp.net c#

Now, we will see how we can download or export gridview data to word document in asp.net using c#.net. Here I have developed an asp.net website where we have used a gridview to bind the data from SQL server table and then we have a button, on the button click we are exporting the data to word format.

Below is the .aspx code.

<asp:GridView ID="GridView1" runat="server"></asp:GridView><br />
<asp:Button ID="btnDownload" runat="server" Text="Download" OnClick="btnDownload_Click" />

.aspx.cs code:

Below is the .cs code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace EnjoySharePointBackup
{
public partial class _Default : Page
{

private SqlConnection con;
private SqlCommand com;
private string constr, query;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bindgrid();

}
}
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
private void connection()
{
string constr = "Data Source=YourDataSource;database=EnjoySharePoint;User ID=User1;Password=**********;";
con = new SqlConnection(constr);
con.Open();

}
protected void btnDownload_Click(object sender, EventArgs e)
{
ExportGridToword();
}
private void Bindgrid()
{
connection();
query = "select * from jobmaster where JobID"+ TextBox1.Text;
com = new SqlCommand(query, con);
SqlDataReader dr = com.ExecuteReader();

GridView1.DataSource = dr;
GridView1.DataBind();
con.Close();

}

private void ExportGridToword()
{
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
string FileName = "EnjoySharePoint" + DateTime.Now + ".doc";
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/msword";
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
GridView1.GridLines = GridLines.Both;
GridView1.HeaderStyle.Font.Bold = true;
GridView1.RenderControl(htmltextwrtter);
Response.Write(strwritter.ToString());
Response.End();
}
}
}

This way we can export gridview data to word in asp.net using c#.net.

Show confirmation message in gridview for delete in Asp.net

Now, we will see how to show a confirmation message while deleting a record from the gridview in asp.net. Like it should first as us whether you want to delete the record and then if the user clicks on yes then it should delete the record.

There are different ways to show confirmation message in gridview for delete in Asp.net.

1st Approach:

You can directly write in the OnClientClick event of the LinkButton like below:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%#Eval("UserID") %>'
CommandName="Delete" OnClientClick="return confirm(‘Are you sure you want to delete this record?');">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

2nd Approach:

You can write a javascript function and call that function in the OnClientClick event like below:

<head runat="server">
<title>Show confirmation message in gridview for delete in Asp.net</title>
<script type="text/javascript">
function DeleteRecord() {
return confirm("Are you sure you want to delete this record?");
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%#Eval("UserID") %>'
CommandName="Delete" OnClientClick="return DeleteRecord();">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>

This way we can display confirmation message in gridview for delete in Asp.net.

Send GridView row data to the next page using Hyperlink in Asp.Net

Now, we will see how to send or pass girdview row value to the next page using a hyperlink in asp.net.

Follow the below steps:

Add hyperlink column in the gridview control. And set the data fields to navigate to the next page in the DataNavigateUrlFields property of the hyperlink field.

Then Pass Query String value in DataNavigateUrlFormatString property of the hyperlink field.

Retrieve data in the 2nd page using Request.QueryString.

Source page of Default.aspx:

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

<Columns>
<asp:HyperLinkField DataNavigateUrlFields="empid,empname,empadd" DataNavigateUrlFormatString="Default2.aspx?empid={0}&empname={1}&empadd={2}" Text= “sendData" />
<asp:BoundField DataField ="empid" HeaderText="empid" SortExpression="empid" />
<asp:BoundField DataField="empname" HeaderText="empname" SortExpression="empname" />
<asp:BoundField DataField="empadd" HeaderText="empadd" SortExpression="empadd" />
</Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:abithaConnectionString %>"
SelectCommand="SELECT * FROM [emp]"></asp:SqlDataSource>

Source page of Default2.aspx:

EmployeeID:
<asp:Label ID="lblEmpId" runat="server"></asp:Label>

EmployeeName:
<asp:Label ID="lblEmpName" runat="server"></asp:Label>

EmployeeAddress:
<asp:Label ID="lblEmpAddress" runat="server"></asp:Label>

Code behind page of Default2.aspx:

Protected void Page_Load( sender As Object, e As System.EventArgs)
{
String strid ;
String strname;
String stradd;

strid = Request.QueryString(“empid");
strname = Request.QueryString(“empname");
stradd = Request.QueryString(“empadd");

lblEmpId.Text = strid;
lblEmpName.Text = strname;
lblEmpAddress.Text = stradd;
}

This way we can send girdview row value to the next page using a hyperlink in asp.net.

Gridview in asp.net
Gridview in asp.net

Implement delete functionality in Gridview in Asp.Net

This gridview example we will discuss how to implement the delete mechanism in grid view in asp.net.

if you are showing some users and want to delete one user when someone clicks on the Delete link button inside the gridview.

Below is the full code:

Gridview code:

<div>
<asp:GridView ID="GridView1″ runat="server" AutoGenerateColumns="false" OnRowCommand="GridView1_RowCommand" OnRowDeleting="GridView1_RowDeleting">
<Columns>
<asp:TemplateField HeaderText="User Name">
<ItemTemplate>
<asp:HyperLink ID="hypUserName" runat="server" Text='<%#Eval("UserName") %>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email ID">
<ItemTemplate>
<asp:Label ID="lblEmailID" runat="server" Text='<%#Eval("EmailID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1″ runat="server" CommandArgument='<%#Eval("UserID") %>'
CommandName="Delete" OnClientClick="return confirm(‘Are you sure you want to delete this user?');">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>

Here we need to handle the OnRowDeleting and OnRowCommand command event.

In the Delete button, we have set the CommandArgument to the UserID which is the ID through which we will write the code to delete the record.

We have also set the CommandName as “Delete” which you can set anything but you need to check in the OnRowCOmmand event.

It will also ask you a confirmation message before deleting the record.

.CS Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
int UserID =Convert.ToInt32( e.CommandArgument);
if (e.CommandName == "Delete")
{
//Write your code to delete the user based on the above UserID
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
//Here no need to write any logic inside.
}

void BindGridView()
{
//Here is the code to Bind the gridview
}
}

Hope you will able to delete the record in the gridview in asp.net.

How to display serial number automatically in GridView in asp.net?

Now, we will see how to display a serial number column in grid view in asp.net.

The serial number will come as an index, it will start from 1 and will go on increasing 1 value for each row.

Here the code to bind a gridview will be the same. The only thing will change is we need to another <ItemTemplate> like below:

<asp:GridView ID="GridView1″ runat="server">
<Columns>
<asp:TemplateField HeaderText="Serial Number">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

If you will write this code then the serial number column will come in Gridview.

You may like the following asp.net tutorials:

Hope this asp.net tutorial explains Gridview in asp.net. Also, we learn how to apply color in gridview rows based dynamically, how to add Eval for a hyperlink in gridview in asp.net? Also, we saw how to Bind gridview using datareader in asp.net.

Then we discussed, how to export gridview data to excel sheet in Asp.net and how to export gridview to word in asp.net c#.net.