Tuesday, July 6, 2010

How to get day name and count between date range

ALTEr FUNCTION [dbo].[GetDayName]
(
@StartDate Datetime ,
@EndDate Datetime
)
RETURNS @TblIBB table ([Date] Date, [dayName] varchar(15))
As
BEGIN
insert into @TblIBB
Select [DATE],DATENAME ( dw ,[DATE] ) [DayName]

from
(
SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )

[Date] = dateadd(DAY,ROW_NUMBER()

OVER(ORDER BY c1.name),

DATEADD(DD,-1,@StartDate))

FROM [master].[dbo].[spt_values] c1
) t1
return
End

Thursday, October 22, 2009

Diable cache for the web page

Add this code on page Load::
----------------------------------
Response.Cache.SetExpires(DateTime.UtcNow.AddMinutes(-1))Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Cache.SetNoStore()
--------------------------------
In Html part Add
-----------
META HTTP-EQUIV="pragma" Content="nocache"

Tuesday, March 24, 2009

converting Rows to columns in SQL Server.

http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx


------------------------::--------------
Rows to Columns How to convert rows into columns? We're going to see how to do that using query, and there is the "Pivot" function in SQL Server 2005.

The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns.

Using Query:

If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns:

Example: We have 2 tables: LKUP_Subject which is a lookup table that holds the subject names, and the Student_Subject which contains the student grades for the different subject.



We are going to build the query having fixed columns:

