Wednesday, May 27, 2009

How to download a file from server using asp.net C#




Please visit my new Web Site WWW.Codedisplay.com



Downloading a file from server is a common task for most of the applications. Such as client wants to offer free download, free newletter etc. And i am sure that when you read this post you will get another articles with source code download facility. Am i right? Which means its our common task. Here i will try to show you how we can write the code to download a server file into our client or local PC.

Focus Area:
1. Download a file
2. Reusability
3. Security aspects
4. File size limitations
5. Few common errors
6. Important links


Download a file:
Let we have a folder in our virtual directory named download. Now from this folder i want to download a file named XMLFile.xml. To do that add a new aspx page. Drag & drop a button control on this page. Now under the button click event write the below code:

protected void cmdDownload_Click(object sender, EventArgs e)
{
Response.ContentType =
"APPLICATION/OCTET-STREAM";
String Header = "Attachment; Filename=XMLFile.xml";
Response.AppendHeader(
"Content-Disposition", Header);
System.IO.
FileInfo Dfile = new System.IO.FileInfo(Server.MapPath("download/XMLFile.xml"));
Response.WriteFile(Dfile.FullName);
//Don't forget to add the following line
Response.End();
}

Thats it. Now you can download your required file from the server.

Reusability:
Lets you have to provide this option in 5 pages then does it viable for you to write same code in 5 pages. There a lot of way for reusabilty but here i will show you the simplest way to achieve this. Add a class & modify this in the following way:

public static class Downloader
{
public static void Download(string sFileName,string sFilePath)
{
HttpContext.Current.Response.ContentType = "APPLICATION/OCTET-STREAM";
String Header = "Attachment; Filename="+sFileName;
HttpContext.Current.Response.AppendHeader("Content-Disposition", Header);
System.IO.
FileInfo Dfile = new System.IO.FileInfo(HttpContext.Current.Server.MapPath(sFilePath));
HttpContext.Current.Response.WriteFile(Dfile.FullName);
HttpContext.Current.Response.End();
}
}

Now you can reuse the method from any pages. The below code segment show you how you can call this method from your aspx page.

protected void cmdDownload_Click(object sender, EventArgs e)
{
Downloader.Download("XMLFile.xml", "download/XMLFile.xml");
}

In my article on Upload file i have discussed Security aspects & file size. To read Click Here.

Few common errors:
1. Could not find a part of the path........
Solution: Make sure that you have specified the path correctly.

2. Access or permission denied...
Solution: Read Security aspects & file size from above link.

3. HTML text/code added in the last with my downlaoded file.
Solution: You missed to add Response.End(); in your code.

Important Links :
http://www.west-wind.com/weblog/posts/76293.aspx
http://www.devarticles.com/c/a/ASP.NET/HTTP-File-Download-Without-User-Interaction-Using-.NET/1/
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q316431

Tuesday, May 26, 2009

How to bind a XML file to a GridView using Asp.net C#




Please visit my new Web Site WWW.Codedisplay.com



Recently one of the most popular data source is XML. For simplicity developer wants to make an XML file since its easy to use in the Asp.net built in data controls like GridView. In this post i will try to show you how one can bind easily XML file as a data source of GridView control. In the later section i will discuss how to bind XML file to a GridView control in run time.

Focus Area:
1. Bind XML file in design mode
2. Bind XML file programetically


Bind XML file in design mode:
To run this example at first add an XML file in your project & keep the name XMLFile.xml. Now modify the XML in the following way:

<?xml version="1.0" encoding="utf-8" ?>
<
Article>
<
Asp.net Id="0001" Title="Bind Dropdown List" Visit="985" Modified="Jan 01, 2009">
</
Asp.net>
<
Asp.net Id="0002" Title="Event calendar" Visit="777" Modified="Feb 01, 2009">
</
Asp.net>
<
Asp.net Id="0003" Title="Select all checkboxes" Visit="853" Modified="Mar 01, 2009">
</
Asp.net>
<
Asp.net Id="0004" Title="Transfer data between" Visit="957" Modified="Apr 01, 2009">
</
Asp.net>
</
Article>

