3 Tips for DataSets and Transactions
Learn how to create a helper function that can fill both untyped and typed DataSets, and more.
by Jimmy Nilsson

VSLive! San Francisco, February 12, 2003

Note: Jimmy Nilsson is presenting "Data Containers for .NET" at VBITS San Francisco, Wednesday, February 12, and "In-Depth Transaction Design" on Thursday, February 13. These tips are from those sessions.

Binary Serialization of DataSets Isn't Very Effective
Despite what I think Microsoft says in a recent architecture paper, "Application Architecture for .NET: Designing Applications and Services," custom classes and collections might be much more efficient than DataSets. One example of that is when you need to send data over a remoting boundary with binary serialization.

Assume you need to send some orders in a data container. If you choose a DataSet as the data container, it will—according to my tests—take approximately three times as long as if you had used a custom order collection as the data container. And that is end to end, meaning it includes the time to fetch the order from the database and the time to inspect each value in the order at the consumer side.

The biggest reason for this is probably that the DataSet is serialized as XML even when a binary formatter is used, so the DataSet will be approximately five times as large as the custom collection over the wire. See for yourself by using this code on a DataSet:

Dim fs As IO.FileStream = New _
	IO.FileStream("c:\temp\ds.txt", _
	IO.FileMode.Create)

Dim bf As New System.Runtime.Serialization. _
	Formatters.Binary.BinaryFormatter _
	(Nothing, New _
	Runtime.Serialization.StreamingContext _
	(Runtime.Serialization. _
	StreamingContextStates.Remoting))

bf.Serialize(fs, anOrderDS)
fs.Close()

The code will serialize the DataSet with a binary formatter and write it to a file.

Then open the file in Notepad. When you know about this problem and you can't live with it, you can choose a workaround such as wrapping the DataSet and writing a custom serialization implementation. You could, of course, also choose another data container if you don't see a big advantage in the built-in functionality of the DataSet.

The Same Helper Can Fill Both Untyped and Typed DataSets
A common question at the newsgroups is how to create a helper function that can fill both untyped and typed DataSets. One solution to the problem is not to use a function that returns a filled DataSet that the helper function itself instantiated, but instead let the consumer of the helper function (or rather sub) to do the instantiation of the correct type. The DataSet (untyped or typed) variable is then passed as a ByVal parameter to the sub.

Here's an example of consumer code that uses a generic sub to instantiate an untyped DataSet:

Dim anOrder As New DataSet()
FillDataSet(anOrder, ...)

And consumer code that uses a generic sub to instantiate a typed DataSet:

Dim anOrder As New OrderDataSet()
FillDataSet(anOrder, ...)

Sub-SELECTs Aren't a Valid Trick for Lowering the TRANSACTION ISOLATION LEVEL
It's quite common to hear that you don't have to increase the TRANSACTION ISOLATION LEVEL to REPEATABLE READ or SERIALIZABLE if you do the reading before writing in one single expression, with the help of a sub-SELECT. Here, you can see the code needed for a certain task with ordinary, "unoptimized" code:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
 
SET @theSum = 
(SELECT SUM(x) 
FROM OneTable)
 
INSERT INTO AnotherTable
(y)
VALUES
(@theSum)
 
COMMIT TRAN

Whereas here you can see the code "optimized," with a sub-SELECT:

INSERT INTO AnotherTable
(y)
SELECT SUM(x) 
FROM OneTable

Unfortunately, this isn't true. It's true that you don't need an explicit transaction, but you do need to increase the transaction isolation level. To prove the trick doesn't work, I ran this code 1 million times from two different SQL Query Analyzer windows:

INSERT INTO YetAnotherTable
(Id, Description)
SELECT COALESCE(MAX(Id),0)+1, 'Testing' 
FROM YetAnotherTable

I got a duplicate key about 20 times.

By changing to SERIALIZABLE, I had a couple deadlocks, which is to be expected in this case because I'm escalating the lock level, but using WITH (UPDLOCK) makes it work without errors:

INSERT INTO YetAnotherTable
(Id, Description)
SELECT COALESCE(MAX(Id),0)+1, 'Testing' 
FROM YetAnotherTable WITH (UPDLOCK)

The moral of the story: If correctness is important for you, the same rules regarding TRANSACTION ISOLATION LEVEL apply to sub-SELECTs as well as to "read before" SELECTs.

About the Author
Jimmy Nilsson is the owner of the Swedish consulting company JNSK AB. He has been working with system development for more than 13 years. He has written numerous technical articles and is the author of .NET Enterprise Design with Visual Basic .NET and SQL Server 2000. Reach Jimmy at jimmy.nilsson@jnsk.se.