SQL Server 2008 Character string data types and functions

• String Data Types
• ->char
• ->varchar
• ->text 
• ->nchar
• ->nvarchar
• ->ntex

• String Functions
• -> ASCII 
• -> CHAR 
• -> NCHAR 
• -> UNICODE
• -> CHARINDEX
• -> PATINDEX
• http://geekswithblogs.net/Vipin/archive/2010/03/25/find-the-occurrence-of-wordcharacter-in-sql-column-with-wildcard.aspx
• -> SOUNDEX
• -> DIFFERENCE
• -> FORMAT
• -> LEFT
• -> RIGHT
• -> LEN
• -> LOWER
• -> UPPER
• -> LTRIM
• -> RTRIM
• -> STR
• -> QUOTENAME
• -> REPLICATE
• -> SPACE
• -> REPLACE
• -> REVERSE
• -> STUFF
• -> CONCAT
• -> SUBSTRING

SQL Server 2005 to 2008
• Transparent Data Encryption. The ability to encrypt an entire database.
• Backup Encryption. Executed at backup time to prevent tampering.
• External Key Management. Storing Keys separate from the data.
• Auditing. Monitoring of data access.
• Data Compression. Fact Table size reduction and improved performance.
• Resource Governor. Restrict users or groups from consuming high levels or resources.
• Hot Plug CPU. Add CPUs on the fly.
• Performance Studio. Collection of performance monitoring tools.
• Installation improvements. Disk images and service pack uninstall options.
• Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
• Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
• LINQ. Development query language for access multiple types of data such as SQL and XML.
• Data Synchronizing. Development of frequently disconnected applications.
• Large UDT. No size restriction on UDT.
• Dates and Times. New data types: Date, Time, Date Time Offset.
• File Stream. New data type VarBinary(Max) FileStream for managing binary data.
• Table Value Parameters. The ability to pass an entire table to a stored procedure.
• Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
• Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
• SQL Server Integration Service. Improved multiprocessor support and faster lookups.
• MERGE. TSQL command combining Insert, Update, and Delete.
• SQL Server Analysis Server. Stack improvements, faster block computations.
• SQL Server Reporting Server. Improved memory management and better rendering.
• Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.
SQL Server 2008 to SQL Server R2
• Backup compression is available in R2 standard (was only in Enterprise)
• SQL Server 2008 R2 Express Edition database size limit increased to 10 GB (from 4GB)
• PowerPivot for SharePoint introduced for loading, querying, and managing PowerPivot workbooks that you publish to a SharePoint 2010 server
• PowerPivot for Excel is used to assemble and create relationships in large amounts of data from different sources, and then use that data as the basis for PivotTables and other data visualization objects that support data analysis in Excel.
• Utility Explorer introduced for centralized management of managed SQL instances.
• Master Data Services introduced to manage master (reference) data and maintain an auditable record of that data as it changes over time.
• Enhanced security to prevent an authentication relay attack.
SQL Server 2008R2 to 2012
• Inclusion of Visual Studio 2010 (BI edition)
• AlwaysOn technology – allowing automatic replication and switching to mirrored database/server in the event of problems
• In most instances, the performance is 10 times faster than the predecessor (Microsoft claim)
• Buffer rate is high in SQL Server 2012 because of data compression.
• Data visualization tool is available in SQL Server 2012.This allows snapshots of data.
• Support for persistent computed columns and extra geographical approach is possible with spatial features in SQL Server 2012.
• Allows for warm Service Patch patching – meaning SQL or server does not have to be rebooted/restarted to apply patches
• New ‘Contained Databases’ feature allowing easier movement of database instances between servers
• New ‘ColumnStore’ indexes – which turn indexes 90 degrees in use (instead of one index per row, you get multiple column indexes per row which has a major impact on performance)
• New TRY_CONVERT T-SQL command, which will perform data type conversion, but will NULL rather than error where conversion fails.
• New OFFSET/FETCH command which allows automatic capture of pages of rows (like SELECT TOP 90 * from…, but gives a starting row number for the page)
• Redesign of SQL Server management studio – providing new features, better intellisense, new viewing tools and cleaner interface
• Custom user server roles – meaning you can define roles with specific access (such as BI data reader) and assign these custom roles to defined users (therefore faster user maintenance)
• New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created < ’20100101′;)
-----------------------------------------------------------

SQL2008 has support for additional datatypes:
date
time
geospatial
timestamp with internal timezone

http://www.codeproject.com/Articles/579523/Character-String-Data-Types-and-Functions-SQL-Serv

