ASP.NET’s Data Storage Objects

Introduction

In this brief tutorial, we’ll look at five different ASP.NET objects we can use to store data. Two of these, the Application and Session objects, should be pretty familiar to anyone coming from ASP. The other three, Context, Cache and ViewState are brand new to ASP.NET. Each object is ideal under certain conditions, varying only in scope (that is the length of time data in them exists, and the visibility of the data), and it’s a complete understanding of this variation that we are after. Also, most of these objects have alternatives that we’ll briefly look at.

Scope

Since the only difference between all these objects is their scope, it’s important to have a clear understanding of exactly what this means. Within the context of this discussion, scope refers to how data inside these objects (or the objects themselves) live for. For example, a user’s user ID should exist until he or she logs out, but the new password he or she enters should only exist for the life of the individual request. Scope also refers to the visibility of the data. There’re only two types of visibility, data is either available throughout the entire application or for a specific user. An example of this would be the SMTP server name to use when sending an email which should be globally accessible, whereas an email address is specific to an individual user.

Commonality

The above objects all expose their data-storage capabilities via something similar to a HashTable. In other words, getting or setting information into or out of any of them is very similar. All can hold any object as a value, and while some can have an object as a key, some can only have a string – which is what you’ll be using 98% of the time. For example:
//C#
//setting
Application.Add("smtpServer", "127.0.0.1");
Context.Items.Add("culture", new CultureInfo("en-CA"));
Session.Add("userId", 3);

//getting
string smtpServer = (string)Application["smtpServer"];
CultureInfo c = (CultureInfo)Context.Items["culture"];
int userId = (int)Session["userId"];
'VB
'setting
Application.Add("smtpServer", "127.0.0.1")
Context.Items.Add("culture", new CultureInfo("en-CA"))
Session.Add("userId", 3)

'getting
Dim smtpServer as string = cstr(Applciation("smtpServer"))
Dim c as CultureInfo = ctype(Context.Items("culture"), CultureInfo)
Dim userId as integer = cint(Session("userId"))

HTTPApplication

The Application object is an instance of the System.Web.HTTPApplication class. Typically you would set values in the Application object on theApplication_Start event of the Global.Asax or the BeginRequest event of a HttpModule. Logical values to store would be the SMTP server to use when sending out emails, the administrator’s contact email address, and any other value which you might globally need across all users/requests.

Worker Process Recycling and Web Farms

While it’s correct to say that data stored in the Application exists as long as the website is up, it would be incorrect to simply leave it at that. Technically, the data in the Application exists as long as the worker process (the actual aspnet.exe if you will) exists. This can have severe repercussion and isn’t a mere technicality. There are a number of reasons why the ASP.NET worker process recycles itself, from touching the web.config, to being idle, or consuming too much RAM. If you use the Application object by setting values to it in the Application_Start event and only read from it in your classes/pages, then no problem. When the worker process recycles itself, Application_Start will fire and your values will be properly set. However, if you set a value in the Application_Start event, update the value later on, when the worker process recycles itself, it’ll default back to the Application_Start value.
Something else to keep in mind is that data stored in the Application object is specific to a computer and can’t be (easily) shared across web farms.

Alternatives

The Application object might have been quite useful in Classic ASP, but a number of better alternatives (in my opinion) are now available.

Web.Config

If you require values that are readonly/constants (such as our SMTP server example), consider using the web.config. Unlike values in the Application, theweb.config can be easily and quickly changed. You can do considerably advanced things in the web.config, check out my tutorial on Creating Custom Configurations.

Constants

You can leverage the Object Oriented nature of ASP.NET and create a utility class with public constants. To be honest, unless you are just mocking something up, I’m not sure why you would ever use this method over using the web.config. It really gives you nothing except for headaches in the long run.

HttpCache + (XML | DB)

While custom sections in the web.config is definitely the way to go for read-only values, what can we do about read/write values and avoid worker process recycling? The answer is to store values in an XML file or database. While you could do the same thing in classic ASP, you can now leverage a new storage object, the HttpCache (which we’ll cover next) to avoid any major performance penalty you would otherwise have. This also avoids any web farm issues you’d have with the HttpApplication class.

