MySQL: Sort By Column Values

Tuesday, December 13, 2011

In MySQL, sorting the data using columns can easily be done by using the ORDER BY clause. Recently, I came across a requirement where I had to sort the data according to the specific values of a column. For example, if a column has the numbers written as words (one, two, three etc.), the values need to be sorted according to their numerical equivalent rather than the default alphabetical sorting.

By default, the ORDER BY clause will sort data using alphanumeric sorting. But there is a trick by using the MySQL field(str,str1,str2,str3...) function to give a different order to the values in the columns. The function finds the position of str in the list of strings str1, str2, str 3...

Here is an example on how you can combine the field function with the ORDER BY clause:

ORDER BY field(`column_name`, 'One', 'Two', 'Three', 'Four', 'Five')

The above code will sort the data according to the column values One, Two, Three, Four, Five.

MySQL: Exporting Selective Columns to CSV

Friday, November 11, 2011

There are many to export large data sets from MySQL in CSV. If you need data for the complete database or a table in the database, the 'mysqldump' command is a great solution. But sometimes you might need to just export a subset of columns in the database, filtered by where clause. This can be achieved by using the SELECT ... INTO clause. It lets you specify the columns you need for your CSV and you can easily use aggregate functions (SUM, AVG etc.), WHERE clause, GROUP BY clause, ORDER BY clause etc. to get the appropriate data to be exported. Also, custom CSV delimiters can be specified to be used for the exported data. You would need to have filesystem access to the MySQL server to run this command.

Here is an example query:

SELECT `column1`, `column2`, `column3` INTO OUTFILE 'c:\\data\\today.csv'

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

    LINES TERMINATED BY '\n'

FROM `tablename`

For more details and options please refer to the MySQL reference for the command.

Another way to export data from MySQL is by using MySQL Workbench, which is a free tool to administer MySQL databases. Though, the above command is really helpful if you want to schedule export jobs.

C# Default Arguments [Optional Arguments]

Monday, September 05, 2011

Coming from the C++ world, one of the things I missed in C# was the support for Default Arguments. Default arguments are used to create functions which could have a few optional arguments with default values. Thus, you could make overloaded calls to the method without creating different function prototypes if the purpose could by using a default value of one of the parameters. Many other common languages like PHP, VB.NET, JavaScript etc. also support this feature. It was surprising that C# did not support it.

With C# 4.0 (2010), the support for this feature has been added to the language. The new Optional Arguments in C# 4.0 allows you to specify the default values of parameters in case there values are not provided from the function call. Here's a small example to illustrate the basics of Optional Arguments in C#:

//C#

// Function with optional arguments
void OptionalArgExample(int reqdField, string opt = "default value")
{
    // do something
}

// Calling the method with just the required argument
OptionalArgExample(10);

// Calling the method with both the arguments
OptionalArgExample(10, "some text");

For more information, read the MSDN article on Named and Optional Arguments in C#

Troubleshooting Remote Desktop's Copy/Paste

Thursday, September 01, 2011

If you use Window's RDP to remote into your computer or server regularly, you might have noticed that sometimes the Copy and Paste functionality stops working. This functionality is often needed when you work with remote systems as you would frequently want to copy paste text to search for some help; copy some documents which would be easier to read on your computer than on the slow VPN connection you are connected to or maybe if you want to copy something a backup. Usually, to fix this I have to turn off my session and create a new session, which is really cumbersome. But, recently I found a really easy method to fix this situation here. Here's what you need to do to six the issue:

• Open Task Manager (Ctrl+Shift+Esc) on the remote machine.
• Find the "rdpclip" process and kill it.
• Run "rdpclip" to start it again.

Just follow the above steps and your Copy/Paste functionality will start working again between the remote and the host computer. This is a real time-saver when dealing with the issue.

Another alternate is to use a internet clipboard site like cl1p.net, which allows you to create a private clipboard which could be accessed from any computer. Also, I've sometimes used GMail to accomplish this by creating a new draft email and opening it from the other host. These methods are also useful if you want to copy data between different computers.

LINQ IEnumerable object to a DataTable

Monday, April 11, 2011

LINQ is really a very powerful tool for working with complex entities and datasets. It provides you with an easy way to map data to Entity classes. But, sometimes it is very helpful to have the results returned by a LINQ query map into a DataTable. If your LINQ returns a collection of DataRow objects, you can do this using the inbuilt DataTableExtensions.CopyToDataTable() method. However, if your query returns a collection of a custom entity type, you would need to write a utility to do that.

Below is a method that could be used to convert an IEnumerable entity object to a DataTable:

// C#
/// <summary>
/// Convert a Linq IEnumerable object to a DataTable
/// </summary>
/// <typeparam name="T">Entity Type</typeparam>
/// <param name="enumberable">Linq Resultset</param>
/// <returns>Datatable with results</returns>
public static DataTable LinqToDataTable(this IEnumerable enumberable)
{
    DataTable dt = new DataTable();

    T first = enumberable.FirstOrDefault();
    if (first == null)  // return if the resultset is empty
        return dt;

    Type t = first.GetType();
    PropertyInfo[] properties = t.GetProperties();

    // Adding columns to the datatable from the property info
    foreach (PropertyInfo pi in properties)
    {
        Type pt = pi.PropertyType;

        // Handling nullable properties in the resultset
        if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
            pt = Nullable.GetUnderlyingType(pt);
                
        dt.Columns.Add(pi.Name, pt);
    }

    // Adding rows to the datatable from the enumerable resultset
    foreach (object obj in enumberable)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyInfo pi in properties)
        {
            object value = (pi.GetValue(obj, null) != null) ? pi.GetValue(obj, null) : DBNull.Value;
            dr[pi.Name] = value;
        }
        dt.Rows.Add(dr);
    }

    return dt;
}

' VB.Net
''' <summary>
''' Convert a Linq IEnumerable object to a DataTable
''' </summary>
''' <typeparam name="T">Entity Type</typeparam>
''' <param name="enumberable">Linq Resultset</param>
''' <returns>Datatable with results</returns> _
Public Shared Function LinqToDataTable(Of T)(enumberable As IEnumerable(Of T)) As DataTable
 Dim dt As New DataTable()

 Dim first As T = enumberable.FirstOrDefault()
 If first Is Nothing Then
  ' return if the resultset is empty
  Return dt
 End If

 Dim t As Type = first.[GetType]()
 Dim properties As PropertyInfo() = t.GetProperties()

 ' Adding columns to the datatable from the property info
 For Each pi As PropertyInfo In properties
  Dim pt As Type = pi.PropertyType

  ' Handling nullable properties in the resultset
  If pt.IsGenericType AndAlso pt.GetGenericTypeDefinition() = GetType(Nullable(Of )) Then
   pt = Nullable.GetUnderlyingType(pt)
  End If

  dt.Columns.Add(pi.Name, pt)
 Next

 ' Adding rows to the datatable from the enumerable resultset
 For Each obj As Object In enumberable
  Dim dr As DataRow = dt.NewRow()
  For Each pi As PropertyInfo In properties
   Dim value As Object = If((pi.GetValue(obj, Nothing) IsNot Nothing), pi.GetValue(obj, Nothing), DBNull.Value)
   dr(pi.Name) = value
  Next
  dt.Rows.Add(dr)
 Next

 Return dt
End Function

Using ASP.NET AJAX UpdatePanels and jQuery Together

Friday, March 18, 2011

There are instances when you would like to use ASP.NET AJAX UpdatePanels and jQuery together on the same page. ASP.NET AJAX’s UpdatePanel does not give you the best performance but the ease of use and direct integration with ASP.NET control often times makes it a very useful control to implement partial page loads on the ASP.NET pages. With the powerful controls of jQuery and the ease of use of the UpdatePanel sometimes it does makes sense to use these two in conjunction. Having said that, as far as possible, you should avoid using multiple JS libraries on the same page to prevent any conflicts between them.

When you use the UpdatePanel and jQuery controls in the same page, often times the jQuery controls will only work before you do any postback for the UpdatePanel. As soon as the postback results get back to the page the jQuery controls stop working. There is an easy workaround to resolve the situation using the ASP.NET AJAX’s pageLoad() function. This function is similar to the Page_Load event for the ASP.NET page. So, your jQuery’s $(document).ready should be replaced by this method.

For example, if your current jQuery looks like:

$(document).ready(function() {
   // jQuery code
 });

To make it not conflict with the UpdatePanel, change it to:

function pageLoad() {
   // jQuery code
}

Remember this pageLoad() function is part of the ASP.NET AJAX library.