Posts

Showing posts from 2011

TakeFive - what an extension

public static class LinqExtensions
{
public public static IQueryable<T> TakeFive(this IQueryable<T> query)
{
return query.Take(5);
}
}

Extract currency values from text with T-SQL

CREATE FUNCTION dbo.fnGetAmounts(@str nvarchar(max))
RETURNS TABLE
AS
RETURN
(
-- generate all possible starting positions ( 1 to len(@str))
WITH StartingPositions AS
(
SELECT 1 AS Position
UNION ALL
SELECT Position+1
FROM StartingPositions
WHERE Position <= LEN(@str)
)
-- generate all possible lengths
, Lengths AS
(
SELECT 1 AS [Length]
UNION ALL
SELECT [Length]+1
FROM Lengths
WHERE [Length] <= 15
)
-- a Cartesian product between StartingPositions and Lengths
-- if the substring is numeric then get it
,PossibleCombinations AS
(

SELECT CASE
WHEN ISNUMERIC(substring(@str,sp.Position,l.Length)) = 1
THEN substring(@str,sp.Position,l.Length)
ELSE null END as Number
,sp.Position
,l.Length
FROM StartingPositions sp, Lengths l
WHERE sp.Pos…

The <link> saga on ASP .Net

Image
Indeed, there is a saga. Since from the very beginning of my ASP .Net experiences I had to deal with the "CSS problem". This problem occurs when the referenced CSS is not loaded, because the relative path is not given right.

We place in the Master Page all the common html used in the website, including the links to the CSS files. Pages might be placed in different nested folders and because of this, the link to the CSS file needs to be adjusted with the relative prefixes. Since the link is placed in the Master Page and it is not known in advantage the website structure, the solution should solve the problem of how is the right prefix added to the CSS link.

HTML introduced Relative Uniform Resource Locators ( see [Page 11]) which actually solves this problem. Instead of writing the absolute paths (ie. http://www.mysite.com/public/main.css ), it suffices to use the relative paths, like public/main.css. That RFC explains the algorithm of how the prefixes ( /, ./, ../, ../../ so o…

Generate class from a LINQ projection

Image
using System;
using System.Linq;
using Microsoft.CSharp;
using System.CodeDom;

public static class QueryExtensions
{
public static string GetClassDefinition<T>(this IQueryable<T> query, string className)
{
var type = typeof(T);
var sb = new StringBuilder();
sb.AppendLine(string.Format("public class {0}", className));
sb.AppendLine("{");
using (var provider = new CSharpCodeProvider())
{
foreach ( var prop in type.GetProperties())
{
var typeRef = new CodeTypeReference(prop.PropertyType);
var propertyTypeName = provider.GetTypeOutput(typeRef);
if ( !propertyTypeName.StartsWith("<>"))
{
propertyTypeName = propertyTypeName.Replace("System.Nullable<", String.Empty)
.Replace(">","?")
.Replace("System.", String.Empty);
sb.AppendLine(string.Format("\tpublic {0} {1} {{get; set;}}", propertyTypeName, prop.Name));
}
}
}
sb.AppendL…

MS-SQL script to generate update statements for rtrim and ltrim

Recently I did a bulk insert into a table from a CSV file and this data had trailing spaces. I didn't notice until some email addresses look like weren't valid.

I read this article which explains when the data is trimed or not

Since I had many columns to update I needed to find another solution to write/generate the update statements for "update table set column = rtrim(ltrim(column))"


declare @table_name varchar(150) = 'dbo.Member'
declare @stmt nvarchar(max)



set @stmt =
(
select '[' + c.name + '] = rtrim(ltrim([' + c.name + ']))' + ',' + char(10) + char(9)
from
dbo.syscolumns c
inner join dbo.systypes st ON st.xusertype = c.xusertype
inner join dbo.systypes bt ON bt.xusertype = c.xtype
where
c.id = object_id(@table_name,'U') and (bt.name = 'varchar' or bt.name = 'nvarchar')

for xml path ('')
)
if @stmt is not null
begin

set @stmt = 'update ' + @table_name + char(10) + 'set ' + substri…

Update between two tables when there is no relation between them

I needed a SQL statement which fills the null values from the second column of #T1 table with values from #T2(C1).

There is no foreign key or match between the columns of those two tables.

I found two approaches:

1. Using CTE

create table #T1 (C1 varchar(10), T2C1 varchar(10))
create table #T2 (C1 varchar(10))

insert into #T1 values ('A1', '1')
insert into #T1 values ('A2', null)
insert into #T1 values ('A3', null)
insert into #T1 values ('A4', '4')
insert into #T1 values ('A5', null)

insert into #T2 values ('a')
insert into #T2 values ('b')

;with t2 as
(
select C1, row_number() over (order by C1) as Index2
from #T2
)
,t1 as
(
select T2C1, row_number() over (order by C1) as Index1
from #T1
where T2C1 is null
)
update t1
set t1.T2C1 = t2.C1
from t2
where t1.Index1 = t2.Index2

select * from #T1

drop table #T1
drop table #T2

2. With Derived Tables

create table #T1 (C1 varchar(10), T2C1 varchar(10))
create table #T2 (…

CKEditor m.lang.contextmenu is undefined

I got the error "m.lang.contextmenu is undefined" while I was installing CKEditor on an ASP .Net website and trying to use a minimum setup.

I supposed the "ckeditor/lang/en.js" is enough, but, as pointed here, the editor doesn't download en.js by default, so it needs to be set on config.js

CKEDITOR.editorConfig = function (config) {
config.language = "en.js";
};

If this is not set, the editor is trying to load "the language that best fit the user language" using browser detection, so this can by any language (for sure, supported by CKEditor). That's why the editor could work with a browser, but not work with other browser, on the same machine.

Encapsulating Sets of Properties in Complex Types, convenient or not?

Image
With EF we can group properties into Complex Properties. For example I'm using audit information on all my entities, like CreatedOn, UpdatedOn, Deleted and Enabled and I grouped them into a ComplexType named Audit. A customer can have an InvoiceAddress and a DeliveryAddress of type Address(Street1, Street2, County, Town Postcode).




This is nice, but I encountered some problems with the updates.
As MSDN says "When any property is changed anywhere in the object graph of a complex type, the property of the parent type is marked as changed and all properties in the object graph of the complex type are updated when SaveChanges is called."
So if the UpdatedOn is changed only, the EF will "update" and CreatedOn, Enabled, Deleted properties when the SaveChanges is called. This means the update statement will update UpdatedOn column with the new value and the CreatedOn, Enabled and Deleted with the values present in the Object Graph:
Let's create first a customer

var cust…

Remotely Install EntityFramework 4.1 with psexec and gacutil

I updated an website to use EntityFramework 4.1 Update 1. While I had no problems installing it on my dev machine, on the production machine the installer wasn't able to install the EntityFramework.dll on GAC. The reason is simple, there is no SDK installed.

I can't RDC on the production server, but I have a hidden shared folder, named "adrian$".

To install EntityFramework on the production machine I needed psexec, able to start and run gacutil. As I said, gacutil was not installed, because there is no SDK, so I copied the .Net 4.0 version (from C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools) on my shared folder "adrian$\gacutil" together with gacutlrc.dll from 1033 folder.
Next thing to do was to copy the EntityFramework.dll. Summarized, the new remote files looks like:

\\server\adrian$\gacutil\1033\gacutlrc.dll
\\server\adrian$\gacutil\gacutil.exe
\\server\adrian$\gacutil\gacutil.exe.config

\\server\adrian$\EntityFramework.dll

And the psexec co…

IIS 7.5, HTTPS Bindings and ERR_CONNECTION_RESET

Having an website with HTTP and HTTPS bindings, running on my local IIS 7.5 server, I encountered this error with Chrome, Error 101 (net::ERR_CONNECTION_RESET) while trying to access it with HTTPS protocol. I checked the IIS and the bindings were fine, both protocols were set and self-signed certificate didn't expire yet.

Since the website it wasn't accessible on the HTTPS protocol and everything seems ok at the IIS level, I suspected there was something which it can be fixed with the netsh command.

The first thing to do was to list the SSL server certificate bindings:

netsh http show sslcert

As I expected, there was nothing shown. To "add a new SSL server certificate binding and corresponding client certificate policies for an IP address and port" (from help), netsh can be used with some basic parameters: ipport, certhash and appid.

The certhash value can be read either from ISS Manager (Manage server\Server Certificates, select a certificate, click View in the right …

Practical Example for Abstract Classes

using System;

namespace AbstractClasses
{
public abstract class Messager
{
protected string _recipient;
protected string _message;
public Messager(string recipient, string message)
{
_recipient = recipient;
_message = message;
}
public void ProcessMessage()
{
LogMessageLength();
// the code from here
var isValid = false;
if (ValidateRecipient())
{
isValid = true;
SendMessage();
}
// to here will be implemented by the concrete classes
if (isValid)
NotifyTheServiceOwner("The message was correctly sent");
else
NotifyTheServiceOwner("The message was NOT sent");
}
protected abstract void SendMessage();
protected abstract bool ValidateRecipient();
private void LogMessageLength()
{
// log the mess…

Table Per Hierarchy Inheritance with Column Discriminator and Associations used in Derived Entity Types

Image
In this article I will show how to model a table per hierarchy, a bit more complex than I found in books or other articles. I'm sure this model is present in real lif,e but I really didn't have the luck to find an example on the web when I needed it most. The complexity of this model is given by the Foreign Keys ( the Associations) which need to be placed in the derived entities types and the fact the discriminator depends on more than one column. The first problem I could fix, but the next one, with the discriminator based on two or three nullable columns I couldn't do it, unless if I use a single discriminator column (ie ItemType or something).
The database is very simple. It is designed for an eshop which sells products, services and also has some nice packages, that combines products and services and offers them for good prices compared with if they would be bought alone. The products and services are very different in terms of how data describes them, so the decision …

Working with TIME type in SQL and ASP .Net

Scenario: a teacher schedules his courses on a random different dates in the near future, but he always know the start time. For sample in the current week he can schedule a course for Tuesday and Friday, at 08:00 AM.
In a database there would be the following tables:
1. course (title, summary, ..)
2. course_schedule(course_id, location, start_time) where data about that schedule is preserved( ie. location)3. course_schedule_span(schedule_id, course_date) for storing the dates when the schedule spans
The dates have always the same start time, I knew this before designing the database, so in the first time I let the time in the course_schedule_span table, in course_date column (ie. 31/12/2011 16:00). Later the teacher wanted to set and the end time so I decided to move the start time into course_schedule table and this new end time field to add it there also. The first attempt was to store the time in a varchar(5) column, this would suffice for storing like hh:mm values. Later I changed my …

About the "The Controls collection cannot be modified because the control contains code blocks" symptom

As the error message says, if a control contains code blocks (but not databinding expressions, ie <%# ..), its Controls collection can't be altered, by adding another control at runtime using Controls.Add. However, this rule doesn't apply for child controls, for example if a control uses other control with code blocks, then the first control's Controls collection (the parent's one) has no limitations.

Usually this error is encountered mostly when working with the <head runat="server".
Suppose there is a code block which uses some code to decide which protocol to use to download some external scripts, depending on the connection used by the client. <head runat="server">
<% if (!HttpContext.Current.Request.IsSecureConnection)
{ %>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js" type="text/javascript"></script>
<% }
else
{%>
<script src="https://ajax.googleapis.c…

Of, Doame!

Image

My Birthday

Image
Today is my birthday and because today my thoughts are only about myself, I had a curiosity about in which years I did celebrate my birthday on Wednesdays ( I like this day of week).
With the following LINQ query I found the answer

from i in Enumerable.Range(0,29)
where (new DateTime(i + 1983,4,6).DayOfWeek) == DayOfWeek.Wednesday
select new { year = i + 1983, age = i }

which is..

Year Age
1983 0
1988 5
1994 11
2005 22
2011 28

And because my curiosity about me and only me goes mad, I wanted to find out more about each day of week and my birthdays.

Enumerable.Range(0,29).ToLookup(i =>new DateTime(i + 1983,4,6).DayOfWeek, i=>new {year = i + 1983,age = i}).OrderBy(d=>(int)d.Key)

click on the image to see it in full size

Thanks to LINQ Pad.

Virusii, messengerul si clinicile particulare

Image
Printre pietenii de pe YM, am si un contact de la o clinica particulara din Iasi. Cand aveam nevoie de o programare, mi-era muuult mai simplu sa o fac pe messenger, decat la telefon.

Dar, cum "virusurile" informatici au pus stapanire pe calculatorul din "front office", clinica s-a pricopsit cu o problema de PR, de care nu prea vrea sa scape prea curand.

Tinute business

Image
Dau peste ditamai banner-ul pe hotnews.ro care te duce la e-shopul www.otto.ro

Arunc un ochi pe pagina tinute business si ce gasesc



Propun ca prezentatorii proiectului "e-romania" sa adopte tinuta.

Ce mai canta un programator?

Ce mai canta un programator?

La primul release: "Like a virgin, touched for the very first time"
La primul set de buguri: "We will we will rock you,
We will we will rock you"
La al 10-lea set de buguri: "Guess who's back
Back again
Shady's back
Tell a friend
Guess who's back, guess who's back, guess who's back, guess who's back"
La primul avertisment: "We don't need no education
We don’t need no thought control"
Dupa 10 ani de programare: "C'mon c'mon, respect authority!"
"C'mon c'mon, obey authority!"
"C'mon c'mon, authorities are here to help you"
"Do what they say"
"…

TVR si sondaje - sau cum poti manipula

Image
Zilele astea a fost un sondaj pe situl TVR, care continea urmatoarea intrebare: "Care credeţi că este soluţia pentru a rezolva problema câinilor comunitari?" si avea cinci variante de raspuns:
Adopţia
Sterilizarea
Eutanasierea
Altă opţiune/comentaţi
Nu ştiu/nu mă interesează



Fiind cat de cat interesat de subiect si inscris intr-un grup de discutii, am vazut ca oamenii au inceput sa se alarmeze ca se voteaza de prea multe ori pentru o anumita optiune. Pur si simplu intr-un interval de doua ore raporturile o luasera razna, deci fie foarte multi oameni s-au mobilizat sa voteze, fie s-a scris o program de votare automata. S-a inclinat pentru prima varianta, datorita faptului ca formularul de sondaj contine si validare CAPTCHA.
Am inceput sa studiez problema si am mai vazut ca de la un calculator se poate vota de mai multe ori, pentru ca votul era numarat pe sesiune, nu pe IP. Deci inchideai si deschideai browserul sau stergeai cookie-ul PHPSESSID si puteai vota din nou. Asta era prima …

Elmah: to find more, query AllXml as Xml!

select convert(xml,AllXml)
.value('(//queryString/item[@name="id"]/value/@string)[1]','nvarchar(255)') as product_id
, errorid
from elmah_error
where convert(xml,AllXml)
.exist('//item[@name="PATH_INFO"]/value[@string="/products/product.aspx"]') = 1


Since Elmah stores in the AllXml column many informations for the logged errors, those can be queried in the XPath/XQuery fashion. The AllXml values needs to be converted to the Xml type and the great thing is MS-SQL Server supports XML queries.

This sample outputs all values used for the "id" query string field, at the "/products/products.aspx" requests. In this way will get a good idea what to fix on that page.

Totals on Reports using LinqDataSource

Image
One common request for reporting is to show aggregations as well, like sums, averages, counts, etc among with the report itself. So for example if an expenses/charges/profits report shows the list of all products sold in some date/time range, then the person who analyzes the report would like to see and some totals (sum of the costs, sum of the charges, sum of profits and the total number of products), somewhere in the report's bottom.



If the number of reports is slightly lower and the features are limited, like the reports need to support only the HTML version, LINQ with LinqDataSource can be the right thing to use, for RAD reports. The control is built so the developers can write less and do more, so basically they set the ContextTypeName, TableName, Select statement if any, WhereParameters, OrderParameters, etc, but this control it doesn't yield the aggregates.

I extended the LinqDataSource in the spirit of "write less do more" principle and I changed its default be…

Round Decimals Up and Down

Nr. Round Up
Math.Ceiling(number * 100) / 100M Round Down
Math.Floor(number * 100) / 100M26.71026.7126.7126.71126.7226.7126.71226.7226.7126.71326.7226.7126.71426.7226.7126.71526.7226.7126.71626.7226.7126.71726.7226.7126.71826.7226.7126.71926.7226.71

Rounding Down (floor) a real number is finding the largest integer not greater than that number and Rounding Up (ceiling) a real number is finding the smallest integer not less than the number.

This table extends the classic definition of floor and ceiling to two decimals.

A cazut serveru'

Am o baza de date, sa zicem cea mai mare din Romania. Sa spunem despre baza asta de date asa mare ca are si cel mai mare grup de utilizatori. Totul e asa de mare incat poate concura cu succes cu facebook.

Baza asta de date a mea sta la baza unei aplicatii care ar trebui sa faca doua lucruri:
1. sa colectecteze date: unii utilizatori introduc date in baza mea de date. Volumul asta de date poate fi foarte mare, cu variatii, dupa cum e vremea, rece sau calda. Noaptea datele sunt mai putine, depinde de urgente.
2. sa afiseze date: alti utilizatori au nevoie sa vada datele. Aplicatia aia intreaba serverele si ele raspund cu un set date, care arata ca un puzzle pentru unii si de aceea aplicatia mi le transforma ori pentru ecranul unui calculator ori pentru o imprimanta ori pentru o alta aplicatie care nu vrea sa le vada asezate decat intr-un anumit fel.

Problema e ca avand un numar mare utilizatori serverele care gestioneaza baza de date s-ar putea sa nu mai faca fata. Zic ca s-ar putea pentru…