ENTITY FRAMEWORK LESSION 1.

Microsoft entity framework is ORM (object relation mapping) that enables developer to work

with relational database and domain sepecific objects.It skips the code that we was writing in earlier times to get data from database. Now developers issues queries using LINQ, then retrieve and manipulate the data as strongly typed objects.ORM implementation provides services like: change tracking, identity resolution, lazy loading and query translation. Now developers only need to focus on business logic rather than fetching data from database.

It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in database and working with the results in addition to DataReader and DataSet.

O/RM includes three main parts: Domain class objects, Relational database objects and Mapping information on how domain objects maps to relational database objects (tables, views & storedprocedures). ORM helps us to keep our database design separate from our domain class design. This makes application maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update & Delete) so developer doesn’t need to write it manually.

here are many ORM frameworks for .net in the market like DataObjects.Net, NHibernate, OpenAccess, SubSonic etc . ADO.NET Entity Framework is from Microsoft.

Differences between entiry framework and LINQ to sql

1.Entity framework supports not only SQL Server but also other database like Oracle, DB2, MySQL etc.

2.Most of the time L2S classes must be one-to-one with database objects e.g. Customer class can be mapped only with Customer table. On other hand in Entity Framework we can map domain class with multiple tables using various inheritance strategies like table per type (class) or table per hierarchy of classes etc.

3. We can also follow mutliple techniques using entity framework like code first, model first or database first. Now no  longer dependencies on old techniques.

4. In upcoming products microsoft also giving support and integration of entity framework.

ENTITY FRAMEWORK ARCHITECHTURE (GOT FROM A ANOTHER WEBSITE IT IS NOT MINE):

Following figure shows the overall architecture of the Entity Framework. Let’s see each component of the architecture:

EDM (Entity Data Model): EDM consist three main parts- Conceptual model, Mapping and Storage model.  

Conceptual Model: Conceptual model is our model classes and their relationships. This will be independent from our database table design. 

Storage Model: Storage model is our database design model which includes tables, views, stored procedures and their relationships and keys. 

Mapping: Mapping consist information about how our conceptual model is mapped to storage model. 

LINQ to Entities:LINQ to Entities is query language used to write queries against the object model. It returns entities which are defined in the conceptual model. We can use your LINQ skills here.

Entity SQL: Entity SQL is again a query language same as LINQ to Entities. However it is little more difficult than L2E and also developer need to learn it separately. 

Object Service: Object service is a main entry point for accessing data from database and to return it back. Object service is responsible for materialization which is process of converting data returned from entity client data provider (next layer) to an entity object structure.  

Entity Client Data Provider: The main responsibility of this layer is to convert L2E or Entity SQL queries into SQL query which is understood by underlying database. It communicates with ADO.Net data provider which in turn sends or retrieves data from database.

ADO.Net Data Provider: This layer communicates with database using standard ADO.Net.

I'll do more posting on this topic very soon, because entity framework 4.5 is knocking the door. :)

ASP.NET 4.5 Web Pages 2

New features include the following:

   1. New and updated site templates.
   2. Adding server-side and client-side validation using the Validation helper.
   3. The ability to register scripts using an assets manager.
   4. Enabling logins from Facebook and other sites using OAuth and OpenID.
   5. Adding maps using the Maps helper.
   6. Running Web Pages applications side-by-side.
   7. Rendering pages for mobile devices.

   8. The configuration logic For MVC applications has been moved from Global.asax.cs to a set of static classes in the App_Start directory. Routes are     registered in RouteConfig.cs. Global MVC filters are registered in FilterConfig.cs. Bundling and minification configuration now lives in     BundleConfig.cs
  9. Publishing of the project come under the project from solution explorere, It is removed from the top menu. Now whole setting can be done by right     click on the project and set the publish settings.

 When you open a Visual Studio 2010 SP1 Web project for the first time in Visual Studio 11 Beta, the following properties are added to the project file:

    FileUpgradeFlags
    UpgradeBackupLocation
    OldToolsVersion
    VisualStudioVersion
    VSToolsPath
