Age Calculating or Date Subtraction in SQL

// Posted By Suresh

declare @BirthDate datetime,@ToDate datetime, @result datetime
set @BirthDate = '2/2/1984'
set @ToDate = GETDATE()
declare @BirthDay int, @ToDay int, @BirthMonth int, @ToMonth int, @BirthYear int, @ToYear int, @PreToMonth int, @BorrowDay int
declare @Day int, @Month int, @Year int

select @BirthDay= DATEPART(day,@BirthDate),@BirthMonth= DATEPART(MONTH,@BirthDate), @BirthYear = DATEPART(year,@BirthDate)
select @ToDay= DATEPART(day,@ToDate),@ToMonth= DATEPART(MONTH,@ToDate), @ToYear = DATEPART(year,@ToDate)

if(@ToMonth >1)
begin
SET @PreToMonth = @ToMonth - 1;
end
else
begin
set @PreToMonth = 12;
end

if(@BirthDay<=@ToDay)
begin
set @Day= @ToDay - @BirthDay
end
else
begin
if(@PreToMonth = 1 or @PreToMonth = 3 or @PreToMonth = 5 or @PreToMonth = 7 or @PreToMonth = 8 or @PreToMonth = 10 or @PreToMonth = 12)
begin
set @BorrowDay = 31
end
else if(@PreToMonth = 4 or @PreToMonth = 6 or @PreToMonth = 9 or @PreToMonth = 11)
begin
set @BorrowDay = 30
end
else
begin
if(@ToYear%400 =0 or (@ToYear%100 <> 0 AND @ToYear%4 = 0))
begin
set @BorrowDay = 29
end
else
begin
set @BorrowDay = 28
end
end
SET @ToMonth = @ToMonth - 1
set @ToDay = @ToDay + @BorrowDay
set @Day= @ToDay - @BirthDay
end


if(@BirthMonth<=@ToMonth)
begin
set @Month= @ToMonth - @BirthMonth
end
else
begin
set @ToYear = @ToYear - 1
set @ToMonth = @ToMonth + 12
set @Month = @ToMonth - @BirthMonth
end

if(@BirthYear<=@ToYear)
begin
set @Year= @ToYear - @BirthYear
end
else
begin
set @Year = 0
end

print cast(@Year as varchar(4)) + ' Year, '+ cast(@Month as varchar(4)) + ' Month, ' + cast(@Day as varchar(4)) + ' Days'

Get column names of a specified table in SQL

//Post By Suresh Chand

SELECT [NAME] AS 'Columns' FROM SYSCOLUMNS WHERE [ID] = Object_Id('Table Name')

OR

exec sp_columns 'Table Name'

String Functions in C#

Swaps the cases in a string
//Suresh -> sURESH, SURESH -> suresh, SuReSh -> sUrEsH
public string SwapCases(string sInput)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToUpper(), false) == 0)
sRetValue += sInput.Substring(i, 1).ToLower();
else
sRetValue += sInput.Substring(i, 1).ToUpper();
}
return sRetValue;
}


Alternates cases between letters of a string, letting the user pick if the first letter is capitalized
public string AlternateCases(string sInput, bool startWithUpper)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
if (startWithUpper)
sRetValue += sInput.Substring(i, 1).ToUpper();
else
sRetValue += sInput.Substring(i, 1).ToLower();
startWithUpper = !startWithUpper;
}
return sRetValue;
}

Removes vowels from a word
//Suresh-> Srsh
public string RemoveVowels(string sInput)
{
string sRetValue = "";
string Letter;
for (int i = 0; i < sInput.Length; i++)
{
Letter = sInput.Substring(i, 1);
if (string.Compare(Letter, "a", true) != 0 && string.Compare(Letter, "e", true) != 0 && string.Compare(Letter, "i", true) != 0 && string.Compare(Letter, "o", true) != 0 && string.Compare(Letter, "u", true) != 0)
{
sRetValue += Letter;
}
}
return sRetValue;
}

Keep vowels from a word
//Suresh -> ue
public string KeepVowels(string sInput)
{
string sRetValue = "";
string Letter;
for (int i = 0; i < sInput.Length; i++)
{
Letter = sInput.Substring(i, 1);
if (string.Compare(Letter, "a", true) == 0 || string.Compare(Letter, "e", true) == 0 || string.Compare(Letter, "i", true) == 0 || string.Compare(Letter, "o", true) == 0 || string.Compare(Letter, "u", true) == 0)
{
sRetValue += Letter;
}
}
return sRetValue;
}