Now add a GridView control in your page. The next step is to click the GridView control's smart tag and select from the Choose Data Source dropdown list. This opens the Data Source Configuration Wizard, as shown in below:



Now click on browse button to set a Data File. Bydefault you found that our previously cretaed XMLFile.xml is selected. So just click OK--OK. Now run the project. Hope you will get the output page like below:



In Configuration data source wizard you saw that i keep blank Transform file & Xpath expression. You can set the Transform file value by your XSLT & use Xpath expression to bind the selected node from your XML file.

To bind an XML file runtime into a GridView using Asp.net you can follow the below code:
protected void Page_Load(object sender, EventArgs e)
{
DataSet XMLDataSet;
string FilePath = Server.MapPath("Your XML File HERE");
XMLDataSet = new DataSet();

//Read the contents of the XML file into the DataSet
XMLDataSet.ReadXml(FilePath);
GridView1.DataSource = XMLDataSet.Tables[0].DefaultView;
GridView1.DataBind();
}

Happy programming.

Tuesday, May 19, 2009

How to Import Excel Spreadsheet Data into SQL Server Database using ASP.NET C#




Please visit my new Web Site WWW.Codedisplay.com



After developing an web application when you ask the operator to enter all existing data into the input interface then most of the cases the operator told you i have an Excel sheet please upload those data into the database which will reduce my time as well as human errors. This is the most common scenario. So you have to develop an interface where user can select an excel sheet to import into the SQL SERVER database.

Fortunately ADO.NET 2.0 provide us a new feature named SqlBulkCopy which gives you DTS like speed to transfer Excel sheet data into the SQL SERVER table. Here i will show you by an example how you can easily do this. At first open the Excel Sheet & determine which columns you want to transfer then if you have already a table then you need to map columns between Excel & SQL SERVER. Other create a table in your database as like as Excel Sheet. The following image shows you data structure & sample data of both SQL SERVER and Excel Sheet:


Now add an aspx page into your project. Add a button to run the import command. Let the button name is cmdImport. Now under button click event write the following code:
Don't forget to import the following two namespaces:
using System.Data.SqlClient;
using System.Data.OleDb;

protected void cmdImport_Click(object sender, EventArgs e)
{
string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("Select [MSISDN],[Name],[Company],[Status] FROM [{0}]", "Sheet1$");
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName =
"tblAgent";
//You can mannualy set the column mapping by the following way.
//bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
bulkCopy.WriteToServer(dr);
}
}
}
}

Now run the application & see the performence that how fast your application import all data into the databse. One another thing if you want to try another sheet to import then run the above code by modifying the sheet name from the sql.

Sample Output:


Want to export GridView data to EXCEL then read:
http://shawpnendu.blogspot.com/2009/03/export-gridview-data-to-excelword-text.html

Friday, May 15, 2009

List of Built-in String Functions in SQL Server




Please visit my new Web Site WWW.Codedisplay.com



String manipulation is one of the most important task for each developer of each language. Its not possible to remember all string list cause as a human we have few limitations. Everytime i google different type of built in functions for different languages. If i can write a complete list of string functions with its corrsponding syntax (parameter list with description) then it will help me as well as readers to easily identify the function which will surely accelarate our productivity.

Lets we need to extract various part of a string body, cut a specific portion means substring, need to change upper to lower or lower to upper, replace, stuff or padding, start & end index of a charater set to match then we can easily depend on SQL SERVER built in function list. All built-in string functions are deterministic with the exception of CHARINDEX.

Focus Area:
1. ASCII9. NCHAR17. SPACE
2. CHAR10. REPLACE18. STR
3. CHARINDEX11. QUOTENAME19. STUFF
4. DIFFERENCE12. REPLICATE20. SUBSTRING
5. LEFT13. REVERSE21. UNICODE
6. LEN14. RIGHT22. UPPER
7. LOWER15. RTRIM
8. LTRIM16. SOUNDEX


Use ASCII:
The ASCII() function returns the ASCII code of the first left charater from your given charater or string as the argument.