Conclusion

It is my opinion that the usefulness of the HttpApplication class, from a data storage point of view, is greatly diminished in ASP.NET. Powerful custom configuration sections in the web.config are a far more elegant and flexible solution for read-only values. Using XML files or a database is ideal for read/write values and web farms, and when combined with the HttpCache object leaves poor’ol Application in the dust.

HttpCache

The HttpCache (cache) class is the first new storage class we’ll look at. It’s also the most unique. There are a couple of reasons for this uniqueness. First,HttpCache isn’t really a storage mechanism, it’s mostly used as a proxy to a database or file to improve performance. Secondly, while you read values from the cache by specifying a key, you have a lot more control when inserting, such as how long to store the data, triggers to fire when the data is removed, and more. When accessing values from the cache, there’s no guarantee that the data will be there (there are a number of reasons why ASP.NET would remove the data), as such, the typical way to use the cache is as follows:
private DataTable GetStates() {
   string cacheKey = "GetStates";  //the key to get/set our cache'd data

   DataTable dt = Cache[cacheKey] as DataTable;
   if(dt == null){

      dt = DataBaseProvider.GetStates();
      Cache.Insert(cacheKey, dt, null, DateTime.Now.AddHours(6), TimeSpan.Zero);
   }

   return dt;
}
Private Function GetStates() As DataTable
   Dim cacheKey As String = "GetStates" 'the key to get/set our cache'd data

   Dim dt As DataTable = CType(Cache(cacheKey), DataTable)
   If dt Is Nothing Then

      dt = DataBaseProvider.GetStates()
      Cache.Insert(cacheKey, dt, Nothing, DateTime.Now.AddHours(6), TimeSpan.Zero)
   End If

   Return dt
End Function
First thing we do is declare a cacheKey [line: 2] which we’ll use when retrieving and storing information from and into the cache. Next, we use the key and try to get the value from the cache [line: 3]. If this is the first time we called this method, or if the data has been dropped for whatever reason, we’ll getnull/Nothing [line: 4]. If we do get null/Nothing, we hit the database via the fictional DataBaseProvider.GetStates() call [line: 5] and insert the value into the Cache object with our cacheKey [line: 6]. When inserting, we specify no file dependencies and we want the cache to expire in six hours from now.
The important thing to note about the code above is that the processing-heavy data-access code DataBaseProvider.GetStates() is skipped when we find the data in the cache. In this example, the real storage mechanism is a fictional database; HttpCache simply acts as a proxy. It’s more likely that you’ll want to retrieve information based on parameters, say all the states/provinces for a specific country, this is easily achieved via the VB.NET code below:
Private Function GetStates(ByVal countryId As Integer) As DataTable
   Dim cacheKey As String = "GetStates:" & countryId.ToString()
   'the key to get/set our cache'd data

   Dim dt As DataTable = CType(Cache(cacheKey), DataTable)
   If dt Is Nothing Then

      dt = DataBaseProvider.GetStates(countyId)
      Cache.Insert(cacheKey, dt, Nothing, _
            DateTime.Now.AddHours(6), TimeSpan.Zero)
   End If

   Return dt
End Function
All I really had to do was add the parameter to my cacheKey, which means if I first try and get the states for country 3, my cacheKey will look likeGetStates:3 and I’ll need to hit the database. Subsequent requests for GetStates:3 will avoid the DB call. However, when I ask for states of country 2, mycacheKey looks like GetStates:2, which, when first called will hit the DB, and subsequently retrieve the right values.

SessionState

Sessions exist for the lifetime of a specific user’s visit, or until the session timeouts, or you remove it. One thing you probably heard a lot in your ASP days was “don’t use sessions”, or “sessions are evil“. The problem with sessions is how easy they were to use combined with their potentially serious impact on performance, since they were stored in memory. In ASP.NET, you have the choice of storing sessions in memory, in a special service that is part of ASP.NET, or in a SQL database. Having this choice, and using it wisely, makes using sessions in ASP.NET a good thing.
You control where sessions are stored via the web.config‘s sessionState element, specifically the mode attribute:
<system.web>
   <!-- can use a mode of "Off", "InProc", "StateServer" or "SQLServer". 
                                            These are CaSe-SeNsItIvE -->
    <sessionState mode="InProc" />
   ...
 </system.web>