We use the "if-else" functionality in the query to put them in the defined columns (in SQL, it is "case"):
SELECT StudentId,
(CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
(CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
(CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
(CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject






2. Then we use the “SUM” function to merge the results in 1 row like we want:

SELECT StudentId,
SUM(Physics) AS Physics,
SUM(Chemistry) As Chemistry,
SUM(Math) AS Math,
SUM(English) As English
FROM
(SELECT StudentId,
(CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
(CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
(CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
(CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject) s
GROUP BY StudentId







Now, we will build it dynamically using cursor (you can do it using temporary tables to do the same functionality for performance reasons):



DECLARE Cur CURSOR FOR
SELECT DISTINCT id, '[' + Description_En + ']' AS Description_En
FROM LKUP_Subject

DECLARE @SubjectName NVARCHAR(MAX),
@SubjectId INT,
@Sum NVARCHAR(MAX), -- The SUM part of the query
@Select NVARCHAR(MAX), -- The inner query
@Sql NVARCHAR(MAX) -- The total sql statement

SET @Select = ''
SET @Sum = ''
SET @Sql = ''

OPEN Cur


FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sum = @Sum + 'SUM(' + @SubjectName + ') AS ' + @SubjectName + ','
SET @Select = @Select + '(CASE WHEN SubjectId = ' + CONVERT(NVARCHAR(10), @SubjectId) + ' THEN Grade END) AS ' + @SubjectName + ','
FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName

END

CLOSE Cur
DEALLOCATE Cur


IF RIGHT(@Select, 1) = ','
SET @Select = STUFF(@Select, LEN(@Select), 1, ' FROM Student_Subject')

IF RIGHT(@Sum, 1) = ','
SET @Sum = STUFF(@Sum, LEN(@Sum), 1, '')

SET @Sql = 'SELECT StudentId, ' + @Sum + ' FROM (SELECT StudentId, ' + @Select + ') s GROUP BY StudentId'

EXEC sp_executesql @Sql



Using Pivot:

In SQL Server 2005, there is a new feature that does all of this in a single step: PIVOT

In fixed columns, here is how we use it:

SELECT StudentId, Physics, Chemistry, Math, English
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
INNER JOIN Student_Subject
ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(Physics, Chemistry, Math, English)) AS pvt



Note: that you should use an aggreate function like the SUM (for the same reason as you should use an aggreate function when using the query to transform Rows to Columns)


As for how to do it dynamically:

DECLARE Cur CURSOR FOR
SELECT DISTINCT Description_En
FROM LKUP_Subject

DECLARE @Temp NVARCHAR(MAX),
@AllSubjects NVARCHAR(MAX),
@SubjectQuery NVARCHAR(MAX)

SET @AllSubjects = ''

OPEN Cur
-- Getting all the subjects
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AllSubjects = @AllSubjects + '[' + @Temp + '],'
FETCH NEXT FROM Cur INTO @Temp
END

CLOSE Cur
DEALLOCATE Cur

SET @AllSubjects = SUBSTRING(@AllSubjects, 0, LEN(@AllSubjects))

-- Building the pivot query
SET @SubjectQuery = 'SELECT StudentId, ' + @AllSubjects + '
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
INNER JOIN Student_Subject
ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(' + @AllSubjects + ')) AS pvt'

EXEC sp_executesql @SubjectQuery

Wednesday, March 18, 2009

javascript:: for getting window height;

1.function winHt()
2.{
3.var ht='';
4.ht=document.body.clientHeight;//gets the body height of window opened.
5.alert(ht);
6.}

Friday, March 6, 2009

Convert Number to words (for Indian rupees and other currency ) in C# Asp.Net

public class NumberToWordsForCurrency
{


#region "--Methods--"
public string ConvertToInteranationWords(string numb)
{
string RetVal = string.Empty;
numb = numb.Trim();
if (this.IsNumber(numb))
{
try
{
string currency = "$ ";
string _currency = " cents Only.";
string Number;
string deciml;
string _number;
string _deciml;

this.Initialize();
string[] no = numb.Split('.');
if (no.Length == 1)
{
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfNumber(Number));
RetVal = currency + _number + " Only.";
}
else
{
RetVal = currency + " Zero Only.";
}

}
else if (no.Length == 2)
{
//int number part
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfNumber(Number));
}
else
{
_number = "Zero";
}
//decimal number part
if (no[1].Length >= 1 && Convert.ToDouble(no[1]) > 0)
{
deciml = no[1];
if (no[1].Length == 1)
{
deciml = Convert.ToString(Convert.ToDouble(no[1]) * 10);
}
_deciml = (NameOfNumber(deciml));
}
else
{
_deciml = "Zero";
}
// when both int and decimal part conbined.
RetVal = currency + _number.Trim() + " and " + _deciml.Trim() + _currency;
}

}
catch (Exception ex)
{

}
}
else
{
RetVal = "Invalid Value";
}

return RetVal;
}
#region "--Private variables--"
string[] US = new string[1003];
string[] SNu = new string[20];
string[] SNt = new string[10];

#endregion

///
///
///

private void Initialize()
{
SNu[0] = "";
SNu[1] = "One";
SNu[2] = "Two";
SNu[3] = "Three";
SNu[4] = "Four";
SNu[5] = "Five";
SNu[6] = "Six";
SNu[7] = "Seven";
SNu[8] = "Eight";
SNu[9] = "Nine";
SNu[10] = "Ten";
SNu[11] = "Eleven";
SNu[12] = "Twelve";
SNu[13] = "Thirteen";
SNu[14] = "Fourteen";
SNu[15] = "Fifteen";
SNu[16] = "Sixteen";
SNu[17] = "Seventeen";
SNu[18] = "Eighteen";
SNu[19] = "Nineteen";
SNt[2] = "Twenty";
SNt[3] = "Thirty";
SNt[4] = "Forty";
SNt[5] = "Fifty";
SNt[6] = "Sixty";
SNt[7] = "Seventy";
SNt[8] = "Eighty";
SNt[9] = "Ninety";

US[1] = "";
US[2] = "Thousand";
US[3] = "Million";
US[4] = "Billion";
US[5] = "Trillion";
US[6] = "Quadrillion";
US[7] = "Quintillion";
US[8] = "Sextillion";
US[9] = "Septillion";
US[10] = "Octillion";


}
///
///
///

///
///
public string NameOfNumber(string Number)
{
string GroupName = "";
string OutPut = "";
if ((Number.Length % 3) != 0)
{
Number = Number.PadLeft((Number.Length + (3 - (Number.Length % 3))), '0');
}
string[] Array = new string[Number.Length / 3];
Int16 Element = -1;
Int32 DisplayCount = -1;
bool LimitGroupsShowAll = false;
int LimitGroups = 0;
bool GroupToWords = true;
for (Int16 Count = 0; Count <= Number.Length - 3; Count += 3)
{
Element += 1;
Array[Element] = Number.Substring(Count, 3);
}
if (LimitGroups == 0)
{
LimitGroupsShowAll = true;
}
for (Int16 Count = 0; (Count <= ((Number.Length / 3) - 1)); Count++)
{
DisplayCount++;
if (((DisplayCount < LimitGroups) || LimitGroupsShowAll))
{
if (Array[Count] == "000") continue;
{
GroupName = US[((Number.Length / 3) - 1) - Count + 1];
}
if ((GroupToWords == true))
{
OutPut += Group(Array[Count]).TrimEnd(' ') + " " + GroupName + " ";
}
else
{
OutPut += Array[Count].TrimStart('0') + " " + GroupName;
}
}
}
Array = null;
return OutPut;
}
///
///
///

///
///
private string Group(string Argument)
{
string Hyphen = ""; string OutPut = ""; Int16 d1 = Convert.ToInt16(Argument.Substring(0, 1));
Int16 d2 = Convert.ToInt16(Argument.Substring(1, 1));
Int16 d3 = Convert.ToInt16(Argument.Substring(2, 1));
if ((d1 >= 1))
{
OutPut += SNu[d1] + " hundred ";
}
if ((double.Parse(Argument.Substring(1, 2)) < 20))
{
OutPut += SNu[Convert.ToInt16(Argument.Substring(1, 2))];
}
if ((double.Parse(Argument.Substring(1, 2)) >= 20))
{
if (Convert.ToInt16(Argument.Substring(2, 1)) == 0)
{
Hyphen += " ";
}
else
{
Hyphen += " ";
}
OutPut += SNt[d2] + Hyphen + SNu[d3];
}
return OutPut;
}
///
/// Test whether string is a number or not.
///

///
/// True if its a valid number.
private Boolean IsNumber(string num)
{
Boolean RetVal = false;
num = num.Trim();
if (num.Length > 0)
{
try
{
double test;
test = Convert.ToDouble(num);
RetVal = true;
}
catch (System.FormatException ex)
{
//throw;
}
catch (Exception ex)
{
//throw;
}
}
return RetVal;
}


public string ConvertToINR(string numb)
{
string RetVal = string.Empty;
numb = numb.Trim();
if (this.IsNumber(numb))
{
try
{
string currency = "Rupees ";
string _currency = " Paise Only.";
string Number;
string deciml;
string _number;
string _deciml;

this.Initialize();
string[] no = numb.Split('.');
if (no.Length == 1)
{
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfINRNumber(Number));
RetVal = currency + _number + " Only.";
}
else
{
RetVal = currency + " Zero Only.";
}

}
else if (no.Length == 2)
{
//int number part
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfINRNumber(Number));
}
else
{
_number = "Zero";
}
//decimal number part
if (no[1].Length >= 1 && Convert.ToDouble(no[1]) > 0)
{
deciml = no[1];
if (deciml.Length > 2)
{
deciml = deciml.Substring(0, 2);
}
if (no[1].Length == 1)
{
deciml = Convert.ToString(Convert.ToDouble(no[1]) * 10);
}
_deciml = (NameOfINRNumber(deciml));
}
else
{
_deciml = "Zero";
}
// when both int and decimal part conbined.
RetVal = currency + _number.Trim() + " and " + _deciml.Trim() + _currency;
}

}
catch (Exception ex)
{

}
}
else //not a number
{
RetVal = "Invalid Input.";
}
return RetVal;

}

