Order by clause is no longer supported in SQL Server 2005 Views

We used to have an application running on Sql Server 2000. And we built a view in which we put a Order By clause to pull data out in order. This works fine and the data is ordered. When we migrate the database to Sql Server 2005, we found the view is no longer ordered even the order by clause is still there. After some research on the net, we found out the Order By clause in a view has never been documented anywhere and this is not supported in Sql Server 2005 anymore.

To make an experiment, I created a view in Sql Server 2000 and 2005 as below:

Create View EmployeeView AS
SELECT TOP (100) PERCENT EmployeeID, LastName, FirstName
FROM dbo.Employees
ORDER BY LastName

When I run a query as Select * from EmployeeView against Sql Server 2000, I get the correct result with LastName ordered. When I run the same query against Sql Server 2005, the result is in the same order in the Employees table, not ordered by the LastName.

.net collection classes summary

There are many collection classes provided in .net framework. To use these classes appropriately is important for a developer. And it's quite essential to understand the subtle difference between each class.

These classes are summarized below.

1. ArrayList. This is the most common class used in .net 1.1. It's a resizeable, index-based collection of objects. Some important methods include Add, AddRange, Remove, Contains and IndexOf. This class is replaced by generic List<> class in .net 2.0. To interate over items in an ArrayList, we can use IEnumerator interface. eg.
IEnumerator en = ar.GetEnumerator();
while(en.MoveNext())
{
string s = en.Current as string;
...
}
ArrayList provides a Sort method. The sort method uses the default implementation of IComparer - Comparer class to do the comparison.

2. Queue class. This is a collection of objects with first-in, first-out feature. The Dequeue method read and remove the item from the front of the queue. The Enqueue method adds an item to the end of the queue. And Peek method only read the first item in the queue.

3. Stack class. The Stack class is a last-in, first-out collections of object. The Pop method reads and removes an ite mfrom the top of the stack. The Push method adds an item on the top of the stack. And the Peek method reads the last item without removing it.

4. Hashtable class. This is a dictionary class, providing a key/value pair of lookup table. eg.
Hashtable ht = new Hashtable();
ht["Key"] = "Value";
which is equivalent to :
ht.Add("Key", "Value")
If you try to Add a same key twice, the class will throw an exception, but you can assign a same key as many times as you like, only the last value is kept.
One important feature of Hashtable is that wecannot access item by index number.

5. SortedList class. This class sort items when adding them to the list, and items can be accessed by their index. The use of this class is quite similar to Hashtable.

6. ListDictionary class. This class is lightweight and efficient class for a small number of items. The usage is identical to Hashtable.

7. HybridDictionary class. HybridDictionary is a dynamic collection. When the number of items are small, it implement itself as ListDictionary. Whe number of items becomes two large, it turns itself to Hashtable.

8. OrderedDictionary class. This class provides the same functionalities of Hashtable, as well as the ability of access items by their index number.

9. BitArray. This class holds an array of boolean values. eg.
BitArray ba = new BitArray(2);
ba[0] = false; ba[1] = true;

10. BitVector32 structure. The BitVector32 structure is very useful to maintain individual stutus in a single integer. Its useful in WebService when network bandwidth is limited. With one integer value, it can contain 32 true or false status.

11. StringCollection class. A simple collection only stores strings.

12. StringDictionary class. Similar to Hashtable, except both key and value are strings.

13. CollectionUtil class can create Hashtable and SortedList that are case insensitive. eg.
CollectionUtil.CreateCaseInsensitiveHashtable();

14. NameValueCollection class is similar to StringDictionary class. The difference of this class is it allows multiple values share a same key. And values can be retrieved by index as well as key. eg.

NameValueCollection col = new NameValueCollection ();
col.Add("key", "Value 1");
col.Add("key", "Value 2"); // then we have two values with same key.

If you add two values with same indexer, only the last value is kept. eg.]
col["key"] = "Value1";
col["key"] = "Value2"; // only Value2 is kept.


15. Generic collection classes. Almost for each class mentioned above there is a generic version. The use of these generic classes are similar. The only difference of Dictionary classes is the way of retriving items. The generic class KeyValuePair is used to read each item. eg.
SortedList list = new SortedList();
list["one"] = 1;
list["two"] = 2;
foreach(KeyValuePair i in list)
{
string s = i.Key;
int value = i.Value;
}

Working with Isolated Storage

