Programming Solutions

SP_Impersonate

info@programmingsolution.net — Mon, 20 Feb 2012 04:00:00 +0300

USE [GSK_DATA_DB] GO /****** Object: StoredProcedure [dbo].[SP_Impersonate] Script Date: 06/28/2010 15:45:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[SP_Impersonate] with execute as 'bd\islamaw1' as begin exec [SP_REIM_REPORT_DATA] '', 'LC', '2010-01', 'PTW', 'All' end

Parallel Programming not supported with Entity Framework

info@programmingsolution.net — Fri, 27 Jan 2012 13:10:00 +0300

While working with Parallel programming in C# 4.0 together with Entity Framework, I faced a problem. I found that trying to do some lambda expression to query the entity model inside a parallel for or parallel foreach does not actually work. It may give different error at different time. All the error will occur at runtime. During compile time, you will not get any problem.

I tried to run the following code. Trying to find out a specific test object searching it with its id. Of course I was trying to do it inside a parallel for block.

Entities db = new Entities();

PP.Parallel.For(0, 10, i =>

{

List<TEST> tests = db.TESTs.Where(t => t.TEST_ID == i).ToList();

if (tests.Count != 0)

{

Console.WriteLine("{0}", tests[0].TEST_ID);

}

});

Sometimes you may get the following error:

EntityException was unhandled by user code

The underlying provider failed on Open.

Sometimes the following:

MappingException was unhandled by user code

The type 'Parallel.TEST' has been mapped more than once.

public ObjectSet<TEST> TESTs

{

get

{

if ((_TESTs == null))

{

_TESTs = base.CreateObjectSet<TEST>("TESTs");

}

return _TESTs;

}

private ObjectSet<TEST> _TESTs;

Now why is that and what is the resolution of this problem. The reason of this problem is very simple. Entity model does not support parallel execution. Entity object model is not thread safe. So you cannot query an entity model inside a parallel for block.

What is the solution? Very simple. Don't use it until Microsoft makes this entity model thread safe. :)

Cannot Implicitly Convert Type IQueryable

info@programmingsolution.net — Wed, 25 Jan 2012 09:29:18 +0300

Cannot implicitly convert type 'System.Linq.IQueryable<DBTestTool.Models.TEST>' to 'System.Collections.Generic.IList<DBTestTool.Models.TEST>'. An explicit conversion exists (are you missing a cast?)

Let me explain the scenario where I got this error message.

IList<TEST> tests = db.TESTs.Where(t => t.VIEW_ID == testTemplate.VIEW_ID && String.IsNullOrEmpty(t.TEMPLATE_FILE_NAME));

I have a DBContext named db and one entity is TESTS. I just want to find some specific tests that mathces with some criteria. The first and ultimate solution to this is to use Where() function in Lambda Expression. The where function uses the condition inside it and matches all the items in the entity object and returns the matching items. As my items are of TEST type, I am trying to hold it in a IList<TEST>, which is quite logical. But I face an error as mentioned at the top.

Cannot implicitly convert type 'System.Linq.IQueryable<DBTestTool.Models.TEST>' to 'System.Collections.Generic.IList<DBTestTool.Models.TEST>'. An explicit conversion exists (are you missing a cast?)

So it says that I need to do and explicit conversion. Why?

This is because lambda expressions are deferred execution expressions just as LINQ. When we write the lambda expression, it is immediately executed. That’s why it returns IQueryable<Test>, not IList<TEST>. So we need to explicitly cast it to IList<TEST>.

Solution to this error:

Solution 1:

IList<TEST> tests = (IList<TEST>)db.TESTs.Where(t => t.VIEW_ID == testTemplate.VIEW_ID && String.IsNullOrEmpty(t.TEMPLATE_FILE_NAME));

Just as the error says, use an explicit casting to IList<TEST>. It will force the execution to be done at that time.

Solution 2:

IList<TEST> tests = db.TESTs.Where(t => t.VIEW_ID == testTemplate.VIEW_ID && String.IsNullOrEmpty(t.TEMPLATE_FILE_NAME)).ToList();

Other than casting it, we can also call the ToList() method over the IQueryable<TEST>. This will also allow us to get the immediate execution and return the IList<TEST>.

So, whenever you use lambda expression that you might think should return an IList<>, you will actually have to cast it and use ToList() to the actual list.

IsPalindrome in T-SQL

info@programmingsolution.net — Sat, 21 Jan 2012 11:58:00 +0300

A palindrome is a word, phrase, number, or other sequence of units that can be read the same way in either direction. Some simple example of palindrome are

1. Malayalam

2. Amma

3. Appa

4. Madam

5. Racecar

To check whether a string is palindrome or not is very easy. We can create a function as follows:

CREATE FUNCTION dbo.IsPalindrome (@str varchar(50))

RETURNS int

BEGIN

if REVERSE(@str) = @str

return (1)

return (0)

END;

SELECT dbo.IsPalindrome('Malayalam') -- this is a palindrome

SELECT dbo.IsPalindrome('Amma') -- this is a palindrome

SELECT dbo.IsPalindrome('Appa') -- this is a palindrome

SELECT dbo.IsPalindrome('Hey') -- this is not a palindrome

SELECT dbo.IsPalindrome('Hola') -- this is not a palindrome

SELECT dbo.IsPalindrome('Ammaa') -- this is not a palindrome

The above function returns 1 if the string is a palindrome and returns 0 is the string is not a palindrome.

To check whether a string is palindrome or not, we just need to reverse the string and check whether the reversed string is equal to the original string or not. As we know, in T-SQL we have a REVERSE function, this is actually a very easy task to do in SQL.

All Character Occurrence in T-SQL

info@programmingsolution.net — Fri, 20 Jan 2012 13:43:09 +0300

In this post, we will see how we can find out what are the different characters available in a string and what is the number of occurrence of each individual character in the string. The first solution that comes to our mind is to use a while loop. This is very trivial solution and most of the beginners will code this way.

CREATE FUNCTION dbo.AllCharacterOccurence (@str varchar(MAX))

RETURNS @tbl TABLE (chr char, freq int)

BEGIN

declare @lp as int

declare @tbl2 table (chr char)

set @lp = 1

while (@lp <= LEN(@str))

begin

insert @tbl2

select SUBSTRING(@str,@lp,1)

set @lp = @lp + 1

end;

insert @tbl

select chr alpha, COUNT(*) freq

from @tbl2

group by chr

return;

END;

Here we see a table valued function that takes a string as the parameter and finds the number of occurrence of each individual character in the string.

Now we will see a different solution to this problem which is not very trivial, but seems to be very interesting. We want to get the result but dont want to use any loops. We will use pure sql to do this.

CREATE FUNCTION dbo.AllCharacterOccurence (@str varchar(MAX))

RETURNS @Characters TABLE (chr char, freq int)

BEGIN

insert into @Characters

select chr, COUNT(*)

from

(

select substring(@str, sequence, 1) as chr

from dbo.IntegerSequence(1, LEN(@str))

) A

group by chr

return;

END;

SELECT * from dbo.AllCharacterOccurence ('This should be very easy to do');

The output will be like this. Just the same as the previous function.

Now let's see how this works.

In this function we have used another function dbo.IntegerSequence(1, LEN(@str)) that gives us the integer numbers starting from 1 to the length of the string. The Integer Sequence generator function can be written as follows.

CREATE FUNCTION dbo.IntegerSequence(@start_number int,@end_number int )

RETURNS @SequenceTable TABLE (sequence int)

BEGIN

;With IntegerSequence( Number ) as -- Common table expression

(

Select @start_number as Number

union all

Select Number + 1

from IntegerSequence

where Number < @end_number

)

insert into @SequenceTable

Select N.number

From IntegerSequence N

Option (MaxRecursion 0)

return;

END;

So we take the numbers in different rows. (Figure - 1)

(Figure - 1) (Figure - 2) (Figure - 3)

Now we can use the string in another column together with this select. So we will get the same string in every row. (Figure - 2)

SELECT sequence, @str

from dbo.IntegerSequence (1, 30)

Now we can easily use substring method using the string and the index position to find out the character at the specified position. (Figure - 3)

SELECT sequence, @str, substring(@str, sequence, 1)

from dbo.IntegerSequence (1, 30)

The rest is really easy. You can just use the counting grouping by the characters.

Integer Sequence Generator Using CTE (Common Table Expression)

info@programmingsolution.net — Fri, 20 Jan 2012 11:39:39 +0300

Often we need to have a sequence of integer numbers in t-sql. There are lots of ways to generate integer sequence in t-sql. You can see one solution in the following location.

Integer sequence generator, Date sequence generator

In this post, we will see how easily we can generate integer sequence using CTE (common table expression). This solution is very short and easy to implement. But if we need a large number of integers, i.e. 1 million numbers, then this CTE based solution will be slow and will consume a lot of memory. If we need a small sequence, then probably this CTE based solution will be our choice.

Declare @start_number int, @end_number int

Select @start_number=3, @end_number=10;

With IntegerSequence( Number ) as -- Common table expression

(

Select @start_number as Number

union all

Select Number + 1

from IntegerSequence

where Number < @end_number

)

Select N.number

From IntegerSequence N

Option (MaxRecursion 0)

We will get the integer sequence as follows.

We can also create a table valued function using is CTE based solution. In that case, we can avoid using the Option (MaxRecursion 0) every time and we will be able to use the table valued function everywhere to generate any integer sequence we need.

CREATE FUNCTION dbo.IntegerSequence (@start_number int, @end_number int )

RETURNS @SequenceTable TABLE (sequence int)

BEGIN

;With IntegerSequence( Number ) as -- Common table expression

(

Select @start_number as Number

union all

Select Number + 1

from IntegerSequence

where Number < @end_number

)

insert into @SequenceTable

Select N.number

From IntegerSequence N

Option (MaxRecursion 0)

return;

END;

SELECT * from dbo.IntegerSequence (2, 1000);

SELECT * from dbo.IntegerSequence (200, 789);

This way we can use this table valued function anywhere and anytime we need to generate an integer sequence.

c# .net array.skip

info@programmingsolution.net — Fri, 20 Jan 2012 05:00:00 +0300

[No text]