Get every n-th record using SQL Server

First, a few things you should already know:
Modulus Returns the remainder of one number divided by another.
SELECT 32/10 -> 3
SELECT 32%10 -> 2

To return the n-th row only of a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = [value of n]

To return every n-th row in a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE [row]%[value of n] = 0

Example:

Return every 10th event from an events table:

WITH tableEvents AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY EventDate)) as row,*
    FROM tblEvents
	Where Month(EventDate) = 7)
SELECT * FROM tableEvents WHERE [row]%10= 0
Advertisements

How to check if a constraint exists

In SQL Server, you can check if a constraint exists using the following script:

SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='[constraint name here]'

or by running one of the following commands:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_Name = '[TableName]'

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each FOREIGN KEY constrain
SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each CHECK constraint
SELECT *
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

Assembly Language: How to Make Sounds example

This code example provides a set of keyboard routines to control sound output while waiting for a user to enter a keyboard character. The advantage to this method is that a main routine can call these sound routines to play a sound sequence, and the sound routines will return control back to the main routine whenever the user enters keyboard data so that the main routine can continue computing while the sound plays in the background. The code example has two different code entry points for getting keyboard data. One code entry point is a standard get_keyinput call which will wait for a key and update the sound data until a key code is found. The other code entry point is the get_keyinput_to call, which will wait a set amount of time for a key code and if none is found, return with a no key code found condition. The calling routine puts a timeout counter value in register AX on entry. The counter value is based on the system clock which ticks at 18.2 times per second. The entry point start_table_sound is used to begin a background sound sequence. On entry, the register BX indexes a table of sound data. The table has a format of four byte entries and is terminated by a data word of zero. The four bytes are used as two words: the first is a duration count and the second is a tone value. There are two code entry points for turning the background sound off and on. There is also a utility to flush out the keyboard buffer that can be executed with a call to flush_keyboard. Continue reading “Assembly Language: How to Make Sounds example”

How to check if a file is an image

You can always check the extension to be “jpg, jpeg, gif, tiff, png, bmp” but sometimes, malicious attackers can upload an exe file with the wrong extension and then use a series of commands to remove the extension/run the file on the server.

How to check extension:

public static readonly List ImageExtensions = new List { ".JPG", ".JPE", ".BMP", ".GIF", ".PNG" };

private void button_Click(object sender, RoutedEventArgs e)
{
    var folder = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    var files = Directory.GetFiles(folder);
    foreach(var f in files)
    {
        if (ImageExtensions.Contains(Path.GetExtension(f).ToUpperInvariant()))
        {
            // process image
        }
    }
}

The other option would be .NET 4.5:
MimeMapping.GetMimeMapping Method

Or:

static Extension()
    {
        ImageTypes = new Dictionary();
        ImageTypes.Add("FFD8","jpg");
        ImageTypes.Add("424D","bmp");
        ImageTypes.Add("474946","gif");
        ImageTypes.Add("89504E470D0A1A0A","png");
    }

    /// 
    ///      Registers a hexadecimal value used for a given image type 
    ///      The type of image, example: "png" 
    ///      The type of image, example: "89504E470D0A1A0A" 
    /// 
    public static void RegisterImageHeaderSignature(string imageType, string uniqueHeaderAsHex)
    {
        Regex validator = new Regex(@"^[A-F0-9]+$", RegexOptions.CultureInvariant);

        uniqueHeaderAsHex = uniqueHeaderAsHex.Replace(" ", "");

        if (string.IsNullOrWhiteSpace(imageType))         throw new ArgumentNullException("imageType");
        if (string.IsNullOrWhiteSpace(uniqueHeaderAsHex)) throw new ArgumentNullException("uniqueHeaderAsHex");
        if (uniqueHeaderAsHex.Length % 2 != 0)            throw new ArgumentException    ("Hexadecimal value is invalid");
        if (!validator.IsMatch(uniqueHeaderAsHex))        throw new ArgumentException    ("Hexadecimal value is invalid");

        ImageTypes.Add(uniqueHeaderAsHex, imageType);
    }

    private static Dictionary ImageTypes;

    public static bool IsImage(this Stream stream)
    {
        string imageType;
        return stream.IsImage(out imageType);
    }

    public static bool IsImage(this Stream stream, out string imageType)
    {
        stream.Seek(0, SeekOrigin.Begin);
        StringBuilder builder = new StringBuilder();
        int largestByteHeader = ImageTypes.Max(img => img.Value.Length);

        for (int i = 0; i  img == builtHex);
            if (isImage)
            {
                imageType = ImageTypes[builder.ToString()];
                return true;
            }
        }
        imageType = null;
        return false;
    }

How to run a Windows Service in Console Mode

Run Windows Service as a console program

Visual Studio and the .NET framework make it really easy to create Windows Services. All you have to do is create a new project, select “Windows Service” as your project type and you’re all set. However, debugging Windows Services in Visual Studio can be a big pain. The recommended way is to use InstallUtil to install them, and then restart the service and attach the debugger everytime you want to debug it. I wanted to be able to debug it without the hassle, so here’s what I came up with:

using System;
using System.ServiceProcess;
 
public partial class DemoService : ServiceBase
{
    static void Main(string[] args)
    {
        DemoService service = new DemoService();
 
        if (Environment.UserInteractive)
        {
            service.OnStart(args);
            Console.WriteLine("Press any key to stop program");
            Console.Read();
            service.OnStop();
        }
        else
        {
            ServiceBase.Run(service);
        }
 
    }
    public DemoService()
    {
        InitializeComponent();
    }
 
    protected override void OnStart(string[] args)
    {
        // TODO: Add code here to start your service.
    }
 
    protected override void OnStop()
    {
        // TODO: Add code here to perform any tear-down
        //necessary to stop your service.
    }
}

This will allow you to use your program as either a normal console program or a windows service, with no special builds, #DEBUG directives, command line parameters or anything like that. What it does is in the Main method it checks the “Environment.UserInteractive” property. This will be true when it is run from Visual Studio, or when you just click on the .exe file, but false if it’s being run as a service. When it’s run from Visual Studio or as a standalone program it will keep running until you press a key, then it will call your OnStop method and then terminate.

Two things to watch out for:

  1. You’ll have to right click on your project in Visual Studio, choose Properties and select the Output type as “Console application” for this to work.
  2. If your Main method is not in your service class, you’ll have to add public methods to your class that can start and stop it, for instance add a public void StartConsole(string[] args) that just calls your OnStart, since OnStart and OnStop are protected methods and as such not accessible from other classes.

List all tables in the database along with their row counts

There is a catalog view that will give us this information. The benefit of this approach is that it uses figures that have already been calculated by SQL Server so it should run very quickly. Here is the script :

SELECT T.name TableName,i.Rows NumberOfRows
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1)
ORDER BY i.Rows DESC,T.name

The script uses the clustered index to source the information (where indid=1). Tables that don’t have a clustered index are stored as a heap but still have a row in sysindexes (where indid=0) containing the rowcount. There is one important caveat, though, the figures may not be entirely accurate ! This is because SQL Server doesn’t always keep this bang up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the script above to make sure the figures are accurate :
DBCC UPDATEUSAGE(0)