private string NameOfINRNumber(string Number)
{
string RetVal = string.Empty;
try
{
int len = Number.Length;
if (len >= 0 && len <= 5)
{
RetVal = (NameOfNumber(Number));
}
else if (len > 5 && len <= 7)
{
RetVal = NameOfNumber(Number.Substring(0, len - 5)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 7 && len <= 9)
{
RetVal = NameOfNumber(Number.Substring(0, len - 7)) + " Crore " + NameOfNumber(Number.Substring(len - 7, 2)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 9 && len <= 11)
{
RetVal = NameOfNumber(Number.Substring(0, len - 9)) + " Arab " + NameOfNumber(Number.Substring(len - 9, 2)) + " Crore " + NameOfNumber(Number.Substring(len - 7, 2)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 11 && len <= 13)
{
RetVal = NameOfNumber(Number.Substring(0, len - 11)) + " Kharab " + NameOfNumber(Number.Substring(len - 11, 2)) + " Arab " + NameOfNumber(Number.Substring(len - 9, 2)) + " Crore " + NameOfNumber(Number.Substring(len - 7, 2)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 13)
{
RetVal = NameOfNumber(Number);
}

}
catch (Exception ex)
{
//throw new Exception("The method or operation is not implemented.");
}
return RetVal;
}

#endregion






}

Wednesday, February 25, 2009

Creating a XML document in .Net

Creating a XML document with C#
using System;
using System.Xml;

namespace AlokKumarSinha
{
class MainClass
{
XmlDocument xmldoc;
XmlNode xmlnode;
XmlElement xmlelem;
XmlElement xmlelem2;
XmlText xmltext;
static void Main(string[] args)
{
MainClass app=new MainClass();
}
public MainClass() //constructor
{
xmldoc=new XmlDocument();
//let's add the XML declaration section
xmlnode=xmldoc.CreateNode(XmlNodeType.XmlDeclaration,"","");
xmldoc.AppendChild(xmlnode);
//let's add the root element
xmlelem=xmldoc.CreateElement("","root","");
xmltext=xmldoc.CreateTextNode("root text");
xmlelem.AppendChild(xmltext);
xmldoc.AppendChild(xmlelem);
//let's add another element (child of the root)
xmlelem2=xmldoc.CreateElement("","child1","");
xmltext=xmldoc.CreateTextNode("The text of the child1 element");
xmlelem2.AppendChild(xmltext);
xmldoc.ChildNodes.Item(1).AppendChild(xmlelem2);
//let's try to save the XML document in a file: C:\alok.xml
try
{
xmldoc.Save("c:\alok.xml"); //I've chosen the c:\ for the resulting file alok.xml
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.ReadLine();
}
}
}

Creating a XML document with VB.Net

Class MainClass
Private xmldoc As XmlDocument
Private xmlnode As XmlNode
Private xmlelem As XmlElement
Private xmlelem2 As XmlElement
Private xmltext As XmlText
Private Shared Sub Main(ByVal args As String())
Dim app As New MainClass()
End Sub
Public Sub New()
'constructor
xmldoc = New XmlDocument()
'let's add the XML declaration section
xmlnode = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", "")
xmldoc.AppendChild(xmlnode)
'let's add the root element
xmlelem = xmldoc.CreateElement("", "root", "")
xmltext = xmldoc.CreateTextNode("root text")
xmlelem.AppendChild(xmltext)
xmldoc.AppendChild(xmlelem)
'let's add another element (child of the root)
xmlelem2 = xmldoc.CreateElement("", "Child1", "")
xmltext = xmldoc.CreateTextNode("The text of child1")
xmlelem2.AppendChild(xmltext)
xmldoc.ChildNodes.Item(1).AppendChild(xmlelem2)
'let's try to save the XML document in a file: C:\alok.xml
Try
xmldoc.Save("c:" & vbNullChar & "alok.xml")
'I've chosen the c:\ for the resulting file alok.xml
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Console.ReadLine()
End Sub
End Class

Saturday, February 7, 2009

To add a search engine to web page

To add a search engine to my web page that my users be able to search content of my website(all the pages not only the first page) ::
Solution
1. Visit This link:: http://forums.asp.net/p/1217595/2163891.aspx.

2.To have internal ASP.NET Search engine for your pages, have a look here: Internal Site Search Engine
To have google search engine on your websitem, follow this link: http://www.google.com/coop/cse/