create number tables for months and days - part 2 #SQLServer

As mentioned in our last post, here's a better way to create a table of numbers.

;with cte as (

select 1 as n
union all
select n+1 from cte
where n+1 <= 31
)
select n from cte

This query uses a recursive Common Table Expression.

More information abount Recursive CTE's can be found here:

• Recursive Queries Using Common Table Expressions

It might be a little bit confuse the first time you use it, but remember that a Recursion is a the result of stacked statements. In this particular case, we are stacking queries.

Any doubts and questions, fill in the comment section bellow.

create number tables for months and days - part 1 #SQLServer

Recently I had to run a report in a table with a date column. When I had my query ready to go, I realize that should repeat the same query for every days and months in an year.
So I came up with this:

declare @months table (month int)

insert into @months
values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)

declare @days table (day int)

insert into @days
values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
(21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31)

select * from @months m
cross join @days d
order by month, day

The hint here its to use cross join, that allows you to create a desired multiplicity.

Although simple, solved my problem for that moment. And after thinking a little, came up with a better solution.
So don't miss the next post, and check a better way to create a very useful numeric table.

See you next time!

No Entity Framework provider found for the ADO.NET provider #error

Problem
I've stumbed recently with this error message:

No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'

In my context, I was trying to use Entity Framework with a MVC 4 application. It happens that I have created the EDMX file in a project different than the MVC one, and did it before. So by the time that I tried to use my EntityModel, my web.config was not properly set.

Solution
In your web.config, check the following:
-Does your "connectionStrings" section properly defined?
-Does your connectionString name correct and used and correct in your <providers>?
-Does your web app references the EntityFramework.SqlServer dll?

For me, began to work with
<providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>

in <entityFramework> section.

mindnote: remember to install using NuGet packages next time

remove duplicated records? #SQLServer

As far as I know, a decent database should never have duplicate records. But sometimes we are not taking the full table, and data might become duplicated for certain context.
The following query is an example of how to differ between two identical records.

;with cte as (
  select

    id,

    RANK() over (partition by id order by LastUpdate desc) as ranking
  from Table
) select * from cte
where cte.ranking = 1

It uses two interesting concepts: CTE's and RANK function.
The trick here is to rank the records with RANK function, ordered by last updated record. The  clause is in charge of keeping the non desired records off the result.

For more information check the following links:
WITH common_table_expression (Transact-SQL)
RANK (Transact-SQL)

create an e-mail attribute in my form? #mvc4

What about using an e-mail built in validation for your Microsoft MVC 4 application?
If you got that question, here's a tip:

in your model class, use the following annotation:

 [EmailAddress]  
 public String UserMail { get; set; }  

Enjoy!