Returns an array converted into a string
public string ArrayToString(Array sInput, string separator)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
sRetValue += sInput.GetValue(i).ToString();
if (i != sInput.Length - 1)
sRetValue += separator;
}
return sRetValue;
}

Inserts a separator after every letter
//Suresh,- -> S-u-r-e-s-h
public string InsertSeparator(string sInput, string separator)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
sRetValue += sInput.Substring(i, 1);
if (i != sInput.Length - 1)
sRetValue += separator;
}
return sRetValue;
}

Inserts a separator after every Count letters
//Suresh, -, 2 -> Su-re-sh
public string InsertSeparatorEvery(string sInput, string separator, int count)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i+=count)
{
if (i + count < sInput.Length)
sRetValue += sInput.Substring(i, count);
else
sRetValue += sInput.Substring(i);
if (i < sInput.Length - count)
sRetValue += separator;
}
return sRetValue;
}

Reverses a string
//Suresh -> hseruS
public string Reverse(string sInput)
{
string sRetValue = "";
for (int i = sInput.Length - 1; i >= 0; i--)
{
sRetValue += sInput.Substring(i, 1);
}
return sRetValue;
}



Capitalizes a word or sentence
//suresh -> Suresh, my self suresh jangid -> My Self Suresh Jangid
public string Capitalize(string sInput)
{
if (sInput.Length == 0) return "";
if (sInput.Length == 1) return sInput.ToUpper();
return sInput.Substring(0, 1).ToUpper() + sInput.Substring(1);
}

Checks whether a word or sentence is capitalized
//Suresh -> True, My self suresh jangid -> True
public bool IsCapitalized(string sInput)
{
if (sInput.Length == 0) return false;
return string.Compare(sInput.Substring(0, 1), sInput.Substring(0, 1).ToUpper(), false) == 0;
}

Checks whether a string is in all lower case
//suresh -> True, Suresh -> False
public bool IsLowerCase(string sInput)
{
for (int i = 0; i < sInput.Length; i++)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToLower(), false) != 0)
return false;
}
return true;
}

Checks whether a string is in all upper case
//suresh -> False, Suresh -> False, SURESH -> True
public bool IsUpperCase(string sInput)
{
for (int i = 0; i < sInput.Length; i++)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToUpper(), false) != 0)
return false;
}
return true;
}

Alternates cases between letters of a string, first letter's case stays the same
//Bi -> Bi, suresh -> SuReSh
public string AlternateCases(string sInput)
{
if (sInput.Length == 0) return "";
if (sInput.Length == 1) return sInput; //Cannot automatically alternate
bool firstIsUpper = string.Compare(sInput.Substring(0, 1), sInput.Substring(0, 1).ToUpper(), false) != 0;
string sRetValue = sInput.Substring(0, 1);
for (int i = 1; i < sInput.Length; i++)
{
if (firstIsUpper)
sRetValue += sInput.Substring(i, 1).ToUpper();
else
sRetValue += sInput.Substring(i, 1).ToLower();
firstIsUpper = !firstIsUpper;
}
return sRetValue;
}

Checks to see if a string has alternate cases
//sUrEsH -> True
public bool IsAlternateCases(string sInput)
{
if (sInput.Length <= 1) return false;

bool lastIsUpper = string.Compare(sInput.Substring(0, 1), sInput.Substring(0, 1).ToUpper(), false) == 0;

for (int i = 1; i < sInput.Length; i++)
{
if (lastIsUpper)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToLower(), false) != 0)
return false;
}
else
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToUpper(), false) != 0)
return false;
}

lastIsUpper = !lastIsUpper;
}

return true;
}

Counts total number of a char or chars in a string
//Suresh, s -> 2, Suresh, re -> 1
public int CountTotal(string sInput, string chars, bool ignoreCases)
{
int count = 0;
for (int i = 0; i < sInput.Length; i++)
{
if (!(i + chars.Length > sInput.Length) && string.Compare(sInput.Substring(i, chars.Length), chars, ignoreCases) == 0)
{
count++;
}
}
return count;
}

