How to get Sql server 2008 intellisense working for schema changes

The new feature "intellisense" added in sql server 2008 is making my life easier. And I am enjoying this feature a lot. But I noticed many times that when we make any changes to DB schema like creating / modifying database objects (tables, sprocs, ...) we don't notice the schema changes in intellisense in the same session. This made me too bad and many times I cursed Microsoft for this inclomplete feature. But after making some research on this, I have found the solution to get intellisense for new schema changes in current session.

Actually what happens when you connect to sql server 2008 using sql server management studio is, it queries for the current database schema and saves it somewhere. And It uses this schema information for showing the intellisense when the user is writing queries. But when we create/update the sql objects (sprocs, tables, views, ...) these won't be updated and the intellisense is pulled from old schema. That's why we don't notice the schema changes in intellisense unless we explicitly refresh the cache. In order to get new chema changes we must clear the intellisense cache.

To clear the intellisense cache we need to click Edit -> Intellisense -> RefreshLocalCache in sql server 2008 management studio as shown in below screen.

Installing IIS 7.0 on Windows Server 2008

Let me explain my experience on installing IIS 7.0 on windows server 2008. I had a tough time to find a way to install IIS 7.0 on windows server 2008. I thought that it would be same as installing IIS 6.0 on windows server 2003 by going to "control panel" -> "Add or Remove Programs" -> "Add/Remove Windows Components" and install IIS. But I couldn't find this in windows server 2008. After doing a long research I found a link which helped me.

I followed the below steps to install IIS 7.0 on windows server 2008.

1. Go to "Start" -> "Administrative tools" -> "Service Manager".
2. Right click the node "Roles" in the "Service Manager" window and click on "Add Roles".
3. Follow the wizard.
4. Select web server(IIS) in 2nd step of the wizard.
5. You might get the following popup immediately. If you get this please click "Add Required Role Services".
6. You follow the wizard until you get the below step.
7. Follow the wizard until the final step.
8. That's it. You are done with installing IIS 7.0 on windows server 2008.
9. If you face any problems please follow the below link. It has detailed step by step guidelines to install the IIS 7.0 on windows server 2008. http://learn.iis.net/page.aspx/29/installing-iis-70-on-windows-server-2008/

C# 4.0 new features: Named and Optional arguments

Microsoft has introduced few new features in C# 4.0. I would like to discuss the below listed new features in this post.

1. Optional arguments

2. Named arguments

Optional arguments: This feature allows you to omit arguments when calling methods. This is done by defining a method with assigning default values to parameters. For better understanding, let's take a look at the below example.

We define a method called "SomeMethod" by proving default values to two of its parameters as shown.

public void SomeMethod(int a, int b = 50, int c = 100);

Now this method can be called in different ways as shown below.

SomeMethod(10, 30, 50); // This is a normal call as 3 arguments were passed

SomeMethod(10, 30); // This call is omitting parameter "c". This call is equalant to SomeMethod(10, 30, 100)

SomeMethod(10); // This call is omitting both "b" and "c". This call is equalant to SomeMethod(10, 50, 100)

As you see, we can omit any number of consecutive parameters from right to left. In the above examples we omitted parameter "c" alone and parameters "b" and "c" together. Do you see a way to omit the parameter "b" in the above example? If you call the method "SomeMethod" by passing one argument as shown below, the compiler assumes that the argument passed was for the first parameter i.e, "a". So we need a way to tell the compiler that this argument was passed to a particular parameter, in this case it is "b". This requirement was fulfilled by c# 4.0's another new feature called "Named Arguments". Let's take a closer look at this feature.

Named Arguments: This feature allows you to pass the arguments by the corresponding parameter names.

For example you can pass the arguments by name as shown below.

SomeMethod(a:10, b:30, c:50);

By this feature, we don't need to pass the arguments in the order of parameters defined in the method. We can rewrite the above method call as shown below.

SomeMethod(c:50, b:30, a:10);

And you can solve the above discussed problem (omitting the middle parameter "b") as shown below.

SomeMethod(a:10, c:50); // This call Omits the parameter "b".

Optional and Named arguments features can also be applied to constructors. This feature is mainly useful where you have methods that have many number of parameters. Because it is very difficult to call a method by passing the parameters in same order as the method was defined. There are many possibilities to make mistakes in the order. So, having named parameters and the ability to pass parameters in any order by explicitly referring the parameter names will make our life easier.

Use of "for" attribute of html label

I have noticed many of the html programmers that, they use an attribute called "for" when writing a label tag. And the attribute value is exactly same as the corresponding html input control such as text box, check box, drop down,... But I never tried to know the actual use of this attribute.

Very recently, I have searched in Google to know the use of it. Now I have come to know that, the "for" attribute of label binds the label with the corresponding control. Binding in the sense, when the user clicks on the label it toggles the corresponding control (control id passed as value for "for" attribute). This way the "for" attribute is used for the label. Now onwards I am going to make a good practice of using "for" attribute of the label control.

