Command line scaffolding for ASP.NET 5

Standard

Last K-MUG Session, there was a demo related to ASP.NET MVC scaffolding. This post is about scaffolding support in ASP.NET 5. This post uses K runtime for scaffolding, you can do it using Visual Studio 2015 Preview as well.

  • Modify project.json, add “Microsoft.Framework.CodeGenerators.Mvc”: “1.0.0-*” under dependencies.
  • Add “gen”: “Microsoft.Framework.CodeGeneration”, under commands
  • You are done with the configurations. Updated project.json file will look like this.
    {
      "dependencies": {
        "Microsoft.AspNet.Diagnostics": "1.0.0-beta1",
        "Microsoft.AspNet.Hosting": "1.0.0-beta1",
        "Microsoft.AspNet.Mvc": "6.0.0-beta1",
        "Microsoft.AspNet.Server.WebListener": "1.0.0-beta1",
        "Microsoft.AspNet.Identity.EntityFramework": "3.0.0-*",
        "EntityFramework.SQLite": "7.0.0-*",
        "Microsoft.Framework.ConfigurationModel.Json": "1.0.0-*",
        "Microsoft.Framework.CodeGenerators.Mvc": "1.0.0-*",
        "Microsoft.AspNet.StaticFiles": "1.0.0-*"
      },
      "commands": {
        "webListener": "Microsoft.AspNet.Hosting --server Microsoft.AspNet.Server.WebListener --server.urls http://localhost:5010",
        "gen": "Microsoft.Framework.CodeGeneration"
      },
      "frameworks": {
        "aspnet50": {},
        "aspnetcore50": {}
      }
    }
    
  • Execute KVM Upgarde command in command prompt, to upgarde your K Runtime.
  • Now execute KPM Restore command in command prompt. It will restore all the required nuget packages for scaffolding.
  • To scaffold the controllers and view, you need to execute the following command – “k gen controller -m TaskItem -dc TaskContext“, where TaskItem is the model class, and TaskContext is the DBContext. (As I am using SQLite, I have created DBContext class, if you are using SQL Server, you don’t require this, scaffolder will generate it for you.) Here is the TaskContext class.
    public class TaskContext : DbContext
    {
    	public DbSet<TaskItem> Tasks { get; set; }
    	protected override void OnConfiguring(DbContextOptions builder)
    	{
    		builder.UseSQLite("Filename=tasksDb.sqlite;");
    	}
    
    	protected override void OnModelCreating(ModelBuilder builder)
    	{
    		builder.Entity<TaskItem>().Key(t => t.Id);
    		base.OnModelCreating(builder);
    	}
    }
    
    

Here is the generated output.

COMMAND LINE SCAFFOLDING FOR ASP.NET 5

COMMAND LINE SCAFFOLDING FOR ASP.NET 5

Customizing scaffolding templates.

Similar to previous versions of MVC, you can customize the templates by modifying the templates. In old versions of MVC, you can find the templates under “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\Web\Mvc\Scaffolding\Templates” folder, but in ASP.NET 5, NuGet packages are stored on a per-user basis by default. So you can the scaffolding under following location – C:\Users\\.kpm\packages\Microsoft.Framework.CodeGenerators.Mvc\1.0.0-beta1\Templates\, you can modify the contents of the files, and you can re-generate using -f (force) option.

k gen controller -f -m TaskItem -dc TaskContext

Happy Programming :)

How to use SQLite in ASP.NET 5

Standard

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. Most of the ASP.NET 5 tutorials / articles, SQL Server is used. In this post I am explaining about how to use SQLite with Entity Framework 7.0.

First you need to modify the project.json file, and need to add SQLite and Entity Framework dependencies. (“EntityFramework.SQLite”: “7.0.0-*”),the project.json, dependencies section will look like this.

{
    "dependencies": {
        "Microsoft.AspNet.Diagnostics": "1.0.0-beta1",
        "Microsoft.AspNet.Hosting": "1.0.0-beta1",
        "Microsoft.AspNet.Mvc": "6.0.0-beta1",
        "Microsoft.AspNet.Server.WebListener": "1.0.0-beta1",
	"Microsoft.AspNet.Identity.EntityFramework": "3.0.0-*",
	"EntityFramework.SQLite": "7.0.0-*"
    }
} 

Now you need to create DbContext class. If you are using SQL Server, you can generate it using scaffolding(For SQLite, it did worked for me.) Here is minimal DbContext class and the model class I have created.

using System;
using Microsoft.Data.Entity;
using Microsoft.Data.Entity.Metadata;

public class Message
{
	public Guid Id { get; set; }
	public string Content { get; set; }
	public DateTime CreatedOn { get; set; }
	public string CreatedBy { get; set; }
}

public class HelloWorldContext : DbContext
{
	public DbSet<Message> Messages { get; set; }
	protected override void OnConfiguring(DbContextOptions builder)
	{
		builder.UseSQLite("Filename=messagedb.sqlite;");
	}

	protected override void OnModelCreating(ModelBuilder builder)
	{
		builder.Entity<Message>().Key(m => m.Id);
		base.OnModelCreating(builder);
	}
}

As you are using EF code first (creating the DB on the fly), you need to add the following code in the constructor of the Startup.cs, which will ensure SQLite Db is created, it is similar to Database Initializer classes in Entity Framework.

