Archive

You are currently browsing the archives for the Coding category.

Oct

20

How to perform an SQL “IN” query in Linq

By pcurd

Using Linq to achieve a query that in SQL that uses the “IN” keyword – i.e. to check a value against a range of values – requires use of the .Contains method on a new array of the range of values.

For example, if I need to exclude from reports the following customers:

  1. Internal
  2. Demo
  3. Sample
I would in SQL use something like:
CustomerName NOT IN ('Internal', 'Demo', 'Sample')
In Linq I need something like:
!(new[] {"Internal", "Demo", "Sample"}).Contains(CustomerName))

Appropriately wired into EF or your ORM of choice.

I think this is confusing since it is written backwards to a SQL programmer, so it reads “does this list of things contain the value?” instead of “is the value in this list of things?” as we are used to in SQL.

Jan

28

How to get SQL Server Table Row Counts fast and efficiently

By pcurd

SQL Server Central ran an article this morning about using a system DMV to get table row counts without doing a table scan (so it’s VERY fast and has no performance hit).

I decided to test this on my test server, a virtual machine running on a Dell R900. I ran the queries four times, discounting the first time to be sure all were in memory, and averaging the last three for the following values.

Read more »

Jul

1

The FizzBuzz problem

By pcurd

Amy Kimber posted an article today referring to a post from Nick Telford on twitter:

Will The Real Programmers Please Stand Up? http://retwt.me/1NEBw // another example of how diluted our industry really is (link)

This interests me as I often find job interviews to be very generic and after about four in a row you get to know all the answers and appear much cleverer than you perhaps are.  I like a problem that makes you think and although FizzBuzz is a trivial problem, I wanted to answer it with a little bit of real world thinking.

To quote Amy, the FizzBuzz problem is “The idea is simple, all you have to do is write a program that prints out the numbers 1 to 100, but for multiples of 3, print Fizz instead of the number and for multiples of 5 print Buzz. If the number is a multiple of both, FizzBuzz should be printed.”

In the real world, specs change. Today it’s 3 and 5 and tomorrow it’s 4 and 9 and we need to add an extra one “Bibble” for 11.  I decided to solve the problem with a list of number and word pairs so that changing it would be easy.  .Net provides a nice KeyValuePair generic which I used as Int,String.

Originally I had this implemented using a Dictionary but as FizzBuzz must be implemented the correct way around (not BuzzFizz) I needed to change this to a SortedDictionary to ensure order of execution.

Code is as below:

using System;
using System.Collections.Generic;

namespace FizzBuzz
{
    class Program
    {
        static ICollection<KeyValuePair<int, string>> wordlist;
        static void Main(string[] args)
        {
            wordlist = new SortedDictionary<int, string>();
            wordlist.Add(new KeyValuePair<int, string>(3, "Fizz"));
            wordlist.Add(new KeyValuePair<int, string>(5, "Buzz"));
            PrintFizzBuzz(1, 100);
            Console.ReadKey();
        }

        static void PrintFizzBuzz(int start, int finish)
        {
            for (int i = start; i <= finish; i++)
            {
                Console.WriteLine(CalculateFizzBuzz(i));
            }
        }

        static object CalculateFizzBuzz(int i)
        {
            string CalculateFizzBuzz = "";
            foreach (KeyValuePair<int, string> word in wordlist)
            {
                if (i % word.Key == 0)
                    CalculateFizzBuzz += word.Value;
            }
            if (CalculateFizzBuzz.Length == 0)
                CalculateFizzBuzz = i.ToString();

            return CalculateFizzBuzz;
        }
    }
}

Update 27th February 2012:

Many great answers in the comments below, great to see how different languages can solve this problem. My solution is designed around flexibility and the ability to change the list of “Fizz”es and “Buzz”es which takes away from the brevity possible!

I found this article today from Calvin Bottoms regarding implementing FizzBuzz in Haskell – at 78 characters it’s impressive and his explanation of how it develops is a great read.

Apr

1

Find Query Plans on Microsoft SQL Server

By pcurd

Today I was asked by Luke Smith a question about Query Plans in Microsoft SQL Server and how to return the Cached Query Plans to check they are decent.

I began to think about the DMV (Dynamic Management View) sys.dm_exec_cached_plans and whilst that includes some important information, it doesn’t include the plan itself.  So then I turned to another DMV, sys.dm_exec_query_plan passing it the plan_handle from the first DMV.

Finally, to be useful as a diagnostic tool I wanted the query text so I added in a third DMV sys.dm_exec_sql_text again passing the plan_handle. I then sorted by usecounts which does what it says on the tin and limited to those that are not system procedures, are actually plans, as opposed to records from parse trees or extended stored procedures, and have been used more than 10 times.  This returned a lot of data for me so I limited to the top 100, but this could obviously be tuned to a different environment.

