string isEmpty = string.Empty; DateTime dt = isEmpty == string.Empty ? BNull.Value : DateTime.Now;Error
string isEmpty = string.Empty; DateTime dt = Convert.ToDateTime(isEmpty == string.Empty ? (object)DBNull.Value : (object)DateTime.Now);
Scalar Functions | In-Line Table Functions | Multistatement Table Functions |
|---|---|---|
Returns only scalar value. | Return a table variable that was created by a single select statement. | Returns a table variable. No multi result set. This type of function is used when we want to apply more logic except we can express in a single query. I.e. like stored procedure we can manipulate with more statements. |
Support multiple T-SQL statements. | Supports only sing T-SQL Statement. | Support multiple T-SQL statements. |
Return type is fixed. | No need to specify the table column names and its type. | We have to specify the table columns. |
CREATE FUNCTION Testscalar(@ID INT)
RETURNS INT
AS
BEGIN
SELECT @ID = 10
SELECT @ID = @ID + 1
SELECT @ID = @ID + 1
RETURN @ID
END
|
CREATE FUNCTION [dbo].[Testinline]
(
Params
)
RETURNS TABLE AS
RETURN
SELECT *
FROM dbo.treeview
CREATE FUNCTION [dbo].[Testinline](@i INT) RETURNS TABLE AS RETURN SELECT @i AS [ColName] |
CREATE FUNCTION [dbo].[Testmultiline]
(
@ID INT
)
RETURNS @TableVariable TABLE ( I INT,
J VARCHAR(10)) AS
BEGIN
INSERT
INTO @TableVariable
SELECT 1, '10' UNION ALL
SELECT 2, '20' UNION ALL
SELECT 3, '30'
RETURN
END
|
The last statement included within a function must be a return statement RETURN statements in scalar valued functions must include an argument. Simply retrun statemet is wrong. | The last statement included within a function must be a return statement. Return @I is wrong Return @I as a is wrong | The last statement included within a function must be a return statement. return @TableVariable is wrong. |
SELECT ColNames, dbo.ScalarValuedFunction(starttime,endtime) AS ColName FROM TableName
WHERE BankerID = dbo.ScalarValuedFunction('IBM')
SELECT * FROM dbo.TableValuedFunctions('key1|key2|key3|key4|key5', '|')
-- For converting arrays to table
SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
SELECT * FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
Event | Occurs |
AcquireRequestState | When ASP.NET acquires the current state (for example, session state) associated with the current request |
AuthenticateRequest | When a security module has established the identity of the user |
AuthorizeRequest | When a security module has verified user authorization |
BeginRequest | When the first event in the HTTP pipeline chain of execution responds to a request |
Disposed | When ASP.NET completes the chain of execution when responding to a request |
EndRequest | When the last event in the HTTP pipeline chain of execution responds to a request |
Error | When an unhandled exception is thrown |
PostRequestHandlerExecute | When the ASP.NET handler (page, XML Web Service) finishes execution |
PreRequestHandlerExecute | Just before ASP.NET begins executing a handler such as a page or XML Web Service |
PreSendRequestContent | Just before ASP.NET sends content to the client |
PreSendRequestHeaders | Just before ASP.NET sends HTTP headers to the client |
ReleaseRequestState | After ASP.NET finishes executing all request handlers; also causes state modules to save the current state data |
ResolveRequestCache | When ASP.NET completes an authorization event to let the caching modules serve requests from the cache, bypassing execution of the handler (the page or XML Web Service, for example) |
UpdateRequestCache | When ASP.NET finishes executing a handler in order to let caching modules store responses that will be used to serve subsequent requests from the cache |
ISNULL() | COALESCE() |
|---|---|
Specifc to SQL SERVER | ANSI standard. The query can be used in another RDBMS which follows the ANSI standard. |
SELECT ISNULL(NULL, NULL) --O/P null | SELECT COALESCE(NULL, NULL) Error: None of the result expressions in a CASE specification can be NULL. In coalesce atleast one parameter should not be null. |
DECLARE @a VARCHAR(10) DECLARE @b VARCHAR(10) SELECT ISNULL(@a, @b) --O/P null | DECLARE @a VARCHAR(10) DECLARE @b VARCHAR(10) SELECT COALESCE (@a, @b) --O/P null |
Required 2 parameters | Requires atleast 2 parameters |
Accepts only 2 parameters. But we can nest like this if we want more than 2 parameter. ISNULL(ISNULL(Col1,Col2), Col3) | I tried 18600 parameters. If it reaches more than that or with this count itself the SQL SERVER throws "System.OutOfMemoryException". So I think this blongs to hardware specification. |
Consider 2nd parameter data type as first datatype. (i.e. It uses as the first parameter datatype.) If the length of the second parameter is greater than the first parameter it will be truncated to first parameter size. Eg.1 DECLARE @a VARCHAR(10) DECLARE @b DATETIME SET @b = GETDATE() SELECT CONVERT(VARCHAR(11), @b, 100) SELECT ISNULL(@a, @b) --O/P Apr 13 201 --It took the format 100 and varchar size to 10. --Change the varchar size of below query and you will understand. SELECT ISNULL(@a, CONVERT(VARCHAR(11), @b, 100)) SELECT ISNULL(CAST(@a as varchar(11)), @b) --O/P Apr 13 201 --O/P Apr 13 2010 Eg.2 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) SET @b = 'SQL SERVER' SELECT ISNULL(@a, @b) --O/P SQL S It truncates to first parameter size. Eg.3 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) DECLARE @c INT SET @c = 123456 SELECT ISNULL(@a, @c) --O/P * If SET @c = 12345--O/P 12345 Since @a size is 5 which is the first parameter. | Multiple datatype is allowed. It took highest data type in the expression list. If we are having int and double. It took double. Eg.1 DECLARE @a VARCHAR(10) DECLARE @b DATETIME SET @b = GETDATE() SELECT COALESCE(@a, @b) --O/P 2010-04-13 17:21:12.330 Eg.2 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) SET @b = 'SQL SERVER' SELECT COALESCE(@a, @b) --O/P SQL SERV It tooks the datatype of the parameter which is not null. Eg.3 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) DECLARE @c INT SET @c = 123456 SELECT COALESCE(@a, @b, @c) --O/P 123456 DECLARE @a INT DECLARE @b DATETIME SET @a = 2 SET @b = GETDATE() SELECT COALESCE (@a, @b) --O/P 1900-01-03 00:00:00.000 if we set SET @a = 0 --O/P 1900-01-01 00:00:00.000 if we set, it increments month and date SET @a = 35 --O/P 1900-02-05 00:00:00.000 |
<head runat="server">
<title></title>
<style type="text/css">
.table
{
background-image: url('Images/buttonbg.png');
background-repeat: repeat;
}
.buttonBg
{
background-color: Silver;
background-image: none;
border-style: solid;
border-width: 1;
border-color: #c63 #930 #930 #c63;
}
.textbox_username
{
background: #ffffff url('images/icon_username.png') no-repeat;
background-position: 1 1;
padding-left: 19px;
border: 1px solid #999999;
border-top-color: #CCCCCC;
border-left-color: #CCCCCC;
color: #333333;
font: 90% Verdana, Helvetica, Arial, sans-serif;
font-size: 12px;
height: 20px;
}
.textbox_password
{
background: #ffffff url('images/icon_password.png') no-repeat;
background-position: 1 1;
padding-left: 19px;
border: 1px solid #999999;
border-top-color: #CCCCCC;
border-left-color: #CCCCCC;
color: #333333;
font: 90% Verdana, Helvetica, Arial, sans-serif;
font-size: 12px;
height: 20px;
}
.button
{
border: 1px solid #999999;
border-top-color: #CCCCCC;
border-left-color: #CCCCCC;
background-color: white;
color: #333333;
font: 90% Verdana, Helvetica, Arial, sans-serif;
font-size: 11px;
-moz-border-radius: 3px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="table">
<tr>
<td colspan="4">
<asp:Label ID="lblUserLogOn" runat="server" Text="User Log On" Style="font-size: smaller;
font-family: Verdana; font-weight: bolder;"></asp:Label>
</td>
</tr>
<tr>
<td rowspan="3">
<img src="Images/Security.PNG" style="width: 80px; height: 80px;" alt="" />
</td>
<td>
<asp:Label ID="lblUserName" runat="server" Text="User Name" Style="font-size: x-small;
font-family: Verdana; font-weight: bold;"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtUserName" runat="server" CssClass="textbox_username" TabIndex = "1" ></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblPassword" runat="server" Text="Password" Style="font-size: x-small;
font-family: Verdana; font-weight: bold;"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtPassword" runat="server" CssClass="textbox_password" TabIndex = "2"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" style="text-align:right; padding-right:2px;">
<asp:Button ID="btnLogOn" runat="server" Text="LogOn" CssClass="buttonBg"
TabIndex = "3" onclick="btnLogOn_Click"/>
</td>
</tr>
<tr>
<td colspan="3" style="text-align:right; padding-right:2px;">
<asp:Label ID="lblInvalid" runat="server" Text="Incorrect username or password." style="color:#FF0000;"></asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
public partial class SimpleLoginTemplate_LogOn : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//ClearLogonCounter();
switch (btnLogOn.Enabled)
{
case false:
break;
case true:
btnLogOn.Enabled = NumberOfLogonAttemps() <= 5 ? true : false;
break;
}
}
protected void btnLogOn_Click(object sender, EventArgs e)
{
AddOrCountLogonAttempt();
if (NumberOfLogonAttemps() > 5)
{
if (!lblInvalid.Text.Trim().Equals("User has been locked for 5 minutes."))
//If the attempt is > 5 Lock the user for 5 min
//After that the userName cleared from the cache
Cache.Insert(txtUserName.Text.Trim(), (int)Cache[txtUserName.Text.Trim()], null, DateTime.Now.AddMinutes(5), TimeSpan.Zero);
lblInvalid.Text = "User has been locked for 5 minutes.";
btnLogOn.Enabled = false;
}
else
{
btnLogOn.Enabled = true;
switch (LogOn())
{
//Clear the count if user logon correctly
case true:
ClearLogonCounter();
break;
case false:
break;
}
}
}
private bool LogOn()
{
lblInvalid.Text = "Incorrect username or password.";
return false;
}
#region "Brute force protect"
//http://madskristensen.net/post/Brute-force-protect-your-website.aspx
private int NumberOfLogonAttemps()
{
if (Cache[txtUserName.Text.Trim()] == null)
return 0;
//txtNoOfTries.Text = Convert.ToString( Cache[txtUserName.Text.Trim()]);
return (int)Cache[txtUserName.Text.Trim()];
}
private void ClearLogonCounter()
{
if (Cache[txtUserName.Text.Trim()] != null)
{
Cache.Remove(txtUserName.Text.Trim());
}
}
private void AddOrCountLogonAttempt()
{
if (Cache[txtUserName.Text.Trim()] == null)
{
//NoAbsoluteExpiration -- item should never expire
// Sliding expiration means we reset the X seconds after each request.
//http://wiki.asp.net/page.aspx/655/caching-in-aspnet/
Cache.Insert(txtUserName.Text.Trim(), 1, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(1));
}
else
{
int tries = (int)Cache[txtUserName.Text.Trim()];
Cache[txtUserName.Text.Trim()] = tries + 1;
}
}
#endregion
}
//public int UserId = 10; //Error
Student s1 = new Student();
Student s2;
s1 = s2;
Response.Write(s1.PUserId);
Response.Write(s2.PUserId);
Student s1;
s1.Address = "Savadipalayam";
Student s1;
Response.Write(s1.PUserId)
Student s1;
s1.PUserId = 10;
Response.Write(s1.PUserId)
public Student(int UserId, int ZipCode, double Salary, string Address)
{
this.UserId = UserId;
this.ZipCode = ZipCode;
this.Salary = Salary;
//this.Address = Address;
}
Student s2 = null;
DateTime dt = null;
Student1 s3;
Response.Write(s3.i);
public struct Student1
{
public int i;
public string j;
}
Student1 s3 = new Student1();
Response.Write(s3.i); //O/P = 0
Response.Write(s3.j); //O/P = null
public Student(Student stud)
{
this.UserId = stud.UserId;
this.ZipCode = stud.ZipCode;
this.Salary = stud.Salary;
//Field 'StructTest.Student.Address' must be fully assigned before control is returned to the caller
//this.Address = stud.Address;
this.Address = stud.Address;
}
public void sub()
{
}
public static string multiply()
{
return Send();
}
public static string Send()
{
//An object reference is required for the non-static field, method, or property 'StructTest.Student.Address'
//Address = "Mottur Road";
//return Address;
Street = "Mottur Road";
return Street;
}
// Override the ToString method so the value appears in text
public override string ToString()
{
return String.Format("({0},{1})", Street, Address);
}
public int PUserId
{
get { return UserId; }
set { UserId = value; }
}
struct Student : IOne, ITwo
{
int UserId;
//public int UserId = 10; //Within a struct declaration, fields cannot be initialized unless they are declared as const or static.
public static string Street = "Savadipalayam";
int ZipCode;
double Salary;
public string Address;
//Structs cannot contain explicit parameterless constructors(Default constructor)
/* public Student()
{
this.UserId = 10;
this.ZipCode = 637101;
this.Salary = 2000;
this.Address = "Idappadi";
}
*/
// all the members of the struct has to be initialized in this way
public Student(int UserId, int ZipCode, double Salary, string Address)
{
this.UserId = UserId;
this.ZipCode = ZipCode;
this.Salary = Salary;
this.Address = Address;
}
// struct can also have copy constructor but have to be fully initialzed
public Student(Student stud)
{
this.UserId = stud.UserId;
this.ZipCode = stud.ZipCode;
this.Salary = stud.Salary;
//Field 'StructTest.Student.Address' must be fully assigned before control is returned to the caller
//this.Address = stud.Address;
this.Address = stud.Address;
}
public int PUserId
{
get { return UserId; }
set { UserId = value; }
}
//new protected member declared in struct
//protected void add()
//{
//}
//Struct can contain methods. It may be static, and can call only another static methods.
public void sub()
{
}
public static string multiply()
{
return Send();
}
public static string Send()
{
//An object reference is required for the non-static field, method, or property 'StructTest.Student.Address'
//Address = "Mottur Road";
//return Address;
Street = "Mottur Road";
return Street;
}
//The modifier 'abstract' is not valid for this item
//public abstract void Return() { }
#region ITwo Members
void ITwo.add()
{
throw new System.NotImplementedException();
}
public void sum()
{
throw new System.NotImplementedException();
}
#endregion
#region IOne Members
void IOne.add()
{
throw new System.NotImplementedException();
}
void IOne.sum()
{
throw new System.NotImplementedException();
}
#endregion
}
//Nested Structures
public struct outer
{
public int i;
public struct inner
{
public int j;
}
}
outer.inner inn = new outer.inner();
inn.j = 10;
namespace StructTest
{
public struct Student
{
int id;
int zipcode;
double salary;
}
}