FileUpgradeFlags, UpgradeBackupLocation, and OldToolsVersion are used by the process that upgrades the project file. They have no impact on working with the project in Visual Studio 2010.

VisualStudioVersion is a new property used by MSBuild 4.5 that indicates the version of Visual Studio for the current project. Because this property didn’t exist in MSBuild 4.0 (the version of MSBuild that Visual Studio 2010 SP1 uses), we inject a default value into the project file.

Extract to user control

In large web pages, it can be a good idea to move individual pieces into user controls. This form of refactoring can help increase the readability of the page and can simplify the page structure. This is similiar to "Extract to metho" functionality which is available in 3.5 onwards. So "Extract to User Control" is available in .net 4.5.


The VSToolsPath property is used to determine the correct .targets file to import from the path represented by the MSBuildExtensionsPath32 setting.

There are also some changes related to Import elements. These changes are required in order to support compatibility between both versions of Visual Studio.

Configuration Changes in ASP.NET 4.5 Website Templates

The following changes have been made to the default Web.config file for site that are created using website templates in Visual Studio 2012 Release Candidate:

    In the <httpRuntime> element, the encoderType attribute is now set by default to use the AntiXSS types that were added to ASP.NET. For details, see AntiXSS Library.

    Also in the element, the requestValidationMode attribute is set to "4.5". This means that by default, request validation is configured to use deferred ("lazy") validation. For details, see New ASP.NET Request Validation Features.

    The <modules> element of the section does not contain a runAllManagedModulesForAllRequests attribute. (Its default value is false.) This means that if you are using a version of IIS 7 that has not been updated to SP1, you might have issues with routing in a new site. For more information, see Native Support in IIS 7 for ASP.NET Routing.

Changes in webforms 4.5

ASP.NET Web Forms Changes:

Strongly Typed Data Controls

In ASP.NET 4.5, Web Forms includes some improvements for working with data. The first improvement is strongly typed data controls. For Web Forms controls in previous versions of ASP.NET, we display a data-bound value using Eval and a data-binding expression:
eg: <asp:Repeater runat="server" ID="customers">
        <ItemTemplate>
            <li>
                First Name: <%# Eval("FirstName")%>

                Last Name: <%# Eval("LastName")%>

            </li>
        </ItemTemplate>
    </asp:Repeater>

For two-way data binding, we use Bind:
<asp:FormView runat="server" ID="editCustomer">
    <EditItemTemplate>
        <div>
            <asp:Label runat="server" AssociatedControlID="firstName">
                First Name:

            <asp:TextBox ID="firstName" runat="server"
                Text='<%#Bind("FirstName") %>' />
        </div>
<EditItemTemplate>
<asp:FormView>
        <div>
            <asp:Label runat="server" AssociatedControlID="lastName">
                First Name:</asp:Label>
            <asp:TextBox ID="lastName" runat="server">

                Text='<%#BindItem.LastName %>' />

At run time, these calls use reflection to read the value of the specified member and then display the result in the markup. This approach makes it easy to data bind against arbitrary, unshaped data.

To address this issue, ASP.NET 4.5 adds the ability to declare the data type of the data that a control is bound to. We do this using the new ItemType property. When we set this property, two new typed variables are available in the scope of data-binding expressions: Item and BindItem. Because the variables are strongly typed, we get the full benefits of the Visual Studio development experience.

The following example shows for the Item member:

<asp:Repeater runat="server" ID="customer" ModelType="WebApplication.Customer">
<itemTemplate>
    First Name: <%# Item.FirstName %><br/>
    Last Name: <%# Item.LastName %>
</itemTemplate>
</asp:Repeater>

For two way binding "BindItem" can be used in place of Item.

Model Binding

Model binding extends data binding in ASP.NET Web Forms controls to work with code-focused data access. It incorporates concepts from the
ObjectDataSource control and from model binding in ASP.NET MVC.
To configure a data control to use model binding to select data, you set the control's SelectMethod property to the name of a method in the page's code. The data control calls the method at the appropriate time in the page life cycle and automatically binds the returned data. There's no need to explicitly call the DataBind method.

