Friday, September 26, 2008

Custom Paging in GridView, DataList or Repeater Using Stored Procedure.

· 1 comments

First of all of I have created table named tblEmployee as belove figure.


Using Row_Number : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.


Query : select *,ROW_NUMBER() over(Order By Name) as PageNum from tblEmployee


This query generates one extra column as PageNum title and it contains index of data row starting at 1for the first row.

Now to perform Paging in store procedure, I Pass two parameter @PageIndex and @PageSizes and get back total number of cecords in the table as out parameter.

Store Procedure


CREATE PROCEDURE usp_List_Employees

(
@pageIndex int,
@PageSize int,
@OutParameter int out
)

AS

Select * from
(
select *,ROW_NUMBER() over(Order By Name) as PageNum
from tblEmployee
)
as tblEmployee
where PageNum Between (@PageIndex - 1) * @PageSize + 1 and @PageIndex * @PageSize

select @outParameter=count(*) from tblEmployee


Inline code

<table cellpadding="5" cellspacing="2" width="400px">
<tr>
<td colspan="8">
<asp:GridView ID="grid" runat="server" AutoGenerateColumns="False" CellPadding="4"
ForeColor="#333333" GridLines="None" Height="184px" Width="491px">
<FooterStyle BackColor="#5D7B9D" ForeColor="White" Font-Bold="True" />
<RowStyle ForeColor="#333333" BackColor="#F7F6F3" HorizontalAlign="left" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" HorizontalAlign="left" />
<Columns>
<asp:TemplateField HeaderText="Sr.No">
<ItemTemplate>
<%# Eval("PageNum") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email Address">
<ItemTemplate>
<%#Eval("Email") %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</td>
</tr>
<tr>
<td width="10px">
<asp:LinkButton ID="lnkFirst" runat="server" Text="First" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="10px">
<asp:LinkButton ID="lnkPre" runat="server" Text="Pre" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="10px">
<asp:LinkButton ID="lnkNext" runat="server" Text="Next" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="10px">
<asp:LinkButton ID="lnkLast" runat="Server" Text="Last" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="100px"></td>
<td>
Page <asp:Label ID="lblPageNo" runat="server"></asp:Label> of 
<asp:Label ID="lblTotalPage" runat="server"></asp:Label></td>
<td>
</td>
<td align="right">
Jump to <asp:DropDownList ID="ddl" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Jump_ToPaging">
</asp:DropDownList></td>
</tr>
</table>




code behind


<add key="conString" value="server=ServerName; Data Source=DataSourceName; uid=username pw=password"/>


using System.Data.SqlClient;


private string conString = System.Configuration.ConfigurationManager.AppSettings["conString"];
private int currentPageIndex = 1;
private int pageSize = 5;
private int totalRecords=0;
private double totalPage = 0;


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind_Employees();
BindPageIndex();
}
}


public void bind_Employees()
{
grid.DataSource = GetData();
grid.DataBind();
PageSetting();
}


public void PageSetting()
{
totalPage = Convert.ToDouble(totalRecords) / pageSize;
totalPage = System.Math.Ceiling(totalPage);
lblPageNo.Text = totalPage == 0 ? "0" : currentPageIndex.ToString();
lblTotalPage.Text = totalPage.ToString();


if (currentPageIndex == 1)
{
lnkFirst.Enabled = false;
lnkPre.Enabled = false;
}
else
{
lnkFirst.Enabled = true;
lnkPre.Enabled = true;
}
if (totalPage > 1)
{
if (currentPageIndex != totalPage)
{
lnkNext.Enabled = true;
lnkLast.Enabled = true;
}
else
{
lnkNext.Enabled = false;
lnkLast.Enabled = false;
}
}
else
{
lnkNext.Enabled = false;
lnkLast.Enabled = false;
}
}


