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.
Dotnet, DotnetCore, Azure, C#,VB.net, Sql Server, WCF, MVC ,Linq, Javascript and Jquery
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.
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 = objEmp.Take
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 = objEmp.Take
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;
});
});
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;
});
});
27 January 2014
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
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
19 January 2014
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.
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.
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.
ActionResult in MVC 4
ActionResult in MVC 4
The ActionResult class is the base class for action results.
An action method responds to user input by performing work and returning an action result. An action result represents a command that the framework will perform on behalf of the action method.
The following types derive from ActionResult:
ContentResult - Represents a user-defined content type that is the result of an action method.
EmptyResult - Represents a result that does nothing, such as a controller action method that returns nothing.
FileResult - Represents a base class that is used to send binary file content to the response.
HttpUnauthorizedResult - Represents the result of an unauthorized HTTP request.
JavaScriptResult - Sends JavaScript content to the response.
JsonResult - Represents a class that is used to send JSON-formatted content to the response.
RedirectResult - Controls the processing of application actions by redirecting to a specified URI.
RedirectToRouteResult - Represents a result that performs a redirection by using the specified route values dictionary.
ViewResultBase - The ViewResultBase class is the abstract base class for both the ViewResult and PartialViewResult classes. The class contains methods for finding the view to be rendered and for executing the result. This class also contains properties that identify the view to be rendered, the name of the view, view data, temporary data, and a collection for view engines for the application.
Reference
http://msdn.microsoft.com/en-us/library/system.web.mvc.actionresult(v=vs.118).aspx
The ActionResult class is the base class for action results.
An action method responds to user input by performing work and returning an action result. An action result represents a command that the framework will perform on behalf of the action method.
The following types derive from ActionResult:
ContentResult - Represents a user-defined content type that is the result of an action method.
EmptyResult - Represents a result that does nothing, such as a controller action method that returns nothing.
FileResult - Represents a base class that is used to send binary file content to the response.
HttpUnauthorizedResult - Represents the result of an unauthorized HTTP request.
JavaScriptResult - Sends JavaScript content to the response.
JsonResult - Represents a class that is used to send JSON-formatted content to the response.
RedirectResult - Controls the processing of application actions by redirecting to a specified URI.
RedirectToRouteResult - Represents a result that performs a redirection by using the specified route values dictionary.
ViewResultBase - The ViewResultBase class is the abstract base class for both the ViewResult and PartialViewResult classes. The class contains methods for finding the view to be rendered and for executing the result. This class also contains properties that identify the view to be rendered, the name of the view, view data, temporary data, and a collection for view engines for the application.
Reference
http://msdn.microsoft.com/en-us/library/system.web.mvc.actionresult(v=vs.118).aspx
Subscribe to:
Posts (Atom)
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...
-
ViewBag, ViewData, TempData and View State in MVC ASP.NET MVC offers us three options ViewData, ViewBag and TempData for passing data from...
-
· Azure Service Fabric is Microsoft’s Platform-as-a-Service (PaaS) and is used to build and deploy microservices-based cloud applicat...
-
// Export Datatable to Excel in C# Windows application using System; using System.Data; using System.IO; using System.Windows.Forms; ...