SQL Convert function - DateTime explored

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY 1 HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM 1
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
DD Mon YY 1	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 1
DD Mon YYYY 1	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 1
Mon DD, YY 1	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 1
Mon DD, YYYY 1	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 1
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM 1
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-01-06
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2006
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	1998/11/23
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)	ODBC Canonical (with milliseconds)	SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM	ISO8601	SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Some basic ASP.Net thoughts

What is State Management?

State management is the process by which you maintain state and page information over multiple requests for the same or different pages.

ASP.NET includes several options that help you preserve data on both a per-page basis and an application-wide basis.

Types of State Management

There are two basic types of State Management:

1.       Client-Based State Management

2.       Server-Based State Management

Client-Based State Management:

Client based state management techniques stores data on the client in various ways. Client based state management techniques are:

A.      View state

B.      Control state

C.      Hidden fields

D.      Cookies

E.       Query strings

View state:

The view state represents the state of the page when it was last processed on the server. It's used to build a call context and retain values across two successive requests for the same page. By default, the state is persisted on the client using a hidden field added to the page and is restored on the server before the page request is processed.

If the amount of data stored in the ViewState() property exceeds the specified value in the MaxPageStateFieldLength() property, multiple hidden fields are used to store View state data.

                Advantages:     

a)      Server resources not required.

b)      Simple implementation

c)       automatic retention of page and control state

d)      Enhanced security features. The values in view state are hashed, compressed, and encoded for Unicode implementations.

Disadvantages:

a)      Scope is limited to only single page.

b)      Performance. The view state is stored in the page itself, so increase the page size.

c)       Security. The view state is stored in a hidden field on the page. Although view state stores data in a hashed format, it can be tampered with. 

Control state:

If you create a custom control that requires view state to work properly, you should use control state to ensure your control work properly though developers disable view state. The ControlState() property allows you to persist property information that is specific to a control and cannot be turned off like the ViewState() property.

     Advantages:     

a)      Server resources not required.

b)      Reliable. Because control state can not be turned off like ViewState.

Disadvantages:

a)      Some programming is required.

Hidden Fields:

A hidden field acts as a repository for any page-specific information that you want to store directly in the page. ASP.NET allows you to store information in a HiddenField control, which renders as a standard HTML hidden field. A hidden field does not render visibly in the browser.

You must submit the page using an HTTP POST command in order for hidden-field values to be available.

                Advantages:     

a)      Server resources not required.

b)      Simple implementation

c)       Widespread support.

Disadvantages:

a)      Potential security risk.

b)      Performance. The hidden fields are stored in the page itself, so increase the page size.

c)       Does not support rich data types to store.

Cookies:

A cookie is a small amount of data that is stored either in a text file on the client file system or in-memory in the client browser session. Browser sends with cookies values with every page request to the same server. Cookies can be temporary or persistent.

Advantages:     

a)      Server resources not required.

b)      Simple implementation

c)       Configurable expiration rules

d)      Data persistent.

Disadvantages:

e)      Size limitation: Most browsers support maximum 4096 bytes cookies.

f)       User configuration refusal: User can disable cookies in their browser.

g)      Security risk: Can be tempered.  

Query strings:

A query string is information that is appended to the end of a page URL. Query strings provide a simple but limited way to maintain state information.  Some browsers and client devices impose a 2083 character limit on the length of the URL.

You must submit the page using an HTTP POST command in order for query string values to be available.

Advantages:     

h)      Server resources not required.

i)        Simple implementation

j)        Widespread support.

Disadvantages:

k)      Size limitation: Some browser limits 2083 chars on the length of URLs.

l)        Security risk: Information is visible to the user.  

Server-Based State Management:

Server based state management techniques store data in memory on the server.  Server based state management techniques are:

A.      Application state

B.      Session state

C.      Profile Properties

Application state:

Application state provides a method of storing data global to whole application. These data are visible to entire application and shared by all active sessions. That’s why Application state variables are global variables for an ASP.Net application.

Advantages:     

m)    Simple implementation

n)      Application wide scope

Disadvantages:

o)      Application scope in case of Web Garden or Web Firm.

p)      Limited Durability of data.

q)      Requires server memory.

Session state:

Session state provides a method of storing session specific information that is visible only within the session.

Advantages:     

