Round, Ceiling and Floor Function in sql server

FLOOR(value) - Returning the largest integer less than or equal to the input value. The returning data type is the same as the input value.

CEILLING(value) - Returning the smallest integer greater than or equal to the input value. The returning data type is the same as the input value.

ROUND(value, 0, 0) - Returning the integer most close to the input value. The returning data type is the same as the input value.


SELECT FLOOR(1234.5658);
SELECT CEILING(1234.5658);
SELECT ROUND(1234.5658, 0, 0);

Output

1234
1235
1235.0000

How to Deleting duplicate rows from a table

USING CTE


with Test as
(
select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
from employee_test1
)

delete from Test where rank > 1

How to Find the 3rd or Nth Highest Salary in a Table via SubQuery

select min(Emp_sal) from Employee_Test where emp_sal in (select top 3 emp_sal from Employee_Test order by emp_sal desc)

OR

select min(emp_sal) from (select top 3 emp_sal from Employee_Test order by emp_sal desc) a 

SSL and HTTPS related ASP.NET Interview Questions

How do you provide Secure Communication over the World Wide Web?
Security is not just a matter of identifying users and preventing unauthorized users from accessing your Web applications, but it’s just as important to ensure that sensitive data sent across the Internet can’t be read by others.

To provide secure communication across the Internet, IIS supports a standardized means of encrypting and decrypting Web requests and responses. This cryptography requires that you request an encryption key called a server certificate from an independent third party called a certificate authority.

What is Secure Sockets Layer (SSL)?
The Secure Sockets Layer (SSL) is the standard means of ensuring that data sent over the Internet can’t be read by others. When a user requests a secure Web page, the server generates an encryption key for the user’s session and then encrypts the page’s data before sending a response. On the client side, the browser uses that same encryption key to decrypt the requested Web page and to encrypt new requests sent from that page.

Explain the process of secure communication using SSL?
Using SSL in your application requires special authorization from a recognized certificate authority. This authorization comes in the form of a server certificate, which you install in IIS to identify your server. The certificate authority licenses server certificates (for a fee) and acts as a clearinghouse to verify your server’s identity over the Internet.

When a user’s browser begins secure communications, it requests the server certificate and checks it against a list of trusted sites provided by the certificate authority. If the server certificate does not match one of the sites already authorized by the user, or if the server certificate does not match the Web address for which it was registered, or if there are any other problems with the server certificate, the browser displays a warning.

In this way, the certificate authority not only provides encryption for secure data transmission, but it also provides assurance to users that your Web site is authentic.

What is the largest certificate authority?
The largest certificate authority is VeriSign.

What are the steps to follow to use SSL in your Web application?
1. Generate a certificate request from IIS.
2. Request a certificate from a certificate authority.
3. Install the certificate on the server using IIS.
4. Install the certificate on browsers if you are using a test certificate.
5. Use the Secure Hypertext Transfer Protocol (HTTPS) when accessing secure pages in your application.

What should you do before you can request a server certificate from a certificate authority?
Before you can request a server certificate from a certificate authority, you must generate a certificate request from IIS. The certificate request contains encrypted information about your server that the certificate authority uses to identify your server over the Internet.

What are the steps to follow to generate a certificate request from the IIS?
1. Select Default Web Site in the console tree of the IIS, and then choose Properties from the Action menu. IIS displays the Default Web Site Properties dialog box.
2. Click the Directory Security tab in the Properties dialog box, and then click Server Certificate. IIS starts the Web Server Certificate Wizard.
3. Step through the wizard by reading each screen and clicking Next. The wizard instructions are straightforward.
4. When you click Finish at the end, the wizard creates an encrypted text file with the .cer file extension. That file is the certificate request that you send to the certificate authority.

Why do you have to select Default Web Site when generating a Certificate Request from IIS?
IIS requires that a certificate be created at the server root before secure communications can be created or configured for subordinate sites on the server. That’s why you have to select Default Web Site (or the root Web site if you have renamed it). After you have installed a server certificate at the root, you can repeat the process for subordinate sites if you want separate certificates for those sites.

What is the file extension of a server certificate?
.cer

