//Posted By Suresh
//HTML Code
//Code Behind Code
private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
pupulategrid();
}
}
protected void GVBooking_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GVBooking.PageIndex = e.NewPageIndex;
pupulategrid();
}
protected void GVBooking_RowCreated(object sender, GridViewRowEventArgs e)
{
// Leave header and footer of Gridview and assign in the rest of the item row
if (e.Row.RowType != DataControlRowType.Header || e.Row.RowType != DataControlRowType.Header)
{
e.Row.Attributes.Add("onmouseout", "this.className='normalRow'");
e.Row.Attributes.Add("onmouseover", "this.className='highlightRow'");
}
}
protected void GVBooking_DataBound(Object sender, EventArgs e)
{
GridViewRow gvrPager = GVBooking.TopPagerRow;
if (gvrPager == null) return;
// get your controls from the gridview
DropDownList ddlPages = (DropDownList)gvrPager.Cells[0].FindControl("ddlPages");
Label lblPageCount = (Label)gvrPager.Cells[0].FindControl("lblPageCount");
if (ddlPages != null)
{
// populate pager
for (int i = 0; i < GVBooking.PageCount; i++)
{
int intPageNumber = i + 1;
System.Web.UI.WebControls.ListItem lstItem = new System.Web.UI.WebControls.ListItem(intPageNumber.ToString());
if (i == GVBooking.PageIndex)
lstItem.Selected = true;
ddlPages.Items.Add(lstItem);
}
}
// populate page count
if (lblPageCount != null)
lblPageCount.Text = GVBooking.PageCount.ToString();
}
protected void ddlPages_SelectedIndexChanged(Object sender, EventArgs e)
{
GridViewRow gvrPager = GVBooking.TopPagerRow;
DropDownList ddlPages = (DropDownList)gvrPager.Cells[0].FindControl("ddlPages");
GVBooking.PageIndex = ddlPages.SelectedIndex;
// a method to populate your grid
pupulategrid();
}
protected void Paginate(object sender, CommandEventArgs e)
{
// get the current page selected
int intCurIndex = GVBooking.PageIndex;
switch (e.CommandArgument.ToString().ToLower())
{
case "first":
GVBooking.PageIndex = 0;
break;
case "prev":
if (GVBooking.PageIndex > 0)
GVBooking.PageIndex = intCurIndex - 1;
break;
case "next":
int i = GVBooking.PageIndex + 1;
if (i <= GVBooking.PageCount)
{
GVBooking.PageIndex = i;
}
break;
case "last":
GVBooking.PageIndex = GVBooking.PageCount;
break;
}
// popultate the gridview control
pupulategrid();
}
public void pupulategrid()
{
//Grid Filling From Data base
//Bind Grid
// Also Maintain Session Of Data table
//Session["DataTableTotalBooking"]=Dt
}
public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;
return (SortDirection)ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}
protected void GVBooking_Sorting(object sender, GridViewSortEventArgs e)
{
string sortExpression = e.SortExpression;
if (GridViewSortDirection == SortDirection.Ascending)
{
GridViewSortDirection = SortDirection.Descending;
SortGridView(sortExpression, DESCENDING);
}
else
{
GridViewSortDirection = SortDirection.Ascending;
SortGridView(sortExpression, ASCENDING);
}
}
private void SortGridView(string sortExpression, string direction)
{
DataTable dt = (DataTable)Session["DataTableTotalBooking"];
DataView dv = new DataView(dt);
dv.Sort = sortExpression + direction;
GVBooking.DataSource = dv;
GVBooking.DataBind();
}
Tuesday, June 29, 2010
File Compression Or DeCompression in C#
// Posted By Suresh
//Compression
public void Compression()
{
string SourcePath = "c:\\bps.txt";
string DestPath = "c:\\bps.gz";
FileStream sourceFile = File.OpenRead(SourcePath);
FileStream destinationFile = File.Create(DestPath);
byte[] buffer = new byte[sourceFile.Length];
sourceFile.Read(buffer, 0, buffer.Length);
using (GZipStream output = new GZipStream(destinationFile, CompressionMode.Compress))
{
output.Write(buffer, 0, buffer.Length);
}
// Close the files.
sourceFile.Close();
destinationFile.Close();
}
//DeCompression
public void DeCompression()
{
string SourcePath = "c:\\bps.gz";
string DestPath = "c:\\bps1.txt";
FileStream sourceFile = File.OpenRead(SourcePath);
FileStream destinationFile = File.Create(DestPath);
// Because the uncompressed size of the file is unknown,
// we are using an arbitrary buffer size.
byte[] buffer = new byte[10485760]; //10MB
int n;
using (GZipStream input = new GZipStream(sourceFile, CompressionMode.Decompress, false))
{
n = input.Read(buffer, 0, buffer.Length);
destinationFile.Write(buffer, 0, n);
}
// Close the files.
sourceFile.Close();
destinationFile.Close();
}
//Compression
public void Compression()
{
string SourcePath = "c:\\bps.txt";
string DestPath = "c:\\bps.gz";
FileStream sourceFile = File.OpenRead(SourcePath);
FileStream destinationFile = File.Create(DestPath);
byte[] buffer = new byte[sourceFile.Length];
sourceFile.Read(buffer, 0, buffer.Length);
using (GZipStream output = new GZipStream(destinationFile, CompressionMode.Compress))
{
output.Write(buffer, 0, buffer.Length);
}
// Close the files.
sourceFile.Close();
destinationFile.Close();
}
//DeCompression
public void DeCompression()
{
string SourcePath = "c:\\bps.gz";
string DestPath = "c:\\bps1.txt";
FileStream sourceFile = File.OpenRead(SourcePath);
FileStream destinationFile = File.Create(DestPath);
// Because the uncompressed size of the file is unknown,
// we are using an arbitrary buffer size.
byte[] buffer = new byte[10485760]; //10MB
int n;
using (GZipStream input = new GZipStream(sourceFile, CompressionMode.Decompress, false))
{
n = input.Read(buffer, 0, buffer.Length);
destinationFile.Write(buffer, 0, n);
}
// Close the files.
sourceFile.Close();
destinationFile.Close();
}
Monday, June 28, 2010
String Functions in Sql Using Example
// Posted By Suresh
Declare @str varchar(50), @str1 varchar(50)
set @str = 'Suresh'
set @str1 = 'Jangid'
--Convert In Upper case
Select UPPER(@str) -- OUTPUT >> SURESH
--Convert In Lower case
Select LOWER(@str) -- OUTPUT >> suresh
--Add Space Between Two Strings
Select @str + SPACE(1) + @str1 --OUTPUT >> Suresh Jangid
--Reverse From any Sting
Select REVERSE(UPPER(@str)) -- OUTPUT >> HSERUS
Select CHARINDEX('r',@str) -- OUTPUT >> 3
--Char to Ascii
Select ASCII('A') --OUTPUT >> 65
--Character Find Out From String Non Case Sensitive
Select LEN(@str)-LEN(REPLACE(@str,'s',''))-- OUTPUT >> 2
--Character Find Out From String Case Sensitive
Select LEN(@str COLLATE SQL_Latin1_General_Cp1_CS_AS)-LEN(REPLACE(@str COLLATE SQL_Latin1_General_Cp1_CS_AS,'s' COLLATE SQL_Latin1_General_Cp1_CS_AS,''))-- OUTPUT >> 1
--ASCII TO Char Conversion
Select Nchar(65) --OUTPUT >> A
Select UNICODE('A') --OUTPUT >> 65
--Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types
SELECT PATINDEX ( '%s%', @str) --OUTPUT >> 1
--The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position
Select STUFF(@str,2,2,@str1) --OUTPUT >> SJangidesh
--Returns the left part of a character string with the specified number of characters.
Select LEFT(@str,3) --OUTPUT >>Sur
--Returns the Right part of a character string with the specified number of characters
Select RIGHT(@str,3) --OUTPUT >> esh
--Repeats a string value a specified number of times
Select REPLICATE(@str,2)--OUTPUT >> SureshSuresh
--Returns part of a character, binary, text, or image expression
Select SUBSTRING(@str,3,LEN(@str)) --OutPut >> resh
--Returns a character expression after it removes leading blanks From Left
Select LTRIM(' '+@str)
--Returns a character expression after it removes leading blanks From Right
Select RTRIM(@str+' ')
--Replaces all occurrences of a specified string value with another string value.
Select REPLACE(@str,'s','x') -- OutPut >> xurexh
Declare @str varchar(50), @str1 varchar(50)
set @str = 'Suresh'
set @str1 = 'Jangid'
--Convert In Upper case
Select UPPER(@str) -- OUTPUT >> SURESH
--Convert In Lower case
Select LOWER(@str) -- OUTPUT >> suresh
--Add Space Between Two Strings
Select @str + SPACE(1) + @str1 --OUTPUT >> Suresh Jangid
--Reverse From any Sting
Select REVERSE(UPPER(@str)) -- OUTPUT >> HSERUS
Select CHARINDEX('r',@str) -- OUTPUT >> 3
--Char to Ascii
Select ASCII('A') --OUTPUT >> 65
--Character Find Out From String Non Case Sensitive
Select LEN(@str)-LEN(REPLACE(@str,'s',''))-- OUTPUT >> 2
--Character Find Out From String Case Sensitive
Select LEN(@str COLLATE SQL_Latin1_General_Cp1_CS_AS)-LEN(REPLACE(@str COLLATE SQL_Latin1_General_Cp1_CS_AS,'s' COLLATE SQL_Latin1_General_Cp1_CS_AS,''))-- OUTPUT >> 1
--ASCII TO Char Conversion
Select Nchar(65) --OUTPUT >> A
Select UNICODE('A') --OUTPUT >> 65
--Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types
SELECT PATINDEX ( '%s%', @str) --OUTPUT >> 1
--The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position
Select STUFF(@str,2,2,@str1) --OUTPUT >> SJangidesh
--Returns the left part of a character string with the specified number of characters.
Select LEFT(@str,3) --OUTPUT >>Sur
--Returns the Right part of a character string with the specified number of characters
Select RIGHT(@str,3) --OUTPUT >> esh
--Repeats a string value a specified number of times
Select REPLICATE(@str,2)--OUTPUT >> SureshSuresh
--Returns part of a character, binary, text, or image expression
Select SUBSTRING(@str,3,LEN(@str)) --OutPut >> resh
--Returns a character expression after it removes leading blanks From Left
Select LTRIM(' '+@str)
--Returns a character expression after it removes leading blanks From Right
Select RTRIM(@str+' ')
--Replaces all occurrences of a specified string value with another string value.
Select REPLACE(@str,'s','x') -- OutPut >> xurexh
Wednesday, June 23, 2010
SQL Table to XML conversion using SQL Query
// Posted By Suresh
SELECT *
FROM Emp_Master s
ORDER BY s.Id
FOR XML PATH('Employee'), root('Employees')
SELECT *
FROM Emp_Master s
ORDER BY s.Id
FOR XML PATH('Employee'), root('Employees')
Saturday, June 19, 2010
Simple Encryption/Decryption Functions in SQL SERVER
// Posted By Suresh
//Encryption
CREATE FUNCTION [dbo].[Suresh_Encryption_UDF] ( @pStr VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vEncStr NVARCHAR(100)
DECLARE @vIndex INT
DECLARE @vBase INT
SET @vIndex = 1
SET @vBase = 128
SET @vEncStr = ''
WHILE @vIndex <= LEN(@pStr)
BEGIN
SET @vEncStr = @vEncStr + NCHAR(ASCII(SUBSTRING(@pStr, @vIndex, 1)) + @vBase + @vIndex - 1)
SET @vIndex = @vIndex + 1
END
RETURN @vEncStr
END
GO
//Decryption
CREATE FUNCTION [dbo].[Suresh_Decryption_UDF] ( @pEncStr NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vStr VARCHAR(100)
DECLARE @vIndex INT
DECLARE @vBase INT
SET @vIndex = 1
SET @vBase = 128
SET @vStr = ''
WHILE @vIndex <= LEN(@pEncStr)
BEGIN
SET @vStr = @vStr + CHAR(UNICODE(SUBSTRING(@pEncStr, @vIndex, 1)) - @vBase - @vIndex + 1)
SET @vIndex = @vIndex + 1
END
RETURN @vStr
END
GO
//Encryption
CREATE FUNCTION [dbo].[Suresh_Encryption_UDF] ( @pStr VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vEncStr NVARCHAR(100)
DECLARE @vIndex INT
DECLARE @vBase INT
SET @vIndex = 1
SET @vBase = 128
SET @vEncStr = ''
WHILE @vIndex <= LEN(@pStr)
BEGIN
SET @vEncStr = @vEncStr + NCHAR(ASCII(SUBSTRING(@pStr, @vIndex, 1)) + @vBase + @vIndex - 1)
SET @vIndex = @vIndex + 1
END
RETURN @vEncStr
END
GO
//Decryption
CREATE FUNCTION [dbo].[Suresh_Decryption_UDF] ( @pEncStr NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vStr VARCHAR(100)
DECLARE @vIndex INT
DECLARE @vBase INT
SET @vIndex = 1
SET @vBase = 128
SET @vStr = ''
WHILE @vIndex <= LEN(@pEncStr)
BEGIN
SET @vStr = @vStr + CHAR(UNICODE(SUBSTRING(@pEncStr, @vIndex, 1)) - @vBase - @vIndex + 1)
SET @vIndex = @vIndex + 1
END
RETURN @vStr
END
GO
Count Character Occurrences in SQL QUERY
//Posted By Suresh
Declare @Str varchar(100), @CStr Varchar(5)
Set @Str = 'Hi, My self Suresh Chand' -- String
Set @CStr = 'S' --Finding Character
-- Not case-sensitive
select (LEN(@Str) - LEN(REPLACE(@Str, @CStr, '')))
-- Case-sensitive
SELECT (LEN(@Str) - LEN(REPLACE(@Str COLLATE SQL_Latin1_General_Cp1_CS_AS, @CStr COLLATE SQL_Latin1_General_Cp1_CS_AS, '')))
Declare @Str varchar(100), @CStr Varchar(5)
Set @Str = 'Hi, My self Suresh Chand' -- String
Set @CStr = 'S' --Finding Character
-- Not case-sensitive
select (LEN(@Str) - LEN(REPLACE(@Str, @CStr, '')))
-- Case-sensitive
SELECT (LEN(@Str) - LEN(REPLACE(@Str COLLATE SQL_Latin1_General_Cp1_CS_AS, @CStr COLLATE SQL_Latin1_General_Cp1_CS_AS, '')))
Comma-Delimited Output in SQL SERVER
// Posted By Suresh
DECLARE C1s CURSOR FOR
SELECT C1 FROM tablename order by C1 desc
DECLARE @C1s VARCHAR(8000)
DECLARE @C1 VARCHAR(10)
OPEN C1s
FETCH NEXT FROM C1s INTO @C1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @C1s = ISNULL(@C1s + ',', '') + @C1
FETCH NEXT FROM C1s INTO @C1
END
CLOSE C1s
DEALLOCATE C1s
SELECT @C1s AS C1s
// ANOTHER Method
SELECT Name
FROM Emp_Master
GO
-- Get CSV values
SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM Emp_Master s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
DECLARE C1s CURSOR FOR
SELECT C1 FROM tablename order by C1 desc
DECLARE @C1s VARCHAR(8000)
DECLARE @C1 VARCHAR(10)
OPEN C1s
FETCH NEXT FROM C1s INTO @C1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @C1s = ISNULL(@C1s + ',', '') + @C1
FETCH NEXT FROM C1s INTO @C1
END
CLOSE C1s
DEALLOCATE C1s
SELECT @C1s AS C1s
// ANOTHER Method
SELECT Name
FROM Emp_Master
GO
-- Get CSV values
SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM Emp_Master s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
Subscribe to:
Posts (Atom)