Saturday, January 1, 2011

Exception handling and logging

Exception - Is an unusual occurance in the logic of the application, eg: divide by zero, reading corrupted file.. etc.
Exception Handling - Managing these exceptions in order to make sure that it does not stop application from running and not distirbing the end user
Exception Handling Methods
1. Structured Exception Handling (try-catch-finally-throw)
eg:
try
{
throw new System.IO.FileNotFoundException;
}
catch(System.IO.FileNotFoundException)
{}
catch(System.UnauthorizedAccessException)
{}
catch(Exception ex)
{}
finally
{}

2. Using Error Events
Error events can be used against exception that were not foreseen or handled elsewhere.
Page_Error - Page level
Global_Error - Global.asax
Application_Error - Global.asax
To get the last error occured in the application use Server.GeLastError() and to clear the excpetions use Server.ClearError()
GetLastError() can be used only before redirecting to a new page.

3. Using Error Pages
This is a high level way of handling exceptions especially for application wide errors which are outside the scope of code. For eg: to handle IIS errors like 404 Page Not Found

There are 3 ways of using it:
a) Specify Error Pages corresponding to Error Code in IIS itself. Exceptions registered with IIS can be seen from custom errors section of application properties.
If we are redeploying the application in IIS, we have to redo these settings all over again.

b) Specify error pages in CustomErrors tag in web.config
<customerrors defaultredirect="1.aspx" mode="on">
<error redirect="Error.aspx" statuscode="404">
</customerrors>
mode ="on" - To view error pages while running locally
mode="RemoteOnly" - To view error pages when ru
nning from client
Error page type should be supportable by asp.net

c) Specify Error page in ErrorPage attribute of Page

Because of redirection, context for error is lost, Server.GetLastError() returns nothing

Tracing
Tracing is used for logging exceptions
How to enable tracing in .net...

This will write the trace at the end of each file.
To write the trace in to a file;
pageOutput="false"
requestLimit="20" - This will restrict the tracing for the first 20 requests
To enable tracing at the page level - trace="true"

Thursday, November 25, 2010

Stored Procedure to move Comma Separated values to a SQL Table

It is very common to use comma seperated values, especially with CSV files and text files. At times we may have to extract these values and put it in a table. Here I am showing you the SQL script which can be used to achieve this. You can create a stored procedure or function to achieve this
The below set of queries shows how we can seperate comma seperated values and put in to a temp table:

declare @strlength int
declare @temp int
declare @start int
declare @end int
declare @string nvarchar(500)
set @string='B31AA,B31AB,B31AA,B31AB,B31AC,B31AD,B31AE,B31AF'
set @strlength=(select len(@string))

set @temp=0
set @start=0
set @end=1
create table #Corelines(subgroup nvarchar(10))
while @temp<=@strlength-1
begin
if substring(@string,@temp,1)=',' or @temp=@strlength-1
begin
if @temp=@strlength-1
insert #Corelines select substring(@string,@start,(@strlength-@start)+1)
else
insert #Corelines select substring(@string,@start,@end-1)
set @start=@temp+1
set @end=0
end
set @temp=@temp+1
set @end=@end+1
end
select subgroup from #Corelines
drop table #Corelines

Sunday, October 11, 2009

SQL Query - Different Date Time Formats in SQL Server

SQL Server 2016 - How To Detect The Current Cumulative Update/Service Pack That Was Installed


  1. select SERVERPROPERTY('ProductLevel') as 'Product Level'
  2. select SERVERPROPERTY('ProductUpdateLevel') as 'ProductUpdate Level'

Get Number of Mon/Tue/Wed..Fri between two dates
declare @from datetime= '3/1/2013' 
declare @to datetime  = '3/31/2013' 


select 
 datediff(day, -7, @to)/7-datediff(day, -6, @from)/7 AS MON,
 datediff(day, -6, @to)/7-datediff(day, -5, @from)/7 AS TUE,
 datediff(day, -5, @to)/7-datediff(day, -4, @from)/7 AS WED,
 datediff(day, -4, @to)/7-datediff(day, -3, @from)/7 AS THU,
 datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 AS FRI,
 datediff(day, -2, @to)/7-datediff(day, -1, @from)/7 AS SAT,
 datediff(day, -1, @to)/7-datediff(day, 0, @from)/7 AS SUN
-------------------------------------------------------------------
If you have trouble converting a date column in SQL Server to any of the desired format, here you go..

I am listing all the major formats below and its corresponding queries

Format Query
mm/dd/yy - USA Format select convert(varchar, getdate(), 1)
yy.mm.dd - ANSI Format select convert(varchar, getdate(), 2)
dd/mm/yy - British/FrenchFormat select convert(varchar, getdate(), 3)
dd.mm.yy - German Format select convert(varchar, getdate(), 4)
dd-mm-yy - ItalianFormat select convert(varchar, getdate(), 5)
dd mon yy select convert(varchar, getdate(), 6)
Mon dd, yy select convert(varchar, getdate(), 7)
mm-dd-yy - USA Format select convert(varchar, getdate(), 10)
yy/mm/dd - JAPAN Format select convert(varchar, getdate(), 11)
yymmdd - ISO select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM) select convert(varchar, getdate(), 100)
mm/dd/yyyy select convert(varchar, getdate(), 101)
yyyy.mm.dd select convert(varchar, getdate(), 102)
dd/mm/yyyy select convert(varchar, getdate(), 103)
dd.mm.yyyy select convert(varchar, getdate(), 104)
dd-mm-yyyy select convert(varchar, getdate(), 105)
dd mon yyyy select convert(varchar, getdate(), 106)
Mon dd, yyyy select convert(varchar, getdate(), 107)
hh:mm:ss select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) select convert(varchar, getdate(), 109)
mm-dd-yyyy select convert(varchar, getdate(), 110)
yyyy/mm/dd select convert(varchar, getdate(), 111)
yyyymmdd select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h) select convert(varchar, getdate(), 114)