Checks to see if a string contains vowels
//Suresh -> True, Srsh -> False
public bool HasVowels(string sInput)
{
string currentLetter;
for (int i = 0; i < sInput.Length; i++)
{
currentLetter = sInput.Substring(i, 1);

if (string.Compare(currentLetter, "a", true) == 0 ||
string.Compare(currentLetter, "e", true) == 0 ||
string.Compare(currentLetter, "i", true) == 0 ||
string.Compare(currentLetter, "o", true) == 0 ||
string.Compare(currentLetter, "u", true) == 0)
{
//A vowel found
return true;
}
}

return false;
}

Checks if string is nothing but spaces
//" " -> True
public bool IsSpaces(string sInput)
{
if (sInput.Length == 0) return false;
return sInput.Replace(" ", "").Length == 0;
}

Checks if the string has all the same letter/number
//aaaaaa -> true, aaaaaaaab -> false
public bool IsRepeatedChar(string sInput)
{
if (sInput.Length == 0) return false;
return sInput.Replace(sInput.Substring(0, 1), "").Length == 0;
}

Checks if string has only numbers
//12453 -> True, 234d3 -> False
public bool IsNumeric(string sInput)
{
for (int i = 0; i < sInput.Length; i++)
{
if (!(Convert.ToInt32(sInput[i]) >= 48 && Convert.ToInt32(sInput[i]) <= 57))
{
//Not integer value
return false;
}
}
return true;
}

Checks if the string contains numbers
//suresh -> False, sure65sh -> True
public bool HasNumbers(string sInput)
{
return System.Text.RegularExpressions.Regex.IsMatch(sInput, "\\d+");
}

Checks if string is numbers and letters
//suresh34 -> True, $chool! -> False
public bool IsAlphaNumberic(string sInput)
{
char currentLetter;
for (int i = 0; i < sInput.Length; i++)
{
currentLetter = sInput[i];

if (!(Convert.ToInt32(currentLetter) >= 48 && Convert.ToInt32(currentLetter) <= 57) &&
!(Convert.ToInt32(currentLetter) >= 65 && Convert.ToInt32(currentLetter) <= 90) &&
!(Convert.ToInt32(currentLetter) >= 97 && Convert.ToInt32(currentLetter) <= 122))
{
//Not a number or a letter
return false;
}
}
return true;
}

Checks if a string contains only letters
//Hi -> True, Hi123 -> False
public bool isLetters(string sInput)
{
char currentLetter;
for (int i = 0; i < sInput.Length; i++)
{
currentLetter = sInput[i];

if (!(Convert.ToInt32(currentLetter) >= 65 && Convert.ToInt32(currentLetter) <= 90) &&
!(Convert.ToInt32(currentLetter) >= 97 && Convert.ToInt32(currentLetter) <= 122))
{
//Not a letter
return false;
}
}
return true;
}

Returns the initials of a name or sentence
//capitalize - whether to make intials capitals
//includeSpace - to return intials separated (True - S. J. or False - S.J.)
//Suresh Jangid -> S. J. or S.J.
public string GetInitials(string sInput, bool capitalize, bool includeSpace)
{
string[] words = sInput.Split(new char[] { ' ' });

for (int i = 0; i < words.Length; i++)
{
if (words[i].Length > 0)
if (capitalize)
words[i] = words[i].Substring(0, 1).ToUpper() + ".";
else
words[i] = words[i].Substring(0, 1) + ".";
}

if (includeSpace)
return string.Join(" ", words);
else
return string.Join("", words);
}

Capitalizes the first letter of every word
//the earth -> The Earth
public string GetTitle(string sInput)
{
string[] words = sInput.Split(new char[] { ' ' });

for (int i = 0; i < words.Length; i++)
{
if (words[i].Length > 0)
words[i] = words[i].Substring(0, 1).ToUpper() + words[i].Substring(1);
}

return string.Join(" ", words);
}


Checks whether the first letter of each word is capitalized
//The Earth -> True, the earth -> False
public bool IsTitle(string sInput)
{
string[] words = sInput.Split(new char[] { ' ' });

for (int i = 0; i < words.Length; i++)
{
if (words[i].Length > 0)
if (string.Compare(words[i].Substring(0, 1).ToUpper(), words[i].Substring(0, 1), false) != 0)
return false;
}
return true;
}