Let's see an example of the usage of the "for" attribute of label control

<label for="FirstName">First Name:</label>

<input id="FirstName" type="text">

The above code is rendered as shown below. You can test this behavior by clicking on the label "First Name:". You can notice the text box focused.

First Name:

Easy way to strip time part in sql date time

Most of the times we wouldn't want the time part of the dates to be stored or retrieved when dealing with only days. But the data type "datetime" in sql always includes the time even though you don't want it. There is no other data type in sql to store or retrieve the date without time. In this case we need to strip the time part from the date and store the remaining in database. To do this favor, I have come up with the below sql code which strips the time part from the date time.

declare @date datetime;
set @date = getdate();
select cast(convert(varchar, @date, 101) as datetime) --Strips time element from the date time

The above code will work like this:
If you have the date time 25/09/2009 12:36:40:654 then this will be converted as 25/09/2009 00:00:00:000

Find common elements in c# generic list

The following code demonstrates how to get the common elements in
given 2 lists using Intersect method of System.Collections.Generic.List.

using System;
using System.Linq;

namespace ConsoleApplication1
{
static class Program
{
    static void Main(string[] args)
    {
        int[] elementSet1 = { 5, 1, 6, 3, 8 };
        int[] elementSet2 = { 3, 7, 8, 6, 5 };

        foreach (int element in elementSet1.Intersect(
                           elementSet2))
        {
            Console.WriteLine(element);
        }
        Console.Read();
    }
}
}

How to find an element in c# Generic List

The following sample code demonstrates you how to find a matching element in the object list.

Let's say we have an object called Person with public properties PersonID, Name, Age, Gender as defined below.

using System.Collections.Generic.List;

public class Person
{
public int PersonID {get; set;}
public string Name {get; set;}
public int Age {get; set;}
public bool Gender {get; set;}
}

class program
{
public static void Main(string[] args)
{
// Let's say we have a method defined as dbAccess.GetPersons() to
//return all the records from Person table and populates the
//list object "persons" as shown below.
List persons = dbAccess.GetPersons();

int personIDToFind = 1234;
//Find person in persons list by PersonID using predicate
//"p=>p.PersonID == personIDToFind"
Person personByFindOperation = persons.Find( p=>p.PersonID == personIDToFind );

string personName = "Kiran"
//Find person in persons list by Name using predicate
//"p=>p.Name == personName"
Person personByFindOperation2 = persons.Find( p=>p.Name == personName );

}
}

Note: The Find method of List returns the first occurrence of the exact match within the entire System.Collections.Generic.List.

How to create a windows service in dot net framework

Windows service is an application which always runs in background. We can view all the installed windows services in ControlPanel/Administrativetools/Services. Sql server, Oracle, ... are the good examples of windows service.

Using visual studio it is very easy to create windows service. Please follow the below steps to create a windows service.

1. Open visual studio and create a new project of template "Windows Service" as shown below
2. After you click OK in the above step you will see a new solution named "WindowsService1" created as shown below.
3. Right click on the above screen and click "Add Installer". This will create an installer component which helps us in installing this service.
4. You can notice a new component "ProjectInstaller.cs" added and opened as shown below. It has 2 controls "ServiceProcessInstaller1" and "ServiceInstaller1".
5. You can set whether this service is manual, automatic, or disabled by changing the StartType property of "ServiceInstaller1" (Right click -> Properties).
6. Change the "ServiceProcessInstaller1"'s property "Account" to "LocalSystem"
7. Right click on Service1.cs in solution explorer and click "View Code"
8. In code you will see the service event handlers "OnStart", "OnStop".
9. Write code that initiates a thread in OnStart event handler.
10. Write code that stops execution of the thread in OnStop event handler.
11. Please see below for sample code
12. Build the solution. You have the windows service built ready.
13. But until we install this service we cannot see it is running or not.
14. To install this service you need to open the Visual Studio 2005/2008 Command prompt in Start->Visual Studio 2005/2008->Visual Studio Tools
15. Run the command : InstallUtil.exe Windows_Service_EXE_Full_Path
16. Here Windows_Service_EXE_Full_Path is the exe generated for the WindowsService1 project. Let's say the full path of the exe is C:/WindowsService1/Bin/Debug/WindowsService1.exe then our command to install it would be like this.
17. InstallUtil.exe C:/WindowsService1/Bin/Debug/WindowsService1.exe
18. If the above command is successful then you will notice a new service named "Service1" is shown in Control Panel->Administrative Tools/Services
    
19. To uninstall a service, InstallUtil.exe -u C:/WindowsService1/Bin/Debug/WindowsService1.exe
20. To make the life easier to install/uninstall windows services we can create a setup project which will let people easily install/uninstall with a wizard Please read this link. http://cherupally.blogspot.com/2009/09/how-to-create-setup-project-to-install.html

How to create a setup project to install windows services