In the following example, the GridView control is configured to use a method named GetCategories:

<asp:GridView ID="categoriesGrid" runat="server" ItemType="WebApplication1.Model.Category"
    SelectMethod="GetCategories" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CategoryID" HeaderText="ID" />
        <asp:BoundField DataField="CategoryName" HeaderText="Name" />
        <asp:BoundField DataField="Description" HeaderText="Description" />
        <asp:TemplateField HeaderText="# of Products">
            <ItemTemplate><%# Item.Products.Count %></ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Here  we have created "GetCategories" method in code behind file which is without any parameters and returning an IEnumerable or IQueryable object. If the new ItemType property is set (which enables strongly typed data-binding expressions, as explained under Strongly Typed Data Controls earlier), the generic versions of these interfaces should be returned — IEnumerable or IQueryable, with the T parameter matching the type of the ItemType property (for example, IQueryable).

Code behind method defination of "GetCategories":
public IQueryable
GetCategories()
{
    var db = new Northwind();
    return db.Categories.Include(c => c.Products);
 }

Any query can be placed on returned IEnumerable and IQueryable objects eg. sorting, paging etc.

Filtering by values from a control

Suppose we want to extend the example to let the user choose a filter value from a drop-down list. Add the following drop-down list to the markup and configure it to get its data from another method using the SelectMethod property:

<asp:Label runat="server" AssociatedControlID="categories"
    Text="Select a category to show products for: " />
<asp:DropDownList runat="server" ID="categories"
    SelectMethod="GetCategories" AppendDataBoundItems="true"
    DataTextField="CategoryName" DataValueField="CategoryID"
    AutoPostBack="true">
  <asp:ListItem Value="" Text="- all -" />
</asp:DropDownList>

<asp:GridView ID="productsGrid" runat="server" DataKeyNames="ProductID"
    AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false"
    SelectMethod="GetProducts" >
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="ID" />
        <asp:BoundField DataField="ProductName" HeaderText="Name"                  
             SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price"
             SortExpression="UnitPrice" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="# in Stock"
             SortExpression="UnitsInStock" />
    </Columns>
    <EmptyDataTemplate>
         No products matching the filter criteria were found
</asp:GridView>

In the page code, add the new select method for the drop-down list:

public IQueryable<Category>
GetCategories()
{
    return _db.Categories;
 }

Finally, update the GetProducts select method to take a new parameter that contains the ID of the selected category from the drop-down list:

public IQueryableGetProducts([QueryString("q")] string keyword,[Control("categories")] int? categoryId)
 {
    IQueryable query = _db.Products;
 
    if (!String.IsNullOrWhiteSpace(keyword))
    {
        query = query.Where(p => p.ProductName.Contains(keyword));
    }
    if (categoryId.HasValue && categoryId > 0)
    {
        query = query.Where(p => p.CategoryID == categoryId);
    }
    return query;
 }

Now when the page runs, users can select a category from the drop-down list, and the GridView control is automatically re-bound to show the filtered data. This is possible because model binding tracks the values of parameters for select methods and detects whether any parameter value has changed after a postback. If so, model binding forces the associated data control to re-bind to the data.

HTML Encoded Data-Binding Expressions

we can now HTML-encode the result of data-binding expressions. Add a colon (:) to the end of the <%# prefix that marks the data-binding expression:

<asp:TemplateField HeaderText="Name">
    <ItemTemplate><%#: Item.Products.Name %></ItemTemplate>
</asp:TemplateField>

Unobtrusive Validation

We can now configure the built-in validator controls to use unobtrusive JavaScript for client-side validation logic. This significantly reduces the amount of JavaScript rendered inline in the page markup and reduces the overall page size. Wecan configure unobtrusive JavaScript for validator controls in any of these ways:
Globally by adding the following setting to the element in the Web.config file:

<add name="ValidationSettings:UnobtrusiveValidationMode" value="WebForms" />

HTML5 Updates

