Math Functions

Function	Oracle	SQL Server
Absolute value	ABS	ABS
Arc cosine	ACOS	ACOS
Arc sine	ASIN	ASIN
Arc tangent of n	ATAN	ATAN
Arc tangent of n and m	ATAN2	ATN2
Smallest integer >= value	CEIL	CEILING
Cosine	COS	COS
Hyperbolic cosine	COSH	COT
Exponential value	EXP	EXP
Round down to nearest integer	FLOOR	FLOOR
Natural logarithm	LN	LOG
Logarithm, any base	LOG(N)	N/A
Logarithm, base 10	LOG(10)	LOG10
Modulus (remainder)	MOD	USE MODULO (%) OPERATOR
Power	POWER	POWER
Random number	N/A	RAND
Round	ROUND	ROUND
Sign of number	SIGN	SIGN
Sine	SIN	SIN
Hyperbolic sine	SINH	N/A
Square root	SQRT	SQRT
Tangent	TAN	TAN
Hyperbolic tangent	TANH	N/A
Truncate	TRUNC	N/A
Highest number in list	GREATEST	N/A
Lowest number in list	LEAST	N/A
Convert number if NULL	NVL	ISNULL
Standard deviation	STDDEV	STDEV
Variance	VARIANCE	VAR

Date Functions

Function	Oracle	Microsoft SQL Server
Date addition	(use +)	DATEADD
Date subtraction	(use -)	DATEDIFF
Last day of month	LAST_DAY	N/A
Time zone conversion	NEW_TIME	N/A
First weekday after date	NEXT_DAY	N/A
Convert date to string	TO_CHAR	DATENAME
Convert date to number	TO_NUMBER(TO_CHAR())	DATEPART
Convert string to date	TO_DATE	CAST
Get current date and time	SYSDATE	GETDATE()

String Functions

Function	Oracle	Microsoft SQL Server
String concatenate	||, CONCAT	+ (expression1 + expression2)
Convert character to ASCII	ASCII	ASCII
Convert ASCII to character	CHR	CHR
Return starting point of character in character string (from left)	INSTR (with two arguments)	CHARINDEX
Length of character string	LENGTH	DATALENGTH or LEN
Convert characters to uppercase	UPPER	UPPER
Convert characters to lowercase	LOWER	LOWER
Remove leading blank spaces	LTRIM	LTRIM
Remove trailing blank spaces	RTRIM	RTRIM
Substring	SUBSTR (second argument cannot be a negative number)	SUBSTRING
Pad left side of character string	LPAD	N/A
Starting point of pattern in character string	INSTR	PATINDEX
Repeat character string multiple times	RPAD	REPLICATE
Phonetic representation of character string	SOUNDEX	SOUNDEX
String of repeated spaces	RPAD	SPACE
Character data converted from numeric data	TO_CHAR	STR
Replace characters	REPLACE	STUFF
Capitalize first letter of each word in string	INITCAP	N/A
Translate character string	TRANSLATE	N/A
Greatest character string in list	GREATEST	N/A
Least character string in list	LEAST	N/A
Convert string if NULL	NVL	ISNULL

Oracle Vs Microsoft SQL Server Functions

Arithmetic Operators

Oracle	Microsoft SQL Server
+	+
-	-
*	*
/	/

Comparison Operators

Oracle	Microsoft SQL Server
=	=
>	>
<	<
>=	>=
<=	<=
<>, !=, ^=	<>
IS NOT NULL	IS NOT NULL
IS NULL	IS NULL

Pattern Matching

Oracle	Microsoft SQL Server
LIKE	LIKE
NOT LIKE	NOT LIKE

Group Functions

Oracle	Microsoft SQL Server
AVG	AVG
COUNT	COUNT
MAX	MAX
MIN	MIN
SUM	SUM

Functions for encrypting and decrypting using TripleDES

string strResult;
byte[] KEY_192 = {42, 16, 93, 156, 78, 4, 218, 32,15, 167, 44, 80, 26, 250, 155, 112,2, 94, 11, 204, 119, 35, 184, 197};
byte[] IV_192 = {55, 103, 246, 79, 36, 99, 167, 3,42, 5, 62, 83, 184, 7, 209, 13,145, 23, 200, 58, 173, 10, 121, 222};