This post assumes that you have knowledge on windows services and how to create a windows service using visual studio. People who don't know how to create windows service please learn here.

Please follow below steps to create a setup project
1. Create a visual studio solution with a project of type Windows Service as explained in http://cherupally.blogspot.com/2009/09/how-to-create-windows-service-in-dot.html

2. Add a new project of type "Setup Project" to the solution as shown below


2. Once you click on OK button in the above step, you will see the screen below. There you notice a new project named "Setup1" is added.


3. Now right click on Setup1 project in the solution and click "Add Project Output" as shown below.

4. In "Add Project Output Group" dialog box shown below, select the windows service project in the drop down and click OK.


5. The above 4 steps are only to copy all the assemblies to specifies installation folder. Setup will not install the services in the system. To tell the setup to install services we must add custom actions. To open the custom actions window right click "Setup1" project in solution explorer and click View->Custom Actions as shown below.


6. You will see the Custom Actions tab opened like this.


7. Right click on "Custom Actions" and click "Add Custom Action".

8. That will open a dialog box which lets you choose the items from "Application Folder". Select "Primary Output From WindowsService1" in "ApplicationFolder" and click OK.


9. The above step will add this custom action to all sub folders "Install", "Commit", "Rollback", "Uninstall". Finally you will see the screen like this.


10. Build the "Setup1" project. Now you are ready to use this setup project to install/uninstall windows services. Take the setup build from WindowsService1\Setup1\Debug and use it to install/uninstall when needed.

Problems in configuring sql server for session mode sql server

Before going to details let me explain my experience on this. I was working on a project which pushed us to use bulky data in session. So we thought that it wouldn't be reliable to use the session in "InProc" mode as the asp.net worker process consumes lot of RAM and leads to crash the service. To avoid this we decided to use the session state in sql server mode. Telling the asp.net application to use sql server for session state is easy as shown below.


But to make the above to work, we need to setup the sql server which is mentioned in connection string above. To do this, I did the following steps mentioned in some article found in Google search.

1. Find the file 'aspnet_regsql.exe' in the location 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727'.
2. Run the aspnet_regsql.exe.
3. Follow the wizard with all default values till the wizard finishes.
4. Verify that a new database is created in the sql server.

It all looked perfect for me and when I tested my application I got an error saying "Cannot open database requested in login 'ASPState'. The asp.net framework was look for the database 'ASPState' but when I looked at the sql server I have noticed that the setup has created 'aspnetdb' instead of 'ASPState'. Then I have changed the config to use the database 'aspnetdb' as show below.


This time I got a different exception.
"Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above."

To solve these problems I did a long research and found a way.

1. Open command prompt
2. Change the current directory to 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727'.
3. Run aspnet_regsql.exe -S SqlServer -E -ssadd -sstype p
4. You will notice the database 'ASPState' created in the sql server give
5. That's it. Your application is ready to use the sql server to store the session state.

Occasionally Connected Systems Sync Framework

Every one of us must have realized that there should be a system which allows us to work offline and online. Nowadays most of the people started using laptops and working remotely. So we got a need of a framework which lets the developers build applications to sync the offline changes with the server.

Fortunately Microsoft has released this kind of framework which is called Microsoft Sync Framework. This enables the developers to build applications with the ability to sync the files in file system, table records in the database between two machines.

For more details on microsoft sync framework please read the following.

1. Sync Framework Overview
2. Introduction to Occasionally Connected Database Synchronization
3. Introduction to File Synchronization

Web page loading twice problem