Thursday, October 23, 2008

DateTime formats in .Net

Add to Google

DateTime formats available in .Net

dd - Day number

MM - Month number

yyyy - Year

hh - Hour

mm - Minute

ss - Second

tt - AM/PM

dddd - Day name

MMMM - Month name

How do we use it?

Suppose you want to display today's date in dddd, MMMM dd, yyyy hh:mm tt format;
Response.Write(DateTime.Now.ToString("dddd, MMMM dd, yyyy hh:mm tt"));
The output will be as follows:
Thursday, December 11, 2008 05:30 PM

There are 133 different types of DateTime formats available in .net. Execute the code below to list all the formats for current datetime


string[] DateTimeFormats = DateTime.Now.GetDateTimeFormats();
GridView1.DataSource = DateTimeFormats;
DataBind();


10/9/2009
10/9/09
10/09/09
10/09/2009
09/10/09
2009-10-09
09-Oct-09
Friday, October 09, 2009
October 09, 2009
Friday, 09 October, 2009
09 October, 2009
Friday, October 09, 2009 7:54 PM
Friday, October 09, 2009 07:54 PM
Friday, October 09, 2009 19:54
Friday, October 09, 2009 19:54
October 09, 2009 7:54 PM
October 09, 2009 07:54 PM
October 09, 2009 19:54
October 09, 2009 19:54
Friday, 09 October, 2009 7:54 PM
Friday, 09 October, 2009 07:54 PM
Friday, 09 October, 2009 19:54
Friday, 09 October, 2009 19:54
09 October, 2009 7:54 PM
09 October, 2009 07:54 PM
09 October, 2009 19:54
09 October, 2009 19:54
Friday, October 09, 2009 19:54:25
Friday, October 09, 2009 7:54:25 PM
Friday, October 09, 2009 07:54:25 PM
Friday, October 09, 2009 19:54:25
October 09, 2009 19:54:25
October 09, 2009 7:54:25 PM
October 09, 2009 07:54:25 PM
October 09, 2009 19:54:25
Friday, 09 October, 2009 19:54:25
Friday, 09 October, 2009 7:54:25 PM
Friday, 09 October, 2009 07:54:25 PM
Friday, 09 October, 2009 19:54:25
09 October, 2009 19:54:25
09 October, 2009 7:54:25 PM
09 October, 2009 07:54:25 PM
09 October, 2009 19:54:25
10/9/2009 7:54 PM
10/9/2009 07:54 PM
10/9/2009 19:54
10/9/2009 19:54
10/9/09 7:54 PM
10/9/09 07:54 PM
10/9/09 19:54
10/9/09 19:54
10/09/09 7:54 PM
10/09/09 07:54 PM
10/09/09 19:54
10/09/09 19:54
10/09/2009 7:54 PM
10/09/2009 07:54 PM
10/09/2009 19:54
10/09/2009 19:54
09/10/09 7:54 PM
09/10/09 07:54 PM
09/10/09 19:54
09/10/09 19:54
2009-10-09 7:54 PM
2009-10-09 07:54 PM
2009-10-09 19:54
2009-10-09 19:54
09-Oct-09 7:54 PM
09-Oct-09 07:54 PM
09-Oct-09 19:54
09-Oct-09 19:54
10/9/2009 19:54:25
10/9/2009 7:54:25 PM
10/9/2009 07:54:25 PM
10/9/2009 19:54:25
10/9/09 19:54:25
10/9/09 7:54:25 PM
10/9/09 07:54:25 PM
10/9/09 19:54:25
10/09/09 19:54:25
10/09/09 7:54:25 PM
10/09/09 07:54:25 PM
10/09/09 19:54:25
10/09/2009 19:54:25
10/09/2009 7:54:25 PM
10/09/2009 07:54:25 PM
10/09/2009 19:54:25
09/10/09 19:54:25
09/10/09 7:54:25 PM
09/10/09 07:54:25 PM
09/10/09 19:54:25
2009-10-09 19:54:25
2009-10-09 7:54:25 PM
2009-10-09 07:54:25 PM
2009-10-09 19:54:25
09-Oct-09 19:54:25
09-Oct-09 7:54:25 PM
09-Oct-09 07:54:25 PM
09-Oct-09 19:54:25
October 09
October 09
2009-10-09T19:54:25.2187500+05:30
2009-10-09T19:54:25.2187500+05:30
Fri, 09 Oct 2009 19:54:25 GMT
Fri, 09 Oct 2009 19:54:25 GMT
2009-10-09T19:54:25
7:54 PM
07:54 PM
19:54
19:54
19:54:25
7:54:25 PM
07:54:25 PM
19:54:25
2009-10-09 19:54:25Z
Friday, October 09, 2009 14:24:25
Friday, October 09, 2009 2:24:25 PM
Friday, October 09, 2009 02:24:25 PM
Friday, October 09, 2009 14:24:25
October 09, 2009 14:24:25
October 09, 2009 2:24:25 PM
October 09, 2009 02:24:25 PM
October 09, 2009 14:24:25
Friday, 09 October, 2009 14:24:25
Friday, 09 October, 2009 2:24:25 PM
Friday, 09 October, 2009 02:24:25 PM
Friday, 09 October, 2009 14:24:25
09 October, 2009 14:24:25
09 October, 2009 2:24:25 PM
09 October, 2009 02:24:25 PM
09 October, 2009 14:24:25
October, 2009
October, 2009

Thank you