Generate your database entities using T4 templates

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

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 :)

Programmatically list the local instances of SQL Server

Long back I wrote a post about Enumerating Instances of SQL Server using C. But this code snippet will not list your sql server instances installed on your system. Here is the code snippet using Windows registry which will list all the local instances of sql server.

var baseKey = RegistryKey.OpenBaseKey(
    RegistryHive.LocalMachine, RegistryView.Registry64);
var key = baseKey.OpenSubKey(
@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL");
foreach (string sqlserver in key.GetValueNames())
{
    Console.WriteLine((string.Format("{0}\\{1}", 
        Environment.MachineName, sqlserver));
}

Happy Programming :)

How to execute a Stored Procedure with Entity Framework Code First

Recently I worked on a project, which I started as code first and then I forced to switch to Database first. This post is about executing procedures from EF code first.(This is an update version of this post Here is my class structure and procedures.

class DatabaseContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
}

class Book
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ISBN { get; set; }
    public int AuthorId { get; set; }
}

class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

And here is my stored procedures

CREATE PROCEDURE usp_CreateBook
@BookName VARCHAR(200), @ISBN VARCHAR(200), @BookId INT OUTPUT
AS
SET NOCOUNT ON
INSERT INTO Books(Name, ISBN, AuthorId) VALUES(@BookName, @ISBN, 1)
SET @BookId = (SELECT SCOPE_IDENTITY())

CREATE PROCEDURE usp_CreateAuthor
@AuthorName VARCHAR(200), @Email VARCHAR(200) = NULL
AS
INSERT INTO Authors(Name, Email) VALUES(@AuthorName, @Email)

CREATE PROCEDURE usp_GetAuthorByName
@AuthorName VARCHAR(200)
AS
SELECT [Id] ,[Name] ,[Email] FROM [Authors]
WHERE Name = @AuthorName

And you can execute using DbContext.Database class. The DbContext.Database.ExecuteSqlCommand() method helps to executes the given DDL/DML command against the database. And it will return the number of rows affected.

var affectedRows = context.Database.ExecuteSqlCommand("usp_CreateAuthor @AuthorName, @Email",
    new SqlParameter("@AuthorName", "author"),
    new SqlParameter("@Email", "email"));

Or you can use without creating the SqlParameters.

var affectedRows = context.Database.ExecuteSqlCommand
    ("usp_CreateAuthor @AuthorName = {0}, @Email= {1}", 
    "author", "email");

The DbContext.Database.SqlQuery method helps to return elements of the given generic type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type.

var authors = context.Database.SqlQuery<Author>("usp_GetAuthorByName @AuthorName", 
    new SqlParameter("@AuthorName", "author"));

This method will return an DbRawSqlQuery, which you can enumerate using For / ForEach loop. For executing procedure with output parameter.

var bookIdParameter = new SqlParameter();
bookIdParameter.ParameterName = "@BookId";
bookIdParameter.Direction = ParameterDirection.Output;
bookIdParameter.SqlDbType = SqlDbType.Int;
var authors = context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT",
    new SqlParameter("@BookName", "Book"),
    new SqlParameter("@ISBN", "ISBN"),
    bookIdParameter);
Console.WriteLine(bookIdParameter.Value);

Happy Programming :)