Returns all the locations of a char in a string
//SURESH, s -> 0, 4
public int[] IndexOfAll(string sInput, string chars)
{
List indices = new List();
for (int i = 0; i < sInput.Length; i++)
{
if (sInput.Substring(i, 1) == chars)
indices.Add(i);
}

if (indices.Count == 0)
indices.Add(-1);

return indices.ToArray();
}


Gets the char in a string at a given position, but counting from right to left
//Suresh, 2 -> e
public char CharRight(string sInput, int index)
{
if (sInput.Length - index - 1 >= sInput.Length ||
sInput.Length - index - 1 < 0)
return new char();

string str = sInput.Substring(sInput.Length - index - 1, 1);
return str[0];
}

Gets the char in a string from a starting position plus the index
//suresh, 3, 1 -> s
public char CharMid(string sInput, int startingIndex, int countIndex)
{
if (startingIndex + countIndex < sInput.Length)
{
string str = sInput.Substring(startingIndex + countIndex, 1);
return str[0];
}
else
return new char();
}

Function that works the same way as the default Substring, but it takes Start and End (exclusive) parameters instead of Start and Length
//Suresh, 1, 3 -> ur
public string SubstringEnd(string sInput, int start, int end)
{
if (start > end) //Flip the values
{
start ^= end;
end = start ^ end;
start ^= end;
}

if (end > sInput.Length) end = sInput.Length; //avoid errors

return sInput.Substring(start, end - start);

}

Splits strings, but leaves anything within quotes
//(Has issues with nested quotes
//This is a "Suresh jangid" ->
//This
//is
//a
//Suresh Jangid
public string[] SplitQuotes(string sInput, bool ignoreQuotes, string separator)
{
if (ignoreQuotes)
return sInput.Split(separator.ToCharArray());
else
{
string[] words = sInput.Split(separator.ToCharArray());
List newWords = new List();

for (int i = 0; i < words.Length; i++)
{
if (words[i].StartsWith('"'.ToString()))
{
List linked = new List();
for (int y = i; y < words.Length; y++)
{
if (words[y].EndsWith('"'.ToString()))
{
linked.Add(words[y].Substring(0, words[y].Length - 1));
i = y;
break;
}
else
{
if (words[y].StartsWith('"'.ToString()))
linked.Add(words[y].Substring(1));
}
}
newWords.Add(string.Join(separator, linked.ToArray()));
linked.Clear();
}
else
newWords.Add(words[i]);
}
return newWords.ToArray();
}
}

Convert an ArrayList to delimited string in c#

//posted By Suresh
//Input = StrA!%StrC!%StrB!%StrD!%StrE";
//Output = StrE!%StrD!%StrC!%StrB!%StrA";

string InStr = "StrA!%StrC!%StrB!%StrD!%StrE";
string outStr = "";
string[] del = new string[] {"!%"};
string[] TempArr = InStr.Split(del, StringSplitOptions.None);
Array.Sort(TempArr);
Array.Reverse(TempArr);
outStr = string.Join(("!%",TempArr);

Custom Pagging and Sorting in GridView

//Posted By Suresh


//HTML Code
<br /><div style="overflow: auto; height: 280px; width: 680px"><br /> <asp:GridView ID="GVBooking" runat="server" BackColor="#FBDAA0" Width="1200px" AutoGenerateColumns="true" BorderColor="Black" BorderWidth="1px" CellPadding="0" Font-Names="Arial" BorderStyle="Solid" ForeColor="Black" Font-Size="9pt" OnRowCreated="GVBooking_RowCreated" EmptyDataText="Data Not Available" AllowPaging="True" PageSize="15" OnPageIndexChanging="GVBooking_PageIndexChanging" Font-Strikeout="False" OnDataBound="GVBooking_DataBound" OnSorting = "GVBooking_Sorting" AllowSorting="true"><br /> <PagerTemplate><br /> <asp:ImageButton ID="ImgbtnFirst" runat="server" ImageUrl="~/Images/first.png" CommandArgument="First" CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="First" /><br /> <asp:ImageButton ID="ImgbtnPrev" runat="server" ImageUrl="~/Images/previous.png" CommandArgument="Prev" CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="Previous" /><br /> Page<br /> <asp:DropDownList OnSelectedIndexChanged="ddlPages_SelectedIndexChanged" ID="ddlPages"<br /> runat="server" AutoPostBack="True"><br /> </asp:DropDownList><br /> of<br /> <asp:Label Height="18px" ID="lblPageCount" ToolTip="Total Pages" runat="server"></asp:Label><br /> <asp:ImageButton ID="ImgbtnNext" runat="server" ImageUrl="~/Images/next.png" CommandArgument="Next"<br /> CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="Next" /><br /> <asp:ImageButton ID="ImgbtnLast" runat="server" ImageUrl="~/Images/last.png" CommandArgument="Last"<br /> CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="Last" /><br /> </PagerTemplate><br /> <PagerStyle CssClass="GridPager" BackColor="#CC3300" Font-Bold="True" ForeColor="White"<br /> VerticalAlign="Middle" Height="18px" HorizontalAlign="Left" /><br /> <HeaderStyle CssClass="GridHeader"></HeaderStyle><br /> <AlternatingRowStyle CssClass="GridAltItem" /><br /> <PagerSettings Position="Top" /><br /> <RowStyle CssClass="GridItem" /><br /> </asp:GridView><br /> </div><br />
//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();
}

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

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

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')

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

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, '')))

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