Javascript Validation

1. http://www.webdevelopmenthelp.net/2013/02/JavaScript-Validating-Input.html

ASP.NET Validation Control

2. http://www.codeproject.com/Articles/49527/How-to-Use-the-ASP-NET-Validation-Control-to-Valid

3. http://aspdotnetcode.blogspot.in/2010/09/aspnet-validation-controls-with-example.html

4. http://www.c-sharpcorner.com/UploadFile/anandn_mvp/aspnetvalidationcontrols06202006021425AM/aspnetvalidationcontrols.aspx

5. http://dotnetprof.blogspot.in/2012/03/validation-controls-in-aspnet.html

6. http://www.tutorialspoint.com/asp.net/asp.net_validators.htm

7. http://www.devmanuals.com/tutorials/ms/aspdotnet/validationsummary.html

// general Dotnet
8. http://dotnetstation.wordpress.com/validation-summary-example-validation-summary-control-in-aspnet/

Calendar Control 

9. http://www.aspdotnet-suresh.com/2012/06/ajax-calendar-extender-control-example.html

Maintain Scroll Position of ASP.NET Panel after postback

<script language="javascript" type="text/javascript">
var IsPostBack= '<%=IsPostBack.ToString() %>';
window.onload = function(){
var strCook = document.cookie;
if(strCook.indexOf("!~")!=0){
var intS = strCook.indexOf("!~");
var intE = strCook.indexOf("~!");
var strPos = strCook.substring(intS+2,intE);
if (IsPostBack=='True')
{
document.getElementById("<%=pnlTree.ClientID %>").scrollTop = strPos;
}
else
{
document.cookie = "yPos=!~0~!";
}
}
}
function SetDivPosition(){
var intY = document.getElementById("<%=pnlTree.ClientID %>").scrollTop;
document.title = intY;
document.cookie = "yPos=!~" + intY + "~!";
}

    </script>

<asp:Panel ID="pnlTree" runat="server" Height="462px" Width="390px" ScrollBars="Auto" onscroll="SetDivPosition()">
….
</asp:Panel>

3 Tier architecture steps

http://geekswithblogs.net/edison/archive/2009/04/05/a-simple-3-tier-layers-application-in-asp.net.aspx

http://www.c-sharpcorner.com/UploadFile/SukeshMarla/3-tier-architecture-using-Asp-Net/

WCF

WCF:

WCF stands for Windows Communication Foundation. It is Microsoft's latest technology that enables applications in a distributed environment to communicate with each other.WCF is Microsoft's unified programming model for building service-oriented applications. It enables developers to build secure, reliable, transacted solutions that integrate across platforms and interoperate with existing.

WCF is an umbrella technology that covers ASMX web services, .NET remoting, WSE, Enterprise Service, and System.Messaging. It is designed to offer a manageable approach to distributed computing, broad interoperability, and direct support for service orientation. WCF supports many styles of distributed application development by providing a layered architecture.
----------------------------------------------------------------------------------------------------------------------------------------------------------

WCF Services - Choosing the appropriate WCF binding

Windows Communication Foundation is a programming model that enables us to develop and then expose our services in a variety of different ways. It means a single WCF service can be exposed with different wire formats and message protocols. Bindings in WCF actually defines that how to communicate with the service. So, binding specifies that what communication protocol as well as encoding method will be used. Optionally, it can specify other important factors like transactions, reliable sessions and security.


Windows Communication Foundation comes with a number of built-in bindings that we can use to expose our service, but WCF is extensible so we can define our own custom bindings to fulfill specific needs.

In this WCF Tutorial, we will keep our focus on learning about existing bindings available in WCF. But instead of explaining those bindings in general, we will try to understand these bindings with respect to some suitable scenarios in which it can be used. For understanding WCF Binding internals, another WCF Binding Tutorial has these details.

Interoperability Scenario:
If we are going to develop a WCF service that will be consumed by non-WCF client applications, then we can expose our service using BasicHttpBinding or WsHttpBinding. So, how these two bindings differ from each other is explain as:
1)    BasicHttpBinding is designed to replace ASMX Web services. It supports both HTTP and Secure HTTP. As far as encoding is concerned, it provides support for Text as well as MTOM encoding methods. BasicHttpBinding doesn’t support WS-* standards like WS-Addressing, WS-Security and WS-ReliableMessaging.

2)    WsHttpBinding also supports interoperability. With this binding, the SOAP message is, by default, encrypted. It supports HTTP and HTTPS. In terms of encoding, it provides support for Text as well as MTOM encoding methods. It supports WS-* standards like WS-Addressing, WS-Security and WS-ReliableMessaging. By default, reliable sessions are disabled because it can cause a bit of performance overhead.