Running this in SQL Server Management Studio has the bonus of being able to click on the XML in the query_plan and view the plan graphically which is definitely better than the XML!
Read more »

Nov

23

Powershell to run Get-MessageTrackingLog on multiple machines

By pcurd

I have an infrastructure with multiple Exchange 2007 servers and wanted to compile a list of all emails sent by two email addresses. Whilst Get-MessageTrackingLog did what I wanted, it only ran on one server at a time and would only search for one email address.

To get around this I wrote a PowerShell script to run Get-MessageTrackingLog for each sending email address and each computer. This is then combined into one CSV file for transfer to Excel.

$computers = "server1", "server2"
$senders = "Email1@domain.com", "Email2@domain.com"
$outputfile = "C:\output.csv"

$start = "DD/MM/YYYY HH:MM:SS"
$end = "DD/MM/YYYY HH:MM:SS"

$(foreach ($sender in $senders) {
$(foreach ($computer in $computers) {
get-messagetrackinglog -Sender $sender -Start $start -End $end -EventID "RECEIVE" -Server $computer | select Timestamp, @{Name="Recipients";expression={$_.Recipients}}, MessageSubject, Sender

})}) | sort-object -property timestamp | Export-CSV -path $outputfile -notype

Mar

22

Rookie Mistake 101

By pcurd

Note to self:

STOP DATABINDING DATAGRIDS IN PAGE_LOAD WITHOUT HANDLING THE POSTBACK!!!!

Thank you.

Apr

18

A forage into Ruby

By pcurd

I’ve been experimenting with Ruby (and with the Rails framework) the last few days after a need came up for a console based application with a large degree of string manipulation. I have been using Python and Perl for similar jobs before – in fact I run a number of Python scripts that have proved to be extremely useful. A lot of the legacy code I work with is written in VBA (Visual Basic for Applications) within Access and Excel so this was another possible choice.

I wanted to avoid hosting my code within Access due to a possible rollout of Access 2003 soon and the fact that I needed to access mailstores – the thought of using ADO scared me and I don’t have a machine I can dedicate a copy of Outlook to so this effectively ruled out VBA.

Perl has loads of libraries available on CPAN and Python seems to be going the same way but the quality of community code can be suspect – I’ve never run into a problem with it myself, I use CPAN libraries all over the place – especially on my web server – and I didn’t want to be supporting third party libraries as well as my own code.

We are effectively a C# shop for new development – just myself and my boss keeping the legacy code up to date really – so I had to consider this as an option. .Net provides a nice SMTP library but not POP3 or IMAP… so that brought me back to the same problem I had with Perl and Python. I spent about half a day with a CodeProject IMAP library but it was not feature complete and after scoping out the changes I’d need decided I might as well have written the library myself.

I’ve been looking into Ruby and Rails recently for a personal database project and knew that Rails provided a lot of functionality. Now, I know that technicaly Rails is a community third party library but it is so much more refined and integrated than a hodge podge of CPAN modules thrown together. And, Rails provides IMAP straight “out of the box”. I loaded up Ruby.Net only to find that neither them or IronRuby supports Rails yet! This project was proving more complicated than I had expected – and I hadn’t written a line of code yet.

So I made the decision to write this application in neat Ruby on Rails without linking to .Net binary in the hope that Ruby.Net will catch up eventually! (Rails support is expected in the next version apparently)

My first surprise was that Rails just works. I did not have to go through any rigmarole to get Net::IMAP to work – just

require 'net/imap'

and I was off. As I didn’t have access to the rails scaffold I used DBI to access a Microsoft SQL Server instance with the amazingly complicated

require 'dbi'

which is shorter even than PHP’s database linking code. Opening a handle with

dbh = DBI.connect('DBI:ODBC:MY_DSN')

gave me immediate access to the ability to run SQL with sensible data returns.

Suffice to say, I was impressed. A few helper functions later, and some hastily learnt Ruby conditional statements for error catching

var = var ? var : " "

to remove nils from strings – this technique even works when passing parameters – amazing. Features available in Perl like

run_my_long_complex_function(variable) unless defined?(use_simple_mode)

are so much cleaner and clearer to read in Ruby – plus the excellent “PickAxe” (Programming Ruby – possibly the best Ruby textbook) not only encourages this technique – it’s the one it teaches you.

More impressive and powerful functions that let you pass in chunks of code (‘blocks’) using “yields” are likely to be my next excitement but so far I haven’t needed them.

The only thing I can say is that I had some 22 lines of error checking code using if then else etc – Ruby first impressed with the ability to use ? : notation which reduced it to about 8 lines. Then I realised that I could use conditional formatting like the unless example above. Then Ruby amazed me with the ability to use them both together – did you realise that if blocks are expressions, not statements – Ruby did. That code is now 1 line – and it works just as well.

I muchly look forward to developing the rest of the feature set in Ruby as I’m sure it’ll keep impressing me with it’s ability to make programming interesting again!