Signature:
ASCII ( character_expression )
Where character_expression can take a single charater or a set of charater means string or varchar.

Example:
SELECT ASCII('A')
-- Ans. is 65
SELECT ASCII('ASP.NET C#/VB.NET')
-- Ans. also 65
So from example we found conclusion that ASCII() function consider only the first charater from provided string whatever next.

Use CHAR():
The CHAR() function is the opposite of ASCII() function. ASCII() function returns the ASCII code where as CHAR() function convert the ASCII code to its alphanumeric code.

Signature:
CHAR ( integer_expression )
Where integer_expression can take only int value ranges 0 to 255. If the value does not satisfy the range then you will get NULL.

Example:
SELECT CHAR(65)
--Ans. is A

The another important use of CHAR() function is you can add dynamically carriage return, line feed or the query output. Lets i want to display category and category wise list in a new line then the query should be:
PRINT 'ASP.NET Blog List:'+CHAR(13)+'http://shawpnendu.blogspot.com'
-- RETURN
-- ASP.NET Blog List:
-- http://shawpnendu.blogspot.com/

Control charater TAB=CHAR(9), Line Feed=CHAR(10) & Carriage Return=CHAR(13).

Use CHARINDEX():
Returns the starting position of a character string. CHARINDEX() function is widely used to findout a sequence of string or the sequence occurence.

Signature:
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Where expression1 is for your sequence or a charater that you are looking for. The expression2 is the column name of your database where you want to search. And the third one is optional. Here you can notify the SQL Server from which location the server starts searching to find your sequence from your provided column. If you does not specify the location then the search starts from beginning of the search column.

Example:
-- Let we have the following records:
-- IMSI=470010210020903;
-- MSISDN=8801723500902;
-- CURRENTNAM=BOTH;
-- CAT=COMMON;
-- ODBROAM=ODBOH;
-- ODBPLMN=PLMN1;
-- In the BatchData column of HLR table
-- Our query is to find out ALL MSISDN Containg rows

SELECT BatchData FROM HLR WHERE CHARINDEX('MSISDN',BatchData)>-1

-- Output will be MSISDN=8801723500902

Now we have change our requirement. We need to select all MSISDN. So the query should be:


SELECT SUBSTRING(BatchData,CHARINDEX('=',BatchData)+1,
LEN(BatchData)-CHARINDEX('=',BatchData)) [MSISDN] FROM HLR
WHERE CHARINDEX('MSISDN',BatchData)>-1

-- Output is: 8801723500902
About SUBSTRING() function i will describe later in this post.

Use DIFFERENCE():
Returns the difference between two character expressions or string based on sound recognition logic. DIFFERENCE() function return 0-4 based on matching. Where 0 means not matched & 4 means strongly matched. 3 means less sronger than 4.

Signature:
DIFFERENCE ( character_expression , character_expression )

Example:
SELECT DIFFERENCE('asp','asp')
-- Will return 4

SELECT
DIFFERENCE('asp','php')
-- Will return 2

Use LEFT():
Transact-SQL supports retrieving a portion of a database column or string. For instance, to retrieve or cut the first few characters from the left of the string you can use the LEFT() built in function.

Signature:
LEFT ( character_expression , integer_expression )
First character_expression is your column name or a string & integer_expression is the number that how many left charaters you want.

Example:
SELECT LEFT('.Net Mixer',4)
-- Will return .Net

SELECT
LEFT('.Net Mixer',100)
-- Will return .Net Mixer
-- Means if you provide more than total length
-- Then SQL Server returns Full String

SELECT
LEFT('.Net Mixer',0)
-- Will return Blank

SELECT
LEFT('.Net Mixer',-1)
-- Will return the error
-- 'Invalid length parameter passed to the left function.'
-- Negetive value will return error.

Use RIGHT():
Same as LEFT() function except reurns the right portion instead of left.

Signature:
RIGHT ( character_expression , integer_expression )
First character_expression is your column name or a string & integer_expression is the number that how many right charaters you want.

Example:
SELECT RIGHT('.Net Mixer',4)
-- Will return ixer