public string encrypt(string strInput)
{
TripleDESCryptoServiceProvider cryptoProvider=new TripleDESCryptoServiceProvider();
MemoryStream ms=new MemoryStream();
CryptoStream cs=new CryptoStream(ms,cryptoProvider.CreateEncryptor(KEY_192, IV_192),CryptoStreamMode.Write);
StreamWriter sw=new StreamWriter(cs);
sw.Write(strInput);
sw.Flush();
cs.Flush();
cs.FlushFinalBlock();
ms.Flush();
strResult=Convert.ToBase64String(ms.GetBuffer(),0,Convert.ToInt32(ms.Length));
return strResult;
}

public string decrypt(string strinput)
{
TripleDESCryptoServiceProvider cryptoProvider=new TripleDESCryptoServiceProvider();
byte[] buffer=Convert.FromBase64String(strinput.Trim());
MemoryStream ms=new MemoryStream(buffer);
CryptoStream cs=new CryptoStream(ms,cryptoProvider.CreateDecryptor(KEY_192, IV_192),CryptoStreamMode.Read);
StreamReader sr=new StreamReader(cs);
strResult = sr.ReadToEnd();
return strResult;
}

Functions for encrypting and decrypting using DES

string strResult;
byte[] KEY_64 ={42, 16, 93, 156, 78, 4, 218, 32};
byte[] IV_64={55, 103, 246, 79, 36, 99, 167, 3};

public string encrypt(string strInput)
{
DESCryptoServiceProvider cryptoProvider=new DESCryptoServiceProvider();
MemoryStream ms=new MemoryStream();
CryptoStream cs=new CryptoStream(ms,cryptoProvider.CreateEncryptor(KEY_64, IV_64),CryptoStreamMode.Write);
StreamWriter sw=new StreamWriter(cs);
sw.Write(strInput);
sw.Flush();
cs.Flush();
cs.FlushFinalBlock();
ms.Flush();
strResult=Convert.ToBase64String(ms.GetBuffer(),0,Convert.ToInt32(ms.Length));
return strResult;
}

public string decrypt(string strinput)
{
DESCryptoServiceProvider cryptoProvider=new DESCryptoServiceProvider();
byte[] buffer=Convert.FromBase64String(strinput.Trim());
MemoryStream ms=new MemoryStream(buffer);
CryptoStream cs=new CryptoStream(ms,cryptoProvider.CreateDecryptor(KEY_64, IV_64),CryptoStreamMode.Read);
StreamReader sr=new StreamReader(cs);
strResult = sr.ReadToEnd();
return strResult;
}

Application Objects

Application variable is reflected in the current sessions of all users. For example, we may like to fix variables like tax rate, discount rate, company name, etc., that will be specified once for all variables we can access in a session.

Creating an application variable is similar to session variables.

Application

(“legal_notice”) = “No part of this article may be reproduced without prior permission”

There is a concern when changing the value of an application variable: at any particular instant, multiple sessions might be trying to change the value, although only one session can be allowed to change it. ASP.NET has an in built mutual exclusion for dealing with these types of problems.

• Application.Lock – Locks the application variables
• Application.Unlock – Unlocks the application variables

Once application variables are locked sessions that attempt to change them have to wait.

Session Objects

Sessions in ASP.NET are simply hash tables in the memory with a timeout specified.

Session

(“username”) = “Aayush Puri”
Session(“color”) = “Blue”

Sessions in ASP.NET are identified using 32-bit long integers known as Session IDs. The ASP engine generates these session ID’s so that uniqueness is guaranteed.

Session Type	What it does	Example
Session.Abandon	Abandons (cancels) the current session.
Session.Remove	Delete an item from the session-state collection.	Session(“username”) = “Aayush Puri” (Initialize a session variable) Session.Remove(“username”) (Deletes the session variable “username”)
Session.RemoveAll	Deletes all session state items
Session.Timeout	Set the timeout (in minutes) for a session	Session.Timeout=30 (If a user does NOT request a page of the ASP.NET application within 30 minutes then the session expires.)
Session.SessionID	Get the session ID (read only property of a session) for the current session.
Session.IsNewSession	This is to check if the visitor’s session was created with the current request i.e. has the visitor just entered the site. The IsNewSession property is true within the first page of the ASP.NET application.

ASP Server Variables

Syntax

Request.ServerVariables (server_variable)

Parameter	Description
server_variable	Required. The name of the server variable to retrieve

Server Variables