Some improvements have been made to Web Forms server controls to take advantage of new features of HTML5:

    The TextMode property of the TextBox control has been updated to support the new HTML5 input types like email, datetime, and so on.
    The FileUpload control now supports multiple file uploads from browsers that support this HTML5 feature.
    Validator controls now support validating HTML5 input elements.
    New HTML5 elements that have attributes that represent a URL now support runat="server". As a result, we can use ASP.NET conventions in URL paths, like the ~ operator to represent the application root
 (for example, <video runat="server" src="~/myVideo.wmv" />).
    The UpdatePanel control has been fixed to support posting HTML5 input fields.
                Text='<%# Bind("LastName") %>' />
        </div>
        <asp:Button runat="server" CommandName="Update"/>
    </EditItemTemplate>
</asp:FormView>

New improvements in .net 4.5 framework

Asynchronously Reading and Writing HTTP Requests and Responses

ASP.NET 4 introduced the ability to read an HTTP request entity as a stream using the HttpRequest.GetBufferlessInputStream method. This method provided streaming access to the request entity. However, it executed synchronously, which tied up a thread for the duration of a request.
ASP.NET 4.5 supports the ability to read streams asynchronously on an HTTP request entity, and the ability to flush asynchronously. ASP.NET 4.5 also gives you the ability to double-buffer an HTTP request entity, which provides easier integration with downstream HTTP handlers such as .aspx page handlers and ASP.NET MVC controllers.

Improvements to HttpRequest handling

The Stream reference returned by ASP.NET 4.5 from HttpRequest.GetBufferlessInputStream supports both synchronous and asynchronous read methods. The Stream object returned from GetBufferlessInputStream now implements both the BeginRead and EndRead methods. The asynchronous Stream methods let you asynchronously read the request entity in chunks, while ASP.NET releases the current thread between each iteration of an asynchronous read loop.
ASP.NET 4.5 has also added a companion method for reading the request entity in a buffered way: HttpRequest.GetBufferedInputStream. This new overload works like GetBufferlessInputStream, supporting both synchronous and asynchronous reads. However, as it reads, GetBufferedInputStream also copies the entity bytes into ASP.NET internal buffers so that downstream modules and handlers can still access the request entity. For example, if some upstream code in the pipeline has already read the request entity using GetBufferedInputStream, you can still use HttpRequest.Form or HttpRequest.Files.

Asynchronously flushing a response

Sending responses to an HTTP client can take considerable time when the client is far away or has a low-bandwidth connection. Normally ASP.NET buffers the response bytes as they are created by an application. ASP.NET then performs a single send operation of the accrued buffers at the very end of request processing.
If the buffered response is large (for example, streaming a large file to a client), you must periodically call HttpResponse.Flush to send buffered output to the client and keep memory usage under control. However, because Flush is a synchronous call, iteratively calling Flush still consumes a thread for the duration of potentially long-running requests.
ASP.NET 4.5 adds support for performing flushes asynchronously using the BeginFlush and EndFlush methods of the HttpResponse class. Using these methods, you can create asynchronous modules and asynchronous handlers that incrementally send data to a client without tying up operating-system threads. In between BeginFlush and EndFlush calls, ASP.NET releases the current thread. This substantially reduces the total number of active threads that are needed in order to support long-running HTTP downloads.

More features:

Support for await and Task-Based Asynchronous Modules and Handlers

New ASP.NET Request Validation Features

By default, ASP.NET performs request validation — it examines requests to look for markup or script in fields, headers, cookies, and so on. If any is detected, ASP.NET throws an exception. This acts as a first line of defense against potential cross-site scripting attacks.
ASP.NET 4.5 makes it easy to selectively read unvalidated request data. ASP.NET 4.5 also integrates the popular AntiXSS library, which was formerly an external library.
Developers have frequently asked for the ability to selectively turn off request validation for their applications. For example, if your application is forum software, you might want to allow users to submit HTML-formatted forum posts and comments, but still make sure that request validation is checking everything else.
ASP.NET 4.5 introduces two features that make it easy for you to selectively work with unvalidated input: deferred ("lazy") request validation and access to unvalidated request data.

Deferred ("lazy") request validation