InProc

InProc means that sessions are stored inside the ASP.NET worker process – this is pretty much how sessions in classic ASP work. Storing data this way can lead to performance issues (since it’s using your web server’s RAM), and also has all the issues associated with worker process recycling that plagues read/write usage of the Application object. However, wisely used for the right website, such as keeping track of a user ID in a small/medium sized site, they are extremely performing and an ideal solution. You don’t need to do anything special, other than setting the sessionState‘s mode to “InProc“.

StateServer

StateServer is a service which is off by default. You can enable it by going into “Administration” –> “Services” and right-clicking on “ASP.NET State Service” (go to Properties and select “automatic” if you want it to start when Windows starts). When your sessionState is in StateServer, sessions aren’t stored in the ASP.NET worker-process, which avoids the worker process recycle problem. Additionally, two or more separate web servers can access a singleStateServermeaning session state will automatically be shared across a web farm.
There are two very important things to keep in mind when using StateServer. Firstly, it isn’t as fast as having the data stored directly in the ASP.NET worker process. This can be easily resolved by smarty using the HttpCache class. Secondly, data stored in the StateServer must be serializable. Strings, ints and most built-in types are mostly all automatically serializable. Custom classes can typically be marked by the System.SerializableAttribute attribute:
[Serializable()]
public class User {
   private int userId;
   private string userName;
   private UserStatus status;

   public int UserId {get { return userId; } set { userId = value; }}
   public string UserName {get { return userName; }set { userName = value; }}
   public UserStatus Status {get { return status; }set { status = value; }}

   public enum UserStatus {
      Invalid = 0,
      Valid = 1
   }
}
To use StateServer, you must specify both the SessionState mode, as well as the address of the StateServer via stateConnectionString.StateServer runs on port 42424 by default, so the example below connects to the state server on the local machine.
<sessionState mode="StateServer" 
       stateConnectionString="tcpip=127.0.0.1:42424" />

SQL Server

Using SQL Server is much like using StateServer. Both require data to be serializable, both are overall slower (but won’t cause the entire app to have performance issues), and both can be accessed by multiple web servers. The difference between the two is, well obviously, one uses the StateServer service while the other uses SQL Server. This has pretty wide implications. By storing your sessions in SQL Server, you can take advantage of multiple databases, load balancing, and fault-tolerance. You also have to pay big money for it.
Enabling SQL Server is much like StateServer: set the mode to SQLServer and specify the connection via the sqlConnectionString attribute:
<sessionState mode="SQLServer" 
   stateConnectionString="Initial Catalog=Session;server=localhsot;uid=sa;pwd=;" />
Additionally, you must run a script to create the database. This script is located in system drive\WINNT\Microsoft.NET\Framework\version\InstallSqlState.sql, for example: C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\InstallSqlState.sql on my computer (Windows instead of WinNT because I’m using XP). Simply run it, and you’re ready to go.

Alternatives

Considering how flexible and powerful sessions are now implemented, there aren’t any great alternatives, but here’s a list anyways:

Cookies

Cookies behave pretty much the same as sessions, but are stored on the user’s computer. This makes them horrible for large data or sensitive information. Additionally, not everyone has cookies enabled (truer and truer with increased privacy concerns). On the flip side, unlike sessions, you can easily use cookies to store information across multiple visits. Cookies used to be a great place to store information you didn’t really care if it was kept or not, like a username (hey, if it’s there great, the user has one less thing to type. If not, oh well), but most browsers do a better job of this than you can with cookies.

Querystring

The querystring used to be considered a possible alternative to sessions. But again, you can’t store large/complex data in them or sensitive information. Additionally, it’s a real maintenance nightmare.

URL Rewriting