join RupeeMail! for income

http://www.rupeemail.in/rupeemail/invite.do?in=NTgwNTIwJSMlTzFocmlkUmJGSmxTeks4WHhMQnVDYW9QMg==

Radio button in Data grid with Single selection

//Posted By Suresh

add radio buttons to datagrid:


OnCheckedChanged="SelectOnlyOne"
id="RadioButton1" Text='hi' runat="server"/>





public void SelectOnlyOne(object sender,EventArgs e)
{
RadioButton rb = new RadioButton();
rb = (RadioButton) sender;
string sRbText = rb.ClientID;

foreach (DataGridItem i in DDLCon.Items)
{
rb = (RadioButton) i.FindControl ("RadioButton1");
rb.Checked = false;
if (sRbText==rb.ClientID)
{
rb.Checked = true;
}
}
}

Read text file as database in c#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;


namespace SureshApps
{
public class ReadText
{
OleDbConnection oConnection = new OleDbConnection();

private bool OpenConnection(string InputTextFileName)
{
if(!string.IsNullOrEmpty(InputTextFileName))
{
oConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+InputTextFileName+"'; Extended Properties=text;HDR=yes;FMT=Delimited";
oConnection.Open();

if(oConnection.State == System.Data.ConnectionState.Open)
return true;
else
return false;
}

return false;
}

public void BindingDataToGridView(string InputTextFileName, string Query, DataSet ds)
{
try
{
OpenConnection(InputTextFileName);

// Create the data adapter to retrieve all rows from text file.
OleDbDataAdapter da =
new OleDbDataAdapter(Query, oConnection);

// Create and fill the table.
DataSet dt = new DataSet("MyData");
da.Fill(dt);
ds = dt.Copy();

// Bind the default view of the table to the grid.
// DBview.DataSource = dt.DefaultView;


}catch(Exception ex)
{
Console.WriteLine("Error Occured: "+ ex.Message);
}
finally
{
if(oConnection.State == System.Data.ConnectionState.Open)
{
oConnection.Close();
}
}


}


}
}


Format of InputFile

Field1,Name,Fname
IM,XYZ,ABC
IM,SDF,ADFF
IM,DFDD,DFFF

Create a new table with old table structure In SQL SERVER

//Posted By Suresh

Select * into NewTable from OldTable where 1=2

Here the condition "1=2" is false. So the data will not be transfered to new table,
but the structure will be created from the old table.

FTP File Rename In C# Win Application

// Posted By Suresh

FtpWebRequest reqFTP;
try
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + currentFilename));
reqFTP.Method = WebRequestMethods.Ftp.Rename;
reqFTP.RenameTo = newFilename;
reqFTP.UseBinary = true;
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream ftpStream = response.GetResponseStream();

ftpStream.Close();
response.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

FTP File Delete in C# Win Application

// Posted By Suresh
string ftpServerIP = "192.168.1.25";
string ftpUserID = "SurESH";
string ftpPassword = "xxxxxx";
string fielname = "test.txt"

string uri = "ftp://" + ftpServerIP + "/" + fileName;
FtpWebRequest reqFTP;
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + fileName));

reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
reqFTP.KeepAlive = false;
reqFTP.Method = WebRequestMethods.Ftp.DeleteFile;

string result = String.Empty;
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
long size = response.ContentLength;
Stream datastream = response.GetResponseStream();
StreamReader sr = new StreamReader(datastream);
result = sr.ReadToEnd();
sr.Close();
datastream.Close();
response.Close();