In ASP.NET 4.5, by default all request data is subject to request validation. However, you can configure the application to defer request validation until you actually access request data. (This is sometimes referred to as lazy request validation, based on terms like lazy loading for certain data scenarios.) You can configure the application to use deferred validation in the Web.config file by setting the requestValidationMode attribute to 4.5 in the httpRUntime element, as in the following example:

<httpRuntime requestValidationMode="4.5" ... />

AntiXSS Library

Due to the popularity of the Microsoft AntiXSS Library, ASP.NET 4.5 now incorporates core encoding routines from version 4.0 of that library.

To do this, add the following attribute to the Web.config file:

<httpRuntime ...
  encoderType="System.Web.Security.AntiXss.AntiXssEncoder,

System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

When the encoderType attribute is set to use the AntiXssEncoder type, all output encoding in ASP.NET automatically uses the new encoding routines.

• Using multi-Core JIT compilation for faster startup

  If you want to disable this feature, make the following setting in the Web.config file:

  <configuration>
    
    <system.web>
     <compilation profileGuidedOptimizations="None"  />
  
       

• Tuning garbage collection to optimize for memory 

            To enable GC memory tuning, add the following setting to the Windows\Microsoft.NET\Framework  \v4.0.30319\aspnet.config file:

<configuration>
<!-- ... -->
  <runtime>
    <performanceScenario value="HighDensityWebHosting"  />

 

New features of VS2012

Hi Friends,
Here sharing some of few features coming in VS2012. I'll try to post the details in future, once getting chance to work on VS2012.

