Friday, April 25, 2014

How can I get the list of available databases on a SQL server instance?

Whenever you want to get the list of available databases on an instance you could use this query. You could also change it as will fit you better. This query it is helpful when you need a list of databases and the list is big, of course.

Query that runs well for Sql Server 2012:
SELECT * 
FROM sys.databases d
WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');
--or you could use a simpler condition which in my opinion is not so relevant 
--WHERE d.database_id > 4

Otherwise you could also use:
EXEC sp_databases

Cheers, Adrian

Friday, April 11, 2014

How to call for a dynamic OrderBy method for a LINQ?

As I wanted to do some experiences in Asp.Mvc 4 I needed a nice way to generate tables using Ajax calls for showing rows, edit, delete and create new item as well with filtering and paging functions
For that I have found this nice package jTable. You could go to http://www.jtable.org/ where you can see a demo by yourself.

Now that I have introduced the scenario I came to an issue. When implementing the sorting of columns by pressing on a column I had as an input parameter string jtSorting which had as a value: "Name ASC", so column name and ordering. Then in my controller's action I had a LINQ query that accessed also the OrderBy extension method which needs exactly the name of property. So I needed a method to order dynamically by property as a string.

Happily I found a solution. Here are my helper classes that I used in final extension method.

public static class OrderHelper
    {
        public class ColumnToSort
        {
            public string ColumnName { get; set; }
            public SortDirection Direction { get; set; }

            public ColumnToSort()
            { }

            public ColumnToSort(string columnName, SortDirection direction)
            {
                ColumnName = columnName;
                Direction = direction;
            }
        }

        public static ColumnToSort SplitJqueryFormatColumn(string sortingColumn) //assume we have as input: "Name ASC"
        {
            ColumnToSort columnToSort = new ColumnToSort();

            if (sortingColumn != null)
            {
                string[] parts = sortingColumn.Split(' ');
                if (parts.Length == 2)
                {
                    columnToSort.ColumnName = parts[0];
                    columnToSort.Direction = parts[1].ToLower() == "asc" ? SortDirection.Ascending : SortDirection.Descending;
                }
            }

            return columnToSort;
        }
    }

This is the extension method that will Order dynamically by a column string value and an sorting order.

public static class OrderExt
    {
        public static IOrderedQueryable< T > Order< T>(this IQueryable< T> source, string propertyName, SortDirection descending, bool anotherLevel = false)
        {
            var param = Expression.Parameter(typeof(T), string.Empty);
            var property = Expression.PropertyOrField(param, propertyName);
            var sort = Expression.Lambda(property, param);

            var call = Expression.Call(
                typeof(Queryable),
                (!anotherLevel ? "OrderBy" : "ThenBy") +
                (descending == SortDirection.Descending ? "Descending" : string.Empty),
                new[] { typeof(T), property.Type },
                source.Expression,
                Expression.Quote(sort));

            return (IOrderedQueryable< T>)source.Provider.CreateQuery< T>(call);
        }
    }

Here is how to use the Order extension method for List Action that is needed for JTable in ASP.NET MVC 4:

[HttpPost]
        public JsonResult List(int jtStartIndex, int jtPageSize, string jtSorting = null)
        {
            try
            {
                int totalItemsNumber = db.Authors.Count();
        
                OrderHelper.ColumnToSort columnToSort = new OrderHelper.ColumnToSort();
                if (jtSorting != null)
                    columnToSort = OrderHelper.SplitJqueryFormatColumn(jtSorting);
                else
                    columnToSort = new OrderHelper.ColumnToSort("Name", SortDirection.Ascending);

                List< author> authors = db.Authors
                                         .Order(columnToSort.ColumnName, columnToSort.Direction)
                                         .Skip(jtStartIndex)
                                         .Take(jtPageSize)
                                         .ToList();

                return Json(new { Result = "OK", Records = authors, TotalRecordCount = totalItemsNumber });
            }
            catch (Exception ex)
            {
                return Json(new { Result = "ERROR", Message = ex.Message });
            }
        }

Hope it solved your issue, too.

Source for the extension method found at: linq-to-entities-dynamic-sorting.

App_Code classes not available in Asp.Net Mvc 4 with Microsoft Visual Studio 2012

As in the last few weeks I have started to learn Asp.Net MVC 4 I found few issues. I want to tell you about one pretty awkward.

Just for you to know I have created a project "ASP.NET MVC 4 Web Application" and then as project template I've chosen "Internet Application".


Now: as I wanted to add some of my helper classes and other utilities I created a "App_Code" folder.


Later as I said, I added few classes in that folder. Then I was surprised for I could not use those classes from Controller's folder.
I've tried to change namespace,changing from  MyDemoProject.App_Code to just MyDemoProject. No success :)

So as App_Code classes were not available(accessible) I've searched a little around and I found this solution: you have to go at Properties section of the file and then for Build Action change from Content to Compile. That is it ;)


Of course, you have to bring in the namespace MyDemoProject or MyDemoProject.App_Code as it fits your scenario.

Hope it helped you, too.