SELECT
RIGHT('.Net Mixer',100)
-- Will return .Net Mixer
-- Means if you provide more than total length
-- Then SQL Server returns Full String

SELECT
RIGHT('.Net Mixer',0)
-- Will return Blank

SELECT
RIGHT('.Net Mixer',-1)
-- Will return the error
-- 'Invalid length parameter passed to the right function.'
-- Negetive value will return error.

Use LEN():
The LEN() function is a very useful function. It returns the number of characters in a string.

Signature:
LEN ( string_expression )
The string_expression is either your database column name or a string.

Example:
SELECT LEN('.Net Mixer')
-- Will return 10

Use LOWER() & UPPER():
Convert the database column value or a string to lowercase or small letter. And UPPER() Function will convert to Capital Letter

Signature:
LOWER ( character_expression ) OR UPPER ( character_expression )
The character_expression may be your databse column or a fixed string.

Example:
SELECT LOWER('.Net Mixer')
-- Will return .net mixer

SELECT
UPPER('.Net Mixer')
-- Will return .NET MIXER

Another example is let you have a reuirement like show all article title where the first charater will be the capital letter or upper case & rest of all are small letter or lower case. Then the query should be:

SELECT UPPER(LEFT(Title,1))+LOWER(RIGHT(Title,LEN(Title)-1)) FROM Articles
-- Dtepart, dateadd, datediff sql server standard datetime function
-- For each category or group select first n rows, top n rows, last n rows, bottom n rows, random data

Use LTRIM() & RTRIM():
You can use LTRIM() function to remove all leading blank charater from your column & can remove all trailing blank spaces by RTRIM() SQL Server built in function. One thing keep in mind that the above specified LEFT() & RIGHT() function never trim data. Hope now you can understand the difference between LTRIM() & LEFT() as well as RTRIM() & RIGHT().

Signature:
LTRIM ( character_expression )
RTRIM ( character_expression )
From the above syntax character_expression either a column name or a string

Example:
SELECT LTRIM(' .Net Mixer')
-- Will return .Net Mixer

SELECT
RTRIM(' .Net Mixer ')
-- Will return .Net Mixer

SELECT
LTRIM(RTRIM(' .Net Mixer '))
-- Will return .Net Mixer

Use NCHAR() & UNICODE():
The NCHAR() function exactly works like CHAR() function except return type. NCHAR() function return Unicode character. The another difference is in value range where NCHAR() argument range is 0 to 65535. You find this function is useful if you're working with large international character sets.

UNICODE() function exactly works just like ASCII() function, except it accepts the Unicode character value as input. This could be useful if you're working with international character sets.

Signature:
NCHAR ( integer_expression )
From the above syntax integer_expression can take only positive integer from 0 through 65535. If a value outside this range is specified, NULL is returned.

UNICODE ( 'ncharacter_expression' )
From the above syntax 'ncharacter_expression' is an nchar or nvarchar expression. One thing note that UNICODE() function always consider the first charater if you supplied a string.

Example:
DECLARE @nstring nchar(8)
SET @nstring = N'København'
SELECT UNICODE(SUBSTRING(@nstring, 2, 1)),
NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1)))

-- Output is 248,ø respectively

Use REPLACE():
The REPLACE() function replaces some characters or sub string within a string with another string or set of characters.

Signature:
REPLACE (string_expression1 , string_expression2 , string_expression3 )
Where expression1 is the column name or a string where you want to replace something. Expression2 is the sub string or a set of character you want to replace from expression1. And finally expression3 is the replacing value.

Example:
SELECT REPLACE(Title, 'Signature', 'Syntax') AS Title
FROM Articles WHERE Approved=1

-- Table has: DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function Signature
-- The output: DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function Syntax
-- Means Signature replaced by Syntax word.

Use QUOTENAME():
The QUOTENAME() functions is used to add square bracket or third bracket to the provided column or a string. It will be helpful when you want to create a dynamic query. Because you knew that we used square quote for thos columns which contains spaces or keyword. Let you have to work with a partner where they put all table names & corresponding column into a table. Your task is to create SQL for insert/delete/select. At this stage the QUOTENAME will work fine.