Visual studio 2012 RC features:
UI features:
1. Very first thing logo and look has been changed
2. Very fast to start in comparison to VS2010
3. Elegant project or file search option in solution explorer, where referesh and other buttons are present.
4. Above this search box, back,farward and home buttons are present (same as in browsers)
5. In VS2010 and previos version, we can see only class and other files present in solution explorer. To view its methods we need to open that class. But now in 2012 tree like structure is given, where you can open that tree all methods, properties in that class will be visible in solution explorer. One of the ultimate feature and time saving.
6. A quikc launch is alson included in VS2012.
7. Allow editing of code while it is in running state.
8. Now in VS2012 it becomes quite easy to develop XNA apps. For this follow the following steps:
    Create a new Class Library Project in .NET Framework 4.5(later we'll transform it to Windows Application)
    Add reference of XNA .dlls from a .NET Framework 4.5 project as a reference but as an extension!
9. Metro Style Application Development
    This is the greatest feature and only available for the Window 8 operating system. To develop a Metro style application, you need to download and install the Windows 8 Release Preview Evaluation copy which is freely available on the Microsoft site. Once you install the Window 8 Release Preview, install Visual Studio 2012 RC.

Select File -> New -> Project which will open the "New Project" window. Select Visual C# or Visual Basic from the Template (my favorite is Visual C#) and go to "Windows Metro style".

Here you have the options to select the kind of Metro style application you want to develop.

(Note: Metro style applications are only available for .Net Framework 4.0 and .Net Framework 4.5.)
10. New class view is introduced in new version. All buttons like NewFolder, back-forward buttons, settings,class icon came under in this classview.
11. Bundling: It lets us combine multiple JavaScript (.js) files or multiple cascading style sheet (.css) files so that they can be downloaded as a unit, rather than making individual HTTP requests.

11. Minification: It squeezes out whitespace and performs other types of compression to make the downloaded files as small as possible. At runtime, the process identifies the user agent, for example IE, Mozilla, etc. and then removes whatever is specific to Mozilla when the request comes from IE.

In earlier version we need to reference each js and css file in our master or other pages. But now if we add all js files in js folder and all css files in css folder. Then we need to give reference of that folder only. thats it.For this we need to use the NuGet packages. I also need to go in its detail.

To check performance of query in sqlserver

Following query is quite helpful to check performance of our sql queries.

SELECT TOP 10
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
                                                           ELSE statement_end_offset
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text
FROM sys.dm_exec_query_stats

Points to remember before Query optimization in SqlServer

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-clustered index
• Non-clustered index should be created on columns of table based on query which is running
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original source table
• Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure instead
• Check if there is atleast 30% HHD is empty – it improves the performance a bit
• If possible move the logic of UDF to SP as well
• Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned. This will help to narrow the return rows else it will perform a whole table scan and waste the Sql server resources with increasing the network traffic. While scanning the whole it will lock the Table which may prevent other users to access the table.
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
• It is seen many times developers use codes like   SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa'
  Instead of writing it like the below
  SELECT * FROM OrderTable WHERE UserName='telsa'
  Infact both the queries does the same work but the 2nd one is better and retrieves rows more speedly than the first query. Because Sql Server is not case sensitive
   
• While running a query, the operators used with the WHERE clause directly affect the performance. The operators shown below are in their decreasing order of their performance.
• =
• >,>=,<, <=
• LIKE
• <>  
• When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS.When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance. 
• It is always best practice to use the Index seek while the columns are covered by an index, this will force the Query Optimizer to use the index while using IN or OR clauses as a part of our WHERE clause. 
• SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN (406,530,956) Takes more time than 
  SELECT * FROM OrderTable (INDEX=IX_OrderID) WHERE Status = 1 AND OrderID IN (406,530,956)
  
• While we use IN, in the sql query it better to use one or more leading characters in the clause instead of using the wildcard character at the starting.
  
  SELECT * FROM CustomerTable WHERE CustomerName LIKE 'm%'
  SELECT * FROM CustomerTable WHERE CustomerName LIKE '%m'
  In the first query the Query optimizer is having the ability to use an index to perform the query and there by reducing the load on sql server. But in the second query, no suitable index can be created while running the query.
• While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result.
• Always avoid the use of SUBSTRING function in the query.
• The queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written. So certain things should be taken care of like
• Provide the least likely true expressions first in the AND. By doing this if the AND expression is false at the initial stage the clause will end immediately. So it will save execution time
• If all the parts of the AND expression are equally like being false then better to put the Complex expression first. So if the complex works are false then less works to be done.
• While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works. 
• e.g. in a SELECT statement with GROUP BY and HAVING clause, things happens like first WHERE clause will select appropriate rows then GROUP BY divide them to group of rows and finally the HAVING clause have less works to perform, which will boost the performance.   
   

Type of temporary tables in sqlserver

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do.These tables are created inside tempdb database.

Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

• Local Temp Table
• Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Creating Temporary Table in SQL Server

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.

Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server:

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into #LocalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.

Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table: 

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into ##NewGlobalTempTable values ( 1, 'test','India');

Now select records from that table:

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Points to Remember Before Using Temporary Tables

• Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
• Number of rows and columns need to be as minimum as needed.
• Tables need to be deleted when they are done with their work.

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The below scripts are used to insert and read the records for Tablevariables:

insert into @TempTableVariable values ( 1, 'Abhijit','India');

Now select records from that tablevariable:

select * from @TempTableVariable

When to Use Table Variable Over Temp Table

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.

When following error comes during browsing of any website directly from iis, it means our .net framework is not registered with our installed iis. I have faced this issue while installing "Umbraco" cms.

Error Summary
HTTP Error 500.21 - Internal Server Error
Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list

Solution :Its solution is very simple. Just register your .net framework with IIS.Run Following command on your command prompt.

windows/Microsoft.NET/Framework/v4.0.30319/aspnet_regiis.exe -i

It will install framework 4.0 (choosen one) on your IIS and your targetted websites will work fine.
Thats all :)
Happy Coding !!!
 

We can easily calculate the distance between two cities if we know the longitude and latitude of those citites. This can achieved by C# code as well as through sql server's function. Following functiona can be used to calculate the distance between two cities.