We had a problem when working on a web project (C# ASP .Net). The problem was, the page was loading twice for every request made. This was happening only for GET requests. And it was working fine for POST requests. After doing a long research on it I found the issue why it was loading 2 times.

We had a master page with 2 images defined with empty (#) src attributes.

The exact code we had was

<img src='#' width='1000' height='392' alt='you need to install flash or turn on javascript' />

<img src='' width='1000' height='300' alt='' class='hide' />


The attribute src must be set other than empty or #. After fixing this we noticed that the page is loading only once.

Due to that bug all the pages which are using this master page were having the problem. After the fix every page worked fine with no issues.

For more details about this issue please check.

http://www.velocityreviews.com/forums/t21131-aspnet-page-load-twice-problem.html

table variables in sql server 2005

Table variable is almost similar to the temporary table in sql server 2005. Table variables are replacement to temporary tables. The following syntax shows how to define a table variable.

declare @Customer table
(
CustomerID int identity(1,1),
CustomerName varchar(100),
CompanyName varchar(100),
Address varchar(250)
)

You can notice the syntax to define a table variable is similar to the syntax to normal table definition. declare keyword is used in place of create keyword. And table name is prefixed with '@' as all tsql variables do.

1. Table variables are stored in memory rather than in database.
2. Querying table variables is very fast as there are no disk reads needed.
3. The scope the table variables is same as other tsql variables. i.e, within statements batch or sproc
4. Table variables cannot be dropped as they are automatically disappeared when they reach out scope
5. As explained above all the data in table variables is stored in server's memory. So if there is huge data then it is not recommended to use table variables to avoid memory overhead.
6. If the number of records is more than 100 then it is recommended to use temporary tables. To learn more about temporary tables please readi this blog post http://cherupally.blogspot.com/2009/05/how-to-create-temporary-table-in-sql.html
   

How to create temporary table in sql server 2005?

The syntax to create a temporary table is exactly same as the syntax to create a normal table. But the difference is the table name is prefixed with '#' (pound). By looking at the prefix '#' the sql server understands that it is a temporary table. The following code demonstrates how to create a temporary variable.

CREATE TABLE #Customer
(
CustomerID int identity(1,1),
CustomerName varchar(100),
CompanyName varchar(100),
Address varchar(250)
)

Important points about temporary tables:

1. It is almost similar to normal table with very few exceptions.
   
2. The scope of the temp table is the current connection session. It can be accessed from any where in same connection session.
   
3. This table is automatically dropped when the connection session is closed.
4. Different connection sessions creating temporary table with same name will create different copies of the temporary table with the same name. Actually internally the table name is appended with a unique number to support many connection sessions creating tables with same name. You can notice how the table name is uniquely maintained by sql server by running this query. select * from information_schema.tables where table_name like '%customer%'
   
5. foreign key relations cannot be applied to temp tables.
6. Optionally you may drop the table at the end of it's use. It is a good practice to drop any temporary table after use. drop table #Customer
   

When you create a temporary table it will be created in tempdb. At the time of table creation the tempdb is locked and hence there is some overhead involved using this. If there are less than 100 records then using temporary tables is not recommended. In such cases you can use table variables which are stored in memory rather than in database(on disk). To learn more about table variables please read this
http://cherupally.blogspot.com/2009/05/table-variables-in-sql-server-2005.html

Implementing Custom Paging, Sorting and Dynamic query in sql

This post explains and demonstrates how to implement following things

1. Custom Paging: Returning a set of records for the given page number and page size
   
2. Dynamic query: Querying based on the where string passed thru parameter
   
3. Dynamic sorting: Sorting based on the sort expression passed thru parameter
   
4. SP_ExecuteSql: Executing sql statements included within parameters
   
5. Common Table Expression: Similar to table variable
6. function row_number(): returns the current row number in a result set

The following code is to create the stored procedure GetCustomersByPage. This stored procedure accepts the parameters page size, page number to return the page, where string (It must start with 'and' keyword if a condition is added. Ex: " and CompanyName like '%soft%' " or empty is accepted but applies no filter), sort expression to get the sorted records and total count which is useful when implementing the paging logic in user interface.

The sample sproc is written for NorthWind database. If you have NorthWind database installed then you can run this sproc and see how it works.

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int


set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end

How to post html code in blogspot / blogger

I had problem when posting an article on blogspot. I was trying to show some sample html/xml code in that article. But I faced problems when I wrote the core html code. The problem was that the html code was rendering as html controls and breaking the post content. I tried many times and got frustration. Finally I searched in google for this problem and I got the below useful link.

http://www.simplebits.com/cgi-bin/simplecode.pl

If you follow the above link you will notice 2 textareas in which the first one is for input the html code and second one is for output of the changed html code which fits into the blogspot post and renders as expected.

Whoever wants to post the html code in blogger, please go to the link and paste your html code in first text box control and click the process button. Then you will notice the second textbox updated with the processed html code. Then copy the complete code from second text box and paste it in your blogspot post. It works not only in blogspot but also in all blogs which don't allow direct html code in posts.

Code to Serialize Object in C# .Net

Please use the below method if you want to serialize any object. This method returns you a string which is XML consists of all public properties and public data members of the object passed. It perfectly works for any type of reference type object.


String SerializeObject(Object obj)
{
try
{
String XmlizedString = null;
MemoryStream memoryStream = new MemoryStream();

XmlSerializer xs = new XmlSerializer(obj.GetType());
XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);

xs.Serialize(xmlTextWriter, obj);
memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
XmlizedString = UTF8ByteArrayToString(memoryStream.ToArray());
return XmlizedString;
}

catch (Exception e)
{
System.Console.WriteLine(e);
return null;
}
}

Return multiple result sets using IMultipleResults in linq c#

As we know that LINQ introduced a new feature which lets us to get multiple result sets from the stored procedure. But there is one important which all should know about IMultipleResults. The order of the GetResult's should be same as the order of the select statements in the sproc. This must be followed in order to avoid unexpected errors. I spent many hours on this to get rid of this problem.

For better understanding please go through the following

The following tsql code shows how the spoc which returns multiple result sets look like

CREATE PROCEDURE GetMultipleResults
@SomeID int
AS
BEGIN
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID
END

The below code shows how to get the IMultipleResult object by calling the above sproc using LINQ

[Function(Name = "dbo.SPROCName")]
[ResultType(typeof(ResultSet1))]
[ResultType(typeof(ResultSet2))]
[ResultType(typeof(ResultSet3))]
[ResultType(typeof(ResultSet4))]
public IMultipleResults SomeMethod([Parameter(DbType = "INT")] int? SomeID
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SomeID);
return ((IMultipleResults)(result.ReturnValue));
}