What are the steps to follow to install the Certificate to enable SSL for your Web applications?
To install a server certificate in IIS:
1. Select Default Web Site in the console tree of the IIS snap-in, and then choose Properties from the Action menu. IIS displays the Default Web Site Properties dialog box.
2. Click the Directory Security tab in the Properties dialog box, and then click Server Certificate. IIS starts the Web Server Certificate Wizard.
3. Click next, and select Process the Pending Request and Install the Certificate.
4. Click next, and enter the name of the certificate file.
5. Click next, and then click Finish to complete the installation.

What is the protocol on which secure pages are generally requested?
HTTPS, the protocol HTTPS is what initializes the secure communication. When you’ve begun secure communication, it continues until you specify a no secure site.

What are the steps to follow to make a web page secure in a web application?
To require secure communication for a Web page using IIS, follow these steps
1. Select the folder or file that requires secure communication, and then choose Properties from the Action menu. IIS displays the Properties dialog box.
2. Click the Directory Security tab, and then click Edit in the Secure Communications group. IIS displays the Secure Communications dialog box.
3. Select the Require Secure Channel (SSL) check box, and click OK.

Can a user access secure web page over HTTP protocol instead of HTTPS?
No, when you require secure communication for a Web page, that page can’t be viewed using HTTP. The user must type in or click a link using HTTPS, otherwise, access will be denied.

What is the difference between layers and tiers?

Layers refer to logical separation of code. Logical layers help you organize your code better. For example an application can have the following layers.


1)Presentation Layer or UI Layer
2)Business Layer or Business Logic Layer
3)Data Access Layer or Data Layer


The above three layers reside in their own projects, may be 3 projects or even more. When we compile the projects we get the respective layer DLL. So we have 3 DLL's now.


Depending upon how we deploy our application, we may have 1 to 3 tiers. As we now have 3 DLL's, if we deploy all the DLL's on the same machine, then we have only 1 physical tier but 3 logical layers.


If we choose to deploy each DLL on a separate machine, then we have 3 tiers and 3 layers.


So, Layers are a logical separation and Tiers are a physical separation. We can also say that, tiers are the physical deployment of layers.


 
Tiers:
1) Presentation Tier or UI Tier (Hosts the Presentation Layer or UI Layer). This can be considered as web server in case of an ASP.NET web application. 
2) Application Tier or Business Tier (Hosts Business Layer or Business Logic Layer). 
3) Data Access Tier or Data Tier (Hosts Data Access Layer or Data Layer).
4) Database Tier - SQL Server or Oracle (or any other database) which has tables, stored procedures and other database objects.


In general the following are the responsibilities of each layer or tier:


1)Presentation Layer or Tier is usually responsible for interacting with the user.
2) Business Layer or Tier is responsible for implementing the business logic of the application.
3) Data Access Layer or Tier is responsible for encapsulating the code that accesses the persistent data stores such as a relational database.

How to implement Custom paging in asp.net using stored procedure in gridview ?

When we need to display only some of the record in page and table in database contains lacks of rows then   we should have to use custom paging instead of default paging.

Why we should avoid to default paging and use custom paging reason

1.   In default paging we are fetching all rows in database that takes more execution time (i.e query                 execution time in SQL Server or any database management system).
2.  In default paging all rows (records of table) load in memory so memory consumption
3.   In default paging all rows fetch form back end to front end but we are displaying only some of the rows i.e page-size.

How implement custom paging -

1. ·                     Need to customize you store procedure 
2. ·                     No Change in binding Gridview or Datalist or Repeater whatever you are using 
3. ·                     Need to populate ( Generate ) Pager (Pagination ) manually ( Code also given below). 

Store Procedure 

CREATE PROCEDURE getDeals

      @StartIndex int,

      @PageSize int,

      @TotalCount int OutPut

as

select @TotalCount=count(1) from mstrDeals;

WITH CTE AS

(

   select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY creationdate) RowNumber,dealid,dealTitle

   from mstrDeals

)

select * from CTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)

Bind gird control 

public void bindGridview(int currentPage)

