Tuesday, March 26, 2013

Optimizing SQL TOP queries (REPOST from geekswithblogs.net)

Here's an interesting article on optimizing queries using Top statement
to filter result sets: Why SQL Top may slow down your query?
The solutions are the following:
1. using Hash joins
 FROM [Articles]  
  INNER HASH JOIN CategoryCountry2Articles  
 ON [Articles].Id = CategoryCountry2Articles.IdArticle  
  INNER HASH JOIN CountryCategories  
 ON CountryCategories.Id = CategoryCountry2Articles.IdCountry  
  INNER HASH JOIN CategoryCity2Articles  
 ON [Articles].Id = CategoryCity2Articles.IdArticle  
  INNER HASH JOIN CityCategories  
 ON CityCategories.Id = CategoryCity2Articles.IdCity  
 WHERE CountryCategories.Name = 'country1'  
 AND CityCategories.Name = 'city4'  
2. Using Variables.
 DECLARE @topCount INT  
 SET @topCount = 5  
 SELECT TOP (@topCount)  
I am pretty much interested why using variables is much faster. Compared with other findings that variables slow your queries:
a. Parameter Sniffing Stored Procedures
b. Sql server query fast but slow from procedure
c. Why SQL Server go slow when using variables
d. SQL 2008 row number slow with variables


Monday, March 25, 2013

IOrderedQueryable<T> Extension Method (C#)

Here's a modified version of Nick Harrison's IOrderedQueryable extension method in his dynamic linq query post:
static class IQueryableExtensions  
     public static IOrderedQueryable<TSource> GenericEvaluateOrderBy<TSource>(this IQueryable<TSource> query, string propertyName)  
       var type = typeof(TSource);  
       var property = type.GetProperty(propertyName);  
       var parameter = Expression.Parameter(type, "p");  
       var propertyReference = Expression.Property(parameter, property); //p.ProductName  
       var sortExpression = Expression.Call(  
         new Type[] { type, property.PropertyType },  
         query.Expression, Expression.Quote(Expression.Lambda(Expression.MakeMemberAccess(parameter, property), parameter)));  
       return query.Provider.CreateQuery<TSource>(sortExpression) as IOrderedQueryable<TSource>;  
In order to use this, you could declare a IQueryable object similar to this:
 private void TestExtensionMethod()  
       IQueryable<Product> prodSorted = model.Products.AsQueryable();  
       Console.WriteLine("\nUsing GenericEvaluateOrderBy");  
       prodSorted = prodSorted.GenericEvaluateOrderBy("ProductName");  
       foreach (Product p in prodSorted)  
         Console.WriteLine("Product Name: " + p.ProductName + " Product Price:" + p.UnitPrice);  

Monday, March 18, 2013

Regex remove Day Name and Date Suffix

In a situation where i want to format this date value from (Tuesday 19th March 2013) to (19 March 2013). I made a regular expression by applying look behind approach and ends with behavior in strings. Here's the expression:
indicatorDate = Regex.Replace(indicatorDate, @"(\b[A-Za-z]*day\b)|((?<=[0-9])[a-zA-z]{2})", string.Empty, RegexOptions.IgnoreCase);

Friday, March 15, 2013

List Tables in a Database with criteria (MS SQL)

Here are some simple T-SQL statements to show table
information with set of criterias.
 -- show tables sort by name ascending  
 use DatabaseName  
 select * from sys.tables order by sys.tables.name asc  
 -- show tables that starts with s  
 use DatabaseName  
 select * from sys.tables where upper(sys.tables.name) like 'S%';  
 -- show tables sort by date created descending  
 use DatabaseName  
 select * from sys.tables order by create_date desc;  
 -- show tables where type description is user  
 use DatabaseName  
 select * from sys.tables where type_desc like lower('%user%')  
Reference: BOL

Wednesday, March 13, 2013

ASP.NET MVC Implementing MVCContribGrid CustomPagination<T> class

Out of boredom, I came upon a class named CustomPagination in MVCContrib that that implements IPagination, IEnumerable
and other interfaces that you can customize your paging needs.Here's the class definition:

namespace MvcContrib.Pagination  
   public class CustomPagination<T> : IPagination<T>, IPagination, IEnumerable<T>, IEnumerable  
     public CustomPagination(IEnumerable<T> dataSource, int pageNumber, int pageSize, int totalItems);  
     public int FirstItem { get; }  
     public bool HasNextPage { get; }  
     public bool HasPreviousPage { get; }  
     public int LastItem { get; }  
     public int PageNumber { get; }  
     public int PageSize { get; }  
     public int TotalItems { get; }  
     public int TotalPages { get; }  
     public IEnumerator<T> GetEnumerator();  
I found an article in this blog(http://lsd.luminis.eu) on how to use the CustomPagination class that returns an Enumerable object with paging but without implementations of the CusomPagination class.

After googling around, I found Jeremy Skinner's class implementation of CusomPagination.
namespace MvcContrib.Pagination  
     /// <summary>  
     /// Implementation of IPagination that wraps a pre-paged data source.   
     /// </summary>  
     public class CustomPagination<T> : IPagination<T>  
         private readonly IList<T> _dataSource;  
         /// <summary>  
         /// Creates a new instance of CustomPagination  
         /// </summary>  
         /// <param name="dataSource">A pre-paged slice of data</param>  
         /// <param name="pageNumber">The current page number</param>  
         /// <param name="pageSize">The number of items per page</param>  
         /// <param name="totalItems">The total number of items in the overall datasource</param>  
         public CustomPagination(IEnumerable<T> dataSource, int pageNumber, int pageSize, int totalItems)  
             _dataSource = dataSource.ToList();  
             PageNumber = pageNumber;  
             PageSize = pageSize;  
             TotalItems = totalItems;  
         public IEnumerator<T> GetEnumerator()  
             return _dataSource.GetEnumerator();  
         IEnumerator IEnumerable.GetEnumerator()  
             return GetEnumerator();  
         public int PageNumber { get; private set; }  
         public int PageSize { get; private set; }  
         public int TotalItems { get; private set; }  
         public int TotalPages  
             get { return (int)Math.Ceiling(((double)TotalItems) / PageSize); }  
         public int FirstItem  
                 return ((PageNumber - 1) * PageSize) + 1;  
         public int LastItem  
             get { return FirstItem + _dataSource.Count - 1; }  
         public bool HasPreviousPage  
             get { return PageNumber > 1; }  
         public bool HasNextPage  
             get { return PageNumber < TotalPages; }  
Based from this implementation, I added some features to this class and declared it in my Model. An implementation of the LastItem property is shown below based on my needs.
         if (PageNumber == TotalPages)  
           return TotalItems;  
           return (PageNumber * PageSize);  
After customizing the class, you can use it in your controller like this:
IPagination pm = new DisplayingAGrid.ModelsCustomPagination<Product>(products.Cast<Product>(),        
               p, 10, products.Cast<Product>().ToList().Count);  
  ViewData["pagedData"] = pm;  
  return View(); 
And in your ASP.NET MVC View, you can apply it in your MVCContribGrid:
 <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">  
   <h2>Products Grid with Custom Paging</h2>  
   <%: Html.Grid((IEnumerable<Product>)ViewData["pagedData"]).Columns(col =>   
           col.For(p => p.ProductName)   
            .Attributes(@class => "left");   
           col.For(p => p.Category.CategoryName)   
            .Attributes(@class => "left");   
           col.For(p => p.QuantityPerUnit)   
            .Attributes(@class => "left");   
           col.For(p => p.UnitPrice)   
            .Attributes(@class => "right");   
           col.For(p => p.Discontinued ? string.Format(@"<img src=""{0}"" />", Url.Content("~/Content/cancel.png")) : string.Empty).DoNotEncode().Named("Discontinued");   
          }) %>    
   <%= Html.Pager((IPagination) (ViewData["pagedData"] as IEnumerable<Product>)).First("<<").Last(">>").Next(">").Previous("<").Format("Item {0} - {1} of {2}")%>    
Here's the rendered ASP.NET MVC Page with MVCContribgrid:

Greg :)

Wednesday, March 6, 2013

Change Cell Url Contents to Hyperlinks using VBA in MS Excel

Assuming in your MS Excel Worksheet you have thousands of cells that contains url and you want them to be a hyperlink. It can be done manually, but tedious.

Another solution is to write a VBA (Visual Basic for Applications) Script to do that for you using the Sub procedure below:
Sub ChangeCellsToHyperlinks()  
   Dim rng As Range  
   Dim ctr As Integer  
   Dim range_name As String  
   ctr = 2 'starting cell to change  
   Do While ctr <= 10000 'end cell range  
     Set rng = ActiveSheet.Range("A" & ctr)  
     rng.Parent.Hyperlinks.Add Anchor:=rng, Address:=rng  
     With rng.Font  
       .ColorIndex = 25  
       .Underline = xlUnderlineStyleSingle  
     End With  
     ctr = ctr + 1  
 End Sub

That's it!

Tuesday, March 5, 2013

Show Label Count of Category Label (Blogger)

When I applied a new template to blogger, the category label count disappears.
So, I searched google and found this site on blogger tricks that shows how to customize labels.

So, I applied it in the expression expr:href='data:label.url'.

The original expression doesn't have label count.
  <a expr:href='data:label.url'><data:label.name/></a>  

Then, I applied the trick to show the label count.
  <a expr:href='data:label.url'><data:label.name/></a>  
  <span dir='ltr'>(<data:label.count/>)</span>    

Monday, March 4, 2013

Keyword not supported: “data source” (Passing connection string of type EF to EF constructor)

Assuming you have a connection string that points to an Entity Framework object. And in your connection class, you pass the connection string to the Entity Framework constructor below:
customerEntity = new Database1Entities(_connectionString); 

You encountered an error Keyword not supported: "data source".

The solution is presented here: Keyword not supported data source initializing entity framework context

It suggests to replace the " word with single quote (') character.
So, in this case, I used Regex to perform replace operation.
 return Regex.Replace(ConfigurationManager.ConnectionStrings[key].ToString(),  
           @"&quot", "'", RegexOptions.IgnoreCase);