3)    WsDualHttpBinding has all features of WsHttpBinding with addition that it supports Duplex MEP (Message Exchange Pattern). In this MEP, service can communicate with client via callback. Its basically a two way communication.

Note: For further details on Message Exchange Patterns, you can refer my other article “WCF Service -Top 10 Interview Questions” which explains MEPs in details.

4)    WsFederationHttpBinding is a specialized form of WS Binding that offers support for federated security.

Single Computer Scenario:
If our WCF service resides on a single computer, then netNamedPipeBinding will be the best choice.
5)    NetNamedPipeBinding is secure and reliable binding on a single WCF computer across process communication. It provides support for binary encoding which is the best choice in this scenario and uses named pipes as transport for SOAP messages.

Intranet/Cross Computers .NET Communication Scenario:
If we need to communicate across computers with same .NET technology on intranet, then netTcpBinding or netPeerTcpBinding options are available. It’s basically the replacement or enhancement of earlier .NET Remoting technology.
6)    NetTcpBinding supports reliability, transactions and security. It also supports TCP protocol and binary as encoding method. We can say that it’s the most optimized or fastest binding because both client and service are on the same WCF technology.

7)    NetPeerTcpBinding supports features as that of netTcpBinding but it provides secure binding for peer-to-peer environment with WCF Services.

Disconnected Queued Scenario:
8)    NetMsmqBinding is required in a cross machine environment with secure and reliable queued communication. This uses MSMQ as transport.

----------------------------------------------------------------------------------------------------------------------------------------------------------

ABC of Windows Communication Foundation

What are the ABCs of WCF? "ABC" stands for address, binding and contract.

1. Address (Where)

   It specifies the location of the service means, where the service is hosted. The service hosting URL may be like http://server/wcfService. Clients will use this location to communicate with your service.

2. Binding (How)

   It specifies how the client will communicate to the service. We have different protocols (like http,tcp,named pipe,msmq) for the WCF to communicate to the client.

3. Contract (What)

   It specifies what the service will do. For this purpose we have different contract like as Data Contract, Operation Contract, Message Contract, Fault Contract. I will discuss all these later.

WCF Hosting

A WCF service is a component that can be called by other applications. It must be hosted in an environment in order to be discovered and used by others. The WCF host is an application that controls the lifetime of the service. With .NET 3.0 and beyond, there are several ways to host the service.

1. Self hosting

   A WCF service can be self-hosted, which means that the service runs as a standalone application and controls its own lifetime. This is the most flexible and easiest way of hosting a WCF service, but its availability and features are limited.

2. Windows services hosting

   A WCF service can also be hosted as a Windows service. A Windows service is a process managed by the operating system and it is automatically started when Windows is started (if it is configured to do so). However, it lacks some critical features (such as versioning) for WCF services.

3. IIS hosting

   A better way of hosting a WCF service is to use IIS. This is the traditional way of hosting a web service. IIS, by nature, has many useful features, such as process recycling, idle shutdown, process health monitoring, message-based activation, high availability, easy manageability, versioning, and deployment scenarios. All of these features are required for enterprise-level WCF services.