Signature:
QUOTENAME(string_expression)
Where string_expression is a column name or a string.

Example:
SELECT QUOTENAME(tblName) FROM tblList

--Input: student list
--Output: [student list]

Use REPLICATE():
The REPLICATE() function repeats a given string according to a specified occurance.

Signature:
REPLICATE(string_expression, integer_expression)
Where string_expression is your column name or a string & integer_expression is the number of occurance required.

Example:
SELECT REPLICATE('.Net Mixer',3)

--Output: .Net Mixer.Net Mixer.Net Mixer

Use REVERSE():
The REVERSE() function returns you reverse output or a mirror output for a given column or a string.

Signature:
REVERSE ( character_expression )
Where character_expression may be a column or a set of charater or string.

Example:
SELECT REVERSE('Asp.Net')

--Output: teN.psA

Use SOUNDEX():
The SOUNDEX() function returns a fixed 4 charater alphanumeric code.

Signature:
SOUNDEX ( character_expression )
Where character_expression will be a column or string.

Example:
SELECT SOUNDEX('Asp.Net'),SOUNDEX('Asp.Net')
--Output: A210,A210 means both are similar

SELECT
SOUNDEX('ASP'),SOUNDEX('AJAX')
--Output: A210,A220
See also DIFFERENCE() function described above.

Use SPACE():
The SPACE() function is almost similar to REPLICATE() function except here you can use space only instead of a substring.

Signature:
SPACE ( integer_expression )
Where integer_expression is any positive number.

Example:
SELECT 'Asp.Net'+SPACE(0)+'Tutorial'
--Output: Asp.NetTutorial

SELECT
'Asp.Net'+SPACE(1)+'Tutorial'
--Output: Asp.Net Tutorial

Use STR():
The STR() function just convert a numeric value to string (Round up works ). You can specify the decimal length as well as total length thats why this is always imprtant importatnt to us for reporting purposes.

Signature:
STR ( float_expression [ , length [ , decimal ] ] )
Where first argument takes a numeric value, 2nd argument is the total length & third one is the decimal places.

Example:
--Let you have few sample data
SELECT STR(1234.456789, 5, 0)
--: 1234 Pad one space at first

SELECT
STR(1234.456789, 7, 2)
--:1234.46 (Round-up)

SELECT
STR(1234.456789, 6, 2)
--:1234.5 (Since length was 6)

SELECT
STR(1234.456789, 3, 2)
--:*** (Since 3 is the total length where as integer value length=4)

So note that
1. If the provided number is less than the specified length then leading spaces will be added.
2. If the result is greater than the specified length then fractional part will be truncated with roud-up
3. If provided length is less than integer part value you will get number of asterisk equal to provided length.

Use STUFF():
The STUFF() function delete or remove a specified length & add or insert or stuff a string into that position.

Signature:
STUFF ( character_expression , start , length , character_expression )
Where character_expression is a column or a string, start means from which position you want to delete on the other hand stuff, length means how many character you want to remove from start position & character_expression is a set of charater or a charater that you want to stuff.

Example:
SELECT STUFF('ASP.NET',4,1,'dot')

-- Here start position 4 means start from .
-- Length=1 means remove one charater from osition 4
-- 'dot' means stuff dot in to the position 4 whatever its length

Use SUBSTRING():
The most popular function. The SUBSTRING() built in function extract or retrieves or return or cut a portion of the column/string.

Signature:
SUBSTRING ( expression , start , length )
Where expression is column or a string. Start means start position from where you want to cut & length means how many charaters you want to cut.

Example:
SELECT SUBSTRING('Asp.Net',4,4)
--Output: .Net

SELECT
SUBSTRING('Asp.Net',4,100)
--Output: Same as above

SELECT
SUBSTRING('Asp.Net',-100,2)
--Output is blank

SELECT SUBSTRING('Asp.Net',100,-2)
--Output: ERROR: Invalid length parameter passed to the substring function

Ref:
http://msdn.microsoft.com/en-us/library/aa258891(SQL.80).aspx