CREATEFUNCTION CalculateDistanceInMiles(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS float
AS 
BEGIN
-- CONSTANTS
DECLARE @EarthRadiusInMiles float;
SET @EarthRadiusInMiles = 3963.1
DECLARE @PI  float;
SET @PI = PI();
-- RADIANS conversion
DECLARE @lat1Radians float;
DECLARE @long1Radians float;
DECLARE @lat2Radians float;
DECLARE @long2Radians float;
SET @lat1Radians = @Latitude1 * @PI / 180;
SET @long1Radians = @Longitude1 * @PI / 180;
SET @lat2Radians = @Latitude2 * @PI / 180;
SET @long2Radians = @Longitude2 * @PI / 180;
RETURN Acos(
Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) +
Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) +
Sin(@lat1Radians) * Sin(@lat2Radians)
) * @EarthRadiusInMiles;
END

Export component art grid to excel

Following code can be used to export component art grid or asp.net grid to excel

 protected void Page_Load(object sender, EventArgs e)
        {
 ExportToExcel(DataTable, Response, "Excel Sheet-" + DateTime.Today.ToString("MM-dd-yyyy"));
 }

 public void ExportToExcel(DataTable dataTable, HttpResponse Response, string FileName)
        {
            Response.Clear();
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xlsx");
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
            GridView gv = new GridView();
            gv.DataSource = dataTable;
            gv.DataBind();
            gv.RenderControl(hw);
            Response.Write(sw.ToString());
            Response.End();
        }

Export component art grid or normal grid to pdf

Code to export component art grid to pdf
 private void Page_Load(System.Object sender, System.EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                Grid1.DataSource = buildGrid();
                Grid1.DataBind();
            }
        }

        public System.Data.DataSet buildGrid()
        {
            string conStr = null;
            conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
            conStr += Server.MapPath("~/app_data/demo.mdb");
            System.Data.OleDb.OleDbConnection dbCon = new System.Data.OleDb.OleDbConnection(conStr);
            dbCon.Open();

            string sql = null;
            sql = "SELECT * FROM Posts ORDER BY LastPostDate DESC";
            System.Data.OleDb.OleDbDataAdapter dbAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, dbCon);
            DataSet ds = new DataSet();
            dbAdapter.Fill(ds);

            return ds;
        }

        protected void  btnExport_Click(object sender, System.EventArgs e)
        {
            ExportToExcel(buildGrid(), 0, Response, "MySheet-" + DateTime.Today.ToString("MM-dd-yyyy"));
        }

        public void ExportToExcel(DataSet dSet, int TableIndex, HttpResponse Response, string FileName)
        {
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
            GridView gv = new GridView();
            gv.DataSource = dSet.Tables[TableIndex];
            gv.HeaderStyle.Font.Size = new FontUnit(4);
            gv.RowStyle.Font.Size = new FontUnit(4);
            gv.DataBind();
            for (int x = 0; x < gv.Columns.Count; x++)
            {
                gv.Columns[x].ItemStyle.Width = Unit.Pixel(100);
            }
            gv.RenderControl(hw);

            Document Doc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

            PdfWriter.GetInstance(Doc, new FileStream(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\myPDF.pdf", FileMode.Create));
            HTMLWorker htmlparser = new HTMLWorker(Doc);
            Doc.Open();

            Chunk c = new Chunk("PDF EXPORT OF POSTS \n", FontFactory.GetFont("Verdana", 15));
            Paragraph p = new Paragraph();
            p.Alignment = Element.ALIGN_CENTER;
            p.Add(c);
            Chunk chunk1 = new Chunk("By ComponentArt - www.componentart.com\n\n\n", FontFactory.GetFont("Verdana", 8));
            Paragraph p1 = new Paragraph();
            p1.Alignment = Element.ALIGN_RIGHT;
            p1.Add(chunk1);

            Doc.Add(p);
            Doc.Add(p1);

            System.Xml.XmlTextReader xmlReader = new System.Xml.XmlTextReader(new StringReader(sw.ToString()));
            htmlparser.Parse(new StringReader(sw.ToString()));
            Doc.Close();
            string Path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\myPDF.pdf";

            ShowPdf(Path);
        }
  private void ShowPdf(string strS)
        {
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/pdf";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + strS);
            Response.TransmitFile(strS);
            Response.End();
            //Response.WriteFile(strS);
            Response.Flush();

            Response.Clear();
        }