Code

Search entire SQL Server database and all tables for a specified string

Having a neatly organized datalayer, perhaps a great ORM will make any developers life easier. But there are times when the best architecture simply isn’t good enough to answer a simply question like: “Are there any occurences of ‘a-specific-peace-of-text’ in the database?”. Sometimes you just need to look through all the tables and views to answer this.

Trivial, but inevitable.

Luckily, using the stored procedure defined below, originally by Narayana Vyas Kondreddi, this becomes a breeze. Simply create it in the database and use it like so:

exec SearchAllTables ‘gummy bears’;

This executes a case insensitive search on all tables in the relevant database, and neatly spits out the fields where the string occurs in.

Here is the stored procedure:

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com + http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

Enjoy!

Code, Projects

ASP.NET MVC 5 Template for Xamarin Studio

Recently the latest version of Xamarin Studio has been released, along with Mono 3.4. And honestly, no one can deny that cross platform .NET development just keeps on getting better and better. I still see a great future for especially Mono if the open source community and the people at Xamarin manage to keep their current pace of improvements. #kudos++

Although with every update of both Xamarin and Mono the bar is set higher, some fields of interest seem to be lacking to get updated. Clearly the mobile platforms, like iOS, Android and Windows Phone seem to get all the attention. But the web platform seems to lose focus in the Xamarin/Mono tagteam, especially ASP.NET.

For example, the native template for ASP.NET is still set to version 3. Which was the non-MyGet version, if you remember. And that means b*tchy to update and cumbersome to update to recent versions. However, it is still possible to actually run and debug new ASP.NET MVC sites on Mono.

To this end, I’ve created a simple and clean setup for a Xamarin project that allows you to run an ASP.NET MVC 5.1 site on Mac OSX. In short:

Fork it on GitHub

Grab a fresh copy from the git repo and you’ll get:

  • A clean ASP.NET MVC 5 application with RAZOR support
  • Can be run entirely from Xamarin Studio on Mac OSX
  • Can be updated and extended through NuGet
  • It just works…

Known Issues

Do note that an Empty ASP.NET MVC 5 project created in Visual Studio won’t run by default in Xamarin. You have to plumb a bit here and there. If you’re curious on what you need to do to make things blend on Xamarin Studio on Mac, take a look at the changes I’ve made in the repo.

Tip: Want to reproduce it yourself? All required changes to run ASP.NET MVC 5 on Mac are neatly documented as seperate commits.

To summarize these changes, the current limitations for a clean setup seems to be:

  • App relative paths don’t work, e.g. using tildes like ~/assets/style.css
  • The view’s web.config has teh disabled
  • Mono currently supports .NET 4.5, not 4.5.1
  • routes.LowerCaseUrls isn’t supported (yet)
  • System.Web.Entity reference isn’t supported (yet)
Code

Create large files with a specific size

In order to test the latency on a specific server configuration, we simply wanted to test the throughput rate of downloading large files. The only question is: how can you efficiently create several (large) files with an exact size?

As easy as two lines, I can tell you. The snippet below basically writes a file with spaces, using a stream, until the designated size is reached. This to accomplish an exact file size.

    /// <summary>
    /// Creates a file with a specific size.
    /// </summary>
    /// <param name="outputFilePath">The full output path for the file. Overwrites if exists.</param>
    /// <param name="length">Filesize in bytes</param>
    static void CreateFileWithSpecificSize(string outputFilePath, long length)
    {
        using (var stream = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write, FileShare.None))
        {
            stream.SetLength(length);
        }
    }
Code

Drop an entire collection in RavenDB

Deleting a single document in RavenDB is easy. Deleting several documents is too. Deleting an entire collection of N-documents isn’t supported by default.

Here is a great extension that deletes an entire collection from your RavenDB document store:

    /// <summary>
    /// Deletes, or 'drops', an entire collection from the datastore, by the specified type.
    /// </summary>
    public void Drop<T>()
    {
        // Determine the concrete type of T
        Type g = typeof (T);

        // Delete the entire collection
        CurrentSession.Advanced.DocumentStore.DatabaseCommands.DeleteByIndex("Raven/DocumentsByEntityName",
            // ouch, be weary of the 's' suffix here (pluralization thingie)
            new IndexQuery { Query = "Tag:" + g.Name + "s" },
            allowStale: true
        );
    }
Concepts

DRY

DRY, as in Don’t Repeat Yourself, is another fundamental principle in coding. And it’s all about avoiding repetition, or doing the same thing twice.

Rule of thumb: if you use the same code more than once in your project, you’re probably doing it wrong.

Code

Iterate through a hierarchical list of infinite child elements

Example that flattens a nested and, possibly infinite, hierarchical list of child elements.


    class Program
    {
        static void Main(string[] args)
        {
            // Create dummy categories - not especially pretty
            var categories = new List();
            categories.AddRange(new List()
                                    {
                                        new Category(){ Id = 1, Name = "Cat1", Children = new List()
                                                                                              {
                                                                                                  new Category(){ Id = 101, Name = "Subcat1"},
                                                                                                  new Category(){ Id = 102, Name = "Subcat2"},
                                                                                                  new Category(){ Id = 103, Name = "Subcat3WithChildren", Children = new List()
                                                                                                                                                                         {
                                                                                                                                                                             new Category() { Id = 10301, Name = "Subcat1-sub1" },
                                                                                                                                                                             new Category() { Id = 10302, Name = "Subcat1-sub2" }
                                                                                                                                                                        }
                                                                                                                }
                                                                                              }},
                                        new Category() { Id = 2, Name = "Cat2"}
                                    });

            // Flatten the nesteded list of *child* categories
            var flatCategories = new List();
            foreach (var rootCategory in categories)
                flatCategories.AddRange(IterateChildCategories(rootCategory));

            // Spit out the results
            foreach (var cat in flatCategories)
                Console.WriteLine(cat.Name);

            Console.ReadKey();

        }

        /// 
        /// Iterates through a list of child categories. 
        /// 
        /// 
        /// Note that this skips the parent categories, and focusses only on the children.
        /// 
        private static IEnumerable IterateChildCategories(Category parent)
        {
            if (parent.Children == null) yield break;

            foreach (var g in parent.Children)
            {
                yield return g;

                foreach (var sub in IterateChildCategories(g))
                    yield return sub;
            }
        }

        public class Category
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public List Children { get; set; }

            public Category()
            {
                this.Children = new List();
            }
        }
    }

 

Concepts

YAGNI

Each and every piece of software will start with a design. Ranging from common sense, to a fully blown YAGNI, You ain’t gonna need it.

Rule of thumb: Don’t over-engineer things. Only write functionality when you need it right now. If you will not have a use right now for it, you will most likely not need it in the future. Even though you think else. Here are some typical statements that should ring some alarm bells when it comes to violating the YANGNI principle:

  • We may have a use for it in the future
  • This may come in handy sometime
  • I think the customer wants it this way

More often than not, when the YAGNI concept is violated it is presumably someone in the base forgot the KISS rule.