DataBase to Excel In C# Win Application

//Posted By Suresh

string Query = "Select top 100 * From Table";

SqlCommand mycom = new SqlCommand(Query, con);
SqlDataAdapter adapter = new SqlDataAdapter(mycom);
DataSet dataset = new DataSet();
adapter.Fill(dataset);

TimeSpan TS = new TimeSpan(5,30,00);
DateTime DT = new DateTime();
DT = Convert.ToDateTime(System.DateTime.UtcNow.Add(TS));
string fn = "BK" + String.Format("{yyMMddHHmmss}", DT);

string filename = "c:\\suresh\\"+fn+".xls";
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;

// Start Excel and get Application object.
oXL = new Excel.Application();

// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "Jangid"; //Sheet Name

// Process the DataTable
// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
System.Data.DataTable dt = dataset.Tables[0];

int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();

// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

FTP File Uploading Using C# Win Application

// Posted By Suresh

string ftpServerIP = "192.168.1.52";
string ftpUserID = "admin";
string ftpPassword = "xxxxx";
string filename = "c:\\suresh.xls"
FileInfo fileInf = new FileInfo(filename);
string uri = "ftp://" + ftpServerIP + "/" + fileInf.Name;
FtpWebRequest reqFTP;

// Create FtpWebRequest object from the Uri provided
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + fileInf.Name));

// Provide the WebPermission Credintials
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);

// By default KeepAlive is true, where the control connection is not closed
// after a command is executed.
reqFTP.KeepAlive = false;

// Specify the command to be executed.
reqFTP.Method = WebRequestMethods.Ftp.UploadFile;

// Specify the data transfer type.
reqFTP.UseBinary = true;

// Notify the server about the size of the uploaded file
reqFTP.ContentLength = fileInf.Length;

// The buffer size is set to 2kb
int buffLength = 2048;
byte[] buff = new byte[buffLength];
int contentLen;

// Opens a file stream (System.IO.FileStream) to read the file to be uploaded
FileStream fs = fileInf.OpenRead();

try
{
// Stream to which the file to be upload is written
Stream strm = reqFTP.GetRequestStream();

// Read from the file stream 2kb at a time
contentLen = fs.Read(buff, 0, buffLength);

// Till Stream content ends
while (contentLen != 0)
{
// Write Content from the file stream to the FTP Upload Stream
strm.Write(buff, 0, contentLen);
contentLen = fs.Read(buff, 0, buffLength);
}

// Close the file stream and the Request Stream
strm.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Upload Error");
}

INSERT DATA FROM EXCEL TO SQL TABLE USING SINGLE QUERY

//POSETED BY SURESH CHAND JANGID

INSERT INTO SQLTableName(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4,GETDATE()
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\MSSQLTips\Examples.xls;',
'SELECT C1, C2, C3, C4 FROM [Sheet1$]')

Random colour on web page

//Posted By Suresh Chand
//Paste this code in head section in script section



//Enter list of bgcolors:
var bgcolorlist=new Array("#DFDFFF", "#FFFFBF", "#80FF80", "#EAEAFF", "#C9FFA8", "#F7F7F7", "#FFFFFF", "#DDDD00")

document.body.style.background=bgcolorlist[Math.floor(Math.random()*bgcolorlist.length)]

Quatation " Waqt Nahi"

Har khushi Hai Logon Ke Daman Mein,
Par Ek Hansi Ke Liye Waqt Nahi.
Din Raat Daudti Duniya Mein,
Zindagi Ke Liye Hi Waqt Nahi.

Maa Ki Lori Ka Ehsaas To Hai,
Par Maa Ko Maa Kehne Ka Waqt Nahi.
Saare Rishton Ko To Hum Maar Chuke,
Ab Unhe Dafnane Ka Bhi Waqt Nahi.

Saare Naam Mobile Mein Hain,
Par Dosti Ke Liye Waqt Nahi.
Gairon Ki Kya Baat Karen,
Jab Apno Ke Liye Hi Waqt Nahi.

Aankhon Me Hai Neend Badee,
Par Sone Ka Waqt Nahi.
Dil Hai Gamon Se Bhara ,
Par Rone Ka Bhi Waqt Nahi.

