DOT NET: October 2010

When I first started using SQL Server as a novice, I was initially confused as to the differences between the SELECT statement, views, and stored procedures. They all seemed to perform more or less the same task (retrieve data), and I wanted to know the pros and cons of using each.
Why would SQL Server offer three different options to retrieve data from database? As a developer and new DBA, I took it upon myself to learn everything I could about these options, why they may be required, and when they should be used. This article is a result of my learning and experience, and explains the differences between SELECT statements, views, and stored procedures for the DBA or developer new to SQL Server. I hope you find this article useful.
As you read this article, if you choose, you can cut and paste the code into Query Analyzer I have provided in order to more fully understand and appreciate the differences between the SELECT statement, views, and stored procedures. I have divided this article into three parts to better explain this information.

Starting Notes
To get us started on learning the differences between the SELECT statement, views, and stored procedures, I need to mention the syscacheobjects system table. It is used to store information about compiled objects and their execution plans. The reason for this is because compiled SELECT statements, views, and stored procedures are stored here, and I have used this table to experiment and learn more about how these three different objects are stored and used by SQL Server. If you are not familiar with this system table, you might want to take a peek at it. It is stored in the master database, and can be viewed with Enterprise Manager or Query Analyzer.
If you choose to follow along with the examples in this article, you will want to run the DBCC FREEPROCCACHE command before each run. This command clears the syscacheobjects table of any current cached objects, and allows us to perform more accurate tests.
Now, let’s create a table and input a few rows in the table before we commence at taking a look at the differences between the SELECT statement, views, and stored procedures.

Create Sample Table
I assume you have a database you can use for this. If not, you will want to create one at this time. Now, we need to create a table for our experimentation.
Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)
Now, let’s add a few records in this table using this script:

Insert Into DummyTable1 Values (1, Replicate ('a',20))
GO
Insert Into DummyTable1 Values (2, Replicate ('b',20))
GO
Insert Into DummyTable1 Values (3, Replicate ('c',20))
GO
Insert Into DummyTable1 Values (4, Replicate ('d',20))
GO
Insert Into DummyTable1 Values (5, Replicate ('e',20))
GO
Insert Into DummyTable1 Values (6, Replicate ('f',20))
GO
Insert Into DummyTable1 Values (7, Replicate ('g',20))
GO
Insert Into DummyTable1 Values (8, Replicate ('h',20))
GO
Insert Into DummyTable1 Values (9, Replicate ('i',20))
GO
Insert Into DummyTable1 Values (10, Replicate ('j',20))
GO

DummyTable1 has contains sufficient rows to experiment with the differences between the SELECT statement, views, and stored procedures.
Let us begin with the SELECT statement and see how it is different from views and stored procedures.

SELECT Statement
Now, let’s view the contents of the table by EXECuting the following command in Query Analyzer for our new table.
SELECT EmpId, EmpName FROM DummyTable1
GO

EmpID	EmpName
1	aaaaaaaaaaaaaaaaaaaa
2	bbbbbbbbbbbbbbbbbbbb
3	cccccccccccccccccccc
4	dddddddddddddddddddd
5	eeeeeeeeeeeeeeeeeeee
6	ffffffffffffffffffff
7	gggggggggggggggggggg
8	hhhhhhhhhhhhhhhhhhhh
9	iiiiiiiiiiiiiiiiiiii
10	jjjjjjjjjjjjjjjjjjjj

As you would expect, the data we inserted earlier has been displayed.
Now, let’s execute the following commands to clear the cache.
DBCC FREEPROCCACHE
GO
Freeing the procedure cache prevents an ad-hoc SQL statement from being reused, assuming that it is currently in the cache. This means that the next time we run the same ad-hoc statement, that it must be newly recompiled.

11) Difference between truncate and delete table

Ans: The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
The DROP command removes a table from the database. All the tables' rows,
indexes and privileges will also be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

12) types of triggers

Ans: Before Trigger, After Trigger, before Insert Trigger, After Insert Trigger, Before Update Trigger and After Update trigger.
http://en.wikipedia.org/wiki/Database_trigger
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
http://www.aspfree.com/c/a/MS-SQL-Server/Brief-Introduction-to-Triggers-in-SQL-Server-2000/