I won’t go into details about URL Rewriting (check out my Localization Tutorial where I explain it briefly). I like using URL Rewriting for simple things such as localization because it needs far less maintenance than using the Querystring, and looks really professional. But just like the cookie or querystring alternative, this won’t cut it most of the time.

Conclusion

As you can probably tell, sessions in ASP.NET are a lot more useful than they were in classic ASP. The main reason being that you have a good alternative to storing them in memory. But with that comes a bunch of other alternatives: sharing them across web farms (state server/ SQL Server), load balancing (SQL Server), fault tolerance (SQL Server), and a couple of other nice things. Another great feature of sessions in ASP.NET is that they’ll work even if the user’s browser doesn’t support session cookies. This is done by automatically placing the session ID in the querystring, instead of a session cookie. Sure, I just said the QueryString was a bad alternative, but this is automatically done, maintenance free for us!

ViewState

You’ve probably heard a lot about viewstate and ASP.NET. It’s one of the many big-things in ASP.NET. Thankfully (or not depending on how you look at it), I don’t plan on going into any details about it, aside for how you can use it as a storage mechanism. For all its glory, the ViewState is a hidden form field. You probably used hidden form fields frequently in classic ASP to track a value between one page and the posted-to page…our usage of the ViewState is no different. Its scope, as you can probably imagine, is for a single user, from one page to another.
What some people don’t realize about the viewstate is that it isn’t some mystical thing that they aren’t allowed to touch. You can easily store and retrieve values from and into it:
'VB
Dim pollId As Integer

If Not Page.IsPostBack Then
   pollId = getPollId()
   viewstate.Add("pollId", pollId)
Else
   pollId = CInt(viewstate("pollId"))
End If
//C#
int pollId;
if(!Page.IsPostBack){
   pollId = getPollId();
   ViewState.Add("pollId", pollId);
}else{
   pollId = (int)ViewState["pollId"];
}
In the above example, we call an expensive function called getPollId() [line: 4] and store the result in the ViewState. When the page does a postback, we can avoid that same call and simply retrieve the value from the ViewState.

Conclusion

When it comes down to doing similar things to the above example, using the viewstate is a great solution. You could always simply use a hidden form field, or the Page.RegisterHiddenField() which creates a hidden field for you, and use Request.Form, but I wouldn’t call those alternatives since they are pretty much the exact same. Of course, if you need to store objects, large data, or sensitive information, sessions might be what you want.

HttpContext

Personally, I think I’ve kept the best for last. I think the HttpContext is probably the least well known of the ASP.NET data storage objects, and while it won’t solve all your problems, it’s definitely useful in a number of instances. Of all the objects, it has the most limited scope – it exists for the lifetime of a single request (remember the viewstate exists for the life time of two requests, the original one and the postback). The most common use of HTTPContext is to store data into it on Application_BeginRequest, and access it as need be throughout your page, user controls, server controls and business logic. For example, say I built a portal infrastructure which, at the start of each request, created a portal object which identified which portal the request was in and which section within the portal. We’ll skip all the portal code, but your Application_BeginRequest would probably look something like this:
protected void Application_BeginRequest(Object sender, EventArgs e) {
   Context.Items.Add("portal", new Portal(Request.Url));
}
You can then access the instance of the portal throughout the request via:
'VB
Dim portal As Portal= CType(Context.Items("portal"), Portal)
You shouldn’t use HttpContext to pass information between a Page and UserControls, instead use strongly-typed controls, interfaces and base classes. You should use HttpContext to store request-specific information that you want to make accessible throughout all layers of your application.
Another example of using the HttpContext is for a simple performance monitor:
'VB
'Fires when the request is first made

Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
   Context.Items.Add("StartTime", DateTime.Now)
End Sub

'fires at the end of the request
Sub Application_EndRequest(ByVal sender As Object, ByVal e As EventArgs)
   Dim startTime As DateTime = CDate(Context.Items("StartTime"))
      If DateTime.Now.Subtract(startTime).TotalSeconds > 4 Then
        'Log this slow request
      End If