using(var context = new HelloWorldContext())
{
	context.Database.EnsureCreated();
}

This method won’t throw exception even if DB exists. Now you can write simple EF insert statement to insert data to the Table.

var helloWorldContext = new HelloWorldContext();
helloWorldContext.Messages.Add(new Message() {
	Id = Guid.NewGuid(),
	Content = "Hello World",
	CreatedOn = DateTime.Now,
	CreatedBy = "Anuraj" });
helloWorldContext.SaveChanges();

In the next post I will try explain how to scaffold controllers and views in ASP.NET 5 with SQLite.

Happy Programming :)

Generate your database entities using T4 templates

Standard

Long back I wrote some post about code generation using T4 templates. This post is about generating database entities using T4 templates. This code is pretty straight forward, you are reading the app.config to get the connection string, connecting to db server using Sql Server SMO objects, enumerating tables and columns.

Here is the code, which will read the App.Config file and get the connection string. (I am using a connection string with name, Default).

string appConfig = this.Host.ResolvePath("App.config");
if(!File.Exists(appConfig))
{
	Error("App.config not exists");
}

ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
configFile.ExeConfigFilename = appConfig;
var configuration = ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
var connectionString = configuration.ConnectionStrings.ConnectionStrings["Default"].ConnectionString;

Here is the code which will connects to the database, enumerate tables and columns and generates classes and properties.

SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(sqlConnection);
Server server = new Server(serverConnection);
var tables = server.Databases[server.ConnectionContext.DatabaseName].Tables;
#>
namespace <#= server.ConnectionContext.DatabaseName #>.Entities
{
<#
foreach(Table table in tables)
{
#>
public sealed partial class <#= CleanName(table.Name) #>
{
<#
foreach(Column column in table.Columns)
{
#>
	public <#= ToClrType(column.DataType, column.Nullable) #> <#= CleanName(column.Name) #> { get; set; }
<#
}
#>
}
<#
}
#>
}

And here is the two functions which will fix any issue with table name and column names. And returns C# type by using a SQL Server type.

private string FixTableName(string tableName)
{
	var result = tableName.Replace(" ","_");
	return System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(result);
}

private string ToClrType(DataType dataType, bool isNullable)
{
	string returnType = string.Empty;
	switch (dataType.SqlDataType)
    {
        case SqlDataType.BigInt:
            returnType = string.Format("{0}{1}","long", isNullable ? "?":"");
			break;
        case SqlDataType.Binary:
        case SqlDataType.Image:
        case SqlDataType.Timestamp:
        case SqlDataType.VarBinary:
            returnType = "byte[]";
			break;
        case SqlDataType.Bit:
			returnType = string.Format("{0}{1}","bool", isNullable ? "?":"");
			break;
        case SqlDataType.Char:
        case SqlDataType.NChar:
        case SqlDataType.NText:
        case SqlDataType.NVarChar:
        case SqlDataType.Text:
        case SqlDataType.VarChar:
        case SqlDataType.Xml:
            returnType = string.Format("{0}{1}","string", "");
			break;
        case SqlDataType.DateTime:
        case SqlDataType.SmallDateTime:
        case SqlDataType.Date:
        case SqlDataType.Time:
        case SqlDataType.DateTime2:
			returnType = string.Format("{0}{1}","System.DateTime", isNullable ? "?":"");
			break;
        case SqlDataType.Decimal:
        case SqlDataType.Money:
        case SqlDataType.SmallMoney:
			returnType = string.Format("{0}{1}","decimal", isNullable ? "?":"");
			break;
        case SqlDataType.Float:
			returnType = string.Format("{0}{1}","double", isNullable ? "?":"");
			break;
        case SqlDataType.Int:
			returnType = string.Format("{0}{1}","int", isNullable ? "?":"");
			break;
        case SqlDataType.Real:
			returnType = string.Format("{0}{1}","float", isNullable ? "?":"");
			break;
        case SqlDataType.UniqueIdentifier:
			returnType = string.Format("{0}{1}","Guid", isNullable ? "?":"");
			break;
        case SqlDataType.SmallInt:
            returnType = string.Format("{0}{1}","short", isNullable ? "?":"");
			break;
        case SqlDataType.TinyInt:
            returnType = string.Format("{0}{1}","byte", isNullable ? "?":"");
			break;
        case SqlDataType.Variant:
            returnType = string.Format("{0}{1}","object", "");
			break;
        case SqlDataType.DateTimeOffset:
            returnType = string.Format("{0}{1}","DateTimeOffset", isNullable ? "?":"");
			break;
		}

		return returnType;
}

Happy Coding :)

You can find the Gist here

CREATE DATABASE permission denied in database ‘master’ – SQL EXPRESS

Standard

Long back I wrote a post about Entity Framework Error – The underlying provider failed on Open. Yesterday some one told me, he is getting this error – CREATE DATABASE permission denied in database ‘master’, he is using an application pool with NETWORK SERVICE identity. Here is the solution, you require SysAdmin Role for “NT AUTHORITY\NETWORK SERVICE” to resolve this issue.

Setting the SysAdmin Role for Network Service

Setting the SysAdmin Role for Network Service

Happy Programming :)