Thursday, January 28, 2016

Create class from database table

declare @TableName sysname = 'CustomerProfile'
declare @ClassText varchar(max) = 'public class ' + @TableName + '
{'

select @ClassText = @ClassText + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @ClassText = @ClassText  + '
}'

print @ClassText

Thursday, June 18, 2015

Display loader until the image gets fully loaded using javascript

One of the most common problems with web based applications involving high resolution images is the time taken to load the images. The images would load slowly section by section and it might be annoying for end users. To resolve this issue, we can display a loader before the image gets fully loaded and then display the actual image with a simple fade effect through jQuery:

First set the loader image as the default image

<img src="/ajax-loader.gif" id="profileImage" />


Declare the javascript to handle image loading

function loadImage(control, src) {
        var loadImage = new Image();
        loadImage.src = src;
        if (loadImage.complete) {
            control.hide().attr('src', src).fadeIn();
            loadImage.onload = function () { };
        }
        else {
            loadImage .onload = function () {
                control.hide().attr('src', src).fadeIn();
                loadImage.onload = function () { };
            }
        }
}


Call the function

loadImage($('#profileImage'), 'https://www.microsoft.com/global/learning/en-us/PublishingImages/ms-logo-site-share.png');

- Notice that I've used jQuery to fetch image object i.e. $('#profileImage') because of which I could use control.hide() in the function directly without having to use $(control).





Tuesday, June 9, 2015

LINQ prioritize order

I recently faced a scenario of prioritizing search results filtered by LINQ. Here is the scenario:

I have these records in database table:

1. Superman vs Batman
2. Batman
3. Dark night rises - Batman
4. Batman Returns

I want to search all the records having 'batman' text, however the words starting with batman should be displayed first. i.e. the order should be:

1. Batman
2. Batman Returns
...


This can be achieved using this simple LINQ query:

DbContext.Movies.Where(w => w.Name.Contains("batman")).OrderBy(d => d.Name.IndexOf("batman")).ToList();

I've highlighted the key part of the query which sorts the results based on the Index of word "batman". This means, if the word starts with 'batman', the index would be 0 and it would be displayed first.

Thursday, June 4, 2015

Executing MySQL sub routine in Entity Framework

Entity framework has an easy and slick way to support MySql. In MySQL stored procedures are called stored routines. Routines can be called from Entity Framework using below syntax:


using (var dbContext = new DatabaseContext())
{
  var subRoutine = string.Format("CALL StoredRoutineName('{0}','{1}')", value1, value2);
   signInAttempts = dbContext.Database.SqlQuery<int>(subRoutine,
                     new MySqlParameter("value1", value1),
                     new MySqlParameter("value2", value2)).FirstOrDefault();

}

Invalid object name 'dbo.__MigrationHistory'

Recently I faced an issue with Entity Framework 6.0. The error did not crashed the website, but I got it frequently in event viewer. To resolve this, I just added a line in DbContext's constructor:

public DatabaseContext() : base("name=Sample")
{
       Database.SetInitializer<DatabaseContext>(null);
}


If you use an existing database with Code First approach, you may not want to execute any initialization code. You can disable the database initialization process altogether by passing null to SetInitializer() method

Wednesday, December 17, 2014

C# CSV to Datatable

Add reference of  Microsoft.VisualBasic which would enable TextFieldParser class

using Microsoft.VisualBasic.FileIO;

public static DataTable GetDataTabletFromCsvFile(string csvFilePath)
{
    DataTable csvData = new DataTable();
    using (TextFieldParser csvReader = new TextFieldParser(csvFilePath,Encoding.UTF7))
    {
        csvReader.SetDelimiters(new string[] { "," });
        csvReader.HasFieldsEnclosedInQuotes = true;
        //read column names
        string[] colFields = csvReader.ReadFields();
        foreach (string column in colFields)
        {
            DataColumn datecolumn = new DataColumn(column);
            datecolumn.AllowDBNull = true;
            csvData.Columns.Add(datecolumn);
        }
        while (!csvReader.EndOfData)
        {
            string[] fieldData = csvReader.ReadFields();
            //Making empty value as null
            for (int i = 0; i < fieldData.Length; i++)
            {
                if (fieldData[i] == "")
                {
                    fieldData[i] = null;
                }
            }
            csvData.Rows.Add(fieldData);
        }
    }
    return csvData;
}

ASP.NET MVC partial view to string

protected string RenderPartialViewToString(string viewName, object model)
{
   if (string.IsNullOrEmpty(viewName))
                viewName = ControllerContext.RouteData.GetRequiredString("action");

    ViewData.Model = model;

   using (StringWriter sw = new StringWriter())
  {
     ViewEngineResult viewResult = 
           ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
     ViewContext viewContext = 
          new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, sw);
      viewResult.View.Render(viewContext, sw);

      return sw.GetStringBuilder().ToString();
  }
}