Great sample on how to implement Custom Principal in ASP.NET
http://www.creatingsoftware.net/2007/11/aspnet-20-forms-authentication-keeping.html
I’ll be using this technique to integrate with ASP.NET MVC so I can avoid using Session State completely
I’ve been using EF for quite sometimes and I use Code First to build my schema. The question that I always have in mind is “Once the project is released and we need to add a new entity or add a new column to the existing entity, how do we tackle that?” and the question is even asked by my colleague. My answer was “oh, we can track down our changes by hand on a SQL file” and “I also try to be smart by saying we can get the latest production DB and compare it with the Development DB locally by using Database project or by using RedGate Schema Compare”
I’m still not happy even with my own answer!! and I believe that it’s not only me facing this problem and Microsoft should be smart enough to tackle this problem. So I start doing my research and I found the answer that I’m looking for – EF has an initializer called “MigrateDatabaseToLatestVersion” which accept 2 parameters DB Context and Configuration. So what is Configuration?It is a class that inherits from DbMigrationsConfiguration
So how do I get this class?There are 2 possible ways:
1. Use PackageManager Console (on your data layer project) and type the code below and it will create a new folder called “Migration” and a file with name “Configuration”
[code language=”bash”]PM>enable-migrations -enableautomaticmigration [/code]
2. Create a file called “Configuration.cs/.vb” or with whatever name that you wanted and paste the code below
[code language=”csharp”]
namespace GenericSolution.Data.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
using GenericSolution.Data;
internal sealed class Configuration : DbMigrationsConfiguration<GenericSolutionContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
//***DO NOT REMOVE THIS LINE,
//DATA WILL BE LOST ON A BREAKING SCHEMA CHANGE,
//TALK TO OTHER PARTIES INVOLVED IF THIS LINE IS CAUSING PROBLEMS
AutomaticMigrationDataLossAllowed = true;
}
protected override void Seed(GenericSolutionContext context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data. E.g.
//
// context.People.AddOrUpdate(
// p => p.FullName,
// new Person { FullName = "Andrew Peters" },
// new Person { FullName = "Brice Lambson" },
// new Person { FullName = "Rowan Miller" }
// );
//
}
}
}
[/code]
*AutomaticMigrationDataLossAllowed is a property that allow you to automatically drop the column from the schema when you remove a property from your entity class. By default, it sets to false which means it will throw an exception “AutomaticDataLossException” when you try to remove column from your table. So please use it cautiously
Next step is to use this configuration on your DBContext initializer which can be on your DBFactory class
[code language=”csharp”]
public DBFactory()
{
//Create database when not exists with code below
//Database.SetInitializer(new CreateDatabaseIfNotExists<GenericSolutionContext>());
//Pass null when you already have the database exists and no changes
//Database.SetInitializer<GenericSolutionContext>(null);
//Automatic schema migration
Database.SetInitializer(new MigrateDatabaseToLatestVersion<GenericSolutionContext, Configuration>());
}
[/code]
There is another class that will create an empty migration so that the future migrations will start from the current state of your database. I will update this blog once I know in detail about the functionality of it
1. Getting it by using PM Console
[code language=”bash”]PM> Add-Migration InitialMigration -IgnoreChanges[/code]
2. Create a class
[code language=”csharp”]
namespace GenericSolution.Data.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class InitialMigration : DbMigration
{
public override void Up()
{
}
public override void Down()
{
}
}
}
[/code]
EF is an ORM tool for Microsoft. It helps developer in interacting with the database (CRUD) and running a custom stored procedure. I’ve been using it and it’s all running perfectly fine. But I have a problem with querying an entity, when I try to query an entity by default it will run a SELECT * or selecting all columns, but for some data intensive application I might just require 2 columns instead of all columns. I’m not too concerned with a simple table with a few columns but how about when you have a or more than one BLOB/Varbinary/NVARCHAR(MAX) and you need 100 or 1000 rows records with a 2 single columns or even just an ID column. By default it will return all the columns but then you can use PROJECTION
[code language=”csharp”]
var q = from r in Context.TableName
select new
{
Id = r.Id,
Status = r.Status
}
[/code]
Let’s take it to another level, I’m building a common library for all entities and can be used dynamically by passing the column to the function
[code language=”csharp”]
/// <summary>
/// Dynamic Select query that loads only specific column passed in parameter and also based on the where clause that passed as a parameter
/// </summary>
/// <typeparam name="U"></typeparam>
/// <param name="columns"></param>
/// <param name="where"></param>
/// <returns></returns>
public IEnumerable<U> GetBy<U>(Expression<Func<T, U>> columns, Expression<Func<T, bool>> where)
{
return dbset.Where(where).Select<T,U>(columns);
}
[/code]
And it can be called as below and it will return me an IEnumerable of Anonymous object which then I need to process it manually to map with the actual entity
[code language=”csharp”]
_userRepository.GetBy(u => new { u.UserName, u.FirstName }, f => f.UserName == UserName)
[/code]
So the question is now how do I modify this function to return me the actual entity or else make a single function that just work across all entities without requiring manual mapping.
We can use Automapper – (you can get this from NuGet) to map your Anonymous Object with your base Entity function, so this combines the Generic <T> of entity with the projection as the parameter to select
a few specific columns) and it will return back the collection of the entity that you requested
[code language=”csharp”]
/// <summary>
/// Dynamic Select query that loads only specific column passed in parameter and also based on the where clause that passed as a parameter
/// </summary>
/// <typeparam name="U"></typeparam>
/// <param name="columns"></param>
/// <param name="where"></param>
/// <returns></returns>
public IEnumerable<T> GetBy<U>(Expression<Func<T, U>> columns, Expression<Func<T, bool>> where)
{
//Create an initial mapping between anonymous object and returned entity type
Mapper.CreateMap<U, T>();
var anonymousList = dbset.Where(where).Select<T, U>(columns);
return Mapper.Map<IEnumerable<U>, IEnumerable<T>>(anonymousList);
}
[/code]
It’s pretty clean to set, bind and use enum in your javascript
//Enum declaration
var AvailableActions = { “Action”: 1, “Priority”: 2, “Status”: 3 };
//Bind
function Load()
{
for (var action in AvailableActions) {
jQuery(“#MandatoryActionList”).append(“<option value=” + AvailableActions[action] + “>” + action + “</option>”);
}
}
//Use the enum
AvailableActions[“Priority”] will return 2
action will return the string representation in this case e.g “Action”, “Priority”, or “Status”
I was required to create a SQL Splitter function and I tried to browse internet to find an article about it but I never thought there is someone has done wonderful job in comparing the performance of his SQL Split function including with the benchmark and technical break down. Now based on this article, I’m quite sure I have a strong/high performance SQL Splitter function
http://bradsruminations.blogspot.com.au/2010/08/integer-list-splitting-sql-fable.html
Regards,
Fransiscus
This is a great article explaining how to create a custom exception that follows the standard
http://dotnetconcepts.wordpress.com/2010/05/28/custom-exceptions-in-c/
If you use IIS7 with windows authentication and kerberos, you might want to look into this support article from microsoft. This support article also cover IIS6
Now you can use IIF in SQL Server 2012 instead of CASE (again this only if you have 2 possible values that you want to evaluate)
–This will return Apple
SELECT IIF(‘APPLE’ = ‘APPLE’, ‘Apple’, ‘Banana’)
–This will return Banana
SELECT IIF(‘APPLE’ = ‘Banana’, ‘Apple’, ‘Banana’)
In SQL Server 2005, normally when we do paging we need to use CTE or nested SELECT Statement, but in SQL Server 2012 we can use OFFSET and FETCH to do the paging
Let’s start of how we normally do paging in SQL Server 2005/2008 – this will return the first 10 rows ordered by REF
WITH Fields AS
(
SELECT REF, NAME, DISPLAY, ROW_NUMBER() OVER (ORDER BY REF) AS RowNumber
FROM SU_FIELD
)
SELECT REF, NAME, DISPLAY
FROM Fields
WHERE RowNumber BETWEEN 1 AND 10
ORDER BY RowNumber ASC;
To move to next page we do
WITH Fields AS
(
SELECT REF, NAME, DISPLAY, ROW_NUMBER() OVER (ORDER BY REF) AS RowNumber
FROM SU_FIELD
)
SELECT REF, NAME, DISPLAY
FROM Fields
WHERE RowNumber BETWEEN 11 AND 20
ORDER BY RowNumber ASC;
In SQL Server 2012 – it looks much more simpler to write the query. OFFSET is used for skipping the first x rows ad FETCH NEXT is used to control how many records to be returned
–This will return 10 records without skipping any row (REF from 0 – 9)
SELECT REF, NAME, DISPLAY
FROM SU_FIELD
ORDER BY REF
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;
to move to the next page
–This will return 10 records with skipping first 10 rows (REF from 10 – 19)
SELECT REF, NAME, DISPLAY
FROM SU_FIELD
ORDER BY REF
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;