r)       Simple implementation

s)       Session specific events

t)       Data can persists across multiple process

Disadvantages:

u)      Application scope

v)      Limited Durability of data.

w)    Requires server memory

New Features Announced for Silverlight 5

1) Improved media support and rich UI capabilities:

Hardware Decode and presentation of H.264 improve performance for lower-power devices to render high-definition video using GPU support.

TrickPlay allows video to be played at different speeds and supports fast-forward and rewind. At up to twice the speed, audio pitch correction allows users to watch videos while preserving a normal audio pitch.

Improved power awareness prevents the screen saver from being shown while watching video and allows the computer to sleep when video is not active.

Remote-control support allows users to control media playback.

Digital rights management advancements allow seamless switching between DRM media sources.

 

2)Building next-generation business applications:

Silverlight 5 text (bottom) has improved clarity.

Fluid user interface enables smoother animation within the UI. Inter-Layout Transitions allow developers to specify animations to apply when elements are added, removed or re-ordered within a layout. This provides smoother user experiences when, for example, items are inserted into a list.

Text improvements make it possible to build rich magazine-style text layouts:

• Multicolumn text and linked text container allow text to flow around other elements.
• Tracking/leading set precisely how far apart each character is for full creative control.
• Text clarity is improved with Pixel Snapping.
• Text layout performance is significantly improved.
• OpenType support has been enhanced.

Support for Postscript vector printing enables users to create reports and documents, including the ability to create a virtual print view different from what is shown on the screen.

Applications can now work the way users expect with added support for double-click and Combobox type ahead.

Model View ViewModel (MVVM) and Databinding enhancements allow more work to be done more easily via XAML:

• Debugging support now allows breakpoints to be set on a binding, so you can step through binding failures.
• Implicit DataTemplates allow templates to be created across an application to support a particular type by default.
• Ancestor RelativeSource allows, for example, a DataTemplate to bind to a property on the control that contains it.
• Binding in style setters allows bindings to be used within styles to reference other properties.
• The DataContextChanged event is being introduced. Markup extensions allow code to be run at XAML parse time for both properties and event handlers, enabling cutting-edge MVVM support.

3) Networking and Windows Communication Foundation enhancements:

• Reduced network latency by using a background thread for networking.

4) Silverlight 5 performance improvements include:

• Reduced network latency by using a background thread for networking.
• XAML parser improvements that speed up startup and runtime performance.
• Support for 64-bit operating systems.

 

5) Graphics improvements:

• Graphics Processing Unit (GPU) accelerated 3-D application programming interface (API) provides rich graphics on the Web for building advanced data visualizations and rich user experience (UI).
• Immediate mode graphics API allows direct rendering to the GPU.
• Hardware acceleration is enabled in windowless mode with Internet Explorer 9.

Silverlight 5 extends features of the ‘Trusted Application’ model to the browser for the first time. These features, when enabled via a group policy registry key and an application certificate, mean users won’t need to leave the browser to perform complex tasks:

• Host HTML content as a Web browser control within the Silverlight application. HTML pages, such as help content or e-mail, can be integrated within the application.
• Read and write files to the user’s My Documents folder, making it easier to find media files or create local copies of reports.
• Launch Microsoft Office and other desktop programs. Users can open Microsoft Outlook and create an e-mail message, or send a report to Word utilizing the power of Office.
• Access devices and other system capabilities by calling into application COM components. Users can access a USB security card reader or a bar-code scanner.
• Enjoy full keyboard support in full screen, which enables richer kiosk and media viewing applications.
• Call existing unmanaged code directly from within Silverlight with PInvoke

SQL Script for getting last day of the month

Created a sample script for showing last day of the month..

SELECT     (CASE   MONTH(GETDATE())
                WHEN 1 THEN 31
                WHEN 2 THEN (CASE YEAR(GETDATE())%4 WHEN 0 THEN 29 ELSE 28 END)
                WHEN 3 THEN 31
                WHEN 4 THEN 30
                WHEN 5 THEN 31
                WHEN 6 THEN 30
                WHEN 7 THEN 31
                WHEN 8 THEN 31
                WHEN 9 THEN 30
                WHEN 10 THEN 31
                WHEN 11 THEN 30
                WHEN 12 THEN 31
        END) AS LastDayOfMonth

 

