Month: February 2012

How to fetch data on basis of multiple Id in LINQ or EF ?

Chandradev1 Comment

Hi
Recently one of my project, there was requirement to filter from one table on basis of multiple Id. There was using EF. So in EF we can write the Syntax like this

using (DemoJobPortalModel.DemoJobPortalEntities objE = new DemoJobPortalModel.DemoJobPortalEntities())
{
List<int> ListCountryId = new List<int> { 3, 5, 7 };

var query = from m in objE.tblCountries
where ListCountryId.Contains(m.Id)
select m;
GridView1.DataSource = query;
GridView1.DataBind();
}

In Sql Server we can do like this

Select *from tblCountry where Id in (3,5,7)

How to do Custom paging in Gridview using SP ?

Chandradev2 Comments


We can do custom paging using store Procedure in gridview like this

Step1: Write the Sp like this

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[usp_GetEmpName] Script Date: 01/29/2012 18:18:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetEmpName]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex – 1) * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = Id FROM tblEmp ORDER BY Id

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT ID, EmpName,EmpSal FROM tblEmp WHERE
ID >= @first_id
ORDER BY ID

SET ROWCOUNT 0
— GEt the total rows

SELECT @totalRows = COUNT(ID) FROM tblEmp

Step2: Design the default page like this

<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button2" runat="server" Text="Click Here"
onclick="Button2_Click" />
<br />
<br />
<asp:GridView ID="Gridview1" runat="server">
</asp:GridView>
<asp:Button ID="Btn_Previous" CommandName="Previous"
runat="server" OnCommand="ChangePage"
Text="Previous" />
<asp:Button ID="Btn_Next" runat="server" CommandName="Next"
OnCommand="ChangePage" Text="Next" />
<br />
Pages <asp:Label ID="lblCurrentPage" runat="server" Text=""></asp:Label> Of
<asp:Label ID="lblTotalPages" runat="server" Text=""></asp:Label>

<br />
<br />

</div>
</form>
</body>
</html>

Step3: Write the C# code in DAL like this

public class EmpDAL
{
string connectionString = "Data Source=.\\sqlExpress;Initial Catalog=Test;Integrated Security=True";
public DataTable GetAllEmpName(int CurrPage,int PageSize,out int rowno)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("usp_GetEmpName", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startRowIndex", CurrPage);
cmd.Parameters.AddWithValue("@maximumRows", PageSize);
cmd.Parameters.Add("@totalRows", SqlDbType.Int, 4);
cmd.Parameters["@totalRows"].Direction = ParameterDirection.Output;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
rowno = (int)cmd.Parameters["@totalRows"].Value;
return dt;
}
}
}

}

Step4: Write the C# code in Default page like this

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;

public partial class FinalEmpCustomPaging : System.Web.UI.Page
{
protected int currentPageNumber = 1;
private const int PAGE_SIZE = 10;
protected void Page_Load(object sender, EventArgs e)
{
NotVisible();

}

private void NotVisible()
{
Btn_Next.Visible = false;
Btn_Previous.Visible = false;
lblCurrentPage.Visible = false;
lblTotalPages.Visible = false;
}
private void Visible1()
{
Btn_Next.Visible = true;
Btn_Previous.Visible = true;
lblCurrentPage.Visible = true;
lblTotalPages.Visible = true;
}
private void BindData()
{
int rowno1;
EmpDAL objDAL = new EmpDAL();
Gridview1.DataSource = objDAL.GetAllEmpName(currentPageNumber, PAGE_SIZE,out rowno1);
Gridview1.DataBind();
double totalRows = rowno1;
lblTotalPages.Text = CalculateTotalPages(totalRows).ToString();
lblCurrentPage.Text = currentPageNumber.ToString();

if (currentPageNumber == 1)
{
Btn_Previous.Enabled = false;

if (Int32.Parse(lblTotalPages.Text) > 0)
{
Btn_Next.Enabled = true;
}
else
Btn_Next.Enabled = false;
}

else
{
Btn_Previous.Enabled = true;

if (currentPageNumber == Int32.Parse(lblTotalPages.Text))
Btn_Next.Enabled = false;
else Btn_Next.Enabled = true;
}
}
private int CalculateTotalPages(double totalRows)
{
int totalPages = (int)Math.Ceiling(totalRows / PAGE_SIZE);

return totalPages;
}
protected void ChangePage(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case “Previous”:
currentPageNumber = Int32.Parse(lblCurrentPage.Text) – 1;
break;

case “Next”:
currentPageNumber = Int32.Parse(lblCurrentPage.Text) + 1;
break;
}

BindData();
Visible1();
}

protected void Button2_Click(object sender, EventArgs e)
{
Visible1();
BindData();
}
}