ROW_NUMBER returns a sequential number for each row returned in a resultset, starting from 1. It can help with paging records for the database applications.
Let see the Example:
Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers
If you have lots of records on your database and you want certain number of records to retrieve (at a time) from database to make it faster and efficient, then this is the best idea.
Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers where row>=1 and row<=50
Let say you have 100 thousands records and you made a interface to access those records. If you try to get all the records at a time, it might crash the internet explorer. In this situation, you can write stored procedure, that would accept 'startRowIndex' and 'MaximumRows' (where you define the maximum rows to display in a page). In the mean time, if you display those records in a Gridview control and want to do the header sorting when click on it, you can pass sortExpression parameter in your SP. I have written this SP for my database search application last week:
Create procedure RajSearch
@StartRowIndex INT,
@MaximumRows INT,
@SortExpression nvarchar(100),
@StringPass nvarchar(100)
As
declare @RajTable table
(rowId int identity(1,1), ID int,
Name nvarchar(255),
Sender nvarchar(255),
Recipient nvarchar(255),
date varchar(255),
Reel nvarchar(255),
Reference_URL nvarchar(255) )
Declare @ID int,
@Name nvarchar(255),
@Sender nvarchar(255),
@Recipient nvarchar(255),
@Date nvarchar(255),
@Reel nvarchar(255),
@Reference_URL nvarchar(255)
--Define a cursor
Declare Search cursor Fast_Forward for
with FixedList as(
SELECT ID,Name,Sender,Recipient,Date,Reel,Reference_URL,
ROW_NUMBER() OVER
(ORDER BY
(case when @sortExpression = 'Sender ASC' THEN Sender END) ASC,
(case when @sortExpression = 'Sender DESC' THEN Sender END) DESC,
(case when @sortExpression = 'Recipient ASC' THEN Recipient END) ASC,
(case when @sortExpression = 'Recipient DESC' THEN Recipient END) DESC,
(case when @SortExpression = 'Date ASC' THEN Date END) ASC,
(case when @SortExpression = 'Date DESC' THEN Date END) DESC,
(case when @sortExpression = 'Reel ASC' THEN Reel END) ASC,
(case when @sortExpression = 'Reel DESC' THEN Reel END) DESC )
AS [RowNo] from TestTable
where (Name like '%' + @stringPass + '%' or
Sender LIKE '%' + @stringPass + '%' or
Recipient LIKE '%' + @stringPass + '%' or
Date LIKE '%' + @stringPass + '%' or
Reel LIKE '%' + @stringPass + '%' or
Reference_URL LIKE '%' + @stringPass + '%'))
Select Sender, Recipient, Date, Reel, Reference_URL from FixedList
where RowNo BETWEEN @StartRowIndex AND @StartRowIndex + @MaximumRows
open Search
while 1=1
begin
fetch next from Search
into @Sender, @Recipient,@Date,@Reel,@Reference_URL
if @@FETCH_STATUS<>0
break
if(@Reel is not null and @Reference_URL is not null)
begin
--open in a new window
set @Reel = '<a href=' + '"' + @Reference_URL + '"' + ' target="_blank">' + @Reel + '</a>'
end
insert into @RajTable(ID,Name, Sender,
Recipient, Date, Reel,Reference_URL)
values
(@ID,@Name,@Sender, @Recipient, @Date@Reel,@Reference_URL)
end
close Search
deallocate Search
select Sender,Recipient,Date,Reel from @RajTable order by Date
return
Happy Programming!!
Friday, February 4, 2011
Tuesday, January 11, 2011
Encode and Decode the QueryString parameter value
Let say, I have a web application where I send data from a page to another page via QueryString. If the Querysting value contains '&' sign, it'll truncate the data after this sign and we won't get the matched data:
<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%#DataBinder.Eval(Container.DataItem,"book_name","chapter_Matches.aspx?book_name={0}")%>'>
<%# DataBinder.Eval(Container.DataItem, "book_name")%></asp:HyperLink>
</td>
and in chapter_Matches.aspx:
str = Request.QueryString["book_name"].ToString();
<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%#DataBinder.Eval(Container.DataItem,"book_name","chapter_Matches.aspx?book_name={0}")%>'>
<%# DataBinder.Eval(Container.DataItem, "book_name")%></asp:HyperLink>
</td>
and in chapter_Matches.aspx:
str = Request.QueryString["book_name"].ToString();
Example: If the book_name is 'Chemistry', it'll work on this case but if the book_name is 'Chemistry & Physics', it'll truncate the value after '&' sign and only pass 'Chemistry' and the URL is chapter_matches.aspx?book_name=chemistry.
to pass this value(Chemistry & Physics) via QueryString, encode the specific parameter value, by itself and on the receiving page, do the opposite:
<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%# string.Format("chapter_Matches.aspx?book_name={0}"), SanitizeURLString(DataBinder.Eval(Container.DataItem, "book_name").ToString()))%>' Text ='<%# DataBinder.Eval(Container.DataItem, "book_name")%' </asp:HyperLink>
</td>
on aspx.cs page:
protected string SanitizeURLString(string RawURLParameter)
{
string Results;
Results = RawURLParameter;
Results = Results.Replace("<", "%3C");
Results = Results.Replace(">", "%3E");
Results = Results.Replace("#", "%23");
Results = Results.Replace("%", "%25");
Results = Results.Replace("{", "%7B");
Results = Results.Replace("}", "%7D");
Results = Results.Replace("", "%7C");
Results = Results.Replace("\\", "%5C");
Results = Results.Replace("^", "%5E");
Results = Results.Replace("~", "%7E");
Results = Results.Replace("[", "%5B");
Results = Results.Replace("]", "%5D");
Results = Results.Replace("`", "%60");
Results = Results.Replace(";", "%3B");
Results = Results.Replace("/", "%2F");
Results = Results.Replace("?", "%3F");
Results = Results.Replace(":", "%3A");
Results = Results.Replace("@", "%40");
Results = Results.Replace("=", "%3D");
Results = Results.Replace("&", "%26");
Results = Results.Replace("$", "%24");
return Results;
}
On receiving aspx.cs page:
str = DeSanitizeURLString(Request.QueryString["book_name"].ToString());
protected string DeSanitizeURLString(string RawURLParameter)
{
string Results;
Results = RawURLParameter;
Results = Results.Replace("%3C", "<");
Results = Results.Replace("%3E", ">");
Results = Results.Replace("%23", "#");
Results = Results.Replace("%25", "%");
Results = Results.Replace("%7B", "{");
Results = Results.Replace("%7D", "}");
Results = Results.Replace("%7C", "");
Results = Results.Replace("%5C", "\\");
Results = Results.Replace("%5E", "^");
Results = Results.Replace("%7E", "~");
Results = Results.Replace("%5B", "[");
Results = Results.Replace("%5D", "]");
Results = Results.Replace("%60", "`");
Results = Results.Replace("%3B", ";");
Results = Results.Replace("%2F", "/");
Results = Results.Replace("%3F", "?");
Results = Results.Replace("%3A", ":");
Results = Results.Replace("%40", "@");
Results = Results.Replace("%3D", "=");
Results = Results.Replace("%26", "&");
Results = Results.Replace("%24", "$");
return Results;
}
Happy Programming!!
Thursday, September 23, 2010
Save and display images to and from SQL Database C#
Save images on the Database
Let say, I've a fileupload control to browse images and a button to upload images on SQL database.
<asp:FileUpload ID="FileUpload1" runat="server"/>
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click"/>
write this code inside the button click event:
protected void btnSubmit_Click(object sender, EventArgs e)
{
//to store image into sql database.
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);
// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Pictures(ImageName,Image)" +
" VALUES (@ImageName,@Image)";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);
SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
lblSuccess.Text = "Successful !";
}
}
Display the images from database:
Let's display the image on Gridview
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
AutoGenerateColumns="False" DataKeyNames="ID" Width="245px">
<Columns>
<asp:BoundField DataField="TreatmentID" HeaderText="ID" Visible="false"
SortExpression="TreatmentID" />
<asp:BoundField DataField="imageName" HeaderText="ImageName"
SortExpression="imageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
ImageUrl='<%#"Handler.ashx?ID=" + Eval("ID")%>'/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TreatmentConnectionString %>"
SelectCommand="SELECT * FROM [Pictures]">
and add a handler class with the below code:
using System;
using System.Web;
using System.Data.SqlClient;
public class Handler : IHttpHandler {
public void ProcessRequest (HttpContext context)
{
try
{
SqlConnection con = new SqlConnection(GetConnectionString());
// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ID,imageName, Image from Pictures" +
" where ID =@ID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
SqlParameter ImageID = new SqlParameter
("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}
catch(Exception ex)
{
ex.Message.ToString();
}
}
public bool IsReusable {
get {
return false;
}
}
}
Let say, I've a fileupload control to browse images and a button to upload images on SQL database.
<asp:FileUpload ID="FileUpload1" runat="server"/>
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click"/>
write this code inside the button click event:
protected void btnSubmit_Click(object sender, EventArgs e)
{
//to store image into sql database.
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);
// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Pictures(ImageName,Image)" +
" VALUES (@ImageName,@Image)";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);
SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
lblSuccess.Text = "Successful !";
}
}
Display the images from database:
Let's display the image on Gridview
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
AutoGenerateColumns="False" DataKeyNames="ID" Width="245px">
<Columns>
<asp:BoundField DataField="TreatmentID" HeaderText="ID" Visible="false"
SortExpression="TreatmentID" />
<asp:BoundField DataField="imageName" HeaderText="ImageName"
SortExpression="imageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
ImageUrl='<%#"Handler.ashx?ID=" + Eval("ID")%>'/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TreatmentConnectionString %>"
SelectCommand="SELECT * FROM [Pictures]">
and add a handler class with the below code:
using System;
using System.Web;
using System.Data.SqlClient;
public class Handler : IHttpHandler {
public void ProcessRequest (HttpContext context)
{
try
{
SqlConnection con = new SqlConnection(GetConnectionString());
// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ID,imageName, Image from Pictures" +
" where ID =@ID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
SqlParameter ImageID = new SqlParameter
("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}
catch(Exception ex)
{
ex.Message.ToString();
}
}
public bool IsReusable {
get {
return false;
}
}
}
Friday, August 20, 2010
Mobile Device detection in ASP.NET and use CSS to display
There are basically two ways to detect mobile devices:
-in terms of display (480px/800px)
-in terms of user agent.
My code used to detect using the display but last week Android updated their software (changed the display from 480px to 800px) so all the mobile tweaks I made for our apps are no longer work on Droid Phones.
That's why I want to follow the second method to detect the mobile devices and in future no matter whether they channged the display setting or not.
Let's identify the User Agent(e.g. Android) and switch style sheets depending. (I don't want to redirect to another page, just switch style sheets).
In aspx.cs page:
protected override void OnInit(EventArgs e)
{
string userAgent = Request.UserAgent;
if (userAgent.Contains("BlackBerry")
(userAgent.Contains("iPhone") (userAgent.Contains("Android"))))
{
//add css ref to header from code behind
HtmlLink css = new HtmlLink();
css.Href = ResolveClientUrl("~/mobile.css");
css.Attributes["rel"] = "stylesheet";
css.Attributes["type"] = "text/css";
css.Attributes["media"] = "all";
Page.Header.Controls.Add(css);
}
}
Mobile.cs:
#header {
display: none;
}
#body {
margin: 0;
width: 100%;
min-width: 100%;
font-size: 12px;
background-image: none;
empty-cells: hide;
}
#column_left {
float: none;
width: 100%;
padding-left: 5px;
}
#column_right {
float: none;
width: 100%;
padding-left: 5px;
}
#menuwrapper {
display: none;
}
#sssubsearch {
display: none;
}
h2 {
font-size: 16px;
text-align: center;
}
h3, h4 {
font-size: 1em;
/* border-bottom-width: 1px;
border-bottom-style: solid;
border-bottom-color: #666;*/
}
h3 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #E6E6E6;*/
font-size: 16px;
padding-left: 0;
margin: 0;
padding-top: 10px;
padding-bottom: 0;
}
h4 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #FFC;*/
font-size: 16px;
color: #600;
padding-left: 0;
margin-left: 0;
empty-cells: hide;
}
table {
width: 98%;
font-size: 14px;
empty-cells: hide;
}
#navigation {
display: none;
}
.left {
float: left;
vertical-align:top;
}
.right {
float: right;
vertical-align:top;
}
hr {display: none;}
Happy Coding !!
-in terms of display (480px/800px)
-in terms of user agent.
My code used to detect using the display but last week Android updated their software (changed the display from 480px to 800px) so all the mobile tweaks I made for our apps are no longer work on Droid Phones.
That's why I want to follow the second method to detect the mobile devices and in future no matter whether they channged the display setting or not.
Let's identify the User Agent(e.g. Android) and switch style sheets depending. (I don't want to redirect to another page, just switch style sheets).
In aspx.cs page:
protected override void OnInit(EventArgs e)
{
string userAgent = Request.UserAgent;
if (userAgent.Contains("BlackBerry")
(userAgent.Contains("iPhone") (userAgent.Contains("Android"))))
{
//add css ref to header from code behind
HtmlLink css = new HtmlLink();
css.Href = ResolveClientUrl("~/mobile.css");
css.Attributes["rel"] = "stylesheet";
css.Attributes["type"] = "text/css";
css.Attributes["media"] = "all";
Page.Header.Controls.Add(css);
}
}
Mobile.cs:
#header {
display: none;
}
#body {
margin: 0;
width: 100%;
min-width: 100%;
font-size: 12px;
background-image: none;
empty-cells: hide;
}
#column_left {
float: none;
width: 100%;
padding-left: 5px;
}
#column_right {
float: none;
width: 100%;
padding-left: 5px;
}
#menuwrapper {
display: none;
}
#sssubsearch {
display: none;
}
h2 {
font-size: 16px;
text-align: center;
}
h3, h4 {
font-size: 1em;
/* border-bottom-width: 1px;
border-bottom-style: solid;
border-bottom-color: #666;*/
}
h3 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #E6E6E6;*/
font-size: 16px;
padding-left: 0;
margin: 0;
padding-top: 10px;
padding-bottom: 0;
}
h4 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #FFC;*/
font-size: 16px;
color: #600;
padding-left: 0;
margin-left: 0;
empty-cells: hide;
}
table {
width: 98%;
font-size: 14px;
empty-cells: hide;
}
#navigation {
display: none;
}
.left {
float: left;
vertical-align:top;
}
.right {
float: right;
vertical-align:top;
}
hr {display: none;}
Happy Coding !!
Subscribe to:
Posts (Atom)