Isolated Storage is a special and safe place in the computer to save user data in machine/assembly scope or user/assembly scope. It eliminates the risk of making damages to the whole system by the user, and the application will run regardless of whether it's running uder partial, limited or full trust security level.

There are two important classes for isolated storage: IsolatedStorageFile and IsolatedStorageFileStream class. The IsolatedStorageFile class is responsible for creating files and directories in isolated storage. The IsolatedStorageFileStream class inherites from FileStream and is responsible for file IO.

To read data from isolated storage:

IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForAssembly();
// here we could also call IsolatedStorageFile.GetMachineStoreForAssembly() to get store for the application scope.

IsolatedStorageFileStream stream = new IsolatedStorageFileStream(fileName, FileMode.Open, store);
StreamReader rdr = new StreamReader(theFile);
string s = rdr.ReadToEnd();

To write data is similar:
IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForAssembly();
// here we could also call IsolatedStorageFile.GetMachineStoreForAssembly() to get store for the application scope.

IsolatedStorageFileStream stream = new IsolatedStorageFileStream(fileName, FileMode.Create, store);
StreamWriter writer = new StreamWriter(theFile);
writer.WriteLine("hello");

Various ways of file read/write in .net framework

I have summarized various ways of file IO in .net framework. There are many ways of doing same thing in file IO.


string path = @"C:\Temp\Test.txt";
// 1. Using System.IO.File.ReadAllLines to read lines of file content into an array of strings
string[] lines = File.ReadAllLines(path);

// 2. Using System.IO.File.ReadAllText to read the whole content of a file to a string
string s = File.ReadAllText(path);

// 3. Using StreamReader, File and FileStream calsses
FileStream theFile = File.Open(path, FileMode.Open, FileAccess.Read);
StreamReader rdr = new StreamReader(theFile);
string s = rdr.ReadToEnd();

// 4. Using StreamReader class only
StreamReader rdr = new StreamReader(path);
string s = rdr.ReadToEnd();

// 5. Using StreamReader and File class
StreamReader rdr = File.OpenText(path);
string s = rdr.ReadToEnd();

// 6. Using MemoryStream class to write to file to improve performance
MemoryStream memStrm = new MemoryStream();
StreamWriter writer = new StreamWriter(memStrm);
writer.WriteLine("hello");
writer.Flush();

// write content in MemoryStream to file stream
FileStream theFile = File.Create(path);
memStrm.WriteTo(theFile);

// 7. Using BufferedStream to improve performance.
FileStream file = File.Create(path);
BufferedStream buffered = new BufferedStream(file);
StreamWriter writer = new StreamWriter(buffered);
writer.WriteLine("hello");
writer.Close();

// 8. Using GZipStream to compress data. The source file is a normal file. The destination file is compressed.
FileStream source = File.Open(inFileName);
FileStream dest = File.Open(outFileName);

GZipStream comStrm = new GZipStream(dest, CompressionMode.Compress);

int theByte = source.ReadByte();
while (theByte != -1)
{
comStrm.WriteByte((byte)theByte);
int theByte = source.ReadByte();
}

// 9. Using GZipStream to decompress data. The source file is a compressed file. The destination file is normal.
FileStream source = File.Open(inFileName);
FileStream dest = File.Open(outFileName);

GZipStream comStrm = new GZipStream(dest, CompressionMode.Decompress);

int theByte = source.ReadByte();
while (theByte != -1)
{
comStrm.WriteByte((byte)theByte);
int theByte = source.ReadByte();
}

// 10. Using FileInfo class
FileInfo fileInfo = new FileInfo(path);
FileStream stream = fileInfo.Open(FileMode.Open, FileAccess.Read);

SCOPE_IDENTITY() Returns Decimal

We use SCOPE_IDENTITY() function quite often to get the last identity value generated by Sql Server. What confuses me first is this function returns Decimal value. I have a sql insert stored procedure which has this last statement:

Select SCOPE_IDENTITY(),

The table has a auto incremental id field.

In my client code I have this:

int id = (int) command.ExecuteScalar(...)

Initially I thought I would read the integer id value without problem, but soon I got an InvalidCastException. And I discovered that the return value from DbCommand is of Decimal value type.

When I looked into MSDN, which is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp

I found out the return data type of this function is sql_variant. So the actual datatype could be any as Microsoft may change it in the future.

This is really interesting. To return an integer from stored procedure, we could cast it to integer as : Select CAST(SCOPE_IDENTITY() as Int) . This solves problem.