The below code shows how to read the result set from the object of IMultipleResult type

public void SomeOtherMethod(int SomeID)
{
DataContext1 context = new DataContext1 (dbConnString);
IMultipleResults results = context.SomeMethod(SomeID);

ResultSet1 resultSet1= results.GetResult().FirstOrDefault();
IEnumerable resultSet2 = results.GetResult();
IEnumerable resultSet3 = results.GetResult();
ResultSet4 resultSet4= results.GetResult().FirstOrDefault();
}

If you observe the above code you can notice that the method is reading individual result sets from the IMultipleResult object in the same sequence as the sproc returns the results. This is very important that the order of the IMultipleResults.GetResult() statements for the result sets should be same as the order of the select statements in sproc. If the order is different then you will get runtime errors or invalid objects and many more issues.

Impose maxlength on textarea controls to avoid "String or binary data would be truncated" error

As we all know that you can restrict a text box to allow limited number of characters using maxlength attribute as shown below

<input id="tbName" maxlength="20" type="textbox" width="100"/>

(or)

<asp:TextBox ID="tbName" MaxLength="20" Width="100px" runat="server" />

It is very simple and easy to impose the maximum length constraint for text boxes.

But this is not the case for textarea /multiline text box controls. There is no straight way to do this. We don't have the maxlength attribute for html textarea control. This post explains how to add that support.

Please check the below javascript function imposeMaxLength(field,maxChars) which takes 2 parameters:
- field which is the textarea control itself
- maxChars to tell the function how many charaters to be allowed.

<script type="text/javascript">
function imposeMaxLength(field,maxChars)
{
if(field.value.length > maxChars)
{
field.value = field.value.substring( 0, maxChars );
alert( 'Textarea value can only be ' +maxChars + ' characters in length.' );
return false;
}
}
</script>

The function needs to be called in onkeypress event of the textarea control. Please see the below code to know how we should call the method imposeMaxLength in onkeypress event of the textareancontrol

<textarea id="tbDescription" onkeypress="imposeMaxLength(this, 512)" />

(or)

<asp:TextBox ID="tbDescription" TexMode="MultiLine" runat="server" onkeypress="imposeMaxLength(this, 512)" />

The above code renders a text area control and restricts the control not to allow more than 512 characters.

This is very useful when you have database fields with limited string length. We have faced many pronlems as the user was entering bulky data and the field doesn't support and hence throws the exception. After implementing this we were comfortable and there were no exceptions like String or binary data would be truncated.

How to sort list of objects in C#

The following code helps in sorting list of objects based on some property’s value.

public class Person
{
public int age;
public string name;

public Person(int age, string name)
{
this.age = age;
this.name = name;
}
}

List people = new List();

people.Add(new Person(50, “Kiran”));
people.Add(new Person(30, “Raghava”));
people.Add(new Person(26, “Andrew”));
people.Add(new Person(24, “Praveen”));
people.Add(new Person(5, “Sudhir”));
people.Add(new Person(6, “Prudhvi”));

Console.WriteLine(”Unsorted list”);
people.ForEach(delegate(Person p) { Console.WriteLine(String.Format(”{0} {1}”, p.age, p.name)); });

Console.WriteLine(”Sorted list, by name”);
people.Sort(delegate(Person p1, Person p2) { return p1.name.CompareTo(p2.name); });
people.ForEach(delegate(Person p) { Console.WriteLine(String.Format(”{0} {1}”, p.age, p.name)); });

people.Sort(delegate(Person p1, Person p2) { return p1.age.CompareTo(p2.age); });
Console.WriteLine(”Sorted list, by age”);
people.ForEach(delegate(Person p) { Console.WriteLine(String.Format(”{0} {1}”, p.age, p.name)); });

Learn How HyperText Transfer Protocol works

This post describes the complete HTTP protocol how it works.

HTTP stands for Hypertext Transfer Protocol. It’s the network protocol used to deliver virtually all files and other data (collectively called resources) on the World Wide Web, whether they’re HTML files, image files, query results, or anything else. Usually, HTTP takes place through TCP/IP sockets.

A browser is an HTTP client because it sends requests to an HTTP server (Web server), which then sends responses back to the client. The standard (and default) port for HTTP servers to listen on is 80, though they can use any port.

HTTP is used to transmit resources, not just files. A resource is some chunk of information that can be identified by a URL (it’s the R in URL). The most common kind of resource is a file, but a resource may also be a dynamically-generated query result, the output of a .aspx file, a document that is available in several languages, or something else.

Structure of HTTP Transactions:

Like most network protocols, HTTP uses the client-server model: An HTTP client opens a connection and sends a request message to an HTTP server; the server then returns a response message, usually containing the resource that was requested. Once the connection is opened It remains open in order to process further requests.

The format of the request and response messages is similar, and English-oriented. Both kinds of messages consist of:

• an initial line,
• zero or more header lines,
• a blank line (i.e. a CRLF – new line by itself), and
• an optional message body (e.g. a file, or query data, or query output).

Put another way, the format of an HTTP message is:

Header1: value1

Header2: value2

Header3: value3

it can be many lines long, or even binary data $&*%@!^$@>

Initial lines and headers should end in CRLF, though you should gracefully handle lines ending in just LF. (More exactly, CR and LF here mean ASCII values 13 and 10, even though some platforms may use different characters.)

Initial Request Line:

The initial line is different for the request than for the response. A request line has three parts, separated by spaces: a method name, the local path of the requested resource, and the version of HTTP being used. A typical request line is:

GET /path/to/file/index.html HTTP/1.0

Notes:

• GET is the most common HTTP method; it says “give me this resource”. Other methods include POST and HEAD. Method names are always uppercase.
• The path is the part of the URL after the host name, also called the request URI (a URI is like a URL, but more general).
• The HTTP version always takes the form “HTTP/x.x“, uppercase.

Initial Response Line (Status Line):

The initial response line, called the status line, also has three parts separated by spaces: the HTTP version, a response status code that gives the result of the request, and an English reason phrase describing the status code. Typical status lines are:

HTTP/1.0 200 OK

or

HTTP/1.0 404 Not Found

Notes:

• The HTTP version is in the same format as in the request line, “HTTP/x.x“.
• The status code is meant to be computer-readable; the reason phrase is meant to be human-readable, and may vary.
• The status code is a three-digit integer, and the first digit identifies the general category of response:
  • 1xx indicates an informational message only
  • 2xx indicates success of some kind
  • 3xx redirects the client to another URL
  • 4xx indicates an error on the client’s part
  • 5xx indicates an error on the server’s part

The most common status codes are:

200 OK

The request succeeded, and the resulting resource (e.g. file or script output) is returned in the message body.

404 Not Found. The requested resource doesn’t exist.

301 Moved Permanently
302 Moved Temporarily
303 See Other (HTTP 1.1 only)

The resource has moved to another URL (given by the Location: response header), and should be automatically retrieved by the client. This is often used by a CGI script to redirect the browser to an existing file.

500 Server Error

An unexpected server error. The most common cause is a server-side script that has bad syntax, fails, or otherwise can’t run correctly.

Header Lines:

Header lines provide information about the request or response, or about the object sent in the message body.

The header lines are in the usual text header format, which is: one line per header, of the form “Header-Name: value“, ending with CRLF. . Details about RFC 822 header lines:

• As noted above, they should end in CRLF, but you should handle LF correctly.
• The header name is not case-sensitive (though the value may be).
• Any number of spaces or tabs may be between the “:” and the value.
• Header lines beginning with space or tab are actually part of the previous header line, folded into multiple lines for easy reading.

Thus, the following two headers are equivalent:

Header1: some-long-value-1a, some-long-value-1b

HEADER1: some-long-value-1a,

some-long-value-1b

HTTP 1.0 defines 16 headers, though none are required. HTTP 1.1 defines 46 headers, and one (Host:) is required in requests. For Net-politeness, consider including these headers in your requests:

• The From: header gives the email address of whoever’s making the request, or running the program doing so. (This must be user-configurable, for privacy concerns.)
• The User-Agent: header identifies the program that’s making the request, in the form “Program-name/x.xx“, where x.xx is the (mostly) alphanumeric version of the program. For example, Netscape 3.0 sends the header “User-agent: Mozilla/3.0Gold“.

These headers help webmasters troubleshoot problems. They also reveal information about the user. When you decide which headers to include, you must balance the webmasters’ logging needs against your users’ needs for privacy.

If you’re writing servers, consider including these headers in your responses:

• The Server: header is analogous to the User-Agent: header: it identifies the server software in the form “Program-name/x.xx“. For example, one beta version of Apache’s server returns “Server: Apache/1.2b3-dev“.
• The Last-Modified: header gives the modification date of the resource that’s being returned. It’s used in caching and other bandwidth-saving activities. Use Greenwich Mean Time, in the format
• Last-Modified: Fri, 31 Dec 1999 23:59:59 GMT

The Message Body:

An HTTP message may have a body of data sent after the header lines. In a response, this is where the requested resource is returned to the client (the most common use of the message body), or perhaps explanatory text if there’s an error. In a request, this is where user-entered data or uploaded files are sent to the server.

If an HTTP message includes a body, there are usually header lines in the message that describe the body. In particular,

• The Content-Type: header gives the MIME-type of the data in the body, such as text/html or image/gif.
• The Content-Length: header gives the number of bytes in the body.

Methods:

GET :

