code & chips

Recently I have taken to using a lot of xml in my stored procedures.  I find they are easier to use for manipulating parent child updates.

I don’t particularly care too much for cycling through each child record and updating the database.  Especially, as most of the times nowadays, the database does nothing with the data but store it!

This way, for me, not only allows me to make one call, which can then be run on another thread to further process the record, but it also allows me to know something or everything about all related items of that record.

I send the xml interpretation of my class to the stored procedure as a string.  I write a custom serialiser:

   1: private static string SerialisePeople(IEnumerable<People> people)

   2: {

   3:     var builder = new StringBuilder();

   4:     var settings = new XmlWriterSettings { Encoding = Encoding.UTF8, OmitXmlDeclaration = true };

   5:     using (var writer = XmlWriter.Create(builder, settings))

   6:     {

   7:         if (writer == null) return null;

   8:  

   9:         //writer.WriteStartDocument();

  10:         writer.WriteStartElement("ppl");

  11:         foreach (var person in people)

  12:         {

  13:             writer.WriteStartElement("pn");

  14:             writer.WriteElementString("id", person.Id.ToString());

  15:             writer.WriteElementString("name", person.Name.ToString());

  16:             writer.WriteEndElement();

  17:         }

  18:         writer.WriteEndElement();

  19:         //writer.WriteEndDocument();

  20:     }

  21:  

  22:     return builder.ToString();

  23: }

In the stored procedure, I can hydrate this xml into a pseudo table by:

   1: DECLARE @people XML

   2: SET        @people = '<ppl><pn><id>7</id><n>Kam</n></pn><pn><id>127</id></pn><pn><id>9</id></pn></ppl>'

   3:  

   4: SELECT    doc.col.value('id[1]', 'int') id

   5:         , doc.col.value('n[1]', 'varchar(100)') name

   6: FROM    @people.nodes('/ppl/pn') doc(col)

At this point, I could insert all the records into a table variable and process them in one call.  I can also, save the xml on the parent record.  This allows me to bring all the information back in one call to one table and allows me to deserialise the xml back into an object.

Consider we have an order table with order lines;  I do not propose you save the whole order line xml to the order table, but maybe a subset of it’s information to act as header information of the order lines.  This would act as a hash allowing you to aggregate orders that are the same without having to join to the order line table.  It would also allow you send back header information in one hit from one table.

Other times, I find it easier to output the results of my stored procedure as xml.  This allows me to deserialise directly into an object.

   1: SELECT  pkId id, name nm

   2: FROM    dbo.Person pn

   3: FOR     XML AUTO, ELEMENTS, ROOT('person')

And for completeness sake, the way I convert a comma delimited list into xml assuming the following user defined function exists:

   1: CREATE FUNCTION [dbo].[udfCSVSplit] ( @csvList nvarchar(4000) )

   2: RETURNS @result TABLE 

   3: ( 

   4: id int 

   5: )

   6: AS

   7: BEGIN

   8:  

   9:     DECLARE @string int

  10:  

  11:     WHILE LEN(@csvList) > 0

  12:     BEGIN

  13:         SET @string      = LEFT(@csvList, 

  14:                                 ISNULL(NULLIF(CHARINDEX(',', @csvList) - 1, -1),

  15:                                 LEN(@csvList)))

  16:         SET @csvList = SUBSTRING(@csvList,

  17:                                      ISNULL(NULLIF(CHARINDEX(',', @csvList), 0),

  18:                                      LEN(@csvList)) + 1, LEN(@csvList))

  19:  

  20:         INSERT INTO @result ( id )

  21:         VALUES ( @string )

  22:     END

  23:     

  24:     RETURN

  25: END

   1: declare @ids varchar(100)

   2: set @ids = '1,2,3,4,5,6,7,8'

   3:  

   4:  

   5: SELECT  id 

   6: FROM    udfCSVSplit(@ids) mo

   7: FOR     XML AUTO, ELEMENTS, ROOT('eg')

So you want to execute DDL from your C# code.  I found this helpful when I was writing integration tests.

var conn = new SqlConnection(connString);
              
var server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(script);

ArrayList

• This is a dynamically sizable array. It is useful when you do not know the required array size at design time.
• Avoid ArrayList for storing strings.
• Use ArrayList to bind the read-only sorted data to a data grid as a data source.
• Use ArrayList for zero-based index access to the data.

Hashtable

• This is a collection of key/value pairs that are organized based on the hash code of the key. It is appropriate when you need to search but not sort.
• Hashtable is suitable for large number of records.

HybridDictionary

• This uses a ListDictionary when the collection is small, and switches to Hashtable when the collection gets large.
• Use HybridDictionary for frequently queried data.
• Do not use HybridDictionary to sort data. It is not optimized for sorting.

ListDictionary

• This is useful for storing 10 or less key/value pairs.

NameValueCollection

• This is a sorted collection of associated String keys and String values that can be accessed either with the key or with the index.
• Use NameValueCollection for sorting strings.
• Use NameValueCollection to store strings of key/value pairs in a pre-sorted order.
• Use NameValueCollection for frequently changing data.

Queue

• This is a first-in, first-out collection that implements ICollection.
• Use Queue when you need to access data sequentially, based on priority.

SortedList

• This is a collection of key/value pairs that are sorted by the keys and are accessible by key and by index.
• Use SortedList for fast object retrieval using an index or key.
• Use SortedList for sorting data that is mostly static and needs to be updated only infrequently.
• Avoid using SortedList for large data changes because the cost of inserting the large amount of data is high.
• The SortedList is a dictionary class that supports sorting.

Stack

• This is a simple last-in, first-out collection of objects.
• Use Stack where you can discard the items after processing it.
• Use Stack where you do not need to access arbitrary items in the collection.
• Specify the initial capacity if you know the size.

StringCollection

• This is a strongly typed array list for strings.
• Use StringCollection for zero-based index access to the data.
• Use StringCollection to store string data that changes frequently and needs to be retrieved in large chunks.
• Do not use StringCollection for sorting strings or to store presorted data.

StringDictionary

• This is a hash table with the key strongly typed to be a string rather than an object.
• Always prefer StringDictionary over Hashtable for storing string key/value pairs if you want to preserve the string type to ensure type safety.

OrderedDictionary

• Use the OrderedDictionary to store name/value pairs in a way that preserves the order of adding them to the collection.
• When you need a fast dictionary but also need to keep the items in an ordered fashion.

BitArray

• The real power of the BitArray is in its ability to perform Boolean operations on twoBitArray objects (of the same size).

BitVector32

• It is fixed at 32 bits. It deals with sets of Boolean values.
• The BitVector32 is not a resizable collection at all.
• BitVector32 stores all its data as a single 32-bit integer.

Source/Hat Tip: Anils Code Gallery