code bits: June 2015

Wednesday, June 24, 2015

wpf - Application to convert string case

For the most part at my work place, programmers follow certain coding conventions.However,there are multiple teams that work on separate projects and there is no hard fast rule regarding coding standards.

I work for the platform team and my role involves converting and upgrading projects.When it comes to coding standards, I would definitely like to follow certain coding standards so that the code remains consistent and is easy to read.

Often, during upgrades,rewrites I end up with code that has case mixed up all over the place.Below is a very small piece of sql with mixed case.Usually I deal with SQL that is several hundred of lines and I find code with mixed case especially in sql server as an eye sore.Sorry for being a stickler :)

WITH T1 AS (

select * from my_table

)

SELECT

(SELECT COUNT(*) FROM T1) AS total,

(SELECT COUNT(*) FROM T1 WHERE mm_match = 'Y') AS match,

(select count(*) FROM T1 where mm_match = 'N') as nomatch,

((select count(*) FROM T1 WHERE mm_match = 'Y')/(select count(*) FROM T1))*100 as successrate

SQL server is case insensitive and ideally I like to keep the sql code case,table name,stored procedures names and the rest of the database object names in lower case.

I created a small WPF application top handle this.Below is the application that helps me in converting the case of the sql to lower or upper case.

The XAML is below

<Window x:Class="StringCaseConverter.MainWindow"

xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

Title="Case Converter" Height="768" Width="1024" ResizeMode="NoResize" Background="#FF0F0101">

<Grid>

</Grid>

</Window>

The code

using System.Windows;

namespace StringCaseConverter

{

/// <summary>

/// Interaction logic for MainWindow.xaml

/// </summary>

public partial class MainWindow : Window

{

public MainWindow()

{

InitializeComponent();

}

private void LowerButton_Click(object sender, RoutedEventArgs e)

{

string s = UserStringTextBox.Text.Trim();

if (string.IsNullOrEmpty(s))

MessageBox.Show("Empty String ! Enter a string to be converted.","Warning",MessageBoxButton.OK,MessageBoxImage.Warning);

else

{

ConvertedStringTextBox.Text = s.ToLower();

CaseLabel.Content = "The string has been converted to LOWER case";

}

private void UpperButton_Click(object sender, RoutedEventArgs e)

{

string s = UserStringTextBox.Text.Trim();

if (string.IsNullOrEmpty(s))

MessageBox.Show("Empty String ! Enter a string to be converted.","Warning",MessageBoxButton.OK,MessageBoxImage.Warning);

else

{

ConvertedStringTextBox.Text = s.ToUpper();

CaseLabel.Content = "The string has been converted to UPPER case";

}

Lower Case

Upper case

sql - count total rows,subset of rows and percentage

Recently in one of the projects, I had to get the total number of rows along with count of a subset of rows and the percentage of the subset of rows compared to the total rows.

The table structure is very simple with just two columns Id,Match.The Id column is of type int and Match column takes two values 'Y' and 'N' indicating whether a match was found or not in the given Id.We use this table as a reporting table to record some activity.

For reporting purposes we wanted to get the total records sent for match in a given week and wanted to know the success rate.

Below is the query to get the total count,match count,nomatch count and the success rate.

select

id,count(*) total,

count(case when match='Y' then 1 end) match,

count(case when match='N' then 1 end) nomatch,

(cast(count(case when match='Y' then 1 end) as float)/cast(count(*) as float)) * 100 successrate

from
my_table

group by
id

order by
id desc

Monday, June 22, 2015

ASP pages on IIS - Active Server Pages Error 0221 Invalid @ Command Directive

Setting up asp pages that use asp.net code behind pages to handle the requests will require additional steps to be configured on IIS. I had to upgrade one of the sites which had few asp pages.I did not have the option to rewrite the page as aspx page and hence had to host it on the IIS server an asp page.

The site was upgraded to use 4.0 .Net engine and hosted on IIS 8.5. The aspx pages worked as expected,however I stared getting asp error 0221 when I tried to access the asp urls.

Below is the error screenshot.

The asp file has a page directive indicating that the language is c# and that it uses a code behind page aspx.cs to process the requests.

In order to get the asp page with asp.net code behind file to work, I had to indicate that the page will be processed by the asp.net engine. Below are the screenshots of the steps required to get the asp page to be processed by asp.net engine on IIS 8.5.

Open Inetmgr and navigate to Handler Mappings in the features section for the site hosting the asp page with asp.net code behind page.

Double Click the Handler Mappings features to bring up all the extension mappings.

Double Click ASPClassic to edit the handler.You will notice that the ASPClassic pages are handled by the asp.dll. This makes the asp page not recognize the page directives since the directives are for the asp.net engine.

Click the ellipses and navigate to the appropriate .Net framework directory and choose the aspnet_isapi.dll as the handler.In my case the site has been built in .Net 4.0 version.

Note:For applications built on/for 64-Bit servers the aspnet_isapi.dll handler should be from Framework64 folder.

Once the asp.net handler has been mapped.Restart IIS and navigate to the asp page and the error should be resolved.Note that when you do the above handler mapping changes, the configuration file for you site will be updated with the new settings.

<system.webServer>
<handlers>
<remove name="ASPClassic" />
<add name="ASPClassic" path="*.asp" verb="GET,HEAD,POST" modules="IsapiModule" scriptProcessor="C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" resourceType="File" requireAccess="Script" preCondition="bitness32" />
</handlers>
</system.webServer>

The page no long throws the asp error and returns a xml tag as it supposed in my application.
Hope that helps !