The GET method is used when the client wants to retrieve a document from the server.

POST:

A POST request is used to send data to the server to be processed in some way, like by an aspx file. A POST request is different from a GET request in the following ways:

• There’s a block of data sent with the request, in the message body. There are usually extra headers to describe this message body, like Content-Type: and Content-Length:.
• The request URI is not a resource to retrieve; it’s usually a program to handle the data you’re sending.
• The HTTP response is normally program output, not a static file.

The most common use of POST, by far, is to submit HTML form data to CGI scripts. In this case, the Content-Type: header is usually application/x-www-form-urlencoded, and the Content-Length: header gives the length of the URL-encoded form data. The CGI script receives the message body through STDIN, and decodes it. Here’s a typical form submission, using POST:

POST /path/script.cgi HTTP/1.0

From: frog@jmarshall.com

User-Agent: HTTPTool/1.0

Content-Type: application/x-www-form-urlencoded

Content-Length: 32

You can use a POST request to send whatever data you want, not just form submissions. Just make sure the sender and the receiving program agree on the format.

The GET method can also be used to submit forms. The form data is URL-encoded and appended to the request URI.

Schedule daily backup for sql server 2005 database using sql agent

Many of us come across a need to schedule periodic database backups for sql server. But many of us don't realize that this feature is readily available in sql server management studio and we purchase some 3rd party tools to create such scheduled tasks. I recommend to use Sql Server Management Studio to do such jobs rather than spending on unnecessary tools. But remember, this feature is not available in express releases of sql server. If you are using sql server express edition please ignore this post. And this post also assumes that you have SSIS (Sql Server Integration Services) installed and running on your machine. SSIS is the prerequisite to follow the below instructions to schedule the database periodic backups.
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.

1. Open sql server 2005 Management Studio and connect a server
2. Expand the tree (left navigation) ”Management->Maintenance Plans”
3. Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.

4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following

6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.

7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.
8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.

9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.

10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish step.
12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.
13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).

The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

Every time I install sql server, in the initial days I used to face problems in logging on by sql server authentication mode.

These were the steps I used to follow to create new login in order to logon using sql server authentication mode.

1. Log on to the server using windows authentication mode and create some login in the section of Sqlserver->Security->Logins->(Right click)NewLogin.

2. Restart the sql server and disconnect it.

3. This time connect to the server using the sql server authentication mode by entering new login credentials.

But it never used to work. The error I receive was “The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)”.

After doing a long research on this I found the solution to this. To make sql server authentication work we need to configure the sql server to use Sql server and windows authentication mode. The following steps will solve the problem.

1. Right click on the root node of the sql server and select properties in the context menu.

2. That will open a popup window which will have server level settings.

3. Click on security tab it will show the view as in the following figure. There you have to make sure you have “Sql server and Windows authentication mode” is selected.

4. Press OK and restart the sql server again.

5. This time the sql server authentication mode using the new login credential will work.

Note: You have to make sure your login has server role as “SysAdmin” in order to logon.

How to invoke a process/application from parent application using C#

You can start a new process/application using the following C# code line.

System.Diagnostics.Process.Start(filePATH);

filePATH is the relative path of the application executable.

If you give a website url in filePATH then it will be opened in a default web browser.

