Make Store Procedure for Paging and make Project for Paging in Asp.net and explain Cursors
This Article explain the how to make Store Procedure and its use in Asp.net program
#Cursors (Database)
-
Private area to sql (where results of
query are stored)
# Types of cursors
- Static Cursor
- Key set Cursor
- Dynamic Cursor
*Static Cursor
By default cursor type is static
-
Just like a snapshot
-
Insertion, updation, deletion are not visible
-
Better performance but double memory
-
Data is stored in it
*Keyset Cursor
Keys made= no. of records in table
-
It have a rule that primary key should
be in table
-
In it, data is not stored but keys are
made
-
Updation and deletion is visible but not
insertion
-
Double memory is not occupied
*Dynamic Cursor
- insertion, updation , deletion is visible
# Steps to make cursor
Declare the cursor
Open the cursor
Fetch the cursor
Close the cursor
De-allocate the cursor
#Scrollable cursor
Scroll cursor
Forward cursor
# Sored Procedure for paging
--5= to pass comment
|
CREATE PROCEDURE paging
@pagenumber
int,--5
@pagesize
int--5
AS
declare @srec int--21
declare @erec int--25
declare @sbid int
declare @ebid int
declare @rc int
set @srec=@pagenumber*@pagesize-@pagesize+1
declare c_book scroll cursor for select BookId from tbbook1 order by BookId
open c_book
Fetch absolute @srec from c_book into @sbid
select @rc= count (*) from
tbbook1 where BookId>@sbid
if @rc<@pagesize
set @erec=@srec+@rc
else
set @erec=@pagenumber*@pagesize
fetch absolute @erec from c_book into @ebid
close c_book
deallocate c_book
select count(*) from tbbook1
select * from tbbook1 where
BookId>=@sbid and BookId<=@ebid
|
@srec-
position
#Cursors (Database)
Add new WebForm3
*Add Datalist
Nor: dropdownlist > task list > edit items
> add > text>2,4,6,8,10 and enable autopost back
* . Add 3 labels and then 4 buttons (first,previous,next,last)
NOR – number of record/page
*By default SqlDataReader will return first query in stored procedure
* if
(dr.NextResult()) it is used for next query
#Coding
|
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;
namespace WebApplication10
{
public partial class WebForm3 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ps"].ConnectionString;
if(con.State==ConnectionState.Closed)
{
con.Open();
}
if(Page.IsPostBack==false)
{
Page_Bind(1);
}
}
private void Page_Bind(Int32 pno)
{
Int32 nor, repcol;
nor = Convert.ToInt32(DropDownList1.SelectedValue);
if(nor<=4)
{
repcol = nor;
}
else
{
repcol = nor / 2;
}
DataList1.RepeatColumns = repcol;
SqlCommand cmd = new SqlCommand("paging", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pagenumber", SqlDbType.Int).Value = pno;
cmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = nor;
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
Int32 totrec = Convert.ToInt32(dr[0]);
Label1.Text = pno.ToString();
Label2.Text = "of";
Int32 totpage = totrec / nor;
if(totrec %nor!=0)
{
totpage += 1;
}
Label3.Text = totpage.ToString();
if (dr.NextResult())
{
DataList1.DataSource = dr;
DataList1.DataBind();
}
dr.Close();
cmd.Dispose();
//*****
Button1.Enabled = true;
Button2.Enabled = true;
Button3.Enabled = true;
Button4.Enabled = true;
if(pno==1)
{
Button1.Enabled = false;
Button2.Enabled = false;
}
if(pno==totpage)
{
Button3.Enabled = false;
Button4.Enabled = false;
}
}
protected void
DropDownList1_SelectedIndexChanged(object sender,
EventArgs e)
{
Page_Bind(1);
}
protected void Button1_Click(object sender, EventArgs e)
{
Page_Bind(1);
}
protected void Button4_Click(object sender, EventArgs e)
{
Page_Bind(Convert.ToInt32(Label3.Text));
}
protected void Button3_Click(object sender, EventArgs e)
{
Page_Bind(Convert.ToInt32(Label1.Text) + 1);
}
protected void Button2_Click(object sender, EventArgs e)
{
Page_Bind(Convert.ToInt32(Label1.Text) - 1);
}
}
}
|
#Html
|
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="WebApplication10.WebForm3" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server">
<ItemTemplate>
<b> Name : </b><i><%#Eval("BookTitle")%></i><br />
<b> Author : </b><%#Eval("BookAuthor")%><br />
<b> Publisher : </b><%#Eval("BookPublisher")%><br />
<b> Price : </b><%#Eval("BookPrice")%><br />
</ItemTemplate>
</asp:DataList>
<br />
<br />
NOR :
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
<br />
<br />
<br />
<asp:Button ID="Button1" runat="server" style="font-size: medium" Text="First" OnClick="Button1_Click" />
<asp:Button ID="Button2" runat="server" style="font-size: medium" Text="Previous" OnClick="Button2_Click" />
<asp:Button ID="Button3" runat="server" style="font-size: medium" Text="Next" OnClick="Button3_Click" />
<asp:Button ID="Button4" runat="server" style="font-size: medium" Text="Last" OnClick="Button4_Click" />
</div>
</form>
</body>
</html>
|
# For 1,2,3,4
using System.Collections;
|
private void Page_Bind(Int32 pno)
if(pno==totpage)
{
Button3.Enabled = false;
Button4.Enabled = false;
}
//Array for 1,2,3,4,5
ArrayList ar = new ArrayList();
for (Int32 i = 1; i <=totpage;i++)
{
ar.Add(i);
}
DataList2.RepeatDirection = RepeatDirection.Horizontal;
DataList2.DataSource = ar;
DataList2.DataBind();
}
|
|
protected void DataList2_SelectedIndexChanged(object sender, EventArgs e)
{
Page_Bind(Convert.ToInt32(DataList2.SelectedIndex) + 1);
}
|
#Html Source
|
<asp:DataList ID="DataList2" runat="server" OnSelectedIndexChanged="DataList2_SelectedIndexChanged">
<ItemTemplate>
<asp:LinkButton ID="lk" Text='<%#Container.DataItem%>' CommandName="select" runat="server" />
</ItemTemplate>
</asp:DataList>
|
* ArrayList- array of object
*Array- it maybe of string , integers
* rollup- detailed summary as per select command
* rollup is always used with group by
*
Container- current item
*
DataItem- element
* using System.Collections – array of
objects, pre-defined collection
No comments:
Post a Comment