13) Primary Key, Foreign Key

SQL

1) http://www.1keydata.com/sql/sqltruncate.html
2) http://www.sourcecodeonline.com/list?q=sql_for_banking_system
3) http://www.blueclaw-db.com/accessquerysql/
4) http://www.databasejournal.com/scripts/

Redirect a SharePoint site by using the Content Editor Web Part

http://www.endusersharepoint.com/2010/01/20/redirect-a-sharepoint-site-by-using-the-content-editor-web-part/

SharePoint Gradual Upgrade – Random Page Error ‘This Page is Redirecting…’

http://cregan.wordpress.com/2007/11/16/sharepoint-gradual-upgrade-random-page-error-this-page-is-redirecting/

Sharepoint 2007 How to redirect the url from http://servername to http:/intranet

http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/96ae27c7-7ec6-4a30-9c9a-0ba6b940477b

SharePoint Customization Tip: Redirect to a custom page after completing a list form

http://www.graphicalwonder.com/?p=666

Redirect Pages on SharePoint

Replicated for archival retrieval purposes only!

Redirection options in SharePoint

I see questions about redirecting users in SharePoint all the time. Usually they're in the context of "I want to redirect users, can I do this with an Alternate Access Mapping?" SharePoint admins know that if they don't set their AAMs up correctly that SharePoint will bounce their users around like a SuperBall, so they hope they can harness that power for good, instead of evil. While you can in certain circumstances most likely you will want to use a different approach. In this blog post I will show you several different to redirect users in SharePoint. Then when you find yourself needing to, you can use the method best suited for your situation.
When deciding which redirection method to employ, you need to determine what you're redirecting from and what you're redirecting to. That will determine which method to use. For each of the methods I discuss below I will provide examples of the situation you would use it in. Enough with the introduction, let's get down to it.

Vanity URLs

One of the most common requests I see is for vanity URLs. SharePoint URLs can get long and unmanageable and admins want URLs that their users can remember. They want to use something like http://software and have it forwarded to http://team/sites/software/development. To set expectations correctly, you should just come to terms with the fact that you aren't going to write SharePoint URLs down, you'll pass them along via or IM. If you do want short URLs you can have them forward to deep SharePoint sites. Of course the URL your users will eventually get to will be the long URL, but the jumping off point will be easy to remember. To do this kind of redirection I use IIS directly, I don't get SharePoint involved at all. Create a new virtual server in IIS Manager and give it a descriptive name like "Software Redirect." When you're walking through the wizard you'll take the defaults. You'll have to point it to a local directory. This is only temporary, so you can point it anywhere. After the virtual server is created, edit the properties. Open the "Home Directory" tab and click "A redirection to a URL" then type your SharePoint URL in the "Redirect to:" box. It should look like this:

Figure 1 – Redirection settings
This method works pretty well and is easy for users. It does have a couple of drawbacks you should know about. Each virtual server running takes up RAM on your IIS server. You can take steps to mitigate this. I create a new App Pool and use it strictly for the redirect virtual servers. Then I set that App Pool to timeout if idle for 1 minute. Since this App Pool does not really do much, it does not use much RAM, but this reduces that memory footprint liability to only a minute or two on your web server each time someone uses the redirect. In my experience the App Pool only used 5 or 10 MB of RAM each time someone hit it. With today's machines that's not much of a hit. Overall I've been very happy with this approach.

Figure 2 - App Pool memory settings

Server URL has changed and users have bookmarks

This can happen from time to time, a server name changes or you move your site from HTTP to HTTPS. If your users have bookmarks to the old URLs this can cause problems. One option is to tell your users they're out of luck, but this is a great opportunity to look like a SharePoint Hero. You may be able to use AAMs to work this type of redirection, but they don't work consistently. For instance, the AAM will redirect a URL without a page, but will not redirect one that does. So http://oldserver would redirect but http://oldserver/default.aspx would not. Because most bookmarks will have the page included we can't rely on this. To handle this I employ a technique similar to how I handle Vanity URLs. First you'll need to make sure your existing SharePoint virtual server no longer responds to the old URL. Normally I just change the port to an used port like 8080, so that it's easy to change back. Next create a new virtual server and make sure it responds to the old URL, so you may need to add a host header or adjust the port. Like above, after you've created the redirect virtual server edit its Home Directory property. Instead of feeding it a static URL we're going to employ some redirect variables. Before I explain them, here's what your redirect URL will look like:

Figure 3 - Redirection variables
Two changes should jump out at you. First, the URL ends in $V$Q. The $V represents the original URL the user is trying to access, but without the protocol, servername or parameters. The $Q represents any parameters the URL included. Here's a crude drawing of how the URL breaks down:

Figure 4 - URL breakdown
Since all we want from the old URL is the path and the parameters, $V$Q works well for us. You can find a list of all the supported redirection parameters here. You'll also want to check the "A permanent redirection for this resource." This gives the client back an http 301 reponse which tells it the redirect is permanent. Some clients can understand this and will fix the Bookmark to the new URL. Other clients can't understand 300 level responses at all and will fail completely regardless. Full documentation on this switch and the rest can be found in the IIS Manager help. Once the screen is up you can hit F1 to open the help. After you have this all configured any requests this virtual server handles will be forwarded with path and parameters to the URL you specified.

Redirect from Deep URL

Sometimes you aren't redirecting an entire URL, or even just the servername. Sometimes you're just moving a web and you want users to be redirected to the new location. Since you don't want to redirect the entire web application you can't use either of the previous techniques. Fortunately there is a way. There is much magic in the Content Editor Web Part (CEWP) and this is another example. You can use a CEWP to insert HTML code to send your users wherever you want them. To utilize it add a CEWP to your web part page, usually the default.aspx at the root of your web. Edit the web part properties. Click Source Editor and add a Meta Refresh tag to send your users to the new site. It will look like this:

Figure 5 - CEWP Properties
The "10" in the content tag means the page wait 10 seconds before it refreshes. A longer time is handy if you want to put a message alerting your users to the new location before you forward them. The URL is the URL your users will be sent to. Of course this will only work on the page you put it on, so you may need place it on multiple pages in your web, like your Shared Document library pages. Fortunately since it is just a web part you can export it and import on additional pages, you don't need to recreate it each time.
There are some drawbacks to this approach, and it's generally considered bad form to use a refresh tag to forward users. For one it breaks your users' Back button in their browsers. It can also confuse your users, especially if the redirect is fast and there isn't a note on the page explaining that the site has moved. Finally, make very sure the URL you're redirecting them to is valid. If not they get stuck on a dead page and no way to use Back to get out. Sometimes it is your only option though.
This blog post wouldn't be complete without a mention of MOSS' Redirect Page content type. If you're using MOSS and you're on a Publishing Site you can access to it. Go to your Pages document library and Click new. It will be in your list.

Figure 6 - Redirect Page content type
Once the Redirect Page is created you'll need to Edit its Properties and enter the URL you want it to redirect to. You'll also need to preview the page and check it in before it will work. Unfortunately this solution only works on Publishing sites.
As you can see, SharePoint and IIS provide many ways to redirect users. Each method has its benefits and drawbacks. Hopefully this blog post will provide you with one method that will work for your situation.

Monday, October 18, 2010

MVC Architecture Model In ASP.NET

When developing ASP.NET applications the need of reducing code duplication increases along with their complexity. This is because testing and performing changes become very difficult tasks when having many different sources of code with the same functionality.

Model View Controller architecture (or pattern) allows us to separate different parts of our applications into tiers to fulfill this need.

MVC Overview

Model View Controller architecture aims to separate an application into three parts:
Model: It is the business logic of an application. From an object oriented perspective it would consist of a set of classes that implement the critical functionality of an application from a business point of view.
View: It can consist of every type of interface given to the user. In ASP.NET the view is the set of web pages presented by a web application.
Controller: This part of the architecture is the most difficult to explain, hence the most difficult to implement in many platforms. The controller is the object that allows the manipulation of the view. Usually many applications implement Model-Controller tiers that contain the business logic along with the necessary code to manipulate a user interface. In an ASP.NET application the controller is implicitly represented by the code-behind or the server side code that generates the HTML presented to the user.