Paison ki Daud Me Aise Daude,
Ki Thakne ka Bhi Waqt Nahi.
Paraye Ehsason Ki Kya Kadr Karein,
Jab Apne Sapno Ke Liye Hi Waqt Nahi.

Tu Hi Bata E Zindagi,
Iss Zindagi Ka Kya Hoga,
Ki Har Pal Marne Walon Ko,
Jeene Ke Liye Bhi Waqt Nahi.........

Funy Quatation about Engineer

Engineer Woh Hai Jo Aksar Phasta Hai
Interviews Ke Sawaal Mae
Badi Companiyon Ki Chaal Mae
Boss Aur Client Ke Bawaal Mae
Engineer Woh Hai Jo Pak Gaya Hai
Meetings Ki Jhelai Mae
Submissions Ki Gehrai Mae
Teamwork Ki Chatai Mae
Engineer Woh Hai Jo Laga Rahta Hai
Schedule Ko Failane Mae
Targets Ko Khiskaane Mae
Roz Naye-Naye Bahane Mae
Engineer Woh Hai Jo
Lunch Time Mae Breakfast Karta Hai
Dinner Time Mae Lunch Karta Hai
Commutation Ke Waqt Soya Karta Hai
Engineer Woh Hai Jo Pagal Hai
Chai Aur Samose Ke Pyar Mae
Cigeratte Ke Khumar Mae
Birdwatching Ke Vichar Mae
Engineer Woh Hai Jo Khoya Hai
Reminders Ke Jawaab Mae
Na Milne Wale Hisaab Mae
Behtar Bhavishya Ke Khwaab Mae
Engineer Woh Hai Jise Intezaar Hai
Weekend Night Manane Ka
Boss Ke Chhutti Jaane Ka
Increment Ki Khabar Aane Ka
Engineer Woh Hai Jo Sochta Hai
Kaash Padhai Pe Dhyaan Diya Hota
Kaash Teacher Se Panga Na Liya Hota
Kaash Ishq Na Kiya Hota
Aur Sabse Behtar To Ye Hota
Kambakht Engineering Hi Na Kiya Hota…….. ….

Draw Bar Graph In C#

//Posted By Suresh Chand Jangid
// A procedure which is get three perameter and return a Bar graph
//1 strTitle - Tile of graph
//2 aX - Array list of X axis
//3 aY - Array list of Y axis



public void DrawBarGraph(string strTitle, ArrayList aX, ArrayList aY)
{
const int iColWidth = 15, iColSpace = 25, iMaxHeight = 400, iHeightSpace = 25, iXLegendSpace = 30, iTitleSpace = 50;

int iMaxWidth = (iColWidth + iColSpace) * aX.Count + iColSpace, iMaxColHeight = 0, iTotalHeight = iMaxHeight + iXLegendSpace + iTitleSpace;

Bitmap objBitmap = new Bitmap(iMaxWidth, iTotalHeight);
Graphics objGraphics = Graphics.FromImage(objBitmap);

objGraphics.FillRectangle(new SolidBrush(Color.White), 0, 0, iMaxWidth, iTotalHeight);
objGraphics.FillRectangle(new SolidBrush(Color.Ivory), 0, 0, iMaxWidth, iMaxHeight);

// find the maximum value
//int iValue;
foreach(int iValue in aY)
{
if(iValue > iMaxColHeight) iMaxColHeight = iValue;
}
int iBarX = iColSpace, iCurrentHeight;
SolidBrush objBrush = new SolidBrush(Color.FromArgb(70, 20, 20));
Font fontLegend = new Font("Arial", 11), fontValues = new Font("Arial", 8), fontTitle = new Font("Arial", 14);

// loop through and draw each bar
int iLoop;
for(iLoop = 0; iLoop <= aX.Count - 1;iLoop++)
{
iCurrentHeight = Convert.ToInt32( ((Convert.ToDouble(aY[iLoop]) / Convert.ToDouble(iMaxColHeight)) * Convert.ToDouble(iMaxHeight - iHeightSpace)));
objGraphics.FillRectangle(objBrush, iBarX, iMaxHeight - iCurrentHeight, iColWidth, iCurrentHeight);
objGraphics.DrawString(aX[iLoop].ToString(), fontLegend, objBrush, iBarX, iMaxHeight);
objGraphics.DrawString(aY[iLoop].ToString(), fontValues, objBrush, iBarX, iMaxHeight - iCurrentHeight - 15);
iBarX += (iColSpace + iColWidth);
}
objGraphics.DrawString(strTitle, fontTitle, objBrush, (iMaxWidth / 2) - strTitle.Length * 6, iMaxHeight + iXLegendSpace);
//objBitmap.Save("C:\inetpub\wwwroot\graph.gif", ImageFormat.GIF)
objBitmap.Save(Response.OutputStream, ImageFormat.Jpeg);
objGraphics.Dispose();
objBitmap.Dispose();
}

