Dynamic sql where in sql server

I’ve been recently working a dynamic sql script launched from within a stored procedure that will generate a filter claused based on parameters passed. One the filter clause that i had to take into account was a “WHERE IN”, while passing something like “a,b,c” as parameter, it did not seem to work, so i went a bit in depth and found out that there was some issues while using the WHERE IN clause in dynamic SQL.

The solution found to this problem is to transform the list in the parameter into a table form that can be used in the dynamic WHERE IN clause

[sql]

DECLARE @ParamList NVARCHAR(max)

DECLARE @Query NVARCHAR(4000)

SET @ParamList = ‘hello,this,is,a,list,of,param’

SET @Query = N’SELECT * FROM TableName WHERE ColumnName IN (SELECT param FROM iter_simple_charlist_to_tbl(@ParamList))’

exec sp_executesql @Query, N’@ParamList NVARCHAR(max)’, @ParamList

[/sql]

 

Below is the script for the Table-valued function to convert a given VARCHAR parameter list to table (A small variation is needed for integer type parameter list)

[sql]
/****** Object: UserDefinedFunction [dbo].[iter_simple_charlist_to_tbl] Script Date: 06/17/2011 12:40:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[iter_simple_charlist_to_tbl]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))
DROP FUNCTION [dbo].[iter_simple_charlist_to_tbl]
GO
/****** Object: UserDefinedFunction [dbo].[iter_simple_charlist_to_tbl] Script Date: 06/17/2011 12:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[iter_simple_charlist_to_tbl] (@list nvarchar(MAX))
RETURNS @tbl TABLE (param nvarchar(50) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(‘,’, @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END – @pos – 1
INSERT @tbl (param)
VALUES (substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
RETURN
END
[/sql]

References of this implementation are:
http://www.sommarskog.se/arrays-in-sql-2005.html#returnformat
http://www.restfuldevelopment.net/david-kawliche/writing/dynamic-in-without-dynamic-sql/

Note: This solution does not offer an performance optimization and can be inefficient in some scenarios.

Simple Resx Editor

I recently came to a nice Resx Editor alternative. It’s simple to use and the learning curve is very low. It’s perfect for your staff that you don’t want to train in Visual Studio to edit Resx file to text translation.

Take a look at it: http://simpleresxeditor.blogspot.com/2010/12/simple-resx-editor-0660-is-now.html

There is also a video tutorial available: http://www.youtube.com/watch?v=7hUdkqVXmWc&feature=player_embedded

It features:

* Highlight differences and coincidences (1)

* Highlight keys containing text

* Show/Hide keys

* Filter text-only rows

* Drag and drop support

* Multi-language UI

(1) With multiple resx files loaded

Toggl – Time tracking that works

Recently I’ve been searching for a Time Tracking/Management tool that will allow me to monitor and report the time that I spend  working on my task/projects. I found Toggl, as mention on the website:

Toggl is Time tracking that works. Get things done and turn your time into money. Track time with a single click, switch easily between different tasks and create reports.

I have been using it for a few month now and it’s really worth a try, the free version offers nearly all functionality that a time tracker needs. It has a windows agent that allows you to track your time directly from your desktop without event connecting to the website. And most of all it’s compatible with all major platforms (Windows/Mac/Linux/Mobile) !!!

CodeIgniter – Pagination SEO Issue

I have recently been working with a PHP MVC Framework called CodeIgniter on a complete web application solution.  I have been trying some major framework like CakePHP, Zen and Symphony which where all very powerful framework for MVC and RAD development, the only thing they lack was a bit more of flexibility like CodeIgniter propose. Anyway may not have taken enought time to get to know all of the specifics of the other Frameworks, but while benchmarking i got aquainted to CodeIgniter much faster.

Even though CodeIgniter is a very flexible framework, it’s very lightweight and some feature for Web application have not been taken into account, that in mind, the people behind EllisLab, Inc made sure that these small twigs were easily bypassed by allowing complete customization of their libraries.

Here is my original issue:

I have a item listing page with pagination activated and I wanted the first page to be the the root URL of the item page.
e.g. http://www.mysite.com/items

But what CodeIgniter Pagination Library generated for the first page was: http://www.mysite.com/result/1

That is pretty inconvenient for SEO, because the crawler will find two pages with the same content while crawling the pages.

Thus i modified the CI_Pagination library an created MY_Pagination.

First of all i have added a new variable called first_page_url as class variable in MY_Pagination class

[php]

class MY_Pagination extends CI_Pagination {

var $first_page_url        = ”; // The first page will have this URL

[/php]

I have changed the original Pagination Library First page rendering from

[php]

// Render the “First” link
if  ($this->cur_page > ($this->num_links + 1))
{
$output .= $this->first_tag_open.'<a href=”‘.$this->base_url.'”>’.$this->first_link.'</a>’.$this->first_tag_close;
}

[/php]

to

[php]

// Render the “First” link
if  ($this->cur_page > ($this->num_links + 1))
{
$output .= $this->first_tag_open.'<a href=”‘.$this->first_page_url == ” ? $this->base_url : $this->first_page_url.'”>’.$this->first_link.'</a>’.$this->first_tag_close;
}

[/php]

This way if during the initialization of the Pagination class the configuration setting first_page_url was passed it will be used instead of the base_url.

Some modification were also made to the pagination digit generation from

[php]

// Write the digit links
for ($loop = $start -1; $loop <= $end; $loop++)
{
$i = ($loop * $this->per_page) – $this->per_page;

if ($i >= 0)
{
if ($this->cur_page == $loop)
{
$output .= $this->cur_tag_open.$loop.$this->cur_tag_close; // Current page
}
else
{
$n = ($i == 0) ? ” : $i;
$output .= $this->num_tag_open.'<a href=”‘.$this->base_url.$n.'”>’.$loop.'</a>’.$this->num_tag_close;
}
}
}

[/php]

to

[php]

// Write the digit links
for ($loop = $start -1; $loop <= $end; $loop++)
{
$i = ($loop * $this->per_page) – $this->per_page;

if ($i >= 0)
{
if ($this->cur_page == $loop)
{
$output .= $this->cur_tag_open.$loop.$this->cur_tag_close; // Current page
}
else if($loop == 1 && $this->first_page_url != ”)
{
$output .= $this->num_tag_open.'<a href=”‘.$this->first_page_url.'”>’.$loop.'</a>’.$this->num_tag_close;
}
else
{
$n = ($i == 0) ? ” : $i;
$output .= $this->num_tag_open.'<a href=”‘.$this->base_url.$n.'”>’.$loop.'</a>’.$this->num_tag_close;
}
}
}

[/php]

which will make sure that the page numbered 1 takes has the first_page_url has href when  first_page_url is available.

The complete file can be found here: MY_Pagination

Been a while !!!

It’s been a while since the last post on my blog. Since then there have been quiet some changes in my life and work, in mostly good ways. I’ve been working a lot on various aspect or our web platform at work, in terms of optimization, design and scalability. And i think this has help me progress and learn a lot from the Web. I’ll try to post my learning as much as I can and contribute the maximum to help the web community.

Merge pdf files using C#

Recently I had to create an assembly component using C# to merge multiple PDF files into one file. The specification was pretty straight forward:
1) Merge two of more PDF document into a single output PDF File.
2) Used on an ASP.NET Page

After a few minutes of GOOG, I came up to this “ITextSharp

iText# (iTextSharp) is a port of the iText open source java library written entirely in C# for the .NET platform. iText# is a library that allows you to generate PDF files on the fly. It is implemented as an assembly.

It must be noted that the assembly is coded and compiled using the .NET Framework 1.1. You might want to migrate to the version 2.0 or 3.5 of the .NET Framework.

With a some more research on PDF merging. I was able to create a class that will make use of the ITextSharp assembly and perform as the merge pdf operation as needed.

Here is the code:
[csharp]using iTextSharp.text;
using iTextSharp.text.pdf;

public class MergeEx
{
#region Fields
private string sourcefolder;
private string destinationfile;
private IList fileList = new ArrayList();
#endregion

#region Public Methods
///
/// Add a new file, together with a given docname to the fileList and namelist collection
///
public void AddFile(string pathnname)
{
fileList.Add(pathnname);
}

///
/// Generate the merged PDF
///
public void Execute()
{
MergeDocs();
}
#endregion

#region Private Methods
///
/// Merges the Docs and renders the destinationFile
///
private void MergeDocs()
{

//Step 1: Create a Docuement-Object
Document document = new Document();
try
{
//Step 2: we create a writer that listens to the document
PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(destinationfile, FileMode.Create));

//Step 3: Open the document
document.Open();

PdfContentByte cb = writer.DirectContent;
PdfImportedPage page;

int n = 0;
int rotation = 0;

//Loops for each file that has been listed
foreach (string filename in fileList)
{
//The current file path
string filePath = sourcefolder + filename;

// we create a reader for the document
PdfReader reader = new PdfReader(filePath);

//Gets the number of pages to process
n = reader.NumberOfPages;

int i = 0;
while (i < n)
{
i++;
document.SetPageSize(reader.GetPageSizeWithRotation(1));
document.NewPage();

//Insert to Destination on the first page
if (i == 1)
{
Chunk fileRef = new Chunk(” “);
fileRef.SetLocalDestination(filename);
document.Add(fileRef);
}

page = writer.GetImportedPage(reader, i);
rotation = reader.GetPageRotation(i);
if (rotation == 90 || rotation == 270)
{
cb.AddTemplate(page, 0, -1f, 1f, 0, 0, reader.GetPageSizeWithRotation(i).Height);
}
else
{
cb.AddTemplate(page, 1f, 0, 0, 1f, 0, 0);
}
}
}
}
catch (Exception e) { throw e; }
finally { document.Close(); }
}
#endregion

#region Properties
///
/// Gets or Sets the SourceFolder
///
public string SourceFolder
{
get { return sourcefolder; }
set {sourcefolder = value; }
}

///
/// Gets or Sets the DestinationFile
///
public string DestinationFile
{
get { return destinationfile; }
set { destinationfile = value; }
}
#endregion
}
[/csharp]
To use the MergeEx class:
1) Initialize the class
2) Set the SourceFolder and DestinationFile properties
3) Using the AddFile method, add the source file names that need to be merged (Filename only since the SourceFolder has already been set)
4) Call the Execute Method

If everything works fine you will find your Merged PDF Document at your stated destination file.

The code is pretty much self decribed. If there is any question, i can always be contacted via this post.

SEO: Bounce rate of a website

Why is my bounce rate so high ?

Definition: A bounce occurs when a person leaves your website after reaching your entry page. The above cases can be considered equally as bounces from your website.

1) Visitor enters your site and press back immediately (before or even after the page has loaded)

2) Visitor waits for the page to load stays on this page for some time and then press back or navigate on another site. ( In this case the visitor might have found the information and then chose to navigate elsewhere to either find some supplementary information. Or it could be that he/she might not have found it but just read some pieces to see what is there, a third case could be the persons did not like the: website, content or colors on the site and went away.)

Therefore there seems to be considerable number of aspects to take into consideration to get a more precise question about “Why is my bounce rate so high ?”. There isn’t any straight forward answer to this question, but there are many questions that can lead to possible solutions:

When you ask your questions about bounce rate here are the different questions that might come to your mind.

Why is my bounce rate so high ?

User Interface
Is my layout/presentation/design attractive to visitors ?
Does my pages load slowly ?
Do my page have appropriate ads ? Are these ads non-aggressive towards the user ?
Is your page browser friendly ? (Can be views at any resolution with any browser the same way)

Content

Does

Windows Live Writer

It’s been not nearly a year since I last used Windows Live Writer, there seems to have been a lot of enhancement and changes compared to the first version that i have tried up. Today, I’ll be giving a try to the beta version (Build 14.0.5025.904). The setup was quiet easy and straight forward, warning me that I must enable XML-RPC on my blog before I can continue.

The first thing I installed is the S3 Object plug-in, to be able to upload images directly to my Amazon S3 account and use them on this post.

This image comes from my S3 bucket.

There are many feature such as the Insert Map: Let’s try it out and point out Mauritius for example 🙂

Where i live.

Nice. I have also created a Marker (Push Pin). Which does not seem to appear, When you create a map on Windows Live Messenger, it’s not the actual map that appears but an image with a link to the actual map… 🙁

Some other comment: There is to a problem, when there are connection cuts. The application just hangs during an operation. It was the case for my first post. Hope that is will be happen.

Conclusion a very nice and elaborated tool for blogging. I’ll will be trying it for a fews days and will send some feedback on this post. Or create a new one if need be.

My previous article to Windows Live Writer can be found here