End Sub
When the application starts, we store the current time in the context [line: 4]. When the application ends, we retrieve that value [line: 9], compare it to the current time [line: 10], and if it’s greater than a certain threshold (4 seconds in this case), we could log some information which might help us identify bottlenecks.

Conclusion

There are two reasons I like HttpContext so much. Firstly, because I find it relatively unknown and unused. Even Microsoft’s documentation doesn’t do justice to how useful it can be. Secondly, because it’s just really useful and handy to have. There are alternatives to using it, the Session is probably the most obvious. If you find yourself unsure of which to use, ask yourself if the information is specific to a user (Session) or specific to a request (HttpContext).

Going the extra mile by strongly-typing

If there’s one thing I dislike about all the above mechanisms, it is that they return objects. At worst, this can lead to run-time errors; at best, it’ll give you maintenance problems. The solution is strong-type the object. Look at the code below:
Dim currentUser as User = ctype(Session("currentUser"), User)
If currentUser is Nothing Then
   'do something, what?
End if
So, what’s wrong with the above? First of all, we need to cast the Session object to CurrentUser, we need to specify the key “currentUser“, and we need to add error handling. The problem is we need to do this every time we access the value. We might do it once in our Page, once in each of ourUserControls, and once in some business logic. That’s not very encapsulated, is it? Even changing the key from “currentUser” to “User” would require a potentially dangerous search and replace.
The solution is to wrap this code into a Shared/static property:
Public Shared ReadOnly Property CurrentUser() As User
   Get
      Dim _currentUser As User = _
          CType(HttpContext.Current.Session("CurrentUser"), User)
      If _currentUser Is Nothing Then
         _currentUser = New User
         HttpContext.Current.Session.Add("CurrentUser", _currentUser)
      End If
      Return _currentUser
   End Get
End Property
I’ll explain the whole HttpContext.Current thing in the next section, for now, just assume that it isn’t there. The code hasn’t changed much. Basically, we’ve added some error handling…but this code is encapsulated, look at how much cleaner accessing the value is:
Dim currentUser as User = User.CurrentUser
There’s only one hard coded “currentUser” key, and that’s in the User class. We aren’t having to cast anymore and we have full intellisense support.
Strongly-typing only really makes sense for objects that have a wide scope. The above example is a really good one. But if you find yourself frequently accessing values from one of the above storage objects and repeatedly casting it and doing error checking, consider the above solution.

Accessing these objects from the business layer

All of the above objects are readily available to you when programming in code-behind files. You can simply type “ViewState.Add("xxx", "yyy")“, and it works. But really, that works because those are properties of the Page class (go ahead and type Page.V, you’ll see the intellisense will list ViewState).
Not all your code will be written in code-behind. You’ll likely have a bunch of classes (users, groups, portals, sections, roles…), validation objects, and utility classes which don’t inherit from System.web.UI.Page. Does that mean all those objects are lost to you? Not all all. Within a web request, all of those objects are available via System.Web.HttpContext.Current. The above example shows how you can use it. Just remember that HttpContext.Current can return null if your code isn’t being executed from within a web request.
It’s possible to access the HttpCache object via either the above mentioned method or HttpRuntime.Cache – enabling you to utilize the power ofHTTPCache in non-web requests.

Conclusion

Hopefully, this tutorial has given you both an overview of these storage objects, as well as some insight to help you chose the right one for the job. It’s important to note that we only looked at the storage capabilities of these objects, some of them actually do a lot more (ViewState and HttpContext). However, you can use their storage capabilities without knowing anything more about them. Always ask yourself how the data will be used. Is it specific for all users (ApplicationHttpCache), or a specific user (Session, ViewState, Context)? Does it have a long life-time (ApplicationHttpCacheSession) or a short one (ViewState, Context)?
Remember to consider alternatives, specifically when looking at the Application object. There are some cases where a particular alternative might be the best tool for the job, even if overall it isn’t a great thing to use. And make your life easier, and encapsulate access to these storage objects when it makes sense to do so.
Advertisements

INNER JOIN With WHERE

Introduction

The purpose of this article is to show the difference between the On clause and Where clause when using with left join in SQL Server.

