code & chips

(from c in clients

join s in sales on c.Id equals s.clientId into temp

from s in temp.DefaultIfEmpty()

join p in products on c.id equals p.clientId into temp2

from p in temp2.DefaultIfEmpty()

select new {Client = c, Sale = s == null ? new EmptySale() : s, Product = p}).ToList()

 

This should produce a collection of objects with a class structure with three properties: Client, Sale and Product.

The last select line allows me to choose whether I use the ‘null’ like I have for ‘Product’ or handle the ‘null’ like I have for ‘Sale’.

I ran a stored procedure through SQL Server Studio and it came back quick!

I ran the same stored procedure through C# code and it ran slow.

The solution?

SET ARITHABORT ON

GO

CREATE PROCEDURE dbo.SomeProcedure

AS

BEGIN

(…)

END

Not altogether sure why though…..ho hum…

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

I know it’s old….but it’s useful:

   1: SELECT    ROW_NUMBER() OVER (ORDER BY clientId) AS rowNumber, 

   2:           clientName, 

   3:           age

   4: FROM      DBO.Client

I wanted to create a class that stored a private collection but when you bound the class to a component or iterated through, it would only display a filtered list.

It turns out, that I just needed to implement the IEnumerable<T> interface:

   1: 1: public class MyClassCollection : IEnumerable<MyClass>

   2: 2: {

   3: 3:     private readonly List<MyClass> _myClasses;

   4: 4:     

   5: 5:     internal MyClassCollection()

   6: 6:     {

   7: 7:         _myClasses = new List<MyClass>();

   8: 8:     }

   9: 9:  

  10: 0:     public IEnumerator<MyClass> GetEnumerator()

  11: 1:     {

  12: 2:         foreach (var x in _myClasses)

  13: 3:         {

  14: 4:             yield return x;

  15: 5:         }

  16: 6:     }

  17: 7:     

  18: 8:     System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()

  19: 9:     {

  20: 0:         foreach (var x in _myClasses)

  21: 1:         {

  22: 2:             yield return x;

  23: 3:         }

  24: 4:     }

  25: 5: }

Just had a ‘king nightmare with the whole ‘single user’ mode on SQL Server 2005.  I tried to restore a database backup, but SQL Server complained that there were users connected.

So I disconnected them by putting the database into ‘single user’ mode.

Thinking everything was fine, i continued to try the restore.

No joy.

So I logged in as SA.

No joy.

Now I was getting worried as I thought I may be locking myself out slowly.

Nothing was working.

I tried all the normal ‘google gospel’.

ALTER DATABASE [XXX] SET MULTI_USER WITH NO_WAIT

EXEC SP_DBOPTION 'XXX, 'SINGLE USER, 'FALSE;

Log in as SA and try a drop of the database

Nothing.

Nada.

Jack!

So I went Old Testament God on it.

EXEC SP_WHO

to find out what processes were running and hooked into my database then:

KILL @processId

Then retried the restore.

HOORAY!!!!

Success!

Be it good practice or not it worked. And in the world of the ‘one man band’ freelancer – this information is invaluable.

UPDATE: by pure chance I found the SQL that worked:

ALTER DATABASE TestCratos SET offline with rollback immediate

Hooray!!!  I now have my database restore in a script to compliment the automated front end testing with Selenium!!!

This is a piece of code that I just know I will forget.  So for future reference I shall keep it here:

public static T NumToEnum<T>(int number)

{

    return (T)Enum.ToObject(typeof(T), number);

}

public static T StringToEnum<T>(string name)
{
    return (T)Enum.Parse(typeof(T), name);
}

“What makes the deployment element special is that it is a machine level configuration element. When its retail attribute is set to true, it will disable the <compilation debug=”true”> for ALL ASP.Net applications running on that machine. In addition, it turns of detailed errors messages being sent to remote machines and disables the ability to trace output.”

Source: Aggregated Intelligence