Variable	Description
ALL_HTTP	Returns all HTTP headers sent by the client. Always prefixed with HTTP_ and capitalized
ALL_RAW	Returns all headers in raw form
APPL_MD_PATH	Returns the meta base path for the application for the ISAPI DLL
APPL_PHYSICAL_PATH	Returns the physical path corresponding to the meta base path
AUTH_PASSWORD	Returns the value entered in the client's authentication dialog
AUTH_TYPE	The authentication method that the server uses to validate users
AUTH_USER	Returns the raw authenticated user name
CERT_COOKIE	Returns the unique ID for client certificate as a string
CERT_FLAGS	bit0 is set to 1 if the client certificate is present and bit1 is set to 1 if the cCertification authority of the client certificate is not valid
CERT_ISSUER	Returns the issuer field of the client certificate
CERT_KEYSIZE	Returns the number of bits in Secure Sockets Layer connection key size
CERT_SECRETKEYSIZE	Returns the number of bits in server certificate private key
CERT_SERIALNUMBER	Returns the serial number field of the client certificate
CERT_SERVER_ISSUER	Returns the issuer field of the server certificate
CERT_SERVER_SUBJECT	Returns the subject field of the server certificate
CERT_SUBJECT	Returns the subject field of the client certificate
CONTENT_LENGTH	Returns the length of the content as sent by the client
CONTENT_TYPE	Returns the data type of the content
GATEWAY_INTERFACE	Returns the revision of the CGI specification used by the server
HTTP_<HeaderName>	Returns the value stored in the header HeaderName
HTTP_ACCEPT	Returns the value of the Accept header
HTTP_ACCEPT_LANGUAGE	Returns a string describing the language to use for displaying content
HTTP_COOKIE	Returns the cookie string included with the request
HTTP_REFERER	Returns a string containing the URL of the page that referred the request to the current page using an tag. If the page is redirected, HTTP_REFERER is empty
HTTP_USER_AGENT	Returns a string describing the browser that sent the request
HTTPS	Returns ON if the request came in through secure channel or OFF if the request came in through a non-secure channel
HTTPS_KEYSIZE	Returns the number of bits in Secure Sockets Layer connection key size
HTTPS_SECRETKEYSIZE	Returns the number of bits in server certificate private key
HTTPS_SERVER_ISSUER	Returns the issuer field of the server certificate
HTTPS_SERVER_SUBJECT	Returns the subject field of the server certificate
INSTANCE_ID	The ID for the IIS instance in text format
INSTANCE_META_PATH	The meta base path for the instance of IIS that responds to the request
LOCAL_ADDR	Returns the server address on which the request came in
LOGON_USER	Returns the Windows account that the user is logged into
PATH_INFO	Returns extra path information as given by the client
PATH_TRANSLATED	A translated version of PATH_INFO that takes the path and performs any necessary virtual-to-physical mapping
QUERY_STRING	Returns the query information stored in the string following the question mark (?) in the HTTP request
REMOTE_ADDR	Returns the IP address of the remote host making the request
REMOTE_HOST	Returns the name of the host making the request
REMOTE_USER	Returns an unmapped user-name string sent in by the user
REQUEST_METHOD	Returns the method used to make the request
SCRIPT_NAME	Returns a virtual path to the script being executed
SERVER_NAME	Returns the server's host name, DNS alias, or IP address as it would appear in self-referencing URLs
SERVER_PORT	Returns the port number to which the request was sent
SERVER_PORT_SECURE	Returns a string that contains 0 or 1. If the request is being handled on the secure port, it will be 1. Otherwise, it will be 0
SERVER_PROTOCOL	Returns the name and revision of the request information protocol
SERVER_SOFTWARE	Returns the name and version of the server software that answers the request and runs the gateway
URL	Returns the base portion of the URL

life of an ASP.NET page

The ASP.NET Page Object Model-life of an ASP.NET page

Stage	Page Event	Overridable method
Page initialization	Init
View state loading		LoadViewState
Postback data processing		LoadPostData method in any control that implements the IPostBackDataHandler interface
Page loading	Load
Postback change notification		RaisePostDataChangedEvent method in any control that implements the IPostBackDataHandler interface
Postback event handling	Any postback event defined by controls	RaisePostBackEvent method in any control that implements the IPostBackEventHandler interface
Page pre-rendering phase	PreRender
View state saving		SaveViewState
Page rendering		Render
Page unloading	Unload