Firefox addon for View state size of ASP.Net page

ViewstateSize is a Firefox add-on which displays the size of the viewstate on the current asp.net page you are seeing.

Details about this add-on is available in the following link

https://addons.mozilla.org/en-US/firefox/addon/5956/

COM Interop with silverlight 4.0

Silverlight 4 has a feature for accessing COM components which is installed /available in client machines.
I will walk through how to access a Excel application through silvelight .
For developing this application , we need VS 2010 , Silverlight 4 SDK.
Create a silverlight project using VS2010

Take the properties of SL project under silverlight tab for enabling Out Of Browser support.

We need to check ‘require elevated permission’ under Out of browser settings button.
Now am creating a datagrid(dgName) in MainPage.xaml and binding some data.
below code is used for setting datasource to datagrid.
private void UserControl_Loaded(object sender, RoutedEventArgs e)
       {
           ModelClass m = new ModelClass();
           dgName.ItemsSource = m.SetDataSource();
       }

preview : data grid.
Now i am putting a button which invokes ‘excel’ application and write all the data from the above data grid to the excel app.
private void Button_Click(object sender, RoutedEventArgs e)
       {
           dynamic excelObj = AutomationFactory.CreateObject("Excel.Application");
           excelObj.Visible = true;
           // add a workbook to the instance  
           dynamic workbook = excelObj.workbooks;
           workbook.Add();
           dynamic sheet = excelObj.ActiveSheet;
           sheet.Name = "Sample Sheet";
           // get the active sheet  
           dynamic cell = null; int i = 1;
           // iterate through our data source and populate the excel spreadsheet 
           foreach (ModelClass item in dgName.ItemsSource)
           {
               cell = sheet.Cells[i, 1]; // row, column   
               cell.Value = item.ID;
               cell.ColumnWidth = 25;
               cell = sheet.Cells[i, 2];
               cell.Value = item.Name;
               i++;
           }
       }
we are using AutomationFactory ( static class under System.Windows.Interop) for invoking Excel app . we can use this class for invoking apps like ‘Outlook’ , ‘Text to Speech’ COMs’.
‘dynamic’ is object whose operations will be resolved at run time.Intellisence will not be available while coding using ‘dynamic’ object.

I am attaching a sample application , can be download from here

LiveJournal Tags: Silverlight 4,Silverlight COM,AutomationFactory

Bing Map integration with ASP.Net

Describes a sample application for showing your location using bing map api.
Step 1 .
Create an ASP.Net web site.
Step 2.
Include <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script> in header part of aspx page.
Include these functions for initializing and locating bing map
<script type="text/javascript">
      var map;       
      function LoadMap() {
          map = new VEMap('myMap');
          map.LoadMap();
      }
      function StartGeocoding(address) {
          map.Find(null,    // what
                      address, // where
                      null,    // VEFindType (always VEFindType.Businesses)
                      null,    // VEShapeLayer (base by default)
                      null,    // start index for results (0 by default)
                      1,    // max number of results (default is 10)
                      null,    // show results? (default is true)
                      null,    // create pushpin for what results? (ignored since what is null)
                      null,    // use default disambiguation? (default is true)
                      null,    // set best map view? (default is true)
                      GeocodeCallback);  // call back function
      }
      function GeocodeCallback(shapeLayer, findResults, places, moreResults, errorMsg) {
          // if there are no results, display any error message and return
          if (places == null) {
              //alert((errorMsg == null) ? "There were no results" : errorMsg);
              return;
          }
          var bestPlace = places[0];
          // Add pushpin to the *best* place
          var location = bestPlace.LatLong;
          var newShape = new VEShape(VEShapeType.Pushpin, location);
          //var desc = "Latitude: " + location.Latitude + "<br>Longitude:" + location.Longitude;
          newShape.SetTitle(bestPlace.Name);
          map.AddShape(newShape);
      }
      function locate()
      {
        var obj= document.getElementById('txtAddress');
        if(obj !=null)
        {
        LoadMap();
        StartGeocoding(obj.value);
        }
      }
</script>
Step 3 .
Create a div area for displaying map control.
add a text box for accepting address and a button for invoking locate() JS function included in the page.


Sample application can be downloaded from here

LiveJournal Tags: Bing Map API Integration with ASP.Net,ASP.Net