Background

I have been working on SQL Server databases for more than 5 years and I was not aware of the difference between the On clause and Where clause when used with left join. Once I asked this question to our DBA and he said that there is some difference but he was not be able to explain the difference. Then I started exploring this topic myself and got some interesting observations of these two clauses when used with left join. So today I decided to share my knowledge so that everyone could benefit from my work.

Using the code

Today I will explain the difference between the On clause and Where clause when used with left join in SQL Server. When the On clause is used in an outer join, the outer table will have no effect on this On clause and all rows from the outer table will be returned and the On clause determines which rows of the subordinate table joins to the outer table. Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output. It’s difficult to understand this from the above definition, so let’s try to understand this difference with an example. Suppose we have two tables Departments (deptId, deptName) and Employees (empID, DeptID, EmpName, Salary) and deptiD is the foreign key of the department table. An employee can have only one department where as a department can have many employees.

We have this sample data in the Department table:

Collapse | Copy Code

DeptId      DeptName

———– —————

1           HR

2           Payroll

3           Admin

4           Marketing

5           HR & Accounts

 

(5 rows)

And here is the sample data for the Employees table:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ———————

1           John            1           5000.00

2           Albert          1           4500.00

3           Crain           2           6000.00

4           Micheal         2           5000.00

5           David           NULL        34.00

6           Kelly           NULL        457.00

7           Rudy            1           879.00

8           Smith           2           7878.00

9           Karsen          5           878.00

10          Stringer        5           345.00

11          Cheryl          NULL        NULL

 

(11 rows)

Case 1

Suppose we are asked to show all the employees and their relevant departments, then we would write a query like this:

Collapse | Copy Code

select * from employees e

<p>left join departments d on e.deptid = d.deptid</p>

 

(query1)

and the result of this query is:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary             DeptId      DeptName

———– ————— ———– ——————— ———– —————

1           John            1           5000.00               1           HR

2           Albert          1           4500.00               1           HR

3           Crain           2           6000.00               2           Payroll

4           Micheal         2           5000.00               2           Payroll

5           David           NULL        34.00                 NULL        NULL

6           Kelly           NULL        457.00                NULL        NULL

7           Rudy            1           879.00                1           HR

8           Smith           2           7878.00               2           Payroll

9           Karsen          5           878.00                5           HR & Accounts

10          Stringer        5           345.00                5           HR & Accounts

11          Cheryl          NULL        NULL                  NULL        NULL

 

(11 rows)

(Table 1)

Here you can see that all rows from the Employees table are listed along with their department, if they have a department; otherwise, deptid and deptname are null.

Case 2

Let’s say we are asked to show the list of all employees and the department name of only those employees who have “HR” or “HR & Accounts” department; then we would write a query like this:

Collapse | Copy Code

select * from employees e

left join departments d on e.deptid = d.deptid

and ( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 2)

and the result of this query is:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary             DeptId      DeptName

———– ————— ———– ——————— ———– —————

1           John            1           5000.00               1           HR

2           Albert          1           4500.00               1           HR

3           Crain           2           6000.00               NULL        NULL

4           Micheal         2           5000.00               NULL        NULL

5           David           NULL        34.00                 NULL        NULL

6           Kelly           NULL        457.00                NULL        NULL

7           Rudy            1           879.00                1           HR

8           Smith           2           7878.00               NULL        NULL

9           Karsen          5           878.00                5           HR & Accounts

10          Stringer        5           345.00                5           HR & Accounts

11          Cheryl          NULL        NULL                  NULL        NULL

 

(11 rows)

Table (2)

You can notice here that only the same number of rows are returned as in query 1, but here the department of only those employees are listed whose department name is “HR” or “HR & Accounts”. As you can see, “Crain”,” Micheal”, and “Smith” have department “Payroll” (see table 1), but it’s not listed in the above result set because here we applied the filter in the “On clause”; hence only those rows become part of the join that has department name “HR” or “HR & Employee”, and as a result, all other employees who don’t have department name “HR” and “HR & Accoutns” show their department name as null. This is an example of the On clause in left join.