{

 int pageSize = 10;

 int _TotalRowCount = 0;

 string _ConStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

 using (SqlConnection con = new SqlConnection(_ConStr))

 {

   SqlCommand cmd = new SqlCommand("getDeals", con);

   cmd.CommandType = CommandType.StoredProcedure;

   int startRowNumber = ((currentPage - 1) * pageSize) + 1;

      

   cmd.Parameters.AddWithValue("@StartIndex", startRowNumber);

   cmd.Parameters.AddWithValue("@PageSize", pageSize);

   SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);

   parTotalCount.Direction = ParameterDirection.Output;

   cmd.Parameters.Add(parTotalCount);

   SqlDataAdapter da = new SqlDataAdapter(cmd);

   DataSet ds = new DataSet();

   da.Fill(ds);

   _TotalRowCount = Convert.ToInt32(parTotalCount.Value);

   grdCustomPagging.DataSource = ds;

   grdCustomPagging.DataBind();

   generatePager(_TotalRowCount, pageSize, currentPage);

 }

}

<asp:GridView Width="500" runat="server" ID="grdCustomPaggingdemo">

</asp:GridView>

Then we need to generate pager with total no of rows, page size and current page, below is the sample code that populating pager here total number of pager links is 5 that can be customize further by resetting local variable named totalLinkInPage and have first and last button also which automatic enable or disable when current page is first or last accordingly.

public void generatePager(int totalRowCount, int pageSize, int currentPage)

{

  int totalLinkInPage = 5;

  int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);

  int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);

  int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);

  if ((startPageLink + totalLinkInPage - 1) > totalPageCount)

  {

      lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);

      startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);

  }

  List<ListItem> pageLinkContainer = new List<ListItem>();

  if (startPageLink != 1)

      pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));

  for (int i = startPageLink; i <= lastPageLink; i++)

  {

      pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));

  }

  if (lastPageLink != totalPageCount)

      pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));

  dlPager.DataSource = pageLinkContainer;

  dlPager.DataBind();

}

protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)

{

   if (e.CommandName == "PageNo")

   {

       bindGrid(Convert.ToInt32(e.CommandArgument));

   }

}

<asp:DataList CellPadding="5" RepeatDirection="Horizontal" runat="server" ID="dlPager"

    onitemcommand="dlPager_ItemCommand">

    <ItemTemplate>

       <asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo"Text='<%#Eval("Text") %>' CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>

    </ItemTemplate>

</asp:DataList>

Can we declare delegate and event inside interface ?

No delegate can not declare but event can declare.
We can  declare 'method, event, properties, indexers' in interface.    

Can we store dataset in viewstate?

We can store dataset in viewstate but can not store dataReader.   We can store only  serializable value. We can serialize data set but not reader because datareader always in connection mode.

What is Cashing in ASP .net ?

Caching is a state management technique that can store a copy of the data in memory.
 To increase the performance of the application and improve the access time, caching is used. It exists in temporary storage, in other words when the data is no longer used then it expires. Using a cache we can retrieve the data from a database directly.

Mostly, there are 3 types of caching.
1. Output Caching  Synatx <%@ OutputCache Duration="30" VaryByParam="None" %>  

 2. Data Caching   ==  Store value in cashe   as like session and viewstate   Cache["UserName"] =        tbUserName.Text;
 Cache["Pwd"] = tbpwd.Text;  

3 Fragment Caching   create the user control

What is the difference between parent() and parents() methods in jQuery ?

Difference is the parent() function travels only one level in the DOM tree, where parents() function search through the whole DOM tree.

What is the difference between jquery.size() and jquery.length?

jQuery .size() method returns number of element in the object.
 But it is not preferred to use the size() method as jQuery provide .length property and which does the same thing. But the .length property is preferred because it does not have the overhead of a function call.

Write a query search name start with 'a' from table in Linq.

 string[] names = { "Alonso", "Zheng", "Smith" };
            var queryResults =
                from n in names
                where n.StartsWith("A")
                select n;

Write a program How many time Character occurs in string using c# ?

String inputstring = "anand swaroop";
            int countValue = 1;
            Dictionary<string, int> objdict = new Dictionary<string, int>();
            foreach (var value in inputstring.ToCharArray())
            {
                if (objdict.ContainsKey(value.ToString()))
                {
                    objdict.TryGetValue(value.ToString(), out countValue);
                    objdict[value.ToString()] = countValue+1;
                     
                }
                else
                {
                    objdict.Add(value.ToString(), countValue);
                }
            }