Auto Increament Field and Constant value Through Bulk COPY IN SQL

//Posted By Suresh Chand Jangid
//Auto Increament Field and Constant value Through Bulk COPY IN SQL
//complete text file copy in sql table

CREATE PROCEDURE ps_StudentList_Import
@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint
AS

--Step 1: Build Valid BULK INSERT Statement
DECLARE @SQL varchar(2000)
IF @FileType = 1
BEGIN
-- Valid format: "suresh","jangid","suresh@jangid.com"
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
END
ELSE
BEGIN
-- Valid format: "suresh","jangid","suresh@jangid.com"
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
END

--Step 2: Execute BULK INSERT statement
EXEC (@SQL)

--Step 3: INSERT data into final table
INSERT StudentList (StFName,StLName,StEmail,OrderID)
SELECT CASE WHEN @FileType = 1 THEN SUBSTRING(StFName,2,DATALENGTH(StFName)-1)
ELSE StFName
END,
SUBSTRING(StLName,1,DATALENGTH(StLName)-0),
CASE WHEN @FileType = 1 THEN SUBSTRING(StEmail,1,DATALENGTH(StEmail)-1)
ELSE StEmail
END,
@OrderID
FROM tmpStList

--Step 4: Empty temporary table
TRUNCATE TABLE TmpStList
Go

Folder / Directory Information in C#

// Posted By Suresh Chand Jangid
// Folder / Directory Infortion in C#
DirectoryInfo objDirectoryInfo = new DirectoryInfo "C://IndDBF/");
FileInfo []oFileInfo = objDirectoryInfo.GetFiles();
for(int i=0;i{
if(oFileInfo[i].Exists)
{
Path = oFileInfo[i].FullName;
FileName = oFileInfo[i].Name;
CreateDate = oFileInfo[i].CreationTime;
Size = Convert.ToString(oFileInfo[i].Length;
//oFileInfo[i].Delete(); // For Delete
}
}

Easy Encryption and Decryption Funda in C#

// Posted By Suresh Chand Jangid
//Encrytion In C#
Byte[] b = System.Text.ASCIIEncoding.ASCII.GetBytes(TextBox1.Text);
TextBox2.Text = Convert.ToBase64String(b);

//Decryption In C#
Byte[] b = Convert.FromBase64String(TextBox2.Text);
TextBox4.Text = System.Text.ASCIIEncoding.ASCII.GetString(b); }

Web Site Developed by Me

www.deepyoginterior.com

Read Excel File Through SQL Commands SELECT / INSERT / UPDATE in C#

// Posted By Suresh Chand Jangid
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source=c:/NameAndAddress.xls;Extended Properties=Excel 8.0";
OleDbConnection objConn = new OleDbConnection(strConn);
string strSql = "Select LastName, FirstName, Address, City, State From [Sheet1$]";
lblSql1.Text = strSql;
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
objConn.Open();
dtgAddresses1.DataSource = objCmd.ExecuteReader();
dtgAddresses1.DataBind();
objConn.Dispose();
objConn = new OleDbConnection(strConn);
strSql = "Select * From [Sheet1$] Where State='CA'";
lblSql2.Text = strSql;
objCmd = new OleDbCommand(strSql, objConn);
objConn.Open();
dtgAddresses2.DataSource = objCmd.ExecuteReader();
dtgAddresses2.DataBind();
objConn.Dispose();
objConn = new OleDbConnection(strConn);
strSql = "UPDATE [Sheet1$] SET LastName='CHAND' where FirstName='Suresh'";
objCmd = new OleDbCommand(strSql, objConn);
objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Dispose();
objConn = new OleDbConnection(strConn);
strSql = "INSERT INTO [Sheet1$] (LastName, FirstName, Address, City, State) VALUES ('a','b','c','d','e')";
objCmd = new OleDbCommand(strSql, objConn);
objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Dispose();