Implementing MVC in ASP.NET

A basic diagram that would help us understand perfectly the specific parts that implement the Model View Controller architecture in an ASP.NET application is presented below:

MVC Model Implementation

When implementing the business logic of an application it is a must to use a Class Library project in order to generate a .dll file that will encapsulate all the functionality. This is critical as we as professional developers would not like to jeopardize the source code of a software product by placing the actual .cs files as a reference in a web application.
This type of project can be easily created in Visual Studio 2005 under the Visual C# or Visual Basic tabs:

As a tutorial example we will develop a simple calculator under a new namespace we will call "Math".
Once the project is created we will add a class called Calculator:

As the code is very simple and a sample is provided in this tutorial we will not get into much detail as far as how it is developed. The only important thing we need to mention is the way errors have to be handled in this class. Take a look at the following code:
1. protected float Divide(float fNumber1, float fNumber2)
2. {
3.   if (fNumber2 == 0)
4. {
5.           throw new Exception( "Second number cannot be equal to zero.");
6.    }

7.   return (fNumber1 / fNumber2);
8. }

When implementing the Divide function we need to ensure that the user would not be able to set the "fNumber2" parameter (line 1) to zero as a division between zero does not exist. The validation statement in lines 3-6 takes care of this case but the important fact we need to notice is that this class will NEVER use specific methods to present errors like message boxes or writing into labels. Errors captured in the model part of the architecture ALWAYS have to be presented in the form of exceptions (line 5). This will allow us to use this object in several types of applications like ASP.NET applications, Windows applications, Web services, etc.
Once we have finished coding our Calculator class the project has to be built in order to get the .dll file we will use in our Web application.

MVC View-Controller Implementation

The View and the Controller objects will be implemented by using a common ASP.NET Website. Once we have created our project we need to add the reference to the .dll file we created before.
The option to do this can be found in the context menu when right-clicking the project in the solution explorer:

We can find the file in the path "\bin\Release" (or "\bin\Debug" depending on how you build your class library) inside our main folder containing the math class library project:

$Adding Math.dll reference$

Once we have referenced our library we will create a simple web page that will allow us to choose between the four basic arithmetic operations and type two different numbers to operate.
The web page will look like this:

In the code behind we need to reference the Math namespace in order to use our Calculator class. The following statement will do that:
using Math;
As the code for this application is also simple we will only explain the method called when the "Operate!" button is clicked:
1. protected void btnOperate_Click(object sender, EventArgs e)
2. {
3.   if (pbValidateNumbers())
4.   {
5.        Calculator cOperator = new Calculator();
6.         try
7.         {
8.               txtResult.Text = cOperator.Operate(float.Parse(txtNumber1.Text.Trim()), float.Parse(txtNumber2.Text.Trim()), Convert.ToInt16(rblOperations.SelectedValue)).ToString();
9.               lbError.Text = "";
10.        }
11.        catch (Exception ex)

12.        {
13.              txtResult.Text = "";
14.              lbError.Text = ex.Message;
15.        }
16. }
17.}
In line 3 we call the bool function "pbValidateNumbers" that will return true if the numbers typed in both textboxes are valid. These types of validations have to be performed by the controller object as they allow the interface to work properly and have nothing to do with the business logic.
In line 5 we create an instance of our Calculator class so we can perform the arithmetic operation. We call the method "Operate" (line 8) and return the value in another textbox. An important thing to mention is that we have to use a try-catch statement (lines 6-15) to handle any exception that could be thrown by our method "Operate" as every error caught in our Calculator class is handled by throwing a "digested" exception that is readable to the user.
In the code above we can appreciate how well encapsulated the business logic is, hence it can be reused in several applications without having to code it again.

Advantages of using MVC in ASP.NET

There's no duplicated code.
The business logic is encapsulated; hence the controller code is transparent and safer.
The business logic can be used in several front ends like Web pages, Web services, Windows applications, services, etc.
Exception handling is well managed showing the user only digested error messages.
Testing every part of an application is easier as it can be done separately using automated methods.
Application changes are easier to apply as they are focused in one part of the architecture only.

Click here to download the example project, used in this tutorial.