Ex: System.Diagnostics.Process.Start(”http://rampgroup.com”); will cause to open the website home page in a default web browser.

How to access Remote sql server’s database table

Recently I got a requirement to migrate data from other sql server’s database to my local sql server’s database. Then I tried querying like this.

select * from ServerName.DataBaseName.dbo.TableName

When I ran the query “select * from FilterDB011.FilterWebDB.dbo.SiteUser” I got an error saying

“Could not find server ‘FilterDB011′ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.”

Then I ran the query ” execute sp_addlinkedserver FilterDB011″ as the error message suggested.

Then It worked fine.

Now I am able to access the tables and databases from that remote database server.

The syntax to add a remote sql server to sys.servers is:

execute sp_addlinkedserver sqlserverName

The syntax to access a table in remote sql server is:

SeverName.DatabaseName.dbo.TableName

Exception handling in Sql server 2000

When I was working on a project, I had written a sproc which runs in sql server 2005. I had used Try Catch blocks to handle the exceptions and roll back the transaction for any exception. My script was working fine with sql-2005. But when I had to deploy on production, I noticed that we had sql server 2000 on which our legacy database was sitting. When I ran my script it was failing to create the sproc due to try catch blocks. After doing a while research on it I found that sql server 2000 deals with exceptions in different way.

When any exception is raised then @@ERROR will be set to a non zero value. If you want to check whether exception has araised you should check

@@ERROR <>0 -> Exception occurred

@@ERROR =0 -> No Exception

To handle exceptions in sql server 2000 we need to check the condition @@ERROR <>0 for every insert/update/delete statement and based on that we need to roll back the transaction if used.

Example:

BEGIN TRAN

DELETE EMP WHERE EmployeeType = ‘ProjectManager’

IF (@@ERROR <>0)

GOTO HandleError;

UPDATE EMP SET Salary = Salary * 1.30, EmployeeType = ‘ProjectManager’ WHERE EmployeeType = ‘TeamLead’

IF (@@ERROR <>0)

GOTO HandleError;

COMMIT TRANS

RETURN;

HandleError:

ROLLBACK TRAN

RETURN;

Setting Execution Order of Triggers in Sql Server

I had multiple triggers defined for update on a table. I got a requirement to force the execution for the triggers to be done in a defined order. I had searched some websites and found that it is possible using the built in sproc sp_settriggerorder

sp_settriggerorder [@triggername = ] ‘triggername‘
, [@order = ] ‘value‘
, [@stmttype = ] ‘statement_type‘

Argument

[@triggername = ] ‘triggername‘

Is the name of the trigger whose order will be set or changed. triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure will return an error.

[@order = ] ‘value‘

Is the setting for the new trigger order. value is varchar(10) and it can be any of the following values.

Important The First and Last triggers must be two different triggers.

Value	Description
First	Trigger will be fired first.
Last	Trigger will be fired last.
None	Trigger will be fired in undefined order.

[@stmttype = ] ‘statement_type‘

Specifies which SQL statement fires the trigger. statement_type is varchar(10) and can be INSERT, UPDATE, or DELETE. A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. SQL Server will return an error if TR1, which has been defined only as an INSERT trigger, is set as a First (or Last) trigger for an UPDATE statement. For more information, see the Remarks section.

solving error #2104 “Could not download the Silverlight application.”

This error generally occurs when the silver light file types are not registered as MIME types in the web server. To fix this error please follow the following guide lines.
1. Go to
inetmgr — > select ur silverlight applciation –>Right click Properties –> HTTP Headers — MIME Map –> File Types — New Types –>

2. Add one by one the below mentioned:

Associated Extension Content Type(MIME)

.xaml application/xaml+xml
.xap application/x-silverlight-app
.manifest application/manifest
.application application/x-ms-application
.xbap application/x-ms-xbap
.deploy application/octet-stream
.xps application/vnd.ms-xpsdocument
3. Click OK and close the dialog.

Asp.Net Server.UrlEncode doesn't work if performed once

Hi,

I have found one interesting thing about UrlEncode when I was working on a project.

In many page requests we were passing sensitive query string parameters. As these were sensitive we were encrypting them using some encrypting algorithm. That algorithm was producing non numeric characters (Ex:'/', '&', '=','?',...) which are recognized by the web server. This was leading to conflicts by the web server and the application was breaking.

To Avoid this we started performing Server.UrlEncode on the encrypted string and using it in any url or querystring. On landing page we were doing Server.Decode and then decrypting the resulted string. Please see the blow code for better understanding.

Server.UrlEncode(Utility.Encrypt("sensitive data"));
Utility.Decrypt(Server.UrlDecode("url encoded and encrypted sensitive data"));

But this didn't help us. Even though we did url encoding we were not seeing the url encoded url in the browser. And due to this the application was breaking. After this we did a long research and tried all combinations but nothing helped.

Finally we got the solution. The solution is we need to perform the url encoding twice and url decoding once. That's how it was working perfectly. We changed the code accordingly in the entire project and we didn't see any issue. The final fix for this problem is shown below.

Server.UrlEncode(Server.UrlEncode(Utility.Encrypt("sensitive data"))); //Encode twice
Utility.Decrypt(Server.UrlDecode("url encoded and encrypted sensitive data")); //Decode once


Example:

....
.....
int CustomerID;
string CustomerIDEncrypyted = Utility.Encrypt(CustomerID.ToString());
string CustomerIDEncoded = Server.UrlEncode(Server.UrlEncode(CustomerIDEncrypyted));
string url = string.Format("Customer.aspx?CID={0}", CustomerIDEncoded );
anchorNext.Href = url;
.....
.....

//PageLoad event code in Customer.aspx.cs page

protected void Page_Load(object sender, EventArgs e)
{
string CustomerIDEncoded = Request.QueryString["CID"];
string CustomerIDDecoded = Server.Decode(CustomerIDEncoded);
string CustomerIDDecrypted = Utility.Decrypt(CustomerIDDecoded);
.....
.....
}

Search for keywords in stored procedures

When I was working on a project I got a requirement to know how certain columns/tables are populated, which sprocs are populating them. Then I googled for a while about it and got the link about information_schema.routines. information_schema.routines is a system view which returns all the sprocs defined in the database.

You can have a look at the below example to know the usage of it. The below query shows the list of sprocs whose definition contains the word "customers". In general terms I am using this query to know what all sprocs are operating on the table "customers"

use northwind
go
select routine_name,routine_definition from information_schema.routines where routine_definition like '%customers%'

This is very useful when there is a huge database with many number of tables and many sprocs.

It was useful to me as our project was very complex and huge.