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.

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.

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;
  });
});

Why Select is DML statement in SQL?

Why Select is DML statement in SQL?

DML - Data Manipulation Language

The purely read-only SELECT query statement is classed with the 'SQL-data' statements 

The SELECT ... INTO form is considered to be DML because it manipulates (i.e. modifies) data.

SELECT Column1, Column2
INTO DestinationTable

FROM SourceTable

Copy Coumn1,Column2 From SourceTable to DestinationTable.


So Select is dml statement

Other DML Statement are
Insert, Update and Delete

Session Add, Remove, Clear, RemoveAll and Abandon in ASP.NET

Session Add, Remove,  Clear, RemoveAll  and Abandon in ASP.NET

1. Session Add

To add in the Session
For ex
Session.Add("UserID",1);
   or
Session["UserID"]=1;

UserID is Session ID
1 is Session value

2. Session Remove

Session.Remove(“UserID”);
It removes the specific session’s specific key value i.e) It removes the SessionID UserID


3. Session Clear

Session.Clear()
It clears all session value i.e) It clears all the key value pairs stored in the session state collection.

4. Session RemoveAll

Session.RemoveAll();
This Method calls above clear method in its implementation,
public sealed class HttpSessionState : ICollection, IEnumerable
{
   .....
   public void RemoveAll()
   {
       this.Clear();
   }
   .....
}

5. Session Abandon

Session.Abandon() destroy everything in the session. While logout you have to clear everything in session.

Handling Session in Common Class in ASP.NET WebForms or ASP.NET MVC

Handling Session in Common Class in ASP.NET WebForms or  ASP.NET MVC

SessionVariables.cs
using System;
using System.Web;
namespace WebApplication1
{
    public class SessionVariables
    {

        ///

        ///  Store EmpID in Session
        ///
        public static Int64 EmpID
        {
            get
            {
                return (Int64)HttpContext.Current.Session["EmpID"];
            }

            set
            {
                HttpContext.Current.Session["EmpID"] = value;
            }
        }
       
       
        ///

        /// Store EmpName in Session
        ///
        public static string EmpName
        {
            get
            {
                return (string)HttpContext.Current.Session["EmpName"];
            }

            set
            {
                HttpContext.Current.Session["EmpName"] = value;
            }
        }
     
    }

}

You can get or set the session variables in Webforms or MVC

using System;
using System.Web.UI;

namespace WebApplication1
{
    public partial class HandlingSession : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SessionVariables.EmpID = 1;

            SessionVariables.EmpName = "ArunPrakash";
        }
    }
}

Advantages:
1. Reduces duplicate Session variables declaration
2. Type conversion is in common functions.