public void BindPageIndex()
{
ddl.Items.Clear();
for (int i = 1; i <= totalPage; i++)
{
ddl.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
}


public DataSet GetData()
{
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "usp_List_Employees";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

SqlParameter prm = new SqlParameter();
prm.ParameterName = "@pageIndex";
prm.Direction = ParameterDirection.Input;
prm.SqlDbType = SqlDbType.Int;
prm.SqlValue = currentPageIndex.ToString();
cmd.Parameters.Add(prm);

prm = new SqlParameter();
prm.ParameterName = "@PageSize";
prm.Direction = ParameterDirection.Input;
prm.SqlDbType = SqlDbType.Int;
prm.SqlValue = pageSize;
cmd.Parameters.Add(prm);

prm = new SqlParameter();
prm.ParameterName = "@OutParameter";
prm.Direction = ParameterDirection.Output;
prm.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(prm);


SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
totalRecords = int.Parse(cmd.Parameters["@OutParameter"].Value.ToString());
return ds;
}


protected void Paging_Event(object sender, EventArgs e)
{
switch (((LinkButton)sender).ID)
{
case "lnkFirst":
currentPageIndex = 1;
break;
case "lnkLast":
currentPageIndex = Int32.Parse(lblTotalPage.Text);
break;
case "lnkNext":
currentPageIndex = Int32.Parse(lblPageNo.Text) + 1;
break;
case "lnkPre":
currentPageIndex = Int32.Parse(lblPageNo.Text) - 1;
break;
}
bind_Employees();
ddl.SelectedValue = currentPageIndex.ToString();
}


protected void Jump_ToPaging(object sender, EventArgs e)
{
currentPageIndex = int.Parse(ddl.SelectedValue);
bind_Employees();
}

Thursday, September 25, 2008

Using RSS feeds in asp .net and C#

· 0 comments


using System.XML;


Gets the xml from the Url using XmlTextReader

XmlTextReader reader = new XmlTextReader("http://msdn.microsoft.com/rss.xml");


creates a new instance of DataSet

DataSet ds = new DataSet();


Reads the xml into the dataset

ds.ReadXml(reader);


Assigns the data table to the datagrid

grid.DataSource = ds.Tables[0];
myDataGrid.DataBind();

Friday, September 19, 2008

Programmatically Add Meta Tags in asp.net page

· 0 comments

// Create HtmlHead object.
HtmlHead head = (System.Web.UI.HtmlControls.HtmlHead)Header;

//Create HtmlMeta Object.
HtmlMeta meta = new HtmlMeta();

//Add Attributes to Meta tags.
meta.Attributes.Add("content", objLpa.MetaDesc);
meta.Attributes.Add("name", "description");

//Add meta tag to html header.
head.Controls.Add(meta);

Thursday, September 18, 2008

Rotate Image using c#

· 1 comments

using System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Text;
using System.Runtime.InteropServices;
using System.Drawing.Drawing2D;


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bitmap b = new Bitmap(Server.MapPath("TextWithMultilines.png"));
b = RotateImage(180, b); // Pass angle and bitmap object
b.Save(Server.MapPath("NewImage.png"), ImageFormat.Png);
b.Dispose();
}
}



public Bitmap RotateImage(float Angle, Bitmap bm_in)
{
try
{
float wid = bm_in.Width;
float hgt = bm_in.Height;
Point[] corners = { new Point(0, 0), new Point(int.Parse(wid.ToString()), 0), new Point(0, int.Parse(hgt.ToString())), new Point(int.Parse(wid.ToString()), int.Parse(hgt.ToString())) };
int cx = int.Parse(wid.ToString()) / 2;
int cy = int.Parse(hgt.ToString()) / 2;
long i;
for (i = 0; i <= 3; i++)
{
corners[i].X -= Convert.ToInt32(cx.ToString());
corners[i].Y -= Convert.ToInt32(cy.ToString());
}

float theta = (float)(Angle * Math.PI / 180.0);
float sin_theta = (float)Math.Sin(theta);
float cos_theta = (float)Math.Cos(theta);
float X;
float Y;
for (i = 0; i <= 3; i++)
{
X = corners[i].X;
Y = corners[i].Y;
corners[i].X = (int)(X * cos_theta + Y * sin_theta);
corners[i].Y = (int)(-X * sin_theta + Y * cos_theta);
}

float xmin = corners[0].X;
float ymin = corners[0].Y;
for (i = 1; i <= 3; i++)
{
if (xmin > corners[i].X)
xmin = corners[i].X;
if (ymin > corners[i].Y)
ymin = corners[i].Y;
}
for (i = 0; i <= 3; i++)
{
corners[i].X -= int.Parse(xmin.ToString());
corners[i].Y -= int.Parse(ymin.ToString());
}

Bitmap bm_out = new Bitmap((int)(-2 * xmin), (int)(-2 * ymin));
Graphics gr_out = Graphics.FromImage(bm_out);
// ERROR: Not supported in C#: ReDimStatement
Point[] temp = new Point[3];
if (corners != null)
{
Array.Copy(corners, temp, Math.Min(corners.Length, temp.Length));
}
corners = temp;
gr_out.DrawImage(bm_in, corners);
gr_out.Dispose();

return bm_out;
}
catch (Exception ex)
{
return bm_in;
}
}

Download file from the server to local machine

· 0 comments

In C#
string FileName = Server.MapPath("MyFileName.txt");
Response.Clear();
Response.ClearContent();
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Disposition", "attachment; filename=DownloadFile.txt;");

byte[] buffer = System.IO.File.ReadAllBytes(FileName);

System.IO.MemoryStream mem = new System.IO.MemoryStream();
mem.Write(buffer, 0, buffer.Length);

mem.WriteTo(Response.OutputStream);
Response.End();

In Vb.Net
Dim FileName As String = Server.MapPath("MyFileName.txt")
Response.Clear()
Response.ClearContent()
Response.ContentType = "application/pdf"
Response.AddHeader("Content-Disposition", "attachment; filename=DownloadFile.txt;")

Dim buffer As Byte() = System.IO.File.ReadAllBytes(FileName)

Dim mem As New System.IO.MemoryStream()
mem.Write(buffer, 0, buffer.Length)

mem.WriteTo(Response.OutputStream)
Response.End()