Thursday, May 14, 2009

DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function




Please visit my new Web Site WWW.Codedisplay.com



SQL SERVER Transact-SQL provide us a set of functions to retrieve the current date and time or the parts of a DATETIME or SMALLDATETIME value. Say, you can extract the day, month or year from a datetime value, as well as the quarter, week, hour or even the millisecond. In this article, I will describe each of these functions with appropriate syntax and provide the best practical examples like (time difference between two dates or from same date. Day, month, year difference, age calculation) that demonstrate how to use these functions to retrieve datetime data from SQL Server using query. Before start i want to write a simple T-SQL query which return the current datetime based on server clock settings. So to get the server current date time run the below query:

For DATETIME formatting issues you can visit this link.

SELECT GETDATE() [NOW]

SQL Server Date Functions:
1. DATEPART()
2. DATEADD()
3. DATEDIFF()
4. DATENAME()
5. DAY()
6. MONTH()
7. YEAR()
8. CONVERT()
9. CAST()


Use DATEPART():
Returns an integer that represents the specified datepart of the specified date. Such as day, month, year, hour, minute etc.

Signature:
DATEPART(datepart,date)

Where date is a valid date expression and the following table lists all valid datepart arguments:

datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

Example:
Let we have a table like below:

IDNAMEDOB
00000001Shawpnendu Bikash Maloroy1979-01-01 10:05

Now we want day, month, year & quarter value from DOB column. To achieve this we have to write the following sql by using SQL Server builtin function DATEPART():

SELECT DATEPART(yyyy,DOB) [Year], DATEPART(mm,DOB) [Month], DATEPART(dd,DOB) [Day], DATEPART(dayofyear,DOB) [Day of Year] FROM Developers

And the ouput will be:
YearMonthDayDay of Year
1979111

Use DATEADD():
The DATEADD() function is used to add or subtract a specified time interval from a given date.

Signature:
DATEADD(datepart,number,date)

Where datepart is datepart (see above), number is the interval which specified how many day or month or year you want to add or subtract based on third parameter date.

Example:
Let from today we want to add 65 days or back to 65 days then we can use the below SQL:

SELECT DATEADD(day,65,GETDATE()) [Future], DATEADD(day,-65,GETDATE()) [Past]

Use DATEDIFF():
The DATEDIFF() function is used to returns the time between two given dates.

Signature:
DATEDIFF(datepart,startdate,enddate)

For datepart see above, based on datepart DATEDIFF() function return day or time or quarter between two given date.

Example:
In the below example i will show how you can get hour, day, month, year difference between 2 given dates:

SELECT
DATEDIFF(hh,GETDATE(),GETDATE()+65) [Hour Gap],
DATEDIFF(day,GETDATE(),GETDATE()+65) [Day Gap],
DATEDIFF(month,GETDATE(),GETDATE()+65) [Month Gap],
DATEDIFF(year,GETDATE(),GETDATE()+65) [Year Gap]

Here i want to discuss a bit more on SQL Time Difference because most of the forum i found the question how we can get time difference in SQL for the same date.

Query to get difference in Minute, Query to get difference in Second, Query to get difference in Mili Second:

