Asp Materials,WCF, Sql Server,Interview Question

Saturday 29 December 2012

Calculate total sum of columns in gridview footer using c# in Asp.net.

In this post I will explain Hoe to calculate total sum of columns in gridview footer using c# in Asp.Net.

Description:-

I have to show total sum of columns in griview footer. So take one gridview control on your page and show footer(Initially it’s visibily is false). Create one database table with a name EmpSalary like this.

ColumnName	DataType
EmpId	Int(primary key)
EmpName	Varchar(50)
DeptId	Int
Salary	Varchar(50)

Insert some Dummy data into the table.

Now create Design page like this.

Default.aspx:-

</head>

<body>

<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="true" PageSize="5"

BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"

CellPadding="3" CellSpacing="2" DataSourceID="SqlDataSource1"

ShowFooter="True" onrowdatabound="GridView1_RowDataBound">

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

<asp:BoundField DataField=" EmpName " HeaderText="EmpName"

SortExpression=" EmpName " />

<asp:TemplateField HeaderText="DeptId">

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

</ItemTemplate>

<asp:Label ID="lblTotalAmount" runat="server" Text="Total Amount"></asp:Label>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Salary">

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

</ItemTemplate>

<asp:Label ID="lblTotal" runat="server" ></asp:Label>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

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

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

SelectCommand="SELECT [EmpId], [EmpName], [DeptId], [Salary] FROM [EmpSalary]">

</asp:SqlDataSource>

</div>

</form>

</body>

</html>

After completion of design page write the code in codebehind.

Default.aspx.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

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