Case 3

What if we are asked to show only those employees who have their department name “HR” or “HR & Accounts”? We would write a query like this:

Collapse | Copy Code

select * from employees e

left join departments d on e.deptid = d.deptid

where ( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 3)

and the result of this query is:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary             DeptId      DeptName

———– ————— ———– ——————— ———– —————

1           John            1           5000.00               1           HR

2           Albert          1           4500.00               1           HR

7           Rudy            1           879.00                1           HR

9           Karsen          5           878.00                5           HR & Accounts

10          Stringer        5           345.00                5           HR & Accounts

 

(5 rows)

Table (3)

You can see here that only those employees are listed who have their department “HR” or “HR & Accounts”, so what we did here is the same query as query 1. You can check the result of query 1 in table 1, and then we just applied a filter in the Where clause to filter the rows and to return only those employees who have their department name as “HR” or “HR & Accounts”. This is an example of Where clause in left join.

Now you have seen all three cases and can see how these differ from each other. This was an example of a (1:1) one to one table as one employee can have only one department. Let’s execute the above 3 cases with (1:M), a one to many table relationship where one department can have multiple employees (or more than one employee can be in one department).

Case 4

Let’s suppose our requirement is to get all departments and the employees listed in these departments, then we would write a query like:

Collapse | Copy Code

select * from departments d

left join employees e on e.deptId = d.deptId

(query 4)

It will return all rows from the Department table and only those rows from the Employees table that have a department. The result will look like:

Collapse | Copy Code

DeptId      DeptName        EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ————— ———– ———————

1           HR              1           John            1           5000.00

1           HR              2           Albert          1           4500.00

1           HR              7           Rudy            1           879.00

2           Payroll         3           Crain           2           6000.00

2           Payroll         4           Micheal         2           5000.00

2           Payroll         8           Smith           2           7878.00

3           Admin           NULL        NULL            NULL        NULL

4           Marketing       NULL        NULL            NULL        NULL

5           HR & Accounts   9           Karsen          5           878.00

5           HR & Accounts   10          Stringer        5           345.00

(10 rows)

(Table 4)

You can see in the result that all departments are listed, even those that don’t have any employees, as “Admin” and “Marketing”.

Case 5

But what if we are asked to show all departments and only those employees who have department name “HR or “HR & Accounting”. Then we would write a query like:

Collapse | Copy Code

select * from departments d

left join employees e on e.deptId = d.deptId and

( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 5)

It will show the following records:

Collapse | Copy Code

DeptId      DeptName        EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ————— ———– ———————

1           HR              1           John            1           5000.00

1           HR              2           Albert          1           4500.00

1           HR              7           Rudy            1           879.00

2           Payroll         NULL        NULL            NULL        NULL

3           Admin           NULL        NULL            NULL        NULL

4           Marketing       NULL        NULL            NULL        NULL

5           HR & Accounts   9           Karsen          5           878.00

5           HR & Accounts   10          Stringer        5           345.00

 

(8 rows)

 

Table (5)

You can see in the result, this is the On clause in left join; it just return all rows from the Department table and only those rows from the Employee table that have department “HR” or “HR & Accounts”. We have employees in the Payroll department (Table 4 as a result of query 4) but it’s not listed in the above result set as we just put an extra condition in the “On clause” that the department name should be “HR” or “HR & Accounts”. That’s why only those rows were part of this join which have department name “HR” and “HR & Accounts”. Hence all rows from the Department table were returned and only those matching rows were returned from the Employees table who have their department as “HR” or “HR & Accounts”.

Case 6

What if we are asked to show only the “HR” and “HR & Accounts” departments along with their relevant employees? We would then write a query like this:

Collapse | Copy Code

select * from departments d

left join employees e on e.deptId = d.deptId

where ( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 6)

and the result of the above query would be:

Collapse | Copy Code

DeptId      DeptName        EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ————— ———– ———————

1           HR              1           John            1           5000.00

1           HR              2           Albert          1           4500.00

1           HR              7           Rudy            1           879.00

5           HR & Accounts   9           Karsen          5           878.00