SELECT DATEDIFF(mi, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') Minute
SELECT DATEDIFF(ss, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') Second
SELECT DATEDIFF(ms, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') MiliSecond

Another interesting example is age calculation. Using DATEDIFF() built in function you can easily calculate personels age up to current date or up to any other date from stored Date of birth data. Here is an example how you can calculate:

-- Change '1979-01-01' by your age column name
-- Change GETDATE() if you don't want to consider system date
SELECT DATEDIFF(year,'1979-01-01',GETDATE()) [Age]

Use DATENAME():
Returns a character string that represents the specified datepart of the specified date. The datepart weekday played an interesting role. By using DATENAME() function you will get all textual week days name like Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday and Friday.

Example:

SELECT DATENAME(weekday, GETDATE())-- RETURN SATURDAY, SUNDAY, MONDAY ...
SELECT DATEPART(weekday, GETDATE())-- RETURN 1, 2, 3.........

Another nice example is to get the textual Month Name from a given date. For diiferent type of Month Name formatting visit my another link which is stated in CONVERT() method section at the bottom of the post.

SELECT DATENAME(month,GETDATE()) [MONTH NAME]
SELECT DATEPART(month,GETDATE()) [MONTH NO]

Use DAY(), MONTH(), YEAR():

DAY() builtin SQL function return the day part from a given date. MONTH() builtin SQL function return no of month from a given date & YEAR() built in SQL function used to get only the year.

Example:

SELECT DAY(GETDATE()) [Day], MONTH(GETDATE()) [Month], YEAR(GETDATE()) [Year]

Use CONVERT():
CONVERT() function will be used to format a date. For more details CLICK HERE.


Use CAST():
CAST() function is used for data type conversion. Lets you want to change data type of string or varchar to another datatype like numeric, int, decimal then CAST() function is the choice. Also you can use CONVERT() function to change the data type.

Syntax:
CAST ( expression AS data_type [ (length ) ])

Example:

SELECT CAST('11.57' AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), '11.57')

Ref:
http://msdn.microsoft.com/en-us/library/ms186724.aspx#DateandTimeFunctions

For each Category or Group Select FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows, Random data




Please visit my new Web Site WWW.Codedisplay.com



First of all i want to tell you one truth that SQL SERVER never guranteed physical sequence of data. You can retrieve/obtain data logically. Another one is its a bit difficult for SQL SERVER 2000 but easy for 2005. Initially i tried to start with simple example so that beginner can track the post easily:

Focus Area:
1. TOP N
2. Bottom N
3. Random Records
4. Group or Category wise


To follow the below examples it will be better to create two tables & put relevant datas into those tables. Then you can directly run the below example queries by copying from this page. Table structure & few data for rest of the examples is as follows:



SELECT TOP N:
SELECT TOP 3 * FROM Product ORDER BY Name

Specify your required no of record after TOP you will get specified no of record from Product table ordered by Name. The ORDER BY Clause is used to SORT data. Bydefault its returned Ascending data.

Let a bit complex that you have a requirement that find first 3 active products. So the query will be:
SELECT TOP 3 * FROM Product WHERE [Continue]=1

SELECT LAST or BOTTOM N:
There is no built in function to get LAST or BOTTOM n no of data. But we can achieve by TOP & ORDER BY Clause combination. Let now you have a requirement like Find Last 3 products from product table. So the query will be:

SELECT TOP 3 * FROM Product ORDER BY Name DESC

Now a bit complex let you have a requirement that find Last 3 products from product table those name prefix is 'L'. So the query will be:

SELECT TOP 3 * FROM Product WHERE SUBSTRING(Name,1,1)='L' ORDER BY Name DESC


Getting Random Records:


SELECT TOP 2 * FROM Product ORDER BY NEWID()

Now run the above sql to get random records. Run twice you will get diffierent value.

Getting Group or Category wise N Rows:
Let you have a requirement like: Find all products highest price. SO that in this case Group concept will be applied for Product and Order By Clause will be applied for price to get each product highest price. Look at the below SQL:

SELECT P.Name,V.Price FROM Product P
LEFT JOIN Variant V ON P.ID=V.ProductID
WHERE V.ID IN
(
SELECT TOP 1 ID FROM Variant WHERE ProductID=V.ProductID ORDER BY Price DESC)

The output will be:


Change TOP 1 to N from the above SQL subquery to get N no of highest Price for each product group. The speciality of the above SQL is you can run this query in both SQL SERVER 2000 & SQL SERVER 2005.

SQL SERVER 2005 provided us a method ROW_NUMBER() which we can use to filter group wise value from database:

SELECT P.Name,V.Price FROM Product P
INNER JOIN (
SELECT ROW_NUMBER() OVER(PARTITION BY V.ProductID ORDER BY V.ProductId DESC) AS RowNumber,
ProductID,Price
FROM Variant V)
V ON P.ID=V.ProductID
WHERE V.RowNumber<2

So keep experimenting.
Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash