Entity Framework in .NET and MySQL tinymce(1) problem

You know at tinymce(1) field in MySQL database will be converted to boolean type in C#. Sometimes you don’t want this because you just want to read the integer value from MySQL in this field (it can be any from 0-255 if unsigned or -127-127 if signed) – one byte.

In order to do so you need to do two things:

  1. Add TreatTinyAsBoolean=false to your connection string
  2. Edit the database edmx file and in SSDL content section change bool to tinyint, and in CSDL content section change Boolean into SByte.

Be careful if you update edmx file because changes you have made will be lost. This is the bad thing about this, but it is working solution.

Microsoft .NET to run on Linux and Mac OS X

Microsoft .NET will finally run fully on Linux and Mac OS X platform and this is a great news.

Visual Studio is maybe the best tool for developers and Microsoft decided to open sourcing the full server-side .NET Core stack, from ASP.NET 5 down to Core Runtime and Framework.

Microsoft is also giving for free Visual Studio Community 2013 full featured edition of Visual Studio, available today.

You can watch more about this here.

Open source links
ASP.NET – https://github.com/aspnet/home
.NET Compiler – http://roslyn.codeplex.com/
.NET Core – https://github.com/dotnet/corefx
.NET – https://github.com/Microsoft/dotnet

How to stop annoying mouse selection on click in Visual Studio?

You were working normally and suddenly on every click your mouse perform annoying multirow selection.

In order to stop this just press this combination of keys:

Alt Gr+Ctrl (right)+Shift (right)

How to delete duplicated records in MS SQL Server?

First, create one demo table

Let’s create one demo table first. It will be called Person and will hold some fictive person data.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE dbo.Person
(
  Id int IDENTITY(1,1) NOT NULL,
  Firstname nvarchar(50) NULL,
  Lastname nvarchar(50) NULL,
  Email varchar(100) NULL,
  Created datetime NULL,
  CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
  (
    Id ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
GO
SET ANSI_PADDING OFF
GO

Now, fill the data

Insert some fictive person data and do some duplicated records that have the same values for fieldsĀ  firstname, lastname and email.

INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('John', 'Belvien', 'john.belvien@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Peter', 'Lumberjack', 'peter.lumberjack@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Peter', 'Lumberjack', 'peter.lumberjack@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Peter', 'Lumberjack', 'peter.lumberjack@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Suzy', 'Leghorn', 'suzy.leghorn@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Suzy', 'Leghorn', 'suzy.leghorn@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())
INSERT INTO Person (Firstname, Lastname, Email, Created) VALUES ('Daisy', 'Sommerfield', 'daisy.sommerfield@example.com', GETDATE())

Now the magic

Use this subquery trick to extract duplicate records and then delete them. Notice the use of PARTITTION and WITH Transact-SQL keywords. All records that have RowNumber > 1 are deleted.

WITH CTE AS
(
  SELECT  Id,
          Firstname,
          Lastname,
          Email,
          ROW_NUMBER() OVER (PARTITION BY Firstname, Lastname, Email
          ORDER BY	Created) AS RowNumber
  FROM Person
)
DELETE FROM Person WHERE Id IN (SELECT Id FROM CTE WHERE RowNumber > 1)

How to submit form in jQueryMobile?

jQuery Mobile is excellent framework and I was trying to figure out why postback of my web form is not working properly and I finally found out that jQuery Mobile use ajax in passing forms so you need to add parameter data-ajax=”false”. It is better to read documentation sometimes.

<form id="page_form" method="post" action="[your-url]" data-ajax="false">
...
</form>

Using SQL Server PIVOT feature to transform rows into columns

PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level.

Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount.

Id	PaidDate                    Amount
1	2014-01-05 12:34:22.000	    318.22
2	2014-01-06 11:44:00.000	    128.33
3	2014-04-07 16:44:00.000	     55.50
4	2014-02-08 15:12:00.000	     88.55
5	2014-02-16 18:39:00.000     115.45
6	2014-03-01 16:27:00.000	      9.99
7	2014-03-08 13:13:00.000	     16.99
8	2014-04-27 16:44:00.000	    568.69
9	2014-05-10 19:40:00.000	     43.65

What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query:

SELECT *
FROM (
  SELECT  YEAR(PaidDate) AS [Year],
          LEFT(DATENAME(month, PaidDate), 3) AS [Month],
          Amount FROM Invoice
) as PaymentsByYear
PIVOT
(
  SUM(Amount)
  FOR [Month] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
) AS PaymentsByYear
ORDER BY [Year] DESC

And we finally get

Year    JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     SEP     OCT     NOV     DEC
2014	446.55  204.00  26.98   624.19  43.65   NULL    NULL    NULL    NULL    NULL    NULL    NULL

How to get TimeZone by RemoteIP or for specific city

The goal: to find out the TimeZone of a visitor.

There are few free services that can give you this information.

First step is to get the City name by RemoteIP address – there are free services/databases that helps you to achieve this, one of them is MaxMind.

When you have a city name you can get its latitude/longitude from the database of the same service or you can use Google API like this:

For Berlin, Germany:
http://maps.googleapis.com/maps/api/geocode/json?address=Berlin&sensor=false

You will get JSON result like this:

{
  "results" : [
  {
    "address_components" : [
    {
      "long_name" : "Berlin",
      "short_name" : "Berlin",
      "types" : [ "locality", "political" ]
    },
    {
      "long_name" : "Berlin",
      "short_name" : "Berlin",
      "types" : [ "administrative_area_level_1", "political" ]
    },
    {
      "long_name" : "Germany",
      "short_name" : "DE",
      "types" : [ "country", "political" ]
    }
    ],
    "formatted_address" : "Berlin, Germany",
      "geometry" :
      {
        "bounds" :
        {
          "northeast" :
          {
            "lat" : 52.6754542,
            "lng" : 13.7611176
          },
          "southwest" :
          {
            "lat" : 52.33962959999999,
            "lng" : 13.0911663
          }
        },
        "location" :
        {
          "lat" : 52.52000659999999,
          "lng" : 13.404954
        },
        "location_type" : "APPROXIMATE",
        "viewport" :
        {
           "northeast" :
           {
             "lat" : 52.6754542,
             "lng" : 13.7611176
           },
           "southwest" :
           {
             "lat" : 52.33962959999999,
             "lng" : 13.0911663
           }
         }
       },
       "types" : [ "locality", "political" ]
     }
   ],
   "status" : "OK"
}

After getting latitude and longitude of the city you can now ask Google for the TimeZone like this
https://maps.googleapis.com/maps/api/timezone/json?location=52.6754542,13.7611176&timestamp=1331161200&sensor=false

Note: timestamp is number of seconds since Jan 1st, 1970 which you can calculate in csharp (C#) using these function:

public static double ConvertToUnixTimestamp(DateTime date)
{
  DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
  TimeSpan diff = date.ToUniversalTime() - origin;
  return Math.Floor(diff.TotalSeconds);
}

public static DateTime ConvertFromUnixTimestamp(double timestamp)
{
 DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
 return origin.AddSeconds(timestamp);
}

And it will return this JSON result with TimeZone offset and name

{
  "dstOffset": 0,
  "rawOffset": 3600,
  "status": "OK",
  "timeZoneId": "Europe/Berlin",
  "timeZoneName": "Central European Standard Time"
}

Tips and Tricks: Export GoogleMaps as image

You don’t really need to bother with exporting GoogleMaps position into the image because Google created StaticAPI that renders the whole map as image, instead of using JavaScript to make dynamic map.

Example:

<img alt="" src="//maps.googleapis.com/maps/api/staticmap?center=46.056988,14.515536&amp;zoom=15&amp;size=455x350&amp;maptype=roadmap&amp;markers=color:red%7Clabel:S%7C46.056988,14.515536&amp;sensor=false" border="0" />

This will results with this:

How to escape parenthesis/brackets ‘{‘, ‘}’ in string.Format?

Usually you have something like this:

string.Format("Output: {0}", "A");

This will result as:

Output: A

What you should do if you want to make an output like this?

Output: {A}

You need to escape parenthesis/brackets like this:

  • You use {{ to output {
  • You use }} to output }

So, you final C# command should look like this:

string.Format("Output: {{{0}}}", "A");

Daily project: Automatic Twitter status updater

I like to read great quotes, because they express the wisdom and the creativity of human race.

So, I was thinking how I can share them automatically on my Twitter account. First task was to find out the source of great quotes and RSS/Atom feeds are perfect data source to complete this task. After some researching I found out few publicly available sources on BrainyQuote.com:

  1. Quotes of the day: http://feeds.feedburner.com/brainyquote/QUOTEBR
  2. Love quotes: http://feeds.feedburner.com/brainyquote/QUOTEFU

After having the source all we need to do now is to parse these RSS feeds and we need also a tool to publish these quotes automatically to the Twitter account.

For parsing RSS I rewrote one C# class that do this job (it reads data from RSS feed which is basically XML and put these data into one list of custom RSS objects).

TweetSharp is nice tool to do automatic updates of your Twitter status. You need to enter four keys from your Twitter application to make this process seamless:

  1. ConsumerKey
  2. ConsumerSecret
  3. AccessToken
  4. AccessTokenSecret

You get these keys after you create your first Twitter application under http://dev.twitter.com

Updating Twitter status is then an easy task:

//You have defined somewhere before: consumerKey, consumerSecret, accessToken, accessTokenSecret
var service = new TweetSharp.TwitterService(consumerKey, consumerSecret);
service.AuthenticateWith(accessToken, accessTokenSecret);
var response = service.SendTweet(new TweetSharp.SendTweetOptions() { Status = quote.Tweet });

After all is programmed and tested I just created an automated task that updates my Twitter status every 4 hours.

What was the result?

I increased the number of followers by 20% in just few days :-)

Plus, I can read this every day during my lunch break and enjoy in great wisdom quotes.

What need to be improved?

Sometimes I got an empty quote in my Twitter feed – so I need to check the content better before I publish it online.

What’s next?

This mini project could be upgraded into an SaaS service product where users will register, enter the RSS sources from which they want us to parse the data and service will do everything else automatically (publish on Twitter). So it could be named somehow “Intelligent automatic Twitter updater from the given data source”.

Interested? Contact me if you think it could be interesting and you want to contribute to this idea (perfect match would be if you can create the design and user experience for this service).

Follow

Get every new post delivered to your Inbox

Join other followers