20 April 2014

SQL Server Max Datetime

SQL Server Max Datetime

9999-12-31 23:59:59.997


Return SQL Server Min DateTime

Return SQL Server Min DateTime

Example
select cast('1753-1-1' as datetime)

or

select cast(-53690 as datetime)


Symmetric Key vs Asymmetric Key Cryptography in SQL Server

Symmetric Key vs Asymmetric Key Cryptography in SQL Server

Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.

Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. This intricacy has turned it into a resource-intensive process.

Examples in the below link
http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

MSDN
http://technet.microsoft.com/en-us/library/ms188357.aspx

http://technet.microsoft.com/en-us/library/ms174430.aspx


COALESCE Ignore Parameter if it is null in SQL Server Queries or Stored Procedure

COALESCE Ignore Parameter if it is null in SQL Server Queries or Stored Procedure

Example :
SELECT Employee, City, DateHired
FROM Employees
WHERE Employee = COALESCE(@Employee, Employee)

If @Employee is null, it displays all the Employee details and if it is not null, it filters and displays the record.


15 March 2014

IEnumerable VS IQueryable

IEnumerable VS IQueryable

IEnumerable
1.IEnumerable exists in System.Collections Namespace.
2.IEnumerable is best to query data from in-memory collections like List, Array etc.
3.While query data from database, IEnumerable execute select query on server side, load data in-memory    on client side and then filter data.
4.IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
5.IEnumerable supports deferred execution.
6.IEnumerable doesn’t supports custom query.
7.IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios.
8.Extension methods supports by IEnumerable takes functional objects.

StudentContext studcontext= new StudentContext();
IEnumerable objEmp = studcontext.Student.Where(a => a.StudName.StartsWith("A"));
objEmp = objEmp.Take(5); 

When you check the above execution using Sql Profiler, it looks like below

SELECT [t0].[StudID], [t0].[StudName], [t0].[StudentAddress] FROM [Student] AS [t0]
WHERE [t0].[StudName] LIKE @p0

In the above Query "top 5" is missing since IEnumerable filter the records on client side.

Accessing database using IEnumerable is not recommended.

IEnumerable is inherited by IQueryable

IQueryable
1.IQueryable exists in System.Linq Namespace.
2.IQueryable can move forward only over a collection, it can’t move backward and between the items.
3.IQueryable is best to query data from out-memory (like remote database, service) collections.
4.While query data from database, IQueryable execute select query on server side with all filters.
5.IQueryable is suitable for LINQ to SQL queries.
6.IQueryable supports deferred execution.
7.IQueryable supports custom query using CreateQuery and Execute methods.
8.IQueryable support lazy loading. Hence it is suitable for paging like scenarios.

StudentContext studcontext= new StudentContext();
IQueryable objEmp = studcontext.Student.Where(a => a.StudName.StartsWith("A"));
objEmp = objEmp.Take(5);

When you check the above execution using Sql Profiler, it looks like below

SELECT Top 5 [t0].[StudID], [t0].[StudName], [t0].[StudentAddress] FROM [Student] AS [t0]
WHERE [t0].[StudName] LIKE @p0

In the above query top 5 is exists and it filters the record in database itself.

Note:
1. Both IEnumerable and IQueryable can move forward only over a collection, it can’t move backward and    between the items.
2. While accessing database and Paging in Grid , IQueryable is recommended over IEnumerable.


2 February 2014

Alert Message in Window.Close in Jquery

Alert Message in Window.Close in  Jquery

window.onbeforeunload = function(){
    alert('You are closing the window');
}

Unload fires every time the current page is unloaded, that includes clicking on links, so you have to remove the event for them:
Code:
$(function () {
  $("btnSubmit").click(function {
    window.onbeforeunload = null;
  });
});


Implementing OAuth validation in a Web API

 I mplementing OAuth validation in a Web API Implementing OAuth validation in a Web API using C# typically involves several key steps to sec...