{

int total = 0;

protected void Page_Load(object sender, EventArgs e)

{

}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

{

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

{

total += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Salary"));

}

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

{

Label lbltotalAmount = (Label)e.Row.FindControl("lblTotal");

lbltotalAmount.Text = total.ToString();

}

Your ouput should be like this.

Tuesday 25 December 2012

Pagination in SQL Server 2008.

In this I will explain Pagination in sql server 2008.

Please follow the steps:-

Open sql server 2008 Management studio à create a new Database à create a table like this

Create table Details (Id int primary key , Name varchar(50), Country varchar(50));

Now insert the dummy data in the table.

Here I am inserting 1000 data one at a time. See the code given below ..

declare @count int=1;

declare @max int=5000;

delete from Details;

while(@count<=@max)

begin

insert into Details(Id,Name,Country)

select @count ,'Name'+CAST(@count as varchar(5)),'Country'+CAST(@count as varchar(5))

set @count=@count+1

end

This will insert 1000 data in the table.

Now create a stored procedure .

CREATE PROCEDURE PaginationIn2008

(

@start int=1,

@end int=500

)

BEGIN

select * from Details where Id between @start and @end

order by Id

END

Execute stored procedure.

To get data from 1 to 10 , execute the procedure like this

exec PaginationIn2008 1,10

If you want 100 to 200 , execute the procedure like this.

exec PaginationIn2008 100,200

Monday 24 December 2012

Hello message in MVC2.0 in .Net.

In this post I will explain how to print Hello message in MVC2.0 in .NET.

First create a new Asp.net MVC2.0 project in visual studio 2010.

Select the visual studio c#/web Template group on left , then choose the “ASP.NET MVC 2 Empty Web Application” , name the project and click ok button.

Go to solution explorer -> right click on controller folder -> select add -> click on Controller -> give a controller name i.e HomeController -> click Add button . This will create HomeController.aspx.cs page with the following code like this.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace MvcHello.Controllers

{

public class HomeController : Controller

{

// GET: /Home/

public ActionResult Index()

{

return View();

}

Let change the following two thing in the above code

· Change the method to return a string instead of ActionResult

· Change the return statement to return “Hello From Home”.

After changing the two thing the code will look like this:

public string Index()

{

return "Hello From Home";

}

Now it’s time to run the code. So press F5.

Sunday 23 December 2012

Large Image Preview on mouseover in Jquery in Asp.Net.

In this post I will explain Image Preview on mouseover in Jquery in Asp.Net.

Jquery Code:-

$(document).ready(function () {

ImagePreview();

});

function ImagePreview() {

xOffset = -25;

yOffset = 50;

$("a.preview").hover(function (e) {

this.t = this.title;

this.title = "";

var c = (this.t != "") ? "<br/>" + this.t : "";

$("body").append("<p id='preview'><img src='" + this.href + "' alt='Image Preview' />" + c + "</p>");

$("#preview")

.css("top", (e.pageY - xOffset) + "px")

.css("left", (e.pageX + yOffset) + "px")

.fadeIn("fast");

function () {

this.title = this.t;

$("#preview").remove();

});

$("a.preview").mousemove(function (e) {

$("#preview")

.css("top", (e.pageY - xOffset) + "px")

.css("left", (e.pageX + yOffset) + "px");

});

};

</script>

Design.aspx:-

$(document).ready(function () {

ImagePreview();

});

function ImagePreview() {

xOffset = -25;

yOffset = 50;

$("a.preview").hover(function (e) {

this.t = this.title;

this.title = "";

var c = (this.t != "") ? "<br/>" + this.t : "";

$("body").append("<p id='preview'><img src='" + this.href + "' alt='Image Preview' />" + c + "</p>");

$("#preview")

.css("top", (e.pageY - xOffset) + "px")

.css("left", (e.pageX + yOffset) + "px")

.fadeIn("fast");

function () {

this.title = this.t;

$("#preview").remove();

});

$("a.preview").mousemove(function (e) {

$("#preview")

.css("top", (e.pageY - xOffset) + "px")

.css("left", (e.pageX + yOffset) + "px");

});

};

</script>

#preview

{

position:absolute;

border:none;

background:gray;

padding:2px;

display:none;

color:Gray;

box-shadow:4px 4px 4px rgba(105,116,130,1);

}

</style>

</head>

<body>

<div>

<asp:DataList ID="DataList1" runat="server" RepeatColumns="4" CellPadding="5">

<asp:HyperLink ID="HyperLink1" runat="server" class="preview" ToolTip='<%#Bind("Name") %>' NavigateUrl='<%#Bind("Name","image/{0}") %>' >

<asp:Image ID="Image1" runat="server" ImageUrl='<%#Bind("Name","image/{0}") %>' /></asp:HyperLink>

</ItemTemplate>

</asp:DataList>

</div>

</form>

</body>

</html>

Design.aspx.cs:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Collections;

using System.IO;

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindDataList();

}

public void BindDataList()

{

DirectoryInfo dirInfo = new DirectoryInfo(MapPath("image"));

FileInfo[] file = dirInfo.GetFiles();

ArrayList arrList = new ArrayList();

foreach (FileInfo info in file)

{

arrList.Add(info);

}

DataList1.DataSource = arrList;

DataList1.DataBind();

}

Saturday 22 December 2012

Join in Sql server.

In this post I will explain join in Sql Server.

Introduction:-

This is very common question asked in interview. Join is used to retrieve the data from two tables. For retrieving the data from two tables it is necessary that the two tables must have some relation in between them. If tables have relation then it must have primary and foreign key. If there is no relation between two tables then it cannot be retrieve the data from the tables.

First create two tables in database like as:-

First Table name:- EmpDetail

Key: EmpId is primary key

EmpId	EmpName	Country
1	Sushil kumar	India
2	Pradeep kumar	U.K
3	Sushil Modi	India

Second Table Name:- OrderDetails

Key:- OrderId is primary key and EmpId is foreign key

OrderId	OrderName	EmpId
1	PC	1
2	Laptop	2
3	Mobile	3
4	HDD	1

There are three types of join.

· Inner Join

· Outer Join

· Self Join

Inner Join:-

Inner Join display only those rows which matched in both the tables. Inner join is also called as default join in sql query .

Syntax For Inner Join:-

Select tb1.Column_name1 , tb1.Column_name2 , tb2.Column_name1

From Table1 tb1 Inner Join Table2 tb2 on tb1.Column_name=tb2.Column_name

Example:-

select tb1.EmpId,tb1.EmpName,tb2.OrderName

from EmpDetails tb1

inner join OrderDetails tb2

on tb1.EmpId=tb2.EmpId

When you execute this code output like this

EmpId	EmpName	OrderName
1	sushil kumar	PC
2	Pradeep Kumar	Laptop
3	Sushil Modi	Mobile
1	sushil kumar	HDD

Types Of Inner Join:-

· Equi Join

· Natural Join

· Cross Join

Equi Join:-

Equi Join is used to display all matched records from joined tables and also display redundant values. Equi join use * sign to join table.

Syntax for Equi Join:-

select *

from Table1 tb1

inner join Table2 tb2

on tb1. Table1 =tb2. Table2

Example:-

select *

from EmpDetails tb1

inner join OrderDetails tb2

on tb1.EmpId=tb2.EmpId

when you execute this query the output like this

EmpId	EmpName	Country	OrderId	OrderName	EmpId
1	sushil kumar	India	1	PC	1
2	Pradeep Kumar	U.K	2	Laptop	2
3	Sushil Modi	India	3	Mobile	3
1	sushil kumar	India	4	HDD	1

Natural Join:-

Natural join is same as Equi join but only difference is that it restricts to display redundant values.

Syntax For Natural Join:-

select *

from Table1 tb1

Natural Table2 tb2

on tb1. Table1 =tb2. Table2

This syntax will give error because it will not work in Sql Server . It will work in Oracle.

Cross Join:-

It gives the cartesian product of two join tables and the size of cartesian product is equal to the multiplication of total no of row of first table and total no of row of second table.

Syntax For Cross Join:-

select *

from Table1

Cross join Table2

Example:-

select *

from EmpDetails

Cross join OrderDetails

When You will execute this query the output will be like this..

EmpId	EmpName	Country	OrderId	OrdernName	EmpId
1	sushil kumar	India	1	PC	1
1	sushil kumar	India	2	Laptop	2
1	sushil kumar	India	3	Mobile	3
1	sushil kumar	India	4	HDD	1
2	Pradeep Kumar	U.K	1	PC	1
2	Pradeep Kumar	U.K	2	Laptop	2
2	Pradeep Kumar	U.K	3	Mobile	3
2	Pradeep Kumar	U.K	4	HDD	1
3	Sushil Modi	India	1	PC	1
3	Sushil Modi	India	2	Laptop	2
3	Sushil Modi	India	3	Mobile	3
3	Sushil Modi	India	4	HDD	1

OuterJoin:-

OuterJoin returns all the row which are matched and unmatched from the joined table .

There are three types of OuterJoin:-

· Left Outer Join

· Right Outer join

· Full Outer Join

Left Outer Join:-

Left Outer Join display all the row form first table and matched row from the scond table.

Syntax for Left Outer Join:-

select Column_list from Table1 tb1

LEFT OUTER JOIN Table2 tb2

on tb1. Column_name =tb2. Column_name

Example:-

select tb1.EmpName, tb1.Country from EmpDetails tb1

LEFT OUTER JOIN OrderDetails tb2

on tb1. EmpId =tb2. EmpId

After executing the query output will be like this.

EmpName	Country	OrderName
sushil kumar	India	PC
sushil kumar	India	HDD
Pradeep Kumar	U.K	Laptop
Sushil Modi	India	Mobile

Right Outer Join:-

Right Outer Join Display all the row from right table and matched row from left table.

Syntax for Left Outer Join:-

select Column_list from Table1 tb1

RIGHT OUTER JOIN Table2 tb2

on tb1. Column_name =tb2. Column_name

Example:-

select tb1.EmpName, tb1.Country from EmpDetails tb1

RIGHT OUTER JOIN OrderDetails tb2

on tb1. EmpId =tb2. EmpId

After executing the query output will be like this.

EmpId	EmpName	Country	OrderName
1	sushil kumar	India	PC
2	Pradeep Kumar	U.K	Laptop
3	Sushil Modi	India	Mobile
1	sushil kumar	India	HDD

Full Outer Join:-

Full Outer join deisply all the matching and non-matching records from join table.

Synatx for Full Outer Join:-

select Column_list from Table1 tb1

RIGHT OUTER JOIN Table2 tb2

on tb1. Column_name =tb2. Column_name

Example:-

select tb1.EmpName, tb1.Country from EmpDetails tb1

FULL OUTER JOIN OrderDetails tb2

on tb1. EmpId =tb2. EmpId

After executing the query output will be like this.

EmpId	EmpName	Country	OrederName
1	sushil kumar	India	PC
1	sushil kumar	U.K	HDD
2	Pradeep Kumar	India	Laptop
3	Sushil Modi	India	Mobile

Self Join:-

Joining the table itself is called as Self Join. Self join ise used if the table have a relation with other record in the same table.

First Make Table EmpDetails in which the fields name are EmpId,EmpName and ManagerId like this.

EmpId	EmpName	ManagerId
1	Sushil Kumar	2
2	Pradeep Kumar	4
3	Ram	7
4	Krishan	1
6	Shyam	3
7	Mohan	2

Example:-

select tb2.EmpName,tb1.EmpName as 'Manager'

from EmpDetails tb1

inner join EmpDetails tb2

on tb1.EmpId=tb2.ManagerId

After executing the query output will be like this.

EmpName	ManagerId
Krishan	Sushil Kumar
Sushil Kumar	Pradeep Kumar
Mohan	Pradeep Kumar
Shyam	Ram
Pradeep Kumar	Krishan
Ram	Mohan

ASP Materials

Pages

Labels

Saturday 29 December 2012

Calculate total sum of columns in gridview footer using c# in Asp.net.

Tuesday 25 December 2012

Pagination in SQL Server 2008.

Monday 24 December 2012

Hello message in MVC2.0 in .Net.

Sunday 23 December 2012

Large Image Preview on mouseover in Jquery in Asp.Net.

Saturday 22 December 2012

Join in Sql server.