Dot Net Example: November 2017

In this demo, using the Hyperlink in gridview and clicking on Hyperlink displaying details of employees in department.

Note:-

Code Highlighted with yellow color, used for create the Hyperlink in gridview. using gridview RowDataBound event.

Step 1:- Create Department Table

CREATE TABLE tblDepartment

(

DepartmentId INT IDENTITY(1, 1) PRIMARY KEY,

DeptName VARCHAR(10)

)

Step 2:- Insert data into tblDepartment table

Insert into tblDepartment values ('IT')

Insert into tblDepartment values ('Payroll')

Insert into tblDepartment values ('HR')

Insert into tblDepartment values ('Admin')

Step 3:- Select tblDepartment table

SELECT * FROM tblDepartment

Step 4:- Create Employee Table

CREATE TABLE tblEmployee

(

EmployeeId INT IDENTITY(1, 1) PRIMARY KEY,

Name VARCHAR(10),

Gender VARCHAR(10),

City VARCHAR(10),

DateOfBirth DATETIME,

DepartmentId INT

)

Step 5:- Insert data into tblEmployees table

Insert into tblEmployee values ('Ram','Male','Mumbai','1979-01-05', 3)

Insert into tblEmployee values ('Shyam','Male','Delhi','1981-03-07', 2)

Insert into tblEmployee values ('Sita','Female','Mumbai','1982-02-03', 1)

Insert into tblEmployee values ('Bharat','Male','Kolkata','1972-04-06', 4)

Insert into tblEmployee values ('Divya','Female','Mumbai','1973-04-06', 1)

Insert into tblEmployee values ('Laxchaman','Male','Chennai','1974-04-06', 3)

Insert into tblEmployee values ('Noah','Male','London','1989-01-05', 3)

Insert into tblEmployee values ('Mason','Male','UK','1984-03-07', 2)

Insert into tblEmployee values ('Liam','Female','USA','1986-02-03', 1)

Insert into tblEmployee values ('Jacob','Male','Australia','1962-04-06', 4)

Insert into tblEmployee values ('Alexander','Male','Bangkok','1993-04-06', 1)

Insert into tblEmployee values ('William','Male','USA','1964-04-06', 3)

Step 6:- Select tblEmployees table

SELECT * FROM tblEmployee

Step 7:- Create spGetDepartment store procedure

CREATE PROC spGetDepartment

BEGIN

SELECT a.DepartmentId,

a.DeptName'Department Name',

COUNT(b.Name) 'Total Employee'

FROM tblDepartment a

INNER JOIN tblEmployee b

ON a.DepartmentId = b.DepartmentId

GROUP BY

a.DepartmentId,

a.DeptName

END

Step 8:- Create spGetEmployees store procedure

CREATE PROC spGetEmployees

@DepartmentId INT

BEGIN

SELECT NAME'Employee Name',

Gender,

City,

FORMAT(DateOfBirth, 'dd/MM/yyyy')'DateOfBirth'

FROM tblEmployee

WHERE DepartmentId = @DepartmentId

END

Step 9:- Open Visual Studio 2010 => Goto File Menu => New => Project...

Step 10:- In the Installed Templates list, select Visual C# => Web

Step 11:- Select ASP.Net Web Application from the Web list => Type HyperlinkInGridDemo in the Name box and click OK

Step 12:- Right-click HyperlinkInGridDemo in Solution Explorer => Add => Click New Items...

Step 13:- Select Web Form from the Web list = > Type DepartmentList.aspx in the Name box and click OK

Follow Step 12 and 13 again to add EmployeesList.aspx

Step 14:- Copy Past following code in DepartmentList.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DepartmentList.aspx.cs" Inherits="HyperLinkInGridDemo.DepartmentList" %>

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

<title>Department List</title>

</head>

<body>

<div>

<h2>Department List</h2>

</div>

<div>

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="Both"

AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">

<asp:BoundField DataField="DepartmentId" HeaderText="DepartmentId" />

<asp:BoundField DataField="Department Name" HeaderText="Department Name" />

<asp:BoundField DataField="Total Employee" HeaderText="Total Employee" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

Step 15:- Copy Past following code in DepartmentList.aspx.cs

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

namespace HyperLinkInGridDemo

{

public partial class DepartmentList : System.Web.UI.Page

{

SqlConnection con = null;

SqlDataAdapter da = null;

SqlCommand cmd = null;

DataSet ds = null;

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

GetDepartment();

}

protected void GetDepartment()

{

string strConnection = ConfigurationManager.ConnectionStrings["strConnection"].ToString();

using (con = new SqlConnection(strConnection))

{

using (cmd = new SqlCommand())

{

cmd.CommandText = "spGetDepartment";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

using (da = new SqlDataAdapter(cmd))

{

using (ds = new DataSet())

{

da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)

{

GridView1.DataSource = ds;

GridView1.DataBind();

}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

if (e.Row.Cells[2].Text != "0" && e.Row.Cells[2].Text != " ")

{

e.Row.Cells[2].Text = "<a href=\"#\" onclick=\"javascript:var w=window.open('EmployeesList.aspx?DepartmentId=" + e.Row.Cells[0].Text + "&DeptName=" + e.Row.Cells[1].Text + "&footer=row','_blank','scrollbars=yes,menubar=no,height=400,width=400,resizable=yes,toolbar=no,location=no,status=no')\"><U><B>" + e.Row.Cells[2].Text + "</U></B></a>";

}

Step 16:- Copy Past following code in EmployeesList.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeesList.aspx.cs" Inherits="HyperLinkInGridDemo.EmployeesList" %>

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

<title>Employees List</title>

</head>

<body>

<h2>Employees List</h2>

<h2>Department :

<asp:Label ID="Label2" runat="server" Text=""></asp:Label>

</h2>

<div>

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">

</asp:GridView>

</div>

</form>

</body>

</html>

Step 17:- Copy Past following code in EmployeesList.aspx.cs

using System;

using System.Web.UI;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

namespace HyperLinkInGridDemo

{

public partial class EmployeesList : System.Web.UI.Page

{

SqlConnection con = null;

SqlDataAdapter da = null;

SqlCommand cmd = null;

DataSet ds = null;

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

if (!string.IsNullOrEmpty(Request.QueryString["DepartmentId"]))

{

GetEmployees(Convert.ToInt32(Request.QueryString["DepartmentId"]));

Label1.Text = Convert.ToString(Request.QueryString["DeptName"]);

}

protected void GetEmployees(int DepartmentId)

{

string strConnection = ConfigurationManager.ConnectionStrings["strConnection"].ToString();

using (con = new SqlConnection(strConnection))

{

using (cmd = new SqlCommand())

{

cmd.CommandText = "spGetEmployees";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.AddWithValue("DepartmentId", DepartmentId);

using (da = new SqlDataAdapter(cmd))

{

using (ds = new DataSet())

{

da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)

{

GridView1.DataSource = ds;

GridView1.DataBind();

}

Step 18:- Copy Past following code in Web.config under <configuration> section

</connectionStrings>

Step 19:- Right-click DepartmentList.aspx in Solution Explorer => Click Set As Start Page

Now Application is ready to run. Press Ctrl + F5 or From Debug Menu Click Start Debugging to run application.

Dot Net Example

Tuesday, 28 November 2017

How to add Hyperlink in gridview in ASP.NET