Reset MSSQL Table Identity

We can use DBCC CHECKIDENT to reset the indentity value of SQL table. Let Say, you have a SQL table with Auto-Increment ID. If you delete the records from ID numbers 10-15 and insert the new record, it'll take the next ID i.e. 16.

But if you want the new record starts from ID: 11, you can do this with the help of simple single query:

DBCC CHECKIDENT ('TableName', RESEED, 10)

So, it'll reset the indentity to 10 and next ID will be autoIncrement by 1 i.e. 11.

Happy Programming !!

ROW_NUMBER Function--Paging Records Using SQL Server 2005 Database

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!!

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();

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!!

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;
}
}

}