What is difference between Routing and URL Rewriting ?

 Many developers compare routing to URL rewriting since both look similar and can be used to make SEO friendly URLs. But both the approaches are very much different. The main difference between routing and url rewriting is given below:

1  URL rewriting is focused on mapping one URL (new url) to another URL (old url) while routing is focused on mapping a URL to a resource.

2 URL rewriting rewrites your old url to new one while routing never rewrite your old url to new one but it map to the original route.

When to use Attribute Routing?

 The convention-based routing is complex to support certain URI patterns that are common in RESTful APIs. But by using attribute routing you can define these URI patterns very easily.
For example, resources often contain child resources like Clients have orders, movies have actors, books have authors and so on. It’s natural to create URIs that reflects these relations like as: /clients/1/orders
This type of URI is difficult to create using convention-based routing. Although it can be done, the results don’t scale well if you have many controllers or resource types.
With attribute routing, it’s pretty much easy to define a route for this URI. You simply add an attribute to the controller action as:


[Route("clients/{clientId}/orders")]
 public IEnumerable<Order> GetOrdersByClient(int clientId)
 { //TO DO }

How to call WEB API in ASP.NET MVC ?

ASP.NET WEB API can be called by using HttpClient and WEB API address as given below:

public class ProductController : Controller
 {
 HttpClient Client = new HttpClient();
Uri BaseAddress = new Uri("http://localhost:131/");
 public ActionResult Index()
 {
Client.BaseAddress = BaseAddress; HttpResponseMessage response = Client.GetAsync("productservice/GetProducts").Result;
if (response.IsSuccessStatusCode)
{
var data = response.Content.ReadAsAsync<IEnumerable<Product>>().Result; return View(data);
}
return View();
 }
}

What is difference between ASP.NET MVC and ASP.NET WEB API ?

 There are following differences between ASP.NET MVC and WEB API:

1. ASP.NET MVC is used to create web applications that return both views and data but ASP.NET WEB API is used to create full blown HTTP services with easy and simple way that returns only data not view.

2. WEB API helps to build REST-ful services over the .NET Framework and it also support content-negotiation(it's about deciding the best response format data that could be acceptable by the client. it could be JSON,XML,ATOM or other formatted data), self-hosting which are not in MVC.

3. WEB API also takes care of returning data in particular format like JSON, XML or any other based upon the Accept header in the request and you don't worry about that. MVC only return data in JSON format using JsonResult.


4. In WEB API the request are mapped to the actions based on HTTP verbs but in MVC it is mapped to actions name.

5. ASP.NET WEB API is new framework and part of the core ASP.NET framework. The model binding, filters, routing and others MVC features exist in WEB API are different from MVC and exists in the new System.Web.Http assembly. In MVC, these features exist within System.Web.Mvc. Hence WEB API can also be used with ASP.NET and as a stand-alone service layer.

6. You can mix WEB API and MVC controller in a single project to handle advanced AJAX requests which may return data in JSON, XML or any others format and building a full blown HTTP service. Typically, this will be called WEB API self-hosting.

7. When you have mixed MVC and WEB API controller and you want to implement the authorization then you have to create two filters one for MVC and another for WEB API since both are different.

8. Moreover, WEB API is light weight architecture and except the web application it can also be used with smart phone apps.

Which one to choose between WCF and WEB API ?

 The following points help you to choose between WCF and WEB API:

1. Choose WCF when you want to create a service that should support special scenarios such as one way messaging, message queues, duplex communication etc.
2. Choose WCF when you want to create a service that can use fast transport channels when available, such as TCP, Named Pipes, or maybe even UDP (in WCF 4.5), and you also want to support HTTP when all other transport channels are unavailable.
3. Choose WEB API when you want to create resource-oriented services over HTTP that can use the full features of HTTP (like URIs, request/response headers, caching, versioning, various content formats).
4. Choose WEB API when you want to expose your service to a broad range of clients including browsers, mobiles, iphone and tablets.

What is difference between WCF and WEB API and WCF REST and Web Service?

.NET framework has a number of technologies that allow you to create HTTP services such as Web Service, WCF and now WEB API. There are following differences among these four:

Web Service

1. It is based on SOAP and return data in XML form.
2. It supports only HTTP protocol.
3. It is not open source but can be consumed by any client that understands xml.
4. It can be hosted only on IIS.


WCF

1. It is also based on SOAP and return data in XML form.
2. It is the evolution of the web service (ASMX) and support various protocols like TCP, HTTP, HTTPS, Named Pipes, MSMQ.
3. The main issue with WCF is, its tedious and extensive configuration.
4. It is not open source but can be consumed by any client that understands xml.
5. It can be hosted with in the application or on IIS or using window service.


WCF Rest

1. To use WCF as WCF Rest service you have to enable webHttpBindings.
2. It support HTTP GET and POST verbs by [WebGet] and [WebInvoke] attributes respectively.
3. To enable other HTTP verbs you have to do some configuration in IIS to accept request of that particular verb on .svc files
4. Passing data through parameters using a WebGet needs configuration. The UriTemplate must be specified
5. It support XML, JSON and ATOM data format.

WEB API

1. This is the new framework for building HTTP services with easy and simple way.
2. WEB API is open source an ideal platform for building REST-ful services over the .NET Framework.
3. Unlike WCF Rest service, it use the full features of HTTP (like URIs, request/response headers, caching, versioning, various content formats)
4. It also supports the MVC features such as routing, controllers, action results, filter, model binders, IOC container or dependency injection, unit testing that makes it more simple and robust.
5. It can be hosted with in the application or on IIS.
6. It is light weight architecture and good for devices which have limited bandwidth like smart phones.
7. Responses are formatted by WEB API’s MediaTypeFormatter into JSON, XML or whatever format you want to add as a MediaTypeFormatter.

What are the different types of results in MVC ?

There 12 kinds of results in MVC, at the top is the ActionResult class which is a base class that can have 11 subtypes as listed below:

1. ViewResult - Renders a specified view to the response stream
2. PartialViewResult - Renders a specified partial view to the response stream
3. EmptyResult - An empty response is returned
4. RedirectResult - Performs an HTTP redirection to a specified URL
5. RedirectToRouteResult - Performs an HTTP redirection to a URL that is determined by the routing engine, based on given route data
6. JsonResult - Serializes a given ViewData object to JSON format
7. JavaScriptResult - Returns a piece of JavaScript code that can be executed on the client
8. ContentResult - Writes content to the response stream without requiring a view
9. FileContentResult - Returns a file to the client
10. FileStreamResult - Returns a file to the client, which is provided by a Stream
11. FilePathResult - Returns a file to the client

Difference between each version of MVC 2, 3 , 4, 5 and 6?

MVC 6

ASP.NET MVC and Web API has been merged in to one.

Dependency injection is inbuilt and part of MVC.

Side by side - deploy the runtime and framework with your application

Everything packaged with NuGet, Including the .NET runtime itself.

New JSON based project structure.

No need to recompile for every change. Just hit save and refresh the browser.

Compilation done with the new Roslyn real-time compiler.

vNext is Open Source via the .NET Foundation and is taking public contributions.

vNext (and Rosyln) also runs on Mono, on both Mac and Linux today.

MVC 5

One ASP.NET

Attribute based routing

Asp.Net Identity

Bootstrap in the MVC template

Authentication Filters

Filter overrides

MVC 4

ASP.NET Web API

Refreshed and modernized default project templates

New mobile project template

Many new features to support mobile apps

Enhanced support for asynchronous methods

MVC 3

Razor

Readymade project templates

HTML 5 enabled templates

Support for Multiple View Engines

JavaScript and Ajax

Model Validation Improvements

MVC 2

Client-Side Validation

Templated Helpers

Areas

Asynchronous Controllers

Html.ValidationSummary Helper Method

DefaultValueAttribute in Action-Method Parameters

Binding Binary Data with Model Binders

DataAnnotations Attributes

Model-Validator Providers

New RequireHttpsAttribute Action Filter

Templated Helpers

Display Model-Level Errors