4. Windows Activation Services hosting

   The IIS hosting method, however, comes with several limitations in the service-orientation world; the dependency on HTTP is the main culprit. With IIS hosting, many of WCF's flexible options can't be utilized. This is the reason why Microsoft specifically developed a new method, called Windows Activation Services, to host WCF services.
   Windows Process Activation Service (WAS) is the new process activation mechanism for Windows Server 2008 that is also available on Windows Vista. WAS hosting is possible only with IIS 7.0.Additional WCF components also plug into WAS to provide message-based activation over the other protocols that WCF supports, such as TCP, MSMQ, and named pipes. This allows applications that use the non-HTTP communication protocols to use the IIS features such as process recycling, rapid fail protection, and the common configuration systems that were only available to HTTP-based applications.

   
   

   
   

   Service Contract:-Service Contract is used to define the service name.

   Operation Contract:-Operation contract defines the methods in the services.

   Data Contract:-Data contract defines custom data types in the services.

   
   

   WCF explained with detail the following link 
   Link: http://www.dotnet-tricks.com/Tutorial/wcf/YH3R060312-Introduction-to-WCF.html

   
   

   EBook: http://a2zdotnet.files.wordpress.com/2012/03/step_by_step_wcf.pdf

   
   

   Dotnet question bank with ans: http://www.questpond.blog.com/

   
   

   2013-06-14:

   
   http://www.codeproject.com/Articles/255114/Windows-Communication-Foundation-Basics

   
   

   http://www.aspdotnet-suresh.com/2011/06/introduction-to-wcf-wcf-tutorial-wcf.html

   
   

   http://www.codeproject.com/Articles/431291/WCF-Services-Choosing-the-appropriate-WCF-binding

   
   

   20130712

   
   

   http://tnvbalaji.com/articles/wcf-tutorials/

   
   

   http://nareshkamuni.blogspot.in/2012/04/inserting-and-retrieving-data-from.html
   

   
   

   
   

   2013-06-21
   
   http://tnvbalaji.com/articles/wcf-tutorials/
   
   
   

   The worst 5 mistakes in the web.config file

    

   http://dotnetstories.wordpress.com/2007/10/13/the-worst-5-mistakes-in-the-webconfig-file/
   
   

   Web.Config Security Guidelines

   http://www.iis.net/configreference/system.webserver/security

   http://www.petefreitag.com/item/741.cfm

   
   Configure an IIS-hosted WCF service with SSL
   
   
   
   http://msdn.microsoft.com/en-us/library/hh556232(v=vs.110).aspx
   
   
   
   
   

   <?xml version="1.0"?>

   <configuration>

    <connectionStrings/>

      <system.web>

        <compilation strict="false" explicit="true" targetFramework="4.0" debug="true"/>

      </system.web>

   <system.serviceModel>

   <behaviors>

     <serviceBehaviors>

       <behavior name="">

         <serviceMetadata httpGetEnabled="true"/>

         <serviceDebug includeExceptionDetailInFaults="false"/>

       </behavior>

     </serviceBehaviors>

   </behaviors>

   <bindings>

     <basicHttpBinding>

       <binding name="SNCBinding" closeTimeout="00:10:00" openTimeout="00:10:00"

         receiveTimeout="00:10:00" sendTimeout="00:10:00" maxBufferSize="2147483647"

         maxReceivedMessageSize="2147483647" textEncoding="utf-8">

         <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647"

           maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />

         <security mode="TransportCredentialOnly">

           <transport clientCredentialType="Windows" proxyCredentialType="Windows" />

         </security>

       </binding>

     </basicHttpBinding>

   </bindings>

   <serviceHostingEnvironment aspNetCompatibilityEnabled="true"

     multipleSiteBindingsEnabled="true" />

   <services>

     <service name="SNC.MaterialRequest.WCF.MaterialRequest">

       <endpoint address="" binding="basicHttpBinding" bindingConfiguration="SNCBinding"

         contract="SNC.MaterialRequest.WCF.MaterialRequest" />

     </service>

   </services>

    </system.serviceModel>

    </configuration>

   
   

   
   

   
   

   Buffer size

   
   

   Given below are the settings in web.config for Service

   
   

   
   

    <system.serviceModel>

       <serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>

       <behaviors>

          <serviceBehaviors>

              <behavior name="MyService.MyServiceBehavior">

                  <serviceMetadata httpGetEnabled="true"/>

                  <serviceDebug includeExceptionDetailInFaults="true"/>

                  <dataContractSerializer maxItemsInObjectGraph="2147483647"/>

              </behavior>

          </serviceBehaviors>

       </behaviors>

       <bindings>

          <basicHttpBinding>

              <binding name="BasicHttpBinding_MyService"

                   maxBufferSize="2147483647" maxReceivedMessageSize="2147483647"

                   receiveTimeout="00:40:00" openTimeout="00:40:00"

                   closeTimeout="00:40:00" sendTimeout="00:40:00">

                   <readerQuotas maxDepth="2147483647"

                       maxStringContentLength="2147483647" maxArrayLength="2147483647"

                       maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647"/>

                   <security mode="None"/>

              </binding>

           </basicHttpBinding>

           <customBinding>

              <binding name="MyService.MyService.customBinding0">

                 <binaryMessageEncoding/>

                 <httpTransport/>

               </binding>

           </customBinding>

        </bindings>

        <services>

             <service behaviorConfiguration="MyService.MyServiceBehavior"

                      name="MyService.MyService">

                 <endpoint name="BasicHttpBinding_MyService"

                     address=""

                     binding="basicHttpBinding"

                     bindingConfiguration="BasicHttpBinding_MyService"

                     contract="MyService.IMyService"/>

              </service>

        </services>

   </system.serviceModel>

   
   

   Client:

   
   

   <system.serviceModel>

       <serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>

       <behaviors>

          <serviceBehaviors>

              <behavior name="MyService_Behavior">

                 <serviceDebug includeExceptionDetailInFaults="true"/>

                 <serviceMetadata httpGetEnabled="true"/>

              </behavior>

          </serviceBehaviors>

          <endpointBehaviors>

             <behavior name="r1">

                 <dataContractSerializer maxItemsInObjectGraph="2147483647"/>

             </behavior>

          </endpointBehaviors>

       </behaviors>

       <bindings>

          <basicHttpBinding>

               <binding name="BasicHttpBinding_MyService"

                   closeTimeout="00:03:00" openTimeout="00:03:00"

                   receiveTimeout="00:10:00" sendTimeout="00:03:00"

                   allowCookies="false" bypassProxyOnLocal="false"

                   hostNameComparisonMode="StrongWildcard"

                   maxBufferSize="2147483647" maxBufferPoolSize="2147483647"

                   maxReceivedMessageSize="2147483647"

                   messageEncoding="Text" textEncoding="utf-8"

                   transferMode="Buffered" useDefaultWebProxy="true">

                  <security mode="None"/>

                </binding>

           </basicHttpBinding>

        </bindings>

        <client>

           <endpoint name="BasicHttpBinding_MyService"

               address="http://localhost:8080/MyService/MyService.svc"

               behaviorConfiguration="r1"

               binding="basicHttpBinding"

               bindingConfiguration="BasicHttpBinding_MyService"

               contract="MyService.IMyService"  />

       </client>

   </system.serviceModel>

   
   

   
   

   IMetadataExchange:

   
   

   http://azure.snagy.name/blog/?tag=imetadataexchange

   
   

   
   

   
   

   
   

   
   

   
   

   http://stackoverflow.com/questions/5337412/buffer-size-in-wcf-service?rq=1

   
   

   http://stackoverflow.com/questions/7827774/imetadataexchange-wcf-configuration-error

   
   

   http://stackoverflow.com/questions/784606/large-wcf-web-service-request-failing-with-400-http-bad-request

   
   

   http://stackoverflow.com/questions/8043802/how-do-you-send-image-data-to-a-wcf-service

   
   

   
   

   
   

   
   

   
   

   disable browser

   
   

   <SCRIPT type="text/javascript">

       window.history.forward();

       function noBack() { window.history.forward(); }

   </SCRIPT>

   </HEAD>

   <BODY onload="noBack();"

       onpageshow="if (event.persisted) noBack();" onunload="">

   
   

   -----------------------------------

   
   

   <html xmlns="http://www.w3.org/1999/xhtml">

   <head runat="server">

   <title>Disable Browser Back buttons</title>

   <script type="text/javascript" language="javascript">

   
   

   function DisableBackButton() {

   window.history.forward()

   }

   DisableBackButton();

   window.onload = DisableBackButton;

   window.onpageshow = function(evt) { if (evt.persisted) DisableBackButton() }

   window.onunload = function() { void (0) }

   </script>

   </head>

   <body >

   <form id="form1" runat="server">

   <div>

   First Page

   </div>

   <div>

   ........

   </div>

   </form>

   </body>

   </html>

   
   

   --------------

   
   

   1.Disable the page cache using script on server-side. The code is as below:

   
   

      Response.Buffer = true;

      Response.ExpiresAbsolute = DateTime.Now.AddHours(-1);

      Response.Expires = 0;

      Response.CacheControl = "no-cache";

   
   

   
   

   
   

   
   

   2.Disable the page cache to let the browser no longer save cache of web pages on client-side as below:

   
   

      <head>  

         <meta http-equiv="Expires" CONTENT="0">  

         <meta http-equiv="Cache-Control" CONTENT="no-cache">  

         <meta http-equiv="Pragma" CONTENT="no-cache">  

      </head>

   
   

   
   

   
   

   
   

   3.Use javascript on client-side to realize the forward effect, this counteracts the action of user’s clicking back button.

   
   

   See the code below:

   
   

      <script type="text/javascript">  

         window.history.forward(1);  

      </script>

   
   

   --------------

   
   

   
   

Java Script to C# (function calling)

http://social.msdn.microsoft.com/Forums/en-US/netfxjscript/thread/8ca72110-a149-4a03-8d55-30566aea521c

Unlock EC150 Bsnl or relaince CDMA modem

http://www.modemsolution.com/2012/12/unlock-huawei-ec150-reliance-cdma-modem.html