5           HR & Accounts   10          Stringer        5           345.00

 

(5 rows)

 

(Table 6)

So what happened here? We just did a left join as we did in query 4, and you can see the result in table 4. And then we apply a filter on the result set to return only those rows that have deptNmae as “HR” or “HR&Accounts”. This is a Where clause in left join. Now you can see how it is different from the result of query 5 where we put this condition in the “On clause”.

There is no difference between the Where clause and On clause when used with inner join.

Reference : CodeProject.com

AdoHelper Class

using System;

using System.Data;

using System.Data.SqlClient;

namespace BlackBeltCoder

{
class AdoHelper : IDisposable
{

protected string _connString = null;

protected SqlConnection _conn = null;
protected SqlTransaction _trans = null;
protected bool _disposed = false;
public static string ConnectionString { get; set; }
public SqlTransaction Transaction { get { return _trans; } }

public AdoHelper()
{
_connString = ConnectionString;

Connect();

}

public AdoHelper(string connString)

{

_connString = connString;

Connect();

}

protected void Connect()

{

_conn = new SqlConnection(_connString);

_conn.Open();

}

public SqlCommand CreateCommand(string qry, CommandType type, paramsobject[]

args)
{
SqlCommand cmd = new SqlCommand(qry, _conn);
if (_trans !=  null)
cmd.Transaction = _trans;
cmd.CommandType = type;
for (int i = 0; i < args.Length; i++)
{

if (args[i] is string && i < (args.Length – 1))

{
SqlParameter parm = new SqlParameter();
parm.ParameterName = (string)args[i];
parm.Value = args[++i];
cmd.Parameters.Add(parm);
}
else if
(args[i] is SqlParameter)
{
cmd.Parameters.Add((SqlParameter)args[i]);
}
else throw
new
ArgumentException
(“Invalid number or type of
arguments supplied”
);
}
return cmd;
}

#region
Exec Members
public int ExecNonQuery(string qry, params object[] args)
{
using (SqlCommand cmd =CreateCommand(qry, CommandType.Text, args))
{
return cmd.ExecuteNonQuery();
}
}

public int ExecNonQueryProc(string proc, params object[] args)

{

using (SqlCommand cmd =
CreateCommand(proc, CommandType.StoredProcedure, args))

{

return cmd.ExecuteNonQuery();

}

}

public object ExecScalar(string qry,  params object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.Text, args))

{

return cmd.ExecuteScalar();

}

}

publicobject ExecScalarProc(string qry,paramsobject[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.StoredProcedure, args))

{

return cmd.ExecuteScalar();

}

}

public SqlDataReader ExecDataReader(string qry, params

object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.Text, args))

{

return cmd.ExecuteReader();

}

}

public SqlDataReader ExecDataReaderProc(string qry, params

object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.StoredProcedure, args))

{

return cmd.ExecuteReader();

}

}

 public DataSet ExecDataSet(string qry, params

object[] args)

{

using (SqlCommand cmd =
CreateCommand(qry, CommandType.Text, args))

{

SqlDataAdapter adapt = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapt.Fill(ds);

return ds;

}

}

public DataSet ExecDataSetProc(string qry, params

object[] args)

{

using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))

{

SqlDataAdapter adapt = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

adapt.Fill(ds);

return ds;

}

}

#endregion   

#region Transaction Members

public SqlTransaction BeginTransaction()

{

Rollback();

_trans = _conn.BeginTransaction();

return Transaction;

}

public void

Commit()

{

if (_trans !=
null
)

{

_trans.Commit();

_trans = null;

}

}

 public void Rollback()

{


if (_trans != null)

{

_trans.Rollback();

_trans = null;

}

}      

#endregion      

#region IDisposable Members

 public void Dispose()

{

Dispose(true);

GC.SuppressFinalize(this);

}

protected virtual void Dispose(bool disposing)

{

if (!_disposed)

{

if (disposing)

{

if (_conn != null)

{

Rollback();

_conn.Dispose();

_conn = null;

}

}

_disposed = true;

}

}    

#endregion

}

}